千家信息网

请输入关键字词

热门搜索排行

最新搜索排行

导航： 首页 > 数据库 >

优化 | 再用传统分页SQL你就死定了

发表于：2025-11-09 作者：千家信息网编辑
千家信息网最后更新 2025年11月09日，0、导读在分页功能开发时，我们很习惯用LIMIT O,N的方法来取数据。这种方法在遇到超大分页偏移量时是会把MySQL搞死的，请别再这么写SQL了通常，我们会采用ORDER BY LIMIT star
千家信息网最后更新 2025年11月09日优化 | 再用传统分页SQL你就死定了

0、导读

在分页功能开发时，我们很习惯用LIMIT O,N的方法来取数据。这种方法在遇到超大分页偏移量时是会把MySQL搞死的，请别再这么写SQL了

通常，我们会采用ORDER BY LIMIT start, offset 的方式来进行分页查询。例如下面这个SQL：

SELECT * FROM `t1` WHERE ftype=1 ORDER BY id DESC LIMIT 100, 10;

或者像下面这个不带任何条件的分页SQL：

SELECT * FROM `t1` ORDER BY id DESC LIMIT 100, 10;


一般而言，分页SQL的耗时随着 start 值的增加而急剧增加，我们来看下面这2个不同起始值的分页SQL执行耗时：

yejr@imysql.com> SELECT * FROM `t1` WHERE ftype=1

ORDER BY id DESC LIMIT 500, 10;

…

10 rows in set (0.05 sec)


yejr@imysql.com> SELECT * FROM `t1` WHERE ftype=6

ORDER BY id DESC LIMIT 935500, 10;

…

10 rows in set (2.39 sec)


可以看到，随着分页数量的增加，SQL查询耗时也有数十倍增加，显然不科学。


今天我们就来分析下，如何能优化这个分页方案。


一般滴，想要优化分页的终极方案就是：没有分页，哈哈哈~~~，不要说我讲废话，确实如此，可以把分页算法交给Solr、Lucene、Sphinx等第三方解决方案，尤其是遇到有模糊搜索的需求时，没必要让MySQL来做它不擅长的事情。


当然了，有小伙伴说，用第三方太麻烦了，我们就想用MySQL来做这个分页，咋办呢？莫急，且待我们慢慢分析。


先看下表DDL、数据量、查询SQL的执行计划等信息：

yejr@imysql.com> SHOW CREATE TABLE `t1`;

CREATE TABLE `t1` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

...

`ftype` tinyint(3) unsigned NOT NULL,

...

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;


yejr@imysql.com> select count(*) from t1;

+----------+

| count(*) |

+----------+

| 994584 |

+----------+


yejr@imysql.com> EXPLAIN SELECT * FROM `t1` WHERE ftype=1

ORDER BY id DESC LIMIT 500, 10\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: t1

type: index

possible_keys: NULL

key: PRIMARY

key_len: 4

ref: NULL

rows: 510

Extra: Using where


yejr@imysql.com> EXPLAIN SELECT * FROM `t1` WHERE ftype=1

ORDER BY id DESC LIMIT 935500, 10\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: t1

type: index

possible_keys: NULL

key: PRIMARY

key_len: 4

ref: NULL

rows: 935510

Extra: Using where


可以看到，虽然是通过主键索引扫描数据的，但第二个SQL需要扫描的记录数太大了，而且需要先扫描约935510条记录，然后再根据排序结果取10条记录，这肯定是非常慢了。


针对这种情况，我们的优化思路就比较清晰了，有两点：

  1. 尽可能从索引中直接获取数据，避免或减少再次扫描行数据的次数（也就是我们通常所说的避免回表）；

  2. 尽可能减少扫描的记录数，也就是先确定起始的范围，再往后取N条记录。


根据上面这两种优化思路，有相应的SQL改写方法：子查询、表连接，像下面这样的：

#方法一

#采用子查询的方式优化，在子查询里先从索引获取到最大id，然后倒序排，再取10行结果集

#注意这里采用了两次倒序排，因此在取LIMIT的start值时，比原来的值加了10，即935510，否则结果将和原来的不一致


