MySQL 优化篇 - 06 | SQL 优化实战 - 《DataBase》

分页查询优化
- 1、根据自增且连续的主键排序的分页查询
- 2、根据非主键字段排序的分页查询
Join 关联查询优化
- 1、嵌套循环连接 Nested-Loop Join（NLJ）算法
- 2、基于块的嵌套循环连接 Block Nested-Loop Join（BNL）算法
in 和 exsits 优化
COUNT 查询优化

CREATE TABLE `employees` (
 `id` int(11) NOT NULL AUTO_INCREMENT, 
 `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名', 
 `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄', 
 `position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位', 
 `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间', 
 PRIMARY KEY (`id`), 
 KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE 
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='员工记录表';
drop procedure if exists insert_emp;
delimiter ;; 
create procedure insert_emp() 
begin 
    declare i int; 
    set i=1; 
    while(i<=10000) do 
        insert into employees(name,age,position) values(CONCAT('eric',i),i,'dev'); 
        set i=i+1; 
    end while; 
end;; 
delimiter ; 
call insert_emp();

分页查询优化

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

select * from employees limit 10000, 10;

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

1、根据自增且连续的主键排序的分页查询

首先来看一个根据自增且连续主键排序的分页查询的例子：

select * from employees limit 9000, 5;

该 SQL 表示查询从第 9001开始的五行数据，没添加单独 order by，表示通过主键排序。我们再看表 employees ，因
为主键是自增并且连续的，所以可以改写成按照主键去查询从第 90001开始的五行数据，如下：

select * from employees where id > 9000 limit 5;

查询的结果是一致的。我们再对比一下执行计划：

EXPLAIN select * from employees limit 9000, 5;

EXPLAIN select * from employees where id > 9000 limit 5;

显然改写后的 SQL 走了索引，而且扫描的行数大大减少，执行效率更高。
但是，这条改写的 SQL 在很多场景并不实用，因为表中可能某些记录被删后，主键空缺，导致结果不一致，因此，如果主键不连续，不能使用上面描述的优化方法。另外如果原 SQL 是 order by 非主键的字段，按照上面说的方法改写会导致两条 SQL 的结果不一致。所以这种改写得满足以下两个条件：

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

2、根据非主键字段排序的分页查询

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

select * from employees ORDER BY name limit 9000, 5;

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

发现并没有使用 name 字段的索引（key 字段对应的值为 null），具体原因上节课讲过：扫描整个索引并查找到没索引
的行（可能要遍历多个索引树）的成本比扫描全表的成本更高，所以优化器放弃使用索引。
知道不走索引的原因，那么怎么优化呢？
其实关键是让排序时返回的字段尽可能少，所以可以让排序和分页操作先查出主键，然后根据主键查到对应的记录，SQL
改写如下

select * from employees e 
inner join (select id from employees order by name limit 9000, 5) e_temp 
on e.id = e_temp.id;

需要的结果与原 SQL 一致，执行时间减少了一半以上，我们再对比优化前后sql的执行计划：

原 SQL 使用的是 filesort 排序，而优化后的 SQL 使用的是索引排序。

Join 关联查询优化

CREATE TABLE `t1` ( 
    `id` int(11) NOT NULL AUTO_INCREMENT, 
    `a` int(11) DEFAULT NULL, 
    `b` int(11) DEFAULT NULL, 
    PRIMARY KEY (`id`), 
    KEY `idx_a` (`a`) 
) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8;
create table t2 like t1;
drop procedure if exists insert_t1;
delimiter ;; 
create procedure insert_t1() 
begin 
    declare i int; 
    set i=1; 
    while(i<=10000) do 
        insert into t1(a, b) values(i, i); 
        set i=i+1; 
    end while; 
end;; 
delimiter ; 
call insert_t1();
drop procedure if exists insert_t2;
delimiter ;; 
create procedure insert_t2() 
begin 
    declare i int; 
    set i=1; 
    while(i<=100) do 
        insert into t2(a, b) values(i, i); 
        set i=i+1; 
    end while; 
end;; 
delimiter ; 
call insert_t2();

MySQL 的表关联常见有两种算法

1、嵌套循环连接 Nested-Loop Join（NLJ）算法

一次一行循环地从第一张表（称为驱动表）中读取行，在这行数据中取到关联字段，根据关联字段在另一张表（被驱动
表）里取出满足条件的行，然后取出两张表的结果合集

EXPLAIN select * from t1 inner join t2 on t1.a= t2.a;

从执行计划中可以看到这些信息：
驱动表是 t2，被驱动表是 t1。先执行的就是驱动表（执行计划结果的 id 如果一样则按从上到下顺序执行 sql）；优化器一般会优先选择小表做驱动表。所以使用 inner join 时，排在前面的表并不一定就是驱动表。
使用了 NLJ 算法。一般 join 语句中，如果执行计划 Extra 中未出现 Using join buffer 则表示使用的 join 算法是 NLJ

上面 SQL 的大致流程如下：

从表 t2 中读取一行数据；
从第 1 步的数据中，取出关联字段 a，到表 t1 中查找；
取出表 t1 中满足条件的行，跟 t2 中获取到的结果合并，作为结果返回给客户端；
重复上面 1.2.3 步

整个过程会读取 t2 表的所有数据（全表扫描100行），然后遍历这每行数据中字段 a 的值，根据 t2 表中 a 的值索引扫描 t1 表
中的对应行（扫描100次 t1 表的索引，1 次扫描可以认为最终只扫描 t1 表一行完整数据，也就是总共 t1 表也扫描了100
行）。因此整个过程扫描了 200 行。
如果被驱动表的关联字段没索引，使用 NLJ 算法性能会比较低，MySQL 会选择 Block Nested-Loop Join 算法。

2、基于块的嵌套循环连接 Block Nested-Loop Join（BNL）算法

把驱动表的所有数据读入到 join_buffer 中，然后扫描被驱动表，把被驱动表每一行取出来跟 join_buffer 中的数据做对比

EXPLAIN select * from t1 inner join t2 on t1.b = t2.b;

Extra 中的 Using join buffer (Block Nested Loop) 说明该关联查询使用的是 BNL 算法

上面 SQL 的大致流程如下：

把 t2 的所有数据放入到 join_buffer 中
把 t1 中每一行取出来，跟 join_buffer 中的数据做对比
返回满足 join 条件的数据

整个过程对表 t1 和 t2 都做了一次全表扫描，因此扫描的总行数为 10000（t1 的总量）+ 100（t2 的总量）= 10100。并且 join_buffer 里的数据是无序的，因此对表 t1 中的每一行，都要做 100 次判断，所以内存中的判断次数是 100 * 10000= 100 万次（内存扫描）

被驱动表的关联字段没索引为什么要选择使用 BNL 算法而不使用 Nested-Loop Join 呢？
如果上面第二条 sql 使用 Nested-Loop Join，那么扫描行数为 100 10000 = 100万次，这个是*磁盘扫描。

很显然，用 BNL 磁盘扫描次数少很多，相比于磁盘扫描，BNL 的内存计算会快得多。
因此 MySQL 对于被驱动表的关联字段没索引的关联查询，一般都会使用 BNL 算法。如果有索引一般选择 NLJ 算法，有索引的情况下 NLJ 算法比 BNL 算法性能更高

对于关联 SQL 的优化

关联字段加索引，让 MySQL 做 join 操作时尽量选择 NLJ 算法
小表驱动大表，写多表连接 SQL 时如果明确知道哪张表是小表可以用 straight_join 写法固定连接驱动方式，省去 MySQL 优化器自己判断的时间

straight_join：功能同 join 类似，但能让左边的表来驱动右边的表，能改表优化器对于联表查询的执行顺序。
比如：select * from t2 straight_join t1 on t2.a = t1.a; 代表制定 MySQL 选着 t2 表作为驱动表
straight_join 只适用于 inner join，并不适用于 left join，right join（因为 left join，right join 已经代表指定了表的执行顺序）尽可能让优化器去判断，因为大部分情况下 MySQL 优化器是比人要聪明的。使用 straight_join 一定要慎重，因为部分情况下人为指定的执行顺序并不一定会比优化引擎要靠谱

in 和 exsits 优化

原则：小表驱动大表，即小的数据集驱动大的数据集
in：当 B 表的数据集小于 A 表的数据集时，in 优于 exists，（子查询小）

select * from A where id in (select id from B);
等价于
for(select id from B) {
    select * from A where A.id = B.id
}

exists：当 A 表的数据集小于 B 表的数据集时，exists 优于 in，（子查询大）
将主查询 A 的数据，放到子查询 B 中做条件验证，根据验证结果（true或false）来决定主查询的数据是否保留

select * from A where exists (select 1 from B where B.id = A.id);
等价于
for(select * from A) {
    select * from B where B.id = A.id
}

EXISTS（subquery）只返回 TRUE 或 FALSE ,因此子查询中的 SELECT * 也可以用 SELECT 1 替换，官方说法是实际执行时会忽略 SELECT 清单，因此没有区别
EXISTS 子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比
EXISTS 子查询往往也可以用 JOIN 来代替，何种最优需要具体问题具体分析

COUNT 查询优化

临时关闭 MySQL 查询缓存，为了查看 SQL 多次执行的真实时间

set global query_cache_size=0;
set global query_cache_type=0;

EXPLAIN select count(1) from employees;
EXPLAIN select count(id) from employees;
EXPLAIN select count(name) from employees; -- 不会计算 null 值
EXPLAIN select count(*) from employees;

四个 SQL 的执行计划一样，说明这四个 SQL 执行效率应该差不多，区别在于根据某个非主键的字段 count 不会统计字段为 null 值的数据行（这也是为什么不建议设计表的时候含有 null 值的原因）

为什么 MySQL 最终选择辅助索引而不是主键聚集索引？因为二级索引相对主键索引存储数据更少，检索性能应该更高

常见 COUNT 的优化方法（不含 where 条件）

1、查询 MySQL 自己维护的总行数

对于 MyISAM 存储引擎的表做不带 where 条件的 count 查询性能是很高的，因为 myisam 存储引擎的表的总行数会被
MySQL 直接存储在磁盘上，查询不需要计算

EXPLAIN select count(*) from test_myisam;

对于 innoDB 存储引擎的表 MySQL 不会存储表的总记录行数，查询 count 需要实时计算

2、使用 show table status 查询总数

如果只需要知道表总行数的估计值可以用如下 SQL 查询，性能很高

show table status like 'employees';

3、将总数维护到 Redis 里

插入或删除表数据行的时候同时维护 redis 里的表总行数 key 的计数值（用 incr 或 decr 命令），但是这种方式可能不准，很难
保证 insert 操作和 redis 操作的事务一致性

4、增加计数表

插入或删除表数据行的时候同时维护计数表，让他们在同一个事务里操作

06 | SQL 优化实战