面试官：Mysql千万级大表如何进行深度分页优化？

有信仰

背景

假如有一张千万级的订单表，这张表没有采用分区分表，也没有使用ES等技术，分页查询进行到一定深度分页之后（比如1000万行后）查询比较缓慢，我们该如何进行优化？
数据准备

订单表结构如下：

1. CREATE TABLE `t_order` (  
2.     `id` BIGINT ( 20 ) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增主键',  
3.     `order_no` VARCHAR ( 16 ) NOT NULL DEFAULT '' COMMENT '订单编号',  
4.     `customer_no` VARCHAR ( 16 ) NOT NULL DEFAULT '' COMMENT '客户编号',  
5.     `order_status` TINYINT ( 4 ) NOT NULL DEFAULT 0 COMMENT '订单状态',  
6.     `warehouse_code` VARCHAR ( 16 ) NOT NULL DEFAULT '' COMMENT '发货地仓库编码',  
7.     `country` VARCHAR ( 16 ) NOT NULL DEFAULT '' COMMENT '收货人国家',  
8.     `state` VARCHAR ( 32 ) NOT NULL DEFAULT '' COMMENT '收货人州',  
9.     `city` VARCHAR ( 32 ) NOT NULL DEFAULT '' COMMENT '收货人城市',  
10.     `street` VARCHAR ( 256 ) NOT NULL DEFAULT '' COMMENT '收货人街道',  
11.     `zip_code` VARCHAR ( 32 ) NOT NULL DEFAULT '' COMMENT '收货人邮编',  
12.     `contact_email` VARCHAR ( 128 ) NOT NULL DEFAULT '' COMMENT '收货人邮箱',  
13.     `contact_name` VARCHAR ( 32 ) NOT NULL DEFAULT '' COMMENT '收货人姓名',  
14.     `contact_mobile` VARCHAR ( 32 ) NOT NULL DEFAULT '' COMMENT '收货人手机号',  
15.     `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',  
16.     `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',  
17.     `deleted` TINYINT ( 2 ) NOT NULL DEFAULT 0 COMMENT '是否已被删除',  
18.     PRIMARY KEY ( `id` ),  
19.     KEY `idx_customer` ( `customer_no`, `deleted` ),  
20.     KEY `idx_create_time` ( `create_time`, `deleted` )  
21. ) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8mb4 COMMENT = '销售订单表';

复制代码

其中Mysql版本为8.0。我们使用Python脚本向表中插入2000万条数据。

1. import pymysql  
2. from faker import Faker  
3. import random  
4. from datetime import datetime  
5. from concurrent.futures import ThreadPoolExecutor  
6.   
7. # MySQL 连接配置  
8. db_config = {  
9.    'host': 'your_database_host',
10.    'user': 'your_database_user',
11.    'password': 'your_database_password',
12.    'database': 'your_database_name'
13. }  
14.   
15. # 创建 MySQL 连接  
16. conn = pymysql.connect(**db_config)  
17. cursor = conn.cursor()  
18.   
19. # 使用 Faker 生成模拟数据  
20. fake = Faker()
22. # 获取国家下发货仓库编码  
23. def generate_warehousecode(country):  
24.     if country == "US":  
25.         return "US-"+random.choice(["WEST", "EAST", "MIDDLE", "SOUTH", "NORTH"])+"-0" + str(random.choice([1, 2, 3, 4, 5]))  
26.     else:  
27.         return country + "00" + str(random.choice([1, 2, 3, 4, 5]))
29. # 插入 t_order 表数据（多线程并发，每个线程插入1万条，共2000个线程）  
30. def insert_data_thread(thread_id):  
31.     # 创建 MySQL 连接  
32.     conn = pymysql.connect(**db_config)  
33.     cursor = conn.cursor()  
34.   
35.     order_data = []  
36.     for _ in range(10000):  
37.         order_no = "OC"+ fake.uuid4()[:12]  # 取前16位  
38.         customer_no = fake.uuid4()[:16]
39.         order_status = random.choice([1, 2, 3, 4, 5])  
40.         country = random.choice(  
41.             ["CA", "US", "MX", "JP", "UK", "TR", "DE", "ES", "FR", "IT", "NL", "PL", "SE", "BR", "CN"])  
42.         warehouse_code = generate_warehousecode(country)  
43.         state = fake.uuid4()[:16]  
44.         city = fake.uuid4()[:16]  
45.         street = fake.uuid4()  
46.         zip_code = fake.uuid4()[:6]  
47.         contact_email = fake.email()  
48.         contact_name = fake.name()  
49.         contact_mobile = fake.phone_number()  
50.         create_time = fake.date_time_between(start_date=datetime(2019, 1, 1), end_date=datetime.now())  
51.         update_time = create_time  
52.         deleted = 0  # 默认未删除  
53.   
54.         cursor.execute("""  
55.             INSERT INTO t_order (                order_no, customer_no, order_status, warehouse_code, country,                state, city, street, zip_code, contact_email, contact_name,                contact_mobile, create_time, update_time, deleted            )            VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)        """, (order_no, customer_no, order_status, warehouse_code, country,  
56.               state, city, street, zip_code, contact_email, contact_name,  
57.               contact_mobile, create_time, update_time, deleted))  
58.   
59.         order_data.append((cursor.lastrowid, order_no, customer_no, create_time))  # 保存插入的行的 ID  
60.     # 提交 t_order 数据插入  
61.     conn.commit()  
62.     print(thread_id+ "已经跑完10000条数据。。。。。。。。。")  
63.     # 关闭数据库连接  
64.     cursor.close()  
65.     conn.close()  
66.   
67. # 使用 ThreadPoolExecutor 并发插入  
68. with ThreadPoolExecutor(max_workers=10) as executor:  # 可以根据需要调整最大线程数  
69.     executor.map(insert_data_thread, range(500))

