MySQL查询百万级数据分页查询优化实验记录

sumAll
1,860 阅读7分钟

1.业务场景

在项目开发中，经常需要进行统计数据的上报和分析。通常，经过分析后，这些数据将在后台呈现给运营和产品人员，以便他们进行分页查看和分析。其中，一种常见的需求是按日期进行筛选和汇总数据。随着时间的推移，统计数据的数量将逐渐增加，最终可能达到数百万甚至数千万条数据。这种情况下，数据的处理和展示需要一些优化和考虑，以确保系统性能和用户体验。

2.实验数据插入

1.创建 user 表

CREATE TABLE `user`  (
  `id` int(0) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `password` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `age` int(0) UNSIGNED NULL DEFAULT NULL,
  `phone` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `create_time` datetime(0) NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

2.插入一百万条数据

开放了一个插入接口，通过 Apache Bench 发送 100 万次请求来向数据库插入 100 万条数据。命令如下：

# -c 表示并发请求数；-n 表示总请求数
ab -c 20 -n 1000000 http://127.0.0.1:8080/api/mysql/insert

大约需要 362 秒。

代码及测试 SQL 链接

3.无索引分页查询耗时

-- SQL_NO_CACHE 防止 SQL 查询走缓存
SELECT SQL_NO_CACHE * from `user` WHERE create_time BETWEEN '2000-01-01' AND '2023-12-31' LIMIT 1,10;

不同分页条件下的耗时情况：

LIMITTime (ms)
1,100
100,101
1000,102
10000,109
100000,1062
200000,10122
400000,10418
600000,10466
800000,10520
1000000,10757

上述实验结果是单次实验所得。从表中分析可知，随着数据量的增大，往后页查询所耗时间按理来说会越来越大。

通过 Explain 命令对查询语句进行分析：

EXPLAIN
SELECT SQL_NO_CACHE * from `user` WHERE create_time BETWEEN '2000-01-01' AND '2023-12-31' LIMIT 1000000,10;

结果：

mysql_explain结果.png

从结果中可知走的是全表扫描（ALL），扫描的数据行有 1022908 条，选取的行和读取的行的百分比为 11.11，使用到了 where 过滤。

4.添加索引后分页查询耗时

1.添加索引

ALTER TABLE user
ADD INDEX idx_createtime (create_time);

2.查看索引

SHOW INDEX FROM user;

mysql_索引显示.png

3.查询耗时情况

LIMITTime (ms)
1,1042
100,1043
1000,1057
10000,10209
100000,101502
200000,102860
400000,105975
600000,108492
800000,1011155
1000000,1014192

如果需要频繁通过 creae_time 字段进行条件查询，那么为该字段添加索引是必要的。 从上述实验结果可知，添加索引后，其执行时间反而增加。

通过 Explain 命令对查询语句进行分析：

EXPLAIN
SELECT SQL_NO_CACHE * from `user` WHERE create_time BETWEEN '2000-01-01' AND '2023-12-31' LIMIT 1000000,10;

mysql_explain结果2.png

从结果中可知走的是索引范围扫描（range），实际使用的索引是 idx_createtime，扫描的数据行有 511454 条；Using index condition 表示查询的列有非索引的列，先判断索引的条件，以减少磁盘的 I/O；Using MRR 指通过一次扫描来匹配多个范围。

究其原因，主要还是因为存在回表的情况。其执行过程如下：

  • 先从 「idx_createtime」B+ 树找到对应的叶子节点，获取主键值
  • 然后从上一步获取的主键值，在 「PRIMARY」B+ 树检索到对应的叶子节点，然后获取要查询的数据

5.优化方式

通过分析，可知是由于回表导致的性能变差。那优化的方向就是减少回表或者避免回表。我们可以通过覆盖索引来避免回表的发生。

覆盖索引指在使用「二级索引」（非 PRIMARY 聚簇索引的索引）作为查询条件的时候，如果要查询的字段都包含在「二级索引」的叶子节点中，则不需要再从 「PRIMARY」B+ 树中查询，直接返回即可。

1.通过覆盖索引获取符合条件的 id

SELECT SQL_NO_CACHE id FROM `user` WHERE create_time BETWEEN '2000-01-01' AND '2023-12-31' LIMIT 1000000,10;

对这条 SQL 语句进行分析：

EXPLAIN SELECT SQL_NO_CACHE id FROM `user` WHERE create_time BETWEEN '2000-01-01' AND '2023-12-31' LIMIT 1000000,10;

mysql_explain3.png

从结果可知，Extra 中存在 Using index ，说明用到了覆盖索引，避免了回表操作。

2.将 1 作为子查询

SELECT SQL_NO_CACHE * from (SELECT SQL_NO_CACHE id FROM `user` WHERE create_time BETWEEN '2000-01-01' AND '2023-12-31' LIMIT 1000000,10) AS temp INNER JOIN `user` AS u on temp.id=u.id;

对这条 SQL 语句进行分析：

EXPLAIN
SELECT SQL_NO_CACHE * from (SELECT SQL_NO_CACHE id FROM `user` WHERE create_time BETWEEN '2000-01-01' AND '2023-12-31' LIMIT 1000000,10) AS temp
INNER JOIN `user` AS u on temp.id=u.id;

mysql_explain4.png

个人解读（有问题欢迎评论区指正）：id=2 是子查询的执行情况，然后第一行是对 id=2 查询得到的虚拟表的执行情况，第二行是执行 INNER join 时，对 user 表的执行情况。

Tips：

  • id：id不同，执行顺序从大到小；id相同，执行顺序从上之下

    • 即意味着 user —> <derived2> —> u

  • select_type：表示查询类型

    • DERIVED：派生表的 select（from 子句的子查询）
    • PRIMARY：查询中包含任何复杂的子部分，最外层的 select 被标记为 PRIMARY

  • table：显示数据来自于哪个表

    • derived2 ：表示是虚拟表，是 id=2 的查询

  • type：表示扫描方式

    • range：索引范围扫描
    • ALL：全表扫描
    • eq_ref：唯一索引扫描，表示一条记录与之匹配

  • possible_keys：表示可能用到的索引

  • key：实际使用的索引

  • key_len：表示索引的长度

  • ref：表示哪一列被使用了，常数表示这一列等于某个常数

  • rows：表示扫描的行数

  • filter：表示选取的行和读取的行的百分比，100 表示选取了 100%

  • Extra：一些重要的额外信息

    • Using where：使用了 where 过滤
    • Using index：使用了覆盖索引

3.查询耗时情况

LIMITTime (ms)
1,100
100,102
1000,103
10000,107
100000,1040
200000,1080
400000,10153
600000,10307
800000,10397
1000000,10432

6.总结

对于超大分页的情况，可以通过 覆盖索引+子查询 的方式来减少回表情况，从而提高效率。

avatar
文章
阅读
粉丝