explain的sql优化详解

朱雀 2024-04-07 15:31 42阅读 0赞

## 一、Explain 工具介绍 ##

使用Explain可以查看sql的性能瓶颈信息，并根据结果进行sql的相关优化。在select 语句前加上explain关键字，执行的时候并不会真正执行[sql语句][sql]，而是返回sql查询语句对应的执行计划信息。

当然如果select语句的from后面有一个子查询的话，就会执行子查询了并把结果放到一个临时表中。

有三张表：

-- 演员表
         
         
          
        
        
         
        
        
        
         
         
         
          
        
        
         
        
        
         
         
         
           
          
          
           CREATE 
          
          
           TABLE `actor` (
         
         
          
        
        
         
        
        
        
         
         
         
          
        
        
         
        
        
         
         
         
          
           
          
           `id` 
          
          
           INT ( 
          
          
           11 ) 
          
          
           NOT 
          
          
           NULL,
         
         
          
        
        
         
        
        
        
         
         
         
          
        
        
         
        
        
         
         
         
          
           
          
           `name` 
          
          
           VARCHAR ( 
          
          
           45 ) 
          
          
           DEFAULT 
          
          
           NULL,
         
         
          
        
        
         
        
        
        
         
         
         
          
        
        
         
        
        
         
         
         
          
           
          
           `update_time` datetime 
          
          
           DEFAULT 
          
          
           NULL,
         
         
          
        
        
         
        
        
        
         
         
         
          
        
        
         
        
        
         
         
         
           
          
          
           PRIMARY KEY ( `id` )  
         
         
          
        
        
         
        
        
        
         
         
         
          
        
        
         
        
        
         
         
         
          
           
          
          ) ENGINE 
          
          
           = INNODB 
          
          
           DEFAULT CHARSET 
          
          
           = utf8;
         
         
          
        
        
         
        
        
        
         
         
         
          
        
        
         
        
        
         
         
         
           
         
         
          
        
        
         
        
        
        
         
         
         
          
        
        
         
        
        
         
         
         
          
          
          
           INSERT 
          
          
           INTO `actor` (`id`, `name`, `update_time`) 
          
          
           VALUES (
          
          
           1,
          
          
           'a',
          
          
           '2017‐12‐22 15:27:18'), (
          
          
           2,
          
          
           'b',
          
          
           '20 17‐12‐22 15:27:18'), (
          
          
           3,
          
          
           'c',
          
          
           '2017‐12‐22 15:27:18');
         
         
          
        
        
         
        
        
        
         
         
         
          
        
        
         
        
        
         
         
         
           
         
         
          
        
        
         
        
        
        
         
         
         
          
        
        
         
        
        
         
         
         
          
          
          
           -- 电影表
         
         
          
        
        
         
        
        
        
         
         
         
          
        
        
         
        
        
         
         
         
          
          
          
           CREATE 
          
          
           TABLE `film` (
         
         
          
        
        
         
        
        
        
         
         
         
          
        
        
         
        
        
         
         
         
          
           
          
           `id` 
          
          
           INT ( 
          
          
           11 ) 
          
          
           NOT 
          
          
           NULL AUTO_INCREMENT,
         
         
          
        
        
         
        
        
        
         
         
         
          
        
        
         
        
        
         
         
         
          
           
          
           `name` 
          
          
           VARCHAR ( 
          
          
           10 ) 
          
          
           DEFAULT 
          
          
           NULL,
         
         
          
        
        
         
        
        
        
         
         
         
          
        
        
         
        
        
         
         
         
           
          
          
           PRIMARY KEY ( `id` ),
         
         
          
        
        
         
        
        
        
         
         
         
          
        
        
         
        
        
         
         
         
          
           
          
           KEY `idx_name` ( `name` )  
         
         
          
        
        
         
        
        
        
         
         
         
          
        
        
         
        
        
         
         
         
          
           
          
          ) ENGINE 
          
          
           = INNODB 
          
          
           DEFAULT CHARSET 
          
          
           = utf8;
         
         
          
        
        
         
        
        
        
         
         
         
          
        
        
         
        
        
         
         
         
           
         
         
          
        
        
         
        
        
        
         
         
         
          
        
        
         
        
        
         
         
         
          
          
          
           INSERT 
          
          
           INTO `film` (`id`, `name`) 
          
          
           VALUES (
          
          
           3,
          
          
           'film0'),(
          
          
           1,
          
          
           'film1'),(
          
          
           2,
          
          
           'film2');
         
         
          
        
        
         
        
        
        
         
         
         
          
        
        
         
        
        
         
         
         
           
         
         
          
        
        
         
        
        
        
         
         
         
          
        
        
         
        
        
         
         
         
          
          
          
           -- 演员和电影中间表
         
         
          
        
        
         
        
        
        
         
         
         
          
        
        
         
        
        
         
         
         
          
          
          
           CREATE 
          
          
           TABLE `film_actor` (
         
         
          
        
        
         
        
        
        
         
         
         
          
        
        
         
        
        
         
         
         
          
           
          
           `id` 
          
          
           INT ( 
          
          
           11 ) 
          
          
           NOT 
          
          
           NULL,
         
         
          
        
        
         
        
        
        
         
         
         
          
        
        
         
        
        
         
         
         
          
           
          
           `film_id` 
          
          
           INT ( 
          
          
           11 ) 
          
          
           NOT 
          
          
           NULL,
         
         
          
        
        
         
        
        
        
         
         
         
          
        
        
         
        
        
         
         
         
          
           
          
           `actor_id` 
          
          
           INT ( 
          
          
           11 ) 
          
          
           NOT 
          
          
           NULL,
         
         
          
        
        
         
        
        
        
         
         
         
          
        
        
         
        
        
         
         
         
          
           
          
           `remark` 
          
          
           VARCHAR ( 
          
          
           255 ) 
          
          
           DEFAULT 
          
          
           NULL,
         
         
          
        
        
         
        
        
        
         
         
         
          
        
        
         
        
        
         
         
         
           
          
          
           PRIMARY KEY ( `id` ),
         
         
          
        
        
         
        
        
        
         
         
         
          
        
        
         
        
        
         
         
         
          
           
          
           KEY `idx_film_actor_id` ( `film_id`, `actor_id` )  
         
         
          
        
        
         
        
        
        
         
         
         
          
        
        
         
        
        
         
         
         
          
           
          
          ) ENGINE 
          
          
           = INNODB 
          
          
           DEFAULT CHARSET 
          
          
           = utf8;
         
         
          
        
        
         
        
        
        
         
         
         
          
        
        
         
        
        
         
         
         
           
         
         
          
        
        
         
        
        
        
         
         
         
          
        
        
         
        
        
         
         
         
          
          
          
           INSERT 
          
          
           INTO `film_actor` (`id`, `film_id`, `actor_id`) 
          
          
           VALUES (
          
          
           1,
          
          
           1,
          
          
           1),(
          
          
           2,
          
          
           1,
          
          
           2),(
          
          
           3,
          
          
           2,
          
          
           1);

