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

不同的存储引擎

未完待续 ...