MySQL Explain

た入场券 2024-02-19 20:47 32阅读 0赞

## [MySQL Explain][] ##

注：本系列随笔如无特殊说明都MySQL版本都为5.7.22。

#### 1.explain的作用 ####

通过explain+sql语句可以知道如下内容：

①表的读取顺序。（对应id）

②数据读取操作的操作类型。（对应select\_type）

③哪些索引可以使用。（对应possible\_keys）

④哪些索引被实际使用。（对应key）

⑤表直接的引用。（对应ref）

⑥每张表有多少行被优化器查询。（对应rows）

#### 2.explain包含的信息 ####

explain使用：explain+sql语句，通过执行explain可以获得sql语句执行的相关信息。

![706569-20180621231820702-1539624119.png][]

下面对explain的表头字段含义进行解释。

注：下图中有些explain表头不包含partitions和filtered字段，是因为有些截图是直接从视频资料中截取的，当并不影响我们的分析。

①id

select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序，该字段通常与table字段搭配来分析。

\#1.id相同，执行顺序从上到下。

![706569-20180621232750696-820361994.png][]

id相同，执行顺序从上到下，搭配table列进行观察可知，执行顺序为t1->t3->t2。

\#2.id不同，如果是子查询，id的序号会递增，id值越大执行优先级越高。

![706569-20180621233147579-359902163.png][]

如果是子查询id的序号会递增，id值越大执行优先级越高，搭配table列可知，执行顺序为t3->t1->t2。

\#3.id相同不同，同时存在。

![706569-20180621233603807-1370399356.png][]

id如果相同，可认为是同一组，执行顺序从上到下。在所有组中，id值越大执行优先级越高。所以执行顺序为t3->derived2(衍生表，也可以说临时表)->t2。

总结：id的值表示select子句或表的执行顺序，id相同，执行顺序从上到下，id不同，值越大的执行优先级越高。

②select\_type

查询的类型，主要用于区别普通查询、联合查询、子查询等复杂的查询。其值主要有六个：

\#1.SIMPLE

简单的select查询，查询中不包含子查询或union查询。

\#2.PRIMARY

查询中若包含任何复杂的子部分，最外层查询为PRIMARY，也就是最后加载的就是PRIMARY。

\#3.SUBQUERY

在select或where列表中包含了子查询，就为被标记为SUBQUERY。

\#4.DERIVED

在from列表中包含的子查询会被标记为DERIVED(衍生)，MySQL会递归执行这些子查询，将结果放在临时表中。

\#5.UNION

若第二个select出现在union后，则被标记为UNION，若union包含在from子句的子查询中，外层select将被标记为DERIVED。

\#6.UNION RESULT

从union表获取结果的select。

![706569-20180622085915119-1856869800.png][]

③table

显示sql操作属于哪张表的。

④partitions

官方定义为The matching partitions（匹配的分区），该字段应该是看table所在的分区吧（不晓得理解错误没）。值为NULL表示表未被分区。

⑤type

表示查询所使用的访问类型，type的值主要有八种，该值表示查询的sql语句好坏，从最好到最差依次为：system>const>eq\_ref>ref>range>index>ALL。

要详细了解type取值的作用，需要用数据说话。创建tb\_emp（员工表）和tb\_dept（部门表）。

a）tb\_emp表。

![复制代码][copycode.gif]

