MySQL分优化之超大页查询

背景
基本上只要是做后台开发，都会接触到分页这个需求或者功能吧。基本上大家都是会用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();
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
在翻页比较少的情况下，LIMIT是不会出现任何性能上的问题的。

但是如果用户需要查到最后面的页数呢？

通常情况下，我们要保证所有的页面可以正常跳转，因为不会使用order by xxx desc这样的倒序SQL来查询后面的页数，而是采用正序顺序来做分页查询：

select * from t5 order by text limit 100000, 10;
1
在这里插入图片描述

采用这种SQL查询分页的话，从200万数据中取出这10行数据的代价是非常大的，需要先排序查出前1000010条记录，然后抛弃前面1000000条。我的macbook pro跑出来花了5.578秒。

接下来我们来看一下，上面这条SQL语句的执行计划：

explain select * from t5 order by text limit 1000000, 10;
1
在这里插入图片描述

从执行计划可以看出，在大分页的情况下，MySQL没有走索引扫描，即使text字段我已经加上了索引。

这是为什么呢？

回到MySQL索引（二）如何设计索引中有提及到，MySQL数据库的查询优化器是采用了基于代价的，而查询代价的估算是基于CPU代价和IO代价。

如果MySQL在查询代价估算中，认为全表扫描方式比走索引扫描的方式效率更高的话，就会放弃索引，直接全表扫描。