进阶[数据库] Mysql千万级大表深分页优化方案

背景介绍

有个数字化转型项目采用Mysql数据库，表中大概有两千万数据，做深度分页后查询比较慢，需要优化

需求分析

对于B端项目，需要查询全量数量，查询条件包括起止日期，没有采用水平分表方案，如根据用户id水平分表，根据时间水平分表等 采用es、Hive Impala,ClichHouse等OLAP方案需要引入其它技术栈，开发资源，进度等无法满足要求

讨论主题

1. 分析大表深度分页为什么这么慢

2. 如何优化

3. 优化原理

准备表结构

# MYSQL 5.7.42

CREATE TABLE person (
    `id`            bigint(20)      NOT NULL,
    `name`          varchar(50)     DEFAULT NULL,
    `sex`           varchar(10)     DEFAULT NULL,
    `age`           int(11)         DEFAULT NULL,
    `email`         varchar(100)    DEFAULT NULL,
    `tel`           varchar(50)     DEFAULT NULL,
    `address`       varchar(100)    DEFAULT NULL,
    `ccreate_time`  datetime        DEFAULT NULL,
    `update_time`   datetime        DEFAULT NULL,
    PRIMARY KEY (`id`)
)   ENGINE=InnoDB   DEFAULT CHARSET=utf8mb4;

默认查询

SELECT * FROM person limit 100    # 7ms

问题复现

实际业务场景中一般需要排序，以创建时间逆序为例，耗时7秒

SELECT * FROM person p order by p.create_time desc limit 100

增加创建时间逆序索引后，再执行一次耗时为3ms

ALERT TABLE person ADD INDEX idx_create_time(create_time desc)  # 3ms

如果从第100万行获取100行，耗时明显增加

在100万行获取100行，耗时15s

SELECT * FROM person p ORDER BY p.create_time desc limit 1000000,100 # 15s

在1000万行获取100行，耗时80s

SELECT * FROM person p ORDER BY p.create_time desc limit 1000000,100 # 80s

一般我们查询数据的最大时间容忍度是3s内

分析原因

查看深度分页执行计划，显示没有使用到索引

EXPLAIN SELECT * FROM person p ORDER BY p.create_time desc limit 1000000,100

TYPE:   ALL     全表扫描
KEY:    NULL    没有使用索引，但是索引在少量查询的情况下是可以使用的

limit m, n 查询过程是先回表查询m n条记录，然后丢掉前面m条，取后面n条返回

目前表中默认有一个主键id组成的聚集索引，所有的分支节点存储id,叶子节点存储数据字段

create_time二级索引：时间作为分支节点，叶子节点存储表的主键

create_time二级B tree索引保留主键id,如果使用索引则返回m n条id,再通过聚集索引B tree根据id查询数据，次过程称之为回表

当m n数量非常大时，回表的查询两也很大，Mysql查询优化器认为全表扫描的性能优于使用索引

如我们根据二级索引返回了100万个id,然后返回聚集索引查询id，此时回表数据量巨大

我们优化的方案也是针对二级索引进行优化，如何让二级索引尽可能小地返回数据量，从而提高查询效率

解决方案1 子查询

通过子查询优化，100万行100条耗时242ms,提升61倍 通过子查询优化，1000万行100条耗时242ms,提升61倍

SELECT p.* 
    FROM person p
    WHERE p.create_time <= (
        SELECT create_time FROM person t ORDER BY t.create_time DESC
        LIMIT 1000000, 1
    )
    ORDER BY p.create_time DESC LIMIT 100

通过create_time索引B tree得到create_time偏移量，此时不需要回表; 再一次通过create_time索引B tree计算得到100个id,然后回表读取记录，从而有效减少回表记录数，提高查询效率

分析

该过程实际是执行了两次，第一次是子查询，通过子查询查询最大时间，这个过程是通过二级索引来的，是不需要回表的，因为返回的时间就是节点数据

p.create_time <=查询出来的时间，实际走的还是create_time二级索引,该过程依然不需要回表， 然后获取100条，这个过程是需要回表的。再去聚集索引去查全部数据

索引使用了两次，性能提升几十倍

EXPLAIN SELECT p.* 
    FROM person p
    WHERE p.create_time <= (
        SELECT create_time FROM person t ORDER BY t.create_time DESC
        LIMIT 1000000, 1
    )
    ORDER BY p.create_time DESC LIMIT 100

1
    select_type     PRIMARY
    table           p
    type            range
    possible_keys   idx_create_time
    key             idx_create_time
    key_len         6
    extra           using where
2
    select_type     SUBQUERY
    table           t
    type            index
    key             idx_create_time
    key_len         6
    extra           using index

解决方案2 inner join

将表按照create_time进行子查询，并返回id通过id进行关联，返回数据

查询100万条数据，236ms 查询1000万条数据，2.363s

SELECT p.*
    FROM person p,
    (SELECT id FROM person t ORDER BY t.create_time DESC LIMIT 1000000, 100) p2
    WHERE p.id = p2.id
    ORDER BY p.create_time DESC

t表使用到了id,create_time两个字段，都存储在create_time索引B tree中了，不需要回表; 通过t表临时与p表关联过滤得到100条记录，回表读取100条记录，有效减少回表记录数

EXPLAIN SELECT p.*
    FROM person p,
    (SELECT id FROM person t ORDER BY t.create_time DESC LIMIT 1000000, 100) p2
    WHERE p.id = p2.id
    ORDER BY p.create_time DESC
1
    select_type     PRIMARY
    table           <derived2>
    type            ALL
    possible_keys   NULL
    key             NULL
    key_len         NULL
    extra           using temproary,using filesort
2
    select_type     PRIMARY
    table           p
    type            eq_ref
    possible_keys   PRIMARY
    key             PRIMARY
    key_len         6
    extra           NULL
3
    select_type     DERIVED
    table           t
    type            index
    possible_keys   NULL
    key             idx_create_time
    key_len         6
    extra           using index

解决方案3

每次保存上一次最小的create_time, 第一次查询比上一页create_time小一页的数据 查询第100万行耗时18ms，比80s快4000多倍

SELECT * FROM person p 
    WHERE p.create_time < '2023-01-09 17:21:03'
    ORDER BY p.create_time DESC LIMIT 10

分析

SELECT * FROM person p 
    WHERE p.create_time < '2023-01-09 17:21:03'
    ORDER BY p.create_time DESC LIMIT 10
1. 
    select_type:    SIMPLE
    table           p
    type            range
    possible_keys   idx_create_time
    key             idx_create_time
    rows            9907503
    extra           using index condition

缺点：只能连续分页，移动端常用，web B端使用较少，需要客户认可才可以

总结

深度分页解决方案

1. 需求层面，如果允许最多查询xx页，则避免了深度分页问题

2. 翻页体验层面，如果可以保留上一页，下一页，每次传参上一页的最大最小值，可使用方案能3

3. 通过子查询，inner join减少回表次数，提高查询效率

4. 水平分表

5. ES,HIVE IMPALA,CLICKHOUSE等OLAP方案

这篇好文章是转载于：学新通技术网