复制代码

问题复现

导出数据时我们需要按照时间倒序。所以我们先执行以下sql查询前100条

1. select * FROM t_order ORDER BY create_time desc LIMIT 100;

复制代码

共花费210ms。执行计划如下：

然后我们继续执行sql，我们从第100万行开始取100条数据：

1. select * FROM t_order ORDER BY create_time desc LIMIT 1000000,100;

复制代码

此时耗时3分2秒，耗时明显增加。执行计划如下：

由执行计划看，此时索引已经失效了。。。。
我们继续执行sql，从第1000万行开始取100条数据：

1. select * FROM t_order ORDER BY create_time desc LIMIT 10000000,100;

复制代码

此时耗时4分14秒，时间真是太长了，执行计划如下：

后面还有接近1000万条数据没有取出来，直接就废了。
原因分析

当我们使用 LIMIT offset, count 查询语句进行深度分页查询时，例如 LIMIT 10000000,100 ，会发生以下过程：

• MySQL首先会根据给定条件从相应的索引树中查找m+n条记录。对于聚集索引来说，它直接找到需要的结果即丢弃前offset条数据，返回count条数据并返回；而对于二级索引，则可能涉及回表操作。
  
• 如果使用的是二级索引，在查到m+n条记录后还需要通过这些记录所关联的主键ID去聚集索引里再次搜索出完整的行数据，然后再丢弃掉前offset条数据，返回count条数据。因此在这个过程中可能会产生大量的“回表”操作，这将导致性能下降。
  

我们借助B+ Tree Visualization演示一下这张表的索引结构：

• 聚集索引（主键ID）
  

• 二级索引（idx_create_time）
  

以上述例子来说，当我们查询LIMIT 10000000,100时，它会先从二级索引中查询10000000+100条记录对应的ID，然后再用这些记录的ID去聚集索引中查询ID对应的记录，然后舍弃掉前10000000条数据，返回后100条数据。
所以当offset+count量很大时，Mysql的执行器认为全表扫描的性能更由于使用索引，所以也导致索引失效。所以我们要做的尽可能的减少回表的记录数量。
解决方案

使用子查询

我们改造sql，通过一个子查询按照create_time倒排，获取第offset + 1条记录的最新的create_time，create_time直接从二级索引上可以获取，不会进行回表，然后我们再根据这个create_time传递到主查询时，取100条数据，即回表数据也仅仅只有count条即100条数据，大大减少了回表的记录数量。
[code]SELECT * FROM t_order  WHERE create_time