MySQL调优系列（六）——查询优化

忘是亡心i 2024-03-16 14:46 49阅读 0赞

### 一、查询慢的原因 ###

查询速率受网络、CPU、IO、上下文切换、系统调用、生成统计信息、锁等待时间等因素影响。  
举个常见面试题：  
**一个表非常非常大，上亿级别的数据，性能会变慢嘛？如果表有索引**  
答：增删改会变慢。（索引也需要动态的修改，会降低 SQL 执行效率）。  
查询不一定：如果特定的查询某一条数据或者少量数据，性能几乎没变化。如果并发查询大量数据，受带宽硬盘网络等影响，查询效率会变慢。

### 二、优化查询 ###

查询的执行过程可以参考：[MySQL调优系列（一）——性能监控。][MySQL]  
查询时，首先是SQL命令传递到解析器的时候会被解析器验证和解析，分类/验证语义合法性。其次优化器会**基于成本**选择最有效的执行计划。

#### 1、减少访问的数据量 ####

举个栗子：**分页优化**  
查询语句如下：

explain  select * from suoyin_test  limit 30000,1;
    
    explain  select * from suoyin_test a join (select id from suoyin_test limit 30000,1) b on a.id = b.id;

运行结果如下：  
![在这里插入图片描述][dbb4eab3728a4a7c872fad6e7b15ac5a.png]  
![在这里插入图片描述][7bae17241f6f4fb1be725d4945c235cb.png]  
优化的主要思路：  
先利用覆盖索引把要取的数据行的主键取到，然后再用这个主键列与数据表做关联(查询的数据量小了后再进行查询)。

#### 2、减少不必要的数据筛选 ####

（1）尽量根据需要返回列，不要返回全部列，比如用limit取值。  
（2）多次取用相同的数据，可以使用缓存。

#### 3、优化器的优化类型 ####

> （1）重新定义关联表的顺序。数据表的关联并不总是按照在查询中指定的顺序进行，决定关联顺序时优化器很重要的功能。  
> （2）将外连接转化成内连接，内连接的效率要高于外连接。  
> （3）使用等价变换规则，mysql可以使用一些等价变化来简化并规划表达式。  
> 优化count、min、max等聚合函数。索引的列是否可以为空通常可以帮助mysql优化这类表达式：例如，要找到某一列的最小值，只需要查询索引的最左端的记录即可，不需要全文扫描比较。  
> （4）子查询优化。mysql在某些情况下可以将子查询转换一种效率更高的形式，从而减少多个查询多次对数据进行访问，例如将经常查询的数据放入到缓存中。  
> （5）等值传播（自动等值）。比如关联查询时，两个表的共性属性做筛选条件，只需要写一个就行。

#### 4、关联查询 ####

如果被驱动表加索引，效率是非常高的，但如果索引不是主键索引，所以还得进行一次回表查询。相比，被驱动表的索引是主键索引，效率会更高。具体参考：[MySQL调优系列（五）——索引详解。][MySQL 1]

#### 5、排序优化 ####

（1）**使用索引扫描来做排序。** 创建组合索引的时候默认的索引排序是升序，所以后续用组合索引列进行排序要全部升序或者全部降序才会用到索引。  
（2）**未使用到索引排序。** 分双次排序和单次排序。  
**双次排序** ：两次扫描磁盘，最终得到数据。第一次数据读取是将需要排序的字段读取出来，然后进行排序，第二次是将排好序的结果按照需要去读取数据行。（多次随机IO，读取数据成本会比较高。）  
**单次排序**：从磁盘读取查询需要的所有列，对它们进行排序，然后扫描排序后的列表进行输出。（效率更高，避免二次读取，把随机IO变成了顺序IO，但是对内存要空间要求高，无法存储大量的数据。）  
**优化策略：**

> ① 增大 sort\_buffer\_size参数的设置（单路排序的内存大小）  
> ② 增大 max\_length\_for\_sort\_data参数的设置（单次排序字段大小）  
> ③ 去掉select 后面不需要的字段

#### 6、count查询优化 ####

count(\*)、count(1)、count(id)查询速度大差不差。

#### 7、分组优化 ####

实质是先排序后分组，所以优化策略和排序优化一样：能使用索引尽量使用索引，不行的话就尽量增大sort\_buffer\_size，优化成单次排序。

#### 8、子查询优化 ####

尽量使用关联查询，inner join。

#### 9、去重查询 ####

尽量不要使用 distinct 关键字去重，可以使用分组（group by），可以用到索引。

### 三、总结 ###

补充一句总结：**能用索引就用索引！**

[MySQL]: https://blog.csdn.net/liwangcuihua/article/details/129011620?spm=1001.2014.3001.5501
[dbb4eab3728a4a7c872fad6e7b15ac5a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/c8fbe4eb5d9d42ef9af261e97375931a.png
[7bae17241f6f4fb1be725d4945c235cb.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/481eceb937ee4926a8aadd45785b8aa9.png
[MySQL 1]: https://blog.csdn.net/liwangcuihua/article/details/130719864?spm=1001.2014.3001.5501