学习

MySQL大表分页查询优化思路

文中创建了一个测试数据集，并按照针对主键的id排序的分页查询，以及针对随机分布的create_time排序的分页查询进行了讨论。分别讨论了针对id优化查询，使用子查询，使用临时表，以及使用游标查询的不同原理及其性能

# 背景

之前碰到了MySQL大表查询时的优化问题。因此写了这个文章，整理记录一下目前网上的一些大表分页查询的思路。文中首先创建了一个测试数据集，并讨论了两类情况以及常见的几种优化方案的原理及效率

  • 针对主键的id排序的分页查询
  • 针对随机分布的create_time排序的分页查询

常见的优化方案有：

  • 针对id优化查询
  • 使用子查询
  • 使用临时表
  • 使用游标查询

# 参考链接

https://blog.csdn.net/zhaojw_420/article/details/135910080 https://www.cnblogs.com/trytocatch/p/mysql-page-query.html https://www.cnblogs.com/yanggb/p/11058707.html

# 环境准备

创建一个order表作为测试数据。注意：这里使用id作为主键，并对create_time加上了索引。

配置：

  • 采用本地搭建MySQL数据库，配置为2核CPU，1G内存。使用配置脚本插入1000w行数据。其他配置的数据集请自行测试
  • 文中的查询尽可能使用第一次查询得到的数据，减少数据库缓存对查询性能的干扰

# SQL脚本 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

CREATE TABLE `test_order` (  
    `id` BIGINT ( 20 ) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增主键',  
    `order_no` VARCHAR ( 16 ) NOT NULL DEFAULT '' COMMENT '订单编号',  
    `customer_no` VARCHAR ( 16 ) NOT NULL DEFAULT '' COMMENT '客户编号',  
    `order_status` TINYINT ( 4 ) NOT NULL DEFAULT 0 COMMENT '订单状态',  
    `country` VARCHAR ( 16 ) NOT NULL DEFAULT '' COMMENT '收货人国家',  
    `city` VARCHAR ( 32 ) NOT NULL DEFAULT '' COMMENT '收货人城市',  
    `address` VARCHAR ( 256 ) NOT NULL DEFAULT '' COMMENT '收货人街道',  
    `contact_email` VARCHAR ( 128 ) NOT NULL DEFAULT '' COMMENT '收货人邮箱',  
    `contact_name` VARCHAR ( 32 ) NOT NULL DEFAULT '' COMMENT '收货人姓名',  
    `contact_mobile` VARCHAR ( 32 ) NOT NULL DEFAULT '' COMMENT '收货人手机号',  
    `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',  
    `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',  
    `deleted` TINYINT ( 2 ) NOT NULL DEFAULT 0 COMMENT '是否已被删除',  
    PRIMARY KEY ( `id` ),  
    KEY `idx_customer` ( `customer_no`, `deleted` ),  
    KEY `idx_create_time` ( `create_time`, `deleted` )  
) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8mb4 COMMENT = '销售订单表';

# 数据创建

使用python脚本插入数据

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102

import pymysql
from faker import Faker
import random
from datetime import datetime
from concurrent.futures import ThreadPoolExecutor

# MySQL 连接配置
db_config = {
    "host": "host name",
    "user": "username",
    "password": "password",
    "database": "test",
}

# 创建 MySQL 连接
conn = pymysql.connect(**db_config)
cursor = conn.cursor()

# 使用 Faker 生成模拟数据
fake = Faker()


# 插入 test_order 表数据
# 多线程并发，每个任务插入1万条
def insert_data_thread(task_id):
    # 创建 MySQL 连接
    conn = pymysql.connect(**db_config)
    cursor = conn.cursor()

    order_data = []
    for _ in range(10000):
        order_no = "OC" + fake.uuid4()[:12]  # 取前16位
        customer_no = fake.uuid4()[:16]
        order_status = random.choice([1, 2, 3, 4, 5])
        country = random.choice(
            [
                "CA",
                "US",
                "MX",
                "JP",
                "UK",
                "TR",
                "DE",
                "ES",
                "FR",
                "IT",
                "NL",
                "PL",
                "SE",
                "BR",
                "CN",
            ]
        )
        city = fake.uuid4()[:16]
        address = fake.uuid4()
        contact_email = fake.email()
        contact_name = fake.name()
        contact_mobile = fake.phone_number()
        create_time = fake.date_time_between(
            start_date=datetime(2020, 1, 1), end_date=datetime.now()
        )
        update_time = create_time
        deleted = 0  # 默认未删除

        cursor.execute(
            """  
            INSERT INTO test_order (
            order_no, customer_no, order_status, country, city, address, contact_email, contact_name, contact_mobile, create_time, update_time, deleted) 
                       VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)""",
            (
                order_no,
                customer_no,
                order_status,
                country,
                city,
                address,
                contact_email,
                contact_name,
                contact_mobile,
                create_time,
                update_time,
                deleted,
            ),
        )

        order_data.append(
            (cursor.lastrowid, order_no, customer_no, create_time)
        )  # 保存插入的行的 ID

    # 提交 test_order 数据插入
    conn.commit()
    print("任务：" + str(task_id) + "已经提交10000条数据")
    # 关闭数据库连接
    cursor.close()
    conn.close()