DROP TABLE IF EXISTS `tb_emp`;
    CREATE TABLE `tb_emp` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(20) NOT NULL,
      `deptid` int(11) NOT NULL,
      PRIMARY KEY (`id`),
      KEY `idx_tb_emp_name` (`name`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    INSERT INTO `tb_emp`(name,deptid) VALUES ('jack', '1');
    INSERT INTO `tb_emp`(name,deptid) VALUES ('tom', '1');
    INSERT INTO `tb_emp`(name,deptid) VALUES ('tonny', '1');
    INSERT INTO `tb_emp`(name,deptid) VALUES ('mary', '2');
    INSERT INTO `tb_emp`(name,deptid) VALUES ('rose', '2');
    INSERT INTO `tb_emp`(name,deptid) VALUES ('luffy', '3');
    INSERT INTO `tb_emp`(name,deptid) VALUES ('outman', '4');

![复制代码][copycode.gif]

b）tb\_dept表。

![复制代码][copycode.gif]

DROP TABLE IF EXISTS `tb_dept`;
    CREATE TABLE `tb_dept` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `deptname` varchar(20) NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    INSERT INTO `tb_dept`(deptname) VALUES ('研发');
    INSERT INTO `tb_dept`(deptname) VALUES ('测试');
    INSERT INTO `tb_dept`(deptname) VALUES ('运维');
    INSERT INTO `tb_dept`(deptname) VALUES ('经理');

![复制代码][copycode.gif]

\#1.system

表只有一行记录（等于系统表），是const的特例类型，平时不会出现，可以忽略不计。

但是笔者发现在MySQL5.7.22时，不会出现该字段值，只能出现const，但是在MySQL5.7版本以下可以出现该情况。猜测MySQL5.7版本是不是进行了优化，因为system官网的解释：

![706569-20180622104854512-1235381756.png][]

5.5.48：

![706569-20180622105030303-1864880340.png][]

5.7.22：

![706569-20180622105146631-599522110.png][]

注：两个引擎的执行信息不一样，5.5.48执行过程中产生了临时表（DERIVED），5.7.22为简单查询。

\#2.const

表示通过一次索引就找到了结果，常出现于primary key或unique索引。因为只匹配一行数据，所以查询非常快。如将主键置于where条件中，MySQL就能将查询转换为一个常量。

![706569-20180622105902309-472462389.png][]

注：对于system和const可能实际意义并不是很大，因为单表单行查询本来就快，意义不大。

\#3.eq\_ref

唯一索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见主键或唯一索引扫描。

![706569-20180622111828370-1190494342.png][]

注：经理只有一人，进行了tb\_dept的主键扫描。

\#4.ref

非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，返回匹配某值（某条件）的多行值，属于查找和扫描的混合体。

由于是非唯一性索引扫描，所以对tb\_emp表的deptid字段创建索引：

create index idx_tb_emp_deptid on tb_emp(deptid);

![706569-20180622114039034-672819485.png][]

\#5.range

只检索给定范围的行，使用一个索引来检索行，可以在key列中查看使用的索引，一般出现在where语句的条件中，如使用between、>、<、in等查询。

这种索引的范围扫描比全表扫描要好，因为索引的开始点和结束点都固定，不用扫描全索引。

![706569-20180622115327389-185289729.png][]

虽然我们为deptid字段创建了索引并在where中使用了between等，但在如下情况type仍为ALL。

![706569-20180622124821214-317572085.png][]

![706569-20180622124903478-1926763420.png][]

对比两图，可以看到使用deptid和id进行操作，其type的值一个是ALL也就是进行了全表扫描，一个是range进行了指定索引范围值检索。可能原因deptid并不是唯一索引。

对于以上问题，需要具体问题具体分析，并不能一概而论。

\#6.index

全索引扫描，index和ALL的区别：index只遍历索引树，通常比ALL快，因为索引文件通常比数据文件小。虽说index和ALL都是全表扫描，但是index是从索引中读取，ALL是从磁盘中读取。

![706569-20180622134618301-241493961.png][]

\#7.ALL

全表扫描。

注：一般来说，需保证查询至少达到range级别，最好能达到ref。

⑥possible\_keys和key、key\_len

possible\_keys：显示可能应用在表中的索引，可能一个或多个。查询涉及到的字段若存在索引，则该索引将被列出，但不一定被查询实际使用。

key：实际中使用的索引，如为NULL，则表示未使用索引。若查询中使用了覆盖索引，则该索引和查询的select字段重叠。

key\_len：表示索引中所使用的字节数，可通过该列计算查询中使用的索引长度。在不损失精确性的情况下，长度越短越好。key\_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key\_len是根据表定义计算而得，并不是通过表内检索出的。

简单理解：possible\_keys表示理论上可能用到的索引，key表示实际中使用的索引。

![706569-20180622140302280-2046342062.png][]

possible\_keys为NULL表示可能未用到索引，但key=idx\_deptid表示在实际查询的过程中进行了索引的全扫描。

通过下面的例子来理解key\_len，首先为name字段创建索引：

create index idx_name on tb_emp(name);

![706569-20180622142556991-256314714.png][]

注：在使用索引查询时，当条件越精确，key\_len的长度可能会越长，所以在不影响结果的情况下，key\_len的值越短越好。

⑦ref

显示关联的字段。如果使用常数等值查询，则显示const，如果是连接查询，则会显示关联的字段。

![706569-20180622143923854-156507641.png][]

注：由于id相同，因此从上到下执行：

\#1.tb\_emp表为非唯一性索引扫描，实际使用的索引列为idx\_name，由于tb\_emp.name='rose'为一个常量，所以ref=const。

\#2.tb\_dept为唯一索引扫描，从sql语句可以看出，实际使用了PRIMARY主键索引，ref=db01.tb\_emp.deptid表示关联了db01数据库中tb\_emp表的deptid字段。

⑧rows

根据表统计信息及索引选用情况大致估算出找到所需记录所要读取的行数。当然该值越小越好。

⑨filtered

百分比值，表示存储引擎返回的数据经过滤后，剩下多少满足查询条件记录数量的比例。

⑩Extra

显示十分重要的额外信息。其取值有以下几个：

\#1.Using filesort

Using filesort表明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。

mysql中无法利用索引完成的排序操作称为“文件排序”。

出现Using filesort就非常危险了，在数据量非常大的时候几乎“九死一生”。出现Using filesort尽快优化sql语句。

deptname字段未建索引的情况。

![706569-20180622154520287-452169518.png][]

为deptname字段创建索引后。

![706569-20180622154657116-497510920.png][]

\#2.Using temporary

使用了临时表保存中间结果，常见于排序order by和分组查询group by。非常危险，“十死无生”，急需优化。

将tb\_emp中name的索引先删除，出现如下图结果，非常烂，Using filesort和Using temporary，“十死无生”。

![706569-20180622160659014-495512021.png][]

为name字段创建索引后。

![706569-20180622160923003-398575816.png][]

\#3.Using index

表明相应的select操作中使用了覆盖索引，避免访问表的额外数据行，效率不错。

如果同时出现了Using where，表明索引被用来执行索引键值的查找。（where deptid=1）

如果没有同时出现Using where，表明索引用来读取数据而非执行查找动作。

删除tb\_emp表中name和deptid字段的单独索引，创建复合索引。

![706569-20180622162414976-876933935.png][]

从这里给出覆盖索引的定义：select的数据列只从索引中就能取得数据，不必读取数据行。通过上面的例子理解：创建了（name，deptid）的复合索引，查询的时候也使用复合索引或部分，这就形成了覆盖索引。简记：查询使用复合索引，并且查询的列就是索引列，不能多，个数需对应。

使用优先级Using index>Using filesort（九死一生）>Using temporary（十死无生）。也就说出现后面两项表明sql语句是非常烂的，急需优化！！！

#### 总结 ####

explain（执行计划）包含的信息十分的丰富，着重关注以下几个字段信息。

①id，select子句或表执行顺序，id相同，从上到下执行，id不同，id值越大，执行优先级越高。

②type，type主要取值及其表示sql的好坏程度（由好到差排序）：system>const>eq\_ref>ref>range>index>ALL。保证range，最好到ref。

③key，实际被使用的索引列。

④ref，关联的字段，常量等值查询，显示为const，如果为连接查询，显示关联的字段。

⑤Extra，额外信息，使用优先级Using index>Using filesort（九死一生）>Using temporary（十死无生）。

着重关注上述五个字段信息，对日常生产过程中调优十分有用。

首先创建三张表：tb\_emp(职工表)、tb\_dept(部门表)和tb\_desc(描述表)

1）tb\_emp表。

DROP TABLE IF EXISTS `tb_emp`;
    CREATE TABLE `tb_emp` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `username` varchar(20) NOT NULL,
       `deptid` int(11) NOT NULL,
       PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    -- ----------------------------
    INSERT INTO `tb_emp`(username,deptid) VALUES ('Tom', '1');
    INSERT INTO `tb_emp`(username,deptid) VALUES ('Jack', '1');
    INSERT INTO `tb_emp`(username,deptid) VALUES ('Mary', '2');
    INSERT INTO `tb_emp`(username,deptid) VALUES ('Rose', '3');

2）tb\_dept表。

DROP TABLE IF EXISTS `tb_dept`;
    CREATE TABLE `tb_dept` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(20) NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    -- ----------------------------
    INSERT INTO `tb_dept`(name) VALUES ('综合部');
    INSERT INTO `tb_dept`(name) VALUES ('研发');
    INSERT INTO `tb_dept`(name) VALUES ('测试');
    INSERT INTO `tb_dept`(name) VALUES ('总裁');

3）tb\_desc表。

DROP TABLE IF EXISTS `tb_desc`;
    CREATE TABLE `tb_desc` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `empid` int(11) DEFAULT NULL,
      `deptid` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    -- ----------------------------
    INSERT INTO `tb_desc`(empid,deptid) VALUES (1, 1);
    INSERT INTO `tb_desc`(empid,deptid) VALUES (2, 1);
    INSERT INTO `tb_desc`(empid,deptid) VALUES (3, 2);
    INSERT INTO `tb_desc`(empid,deptid) VALUES (4, 3);

注：这里强行将员工表与部门表不直接关联，通过第三张表（描述表）进行关联，主要为了进行join的分析。

#### 1.left join ####

\#1.首先执行查询。

![706569-20180623224006263-1736705921.png][]

\#2.通过explain进行分析。

![706569-20180623224241091-315300339.png][]

分析：从explain执行结果可以看出对两表都是用了全表扫描（ALL），并且在tb\_desc表中还使用了join连接缓存，需要进行优化。但是如何优化？是在左表建立索引还是右表建立索引呢？因为左连接左表是全有，所以应该在右表建立索引。

\#3.右表创建索引。

![706569-20180623225044004-1072568408.png][]

通过explain执行可以看到，在创建索引后，获得了比较不错的结果。（type=ref,Extra=Using index）。

结论：left join（左连接）情况下，应该在右表（tb\_desc）创建索引。

#### 2.right join ####

通过上面left join的例子，我们直接交换两表位置，并将left join改变成right join。

![706569-20180623230522781-2103775574.png][]

分析：

与left join进行对比，可以得到如下结论：

\#1.在left join下，首先执行tb\_emp（左表），type=ALL，因为左连接情况下左表全有，因此我们在tb\_desc（右表）创建索引，得到比较理想的效果。

\#2.在right join下（我们交换了tb\_emp和tb\_desc的位置），执行顺序：tb\_emp（右表）→ tb\_desc（左表）。右表type=ALL，因为右连接情况下右表全有，因此在左表（tb\_desc，我们交换了位置）创建索引，效果肯定和left join一样。

#### 总结 ####

**left join（左连接）：右表创建索引。**

**right join（右连接）：左表创建索引。**

**简记：左右外连接，索引相反建（left：右表建，right：左表建）。**

#### 0.准备 ####

创建经典的tb\_emp表。

DROP TABLE IF EXISTS `tb_emp`;
    CREATE TABLE `tb_emp` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(20) NOT NULL,
      `age` int(11) NOT NULL,
      gender varchar(10) NOT NULL,
      email varchar(20),
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    -- ----------------------------
    INSERT INTO `tb_emp` (name,age,gender,email) VALUES ('Tom', '22','male','1@qq.com');
    INSERT INTO `tb_emp` (name,age,gender,email) VALUES ('Mary', '21','female','2@qq.com');
    INSERT INTO `tb_emp` (name,age,gender,email) VALUES ('Jack', '27','male','3@qq.com');
    INSERT INTO `tb_emp` (name,age,gender,email) VALUES ('Rose', '23','female','4@qq.com');

注：创建了tb\_emp表，并插入了4条数据。

#### 1.最佳左前缀法则 ####

\#1.定义：在创建了多列索引的情况下，查询从索引的最左前列开始且不能跳过索引中的列。

最佳左前缀法则就是说如果创建了多个索引，在使用索引时要按照创建索引的顺序来使用，不能缺少或跳过，当然如果只使用最左边的索引列，也就是第一个索引是可以的，通俗理解：“带头大哥不能死，中间兄弟不能断”。要点：“头不能掉”。下面将用案例进行说明。

\#2.创建组合索引，并执行explain。

Case 1：

![706569-20180624190401218-1458339822.png][]

分析：

①索引的创建顺序为name，age，gender；

②直接使用name（带头大哥）作为条件，可以看到type=ref，key\_len=82，ref=const，效果还不错。

Case 2：

![706569-20180624185802408-1782895054.png][]

分析：

没使用带头大哥（name），直接用兄弟，type=ALL，为全表扫描。

Case 3：

![706569-20180624190657577-1365748955.png][]

![706569-20180624190850424-708535844.png][]

分析：

①对比上面两句sql语句可发现：我们使用：火车头（name）和中间车厢（age）、火车头（name）和车尾（gender）。

②虽然type=ref，但是观察key\_len和ref两项，并对比Case1中的结果，可得出在使用火车头（name）和车尾（gender）时，只使用了部分索引也就是火车头（name）的索引。

③通俗理解：火车头单独跑没问题，火车头与直接相连的车厢一起跑也没问题，但是火车头与车尾，如果中间没有车厢，只能火车头自己跑。

Case 4：

![706569-20180624191100260-341044144.png][]

分析：

火车头加车厢加车尾，三者串联，就变成了奔跑的小火车。type=ref，key\_len=128，ref=const，const，const。

最佳左前缀法则总结：**带头大哥不能死，中间兄弟不能断；带头大哥可跑路，老二也可跟着跑，其余兄弟只能死**。

#### 2.不要在索引列上做任何操作 ####

在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效从而转向全表扫描。

Case 1：

![706569-20180624191237299-1410230014.png][]

分析：

这里使用了函数计算，type=ALL，导致索引失效。

Case 2：

![706569-20180624191458173-125228980.png][]

分析：

将name=‘Tom’的值修改为‘123’，使用sql后，发生了类型转换，type=ALL，导致全表扫描。

结论：在索引列上做任何操作，都会导致索引失效转向全表扫描。

#### 3.范围右边全失效 ####

存储引擎不能使用索引中范围右边的列，也就是说范围右边的索引列会失效。

Case 1：

![706569-20180624191712959-1140058576.png][]

Case 2：

![706569-20180624191815745-1537412474.png][]

Case 3：

![706569-20180624191925423-1749109401.png][]

Case 4：

![706569-20180624192605853-282101338.png][]

对以上4个case进行分析：

①条件单独使用name时，type=ref，key\_len=82，ref=const。

②条件加上age时（使用常量等值），type=ref，key\_len=86，ref=const，const。

③当全值匹配时，type=ref，key\_len=128，ref=const，const，const。说明索引全部用上，从key\_len与ref可以看出。

④当使用范围时（age>27），type=range，key\_len=86，ref=Null，与Case 1、Case2和Case3可知，使用了部分索引，但gender索引没用上（与Case 3对比）。

结论：范围右边的索引列失效。

#### 4.尽量使用覆盖索引 ####

尽量使用覆盖索引（查询列和索引列尽量一致，通俗说就是对A、B列创建了索引，然后查询中也使用A、B列），减少select \*的使用。

Case 1：

![706569-20180624192929239-899471122.png][]

Case 2：

![706569-20180624193029492-1650110216.png][]

分析：

对比Case1和Case2，Case1使用select \*，Case2使用覆盖索引（查询列与条件列对应），可看到Extra从Null变成了Using index，提高检索效率。

#### 5.使用不等于（！=或<>）会使索引失效 ####

![706569-20180624194116625-767179219.png][]

结论：使用！=会使type=ALL，key=Null，导致全表扫描，并且索引失效。

#### 6.is null 或 is not null也无法使用索引 ####

Case 1：

![706569-20180624130623677-583786719.png][]

Case 2：

![706569-20180624194257642-1518030986.png][]

分析：

在使用is null的时候，索引完全失效，使用is not null的时候，type=ALL全表扫描，key=Null索引失效。

这里的例子可能有点特殊，具体情况肯能和case上的有所不同，但是还是要注意is null和is not null的使用。

#### 7.like通配符以%开头会使索引失效 ####

Case 1：

![706569-20180624194447502-761307758.png][]

Case 2：

![706569-20180624194526161-160167315.png][]

Case 3：

![706569-20180624194639651-1224411136.png][]

分析：

①like的%位置不同，所产生的效果不一样，当%出现在左边的时候，type=ALL，key=Null（全表扫描，索引失效），当%出现在右边的时候，type=range，索引未失效。

②like查询为范围查询，%出现在左边，则索引失效。%出现在右边索引未失效。口诀：like百分加右边。

但是在实际生产环境中，%仅出现在右边可能不能够解决我们的问题，所以解决%出现在左边索引失效的方法：使用覆盖索引。

Case 4：

![706569-20180624213722548-723610021.png][]

分析：对比Case1可知，通过覆盖索引type=index，并且使用了Using index，从全表扫描变成了全索引扫描，还是不错的。

Case 5：

![706569-20180624213945856-1428453394.png][]

分析：这里出现type=index，因为主键自动创建唯一索引。

Case 6：

![706569-20180624214457372-1223558264.png][]

分析：上面四组explain执行的结果都相同，表明都使用了索引，从这里可以深刻的体会到覆盖索引：完全吻合或者沾边（age），都可以使type=index。

Case 7：

![706569-20180624214834571-217778598.png][]

分析：由于只在（name，age，gender）上创建索引，当包含email时，导致结果集偏大（email未建索引）【锅大，锅盖小，不能匹配】，所以type=ALL。

#### 8.字符串不加单引号导致索引失效 ####

Case 1：

![706569-20180625094310677-222554677.png][]

分析：上述两条sql语句都能查询出相同的数据。

Case 2：

![706569-20180625094533249-1839887486.png][]

![706569-20180625094658710-214376473.png][]

分析：

通过explain执行结果可以看出，字符串（name）不加单引号在查询的时候，导致索引失效（type=ref变成了type=ALL，并且key=Null），并全表扫描。

结论：varchar类型的字段，在查询的时候不加单引号导致索引失效，转向全表扫描。

#### 9.少用or，用or连接会使索引失效 ####

![706569-20180625095249155-591889103.png][]

结论：通过上述explain的执行结果可看出，在使用or连接的时候type=ALL，key=Null，索引失效，并全表扫描。

#### 总结 ####

①全值匹配。

②最佳左前缀法则：**带头大哥不能死，中间兄弟不能断；带头大哥可跑路，老二也可跟着跑，其余兄弟只能死**。

③索引列上不计算。

④覆盖索引记住用。

⑤不等于、is null、is not null导致索引失效。

⑥like百分加右边，加左边导致索引失效，解决方法：使用覆盖索引。

⑦字符串不加单引号导致索引失效。

⑧少用or，用or导致索引失效。

#### 0.准备 ####

\#1.创建test表（测试表）。

![复制代码][copycode.gif]

drop table if exists test;
    create table test(
    id int primary key auto_increment,
    c1 varchar(10),
    c2 varchar(10),
    c3 varchar(10),
    c4 varchar(10),
    c5 varchar(10)
    ) ENGINE=INNODB default CHARSET=utf8;
    
    insert into test(c1,c2,c3,c4,c5) values('a1','a2','a3','a4','a5');
    insert into test(c1,c2,c3,c4,c5) values('b1','b2','b3','b4','b5');
    insert into test(c1,c2,c3,c4,c5) values('c1','c2','c3','c4','c5');
    insert into test(c1,c2,c3,c4,c5) values('d1','d2','d3','d4','d5');
    insert into test(c1,c2,c3,c4,c5) values('e1','e2','e3','e4','e5');

![复制代码][copycode.gif]

\#2.创建索引。

![706569-20180625114139843-688761483.png][]

#### 1.根据以下Case分析索引的使用情况 ####

Case 1：

![706569-20180625140904127-1302395868.png][]

分析：

①创建复合索引的顺序为c1,c2,c3,c4。

②上述四组explain执行的结果都一样：type=ref，key\_len=132，ref=const,const,const,const。

结论：在执行常量等值查询时，改变索引列的顺序并不会更改explain的执行结果，因为mysql底层优化器会进行优化，但是推荐按照索引顺序列编写sql语句。

Case 2：

![706569-20180625141728146-1257154425.png][]

![706569-20180625141904917-1270183615.png][]

分析：

当出现范围的时候，type=range，key\_len=99，比不用范围key\_len=66增加了，说明使用上了索引，但对比Case1中执行结果，说明c4上索引失效。

结论：范围右边索引列失效，但是范围当前位置（c3）的索引是有效的，从key\_len=99可证明。

Case 2.1：

![706569-20180625142519493-1707681115.png][]

分析：

与上面explain执行结果对比，key\_len=132说明索引用到了4个，因为对此sql语句mysql底层优化器会进行优化：范围右边索引列失效（c4右边已经没有索引列了），注意索引的顺序（c1,c2,c3,c4），所以c4右边不会出现失效的索引列，因此4个索引全部用上。

结论：范围右边索引列失效，是有顺序的：c1,c2,c3,c4，如果c3有范围，则c4失效；如果c4有范围，则没有失效的索引列，从而会使用全部索引。

Case 2.2：

![706569-20180625143231208-1151707823.png][]

分析：

如果在c1处使用范围，则type=ALL，key=Null，索引失效，全表扫描，这里违背了最佳左前缀法则，带头大哥已死，因为c1主要用于范围，而不是查询。

解决方式使用覆盖索引。

结论：在最佳左前缀法则中，如果最左前列（带头大哥）的索引失效，则后面的索引都失效。

Case 3：

![706569-20180625144706615-1341146854.png][]

分析：

利用最佳左前缀法则：中间兄弟不能断，因此用到了c1和c2索引（查找），从key\_len=66，ref=const,const，c3索引列用在排序过程中。

Case 3.1：

![706569-20180625145159536-1785441356.png][]

分析：

从explain的执行结果来看：key\_len=66，ref=const,const，从而查找只用到c1和c2索引，c3索引用于排序。

Case 3.2：

![706569-20180625145803516-50840484.png][]

分析：

从explain的执行结果来看：key\_len=66，ref=const,const，查询使用了c1和c2索引，由于用了c4进行排序，跳过了c3，出现了Using filesort。

Case 4：

![706569-20180625150919633-1308783530.png][]

分析：

查找只用到索引c1，c2和c3用于排序，无Using filesort。

Case 4.1：

![706569-20180625151236317-567907639.png][]

分析：

和Case 4中explain的执行结果一样，但是出现了Using filesort，因为索引的创建顺序为c1,c2,c3,c4，但是排序的时候c2和c3颠倒位置了。

Case 4.2：

![706569-20180625151917739-1865022943.png][]

![706569-20180625152104703-1743341270.png][]

分析：

在查询时增加了c5，但是explain的执行结果一样，因为c5并未创建索引。

Case 4.3：

![706569-20180625152716821-2133479526.png][]

分析：

与Case 4.1对比，在Extra中并未出现Using filesort，因为c2为常量，在排序中被优化，所以索引未颠倒，不会出现Using filesort。

Case 5：

![706569-20180625153146225-2012259748.png][]

分析：

只用到c1上的索引，因为c4中间间断了，根据最佳左前缀法则，所以key\_len=33，ref=const，表示只用到一个索引。

Case 5.1：

![706569-20180625153605318-963088595.png][]

分析：

对比Case 5，在group by时交换了c2和c3的位置，结果出现Using temporary和Using filesort，极度恶劣。原因：c3和c2与索引创建顺序相反。

#### 总结： ####

通过以上Case的分析，进行如下总结：

①最佳左前缀法则。

\#1.在等值查询时，更改索引列顺序，并不会影响explain的执行结果，因为mysql底层会进行优化。

\#2.在使用order by时，注意索引顺序、常量，以及可能会导致Using filesort的情况。

②group by容易产生Using temporary。

③通俗理解口诀：

全值匹配我最爱，最左前缀要遵守；

带头大哥不能死，中间兄弟不能断；

索引列上少计算，范围之后全失效；

LIKE百分写最右，覆盖索引不写星；

不等空值还有or，索引失效要少用。

#### 0.准备 ####

\#1.创建test表。

\#2.创建索引。

![706569-20180625223438203-1033999353.png][]

#### 1.根据Case分析order by的使用情况 ####

Case 1：

![706569-20180625223944999-13584041.png][]

分析：

①在c1,c2,c3,c4上创建了索引，直接在c1上使用范围，导致了索引失效，全表扫描：type=ALL，ref=Null。因为此时c1主要用于排序，并不是查询。

②使用c1进行排序，出现了Using filesort。

③解决方法：使用覆盖索引。

![706569-20180625225150972-757485019.png][]

Case 1.1：

![706569-20180625225605390-1287916353.png][]

分析：

排序时按照索引的顺序，所以不会出现Using filesort。

Case 1.2：

![706569-20180625230033609-1505851091.png][]

分析：

出现了Using filesort。原因：排序用的c2，与索引的创建顺序不一致，对比Case1.1可知，排序时少了c1（带头大哥），因此出现Using filesort。

Case 1.3：

![706569-20180625230403937-1781972191.png][]

分析：

出现了Using filesort。因为排序索引列与索引创建的顺序相反，从而产生了重排，也就出现了Using filesort。

Case 2：

![706569-20180625231117168-925966033.png][]

![706569-20180625231342815-2077452190.png][]

分析：

直接使用c2进行排序，出现Using filesort，因为不是从最左列索引开始排序的（没有带头大哥）。

Case 2.1：

![706569-20180625231451690-583449657.png][]

分析：

排序使用了索引顺序（带头大哥在），因此不会出现Using filesort。

Case 2.2：

![706569-20180625231710896-1971557040.png][]

分析：

虽然排序的字段列与索引顺序一样，且order by默认升序，这里c2 desc变成了降序，导致与索引的排序方式不同，从而产生Using filesort。

#### 总结： ####

①MySQL支持两种方式的排序filesort和index，Using index是指MySQL扫描索引本身完成排序。index效率高，filesort效率低。

②order by满足两种情况会使用Using index。

\#1.order by语句使用索引最左前列。

\#2.使用where子句与order by子句条件列组合满足索引最左前列。

③尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最佳左前缀法则。

④如果order by的条件不在索引列上，就会产生Using filesort。

\#1.filesort有两种排序算法：双路排序和单路排序。

双路排序：在MySQL4.1之前使用双路排序，就是两次磁盘扫描，得到最终数据。读取行指针和order by列，对他们进行排序，然后扫描已经排好序的列表，按照列表中的值重新从列表中读取对应的数据输出。即从磁盘读取排序字段，在buffer进行排序，再从磁盘取其他字段。

如果使用双路排序，取一批数据要对磁盘进行两次扫描，众所周知，I/O操作是很耗时的，因此在MySQL4.1以后，出现了改进的算法：单路排序。

单路排序：从磁盘中查询所需的列，按照order by列在buffer中对它们进行排序，然后扫描排序后的列表进行输出。它的效率更高一些，避免了第二次读取数据，并且把随机I/O变成了顺序I/O，但是会使用更多的空间，因为它把每一行都保存在内存中了。

\#2.单路排序出现的问题。

当读取数据超过sort\_buffer的容量时，就会导致多次读取数据，并创建临时表，最后多路合并，产生多次I/O，反而增加其I/O运算。

解决方式：

a.增加sort\_buffer\_size参数的设置。

b.增大max\_length\_for\_sort\_data参数的设置。

⑤提升order by速度的方式：

\#1.在使用order by时，不要用select \*，只查询所需的字段。

因为当查询字段过多时，会导致sort\_buffer不够，从而使用多路排序或进行多次I/O操作。

\#2.尝试提高sort\_buffer\_size。

\#3.尝试提高max\_length\_for\_sort\_data。

⑥附上一张从视频中截取出来的总结图。

![706569-20180626091734130-496909449.png][]

⑦group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最佳左前缀法则。当无法使用索引列的时候，也要对sort\_buffer\_size和max\_length\_for\_sort\_data参数进行调整。注意where高于having，能写在where中的限定条件就不要去having限定了。

[MySQL Explain]: https://www.cnblogs.com/developer_chan/p/9211487.html
[706569-20180621231820702-1539624119.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/289d797d176d43d0a9ccd3987bf93c52.png
[706569-20180621232750696-820361994.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/638a45b6eb1743f7852b4e2a766f72cd.png
[706569-20180621233147579-359902163.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/642051c307bb4747bb3201d8a470a61b.png
[706569-20180621233603807-1370399356.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/fae1c0f423514481a292691c399e5487.png
[706569-20180622085915119-1856869800.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/c1862cdd115f4c268883d5e8622b1be3.png
[copycode.gif]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/5752a30765804f48b38a7bfb2ec1f3e4.png
[706569-20180622104854512-1235381756.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/f59879c1a93c4080b53f9cf86ac43122.png
[706569-20180622105030303-1864880340.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/e14281a3bc894ccd8b404fe5975ee3ab.png
[706569-20180622105146631-599522110.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/9a2f7b2bdafb456bb91d4cdba8af6e93.png
[706569-20180622105902309-472462389.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/8fd96225c5bc4d9196f82e884c2c54ac.png
[706569-20180622111828370-1190494342.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/197f5c998111434f86b070f99c33a105.png
[706569-20180622114039034-672819485.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/e4bfeb495d854ec8862e63fe4d257f24.png
[706569-20180622115327389-185289729.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/1dc6b319ea994bc9b036f68beb7ebd36.png
[706569-20180622124821214-317572085.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/71decef177694488a15a07263f54f17b.png
[706569-20180622124903478-1926763420.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/ff5d3ae01e624b57bb4c16053f176a8f.png
[706569-20180622134618301-241493961.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/6f14896e4c2d4fefb8ac94aa9960ebb8.png
[706569-20180622140302280-2046342062.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/7193253487d84524b75f53caf680def2.png
[706569-20180622142556991-256314714.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/2df6847ec57644179aedeea74d5fecb6.png
[706569-20180622143923854-156507641.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/3c628f375f40447aaba14eb7cc75b8c2.png
[706569-20180622154520287-452169518.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/61a0ba73b5c54a4daa5ac385a40d5d6a.png
[706569-20180622154657116-497510920.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/5a389cd767b440a58b8cf6a0716d1821.png
[706569-20180622160659014-495512021.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/7bd715169ffc4c5a922d9ed6ab41895e.png
[706569-20180622160923003-398575816.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/2bc4ad13bac542c2b082caf117e058ea.png
[706569-20180622162414976-876933935.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/5951ef8b82864d97800326b674325b4d.png
[706569-20180623224006263-1736705921.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/b99eb9acdc624b6199f658607c360a4b.png
[706569-20180623224241091-315300339.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/495a3015c7ee42549628913558292f32.png
[706569-20180623225044004-1072568408.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/35923222f00f40b3a4748782e6688bf2.png
[706569-20180623230522781-2103775574.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/7927e8ca842b404e8afe968afc752235.png
[706569-20180624190401218-1458339822.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/91c4a2e79bf74479a037083741910d9f.png
[706569-20180624185802408-1782895054.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/037b3bb871194fcca608bf0320ba9f19.png
[706569-20180624190657577-1365748955.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/f9ca8850584a432cbf607a26c15e2493.png
[706569-20180624190850424-708535844.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/13412e90c11e485f91d0d85b02f35f95.png
[706569-20180624191100260-341044144.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/ea020ba59afc417ea7daae6a699c8a2b.png
[706569-20180624191237299-1410230014.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/741eaff6df9c4ce7a7368bbbf605cd0e.png
[706569-20180624191458173-125228980.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/1deb4b2065a2401094037489951eb2d1.png
[706569-20180624191712959-1140058576.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/57432d407e504034920c79a3f2254d58.png
[706569-20180624191815745-1537412474.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/d1dd8c16f7284005864456cc7325f55a.png
[706569-20180624191925423-1749109401.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/b367c2e1d00948edb2c9f91d9d94e038.png
[706569-20180624192605853-282101338.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/ec24f5f714734176b565e65820cb815e.png
[706569-20180624192929239-899471122.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/1605099b19774f1298a4eb2358c18660.png
[706569-20180624193029492-1650110216.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/78ecda2006f148f3b404b965c6e27987.png
[706569-20180624194116625-767179219.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/c4a92508389c409297e090f47f074479.png
[706569-20180624130623677-583786719.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/18ec351ac0c9422c90030cc017991b9a.png
[706569-20180624194257642-1518030986.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/0401af07a5234e12ba2ef68f14db7278.png
[706569-20180624194447502-761307758.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/543fef31c6ba4d93ab63f354e96a1721.png
[706569-20180624194526161-160167315.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/94d74d1fe40048999cc7f76838e56d92.png
[706569-20180624194639651-1224411136.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/e20ab44f84dc40e3b3ac842dd164302a.png
[706569-20180624213722548-723610021.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/bcc899a3305d453c8b259c9e7d6860b1.png
[706569-20180624213945856-1428453394.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/d78299859c6c405ca4b4704c9807c326.png
[706569-20180624214457372-1223558264.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/46b91b3cc9634bdf8e04a033f311cd17.png
[706569-20180624214834571-217778598.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/d1f9d2947958449a9534dc5c3ef81132.png
[706569-20180625094310677-222554677.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/098055f6a89c471180bb809f7d3271ce.png
[706569-20180625094533249-1839887486.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/2f1d7b21df784eb8b37db41e54a1c2b1.png
[706569-20180625094658710-214376473.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/50edd55fe37a415ea1477714c3ed010f.png
[706569-20180625095249155-591889103.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/f8ca55de4def4f5da857f40042b9fbbb.png
[706569-20180625114139843-688761483.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/38354807d67041f291dfe4dfc0353ad6.png
[706569-20180625140904127-1302395868.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/fb3772b0937c4a2c910a2ed3fcac4494.png
[706569-20180625141728146-1257154425.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/1869f5bfe82a4e51b6a99d7b757eabcd.png
[706569-20180625141904917-1270183615.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/0a0bb1f41ffd464db8e7a83b415eca2f.png
[706569-20180625142519493-1707681115.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/15913041c5224217b2287b534c1939f3.png
[706569-20180625143231208-1151707823.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/3ec8e74be6bc47b0b1d6bfb956976934.png
[706569-20180625144706615-1341146854.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/e942ab7f342445a1b6d02fad4f224b26.png
[706569-20180625145159536-1785441356.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/6f04f5ce0c0f4d859b95f7248254e1d5.png
[706569-20180625145803516-50840484.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/df314ce6d86d471d885c3b98605e5398.png
[706569-20180625150919633-1308783530.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/75c7b1a6c1154c2f9e3f0730f641cbf7.png
[706569-20180625151236317-567907639.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/62315ee2f7134fbc99ae5920b732b09b.png
[706569-20180625151917739-1865022943.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/e295c872e1c840c68bd9c3e0475ffe63.png
[706569-20180625152104703-1743341270.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/c585c803c17b479b88d757a738ade297.png
[706569-20180625152716821-2133479526.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/bf473fd5f97e4e348a4dd3cbecf114a4.png
[706569-20180625153146225-2012259748.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/65d92172ad374a58b84dbb82a7be3557.png
[706569-20180625153605318-963088595.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/873e123d20234fa8ae6898dcf71d61d8.png
[706569-20180625223438203-1033999353.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/c5667bca64bf40c0be4eb87437b3111e.png
[706569-20180625223944999-13584041.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/cca0a92cf71143fca1f563b765a5014c.png
[706569-20180625225150972-757485019.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/d334020327b342faad29424e02fe9c17.png
[706569-20180625225605390-1287916353.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/54ae1cc5c32b45a59fe308d44bd3aa70.png
[706569-20180625230033609-1505851091.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/5a6e616e739741d2bd937906340450d4.png
[706569-20180625230403937-1781972191.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/2f95c4f7a26c4dbbacef461e9cd31b6a.png
[706569-20180625231117168-925966033.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/d604aadb970f4d2f87cb4452e705c620.png
[706569-20180625231342815-2077452190.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/6d76ab3641684a30b2b83be7f923ce13.png
[706569-20180625231451690-583449657.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/c8ef709eb2494857a755e33a153e1147.png
[706569-20180625231710896-1971557040.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/a8ff2dc12eda4271860418659c1981d7.png
[706569-20180626091734130-496909449.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/003f3d9d9bf14d6b868a530535f3edc8.png