sql优化

Explain 工具介绍

1.id

代表sql中查询语句的序列号，序列号越大则执行的优先级越高，序号一样谁在前谁先执行。id为null则最后执行。

2.select_type

查询类型，表示当前被分析的sql语句的查询的复杂度。这个字段有多个值。

SIMPLE：表示简单查询。

PRIMARY：表示复杂查询中的最外层的select查询语句。

SUBQUERY：表是子查询语句  跟在select 关键字后面的select查询语句；

derived： 派生查询，跟在一个select查询语句的from关键字后面的select查询语句  例如：

set session optimizer_switch='derived_merge=off'; -- 关闭mysql5.7新特性对衍生表的合并优化 explain select (select 1 from actor where id =1) from (SELECT * from film where id=1) ac;

3.table 

表示当前访问的表的名称。

当from中有子查询时，table字段显示的是 N为derived的id的值。

4.partitions

返回的是数据分区的信息，不常用 这里不做分析。

5.type

这个字段决定mysql如何查找表中的数据，查找数据记录的大概范围。这个字段的所有值表示的从最优到最差依次为：

system > const > eq_ref > ref > range > index > all;

一般来说我们优化到range就可以了 最好到ref。

null：type字段的值如果为null，那么表示当前的查询语句不需要访问表，只需要从索引树中就可以获取我们需要的数据；

一般如果是主键索引的话 ，查询主键字段或者唯一索引的话 查询主键字段 type字段的值就为null。

system/const:用户主键索引或者唯一索引查询时，只能匹配1条数据  一般可以对sql查询语句优化成一个常量，那么type一般就是system或者const，system是const的一个特例。

eq_ref:在进行连接查询时，例如left join 时，如果是使用主键索引或者唯一索引连接查询 ，结果返回一条数据，则type的值为一般为eq_ref。

那么，反过来在看一下

film表和film_actor中间表关联查询，根据film电影表中的主键id和film_actor表中的film_id字段进行关联的。电影表中的主键id在film_actor中并不是唯一的。所以：

对于film 需要确定查询id  从索引树中就可以获取值  所以是index。对于film_actor就是全表扫描了。

ref: 相比较eq_ref,不使用主键索引或者唯一索引，使用的是普通索引或者唯一索引的部分前缀，索引与一个值进行比较后可能获取到多个符合条件的行，不在是唯一的行了。

简单查询，name是普通索引

复杂查询，film_actor   有联合索引 idx_film_actor_id('film_id','actor_id') 这里使用了联合索引的左前缀 film_id

range:通常使用范围查找，例如between，in，<,>,>=等使用索引进行范围检索。

index:扫描索引树就能获取到的数据，一般是扫描二级索引，并且不会从根节点扫描，一般直接扫描二级索引的叶子节点，速度比较慢。因为二级索引叶子节点不保存表中其他字段数据  只保存主键，所以二级索引还是比较小的，扫描速度相比All还是很快的。这里用到了覆盖索引，什么是覆盖索引：可以直接遍历索引树就能获取数据叫做覆盖索引。这里遍历name索引树就可以获取到主键id的值就是覆盖索引。

ALL:这是一种效率最低的type，需要扫描主键索引树的叶子节点，获取数据是表中其他列的数据，即全表扫描。

和index有什么区别呢？

拿film电影表举例：添加一个remark影评字段，film表结构如下：

