MySQL查询优化器的局限性

关联子查询

关联子查询中的WHERE条件中的IN()子查询语句；一般会被改写成为，外部嵌套查询；建议使用内联优化。进行改进

UNION的限制

MySQL无法将限制条件从外层”下推”到内层。使得限制条件无法应用到内层查询的优化上面。

使用UNION时，应该尽量先对部分结果进行筛选，最后再进行结果集合的合并。

例如：

# 这条查询，会将两个集合查找20条记录进行，添加到临时表中；再筛选20条
(SELECT first_name,last_name FROM sakila.actor ORDER BY last_name) UNION ALL (SELECT first_name,last_name FROM sakila.customer ORDER BY last_name) LIMIT 20;
# 应当修改如下:

(SELECT first_name,last_name FROM sakila.actor ORDER BY last_name LIMIT 20) UNION ALL (SELECT first_name,last_name FROM sakila.customer ORDER BY last_name LIMIT 20) LIMIT 20;

索引合并优化

使用多个索引合并和交叉过滤的方式来定位需要查找的行。

等值传递

并行执行

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

哈希关联

MySQL所有的关联都是嵌套循环关联。但是可以通过建立哈希索引实现哈希关联

松散索引扫描

参考连接： mysql 松散索引与紧凑索引扫描（引入数据结构）

EXPLAIN中显示”Using index for group-by”,表示这里将使用松散索引扫描.其与全表扫描相对(紧凑索引)

select * from xxx where B = xxx group by A; 添加 group by 字段后，会先根据 A 索引分组后，会在每个 A 的范围内使用索引进行快速查询定位所需要的 B 列，这就叫做松散索引扫描，比新建一个索引的效率会慢 A 的 distinct 倍，但省去了新索引的消耗。

最大值和最小值优化

因此索引的元婴，对于最大和最小值；可以使用LIMIT来进行查找。例如:

# 原始语句需要，扫描整张表
SELECT MIN(actor_id) FROM sakila.actor WHERE first_name='PENLOPE';
# 显式使用索引，降低MySQL扫描数量
SELECT actor_id FROM sakila.actor USE INDEX(PRIMARY)
WHERE firts_name='PENLOPE' LIMIT 1;

在同一个表上查询和更新

MySQL不允许对同一张表同时进行查询和更新。但是可以通过使用临时表，来绕过上面的限制；例如:

# 下面的语句会出现错误
UPDATE tbl AS outer_tbl
    SET cnt=(
        SELECT count(*) FROM tbl AS inner_tbl
        WHERE inner_tbl.type=outer_tbl.type
    );
# 生成临时表并内联
UPDATE tbl
    INNER JOIN(
        SELECT type,count(*) AS cnt
        FROM tbl
        GROUP BY type
    ) AS der USING(type)
SET tbl.cnt=der.cnt;