Mysql索引优化分析_explain查看执行计划

今天药忘吃喽~ 2024-03-24 16:46 80阅读 0赞

#### 目录 ####

*  一、Explain
 *   *  1.是什么(查看执行计划)
     *  2.能干嘛
     *  3.怎么玩
     *  4.建表语句
 *  二、索引优化分析\_explain\_各字段解释
 *   *  1.id★
     *  3.table
     *  4.partitions
     *  5.type★
     *   *  5.1.system
         *  5.2.const
         *  5.3.eq\_ref
         *  5.4.ref \*
         *  5.5.range \*
         *  5.6.Index
         *  5.7.all
     *  6.possible\_keys
     *  7.key
     *  8.key\_len ★
     *  9.ref
     *  10.rows★
     *  11.filtered
     *  12.Extra★
     *   *  12.1.Using filesort \*
         *  12.2.Using temporary \*
         *  12.3.Using index \*
         *  12.4.Using where
         *  12.5.using join buffer \*
         *  12.6.impossible where

## 一、Explain ##

### 1.是什么(查看执行计划) ###

使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是 如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈  
官网介绍 [https://dev.mysql.com/doc/refman/5.7/en/explain-output.html][https_dev.mysql.com_doc_refman_5.7_en_explain-output.html]

### 2.能干嘛 ###

*  表的读取顺序
 *  数据读取操作的操作类型
 *  哪些索引可以使用
 *  哪些索引被实际使用
 *  表之间的引用
 *  每张表有多少行被优化器查询

### 3.怎么玩 ###

Explain + SQL语句

<table> 
 <tbody> 
 <tr> 
 <td>Column</td> 
 <td>JSON Name</td> 
 <td>Meaning</td> 
 </tr> 
 <tr> 
 <td>id</td> 
 <td>select_id</td> 
 <td>The SELECT identifier</td> 
 </tr> 
 <tr> 
 <td>select_type</td> 
 <td>None</td> 
 <td>The SELECT type</td> 
 </tr> 
 <tr> 
 <td>table</td> 
 <td>table_name</td> 
 <td>The table for the output row</td> 
 </tr> 
 <tr> 
 <td>partitions</td> 
 <td>partitions</td> 
 <td>The matching partitions</td> 
 </tr> 
 <tr> 
 <td>type</td> 
 <td>access_type</td> 
 <td>The join type</td> 
 </tr> 
 <tr> 
 <td>possible_keys</td> 
 <td>possible_keys</td> 
 <td>The possible indexes to choose</td> 
 </tr> 
 <tr> 
 <td>key</td> 
 <td>key</td> 
 <td>The index actually chosen</td> 
 </tr> 
 <tr> 
 <td>key_len</td> 
 <td>key_length</td> 
 <td>The length of the chosen key</td> 
 </tr> 
 <tr> 
 <td>ref</td> 
 <td>ref</td> 
 <td>The columns compared to the index</td> 
 </tr> 
 <tr> 
 <td>rows</td> 
 <td>rows</td> 
 <td>Estimate of rows to be examined</td> 
 </tr> 
 <tr> 
 <td>filtered</td> 
 <td>filtered</td> 
 <td>Percentage of rows filtered by table condition</td> 
 </tr> 
 <tr> 
 <td>Extra</td> 
 <td>None</td> 
 <td>Additional information</td> 
 </tr> 
 </tbody> 
</table>

### 4.建表语句 ###

CREATE TABLE t1(id INT(10) AUTO_INCREMENT,content  VARCHAR(100) NULL ,  PRIMARY KEY (id));
     CREATE TABLE t2(id INT(10) AUTO_INCREMENT,content  VARCHAR(100) NULL ,  PRIMARY KEY (id));
     CREATE TABLE t3(id INT(10) AUTO_INCREMENT,content  VARCHAR(100) NULL ,  PRIMARY KEY (id));
     CREATE TABLE t4(id INT(10) AUTO_INCREMENT,content  VARCHAR(100) NULL ,  PRIMARY KEY (id));
     
     INSERT INTO t1(content) VALUES(CONCAT('t1_',FLOOR(1+RAND()*1000))); 
     INSERT INTO t2(content) VALUES(CONCAT('t2_',FLOOR(1+RAND()*1000)));  
     INSERT INTO t3(content) VALUES(CONCAT('t3_',FLOOR(1+RAND()*1000)));    
     INSERT INTO t4(content) VALUES(CONCAT('t4_',FLOOR(1+RAND()*1000)));

## 二、索引优化分析\_explain\_各字段解释 ##

### 1.id★ ###

1.select查询的序列号,包含一组数字，表示查询中执行select子句或操作表的顺序  
2.三种情况  
1)id相同，执行顺序由上至下  
Explain select \* from t1,t2,t3;  
![在这里插入图片描述][c13e7b03abd244c0a1dcdbc4f9ebde35.png]

