Mysql学习-03 索引优化分析--性能分析

迈不过友情╰ 2024-02-19 13:25 126阅读 0赞

**性能分析 -->Explain工具：**

1.  执行计划

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzU0OTU3OA_size_16_color_FFFFFF_t_70][]

[ 查看官网说明][Link 1]

2.用途

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

3.使用：

Explain + SQL语句

![20181115230530753.png][]

**各字段解释：**

**1. id**：select查询的序列号,包含一组数字，表示查询中执行select子句或操作表的顺序  
三种情况

![2018111523090271.png][]  
**id相同，执行顺序由上至下**

![20181115230922513.png][]  
i**d不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行**

![20181115230954295.png][]  
 **id相同不同，同时存在**

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

**2.select\_type：**查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzU0OTU3OA_size_16_color_FFFFFF_t_70 1][]

2.1 SIMPLE -->简单的 select 查询,查询中不包含子查询或者UNION

![20181115231649280.png][]

2.2 PRIMARY -->查询中若包含任何复杂的子部分，最外层查询则被标记为Primary

![2018111523172587.png][]

2.3 DERIVED -->在FROM列表中包含的子查询被标记为DERIVED(衍生) MySQL会递归执行这些子查询, 把结果放在临时表里。

![2018111523184930.png][]

2.4 SUBQUERY -->在SELECT或WHERE列表中包含了子查询

![20181115231946112.png][]

2.5 DEPENDENT SUBQUERY -->在SELECT或WHERE列表中包含了子查询,子查询基于外层

![20181115232025158.png][]

2.6 UNCACHEABLE SUBQUREY -->无法被缓存的子查询

![20181115232212981.png][]

![20181115232246350.png][]

2.7 UNION -->若第二个SELECT出现在UNION之后，则被标记为UNION； 若UNION包含在FROM子句的子查询中,外层SELECT将被标记为：DERIVED

![20181115232326117.png][]

2.8 UNION RESULT--> 从UNION表获取结果的SELECT

![20181115232357330.png][]

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

**4.type (重点\*)**

![20181115232715948.png][]

4.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。**

4.2显示查询使用了何种类型 -->**从最好到最差依次是：system>const>eq\_ref>ref>range>index>AL**

**4.2.1:system** \-->表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计

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

![20181115233152883.png][]

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

![20181115233242152.png][]

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

![20181115233400533.png][]

![20181115233410944.png][]

**4.2.5:index**\-->Full Index Scan，**index与ALL区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小**.（也就是说虽然all和Index都是读全表，但**index是从索引中读取的，而all是从硬盘中读的**）

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzU0OTU3OA_size_16_color_FFFFFF_t_70 2][]

**4.2.6:all** **-**\-> Full Table Scan，将遍历全表以找到匹配的行

![20181115233652837.png][]

![20181115233702664.png][]

**4.2.7:index\_merge**\--> 在查询过程中需要**多个索引组合使用**，通常出现在有 or 的关键字的sql中

![20181115233810374.png][]

**4.2.8:ref\_or\_null**\-->对于某个字段既需要**关联条件，也需要null值**得情况下。查询优化器会选择用ref\_or\_null连接查询。

![20181115233934759.png][]

**4.2.9:index\_subquery**\-->利用索引来关联子查询，不再全表扫描。

![20181115234025527.png][]

![20181115234050964.png][]

![20181115234120116.png][]

**4.2.9:unique\_subquery** \-->该联接类型类似于index\_subquery。 子查询中的唯一索引

![20181115234235894.png][]

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

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

**6.key** \-->实际使用的索引。如果为NULL，则没有使用索引;查询中若使用了覆盖索引，则该索引和查询的select字段重叠.

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzU0OTU3OA_size_16_color_FFFFFF_t_70 3][]

**7.key\_len** \-->表示**索引中使用的字节数**，可通过该列计算查询中使用的索引的长度.key\_len字段能够帮你检查是否充分的利用上了索引.

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzU0OTU3OA_size_16_color_FFFFFF_t_70 4][]

**如何计算  
1 、先看索引上字段的类型+长度比如 int=4 ; varchar(20) =20 ; char(20) =20  
2 、如果是varchar或者char这种字符串字段，视字符集要乘不同的值，比如utf-8 要乘 3,GBK要乘2，  
3 、varchar这种动态字符串要加2个字节  
4、 允许为空的字段要加1个字节**

![20181115235234618.png][]

第一组:key\_len=age的字节长度+name的字节长度=4+1 + ( 20\*3+2)=5+62=67  
第二组: key\_len=age的字节长度=4+1=5

**8.ref** \-->显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值

![20181115234855756.png][]

**9. rows** \-->rows列显示MySQL认为它执行查询时必须检查的行数。越少越好

![20181115235002803.png][]

![2018111523502828.png][]

**10.Extra** \-->包含不适合在其他列中显示但十分重要的额外信息

**10.1:Using filesort**   
说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。 MySQL中无法利用索引完成的排序操作称为“文件排序”

出现filesort的情况：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzU0OTU3OA_size_16_color_FFFFFF_t_70 5][]

优化后，不再出现filesort的情况：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzU0OTU3OA_size_16_color_FFFFFF_t_70 6][]

**查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度**

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

优化前存在 using temporary 和 using filesort

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzU0OTU3OA_size_16_color_FFFFFF_t_70 7][]

优化前存在 using temporary 和 using filesort 不在，性能发生明显变化：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzU0OTU3OA_size_16_color_FFFFFF_t_70 8][]

