MySQL查询性能优化（下）

深藏阁楼爱情的钟 2022-10-17 05:27 269阅读 0赞

> 参考文献《高性能MySQL（第三版）》

上一期主要对MySQL的查询过程进行了简要的梳理，理解了一条SQL执行的过程需要经过MySQL的各种组件，本期我们将重点探索下MySQL查询性能优化的方法。

## 5 MySQL查询优化器的局限 ##

MySQL查询优化器对于以下几种类型的查询是不适用的。

### 5.1 关联子查询 ###

where条件中包含in子句的子查询语句。例如：

*  `select * from a where a.id in (select b.id from b where b.name = 'zhongger')`

对于这类查询，MySQL会将表a进行全表扫描，然后根据表a的id逐个去执行in语句中的子查询。如果a表很大，那么这个查询性能会非常差。

### 5.2 Union的限制 ###

当需要对结果集合并时，需要使用union子句。例如：

*  `(select a.name from a order by a.name) union all (select b.name from b order by b.name) limit 10`

这是将两个查询结果合并，然后取前10条记录。MySQL对于这条SQL的处理是把a表中的记录和b表中的记录存放在一个临时表中，然后再从临时表取出10条记录。如果a，b表的记录很大，那么这样子性能也是很慢的。可以将上述SQL改写成如下形势：

*  `(select a.name from a order by a.name limit 10) union all (select b.name from b order by b.name limit 10) limit 10`

### 5.3 索引合并优化 ###

当where条件中包含多个复杂条件的时，MySQL能够访问单个表的多个索引以合并和交叉过滤的方式来定位需要查找的行。

### 5.4 等值查询 ###

等值传递也会带来意想不到的额外消耗。例如：有一个非常大的IN列表，而MySQL优化器发现存在where、on或者using的子句，将这个列表的值和另一个的某个列相关联。

在执行查询时，查询优化器会将In列表都复制到关联的各个表中来进行匹配关联，如果IN列表非常大，则会导致执行和优化都会变慢。

### 5.5 并行执行 ###

MySQL无法利用CPU多核的特性来并行执行查询。

### 5.6 哈希关联 ###

MySQL不支持哈希关联（MySQL的所有关联都是嵌套循环关联的）

### 5.7 松散索引扫描 ###

MySQL不支持松散索引扫描，也就无法按照不连续的方式扫描一个索引。例如：有覆盖索引（a，b），SQL语句：

*  `select * from t where b between 2 and 3`

因为索引的最左前缀列是a，但查询中只覆盖了列b，故MySQL不走索引，只能全表扫描。

### 5.8 最大值和最小值局限 ###

对于Min()和Max()查询，MySQL的优化做得并不好。例如：

*  `select min(id) from t where name = 'zhongger'`

因为在name字段上没有索引，所以MySQL会有一次全表扫描。如果MySQL能够进行主键扫描，那么理论上MySQL读到第一个满足条件的记录时，就需要我们找到的最小值了，因为主键索引中的叶子节点是按照id的大小顺序排序。但是MySQL这时还是会做全表扫描。一个优化方法是：

*  `select id from t use index(primary) where name = 'zhongger' limit 1`

这可以让MySQL扫描尽可能少的记录。

## 6 优化特定类型的查询 ##

前面做了这么多的铺垫，都是为了这一小节能够对查询优化的理解更加深刻。下面一起来看下吧。

### 6.1 优化count()查询 ###

count()是一个聚合函数，它的主要作用是：

*  统计某个列值的数量
 *  统计表的记录的行数

在统计列值的时候，要求列值是非空的（即不统计NULL值），如果在count()的括号中传入了列或者列的表达式作为参数，则统计的就是这个列或列表达式有值的结果数。如果在在count()的括号中传入了通配符\*作为参数，则会统计结果集中的所有行数。

如果希望知道结果集的行数，最好使用count(\*)，而不是count(结果集中的某一列)，这样意义清晰而且性能更好。

#### 简单的优化 ####

在不加任何where条件时，MyISAM存储引擎因为有对表的行数进行存储，所以有些情况下可以考虑使用MyISAM存储引擎来优化count(\*)。

#### 使用近似值 ####

