千家信息网

请输入关键字词

热门搜索排行

最新搜索排行

导航： 首页 > 数据库 >

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

发表于：2024-11-21 作者：千家信息网编辑
千家信息网最后更新 2024年11月21日，0、导读在分页功能开发时，我们很习惯用LIMIT O,N的方法来取数据。这种方法在遇到超大分页偏移量时是会把MySQL搞死的，请别再这么写SQL了通常，我们会采用ORDER BY LIMIT star
千家信息网最后更新 2024年11月21日优化 | 再用传统分页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安全错误 数据库的锁怎样保障安全 数据库模式导航关系图 网络安全基地免费培训 目前我国的网络安全现状如何 i law数据库 数据库查询表的语句6 服务器为啥有两个cpu hp服务器供应商 网络安全防范论文大全 如何证明公司有数据库 win系统服务器安全组如何打开 软件开发规格什么 网络安全工程师 广州 腾讯云推出首款自研服务器星星海 滨海县网络安全知识竞赛 学校网络安全整治自查报告 福建特色软件开发制造价格 浙江大型软件开发咨询报价 数据库技术与运用慕课答案 listbox 数据库 停车场计时计费管理服务器 c 数据库添加数据 网游服务器 黄浦区现代化软件开发排行 广州考勤软件开发公司 用自己的宽带做服务器违法吗 浙江提供网络技术咨询热线 数据库和软件协调 南昌工学院计算机网络技术 vr网络安全教育系统 软件开发转做软件测试

相关文章

0