if __name__ == "__main__":
    # 使用 ThreadPoolExecutor 并发插入，此处配置10个线程，共1000个任务
    with ThreadPoolExecutor(max_workers=10) as executor:  # 可以根据需要调整最大线程数
        print("start insert")
        executor.map(insert_data_thread, range(1000))

# 大数据量分页查询及其问题

一些常见的分页查询sql

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

-- 从0开始，查询耗时 2ms
SELECT * FROM  test_order ORDER BY id LIMIT 100
-- 从9000开始，查询耗时 5ms
SELECT * FROM  test_order ORDER BY id LIMIT 9000,100
-- 从90000开始，查询耗时 27 ms
SELECT * FROM  test_order ORDER BY id LIMIT 90000,100
-- 从900000开始，查询耗时 510ms
SELECT * FROM  test_order ORDER BY id LIMIT 900000,100
-- 从9000000开始，查询耗时 5000ms
SELECT * FROM  test_order ORDER BY id LIMIT 9000000,100

可以看到，随着查询分页时，起始位置的增加，查询性能成倍变慢。原因如下：

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

  • MySQL首先会根据给定条件从相应的索引树中查找m+n条记录。对于聚集索引来说，它直接找到需要的结果即丢弃前offset条数据，返回count条数据并返回；
  • 如果使用的是二级索引，在查到m+n条记录后还需要通过这些记录所关联的主键ID去聚集索引里再次搜索出完整的行数据，然后再丢弃掉前offset条数据，返回count条数据。因此在这个过程中可能会产生大量的“回表”操作，这将导致性能下降。
  • 所以当offset+count量很大时，Mysql的执行器认为全表扫描的性能更由于使用索引，所以也导致索引失效。所以我们要做的尽可能的减少回表的记录数量。
  • 定位到offset的成本过高，获取了很多无效数据并丢弃，未能充分利用索引的有序性

# 针对ID排序的分页查询

# 使用id作为限定条件优化

可以使用between and语句或大于小于符号直接限定id位置。适用于id连续分布，且已知id范围

1
2
3
4
5

-- 使用大于小于号限定范围，耗时3ms
SELECT * FROM  test_order WHERE id > 900000 AND id < 901000 ORDER BY id LIMIT 100;

-- 使用大于小于号限定范围，耗时25ms
SELECT * FROM  test_order WHERE id BETWEEN 900000 AND 901000 ORDER BY id LIMIT 100;

注：

  • 适用于id连续分布，且已知id范围
  • 如表中id不连续或被删除过部分，可考虑适当放宽id范围，确保查询数量大于pagesize。并通过LIMIT限制最终数量

# 使用子查询优化 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

-- 查询第900000行，耗时473ms
SELECT * FROM  test_order ORDER BY id LIMIT 900000,1;

-- 查询第900000行的主键id，耗时124ms
SELECT id FROM  test_order ORDER BY id LIMIT 900000,1;

-- 使用子查询优化，耗时127ms
SELECT * FROM  test_order WHERE id >= (
    SELECT id FROM  test_order ORDER BY id LIMIT 900000,1
) LIMIT 100;

-- 普通分页查询，耗时500ms
SELECT * FROM  test_order ORDER BY id LIMIT 900000,100;

主要优化思路：

  • 在查询时，使用select id替代 select *，不需要获取全部数据再丢弃，加速了大约3倍
  • 先使用子查询直接获取起始id位置，再直接从id位置向后搜索100条取出
  • 相对于原本分页查询，整体速度快了几倍

# 使用 INNER JOIN 优化 

1
2
3
4
5

-- 耗时390ms
SELECT * FROM test_order
INNER JOIN(
    SELECT id FROM test_order ORDER BY id LIMIT 900000,100
)temp ON test_order.id = temp.id ORDER BY test_order.id;

思路类似子查询，将子查询的结果构造为临时表。子查询中仅查询id相关，不需要获取全部数据并使用了主键索引。再在主查询中使用对应id查询所有数据

注：在基于id的分页查询中效率较低，不如直接使用基于主键id的子查询

# 游标查询(SEARCH AFTER)方案

思路：每次都记录查询开始的位置，每次取X条记录，指定从上次结束的位置继续往后取X条，利用索引的有效性加快查询

1
2
3

-- prev id = 900000时，耗时约5ms
SELECT * FROM test_order
WHERE id > 'prev max id' ORDER BY id LIMIT 100

优点：

  • 简单易理解
  • 使用主键，查询效率高

缺点：

  • 如果id不连续或不断变化，可能漏掉数据
  • 只能支持连续分页，不能支持获取随意页的数据。

# 针对create_time排序的分页查询

# 普通查询及问题 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

