索引是存储引擎用于快速找到记录的一种数据结构. 索引优化应该是对查询性能优化最有效的手段了. 索引能够轻易将查询性能提高几个数量级,"最优" 的索引有时比一个 "好的" 索引性能要好两个数量级.
索引可以包含一个 (单列索引) 或多个列 (组合索引) 的值.
想要使用组合索引首先要了解一个原则: 最左前缀原则. 如果索引包含多个列, 那么列的顺序十分重要, 因为 MySQL 只能高效地使用索引的最左前缀列.
最左前缀原则:
比如我们创建一个 (A,B,C) 组合索引, 也就相当与创建了 (A),(A,B),(A,B,C) 三个索引.
这里需要注意的一点是, 在满足最左前缀的原则下, 索引字段的使用和顺序无关.
如: select * from test where B = '1' and A = '2';
上述语句也使用了索引, 使用了 (A,B) 索引.
但是: select * from test where C = '1' and B = '2';
上述语句不能使用索引, 不满足最左前缀原则. 如果后面再加上 and A = '3' 则可以使用索引.
使用索引时需要注意的地方:
首先要满足最左前缀原则 , 如果不是按照索引的最左列开始查找, 则无法使用索引.
不能跳过索引中的列. 例如: where A = '1' and C = '3'; 在这当中, 只有 A 能使用索引, C 不能使用索引.
MySQL 能在索引中做最左前缀匹配的 like 比较, 但是如果是通配符开头的 like 比较, 就不能使用索引. 例如: like 'name%' 可以使用索引, like '%name' 则不能使用索引.
如果查询中有某个列的范围查询, 则其右边所有列都无法使用索引优化查找. 例如: where A = 'name' and B like 'name%' and C = 'name'; 这里只有 A,B 能使用索引, C 不能使用索引.
where 条件中包含表达式或函数的参数不能使用索引. 例如 where A + 1 = 2; 使用了表达式, 不能使用索引.
"把 where 条件里面的列都建上索引". 这类说法是错误的.
将选择性最高的列放在索引最前列.
order by 子句和查找型查询的限制是一样的, 需要满足索引的最左前缀的要求.
EXPLAIN 命令:
EXPLAIN 语句输出结果的各个行的解释如下:
id:select 识别符, 这是 select 查询序列号.
select_type: 表示查询中 select 类型, 简单或复杂.
table: 显示这一行的数据是关于哪张表的.
type: 连接类型, 结果值从好到坏依次是: system> const> eq_ref> ref> fulltext> ref_or_null> index_merge> unique_subquery> index_subquery> range> index> ALL. 一般来说, 得保证查询至少达到 range 级别, 最好能达到 ref. 如果值为 index, 则说明 MySQL 使用了索引扫描来做排序.
possible_keys: 指出 MySQL 能使用哪个索引在表中找到行, 查询涉及到的字段上若存在索引, 则该索引将被列出, 但不一定被查询使用.
key: 显示 MySQL 在查询中实际使用的索引, 若没有使用索引, 显示为 NULL.
key_len : 表示索引中使用的字节数, 可通过该列计算查询中使用的索引的长度.
ref: 表示上述表的连接匹配条件, 即哪些列或常量被用于查找索引列上的值.
rows: 表示 MySQL 根据表统计信息及索引选用情况, 估算的找到所需的记录所需要读取的行数.
Extra: 包含不适合在其他列中显示但十分重要的额外信息. 如 using where(这表示 MySQL 服务器将存储引擎返回行以后再应用 where 过滤条件);using index(使用了覆盖索引).
来源: http://www.linuxidc.com/Linux/2018-11/155369.htm