Mysql索引篇：索引优化

type

status

date

slug

summary

1、EXPLAIN分析执行计划

使用 EXPALIN 可以模拟优化器执行SQL查询语句，从而知道 MySQL 怎么处理你的SQL语句的，分析你的查询语句和表结构的性能瓶颈。注意 EXPALIN 只能解释 SELECT 操作，其他操作要重写为 SELECT 后查看执行计划。

字段解析如下：

id：每个 SELECT 关键字都对应一个唯一的id。如果是简单的单表查询，只有一个 SELECT 关键字，则只会有一条 id=1 的记录；如果是包含子查询的SQL语句，就可能有多个SELECT关键字，那么每个SELECT关键字都会对应一个唯一的id值。

select_type：查询的类型。

SIMPLE：SIMPLE 就是非常简单的查询，意味着不包含 UNION 或子查询，连接查询也算是 SIMPLE 类型。
PRIMARY：如果查询有任何复杂的子部分，如 UNION、UNION ALL、子查询，则最外层部分标记为 PRIMARY。
UNION：在 UNION 或 UNION ALL 子句中的第二个之后的 SELECT 都会被标记为 UNION。
UNION RESULT：UNION 查询需要使用内存临时表来去重，针对该临时表的查询的 select_type 就是 UNION RESULT，上面介绍 UNION 时可以看到这个类型。
SUBQUERY：包含在 SELECT 列表中的子查询中的 SELECT 会标记为 SUBQUERY，不包含 FROM 子查询。
DERIVED：DERIVED 用来表示包含在 FROM 子句的子查询中的 SELECT，MySQL 会将子查询的结果物化，放到一个临时表中，也称为派生表，因为该临时表是从子查询中派生来的。
DEPENDENT UNION、DEPENDENT SUBQUERY、DEPENDENT DERIVED：DEPENDENT 意味着 SELECT 依赖于外层查询中发现的数据。
MATERIALIZED：MATERIALIZED 意味着SQL中包含子查询（非FROM子查询），且子查询是物化的方式和外层查询进行连接查询的。
UNCACHEABLE SUBQUERY、UNCACHEABLE UNION：UNCACHEABLE 意味着 SELECT 中的某些特性阻止结果被缓存。

table：查询的表（别名），可以从这一列中从上往下观察SQL执行的关联顺序。

partitions：分区信息，一般情况下查询语句的执行计划的 partitions 列的值都是 NULL。

type：访问类型或者访问方法，就是MySQL决定如何查找表中的行。这个属性是分析执行计划时需要重点关注的信息，它告诉我们SQL是如何查询这张表的数据，有没有用到索引等等。优先级为：NULL > system > const > eq_ref > ref > ref_or_null > index_merge > range > index > ALL。

NULL：这种访问方式意味着MySQL能在优化阶段分解查询语句，在执行阶段甚至用不着再访问表或者索引。
system：当引擎是 MyISAM 或者 Memory 的时候并且只有一条记录，就是 system，表示可以系统级别的精准访问，这个不常见可以忽略。
const：查询命中的是主键或者唯一二级索引等值匹配的时候。比如 where id = 1。
eq_ref：通过主键或者唯一二级索引列等值匹配的方式进行查询，最多只会返回一条符合条件的记录。
ref：通过普通二级索引列与常量进行等值匹配时来查询某个表。相比 eq_ref，ref 可能返回多条符合条件的记录。
ref_or_null：类似于 ref，只是 ref_or_null 表示查询条件是 where second_key is null。
index_merge：一般情况下对于某个表的查询只会用到一个索引，但也可能同时使用多个索引，使用索引合并的方式来执行查询。不常见，可跳过。
unique_subquery：在一些包含 IN 子查询的SQL语句中，in 语句的时候命中了唯一索引。
index_subquery：类似于 unique_subquery，in 语句的时候命中了普通索引。
range：表示基于索引的范围扫描，比 index 全索引扫描好一些，因为它不用遍历全部索引。
index：命中了索引，但是需要扫描全部索引。index 类似于 ALL 全表扫描，不过 index 是顺序扫描索引的全部记录。它的主要优点是避免了排序，但如果要回表的话，开销可能比 ALL 更大。
ALL：没有使用索引，走的是全表扫描。应尽量避免这种方法的出现。

possible_keys：可能用到的索引

key：实际上使用的索引，也可能不在 possible_keys 中。想强制 MySQL 使用或忽视 possible_keys 列中的索引，在查询中使用 FORCE INDEX、USE INDEX 或者 IGNORE INDEX。这个也是重点关注部分。

key_len：实际使用到的索引长度，不损失精确性的情况下，长度越短越好

ref：使用索引列等值查询时，与索引列进行等值匹配的对象信息

rows：预估需要读取的记录条数

filtered：某个表经过搜索条件过滤后剩余记录条数的百分比。其实 filtered 在单表查询中意义不是很大，一般在多表连接中，则可用于预测被驱动表会被查询多少次。

Extra：扩展信息，但是非常重要，可以更好的辅助你定位 MySQL 到底如何执行的这个语句。从上往下性能越来越差。

