SQL索引优化实战之分页查询与关联查询

一、建表及创建测试数据

a.建表SQL

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=100001 DEFAULT CHARSET=utf8 COMMENT='员工记录表';

b.创建10万条数据

drop procedure if exists insert_emp;
delimiter ;;
create procedure insert_emp()
begin
declare i int;
set i=1;
while(i<=100000)do
insert into employees(name,age,position) values(CONCAT('zhuge',i),i,'dev');
set i=i+1;
end while;
end;;
delimiter ;
call insert_emp();

二、分页查询优化

a.示例

select * from employees limit 90000,5;

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

1.优化技巧

• 根据自增且连续的主键排序的分页查询
  按照上面sql进行分页查询的逻辑（虽然查询5行记录，实际查询了90005行记录），那我们可不可以直接从90000行开始查询？
  当分页查询是按主键进行排序且主键自增并且连续时，可通过下面数据进行优化：

select * from employees where id > 90000 limit 5;

查询时间：0.001s
先添加查询条件，将范围缩小，在进行分页。实际只查询5行记录

• 先查询需要记录的id，再根据id关联查询其他记录

select * from employees order by name limit 90000,5;

查询时间：0.097s
我们先来研究这条语句：首先这个分页查询按name字段排序。
而employees表，创建了二个索引，一个主键id索引，一个联合索引(name,age,position)。
1.按照name进行排序分页，可以走联合索引。
2.但是由于查询所有字段的数据，联合索引叶子节点存储的都是主键值，想要获取所有字段的数据，则必须拿主键值再去走一遍主键索引。
3.而这张表总共10万条数据，走完联合索引的90005条记录后，再走一遍主键索引。这样还不如直接全表扫描速度快。
执行explain命令，查看这条sql是否走了索引：

如上图，mysql走得是全表扫描。
优化方法：
1.既然因为是查询所有字段的数据，导致它不得不再走一遍主键索引
2.那可不可以，先只查出90000到90005条的id，在通过id去走主键索引，取得所有字段的值。
3.虽然联合索引走了90005条，主键索引走了5条，但是好过上面的全表扫描（磁盘IO）
优化sql如下：

select * from employees a inner join (select id from employees order by name limit 90000,5) b on a.id = b.id;

查询时间：0.019s

三、关联查询优化

创建示例表

#创建两张表t1，t2 
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;

#t1表插入10000条记录
drop procedure if exists insert_emp;
delimiter ;;
create procedure insert_emp()
begin
declare i int;
set i=1;
while(i<=10000)do
insert into t1(id,a,b) values(i,i,i);
set i=i+1;
end while;
end;;
delimiter ;
call insert_emp();

#t2表插入100条记录
drop procedure if exists insert_emp;
delimiter ;;
create procedure insert_emp()
begin
declare i int;
set i=1;
while(i<=100)do
insert into t2(id,a,b) values(i,i,i);
set i=i+1;
end while;
end;;
delimiter ;
call insert_emp();

a.Nested-Loop Join （NLJ）算法

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

从执行计划中可以看到如上所示信息：

• 驱动表是t2，被驱动表是t1；先执行驱动表。mysql优化器一般会选择数据量小的作为驱动表，所以使用inner join时，排前排后不一定是驱动表
• 使用了 NLJ算法。一般 join 语句中，如果执行计划 Extra 中未出现 Using join buffer 则表示使用的 join 算法是 NLJ。
  上面sql执行逻辑大致为：
  1.从t2驱动表中拿出一条数据
  2.根据这条数据关联的字段a去t1表中取到结果合并，作为结果返回
  3.重复执行1,2

分析效率：

1. 上面sql语句需要查询所有字段，而表t1，t2的b字段不是索引，关联是通过a的索引字段进行关联
2. 需要先从驱动表t2中取出所有字段的一条数据，mysql直接从磁盘取出一条数据，经历一次磁盘IO（内存中的数据遍历及比较耗时远比磁盘的数据交互少，所以暂时不考虑），通过这条数据的关联字段a去被驱动表t1中查找，因为a为索引字段，可以走索引，找到关联的那条数据，取出ti表中所有字段的数据需要走一次磁盘IO
3. 依次进行。由此驱动表t2需要经过100次的磁盘IO，被驱动表也需要经历100次的磁盘IO。整个查询需要经历200次的磁盘IO。

b.Block Nested-Loop Join（BNL） 算法

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

Extra 中 的Using join buffer (Block Nested Loop)说明该关联查询使用的是 BNL 算法。
上面sql的大致流程如下：

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

分析效率：

1. 将t2表中所有的数据取出来放到join_buffer ，需要经历100次的磁盘IO
2. 把t1表中每一条取出来进行比对，需要经历10000次的磁盘IO
3. 整个执行过程经历了10100次的磁盘IO
   这种BNL算法比NLJ算法耗时多，为何不用NLJ算法呢？
   因为这条sql语句是通过非索引字段b进行关联的。按照NLJ算法，从t2表中拿一条数据去t1表中进行比较，由于比较字段是非索引，则需要对t1表一条条的从磁盘读取出来进行比较。t2表中的一条就要对t1进行10000次的磁盘IO，那100条，就需要100万次的磁盘IO，很显然比BNL算法要慢。

对于关联sql的优化

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