CREATE TABLE `film` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(10) DEFAULT NULL, `remark` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_name` (`name`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

表中建了两个索引：id 主键索引  idx_name(name) 二级索引。

那么：

explain select id,name from film ;

上述sql查询id，name两个字段，分析mysql索引数据结构，以及mysql优化后一般扫描二级索引，索引会扫描idx_name索引树的叶子节点，那么根据B+Tree树的结构，叶子节点保存的是name字段的索引值 和 data数据（主键id）。而正好我们只需要查询id和name两个字段，我们查询的字段被索引（二级索引）给覆盖了 这就是覆盖索引，因此 type的类型就是index。

再来：

explain select remark from film ;

比较上一个sql，这个sql只查询了一个字段：remark，经过上面分析，这个字段是不在idx_name索引树的叶子节点上的，所以mysql不会在扫描idx_name索引树了，直接扫描主键索引的叶子节点，即进行全表扫描，这个时候type类型为ALL。

6.possible_keys

这个字段显示的是sql在查询时可能使用到的索引，但是不一定真的使用，只是一种可能。

如果在进行explain分析sql时，发现这一列有值，但是key列为null，因为mysql觉得可能会使用索引，但是又因为表中的数据很少，使用索引反而没有全表扫描效率高，那么mysql就不会使用索引查找，这种情况是可能发生的。

如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查 where 子句看是否可以创造一个适当的索引来提

高查询性能，然后用 explain 查看效果。

7.key

sql执行中真正用到的索引字段。

8.key_len

用到的索引字段的长度，通过这个字段可以显示具体使用到了索引字段中的哪些列（主要针对联合索引）：计算公式如下

• 字符串  

         char(n)：n字节长度  

         varchar(n)：如果是utf-8，则长度 3n + 2 字节，加的2字节用来存储字符串长度

• 数值类型 

         tinyint：1字节  

         smallint：2字节  

         int：4字节  

         bigint：8字节

• 时间类型

           date：3字节  

           timestamp：4字节  

           datetime：8字节

• 如果字段允许为 NULL，需要1字节记录是否为 NULL

索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索

引。

9.ref

表示那些列或常量被用于查找索引列上的值

10.rows

表示在查询过程中检索了多少列  但是并不一定就是返回这么多列数据。

11.Extra

展示一些额外信息。

二、索引实践

以下实践以employees表为例。一个主键索引  一个联合索引

CREATE TABLE `employees` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名', `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄', `position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位', `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间', PRIMARY KEY (`id`), KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 COMMENT='员工记录表';

1.联合索引最左列原则

例1：

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei';

使用联合索引中的name字段索引。

例2：

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22;

使用联合索引中的name age position字段索引。

例4：

EXPLAIN SELECT * FROM employees WHERE age = 30 AND position = 'dev';

仅仅使用了联合索引中的name字段，因为中间age字段断了，所以position字段索引并未用到。解释一下：

索引是一个有序的数据结构，也就是说使用索引时，需要索引保证有序，那么在联合索引中，是先按照name排序，name相同情况下，在按照age排序，age相同情况下 在按照position排序，因此如果age不确定情况下，position是无序的，所以即使你是用position查询了 也无法走索引的。这就是最左列原则并且中间不能断。

例5：

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age > 22 AND position ='manager';

这个使用了联合索引中的name和age字段，没有使用position，为什么？原理其实和上面差不多。分析一波：

首先按照顺序 name->age->position,name已经确定了等于LiLei，那么age就是有序的了，所以检索age>22的就很容易了 因为age有序。但是age值其实是不确定的，age可以是23,24,25...等等，所以在age不确定情况下 position是无序的 因此是不走position索引字段的。

2.全值匹配

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22 AND position ='manager';

3.不建议在索引列上做任何操作，否则索引会失效转而全表扫描

-- 查询name的最左变的两个字符为Li的行EXPLAIN SELECT * FROM employees WHERE LEFT(name,2) = 'Li';

4.尽量使用覆盖索引 不需要再回表查询了  效率较高

5.再试用!=或<>不等于查询时，会导致索引失效。

EXPLAIN SELECT * FROM employees WHERE name != 'LiLei';

6.尽量不要使用 ‘or’，‘in’操作，在某些情况下也会导致索引失效。

第一种情况：当表中只有两条数据  数据量很少的时候

explain SELECT * from employees where name in ('LiLei','abc');

使用in查询，没有走索引，进行了全表扫描，为什么？分析一波：

首先 如果使用索引的话，mysql大概会怎么操作？ 应该先在name索引树中定位到name=LiLei这个节点（最少一次I/O），然后定位到name=abc这个节点（一次I/O），然后分别拿到主键id，在去主键索引树上扫描定位（最少又要两次I/O），总共4次I/O。

如果不使用索引，直接全表扫描，那么直接扫描主键索引树的叶子节点 只需要两次I/O即可（因为只有两条数据），所以mysql评估全表扫描效率可能会更高，就不会在走索引了。

第二种情况：当表中数据量很多，例如7条数据

同样的sql查询

explain SELECT * from employees where name in ('LiLei','abc');

结果：走了索引

为什么会出现这种情况？再来分析一波：

首先走索引的话  大概需要4次I/O  上面已经分析过了。

那么不走索引的话  需要全表扫描  最坏的情况需要扫描7次，进行7次I/O,mysql评估一下发现全表扫描的效率可能是低于走索引的，所以就走了索引。

第三种情况：数据还是7条，但是我in查询时条件有8个

explain SELECT * from employees where name in ('LiLei','abc','cde','asc','ssw','2dff','wsa','sda');

看下结果:

 为啥又不走索引了呢？经过上面的两波强势分析，这里也很容知道原因，就不过多的赘述了。or查询的情况类似。

7.is null ，is not null一般情况下也无法使用索引

8.是用字符串查询  不见引号  索引也会失效

explain SELECT * from employees where name = 1324;

9.针对范围查找的不走索引的优化

首先看个例子：

-- 先给age加一个独立索引ALTER TABLE `employees` ADD INDEX `idx_age` (`age`) USING BTREE ;-- 查询age 在1到2000分为内的数据explain SELECT * from employees where age >1 and age < 2000 ;

结果：

显然并没有走索引  为什么？再来强势分析一波：

首先，我们脑海中要有一个age的索引树：

我们要找到1-2000的数据，那么在这棵树书上怎么定位？

如果我来定位的话 我会定位一个age=2在树上的位置 在定位一个age=1999在树上的位置，然后从age=2的节点开始取右边的节点，一直取下去 直到age=1999为止，但是我们表总只有7条数据，mysql觉得这样操作还没有全表扫描快，毕竟一共才几条数据全表扫描反而更快些，所以mysql就去全表扫描了。

怎么优化呢？

explain SELECT * from employees where age >1 and age < 1000 ;explain SELECT * from employees where age >1001 and age < 2000 ;

把一个大的范围拆成多个小的范围  可以利用索引查询。

10.like查询建议使用xxx%方式匹配，%xxx或者%xxx%索引失效

EXPLAIN SELECT * FROM employees WHERE name like '%Lei';EXPLAIN SELECT * FROM employees WHERE name like '%Lei%';

 结果：全表扫描

思考下在索引树上name的排序规则，先按照第一个字符比较然后第二个字符依次向后比较，如果是用%xxx，字符串前面的字符不确定，怎么在树上定位呢？显然没法按照顺序定位，只能一个一个遍历比较  所以不会走索引。%xxx%也一样。

在看下面这个例子

EXPLAIN SELECT * FROM employees WHERE name like 'Lei%'

结果：走了索引

其实Lei%匹配相当于范围查询，只要name的值的前三个字符为Lei符合条件，等价于查找前三个字符=Lei的字符串，这个在索引树上是有序的，当然可以使用索引定位。

总结：

1.使用explain关键字，可以分析出sql的性能瓶颈并加以优化

2.了解explain返回的各字段值代表的意义，结合索引数据结构有助于我们对sql的查询效率的分析和优化

3.列举部分可能不会进行索引检索的情况，例如 !=,<>,is null,like的某些情况，or或者in的某些情况，字符串不加引号等

4.对某些不走索引查询的情况作了一些比较详细的分析