yejr@imysql.com> EXPLAIN SELECT * FROM (SELECT * FROM `t1` WHERE

id > ( SELECT id FROM `t1` WHERE ftype=1

ORDER BY id DESC LIMIT 935510, 1) LIMIT 10) t ORDER BY id DESC\G

*************************** 1. row ***************************

id: 1

select_type: PRIMARY

table:

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 10

Extra: Using filesort

*************************** 2. row ***************************

id: 2

select_type: DERIVED

table: t1

type: ALL

possible_keys: PRIMARY

key: NULL

key_len: NULL

ref: NULL

rows: 973192

Extra: Using where

*************************** 3. row ***************************

id: 3

select_type: SUBQUERY

table: t1

type: index

possible_keys: NULL

key: PRIMARY

key_len: 4

ref: NULL

rows: 935511

Extra: Using where


#方法二

#采用INNER JOIN优化，JOIN子句里也优先从索引获取ID列表，然后直接关联查询获得最终结果，这里不需要加10


yejr@imysql.com> EXPLAIN SELECT * FROM `t1` INNER JOIN

( SELECT id FROM `t1` WHERE ftype=1

ORDER BY id DESC LIMIT 935500,10) t2 USING (id)\G

*************************** 1. row ***************************

id: 1

select_type: PRIMARY

table:

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 935510

Extra: NULL

*************************** 2. row ***************************

id: 1

select_type: PRIMARY

table: t1

type: eq_ref

possible_keys: PRIMARY

key: PRIMARY

key_len: 4

ref: t2.id

rows: 1

Extra: NULL

*************************** 3. row ***************************

id: 2

select_type: DERIVED

table: t1

type: index

possible_keys: NULL

key: PRIMARY

key_len: 4

ref: NULL

rows: 973192

Extra: Using where


然后来对比下这2个优化后的执行时间/代价：

#1、子查询优化：从profiling的结果来看，相比原来耗时减少 28.2%

yejr@imysql.com> SELECT * FROM (SELECT * FROM `t1` WHERE

id > ( SELECT id FROM `t1` WHERE ftype=1

ORDER BY id DESC LIMIT 935510, 1) LIMIT 10) T ORDER BY id DESC;

...

rows in set (1.86 sec)


#2、INNER JOIN优化：从profiling的结果来看，相比原来耗时减少30.8%

yejr@imysql.com> SELECT * FROM `t1` INNER JOIN

( SELECT id FROM `t1` WHERE ftype=1

ORDER BY id DESC LIMIT 935500,10) t2 USING (id);

...

10 rows in set (1.83 sec)


再来看一个不带过滤条件的分页SQL对比：

#1、原始SQL

yejr@imysql.com> EXPLAIN SELECT * FROM `t1` ORDER BY id DESC LIMIT 935500, 10\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: t1

type: index

possible_keys: NULL

key: PRIMARY

key_len: 4

ref: NULL

rows: 935510

Extra: NULL


yejr@imysql.com> SELECT * FROM `t1` ORDER BY id DESC LIMIT 935500, 10;

...

10 rows in set (2.22 sec)



#2、采用子查询优化，相比原来耗时减少10.6%

yejr@imysql.com> EXPLAIN SELECT * FROM (SELECT * FROM `t1` WHERE

id > ( SELECT id FROM `t1` ORDER BY id DESC

LIMIT 935510, 1) LIMIT 10) t ORDER BY id DESC;

*************************** 1. row ***************************

id: 1

select_type: PRIMARY

table:

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 10

Extra: Using filesort

*************************** 2. row ***************************

id: 2

select_type: DERIVED

table: t1

type: ALL

possible_keys: PRIMARY

key: NULL

key_len: NULL

ref: NULL

rows: 973192

Extra: Using where

*************************** 3. row ***************************

id: 3

select_type: SUBQUERY

table: t1

type: index

possible_keys: NULL

key: PRIMARY

key_len: 4

ref: NULL

rows: 935511

Extra: Using index


yejr@imysql.com> SELECT * FROM (SELECT * FROM `t1` WHERE

id > ( SELECT id FROM `t1` ORDER BY id DESC

LIMIT 935510, 1) LIMIT 10) t ORDER BY id DESC;