执行explain select \* from actor;

结果：

![2020072115112764.png][]

根据返回的信息可以分析sql的性能瓶颈从而进行优化。

下面分析其中每个字段对应的含义。

### 1.id ###

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

### 2.select\_type ###

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

**SIMPLE**：表示简单查询。

explain select * from actor;

![20200721151548819.png][]

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

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

explain select (select 1 from film where id =1) from actor;

![20200721151908914.png][]

**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;

![20200721152643694.png][]

### 3.table ###

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

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

### 4.partitions ###

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

### 5.type ###

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

**system** > **const** > **eq\_ref** > **ref** > **range** > **index** > **all;**

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

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

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

explain select id from actor where id =1;

![20200721154225591.png][]

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

explain select * from (select * from film where id = 1) tmp;

![20200721154814150.png][]

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

explain 
          
          
           SELECT 
          
          
           * 
          
          
           from film_actor 
          
          
           left 
          
          
           join film 
          
          
           on film.id 
          
          
           = film_actor.film_id;

分析下这个sql，首先我们需要查询的是film\_actor中间表 且这个表是与film表进行主键关联的，索引film\_actor表中的film\_id字段在film表中只有一个唯一值，所以：

![20200721155517576.png][]

那么，反过来在看一下

explain SELECT * from film left join film_actor on film_actor.film_id = film.id;

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

![20200721155840746.png][]

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

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

简单查询，name是普通索引

explain select * from film where name = 'film1';

![2020072116020694.png][]

复杂查询，film\_actor 有联合索引 idx\_film\_actor\_id('film\_id','actor\_id') 这里使用了联合索引的左前缀 film\_id

explain select fa.film_id from film f left join film_actor fa on fa.film_id = f.id;