2)id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行  
explain select t1.id from t1 where t1.id = (select t2.id from t2 where t2.id = (select t3.id from t3 where t3.content=‘t3\_897’));  
![在这里插入图片描述][7981a75a660440a1b86024399e42e1f0.png]

*  id相同，不同，同时存在;
 *  id如果相同，可以认为是一组，从上往下顺序执行；
 *  在所有组中，id值越大，优先级越高，越先执行;
 *  关注点：id号每个号码，表示一趟独立的查询。一个sql的查询趟数越少越好。

11.2.select\_type  
1.有哪些  
![在这里插入图片描述][14a0a740614043f8929cae2ce75fb51f.png]

2.查询的类型，主要是用于区别 普通查询、联合查询、子查询等的复杂查询  
1)SIMPLE

*  简单的 select 查询,查询中不包含子查询或者UNION  
    EXPLAIN SELECT \* FROM t1;  
    ![在这里插入图片描述][5fc18db860cd4c33861e6c4b584c8daa.png]

2)PRIMARY

*  查询中若包含任何复杂的子部分，最外层查询则被标记为primary  
    EXPLAIN select t1.id from t1 where t1.id = (select t2.id from t2 where t2.id = (select t3.id from t3 where t3.content=‘t3\_897’));  
    ![在这里插入图片描述][c48ca44b617a42fab612ae27378bca43.png]

3)SUBQUERY

*  在SELECT或WHERE列表中包含了子查询  
    ![在这里插入图片描述][c2eeb918c23f4d199b91a9e4d2de3ddc.png]

4)DEPENDENT SUBQUERY

*  在SELECT或WHERE列表中包含了子查询,子查询基于外层  
    EXPLAIN SELECT \* FROM t3 WHERE id = ( SELECT id FROM t2 WHERE content = t3.content);  
    ![在这里插入图片描述][6a1b32441573496cab1c575bc507a803.png]

5)UNCACHEABLE SUBQUREY

*  表示这个subquery的查询要受到外部系统变量的影响  
    EXPLAIN SELECT \* FROM t3 WHERE id = ( SELECT id FROM t2 WHERE content = @@character\_set\_server);

![在这里插入图片描述][876f405d5e2241deae7f59d40884bdf5.png]

6)UNION

*  若第二个SELECT出现在UNION之后，则被标记为UNION； 若UNION包含在FROM子句的子查询中,外层SELECT将被标记为：DERIVED  
    EXPLAIN SELECT \* FROM (SELECT \* FROM t1 UNION SELECT \* FROM t2) aa;  
    ![在这里插入图片描述][d4f6ca8d5e8845b694fcab4ac8df8685.png]

7)UNION RESULT

*  从UNION表获取结果的SELECT  
    ![在这里插入图片描述][2fd67371da714acb914f0098b111d0d0.png]

### 3.table ###

*  显示这一行的数据是关于哪张表的