Impossible WHERE：查询语句的 WHERE 子句永远为 FALSE 时将会提示该额外信息。
Using index：性能最高，这意味着使用了覆盖索引，通过索引可以直接定位到满足查询条件的数据行，不需要进行回表查询。当我们查询条件和返回内容都存在索引里面，就可以走覆盖索引，不需要回表。Using index 同时适用于主键索引和二级索引。比如 select second_key from test where second_key = 10 。
Using index condition：经典的索引下推（ICP），将原来在 server 层进行的 table filter 中可以进行 index filter 的部分，在引擎层面使用 index filter 进行处理，不再需要回表进行 table filter。Using index condition 仅适用于二级索引，一般发生在查询字段无法被二级索引覆盖的场景。比如 select * from test where second_key > 10 and second_key like '%0’。
Using where：表示使用 where 的条件过滤数据，有可能命中了索引，也有可能没命中，不管是否命中都需要回表查询所需的数据。一般发生在查询的列未被索引覆盖，且 where 筛选条件是索引列前导列的一个范围，或者是索引列的非前导列，或者是非索引列等场景。因为需要回表，所以性能比前两者差。比如 select * from test where text = 't'。
Using temporary：出现 Using temporary 意味着MySQL内部使用了临时表来完成一些功能，比如去重、排序之类的，前面演示 UNION 时，因为 UNION 要对结果集去重，所以就使用了临时表。
Using filesort：查询没有命中任何索引，需要将数据查到内存中，或者在数据较大时放到磁盘中，再进行排序。比如 select * from test where text = 't' order by text desc limit 10。
Using join buffer (Block Nested Loop)：在连接查询执行过程中，当被驱动表没有索引或者不能有效的利用索引加快访问速度时，MySQL一般会为其分配一块名叫 join buffer 的内存块，然后将数据读到这块内存块中，再基于块中的数据做嵌套循环查询，以此来加快查询速度。

Extra 还有一些组合情况，比如：

Using where;Using index：表示查询的列被索引覆盖，且 where 筛选条件是索引列前导列的一个范围，或者是索引列的非前导列。一般发生在联合索引，且查询条件和返回内容都存在索引里面，这种效率也很高。例如：select id from test where id > 5; 。

另外注意 type=index 和 Extra=Using index 的区别：

type=index 表示命中了索引。

Extra=Using index 表示查询使用了覆盖索引，没有回表查询。

2、索引失效的情况

下面汇总一下索引失效的情况：

模糊匹配使用左匹配或者左右匹配：当我们使用左或者左右模糊匹配的时候，也就是 like %xx 或者 like %xx% 这两种方式都会造成索引失效。

联合索引不满足最左匹配原则：创建了一个 (a, b, c) 联合索引，如果查询条件是 where b=2 或者 where b=2 and c=3 ，因为不满足最左匹配原因导致索引失效。

对索引使用函数： select * from t_user where length(name)=6; 对 name 字段使用了 LENGTH 函数，导致索引失效。

对索引进行表达式计算：select * from t_user where id + 1 = 10; 对 id 字段进行计算后再比较，导致索引失效。如果把查询语句的条件改成 where id = 10 - 1 就可以走索引查询了。

对索引隐式类型转换：参数类型与字段类型不匹配，导致类型发生了隐式转换，索引失效。例如如果索引字段是字符串类型，但是在条件查询中，输入的参数是整型的话，例如 select * from t_user where phone = 1300000001; 会导致索引失效。但是如果索引字段是整型类型，查询条件中的输入参数即使字符串，是不会导致索引失效，还是可以走索引扫描，例如 select * from t_user where id = '1'; 。原因是 MySQL 在遇到字符串和数字比较的时候，会自动把字符串转为数字，然后再进行比较。

OR 前后存在非索引的列：如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。

不等于比较（!=，<>，NOT LIKE）：当查询条件为字符串时，使用 <> 或 != 作为条件查询有可能会导致索引失效。Mysql 优化器会自动判断，当查询结果集占比比较小时会走索引，占比比较大时不会走索引。

IS NOT NULL：查询条件使用 is null 时正常走索引，使用 is not null 时，不走索引。

NOT IN 和 NOT EXISTS：查询条件使用 not in 时，如果是主键则走索引，如果是普通索引，则索引失效。查询条件使用 not exists 时，索引失效。

ORDER BY：当查询条件涉及到 order by、limit 等条件时，是否走索引情况比较复杂，而且与 Mysql 版本有关，通常普通索引，如果未使用 limit，则不会走索引。 order by 多个索引字段时，可能不会走索引。其他情况，建议在使用时进行 expain 验证。

3、SQL优化原则

执行前先用 explain 查看执行计划。

尽量确保使用索引。使用覆盖索引和索引下推都作为创建索引的原则，避免索引失效。

仅列出需要查询的字段。

使用 JOIN 查询，小表驱动大表原则。LEFT JOIN 左连接，左边为驱动表，右边为被驱动表；RIGHT JOIN 右连接，右边为驱动表，左边为被驱动表；INNER JOIN 内连接，mysql会选择数据量比较小的表作为驱动表，大表作为被驱动表。驱动表结果应该尽量小，然后去遍历被驱动的大表表的次数也会少，可以减少磁盘IO，提高性能。

尽量避免使用子查询。执行子查询时，MYSQL需要创建临时表，查询完毕后再删除这些临时表，多了一个创建和销毁临时表的过程，性能会很差。

4、建索引的原则

如果是查询多字段，用好联合索引，尽量发挥覆盖索引和索引下推的优势。

不为离散度低的列创建索引。否则会在二级索引中搜索到大量的重复数据，然后进行大量回表操作。

不为频繁更新的列创建索引。因为可能涉及到数据页分裂的情况，会影响性能。

只为用于搜索、排序或分组的列创建索引。只为出现在 WHERE 子句中的列或者出现在 ORDER BY 和 GROUP BY 子句中的列创建索引即可。仅出现在查询列表中的列不需要创建索引。

对过长的字段，建立前缀索引。可以为该列的前几个字符创建索引，也就是在二级索引的记录中只会保留字符串的前几个字符。比如我们可以为 phone 列创建索引，索引只保留手机号的前3位：ALTER TABLE user_innodb ADD INDEX IDX_PHONE_3 (phone(3));