WIP: 基于 MySQL 的海量据量翻页优化

提出问题

在 MySQL 中,一张数据量膨胀到 1000万的表,进行全量分页查询的时候,随着页码往后翻,MySQL 响应的延迟越来越大。

例如,查询 900 万以后的 20 条数据,耗时长达 98s

-- 98.4s select * from `logs` limit 9000001, 20;

而查询 100 万偏移的 20 条,时间要缩短不少,大约为 10s

显然,按照这种趋势下去,单表膨胀到 1 亿时,将无法在有限的时间里完成分页查询。

解决问题

通过命中索引,进行大范围的跳过,同样的查询需求,改写成下面的查询语句:

-- 38ms select * from `logs` where id > 9900001 limit 20;

可以看到,执行时间只需 38ms,时间缩短了 2578 倍。

究竟是什么原因造成了如此大的时间差异呢?

通过 explain 命令可以查看每一个 sql 语句的执行计划。

优化前

看原始查询,通过 limit + offset 来翻页的执行计划如下:

mysql> explain select * from `logs` limit 9990000, 20\G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: logs type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 16296105 Extra: 1 row in set (0.00 sec)

优化后

而优化后的语句执行计划如下:

mysql> explain select * from `logs` where id > 9990000 limit 20\G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: logs type: range possible_keys: PRIMARY key: PRIMARY key_len: 8 ref: NULL rows: 8148052 Extra: Using where 1 row in set (0.00 sec)

可以看出来,此次查询在 where 子句中命中了 Primary 主键索引,执行了 range 扫描查询。

拓展

思考:MySQL 有哪些索引呢?

不同类型的索引

当添加了过滤条件之后,除了 Primary Key 索引可以用之外,其他字段的索引是不是也一样有效呢?

给字符串字段添加索引:

create index

不同的存储引擎

未完待续 ...

© 2021, XZD