### 4.partitions ###

*  代表分区表中的命中情况，非分区表，该项为null
 *  [https://dev.mysql.com/doc/refman/5.7/en/alter-table-partition-operations.html][https_dev.mysql.com_doc_refman_5.7_en_alter-table-partition-operations.html]

### 5.type★ ###

![在这里插入图片描述][5217e7936a944a33bb5eec2eedfd55cd.png]  
1.访问类型排列  
type显示的是访问类型，是较为重要的一个指标，结果值从最好到最坏依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

system>const>eq_ref>ref>range>index>ALL

一般来说，得保证查询至少达到range级别，最好能达到ref。  
2.类型介绍

#### 5.1.system ####

表仅有一行记录，必须是系统表，这是const类型的特例，查询起来非常迅速。  
explain SELECT \* from mysql.proxies\_priv WHERE `User`=‘root’;  
![在这里插入图片描述][0c23a982c4c44cd58072c0c21dee7044.png]

#### 5.2.const ####

explain select \* from t1 where id = 1;  
![在这里插入图片描述][9351c2563bc84c25b21b75935c14b896.png]

*  表示通过索引一次就找到了,const用于primary key或者unique索引。
 *  因为只匹配一行数据，所以很快 如将主键置于where列表中，MySQL就能将该查询转换为一个常量

#### 5.3.eq\_ref ####

explain select \* from t1,t2 where t1.id = t2.id;  
![在这里插入图片描述][94701a77f7d643ef82ee6dab13b3cf58.png]

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

#### 5.4.ref \* ####

create index idx\_content on t1(content);  
EXPLAIN SELECT \* FROM t1, t2 WHERE t1.content = t2.content;  
![在这里插入图片描述][f4b73321ba09488e8e7affa8f6e7d182.png]

*  非唯一性索引扫描，返回匹配某个单独值的所有行. 本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而， 它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体

#### 5.5.range \* ####

explain select \* from t2 where id >1 and id <5;

![在这里插入图片描述][c6b511a0811a4cb785980372cee0cbdf.png]

* 只检索给定范围的行,使用一个索引来选择行。
 * key 列显示使用了哪个索引 一般就是在你的where语句中出现了between、<、>、in等的查询 这种范围索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。

#### 5.6.Index ####

explain select id from t1;  
![在这里插入图片描述][45c4ed60a2ab4e90a7b397996c8f7c6f.png]

*  出现index是sql使用了索引但是没用通过索引进行过滤，一般是使用了覆盖索引或者是利用索引进行了排序分组
 *  Full Index Scan，index与ALL区别为index类型只遍历索引树。
 *  这通常比ALL快，因为索引文件通常比数据文件小。
 *  也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘中读的

#### 5.7.all ####

explain select \* from t2;  
![在这里插入图片描述][d52cf1d99fa343b096eda4de923e64c2.png]

*  Full Table Scan，将遍历全表以找到匹配的行

### 6.possible\_keys ###

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

### 7.key ###

*  实际使用的索引。如果为NULL，则没有使用索引  
    ![在这里插入图片描述][9c18bfa41b0f43bb854f4312a442dcab.png]

### 8.key\_len ★ ###

> key\_len表示索引使用的字节数，根据这个值可以判断索引的使用情况，特别是在组合索引的时候，判断该索引有多少部分被使用到非常重要。值越大越好。

创建索引，并查询表结构

create index idx_age_deptId_name on emp(age,deptId,name);

![在这里插入图片描述][baa46e65f17d40439d83a6313299b125.png]  
![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAS25pZ2h0X0FM_size_20_color_FFFFFF_t_70_g_se_x_16]

> 如何计算  
> key\_len=age的字节长度+deptid的字节长度+name的字节长度=(4+1) + (4+1) +(20\*3+2+1)=73

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAS25pZ2h0X0FM_size_17_color_FFFFFF_t_70_g_se_x_16]  
**key\_len的长度计算公式：**