**10.3 USING index**  
表示相应的select操作中使用了**覆盖索引(Covering Index)，避免访问了表的数据行，效率不错**！ 如果同时出现using where，表明索引被用来执行索引键值的查找; 如果没有同时出现using where，表明索引只是用来读取数据而非利用索引执行查找。  
覆盖索引(Covering Index)：索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据;当能通过读取索引就可以得到想要的数据，那就不需要读取行了。**一个索引包含了(或覆盖了)\[select子句\]与查询条件\[Where子句\]中所有需要的字段就叫做覆盖索引。**  
注意：  
**如果要使用覆盖索引，一定要注意select列表中只取出需要的列，不可select \*，  
因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降。**  
**10.4 Using where** \--> 表明使用了where过滤

**10.5 using join buffer** \-->使用了连接缓存：

![20181117155044483.png][]

**10.6 impossible where** \--> where子句的值总是false，不能用来获取任何元组

![20181117155120662.png][]

**10.7 select tables optimized away**  
在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者 对于MyISAM存储引擎优化COUNT(\*)操作，不必等到执行阶段再进行计算， 查询执行计划生成的阶段即完成优化。

在innodb中：

![20181117155201491.png][]

在Myisam中：

![20181117155234116.png][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzU0OTU3OA_size_16_color_FFFFFF_t_70]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/05cfc8215b50413892f5d46ebb16e3f3.png
[Link 1]: https://dev.mysql.com/doc/refman/8.0/en/using-explain.html
[20181115230530753.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/e43b3cdfb4aa4e84a9f9a6fbfd86b75a.png
[2018111523090271.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/bd52f97b5947428a91945cef124257c1.png
[20181115230922513.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/91df08c1bfe249149ec19bd2cda2f1d0.png
[20181115230954295.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/c0fc008a3a194196a8c39dac2b1a3b05.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzU0OTU3OA_size_16_color_FFFFFF_t_70 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/8260d3c9d796436786e12a60e513be3b.png
[20181115231649280.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/48079534880a4070b3fa80423181c1bb.png
[2018111523172587.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/521df6d6c89844f0a3e5156a5dfd2703.png
[2018111523184930.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/8967943acfcf409597c85224a4400bbd.png
[20181115231946112.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/4d2a9d6e11174ba8a7b6eb44a5445c13.png
[20181115232025158.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/346c49de91334751aa31f5aaee834722.png
[20181115232212981.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/a52b12582c0245d19e7f449c1a7b7d4d.png
[20181115232246350.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/d81aaef77f3848faa9a349b404fcde2f.png
[20181115232326117.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/4df06219bae64f91a8c92c7db7a1e333.png
[20181115232357330.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/09f3e3c1b15740ec90a4e8ee1f8c8675.png
[20181115232715948.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/c709ded02f6b4ff3bfbf4671043e1ece.png
[20181115233152883.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/ec925c5ba48a403eab849ab8076d1c23.png
[20181115233242152.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/48b5f57816db451683a4fab484ef76d6.png
[20181115233400533.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/a371bed3b02b483b8548a77daaa8b283.png
[20181115233410944.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/f5d3c8b6598445b3af780f428b11f8c8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzU0OTU3OA_size_16_color_FFFFFF_t_70 2]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/3e5027a9616542fe85624059c6cd9da6.png
[20181115233652837.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/62a9702e28294e69bfba7b4de8760c9a.png
[20181115233702664.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/cf182db3aee647f28ede6bfb316b596e.png
[20181115233810374.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/f5b61dd0eaec4608aa48e872352e3780.png
[20181115233934759.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/5108e5e56ca3488295e7ff2825b34f2d.png
[20181115234025527.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/30cad3d305104559a150cb315cc3ad70.png
[20181115234050964.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/cd9cbed347864bb8b69273016d5b2606.png
[20181115234120116.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/bea6b1d9d4de478e963c9b4d5dab2ac5.png
[20181115234235894.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/65660f15d1184df18bec5b3356280e5d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzU0OTU3OA_size_16_color_FFFFFF_t_70 3]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/b382501a314147a38fb154c5335c460a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzU0OTU3OA_size_16_color_FFFFFF_t_70 4]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/73fc67465ad74890a585d73b270b9a9b.png
[20181115235234618.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/73667848ea86473e9b9e45dc925cee7b.png
[20181115234855756.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/ffa435b09dcd4e14980eb2ffdf2983fd.png
[20181115235002803.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/42f42e6937d84ad6a1a7e5526d2011ea.png
[2018111523502828.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/e74fbf176a61472d928c6a38b59bc233.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzU0OTU3OA_size_16_color_FFFFFF_t_70 5]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/5a88bc3c4015429d8014c6c5217689b0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzU0OTU3OA_size_16_color_FFFFFF_t_70 6]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/2a5d451d9a414894854e65e4ec8655c3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzU0OTU3OA_size_16_color_FFFFFF_t_70 7]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/61049dd3b7864982856cea83f0f6820c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzU0OTU3OA_size_16_color_FFFFFF_t_70 8]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/8f2292081c804831a78ee554672e8327.png
[20181117155044483.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/f7b98ddacca84313b5e211b1b1ebe702.png
[20181117155120662.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/4f9a33aef72e47bd8f52e682c0d6c78c.png
[20181117155201491.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/a24e569bdfb249d684629a31e62735b2.png
[20181117155234116.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/fb933f3601344802a28bceae1f80e741.png