MySQL第11天：MySQL索引优化分析之性能分析

迈不过友情╰ 2022-10-22 07:52 203阅读 0赞

###                     MySQL索引优化分析之性能分析 ###

### 一、MySQL Query Optimizer   二、MySQL常见瓶颈   三、Explain（执行计划） ###

1、什么是执行计划？  
         2、执行计划能干什么？  
         3、执行计划怎么使用？  
         4、执行计划中各字段解释

\--------------------------------------------------------------------------------------------

# 一、MySQL Query Optimizer #

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWJvMTIzMDEyMw_size_16_color_FFFFFF_t_70][]

# 二、MySQL常见瓶颈  #

![20210405165341850.png][]

# 三、Explain（执行计划） #

### 1、什么是执行计划？ ###

使用Explain关键字可以模拟优化器执行SQL查询语句，从而知道MySQL如何处理你的SQL语句。分析你的查询语句或是表结构的性能瓶颈。

### 2、执行计划能干什么？ ###

（1）表的读取顺序
    （2）数据读取操作的操作类型
    （3）哪些索引可以使用
    （4）哪些索引被实际使用
    （5）表之间的引用
    （6）每张表有多少行被优化器查询
    
    PS：
    
    （1）select_type：数据读取操作的操作类型
    
    （2）possible_keys：哪些索引可以使用
    
    （3）key：哪些索引被实际使用
    
    （4）rows：每张表有多少行被优化器查询（rows越少越好）

### 3、执行计划怎么使用？ ###

**     1）Explain+SQL语句  
      2）执行计划包含的信息**

![20210405171055178.png][]

### 4、执行计划中各字段解释 ###

### **字段1：id：** ###

1）select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序。  
2）三种情况：  
第一种情况id相同：执行顺序由上而下；

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWJvMTIzMDEyMw_size_16_color_FFFFFF_t_70 1][]  
第二种情况id不同：如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWJvMTIzMDEyMw_size_16_color_FFFFFF_t_70 2][]  
第三种情况id相同和不同，同时存在

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWJvMTIzMDEyMw_size_16_color_FFFFFF_t_70 3][]

### **字段2：**select\_type ###

### ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWJvMTIzMDEyMw_size_16_color_FFFFFF_t_70 4][] ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWJvMTIzMDEyMw_size_16_color_FFFFFF_t_70 5][]

### **字段3：**table ###

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

### **字段4：**type ###

### ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWJvMTIzMDEyMw_size_16_color_FFFFFF_t_70 6][]   ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWJvMTIzMDEyMw_size_16_color_FFFFFF_t_70 7][] ###

![20210405181610243.png][]

### ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWJvMTIzMDEyMw_size_16_color_FFFFFF_t_70 8][] ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWJvMTIzMDEyMw_size_16_color_FFFFFF_t_70 9][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWJvMTIzMDEyMw_size_16_color_FFFFFF_t_70 10][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWJvMTIzMDEyMw_size_16_color_FFFFFF_t_70 11][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWJvMTIzMDEyMw_size_16_color_FFFFFF_t_70 12][]

### ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWJvMTIzMDEyMw_size_16_color_FFFFFF_t_70 13][] ###

### ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWJvMTIzMDEyMw_size_16_color_FFFFFF_t_70 14][] ###

### **字段5：**possible\_keys ###

理论上用到的索引。

### ![20210405182622651.png][]   **字段6：**key ###

实际用到的索引。

### ![20210405182717521.png][]   ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWJvMTIzMDEyMw_size_16_color_FFFFFF_t_70 15][] ###

### **字段7：**key\_len ###

![2021040518330446.png][]

### ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWJvMTIzMDEyMw_size_16_color_FFFFFF_t_70 16][]   **字段8：**ref ###

### ![20210405183832811.png][]   ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWJvMTIzMDEyMw_size_16_color_FFFFFF_t_70 17][] ###

### **字段9：**rows ###

### ![20210405184323350.png][]   ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWJvMTIzMDEyMw_size_16_color_FFFFFF_t_70 18][] ###

### **字段10：**extra ###

extra：包含不适合在其他列中显示但十分重要的额外信息。常见的有：
    （1）using filesort
    （2）using temporary
    （3）using index
    （4）using where
    （5）using join buffer
    （6）impossible where
    （7）select tables optimized away
    （8）distinct

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWJvMTIzMDEyMw_size_16_color_FFFFFF_t_70 19][]

![20210405202115819.png][]

![20210405202605406.png][]

![20210405203041601.png][]

PS：Using temporary：使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表，常见于排序order by和分组查询group by。

### 1、Using  filesort ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWJvMTIzMDEyMw_size_16_color_FFFFFF_t_70 20][]

### 2、Using temporary ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWJvMTIzMDEyMw_size_16_color_FFFFFF_t_70 21][]

PS：group by尽量根据索引的个数和顺序来，否则会产生文件列排序和临时表（using filesort、using temorary），影响查询性能。

### 3、Using  index ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWJvMTIzMDEyMw_size_16_color_FFFFFF_t_70 22][]

### ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWJvMTIzMDEyMw_size_16_color_FFFFFF_t_70 23][] ###

### 4、using join buffer ###

使用了链接缓存

### 5、impossible where ###

where子句的值总是false，不能用来获取任何元组

