【学习笔记】mysql查询优化器的局限性

本文属于读书笔记，大部分内容摘抄于《高性能MYSQL》，摘抄内容版权属于原作者。

mysql的万能“嵌套循环”并不是对每种查询都是最优的。不过还好，mysql查询优化器只对少部分查询不适用，而且我们往往可以通过改写查询让mysql高效的完成工作。而5.6以后会消除很多mysql原本的限制，让更多的查询能够以尽可能高的效率完成。

关联子查询

mysql的子查询实现的是很糟糕的。最糟糕的一类查询时where条件中包含IN()的子查询语句。

UNION的限制

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

如果希望UNION的各个子句能够根据LIMIT只取部分结果集，或者希望能够先排好序再合并结果集的话，就需要在UNION的各个自剧中分别使用这些子句。

索引合并优化

在5.0和更新版本中，当where子句中包涵多个复杂条件的时候，mysql能够访问单个表的多个索引以合并和交叉过滤的方式来定位需要查找的行。

等值传递

某些时候，等值传递会带来一些意想不到的额外消耗。例如，有一个非常大的IN()列表，而mysql优化器发现存在WHERE、ON或者USING的子句，将这个列表的值和另一个列表的某个列关联。

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

并行执行

mysql无法利用多核特性来并行执行查询。很多其他的关系型数据库能够提供这个特性，但是mysql做不到。

哈希关联

到目前为止，mysql并不支持哈希关联——mysql的所有关联都是嵌套循环关联。不过可以通过建立一个哈希索引来曲线地实现哈希关联。

松散索引扫描

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

最大值和最小值优化

对于min()和max()查询，mysql的优化做的并不好。有一个很明显的例子：

    
    
mysql> SELECT MIN(actor_id) FROM sakila.actor WHERE first_name='PENELOPE'

因为在first_name字段上没有索引，因此mysql将会进行一次全表扫描。如果mysql能够进行主键扫面，那么理论上，当mysql读到第一个满足条件的记录的时候就是我们需要找的最小值了，因为主键是严格感召actor_id字段的大小顺序排序的。但是mysql这时只会做全表扫描。一个曲线的优化方法是移除min()，然后使用limit来将查询重写如下：

    
    
mysql> SELECT actor_id FROM sakila.actor USE INDEX(PRIMARY)
    -> WHERE first_name = 'PENELOPE' limit 1;

这个策略可以让mysql扫描尽可能少的数据记录。

在同一个表上查询和更新

mysql不容许对同一张彪同事进行查询和更新。这其实并不是优化器的限制，如果清楚mysql是如何执行查询的，就可以避免这种情况。下面是一个无法运行的sql，虽然这是一个符合标注的sql语句。这个sql语句尝试将两个表中相似的行的数量记录到字段cnt中

    
    
mysql> UPDATE tb1 AS outer_tb1
    ->    SET cnt = (
    ->       SELECT count(*) FROM tb1 AS inner_tb1
    ->       WHERE inner_tb1.type = outer_tb1.type
    ->    ); 

可以通过生成表的形式绕过上面的限制，因为mysql只会把这个表当做一个临时表来处理。实际上，这执行了两个查询：一个是子查询中的select语句，另一个是夺标关联update，只是关联表的一个临时表。子查询会在UPDATE语句打开表之前就完成，所以下面的查询将会正常执行：

    
    
mysql> UPDATE tb1
    ->    INNER JOIN (
    ->       SELECT type, count(*) AS cnt
    ->       FROM tb1
    ->       GROUP BY type
    ->    ) AS der USING(type)
    -> SET tb1.cnt = der.cnt