问题

在很多的场景中，都会使用到排序，然后分页展示。如果有对应的索引，就可以避免额外的文件排序操作，只需要扫描索引，效率通常会不错。

假设我们维护着一个游戏积分排行榜，排行榜有10万个用户，按积分高低排序，界面上每页展示10个用户。

CREATE TABLE `game_list` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户id',
  `name` varchar(11) NOT NULL DEFAULT '' COMMENT '用户名',
  `score` int(11) NOT NULL DEFAULT '0' COMMENT '用户积分',
  `create_time` int(11) DEFAULT '9' COMMENT '创建时间',
  `update_time` int(11) DEFAULT '9' COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `score` (`score`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='积分排行榜'

使用存储过程插入10万条数据来测试。

delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=1;
  while(i<=100000)do
    insert into game_list values(i, i, i, i, i);
    set i=i+1;
  end while;
end;;
delimiter ;
call idata();

我们会同时使用ORDER BY和LIMIT。

SELECT * FROM game_list ORDER BY score Limit 10;

因为在score字段建立了索引，所以查询起来很快，explain分析，扫描的row=10。

GbIh3d.jpg

但常常会出现一个会导致性能问题的情况，虽然有索引，但是如果用户翻页到比较靠后时，查询会变得比较慢。

SELECT * FROM game_list ORDER BY score LIMIT 10 OFFSET 5000;

GbLrGT.jpg

explain分析结果显示，type=ALL，这时候会优化器会放弃使用索引，直接全表扫描。

之前的文章mysql索引里，讲过聚簇索引和辅助索引。查询时会先在扫描辅助索引，然后根据主键去聚簇索引去获取对应的数据行，这需要两次的B+树查找操作。

在偏移量非常大的时候，例如上面的LIMIT 10 OFFSET 5000，如果使用索引，那么MySQL需要查询5010条数据，然后只返回最后的10条，前面的5000条数据都被抛弃，这样的效率是很低的，优化器对比后，发现全表扫描的效率比这种方式高，所以选择了全表扫描。

Full Table Scan vs Full Index Scan Performance，这里解释了为什么优化器会放弃使用索引，选择全表扫描。

When we are doing a full table scan, we are doing sequential reads, which are quite fast even with slow disks. But when we are using the index, we first have to do a full index scan (fast sequential reads, no problem) and then lots of random reads to fetch rows by index value. And random reads are orders of magnitude slower than sequential reads.

解决方法

  1. 使用覆盖索引，避免回主键索引查询。
    如果我们只需要name和score两列，可以创建联合索引(score,name)，此时再执行

    explain SELECT name, score FROM game_list ORDER BY score LIMIT 10 OFFSET 5000;

    GbOPyQ.jpg

    explain分析结果显示，type=index表明使用了索引，同时Extra里的Using index表明了，只扫描了辅助索引，不需要回主键索引查询。

  2. 延迟关联
    通过覆盖索引查询返回需要的主键，再根据这些主键关联原表回去需要的行。

    SELECT * FROM game_list INNER JOIN (SELECT id FROM game_list ORDER BY score LIMIT 10 OFFSET 5000) as x USING(id);

    GqVUaV.jpg
    先通过扫描覆盖索引，得到按score排序的第5001到5010条这10条数据的主键id，生成一个临时表x，这对应explain结果里的第三行。

    然后通过全部扫描x表，对应eplain结果里的第一行。
    使用x表每一行的主键id去game_list查询，这时候只需要在game_list的主键索引，查询10次就可以得到最终的结果，对应explain结果里的第二行。

    PS: 刚开始，我比较疑惑为什么，explain的第一行记录里的row=5010，明明我们的临时表x里只有10条记录，查阅了部分资料，应该是因为LIMIT is not taken into account while estimating number of rows。

  3. 避免使用大偏移量的查询
    LIMIT和OFFSET的问题，其实是OFFSET的问题，它会导致MySQL扫描大量不需要的行然后再抛弃掉。如果可以使用书签记录上次取数据的位置，那么下次就可以直接从该书签记录的位置开始扫描，这样就可以避免使用OFFSET。

    加上一个假设，如果用户的分数是单调递增的（只是举了例子，分数当然会有重复)。如果这一页最后的分数是18888，那么可以使用下面的sql查询下下一页，这样不管翻到第几页，查询的效率都很高。

    SELECT * FROM game_list where score < 18888 ORDER BY score DESC LIMIT 10;

    Gqn9Ld.jpg

参考资料

《 高性能MySQL：第3版》

Last modification：April 11th, 2020 at 11:25 pm
© 允许规范转载
如果觉得我的文章对你有用，请尽情赞赏 🐶