MySQL中limit优化

119 阅读 0 评论 79 点赞

我是靠谱客的博主仁爱电源，最近开发中收集的这篇文章主要介绍MySQL中limit优化，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

“
终于要对MySQL优化下手了，本文将对分页进行优化说明，希望可以得到一个合适你的方案
”

前言

分页这个话题已经是老生常谈了，但是有多少小伙伴一边是既希望优化的自己的系统，另一边在项目上还是保持自己独有的个性。

优化这件事是需要自己主动行动起来的，自己搞测试数据，只有在测试的路上才会发现更多你未知的事情。

本文咔咔也会针对分页优化这个话题进行解读。

一、表结构

这个数据库结构就是咔咔目前线上项目的表，只不过咔咔将字段名改了而已，还有将时间字段取消了。

数据库结构如下

CREATE TABLE `tp_statistics` (
  `ss_id` int(11) NOT NULL AUTO_INCREMENT,
  `ss_field1` decimal(11,2) NOT NULL DEFAULT '0.00',
  `ss_field2` decimal(11,2) NOT NULL DEFAULT '0.00',
  `ss_field3` decimal(11,2) NOT NULL DEFAULT '0.00',
  PRIMARY KEY (`ss_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3499994 DEFAULT CHARSET=utf8 COLLATE=utf8mb4_general_ci ROW_FORMAT=COMPACT;

登录后复制

根据以上信息可以看到目前表里边的数据有350万记录，接下来就针对这350W条记录进行查询优化。

二、初探查询效率

先来写一个查询的SQL语句，先看一下查询耗费的时间。

根据下图可以看到查询时间基本忽略不计，但是要注意的是limit的偏移量值。

于是我们要一步一步的加大这个偏移量然后进行测试，先将偏移量改为10000

可以看到查询时间还是非常理想的。

为了节省时间咔咔将这个偏移量的值直接调整到340W。

这个时候就可以看到非常明显的变化了，查询时间猛增到了0.79s。

出现了这样的情况，那肯定就需要进行优化了，拿起键盘就是干。

三、分析查询耗时的原因

提到分析SQL语句，必备的知识点就是explain，如果对这个工具不会使用的可以去看看MySQL的基础部分。

根据下图可以看到三条查询语句都进行了表扫描。

都知道只要有关于分页就必存在排序，那么加一个排序再来看一下查询效率。

然后在进行对排序的语句进行分析查看。

通过这里看到当使用了排序时数据库扫描的行数就是偏移量加上需要查询的数量。

此时就可以知道的是，在偏移量非常大的时候，就像上图案例中的limit 3400000,12这样的查询。

此时MySQL就需要查询3400012行数据，然后在返回最后12条数据。

前边查询的340W数据都将被抛弃，这样的执行结果可不是我们想要的。

咔咔之前看到相关文章说是解决这个问题的方案，要么直接限制分页的数量，要么就优化当偏移量非常大的时候的性能。

如果你都把本文看到了这里，那怎么会让你失望，肯定是优化大偏移量的性能问题。

四、优化

既然提到了优化，无非就那么俩点，加索引，使用其它的方案来代替这个方案。

咔咔提供的这条数据表结构信息，完全可以理解为就是图书馆的借阅记录，字段的什么都不要去关心就可以了。

对于排序来说，在这种场景下是不会给时间加排序的，而是给主键加排序，并且由于添加测试数据的原因将时间字段给取消了。

接下来使用覆盖索引加inner join的方式来进行优化。

select ss_id,ss_field1,ss_field2,ss_field3 from tp_statistics inner join ( select ss_id from tp_statistics order by ss_id limit 3000000,10) b using (ss_id);
登录后复制