MySQL之查询优化器的局限性

向右看齐 2022-05-15 14:11 237阅读 0赞

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

### 文章目录 ###

*  关联子查询
 *  UNION的限制
 *  等值传递
 *  并发执行
 *  哈希关联
 *  松散索引扫描
 *  最大值和最小值优化
 *  在同一个表上查询和更新

# 关联子查询 #

*  MySQL的关联子查询实现得非常糟糕。最糟糕的一类查询时WHERE条件中包含IN()的子查询。
 *  IN()的优化包括：使用函数GROUP\_CONCAT()在IN()中构造一个由逗号分割的列表。或者使用关联查询JOIN。
 *  关联子查询并不总是性能很差的。我们应该经过测试做出自己的判断。
 *  我们一旦使用了DISTINCT和GROUP BY，那么在查询的执行过程中，通常会产生历史表。

# UNION的限制 #

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

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

这里要注意，从临时表中读出的顺序并不是一定的，所以如果希望获取正确的顺序，还需要加上全局的ORDER BY和LIMIT操作。

# 等值传递 #

有时候，等值传递会带来一些意想不到的消耗。例如，有一个非常大的IN()列表，而MySQL优化器当发现存在`WHERE、ON或者USING`的子句，将这个列表的值和另一个表的某个列向关联。则会导致优化和执行都会变慢。

# 并发执行 #

MySQL无法利用多核特性来并行执行查询。不要花时间去尝试寻找并行执行查询的方法。

# 哈希关联 #

MySQL并不支持哈希关联-MySQL的所有关联都是嵌套循环关联。

# 松散索引扫描 #

由于历史原因，MySQL并不支持松散索引扫描，也就无法按照不连续的方式扫描一个索引。通常，MySQL索引扫描需要指定一个起点和终点，即使需要的数据只是这段索引中很少数的几个，MySQL仍然需要扫描这段索引中的每一个条目。  
MySQL 5.0之后的版本，有些特殊的场景是可以使用松散索引扫描的。如，分组查询中的找到分组中的最大值和最小值：

mysql> EXPLAIN SELECT actor_id, MAX(film_id) FROM film_actor GROUP BY actor_id\G;

在EXPLAIN的Extra字段显示“Using index for group-by”，这表示这里将使用松散索引扫描。  
在MySQL可以很好的支持松散索引扫描之前，一个简单的绕过该问题的方法就是个前导列加上可能的常数值。

# 最大值和最小值优化 #

对于MIN()和MAX()查询，MySQL的优化做的不是很好。如下例子：

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

这里MySQL将使用全表扫描。理论上，如果MySQL能够使用主键扫描，当MySQL能够找到第一个满足条件的记录的时候，就是我们需要找到的最小值了，因为注解是严格按照actor\_id字段的大小排序的。优化如下：

mysql> SELECT actor_id FROM actor USE INDEX(PRIMARY) WHERE first_name = 'PENELOPE' LIMIT 1;

# 在同一个表上查询和更新 #

MySQL不允许对同一个表进行查询和更新。如下:

mysql> UPDATE tbl AS outer_tbl SET cnt = )(SELECT count(*) FROM tbl AS inner_tbl WHERE inner_tbl.type = outer_tbl.type);

这个查询将无法进行，可以通过使用生成临时表的形式来绕过这个限制，如

mysql> update tbl inner join (select type, count(*) as cnt from tbl group by type) as der using(type) SET tbl.cnt = der.cnt