有些时候某些业务场景并不要求完全精确的count值，因此可以使用近似值来代替。像一些弱一致性的场景，没必要每次都去数据库中查count，可以考虑利用Redis缓存来提升效率。

#### 更复杂的优化 ####

通常来说，count需要扫描大量的行才可以获取精确的结果，因此还是比较难优化的。此外，可以考虑新建立一个汇总表，每写入一条记录，汇总表对应的记录就加1，查询count时只需要查一遍汇总表的数字即可，这样可以避免全表扫描，当然这样也增加了维护的难度。

> 快速，精确和实现简单，三者只能取其二。

### 6.2 优化关联查询 ###

对于这条关联SQL：

*  `select * from a inner join b on a.id = b.id`

优化需要注意如下的点：

*  确保on或者using子句中的列有索引。此外，在创建索引的时候也要考虑到表关联的顺序
 *  确保任何的group by和order by中的表达式只涉及一个表中的列，这样MySQL才有可能使用索引来优化这个过程
 *  当升级MySQL时需要注意：关联语法、运算符优先级等可能会发生变化的地方。以前是普通关联的地方可能会变成笛卡尔积，不同类型的关联可能会生成不同的结果等

### 6.3 优化子查询 ###

MySQL5.6以下的版本，子查询最好使用关联查询来代替；MySQL5.6及以上的版本，子查询已经被优化了。

### 6.4 优化group by和distinct ###

*  大多数场景下，MySQL会采用索引来优化group by查询。
 *  当无法使用索引时，group by优化策略是使用临时表或者文件排序来做分组，可以通过SQL\_BIG\_RESULT和SQL\_SMALL\_RESULT来让优化器进行优化。
 *  如果对关联查询做group by，且按照查找表中的某列进行分组，那么常采用查找表的标识列来group by会比其他列效率高。例如：`select * from a inner join b on a.id = b.id group by a.id`的效率比 `select * from a inner join b on a.id = b.id group by a.name`高。

### 6.5 优化limit分页 ###

在系统中需要进行分页操作的时候，我们通常会使用limit加上offset的方法实现，同时加上合适的order by子句。如果有对应的索引，效率通常会不错；否则，MySQL需要做大量的文件查询。

在offset非常大时，例如limit 10000，20这样的查询，这时MySQL要查询10020条记录然后只返回最后20条，前面10000条记录都将被抛弃，这样的代价非常高。要优化这种查询，**要么是在页面中限制分页的数量，要么是优化大offerset的性能**。

优化此类分页查询的一个最简单的方法是尽可能地使用索引覆盖扫描，而非查询所有的列，然后根据需要做一次关联操作再返回所需要的列。对于offset很大的时候，这么做的效率会有很大的提升。对于如下SQL：

*  `select id, name from a order by title limit 50, 5;`

如果a表非常大，那么这个查询最好改写成下面形式：

*  `select a.id , a.name from a inner join (select a.id fom a order by title limit 50,5) as lim using(a.id);`

这让MySQL扫描尽可能少的页面，获取需要访问的记录后再根据关联列返回原表查询需要的所有列，因为利用了聚簇索引去扫描。

有时也可以将limit转为已知未知的查询，让MySQL通过范围扫描获得结果，例如改写成：

*  `select id, name from a order by title limit 50, 5 where position between 50 and 54 order by position`

对于大的offset，会使得MySQL扫描大量不需要的行然后抛弃掉。可以采用**书签**的方式，记录上次取数据的位置，下次就可以从书签记录的位置开始扫描，这样就可以避免使用offerset。

### 6.6 优化Union查询 ###

MySQL总是通过创建并填充临时表的方式来执行union查询，因此很多优化策略在union查询中没办法很好使用。所以需要将where、limit、order by等写到需要union的各个子查询中，以便优化器可以充分利用这些条件进行优化。

## 最后 ##

MySQL查询性能优化是一个很大的课题，往往需要结合实际情况来制定优化策略。一般的步骤不外乎就是先explain分析，然后尽可能地利用索引，避免全表扫描，避免索引失效等。我是Zhongger，一个在互联网公司摸鱼写代码的打工人，你们的支持是我创作的最大动力，我们下期见～  
![在这里插入图片描述][20210603183146278.jpg]

[20210603183146278.jpg]: /images/20221014/b0f7d2ea3a3b4d71a017af9007ef75d5.png