### 6、impossible where ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWJvMTIzMDEyMw_size_16_color_FFFFFF_t_70 24][]

### 7、select tables optimized away ###

### ![20210405202605406.png][] ###

### 8、distinct ###

![20210405203041601.png][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWJvMTIzMDEyMw_size_16_color_FFFFFF_t_70]: /images/20221022/834cedb3695b47fbb2d7ce9f81977a7a.png
[20210405165341850.png]: /images/20221022/0cc3b80a3484445d86c686182f91a2d1.png
[20210405171055178.png]: /images/20221022/68df52dbe10e4e95b648814dc4d48113.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWJvMTIzMDEyMw_size_16_color_FFFFFF_t_70 1]: /images/20221022/47cdb9889a164b309b2b6654a3584371.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWJvMTIzMDEyMw_size_16_color_FFFFFF_t_70 2]: /images/20221022/3e4c4a2a1d204338b1951b5d0865dc7f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWJvMTIzMDEyMw_size_16_color_FFFFFF_t_70 3]: /images/20221022/0a8e863bb07f475b99c92abb302c6cc6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWJvMTIzMDEyMw_size_16_color_FFFFFF_t_70 4]: /images/20221022/48a68fe537534e96bb2c62a1ea515604.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWJvMTIzMDEyMw_size_16_color_FFFFFF_t_70 5]: /images/20221022/9dcd4ad01e74458b9b43e7dd1c43b935.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWJvMTIzMDEyMw_size_16_color_FFFFFF_t_70 6]: https://img-blog.csdnimg.cn/20210405180023961.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWJvMTIzMDEyMw==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWJvMTIzMDEyMw_size_16_color_FFFFFF_t_70 7]: /images/20221022/ba1d4dab07c04846aae5f4205443a182.png
[20210405181610243.png]: /images/20221022/b660988c78e24b64abd72fd7ae933a8b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWJvMTIzMDEyMw_size_16_color_FFFFFF_t_70 8]: /images/20221022/4e01acf77ad34c1fa644ed25f36f2b55.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWJvMTIzMDEyMw_size_16_color_FFFFFF_t_70 9]: /images/20221022/8222388fbd0b49c596c821a639665b14.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWJvMTIzMDEyMw_size_16_color_FFFFFF_t_70 10]: /images/20221022/17520066e0a742699a790e3bb74c309f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWJvMTIzMDEyMw_size_16_color_FFFFFF_t_70 11]: /images/20221022/fc88c7e5d56d464ab34afc263978a251.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWJvMTIzMDEyMw_size_16_color_FFFFFF_t_70 12]: /images/20221022/dc4e21fcc7444d6c8c147d9e35cfe058.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWJvMTIzMDEyMw_size_16_color_FFFFFF_t_70 13]: /images/20221022/c31abada76ee4d80b693869a34b00f11.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWJvMTIzMDEyMw_size_16_color_FFFFFF_t_70 14]: /images/20221022/88b339c2569b4add8ddd446ead43dfcb.png
[20210405182622651.png]: /images/20221022/2c0fc0b9a9a3426b8f9f92d6c68622d0.png
[20210405182717521.png]: https://img-blog.csdnimg.cn/20210405182717521.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWJvMTIzMDEyMw_size_16_color_FFFFFF_t_70 15]: /images/20221022/0c43e1cf1c474dac9b070f055231a500.png
[2021040518330446.png]: /images/20221022/eb3157e2feca4bd68ff58e1209d8b3ff.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWJvMTIzMDEyMw_size_16_color_FFFFFF_t_70 16]: /images/20221022/4e1c493444a047708194b6e5eda17735.png
[20210405183832811.png]: https://img-blog.csdnimg.cn/20210405183832811.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWJvMTIzMDEyMw_size_16_color_FFFFFF_t_70 17]: /images/20221022/b3443f0925ac4f84bfa76d92cce400d4.png
[20210405184323350.png]: https://img-blog.csdnimg.cn/20210405184323350.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWJvMTIzMDEyMw_size_16_color_FFFFFF_t_70 18]: /images/20221022/89b759f91f284da3a1360af395fe54db.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWJvMTIzMDEyMw_size_16_color_FFFFFF_t_70 19]: /images/20221022/5bdca776b9494fa4b5b95e11279519a6.png
[20210405202115819.png]: /images/20221022/575d6876a80948b8aee8f157d5f9b4fa.png
[20210405202605406.png]: /images/20221022/898532392cd046ef9c02e91f5a6f016a.png
[20210405203041601.png]: /images/20221022/df648b38759643d1a8b84967d7a23bf6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWJvMTIzMDEyMw_size_16_color_FFFFFF_t_70 20]: /images/20221022/0332058e1a364d81a22e4cb0239fc993.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWJvMTIzMDEyMw_size_16_color_FFFFFF_t_70 21]: /images/20221022/3c157c7d9967443ebe9a6514f87bc04b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWJvMTIzMDEyMw_size_16_color_FFFFFF_t_70 22]: /images/20221022/d709abda71054418b12e3da1ccd9733c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWJvMTIzMDEyMw_size_16_color_FFFFFF_t_70 23]: /images/20221022/b323f33d79424de88a88456f2b26af60.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWJvMTIzMDEyMw_size_16_color_FFFFFF_t_70 24]: /images/20221022/4a68bcee0f534ed0885851a3725b96d9.png