![20200721161714555.png][]

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

explain select * from film where id >2;

![20200722094813863.png][]

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

explain select id from film ;

![20200722095858788.png][]

**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索引数据结构，以及my[sql优化][sql 1]后一般扫描二级索引，索引会扫描idx\_name索引树的叶子节点，那么根据B+Tree树的结构，叶子节点保存的是name字段的索引值 和 data数据（主键id）。而正好我们只需要查询id和name两个字段，我们查询的字段被索引（二级索引）给覆盖了 这就是覆盖索引，因此 type的类型就是index。

![20200722101144850.png][]

再来：

explain select remark from film ;

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

![20200722101434532.png][]

### 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和gae字段索引。

例3：

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

使用联合索引中的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';

![20200722105037178.png][]

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

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

![20200722105540337.png][]

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

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

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

![20200722105841237.png][]

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

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

![2020072211023065.png][]

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

![20200722110126979.png][]

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

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

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

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

![20200722110851418.png][]

同样的sql查询

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

结果：走了索引

![20200722110142927.png][]

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

首先走索引的话 大概需要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');

看下结果:

![20200722111406534.png][]

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

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

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

explain SELECT * from employees where name = 1324;

![20200722111730794.png][]

### 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 ;

结果：

![20200722112505580.png][]

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

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2J1Z05vbmVOdWxs_size_16_color_FFFFFF_t_70][]

我们要找到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 ;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2J1Z05vbmVOdWxs_size_16_color_FFFFFF_t_70 1][]

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

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

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

结果：全表扫描![2020072211370291.png][]

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

在看下面这个例子

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

结果：走了索引

![2020072211401858.png][]

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

总结：

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

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

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

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

[sql]: https://so.csdn.net/so/search?q=sql%E8%AF%AD%E5%8F%A5&spm=1001.2101.3001.7020
[2020072115112764.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/03e7687389eb4bf4b33a6aefc6cf63eb.png
[20200721151548819.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/ccb97b7196c148ce902d7864e2c289c8.png
[20200721151908914.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/58a4ba59d3454a238a41d2238c583089.png
[20200721152643694.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/22a6b66d5d7f439dbce9421063aa0be8.png
[20200721154225591.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/813f743243744035afefb012b3c75e27.png
[20200721154814150.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/c3fad105f9a845909cdd0ac289d7903b.png
[20200721155517576.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/c82bd932bc204c4e997addc1a74e4259.png
[20200721155840746.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/d6bbf55de27a477e804d8e15fa6fe159.png
[2020072116020694.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/79f2496c52644ca8938cc172042ec458.png
[20200721161714555.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/d21ba246611f4c1994ae87b4017cab84.png
[20200722094813863.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/c436e5dec28643449113d7589c38b6e8.png
[20200722095858788.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/8ca06dc9560d4201b8f202d5bb81c802.png
[sql 1]: https://so.csdn.net/so/search?q=sql%E4%BC%98%E5%8C%96&spm=1001.2101.3001.7020
[20200722101144850.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/d10009ed71b441f5bbb40f10b7b502d9.png
[20200722101434532.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/5309bc2f10894d7abe505dd6980fd8b3.png
[20200722105037178.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/84c8300aa63c4e32b1729ea69e3a8fd0.png
[20200722105540337.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/69dbfbec90344f9791133e192bc6071f.png
[20200722105841237.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/066ee804dc744678856647d4668671df.png
[2020072211023065.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/70385e53ff8040fca370853ec22534cf.png
[20200722110126979.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/7f3e5a6ad5a441c38c77247625013a86.png
[20200722110851418.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/b9eafdf991bc40baa617f40b3a8d4111.png
[20200722110142927.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/c312d524562f43269f61488529c35db0.png
[20200722111406534.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/cbeb27dc753e4af8b60aaccefdf4e83c.png
[20200722111730794.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/416285276bd543d9b317334beddd4cbd.png
[20200722112505580.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/5ed7d74ea404469b96f321edafa3eabc.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2J1Z05vbmVOdWxs_size_16_color_FFFFFF_t_70]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/38ece14fbbfc482185b4c2a37fc7588e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2J1Z05vbmVOdWxs_size_16_color_FFFFFF_t_70 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/73145182e85442ea84cc2f6647930cb1.png
[2020072211370291.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/15ac42586c22453c8d23df621e18b33b.png
[2020072211401858.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/d93563e6db58465589cb5960f16bf877.png