MySQL查询性能优化（五）- 查询优化器的局限性

MySQL的万能嵌套循环并不是对每种查询都是最优的。不过MySQL查询优化器只对少部分查询不适用，而且我们往往可以通过改写查询让MySQL高效的完成工作。

1 关联子查询

MySQL的子查询实现的非常糟糕。最糟糕的一类查询时where条件中包含in()的子查询语句。因为MySQL对in()列表中的选项有专门的优化策略，一般会认为MySQL会先执行子查询返回所有in()子句中查询的值。一般来说，in()列表查询速度很快，所以我们会以为sql会这样执行

select * from tast_user where id in (select id from user where name like '王%');
我们以为这个sql会解析成下面的形式
select * from tast_user where id in (1,2,3,4,5);
实际上MySQL是这样解析的
select * from tast_user where exists 
(select id from user where name like '王%' and tast_user.id = user.id);

in（）查询，MySQL会将相关的外层表压缩到子查询中，它认为这样可以更高效的查找到数据行

这时候由于子查询用到了外部表中的id字段所以子查询无法先执行。通过explin可以看到，MySQL先选择对tast_user表进行全表扫描，然后根据返回的id逐个执行子查询。如果外层是一个很大的表，那么这个查询的性能会非常糟糕。当然我们可以优化这个表的写法：

select tast_user.* from tast_user inner join user using(tast_user.id) where user.name like '王%'

另一个优化的办法就是使用group_concat()在in中构造一个由逗号分隔的列表。有时这比上面使用关联改写更快。因为使用in()加子查询，性能通常会非常糟糕。所以通常建议使用exists()等效的改写查询来获取更好的效率。

如何书写更好的子查询就不在介绍了，因为现在基本都要求拆分成单表查询了，有兴趣的话可以自行去了解下。

 

2 UNION的限制

有时MySQL无法将限制条件从外层下推导内层，使的原本能够限制部分返回结果的条件无法应用到内层查询的优化上

有时，MySQL无法将限制条件从外层下推导内层，这使得原本能够限制部分返回结果的条件无法应用到内层查询的优化上。

如果希望union的各个子句能够根据limit只取部分结果集，或者希望能够先排好序在合并结果集的话，就需要在union的各个子句中分别使用这些子句。例如，想将两个子查询结果联合起来，然后在取前20条，那么MySQL会将两个表都存放到一个临时表中，然后在去除前20行。

(select first_name,last_name from actor order by last_name) union all
(select first_name,last_name from customer order by  last_name) limit 20;

这条查询会将actor中的记录和customer表中的记录全部取出来放在一个临时表中，然后在取前20条，可以通过在两个子查询中分别加上一个limit 20来减少临时表中的数据。

(select first_name,last_name from actor order by last_name limit 20) union all
(select first_name,last_name from customer order by  last_name limit 20) limit 20;

现在中间的临时表只会包含40条记录了，处于性能考虑之外，这里还需要注意一点：从临时表中取出数据的顺序并不是一定，所以如果想获得正确的顺序，还需要在加上一个全局的order by操作

3 索引合并优化

前面文章中已经提到过，MySQL能够访问单个表的多个索引以合并和交叉过滤的方式来定位需要查找的行。

 

4 等值传递

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

那么优化器会将in()列表都赋值应用到关联的各个表中。通常，因为各个表新增了过滤条件，优化器可以更高效的从存储引擎过滤记录。但是如果这个列表非常大，则会导致优化和执行都会变慢

 

5 并行执行

MySQL无法利用多核特性来并行执行查询。很多其他的关系型数据库鞥能够提供这个特性，但MySQL做不到。这里特别指出是想提醒大家不要花时间去尝试寻找并行执行查询的方法。

 

6 哈希关联

在2013年MySQL并不执行哈希关联，MySQL的所有关联都是嵌套循环关联。不过可以通过建立一个哈希索引来曲线实现哈希关联如果使用的是Memory引擎，则索引都是哈希索引，所以关联的时候也类似于哈希关联。另外MariaDB已经实现了哈希关联。

 

7 松散索引扫描

由于历史原因，MySQL并不支持松散索引扫描，也就无法按照不连续的方式扫描一个索引。通常，MySQL的索引扫描需要先定义一个起点和重点，即使需要的数据只是这段索引中很少的几个，MySQL仍需要扫描这段索引中每个条目。

例：现有索引（a,b）

select * from table where b between 2 and 3;

因为索引的前导字段是a，但是在查询中只指定了字段b，MySQL无法使用这个索引，从而只能通过全表扫描找到匹配的行。

MySQL全表扫描：

了解索引的物理结构的话，不难发现还可以有一个更快的办法执行上面的查询。索引的物理结构不是存储引擎的API使得可以先扫描a列第一个值对应的b列的范围，然后在跳到a列第二个不同值扫描对应的b列的范围

这时就无需在使用where子句过滤，因为松散索引扫描已经跳过了所有不需要的记录。

上面是一个简单的例子，处理松散索引扫描，新增一个合适的索引当然也可以优化上述查询。但对于某些场景，增加索引是没用的，例如，对于第一个索引列是范围条件，第二个索引列是等值提交建查询，靠增加索引就无法解决问题。

MySQL5.6之后，关于松散索引扫描的一些限制将会通过索引条件吓退的分行是解决

8 最大值和最小值优化

https://blog.csdn.net/yongqi_wang/article/details/86527819