…

10 rows in set (2.01 sec)



#3、采用INNER JOIN优化，相比原来耗时减少30.2%

yejr@imysql.com> EXPLAIN SELECT * FROM `t1` INNER JOIN

( SELECT id FROM `t1`ORDER BY id DESC

LIMIT 935500,10) t2 USING (id)\G

*************************** 1. row ***************************

id: 1

select_type: PRIMARY

table:

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 935510

Extra: NULL

*************************** 2. row ***************************

id: 1

select_type: PRIMARY

table: t1

type: eq_ref

possible_keys: PRIMARY

key: PRIMARY

key_len: 4

ref: t1.id

rows: 1

Extra: NULL

*************************** 3. row ***************************

id: 2

select_type: DERIVED

table: t1

type: index

possible_keys: NULL

key: PRIMARY

key_len: 4

ref: NULL

rows: 973192

Extra: Using index


yejr@imysql.com> SELECT * FROM `t1` INNER JOIN

( SELECT id FROM `t1`ORDER BY id DESC

LIMIT 935500,10) t2 USING (id);

…

10 rows in set (1.70 sec)


至此，我们看到采用子查询或者INNER JOIN进行优化后，都有大幅度的提升，这个方法也同样适用于较小的分页。


说下结论，子查询和INNER JOIN分页优化方法的提升效率是：

  • 带WHERE条件的分页分别能提高查询效率：24.9%、156.5%；

  • 不带WHERE条件的分页分别提高查询效率：554.5%、11.7%。


单从提升比例说，还是挺可观的。而且这两种优化方法基本上可适用于各种分页模式，强烈建议一开始就改成这种SQL写法习惯。


我们来看下各种场景相应的提升比例是多少：


大分页，带WHERE大分页，不带WHERE大分页平均提升比例小分页，带WHERE小分页，不带WHERE总体平均提升比例
子查询优化28.20%10.60%19.40%24.90%554.40%154.53%
INNER JOIN优化30.80%30.20%30.50%156.50%11.70%57.30%

这样看就和明显了，尤其是针对大分页的情况，因此我们优先推荐使用INNER JOIN方式优化分页算法。


上述每次测试都重启mysqld实例，并且加了SQL_NO_CACHE，以保证每次都是直接数据文件或索引文件中读取。如果数据经过预热后，查询效率会一定程度提升，但上述相应的效率提升比例还是基本一致的。


相关阅读：

[MySQL优化案例]系列 - discuz!热帖翻页优化




老叶茶馆镇店之宝，扫码识别或访问 http://yejinrong.com 直达
很赞哦！
查询 方法 数据 结果 效率 索引 大分 条件 比例 方式 方案 一致 也就是 倒序 小分 尽可能 思路 情况 文件 第三方 数据库的安全要保护哪些东西 数据库安全各自的含义是什么 生产安全数据库录入 数据库的安全性及管理 数据库安全策略包含哪些 海淀数据库安全审计系统 建立农村房屋安全信息数据库 易用的数据库客户端支持安全管理 连接数据库失败ssl安全错误 数据库的锁怎样保障安全 网络技术论文大全 交互式边缘计算服务器报价 客户端软件开发架构 全球最大的服务器商 服务器 风扇 控制 蜜汁顿鱿鱼跟网络安全是什么关系 国家网络安全宣传提出四招 常熟信息化软件开发专业服务 数据库关系链接类型有哪三个 全国网络安全保卫先进单位 服务器文件返回 礼当家互联网科技公司联 服务器实时备份软件 甘肃智慧社区软件开发哪儿好 葫芦岛民宿软件开发 未知网络安全吗 怀旧服哪个服务器刷新信号强 计算机网络技术论文致谢模板 电脑用网络安全模式开机不能关机 关系数据库的表不具备的性质 江阴网络软件开发公司 网络安全周ctf大赛 网络安全主题班会ppt大学生 云服务器怎么开通安全组 古井集团网络安全升级改造 网络技术无罪的辩解 sa中怎么描述数据库 大数据跟软件开发有什么不同 中国研发数据库公司 乌海市2020年网络安全

相关文章

0