《高性能Mysql》重点总结（四）——查询优化

悠悠 2022-05-10 06:28 323阅读 0赞

> 本篇将介绍各种查询语句优化，包括join、in\\exist、order by、group by、limit、distinct等。

# 一、Join语句 #

### 1. 7种Join查询 ###

1.  select \* from A a inner join B b on a.key = b.key;  
    ![在这里插入图片描述][70]
2.  select \* from A a left join B b on a.key = b.key;  
    ![在这里插入图片描述][70 1]
3.  select \* from A a right join B b on a.key = b.key;  
    ![在这里插入图片描述][70 2]  
    
4.  select \* from A a left join B b on a.key = b.key where b.key is NULL;  
    ![在这里插入图片描述][70 3]  
    
5.  select \* from A a left join B b on a.key = b.key where a.key = NULL;  
    ![在这里插入图片描述][70 4]
6.  select \* from A a left join B b on a.key = b.key  
    union  
    select \* from A a right join B b on a.key = b.key;  
    ![在这里插入图片描述][70 5]
7.  (select \* from A a left join B b on a.key = b.key  
    union  
    select \* from A a right join B n on a.key = b.key)  
    where a.key is NULL or b.key is NULL;  
    ![在这里插入图片描述][70 6]

### 2. Join语句优化 ###

1.  单表查询优化
    
    案例：SELECT id,author\_id FROM article WHERE category\_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;
    
    `第一次` 直接查询的结果：  
    ![在这里插入图片描述][70 7]  
    结论：  
    很显然,type 是 ALL,即最坏的情况。Extra 里还出现了 Using filesort,也是最坏的情况。优化是必须的。
    
    `第二次` 对所有查询条件建立复合索引：  
    ![在这里插入图片描述][70 8]  
    结论：  
    type 变成了 range,这是可以忍受的。但是 extra 里使用 Using filesort 仍是无法接受的。
    
    但是我们已经建立了索引,为啥没用呢？  
    这是因为按照 BTree 索引的工作原理,先排序 category\_id,如果遇到相同的 category\_id 则再排序 comments,如果遇到相同的 comments 则再排序 views。当 comments 字段在联合索引里处于中间位置时,因**comments > 1**条件是一个范围值(所谓 range),MySQL 无法利用索引再对后面的 views 部分进行检索,即 range 类型查询字段后面的索引无效。
    
    `第三次` 重建索引 对category\_id 和views 字段建立复合索引：  
    ![在这里插入图片描述][70 9]  
    结论：type 变为了 ref,Extra 中的 Using filesort 也消失了,结果非常理想。
2.  双表关联查询优化
    
    案例：`左外连接` SELECT \* FROM class LEFT JOIN book ON class.card = book.card;
    
    `第一次` 直接查询的结果：  
    ![在这里插入图片描述][70 10]  
    结论：type 为All，全表扫描，出现using join buffer
    
    `第二次` 左边表class的card字段上建索引：  
    ![在这里插入图片描述][70 11]  
    结论：事实证明，左连接加右表，左表确定全有，右表不确定。
    
    `第三次` 右边表book的card字段上建索引：  
    ![在这里插入图片描述][70 12]  
    结论：可以看到第二行的 type 变为了 ref,rows 也变成了优化比较明显。  
    这是由左连接特性决定的。LEFT JOIN 条件用于确定如何从右表搜索行,左边一定都有,所以右边是我们的关键点,一定需要建立索引。
3.  三表关联查询优化
    
    案例：SELECT \* FROM class LEFT JOIN book ON class.card = book.card LEFT JOIN phone ON book.card = phone.card;
    
    `第一次` 直接查询的结果：  
    ![在这里插入图片描述][70 13]
    
    `第二次` 两个被驱动表（右边表book、phone）各加上索引：  
    ![在这里插入图片描述][70 14]
4.  优化建议
    
    1、保证Join语句中被驱动表上Join条件字段已经被索引  
    被驱动表：left join 右边的表为被驱动表 。
    
    2、left join 时，选择小表作为驱动表，大表作为被驱动表  
    尽可能减少Join语句中的NestedLoop的循环总次数，“永远用小结果集驱动大的结果集”。
    
    3、优先优化嵌套循环的内层循环  
    子查询尽量不要放在被驱动表，有可能使用不到索引。若必须用到子查询，可将子查询设置为驱动表，因为驱动表的type 肯定是 all，而子查询返回的结果表没有索引，必定也是all。
    
    4、inner join 时，mysql会自动帮你把小结果集的表选为驱动表
    
    5、当无法保证被驱动表的Join条件字段被索引且内存资源充足的前提下，增加JoinBuffer的大小

# 二、用in还是用exist？ #

### 强调：小表驱动大表 ###

![在这里插入图片描述][70 15]

# 三、Order By 优化 #

### 1. ORDER BY子句，尽量使用Index方式排序，避免FileSort ###

