MySQL执行计划和索引实战之Explain

一、Explain关键字详解

explain关键字用于分析sql执行计划，只需将explain关键字放置到需要分析的sql前面，并执行，观察执行结果，分析sql的性能瓶颈，从而进行优化。一般通过explain可以分析出以下结果：

1. 表的读取顺序；
2. 数据读取操作的操作类型
3. 显示sql语句走了哪个索引字段
4. 如果有联合索引可以根据key_len算出走了索引的哪些列
5. 显示使用索引的级别
6. 表之间的引用
   使用方式：explain+sql语句
   EXPLAIN SELECT * FROM user WHERE id=1
   执行计划包含的信息：
   
   1、id、select_type、table列含义
   首先我准备了3张简单的测试表
   test_innodb表（innodb存储引擎，且name字段有索引，root和password联合索引）：
   

test_myisam表（myisam存储引擎，主键索引，其他无索引）：

test_innodb_myisam（innodb存储引擎，主键索引，其他无索引）

先执行一条简单的sql查询语句：
Explain select * from test_innodb where id =1

先看select_type字段，共有4中类型，上面执行结果为SIMPLE类型，表示查询sql为一条较为简单的查询语句。
下面执行一条相对复杂的sql语句：
EXPLAIN SELECT (SELECT 1 FROM test_innodb WHERE id=1) FROM (SELECT * FROM test_myisam WHERE id=1) a;

select_type字段的另外3个类型：
Primary：复杂查询中最外层的select
Derived：包含在from中的子查询语句，(SELECT * FROM test_myisam WHERE id=1) a ，MySQL会将结果存放在一个临时表中，也叫派生表
Subquery：包含在select中的子查询语句(不在from子句中) ，(SELECT 1 FROM test_innodb WHERE id=1) 其中id字段越大执行等级越高。
id字段表示涉及到的查询表的先后顺序，1表示最先，2表示其次，3表示最后，以此类推；
table表示表名称。

2、type、key列（重要）

2.1、type列表示关联类型或访问类型，MySQL决定如何查找表中的行，查找数据记录的大概范围，依次从最优到最差分别为：system > const > eq_ref > ref > range > index > ALL
一般来说，需要保证sql达到range级别，最好达到ref。

2.1.1、执行 EXPLAIN SELECT max(id) FROM test_innodb

type列为NULL，MySQL能够保证在优化阶段查询语句，在执行阶段用不着再访问表或索引。例如在索引中选取最大值，可以单独查询索引来完成，不需要在执行时访问表。

2.1.2、执行 EXPLAIN SELECT * FROM (select * FROM test_innodb WHERE id = 1) a

type列分别为system和const，system时const的特里，查询表中只有一条记录，速度相当快，最优。

2.1.3、执行 EXPLAIN SELECT * FROM test_innodb a INNER JOIN test_innodb_myisam b ON a.id=b.innodb_id

id都为1，但实际执行时，sql先执行from语句，再根据笛卡尔积把test_innodb_myisam合并到一起。其中a表走了索引，type类型为eq_ref，说明a.id走了主键索引PRIMAR；b.innodb_id并没有走索引。eq_ref仅次于const等级，不需要在进行优化

2.1.4、执行 EXPLAIN SELECT * FROM test_innodb WHERE name=‘a’

type列为ref，相比eq_ref，没有使用唯一索引，而是使用普通索引name,不唯一，查询结果可能有多个。（可能是使用了联合索引的前缀查询）

2.1.5、执行 EXPLAIN SELECT * FROM test_innodb WHERE id>2

type列为range，索引查询范围时，type列类型为range

2.1.6、执行 EXPLAIN SELECT * FROM test_innodb

type列为index，扫描全索引就能拿到结果，一般扫描某个二级索引（test_innodb表name字段有索引），这种扫描不会从索引根节点开始查找，而是直接对二级索引的叶子节点遍历和扫描，速度还是比较慢的，这种查询一般为使用覆盖索引，二级索引一般比较小，这种通常比ALL快一些

问题：分析结果走了name索引，为什么不走主键索引？？？
这条sql需要遍历索引的所有叶子节点，name是二级索引，叶子节点存储的只是主键，占空间较小，而主键索引叶子节点存储了索引所在行的所有数据，占用空间较大，那么加载到内存当然越小越好，所以走了那么二级索引。

2.1.7、执行 EXPLAIN SELECT * FROM test_innodb_myisam

