在介绍深分页场景下的查询性能问题之前，我会先跟你解释一下什么是分页查询，什么是深分页，然后我会跟你分析一下深分页场景下为什么查询性能会这么差，最后再提供几种可行的解决方案供你参考。
1、什么是分页查询
为了让你有直观的感受，我先举几个例子再给你解释概念：
分页查询指的是将一个比较大的数据集分成多个小块（即“页”），并在用户请求时只返回用户需要的那一页的数据。既方便用户查看结果，也能以减少数据传输和处理的时间和资源消耗。
分页查询通常需要指定每页要显示的记录数量（Page Size）和要查询的目标页数（Page）。在进行分页查询时，通常会使用 LIMIT offset, pageSize 的方式来指定要查询的记录范围。
例如，如果要查询第 3 页（Page=3），每页显示 10 条记录（PageSize=10），那么可以使用以下 SQL 查询语句：
SELECT *
FROM table_name
LIMIT 20, 1020 就是 offset（偏移量），表示从第 21 条开始查。
Page 与 offset 的关系为：offset = (Page - 1) * PageSize
SELECT *
FROM table_name
LIMIT (Page - 1) * PageSize, PageSize2、什么是深分页查询
深分页查询指的就是在分页查询中，页码（Page）比较大的情况。
如果要查询第 500000 页（Page=500000），每页显示 10 条记录（Page Size=10），那么 SQL 查询语句要这么写：
SELECT *
FROM table_name
LIMIT 500000, 10;3、模拟实验
下面我会以 MySQL 5.7.37 作为实验环境，生成一张千万级数据量的大表，用于分析和验证深分页查询的性能问题。
表结构
DROP TABLE IF EXISTS `people`;
CREATE TABLE `people` (
`id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` varchar(16) NOT NULL COMMENT '姓名',
`age` tinyint(4) UNSIGNED NOT NULL COMMENT '年龄',
`city` varchar(16) NOT NULL COMMENT '城市',
`addr` varchar(128) DEFAULT NULL COMMENT '详细住址',
`sfz` char(18) DEFAULT NULL COMMENT '身份证号',
`status` tinyint(4) DEFAULT 1 COMMENT '状态: 1:xx 0:oo',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`modified_time` datetime DEFAULT NULL COMMENT '修改时间',
`delete_time` datetime DEFAULT NULL COMMENT '删除时间',
PRIMARY KEY (`id`),
KEY `city` (`city`)
) ENGINE=InnoDB;存储过程
利用存储过程向 people 表填充 1000 万条记录：
DROP PROCEDURE IF EXISTS generate_people;
DELIMITER //
CREATE PROCEDURE generate_people()
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE name_length INT;
DECLARE first_name VARCHAR(2);
DECLARE last_name VARCHAR(4);
DECLARE age INT;
DECLARE city VARCHAR(16);
DECLARE addr VARCHAR(128);
DECLARE sfz char(18);
DECLARE status INT;
DECLARE create_time DATETIME;
DECLARE modified_time DATETIME;
DECLARE delete_time DATETIME DEFAULT NULL;
WHILE i <= 10000000 DO
SET name_length = ROUND(RAND() * 1) + 2;
SET first_name = SUBSTRING('赵钱孙李周吴郑王冯陈唐卫蒋沈韩杨朱秦尤许何吕施张孔曹严华金魏陶姜董谢邹丁柏水薛章云苏潘葛石范彭郎鲁韦昌马苗凤花方俞任袁柳乔岑程邱秋顾梅盛林刁钟徐高夏蔡田姚谭纪程', FLOOR(RAND() * 49) + 1, 1);
SET last_name = SUBSTRING('一二三四五六七八九十', FLOOR(RAND() * 10) + 1, name_length - 1);
SET age = FLOOR(RAND() * 100) + 1;
SET city = CASE FLOOR(RAND() * 10)
WHEN 0 THEN '北京'
WHEN 1 THEN '上海'
WHEN 2 THEN '广州'
WHEN 3 THEN '深圳'
WHEN 4 THEN '重庆'
WHEN 5 THEN '天津'
WHEN 6 THEN '苏州'
WHEN 7 THEN '南京'
WHEN 8 THEN '杭州'
WHEN 9 THEN '成都'
END;
SET addr = CONCAT(city, '市',
CASE FLOOR(RAND() * 4)
WHEN 0 THEN '东'
WHEN 1 THEN '南'
WHEN 2 THEN '西'
WHEN 3 THEN '北'
END, '区',
CASE FLOOR(RAND() * 4)
WHEN 0 THEN '东'
WHEN 1 THEN '南'
WHEN 2 THEN '西'
WHEN 3 THEN '北'
END, '县',
CASE FLOOR(RAND() * 4)
WHEN 0 THEN '东'
WHEN 1 THEN '南'
WHEN 2 THEN '西'
WHEN 3 THEN '北'
END, '镇',
CASE FLOOR(RAND() * 10)
WHEN 0 THEN 'A'
WHEN 1 THEN 'B'
WHEN 2 THEN 'C'
WHEN 3 THEN 'D'
WHEN 4 THEN 'E'
WHEN 5 THEN 'F'
WHEN 6 THEN 'G'
WHEN 7 THEN 'H'
WHEN 8 THEN 'I'
WHEN 9 THEN 'J'
END, '小区',
FLOOR(RAND() * 20) + 1, '号楼',
FLOOR(RAND() * 20) + 1, '单元',
FLOOR(RAND() * 5) + 1, '0', FLOOR(RAND() * 3) + 1, '室');
SET sfz = LPAD(FLOOR(RAND() * POWER(10, 18)), 18, '0');
SET status = CASE FLOOR(RAND() * 10)
WHEN 0 THEN 0
ELSE 1
END;
SET create_time = FROM_UNIXTIME(UNIX_TIMESTAMP('2017-01-01') + FLOOR(RAND() * (UNIX_TIMESTAMP('2022-01-01') - UNIX_TIMESTAMP('2017-01-01'))));
INSERT INTO people(name, age, city, addr,sfz, status, create_time, modified_time, delete_time) VALUES (
CONCAT(first_name, last_name),
age,
city,
addr,
sfz,
status,
create_time,
modified_time,
delete_time
);
SET i = i + 1;
END WHILE;
END //
DELIMITER ;
call generate_people();样例数据
生成的数据格式如下：
mysql> select * from people limit 10;
+----+--------+-----+------+------------------------------------------+--------------------+--------+---------------------+---------------+-------------+
| id | name | age | city | addr | sfz | status | create_time | modified_time | delete_time |
+----+--------+-----+------+------------------------------------------+--------------------+--------+---------------------+---------------+-------------+
| 1 | 柏四 | 46 | 广州 | 广州市北区西县东镇H小区20号楼8单元102室 | 887156682915200300 | 0 | 2020-05-23 00:06:26 | NULL | NULL |
| 2 | 葛十 | 95 | 成都 | 成都市北区北县东镇D小区5号楼4单元101室 | 040055245198360870 | 1 | 2018-05-31 03:26:26 | NULL | NULL |
| 3 | 鲁三四 | 46 | 重庆 | 重庆市西区东县南镇E小区3号楼10单元502室 | 096575767823099870 | 1 | 2017-07-06 22:51:28 | NULL | NULL |
| 4 | 李四五 | 53 | 天津 | 天津市南区西县西镇A小区11号楼7单元102室 | 525526125473460350 | 1 | 2019-03-13 08:39:35 | NULL | NULL |
| 5 | 魏九 | 62 | 重庆 | 重庆市东区西县东镇E小区17号楼16单元303室 | 131008927832365890 | 1 | 2019-03-13 22:06:00 | NULL | NULL |
| 6 | 冯十 | 19 | 上海 | 上海市东区北县北镇E小区3号楼6单元101室 | 050078685442059010 | 1 | 2020-11-06 12:50:09 | NULL | NULL |
| 7 | 冯六 | 39 | 上海 | 上海市西区东县东镇A小区4号楼14单元501室 | 965394272436755100 | 1 | 2017-03-12 09:17:11 | NULL | NULL |
| 8 | 孙十 | 49 | 苏州 | 苏州市北区东县西镇E小区7号楼8单元502室 | 274487027222744200 | 1 | 2021-11-27 00:19:23 | NULL | NULL |
| 9 | 冯六 | 32 | 成都 | 成都市西区东县西镇E小区1号楼17单元103室 | 293914334190929600 | 1 | 2021-01-07 09:18:50 | NULL | NULL |
| 10 | 陶七八 | 62 | 成都 | 成都市北区东县南镇F小区19号楼15单元502室 | 480335228592469570 | 0 | 2020-12-03 06:09:14 | NULL | NULL |
+----+--------+-----+------+------------------------------------------+--------------------+--------+---------------------+---------------+-------------+
10 rows in set (0.00 sec)4、深分页会有什么问题
因为 MySQL 需要扫描大量数据才能找到指定页的数据，Page 越大，查询耗时就越久。
mysql> select count(*) from people;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (0.87 sec)
mysql> select * from people order by modified_time limit 1000001,10;
+---------+-----------+-----+--------+----------------------------------------------------------+--------------------+--------+---------------------+---------------+-------------+
| id | name | age | city | addr | sfz | status | create_time | modified_time | delete_time |
+---------+-----------+-----+--------+----------------------------------------------------------+--------------------+--------+---------------------+---------------+-------------+
| 9646445 | 施四五 | 49 | 深圳 | 深圳市东区西县西镇I小区5号楼8单元103室 | 113222168863957900 | 1 | 2020-10-05 11:50:32 | NULL | NULL |
| 9646446 | 沈九十 | 41 | 天津 | 天津市南区东县南镇D小区19号楼11单元402室 | 370280362079181100 | 1 | 2017-06-17 15:46:28 | NULL | NULL |
| 9646447 | 何三四 | 1 | 广州 | 广州市南区北县东镇A小区17号楼2单元502室 | 993024772026599200 | 1 | 2017-08-04 00:35:23 | NULL | NULL |
| 9646448 | 韩八九 | 65 | 北京 | 北京市西区南县东镇F小区9号楼9单元503室 | 924734121118424600 | 1 | 2017-11-02 10:28:07 | NULL | NULL |
| 9646449 | 苏七八 | 75 | 南京 | 南京市西区南县西镇H小区14号楼8单元403室 | 461758114827571600 | 0 | 2020-12-07 22:55:43 | NULL | NULL |
| 9646450 | 章五六 | 5 | 南京 | 南京市西区北县北镇H小区2号楼20单元402室 | 832649107866593800 | 0 | 2021-10-23 15:22:42 | NULL | NULL |
| 9646451 | 薛四五 | 25 | 广州 | 广州市西区南县西镇D小区13号楼4单元103室 | 315378812435435840 | 1 | 2017-05-02 16:59:44 | NULL | NULL |
| 9646452 | 何四五 | 54 | 天津 | 天津市南区西县东镇H小区9号楼18单元101室 | 874870933475784000 | 1 | 2021-06-02 11:47:43 | NULL | NULL |
| 9646453 | 石九十 | 44 | 苏州 | 苏州市北区北县南镇G小区1号楼1单元102室 | 545967089520736700 | 1 | 2019-12-22 16:45:57 | NULL | NULL |
| 9646454 | 尤九 | 94 | 广州 | 广州市东区西县东镇A小区13号楼4单元503室 | 240246860534182620 | 1 | 2019-07-01 20:42:31 | NULL | NULL |
+---------+-----------+-----+--------+----------------------------------------------------------+--------------------+--------+---------------------+---------------+-------------+
10 rows in set (11.55 sec)说到问题，我再补充一个：如果 order by 排序字段存在重复值，那么在分页查询中可能会出现重复数据的情况。
为了避免这种情况，可以在 SQL 查询语句中使用唯一的排序字段来进行排序，或者在 SQL 查询语句中增加一个唯一的排序条件，例如使用 id 字段进行排序，这样可以确保每条记录在排序后都是唯一的。
5、解决方案
5.1 SQL优化 - 子查询 & 索引
思路：通过将 select * 转变为 select id，把符合条件的 id 筛选出来后，最后通过嵌套查询的方式按顺序取出 id 对应的行。
-- 优化前
select *
from people
order by create_time desc
limit 5000000, 10;
-- 优化后
select a.*
from people a
inner join(
select id
from people
order by create_time desc
limit 5000000, 10
) b ON a.id = b.id;下面我们看一下优化前后的执行计划和耗时：
-- 优化前
mysql> explain
-> select *
-> from people
-> order by create_time desc
-> limit 5000000, 10;
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+----------------+
| 1 | SIMPLE | people | NULL | ALL | NULL | NULL | NULL | NULL | 9088146 | 100.00 | Using filesort |
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+----------------+
1 row in set, 1 warning (0.00 sec)
mysql> select *
-> from people
-> order by create_time desc
-> limit 5000000, 10;
+---------+-----------+-----+--------+----------------------------------------------------------+--------------------+--------+---------------------+---------------+-------------+
| id | name | age | city | addr | sfz | status | create_time | modified_time | delete_time |
+---------+-----------+-----+--------+----------------------------------------------------------+--------------------+--------+---------------------+---------------+-------------+
| 3686078 | 苏二 | 15 | 深圳 | 深圳市东区北县北镇B小区13号楼18单元303室 | 060202373247782120 | 1 | 2019-07-02 08:01:57 | NULL | NULL |
| 8924378 | 陈三四 | 52 | 成都 | 成都市东区西县北镇D小区3号楼14单元502室 | 519416874758356160 | 1 | 2019-07-02 08:01:57 | NULL | NULL |
| 8890178 | 苏二 | 15 | 深圳 | 深圳市东区北县北镇B小区13号楼18单元303室 | 060202373247782120 | 1 | 2019-07-02 08:01:57 | NULL | NULL |
| 9614078 | 范一 | 47 | 上海 | 上海市南区西县东镇I小区11号楼4单元203室 | 903102675362585700 | 1 | 2019-07-02 08:01:57 | NULL | NULL |
| 1819563 | 姜八 | 70 | 广州 | 广州市南区东县东镇G小区18号楼6单元502室 | 430822781688368700 | 1 | 2019-07-02 07:59:01 | NULL | NULL |
| 7023663 | 姜八 | 70 | 广州 | 广州市南区东县东镇G小区18号楼6单元502室 | 430822781688368700 | 1 | 2019-07-02 07:59:01 | NULL | NULL |
| 5161143 | 沈三 | 34 | 成都 | 成都市北区南县西镇F小区4号楼4单元202室 | 576518145926779300 | 1 | 2019-07-02 07:59:01 | NULL | NULL |
| 6165726 | 沈七八 | 41 | 北京 | 北京市东区南县南镇J小区11号楼15单元102室 | 795161817032063200 | 1 | 2019-07-02 07:51:28 | NULL | NULL |
| 8422926 | 云一二 | 80 | 杭州 | 杭州市北区南县南镇D小区2号楼7单元202室 | 580660306458045000 | 1 | 2019-07-02 07:51:28 | NULL | NULL |
| 7248726 | 章七 | 94 | 苏州 | 苏州市西区西县东镇E小区11号楼5单元403室 | 212080246035084350 | 1 | 2019-07-02 07:51:28 | NULL | NULL |
+---------+-----------+-----+--------+----------------------------------------------------------+--------------------+--------+---------------------+---------------+-------------+
10 rows in set (8.44 sec)
-- 优化后
mysql> explain
-> select a.*
-> from people a
-> inner join(
-> select id
-> from people
-> order by create_time desc
-> limit 5000000, 10
-> ) b ON a.id = b.id;
+----+-------------+------------+------------+--------+---------------+---------+---------+------+---------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+--------+---------------+---------+---------+------+---------+----------+----------------+
| 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 5000010 | 100.00 | NULL |
| 1 | PRIMARY | a | NULL | eq_ref | PRIMARY | PRIMARY | 8 | b.id | 1 | 100.00 | NULL |
| 2 | DERIVED | people | NULL | ALL | NULL | NULL | NULL | NULL | 9088146 | 100.00 | Using filesort |
+----+-------------+------------+------------+--------+---------------+---------+---------+------+---------+----------+----------------+
mysql> select a.*
-> from people a
-> inner join(
-> select id
-> from people
-> order by create_time desc
-> limit 5000000, 10
-> ) b ON a.id = b.id;
+---------+-----------+-----+--------+----------------------------------------------------------+--------------------+--------+---------------------+---------------+-------------+
| id | name | age | city | addr | sfz | status | create_time | modified_time | delete_time |
+---------+-----------+-----+--------+----------------------------------------------------------+--------------------+--------+---------------------+---------------+-------------+
| 156398 | 水一二 | 94 | 天津 | 天津市北区西县东镇F小区18号楼17单元303室 | 766290686806934500 | 1 | 2019-07-02 08:01:57 | NULL | NULL |
| 344498 | 朱一 | 25 | 北京 | 北京市西区东县东镇F小区10号楼15单元201室 | 464175883181556900 | 1 | 2019-07-02 08:01:57 | NULL | NULL |
| 378698 | 谢二三 | 62 | 苏州 | 苏州市西区西县北镇H小区20号楼11单元403室 | 923390384692131000 | 1 | 2019-07-02 08:01:57 | NULL | NULL |
| 1068398 | 何十 | 57 | 成都 | 成都市北区北县南镇C小区8号楼1单元101室 | 307076185296360600 | 1 | 2019-07-02 08:01:57 | NULL | NULL |
| 5161143 | 沈三 | 34 | 成都 | 成都市北区南县西镇F小区4号楼4单元202室 | 576518145926779300 | 1 | 2019-07-02 07:59:01 | NULL | NULL |
| 7023663 | 姜八 | 70 | 广州 | 广州市南区东县东镇G小区18号楼6单元502室 | 430822781688368700 | 1 | 2019-07-02 07:59:01 | NULL | NULL |
| 1819563 | 姜八 | 70 | 广州 | 广州市南区东县东镇G小区18号楼6单元502室 | 430822781688368700 | 1 | 2019-07-02 07:59:01 | NULL | NULL |
| 2608926 | 韩一二 | 27 | 广州 | 广州市南区东县南镇I小区2号楼16单元401室 | 163741877455023940 | 1 | 2019-07-02 07:51:28 | NULL | NULL |
| 2044626 | 章七 | 94 | 苏州 | 苏州市西区西县东镇E小区11号楼5单元403室 | 212080246035084350 | 1 | 2019-07-02 07:51:28 | NULL | NULL |
| 8422926 | 云一二 | 80 | 杭州 | 杭州市北区南县南镇D小区2号楼7单元202室 | 580660306458045000 | 1 | 2019-07-02 07:51:28 | NULL | NULL |
+---------+-----------+-----+--------+----------------------------------------------------------+--------------------+--------+---------------------+---------------+-------------+
10 rows in set (3.40 sec)可以看到查询性能明显提升了，但是看到这儿你要注意一个细节，people 表只有 city 这一个二级索引。
现在我们给 create_time 字段加一个索引：
ALTER TABLE `people` ADD INDEX `create_time` (`create_time`);再看一下执行计划和查询耗时：
mysql> explain
-> select a.*
-> from people a
-> inner join(
-> select id
-> from people
-> order by create_time desc
-> limit 5000000, 10
-> ) b ON a.id = b.id;
+----+-------------+------------+------------+--------+---------------+-------------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+--------+---------------+-------------+---------+------+---------+----------+-------------+
| 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 5000010 | 100.00 | NULL |
| 1 | PRIMARY | a | NULL | eq_ref | PRIMARY | PRIMARY | 8 | b.id | 1 | 100.00 | NULL |
| 2 | DERIVED | people | NULL | index | NULL | create_time | 6 | NULL | 5000010 | 100.00 | Using index |
+----+-------------+------------+------------+--------+---------------+-------------+---------+------+---------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)
mysql> select a.*
-> from people a
-> inner join(
-> select id
-> from people
-> order by create_time desc
-> limit 5000000, 10
-> ) b ON a.id = b.id;
+---------+-----------+-----+--------+----------------------------------------------------------+--------------------+--------+---------------------+---------------+-------------+
| id | name | age | city | addr | sfz | status | create_time | modified_time | delete_time |
+---------+-----------+-----+--------+----------------------------------------------------------+--------------------+--------+---------------------+---------------+-------------+
| 1068398 | 何十 | 57 | 成都 | 成都市北区北县南镇C小区8号楼1单元101室 | 307076185296360600 | 1 | 2019-07-02 08:01:57 | NULL | NULL |
| 378698 | 谢二三 | 62 | 苏州 | 苏州市西区西县北镇H小区20号楼11单元403室 | 923390384692131000 | 1 | 2019-07-02 08:01:57 | NULL | NULL |
| 344498 | 朱一 | 25 | 北京 | 北京市西区东县东镇F小区10号楼15单元201室 | 464175883181556900 | 1 | 2019-07-02 08:01:57 | NULL | NULL |
| 156398 | 水一二 | 94 | 天津 | 天津市北区西县东镇F小区18号楼17单元303室 | 766290686806934500 | 1 | 2019-07-02 08:01:57 | NULL | NULL |
| 7023663 | 姜八 | 70 | 广州 | 广州市南区东县东镇G小区18号楼6单元502室 | 430822781688368700 | 1 | 2019-07-02 07:59:01 | NULL | NULL |
| 5161143 | 沈三 | 34 | 成都 | 成都市北区南县西镇F小区4号楼4单元202室 | 576518145926779300 | 1 | 2019-07-02 07:59:01 | NULL | NULL |
| 1819563 | 姜八 | 70 | 广州 | 广州市南区东县东镇G小区18号楼6单元502室 | 430822781688368700 | 1 | 2019-07-02 07:59:01 | NULL | NULL |
| 8422926 | 云一二 | 80 | 杭州 | 杭州市北区南县南镇D小区2号楼7单元202室 | 580660306458045000 | 1 | 2019-07-02 07:51:28 | NULL | NULL |
| 7813026 | 韩一二 | 27 | 广州 | 广州市南区东县南镇I小区2号楼16单元401室 | 163741877455023940 | 1 | 2019-07-02 07:51:28 | NULL | NULL |
| 7248726 | 章七 | 94 | 苏州 | 苏州市西区西县东镇E小区11号楼5单元403室 | 212080246035084350 | 1 | 2019-07-02 07:51:28 | NULL | NULL |
+---------+-----------+-----+--------+----------------------------------------------------------+--------------------+--------+---------------------+---------------+-------------+
10 rows in set (0.73 sec)你会发现速度更快了。
在分析优化效果之前，我得先跟你介绍一下什么是 filesort。
在给 create_time 字段加索引之前，你会看到 SQL 语句的执行计划的 Extra 字段是 “Using filesort”，表示需要排序。
排序这个动作，可能在内存中完成，也可能需要使用外部排序，这取决于排序所需的内存和参数 sort_buffer_size，就是 MySQL 给每个线程用于排序而开辟的内存（sort_buffer）的大小。如果要排序的数据量小于 sort_buffer_size，排序就在内存中完成。但如果数据量太大，内存放不下，就要使用外部排序（利用磁盘临时文件辅助排序，也就是 filesort ）。
外部排序一般使用归并排序算法。可以这么简单理解，MySQL 将需要排序的数据分成 N 份，每一份单独排序后存在这些临时文件中，然后把这 N 个有序文件再合并成一个有序的大文件。sort_buffer_size 越小，需要分成的份数越多，使用的磁盘临时文件数量就越多，排序耗时就越久，查询性能就越差。
了解了什么是 filesort，我们再来分析一下上面的两次优化：
- 使用子查询
我们通过子查询的方式，把 select * 转化为 select id，先把符合条件的记录 id 查出来，再根据 id 去查完整的记录，大大减少了结果集的数据量，降低了排序操作消耗的资源，从查询性能上来看，是有明显提高的。
但是，从执行计划的分析结果来看，仍然使用了 filesort
2. 给create_time 加索引
给 create_time 加上索引之后，MySQL 会生成一颗 create_time 的 B+ 索引树，你知道它本身就是有序存储的，并且我们使用的是 select id，MySQL 可以直接从 create_time 这个索引上取出来，所以也不会有回表的额外性能损耗。
因此 MySQL 只需要按顺序扫描这颗索引树，取得 id 字段后即可，既规避了全表扫描，也不需要对结果集排序，效率是非常高的。
5.2 SQL优化 - 联合索引
刚才我们优化后的 SQL 语句如下，是没有 where 条件的。
select a.*
from people a
inner join(
select id
from people
order by create_time desc
limit 5000010, 10
) b ON a.id = b.id;为了更接近现实场景，假设我们要把 status = 1 的人筛出来，SQL 就要这么写：
select a.*
from people a
inner join(
select id
from people
where status=1
order by create_time desc
limit 5000010, 10
) b ON a.id = b.id;
select a.*
from people a
inner join(
select id
from people
where status=1
order by create_time desc
limit 5000010, 10
) b ON a.id = b.id;在继续往下看之前，你可以自行分析一下它的执行计划，预估一下它的查询耗时。
现在我们再看一下它的执行计划和查询耗时：
mysql> explain
-> select a.*
-> from people a
-> inner join(
-> select id
-> from people
-> where status=1
-> order by create_time desc
-> limit 5000010, 10
-> ) b ON a.id = b.id;
+----+-------------+------------+------------+--------+---------------+---------+---------+------+---------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+--------+---------------+---------+---------+------+---------+----------+-----------------------------+
| 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 908814 | 100.00 | NULL |
| 1 | PRIMARY | a | NULL | eq_ref | PRIMARY | PRIMARY | 8 | b.id | 1 | 100.00 | NULL |
| 2 | DERIVED | people | NULL | ALL | NULL | NULL | NULL | NULL | 9088146 | 10.00 | Using where; Using filesort |
+----+-------------+------------+------------+--------+---------------+---------+---------+------+---------+----------+-----------------------------+
3 rows in set, 1 warning (0.00 sec)
mysql> select a.*
-> from people a
-> inner join(
-> select id
-> from people
-> where status=1
-> order by create_time desc
-> limit 5000010, 10
-> ) b ON a.id = b.id;
+---------+-----------+-----+--------+----------------------------------------------------------+--------------------+--------+---------------------+---------------+-------------+
| id | name | age | city | addr | sfz | status | create_time | modified_time | delete_time |
+---------+-----------+-----+--------+----------------------------------------------------------+--------------------+--------+---------------------+---------------+-------------+
| 728181 | 施八九 | 52 | 广州 | 广州市西区西县西镇D小区8号楼15单元301室 | 046765168241006480 | 1 | 2019-03-22 19:23:31 | NULL | NULL |
| 5020281 | 吕四 | 66 | 北京 | 北京市南区北县南镇E小区16号楼14单元503室 | 678185107818045700 | 1 | 2019-03-22 19:23:31 | NULL | NULL |
| 5242581 | 李二 | 47 | 成都 | 成都市东区东县西镇J小区5号楼3单元101室 | 394197192410190700 | 1 | 2019-03-22 19:23:31 | NULL | NULL |
| 5208381 | 周六七 | 33 | 北京 | 北京市南区北县西镇I小区16号楼5单元403室 | 762777252833151500 | 1 | 2019-03-22 19:23:31 | NULL | NULL |
| 5932281 | 施八九 | 52 | 广州 | 广州市西区西县西镇D小区8号楼15单元301室 | 046765168241006480 | 1 | 2019-03-22 19:23:31 | NULL | NULL |
| 6166600 | 施九 | 81 | 天津 | 天津市东区南县南镇B小区8号楼8单元401室 | 176670158446459260 | 1 | 2019-03-22 19:23:31 | NULL | NULL |
| 2836053 | 杨三四 | 21 | 深圳 | 深圳市北区西县南镇J小区15号楼14单元103室 | 271873994052292800 | 1 | 2019-03-22 19:14:06 | NULL | NULL |
| 3058353 | 卫七八 | 45 | 深圳 | 深圳市西区南县西镇H小区1号楼17单元101室 | 244683661726008770 | 1 | 2019-03-22 19:14:06 | NULL | NULL |
| 3024153 | 吕四 | 35 | 南京 | 南京市西区南县西镇J小区14号楼14单元203室 | 939547710064377300 | 1 | 2019-03-22 19:14:06 | NULL | NULL |
| 3748053 | 严十 | 10 | 苏州 | 苏州市北区西县南镇B小区8号楼11单元302室 | 966738042390661200 | 1 | 2019-03-22 19:14:06 | NULL | NULL |
+---------+-----------+-----+--------+----------------------------------------------------------+--------------------+--------+---------------------+---------------+-------------+
10 rows in set (3.06 sec)可以看到，执行计划里又用上了 filesort。
为了加速这个查询，我们可以创建一个 create_time 和 status 的联合索引，对应的 SQL 语句是：
alter table people add index create_time_status(create_time, status);此时再看一下它的执行计划和查询耗时：
mysql> explain
-> select a.*
-> from people a
-> inner join(
-> select id
-> from people
-> where status=1
-> order by create_time desc
-> limit 5000000, 10
-> ) b ON a.id = b.id;
+----+-------------+------------+------------+--------+---------------+--------------------+---------+------+---------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+--------+---------------+--------------------+---------+------+---------+----------+--------------------------+
| 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 908814 | 100.00 | NULL |
| 1 | PRIMARY | a | NULL | eq_ref | PRIMARY | PRIMARY | 8 | b.id | 1 | 100.00 | NULL |
| 2 | DERIVED | people | NULL | index | NULL | create_time_status | 8 | NULL | 5000020 | 10.00 | Using where; Using index |
+----+-------------+------------+------------+--------+---------------+--------------------+---------+------+---------+----------+--------------------------+
3 rows in set, 1 warning (0.00 sec)
mysql> select a.*
-> from people a
-> inner join(
-> select id
-> from people
-> where status=1
-> order by create_time desc
-> limit 5000010, 10
-> ) b ON a.id = b.id;
+---------+-----------+-----+--------+----------------------------------------------------------+--------------------+--------+---------------------+---------------+-------------+
| id | name | age | city | addr | sfz | status | create_time | modified_time | delete_time |
+---------+-----------+-----+--------+----------------------------------------------------------+--------------------+--------+---------------------+---------------+-------------+
| 5020281 | 吕四 | 66 | 北京 | 北京市南区北县南镇E小区16号楼14单元503室 | 678185107818045700 | 1 | 2019-03-22 19:23:31 | NULL | NULL |
| 2467300 | 石二 | 84 | 杭州 | 杭州市西区北县东镇B小区12号楼7单元501室 | 575461699231957760 | 1 | 2019-03-22 19:23:31 | NULL | NULL |
| 962500 | 施九 | 81 | 天津 | 天津市东区南县南镇B小区8号楼8单元401室 | 176670158446459260 | 1 | 2019-03-22 19:23:31 | NULL | NULL |
| 728181 | 施八九 | 52 | 广州 | 广州市西区西县西镇D小区8号楼15单元301室 | 046765168241006480 | 1 | 2019-03-22 19:23:31 | NULL | NULL |
| 38481 | 李二 | 47 | 成都 | 成都市东区东县西镇J小区5号楼3单元101室 | 394197192410190700 | 1 | 2019-03-22 19:23:31 | NULL | NULL |
| 4281 | 周六七 | 33 | 北京 | 北京市南区北县西镇I小区16号楼5单元403室 | 762777252833151500 | 1 | 2019-03-22 19:23:31 | NULL | NULL |
| 8952153 | 严十 | 10 | 苏州 | 苏州市北区西县南镇B小区8号楼11单元302室 | 966738042390661200 | 1 | 2019-03-22 19:14:06 | NULL | NULL |
| 8262453 | 卫七八 | 45 | 深圳 | 深圳市西区南县西镇H小区1号楼17单元101室 | 244683661726008770 | 1 | 2019-03-22 19:14:06 | NULL | NULL |
| 8228253 | 吕四 | 35 | 南京 | 南京市西区南县西镇J小区14号楼14单元203室 | 939547710064377300 | 1 | 2019-03-22 19:14:06 | NULL | NULL |
| 8040153 | 杨三四 | 21 | 深圳 | 深圳市北区西县南镇J小区15号楼14单元103室 | 271873994052292800 | 1 | 2019-03-22 19:14:06 | NULL | NULL |
+---------+-----------+-----+--------+----------------------------------------------------------+--------------------+--------+---------------------+---------------+-------------+
10 rows in set (0.93 sec)同样的，原理在上一个例子已经分析过了，相信你一定明白了。
当然，这个例子并不是说要为了每个查询能用上覆盖索引，就要把语句中涉及的字段都建上联合索引，毕竟索引还是有维护代价的。这是一个需要权衡的决定。
5.3 SQL优化 - where id > x
将上一页的查询结果中的最大 id 作为下一页查询的 where 条件，这样可以大幅减少扫描行数，提高查询性能。
只要索引设置得当，可以提供与浅分页近似的查询性能。
select a.*
from people a
inner join(
select id
from people
where status=1
and id > ${id}
order by create_time desc
limit 10
) b ON a.id = b.id;执行计划：
mysql> explain
-> select a.*
-> from people a
-> inner join(
-> select id
-> from people
-> where status=1
-> and id>9553668
-> order by create_time desc
-> limit 10
-> ) b ON a.id = b.id;
+----+-------------+------------+------------+--------+---------------+-------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+--------+---------------+-------------+---------+------+------+----------+-------------+
| 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | NULL |
| 1 | PRIMARY | a | NULL | eq_ref | PRIMARY | PRIMARY | 8 | b.id | 1 | 100.00 | NULL |
| 2 | DERIVED | people | NULL | index | PRIMARY | create_time | 6 | NULL | 104 | 0.96 | Using where |
+----+-------------+------------+------------+--------+---------------+-------------+---------+------+------+----------+-------------+
这种方案的局限性也比较大，不支持跳页，而且要求 id 是升序的。但从业务角度看，它非常适合瀑布流场景，只有上一页和下一页。
5.4 业务限制
强制指定查询条件
以京东 web 端为例，根据关键词搜索历史订单，时间维度默认为近三个月，以年为单位允许用户手动切换，但不允许查询全量数据。
限制最大页码数
5.5、分库分表
涉及到分库分表，就属于架构上的重大调整了。
在分库分表之前，建议你先考虑一下这几个问题：
- 业务复杂度：分库分表需要考虑数据的拆分和管理，这会增加业务逻辑的复杂度，包括数据的查询和更新等。
- 数据一致性：分库分表会导致数据被拆分存储在不同的数据库中，数据的一致性需要通过一些机制来保证，如分布式事务或者异步补偿机制等，这些机制都需要开发自己来实现。
- 数据库压力分散：分库分表可能会导致数据库的压力被分散到不同的数据库中，这可能会导致某些数据库的负载过高，而某些数据库的负载过低，导致资源的浪费。
- 数据库升级和迁移：分库分表方案可能会导致数据库的结构和数据被拆分存储在不同的数据库中，这会增加数据库的升级和迁移的复杂度。
因此，在使用分库分表方案之前，你需要仔细权衡各种因素，想清楚了再做决定。