> varchr(10)变长字段且允许NULL = 10 \* ( character set：utf8=3,gbk=2,latin1=1)+1(NULL)+2(变长字段)  
> varchr(10)变长字段且不允许NULL = 10 \* ( character set：utf8=3,gbk=2,latin1=1)+2(变长字段)  
> char(10)固定字段且允许NULL = 10 \* ( character set：utf8=3,gbk=2,latin1=1)+1(NULL)  
> char(10)固定字段且不允许NULL = 10 \* ( character set：utf8=3,gbk=2,latin1=1)

### 9.ref ###

显示将哪些列或常量与键列中命名的索引进行比较，以从表中选择行。  
explain select \* from t1,t2 where t1.id = t2.id;  
![在这里插入图片描述][112553dd7f4d47f7b58c53f394c0503a.png]

### 10.rows★ ###

*  rows列显示MySQL认为它执行查询时必须检查的行数。值越小越好

### 11.filtered ###

*  这个字段表示存储引擎返回的数据在mysql server层过滤后，剩下多少满足查询的记录数量的比例，注意是**百分比**，不是具体记录数

### 12.Extra★ ###

*  包含不适合在其他列中显示,但十分重要的额外信息

#### 12.1.Using filesort \* ####

*  出现filesort的情况：order by 没有用上索引。
 *  explain select id,empno,name,age from emp where deptid=100 order by name limit 10;

![在这里插入图片描述][45c0385d745c4c7d8e368a063369dbe9.png]

*  优化后（给deptid和name字段建立复合索引），去掉filesort  
    create index idx\_deptid\_name on emp (deptid,name);  
    ![在这里插入图片描述][0399fb77c2b4445a9fbb2ba68dc2b890.png]
 *  查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
 *  说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。 MySQL中无法利用索引完成的排序操作称为“文件排序”

#### 12.2.Using temporary \* ####

*  出现Using temporary情况：**分组没有用上索引**。产生临时表。注意：分组操作是需要先排序后分组的。所以，也会出现Using filesort。
 *  优化前存在 using temporary 和 using filesort

explain select name,count(\*) from emp where deptid=100 group by name limit 10;

![在这里插入图片描述][27f09eb6002b429c815958caaa4a2bbc.png]

*  优化后（给deptno和ename建立复合索引）去掉using temporary 和 using filesort，性能发生明显变化：  
    create index idx\_deptno\_ename on emp (deptno,ename);  
    ![在这里插入图片描述][d2fcb191ae7540cfae5ff98cb9bcdad1.png]
 *  使用了临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序order by 和分组查询group by。

#### 12.3.Using index \* ####

表示使用了覆盖索引 \[content是一个索引\]

*  如果同时出现using where，表明索引被用来执行索引键值的查找;  
    ![在这里插入图片描述][bc5236c1d06c482c8ca1bea7e75a0c02.png]
 *  如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。  
    ![在这里插入图片描述][298f6b378dfa47b7b88174f2b6a0883b.png]

#### 12.4.Using where ####

*  表明使用了where过滤！

#### 12.5.using join buffer \* ####

如果有它则表明关联字段没有使用索引！

*  使用了连接缓存

![在这里插入图片描述][0d012ec8542b481e983bdbc4b3368d23.png]

#### 12.6.impossible where ####

*  where 后面筛选条件有错误！  
    ![在这里插入图片描述][a2153de8741241d185a65eb0615a6d9d.png]