-- 普通查询，耗时约30ms
SELECT * FROM  test_order ORDER BY create_time DESC LIMIT 100;
-- 耗时约200ms
SELECT * FROM  test_order ORDER BY create_time DESC LIMIT 900,100;
-- 耗时约1000s
SELECT * FROM  test_order ORDER BY create_time DESC LIMIT 9000,100;
-- 耗时约1500ms
SELECT * FROM  test_order ORDER BY create_time DESC LIMIT 90000,100;
-- 耗时约44s
SELECT * FROM  test_order ORDER BY create_time DESC LIMIT 900000,100;

可以看到，对于create_time排序的查询速度比相同数量的对于id排序的查询速度慢了更多。这种情况下，更需要优化SQL查询的效率。

分析一下，慢的主要原因有：

  • create_time不是主键，在索引上查找二级索引后，需要再进行回表查找主键，再获取对应全部数据，再全部丢弃，效率更差
  • 本次生成的数据集中create_time随机排布，对排序查找更不友好。

# 使用子查询

例子：

1
2
3
4
5
6

-- 使用create_time作为子查询条件，耗时约692ms
SELECT * FROM test_order 
WHERE create_time >= (
    SELECT create_time FROM test_order ORDER BY create_time DESC LIMIT 900000,1
)
ORDER BY create_time DESC LIMIT 100;

优化思路：

  • 子查询中，create_time直接从二级索引上可以获取，不会进行回表
  • 主查询中，从指定的create_time查询，取100调数据。需要回表查询的数据仅需要100条。
  • 减少了大量需要回表查询再丢弃的数据

# 使用 INNER JOIN

例子：

1
2
3
4
5
6

-- 耗时约160ms
SELECT * FROM test_order 
INNER JOIN (
    SELECT id FROM test_order ORDER BY create_time DESC LIMIT 900000,100
) temp ON test_order.id = temp.id
ORDER BY create_time DESC;

优化思路：

  • 临时表查询中create_time存储在二级索引中，且二级索引中能直接获取对应id，不会进行回表查找
  • 临时表通过id与主查询关联，仅需查询count条数据
  • 减少了回表的数据，并使用了主键索引，效率很高
  • 在基于create_time排序时，相对于子查询优化的方案效率更高。因为直接传递了id

# 游标查询(SEARCH AFTER)方案

在对create_time进行游标式分页查询时，实际上由于create_time可能重复，需要用到create_time和id两个条件来确保排序。使用普通分页查询的想法去查询，会比较复杂。这里提供两种思路供参考

# 普通游标查询思路

但如果按照create_time排序，写法较麻烦。可分三种情况执行：

  1. 首次查询，create_time>=‘xxxx-xx-xx’，如果不要求以某时间开始，则无条件
1

SELECT * FROM test_order WHERE create_time >= 'xxxx-xx-xx' ORDER BY create_time, id LIMIT pageSize
  1. 如果上次查询的记录条数等于pageSize，则用create_time和id的组合条件来查询，防止create_time在边界位置发生重复时漏掉数据
1

SELECT * FROM test_order WHERE create_time = 'create_time of last query' AND id > 'id of last query' ORDER BY create_time, id LIMIT pageSize
  1. 如果上次查询的记录数小于pageSize，并且上次查询是第二种查询，则仅用create_time来查询
1

SELECT * FROM test_order WHERE create_time = 'create_time of last query'  ORDER BY create_time, id LIMIT pageSize

可以看到，这样去通过游标查询，逻辑非常繁琐复杂，一般不建议这样使用

# 多条件行比较思路

mysql支持多条件 行比较，如需要要求(a,b,c)>(x,y,z)，可以使用写法：

1

a>x or (a=x and (b>y or (b=y and c>z)))

因此，游标式查询sql可以改为

1
2
3

SELECT * FROM test_order 
WHERE (create_time > 'create_time of last query' OR (create_time = 'create_time of last query' AND id > 'id of last query'))  
ORDER BY create_time, id LIMIT pageSize

缺点：

  • 不能随机跳转数据
  • 对于同时使用create_time和id排序时，这种查询效率较低，更建议使用其他方式

# 总结

本文整理了一些网上对于大表分页搜索的思考以及方案。

总的来说，大表分页查询慢的主要原因是：使用LIMIT进行分页时，会将前半无用的数据都取出，抛弃后再取出真正需要的数据。当数据量大时，取出前半无用数据的过程就消耗了大量查询时间

主要解决的思路有：

  • 基于主键id直接查询
  • 在子查询中使用LIMIT分页时仅查询需要的可索引字段（如id,create_time等），再在主查询中获取对应的全部数据
  • 使用inner join。在子查询中使用LIMIT分页，但仅查询主键id字段，作为临时表输出。再在主查询中直接根据主键id获取对应全部数据
  • 游标式查询，每次都基于上次查询的最大值累进分页

对于按照不同方式排序的分页查询，不同的思路效率不同。还需要根据实际情况选择适当的方案

MySQL
Licensed under CC BY-NC-SA 4.0
© 2019 - 2024 OneEarMouse
使用 Hugo 构建
主题 Stack 由 Jimmy 设计