MySQL索引

索引相关

分析sql的执行计划

**注意：**使用的mysql版本为 5.6.39

准备工作

1）创建表并查看表结构

create table index_test...
DESC index_test

表结构如下：

2）给表的列创建索引

ALTER TABLE index_test ADD INDEX b_d_c(business_division_code);

3）使用explain关键字分析查询语句
explain+查询语句

示例：
EXPLAIN SELECT * FROM index_test WHERE business_division_code='1001'

显示结果：

索引结构

以一张表为例：

假设有个t表（id primary key,name key,sex,flag）；id为主键索引，name为普通索引

假设表中有四条记录：

id	name	sex	flag
1	sj	m	A
3	zs	m	A
5	ls	m	A
9	ww	f	B
主键索引结构图：
普通索引结构图
简单查询语句的查询过程图：

select * from t where name='lisi'

结果分析

很明显进行了回表操作（在下面的示例中会详细分析）

执行计划的关键字

explain分析出来的查询计划的关键字解释
所有的示例都是用index_test表为原型

1.id：id越大越先执行，如果id相同就从上往下依次执行；（标识一条查询语句中执行的先后顺序，因为一条查询语句中可能包含很多子查询）

2.select_type：代表查询类型，是主查询（最外层查询），还是子查询（内层查询），会有好几种类型

3.table：查询的表名称

4.type：表示查询性能的好坏（这个最重要）有多种类型
ALL<index<range<ref<eq_ref<const<system,越往后查询范围越小，查询效率越高，这几个是常用的，中间还有其他的，因为不常用，就没写。
（1）ALL：代表全表扫描才拿到结果，跟有无where字句无关。举个例子，如果where字句后面用到了索引那么肯定不会是ALL，如果where子句后面没有用到索引，就会是ALL
（2）range：没有模拟出来。。。
（3）ref：等值查询，where子句中用到了索引（非主键索引），且为等值查询（就是说查询条件为等号‘=’）；
（4）eq_ref：没有模拟出来。。。
（5）const：等值查询，where子句中用到了索引（主键索引），且为等值查询（就是说查询条件为等号‘=’）；

5.possible_keys：MySQL推测可能用到的索引；mysql推测可用用到的索引不一定用到；mysql推测没有用到索引，实际查询中也可能用到索引。
总的来说，这个值（结果）没啥用

6.key：查询过程中实际用到的索引，这个才有用

7.key_len：实际用到的索引的长度的字节数，这个跟索引名或者字段名的长度无关，而是与编码和建立索引字段的类型有关
例如：varchar(10)、varchar(30)是不一样的
（1）以uft8编码为例；可以看到index_test表的那个执行计划。business_division_code字段为varchar(32)，就是代表了可变字符串长度的32个字符。
图中key_len为98，urf8的一个字符占用3个字节；32个字节就占用 32*3=96个字节。varchar代表可变长度，会占用两个字节 96+2=98，也就是key_len的长度；
有一点需要注意，该表中business_division_code字段是不允许为null的，如果允许为null的化，key_len还要在加上一个字节也就是 98+1 = 99
总结：如果是可变长度例如varchar就要加上2个字节；如果允许为null要加上1个字节。
（2）特殊情况：如果一个索引为联合索引（多个字段组成的索引），key_len就是实际用到的索引的长度，假设有三个字段组成的联合索引长度为12，
但是实际查询过程中只用到了两个字段的索引，假设两个字段的索引长度为8，也就是key_len的实际值为8

8.ref：显示查询条件（自己的理解）

9.rows：表示使用该条语句进行查询的时候扫描的表中的函数（就是扫描了多少条数据才拿到查询结果），并不是显示查询出的结果有多少条数据

10.Extra：包含MySQL解决查询的详细信息
（1）Using where：查询的时候使用了where条件过滤
（2）Using index：查询的时候使用到了索引，并且索引覆盖，也就是只进行了一次操作，没有进行回表就查询出了效率，这样查询效率是最高的
示例SQL：EXPLAIN SELECT business_division_code,id FROM index_test WHERE business_division_code ='1000'
解释：因为主键的值在普通索引的叶子节点，而且business_division_code本身有索引，又是查询条件
使用场景：查询条件为普通索引或者主键索引，查询内容为主键、where中的普通索引；
查询条件为联合索引中的某个组成字段，查询结果为该字段或者组成该联合索引的所有字段
（3）Using index condition：查询的时候使用到了索引，但是没有索引覆盖，也就是进行两次查询才查询到结果；
第一次根据普通索引查找到主键的值，第二次，根据主键的值在查询所需的数据（回表操作）
（4）Using temporary：表示MySQL需要使用临时表来存储结果集；join、order by不会产生临时表，union、group by语句会产生临时表。
（5）Using filesort：包含order by但是order by的字段没有索引，会产生filesort（没测出来，数据库版本有问题？）
（6）Using join buffer：连接条件没有索引，并且需要连接缓冲区存储中间结果（临时表？），出现这种情况，根据连接条件来建立索引？（没测出来）

索引相关总结

(1）如果在查询语句中使用的是非联合索引，即where字句后面条件是单个字段的查询，只会使用到一个索引，
使用的这个索引与where字句字段使用的先后顺序无关（因为mysql会自动优化）；如下示例
explain SELECT business_division_code,id FROM index_test WHERE id=1 and business_division_code ='1000'
explain SELECT business_division_code,id FROM index_test WHERE business_division_code ='1000' and id=1
这俩查询语句都是一样的效果，虽然id、跟business_division_code 都建立有索引，但是查询的时候只会使用到id的索引（因为id为主键，mysql会自动优化）；
总结，如果where子句后面有多个查询条件，只会使用一个索引（查询条件有没有like都是相同的结果）；如果一个普通索引，一个联合索引，也同样遵循上面的规律。