背景
基本上只要是做后台开发，都会接触到分页这个需求或者功能吧。基本上大家都是会用MySQL的LIMIT来处理，而且我现在负责的项目也是这样写的。但是一旦数据量起来了，其实LIMIT的效率会极其的低，这一篇文章就来讲一下LIMIT子句优化的。
LIMIT优化
很多业务场景都需要用到分页这个功能，基本上都是用LIMIT来实现。
建表并且插入200万条数据：
# 新建一张t5表
CREATE TABLE `t5` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
`text` varchar(100) NOT NULL,
PRIMARY KEY (`id`),
KEY `ix_name` (`name`),
KEY `ix_test` (`text`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
# 创建存储过程插入200万数据
CREATE PROCEDURE t5_insert_200w()
BEGIN
DECLARE i INT;
SET i=1000000;
WHILE i<=3000000 DO
INSERT INTO t5(`name`,text) VALUES('god-jiang666',concat('text', i));
SET i=i+1;
END WHILE;
END;
# 调用存储过程插入200万数据
call t5_insert_200w();
select * from t5 order by text limit 100000, 10;
采用这种SQL查询分页的话，从200万数据中取出这10行数据的代价是非常大的，需要先排序查出前1000010条记录，然后抛弃前面1000000条。所以非常慢。
从执行计划可以看出，在大分页的情况下，MySQL没有走索引扫描，即使text字段我已经加上了索引。
MySQL数据库的查询优化器是采用了基于代价的，而查询代价的估算是基于CPU代价和IO代价。
在5000这个临界点上，MySQL分别采用了索引扫描和全表扫描的查询优化方式。
所以可以认为MySQL会根据它自己的代价查询优化器来判断是否使用索引。
优化方式
1、使用覆盖索引（用具体查询字段代替*）
如果一条SQL语句，通过索引可以直接获取查询的结果，不再需要回表查询，就称这个索引为覆盖索引。
select id,text from t5 order by text limit 10000, 10;
在MySQL数据库中使用explain关键字查看执行计划，如果extra这一列显示Using index，就表示这条SQL语句使用了覆盖索引。
让我们来对比一下使用了覆盖索引，性能会提升多少吧。
image.png
- 没有索引覆盖：3.89秒
- 有索引覆盖：0.007
2、子查询优化
因为实际开发中，用SELECT查询一两列操作是非常少的，因此上述的覆盖索引的适用范围就比较有限。
所以我们可以通过把分页的SQL语句改写成子查询的方法获得性能上的提升。
select * from t5 where id>=(select id from t5 order by text limit 1000000, 1) limit 10;
先通过查第10000大的id，这个子查询有索引覆盖，然后查大于该id的前面10个，把所有字段查出来。
其实使用这种方法，提升的效率和上面使用了覆盖索引基本一致。
但是这种优化方法也有局限性：
- 这种写法，要求主键ID必须是连续的
- Where子句不允许再添加其他条件
3、延迟关联（inner join）
和上述的子查询做法类似，我们可以使用JOIN，先在索引列上完成分页操作，然后再回表获取所需要的列。
select a.* from t5 a inner join (select id from t5 order by text limit 1000000, 10) b on a.id=b.id;
先查第10000页前面10个的id，这里有索引覆盖，然后inner join 匹配id把所有字段查出来。
总结
反正一定要索引覆盖，也就是查询字段有id或建索引的字段，这样不再需要回表查询，非常快，然后通过子查询和inner join等方法把所有字段查出来。
网友评论