[https_dev.mysql.com_doc_refman_5.7_en_explain-output.html]: https://dev.mysql.com/doc/refman/5.7/en/explain-output.html
[c13e7b03abd244c0a1dcdbc4f9ebde35.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/e36500b2a10b4ab3ae43367fdef74374.png
[7981a75a660440a1b86024399e42e1f0.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/9f20df1f4a5b426793d33c7f3366e01f.png
[14a0a740614043f8929cae2ce75fb51f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/c0dcf9e7c0bb490ba1d28c350055129a.png
[5fc18db860cd4c33861e6c4b584c8daa.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/01536fa9eb2243c09406a9c8cc06b29b.png
[c48ca44b617a42fab612ae27378bca43.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/2eadd792542643f1851a140699d9ac85.png
[c2eeb918c23f4d199b91a9e4d2de3ddc.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/fe9f6939868e4a36aba8c8d03862b107.png
[6a1b32441573496cab1c575bc507a803.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/016f42503e05459fb4a5e9e394a20133.png
[876f405d5e2241deae7f59d40884bdf5.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/5fd3dbba068543f7a77f2363ba0ca85d.png
[d4f6ca8d5e8845b694fcab4ac8df8685.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/fcd026bb5d424162bab357c896981360.png
[2fd67371da714acb914f0098b111d0d0.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/d78e74fc6eab4ea2988cfb74647a7edf.png
[https_dev.mysql.com_doc_refman_5.7_en_alter-table-partition-operations.html]: https://dev.mysql.com/doc/refman/5.7/en/alter-table-partition-operations.html
[5217e7936a944a33bb5eec2eedfd55cd.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/16a3b41f93114bdfaf3ea4151b0065e3.png
[0c23a982c4c44cd58072c0c21dee7044.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/eb43d56bd9474fd29d74b409acd19321.png
[9351c2563bc84c25b21b75935c14b896.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/6ccfc5746c50431c95450a1f43dbfc76.png
[94701a77f7d643ef82ee6dab13b3cf58.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/18236f64efac485189b6337cc764ee08.png
[f4b73321ba09488e8e7affa8f6e7d182.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/b9846e403d8a46a397d3a869daa211ac.png
[c6b511a0811a4cb785980372cee0cbdf.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/2e18919f7c564ee5ad42f016c1e078e1.png
[45c4ed60a2ab4e90a7b397996c8f7c6f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/6fe0db6fadf2472586da7b40542cca82.png
[d52cf1d99fa343b096eda4de923e64c2.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/3319b67d43544fcfa4ed6fcf7650ceaa.png
[9c18bfa41b0f43bb854f4312a442dcab.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/fd8b46875c0f4c188ffba19457cc2d71.png
[baa46e65f17d40439d83a6313299b125.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/972b5e45b32a46e19033fde671e3f1c4.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAS25pZ2h0X0FM_size_20_color_FFFFFF_t_70_g_se_x_16]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/992dd37c50b6499da5a134b44fc6bbcf.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAS25pZ2h0X0FM_size_17_color_FFFFFF_t_70_g_se_x_16]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/89f5b594a43141abaa626fe23302adcf.png
[112553dd7f4d47f7b58c53f394c0503a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/25c225ee397e416c8297fbf3dda5cfca.png
[45c0385d745c4c7d8e368a063369dbe9.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/3825736ffe984fae820cf24eeecbfc95.png
[0399fb77c2b4445a9fbb2ba68dc2b890.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/63ff2b28815d4bf2bfab6196affa6256.png
[27f09eb6002b429c815958caaa4a2bbc.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/d27d93e79a2e4f5f80d3f77a713440ab.png
[d2fcb191ae7540cfae5ff98cb9bcdad1.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/c7df75ad748545e09f7e65a878b0c927.png
[bc5236c1d06c482c8ca1bea7e75a0c02.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/8027fe255d8c4ddf9aba3a0cddb1a32b.png
[298f6b378dfa47b7b88174f2b6a0883b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/bc30fb0b0a98478e9c4d08d0a9166635.png
[0d012ec8542b481e983bdbc4b3368d23.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/3bd4117f5361404d9046d3cea4d17072.png
[a2153de8741241d185a65eb0615a6d9d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/e9ab0a1a32c94622aea0e57291e96bbb.png