分页查询、关联查询、in/exists和count优化

目录

1、分页查询优化

（1）根据自增且连续的主键排序的分页查询优化

（2）根据非主键字段排序的分页查询优化

2、关联查询优化

（1）嵌套循环连接 Nested-Loop Join(NLJ) 算法(关联字段有索引)

（2）基于块的嵌套循环连接 Block Nested-Loop Join(BNL)算法(关联字段无索引)

（3）对于关联 sql 的优化

3、in和exists优化

4、count(*)查询优化

---

1、分页查询优化

// 'employees' 建表结构同《Mysql explain 索引优化案例》，提前向表中补了10万条数据。

很多时候我们业务系统实现分页功能可能会用如下sql实现

--分页
select * from employees limit 10000,10;

表示从表 'employees' 中取出从 10001 行开始的 10 行记录。看似只查询了 10 条记录，实际这条 SQL 是先读取 10010 条记录，然后抛弃前 10000 条记录，然后读到后面 10 条想要的数据。因此要查询一张大表比较靠后的数据，执行效率非常低。

（1）根据自增且连续的主键排序的分页查询优化

前提：表中数据主键是自增并且连续的

-- 优化前
select * from employees limit 90000,5;

-- 优化后
select * from employees where id > 90000 limit 5;

但是，这条改写的SQL 在很多场景并不实用，因为表中可能某些记录被删后，主键空缺，导致查询的结果不一致。

另外如果原 SQL 是 order by 非主键的字段，按照上面说的方法改写也会导致两条 SQL 的结果不一致。所以这种改写得满足以下两个条件：

• 主键自增且连续
• 结果是按照主键排序的

（2）根据非主键字段排序的分页查询优化

根据非主键字段排序的分页查询，SQL 如下：

select * from employees ORDER BY name limit 90000,5;
EXPLAIN select * from employees ORDER BY name limit 90000,5;

发现并没有使用 name 字段的索引(key 字段对应的值为 null)，具体原因：扫描整个索引并查找到没索引的行(可能要遍历多个索引树)的成本比扫描全表的成本更高，所以优化器放弃使用索引。

知道不走索引的原因，那么怎么优化呢?

关键是让排序时返回的字段尽可能少，所以可以让排序和分页操作先查出主键，然后根据主键查到对应的记录，SQL改写如下

select * from employees e inner join (select id from employees order by name limit 90000,5) ed on e.id = ed.id;
-- 分析
EXPLAIN select * from employees e inner join (select id from employees order by name limit 90000,5) ed on