type列为ALL，扫描全索引，一般扫描主键索引（test_innodb_myisam只有主键索引），不会从根节点开查找，而是直接对主键索引的叶子节点进行扫描遍历，速度很慢

3、key列
显示走了哪个字段的索引

4、key_len列
这一列显示了MySQL再索引里使用的字节数，通过值可以计算出具体使用了索引中的哪些列。比如test_innodb表的联合索引root和password两列组成，且每个字段都是int类型，每个占4字节，通过key_len=4可以推断出查询使用了第一列：root列来执行索引查找EXPLAIN select * FROM test_innodb where root=2

key_len计算规则如下：
字符串：

• Char(n)：n字节长度
• archar(n)：如果是utf-8，则长度是3n+2字节，加2的字节用来存储字符串长度
  数值类型：
• Int：4字节
• Bigint：8字节
  时间类型：
• Date：3字节
• Timestamp：4字节
• atetime：8字节
  如果字段允许为null，那么需要1字节来存储是否为null。
  索引最大长度是768字节，当字符串过长时，MySQL会做一个类似左前缀索引的处理，将前半部分的字符串提取出来做索引。

二、联合索引实战

表employees 有name、age、position三个字段，并组合成联合索引。


联合索引遵循最左前缀原则：
按照name、age、position顺序进行给排序，如果name字段值相同，再按照age字段值排序，依次类推；如果name字段值不相同，那就按照name值大小排序即可(不在考虑age、position)。

1、问题：下面3条语句哪一个走索引了？
Explain Select * from employees where name=’Bill’ and age=30
Explain Select * from employees where age=30 and position=dev
Explain Select * from employees where position=dev

分析：只有第一条走了索引，根据联合索引最左前缀原理，第一条符合；比如第二条查询age=30 and position=dev，当查询根节点时，索引根据name字段值进行了排序，age、position并没有排序，那么查询age=30需要遍历所有，相当于没有索引；第三条同理。

2、问题：下面2条sql中where后面字段是否都走了索引？
EXPLAIN SELECT * FROM employees WHERE name=‘zhangsan’ AND age > 10；
EXPLAIN SELECT * FROM employees WHERE name=‘zhangsan’ AND age > 10 AND position = ‘zhangsan’

分析：
EXPLAIN SELECT * FROM employees WHERE name=‘zhangsan’ AND age > 10；

EXPLAIN SELECT * FROM employees WHERE name=‘zhangsan’ AND age > 10 AND position = ‘zhangsan’

根据执行计划中key列，可知2条sql都走了联合索引，但根据key_len长度等于392，说明第2条sql只走了name和age索引，没有走position字段索引（key_len长度可以根据上文的规则自己计算）。
原因是索引数据结构首先根据name字段值排序，在name值相同情况下再根据age排序（否则只根据name排序，age、posiotion不再需要排序）; 如果age相同再根据position排序（否则根据age排序，position不需要再排序）。第二条Sql中 name=‘zhangsan’ AND age > 10 AND position = ‘zhangsan’ 三个字段，name=‘zhangsan’ AND age > 10会走索引，因为定位到了name='zhangsan’这个值，根据以上原则可知，age是排好序的，那么position字段就不需用排序，所以 position = 'zhangsan’放在age>10后面就不会走索引了（因为position无序，需要遍历所有）

联合索引总结：
设a、b、c组合联合索引，则遵循下表规则

三、索引失效场景
1、根据实际索引字段在sql中的使用情况进行分析，可用explain关键字放到sql前面进行具体分析
2、索引必定失效场景：
2.1、联合索引未使用最左列字段查询（未遵循最左前列原则），索引失效。
2.2、where条件后面对索引字段使用函数，比如mysql中使用date(create_time)=‘2020-09-15’函数对日期格式化查询，此时create_time索引失效。
2.3、where条件使用or连接，那么or前后字段必须全部加索引，否则失效。
2.4、like模糊查询时 ‘%abc’失效，‘abc%’有效。
2.5、where条件后面索引字段不能进行数学运算 where goods_price=goods_price+1。
2.6、存在索引列的数据类型有隐形转换，索引无效，比如列的数据类型是字符串，那条件入参时要带上引号’’
select * from employees where name=‘zhangsan’ 索引有效
select * from employees where name=zhangsan 索引无效

以上为工作学习所记录心得，如果片面的地方或不合理的，欢迎批评指正~~~