MySQL支持二种方式的排序，FileSort和Index，Index效率高。它指MySQL扫描索引本身完成排序。FileSort方式效率较低。

ORDER BY满足以下情况，会使用Index方式排序：  
①ORDER BY 语句符合索引最左前缀原则。  
②使用Where子句与Order BY子句条件列组合满足索引最左前列。  
③where子句中如果出现索引的范围查询(即explain中出现range)会导致order by 索引失效。

示例：CREATE INDEX idx\_A\_ageBirth ON tblA(age,birth,name);  
![在这里插入图片描述][70 16]  
![在这里插入图片描述][70 17]

### 2. 如果不在索引列上，filesort有两种算法：mysql就要启动双路排序和单路排序 ###

1、双路排序

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

2、单路排序

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

3、单路排序存在的问题

在sort\_buffer中，方法B比方法A要多占用很多空间，因为方法B是把所有字段都取出, 所以有可能取出的数据的总大小超出了sort\_buffer的容量，导致每次只能取sort\_buffer容量大小的数据，进行排序（创建tmp文件，多路合并），排完再取取sort\_buffer容量大小，再排……从而多次I/O。

本来想省一次I/O操作，反而导致了大量的I/O操作，反而得不偿失。

4、优化策略

①增大sort\_buffer\_size参数的设置。用于单路排序的内存大小。  
②增大max\_length\_for\_sort\_data参数的设置。单次排序字段大小。(单次排序请求)。  
③去掉select 后面不需要的字段。select 后的多了，排序的时候也会带着一起，很占内存，所以去掉没有用的。

### 3. 尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀 ###

![在这里插入图片描述][70 18]

# 四、Group By优化 #

### 1. 注意点 ###

1.  group by实质是先排序后进行分组，遵照索引建的最佳左前缀。
2.  当无法使用索引列，增大max\_length\_for\_sort\_data参数的设置+增大sort\_buffer\_size参数的设置。
3.  where优于having，能写在where限定的条件就不要去having限定了。

### 2. 其它 ###

Group By排序方面的处理与Order By类似，这里不再赘述。

# 五、Limit优化 #

### 1. 案例 ###

常规：

SELECT  SQL_NO_CACHE * FROM emp  ORDER  BY  deptno   LIMIT 10000,40;

子查询优化：  
先利用覆盖索引把要取的数据行的主键取到，然后再用这个主键列与数据表做关联：(查询的数据量小了后)

SELECT SQL_NO_CACHE * FROM emp INNER JOIN (SELECT id FROM emp e ORDER BY deptno LIMIT 10000,40) a ON a.id=emp.id;

# 六、去重优化 #

尽量不要使用 distinct 关键字去重，用 distinct 关键字去重消耗性能。

### 1. 案例 ###

select distinct kcdz form t_mall_sku where id in( 3,4,5,6,8 );

优化：

kcdz字段上建立索引，利用索引来优化。

select  kcdz form t_mall_sku where id in( 3,4,5,6,8 )  group by kcdz;

# 七、最后 #

[点击获取][Link 1]可以查看我更多更详尽的阅读笔记哈，如有帮助的话，请给我一个star，谢谢支持~

[70]: /images/20220510/8d5f1408a7184da19cffe9a798d6ca3a.png
[70 1]: /images/20220510/01807f60142c45c7aa02c9bb64463585.png
[70 2]: /images/20220510/c9daa007197c4faea563d80d4b05d74f.png
[70 3]: /images/20220510/6fd1287240144c36a8b4dd532d31b2a4.png
[70 4]: /images/20220510/0e597a9f6d3b47a9a5891552b370fbf7.png
[70 5]: /images/20220510/5de35fb68f1d4a9994f72192b8578812.png
[70 6]: /images/20220510/ec1de949cb474bf0aed7127e38f97f0c.png
[70 7]: /images/20220510/6161cfc5793a40d7bb39fbc05a66e42f.png
[70 8]: /images/20220510/a76aa56119324fafa4d11937e8218586.png
[70 9]: /images/20220510/84538b0181d14a0390daf1f80b683812.png
[70 10]: /images/20220510/7c7e725c79084219ad8a5a3c2b1c60d4.png
[70 11]: /images/20220510/a6d00cd8bc0744ada93aba620720be2c.png
[70 12]: /images/20220510/7b315928ea4a4f0bae75301069633bce.png
[70 13]: /images/20220510/2d834503ab264fba859221ecfd432fe5.png
[70 14]: /images/20220510/24d5b94c01d04a11a7ebe9a24bd98f1d.png
[70 15]: /images/20220510/ee0802281fc440328aad31c96296d2e8.png
[70 16]: /images/20220510/cd4863e7d3ec444e889efb01585e8a33.png
[70 17]: /images/20220510/5007b72da06046f492e78bcc74d1a491.png
[70 18]: /images/20220510/41b6ee0f5f1f44b2990349054f0831eb.png
[Link 1]: https://github.com/hdonghong/reading-notes