文档章节

[MySQL优化案例]系列 — 分页优化

运维技术
 运维技术
发布于 2016/08/08 10:27
字数 1893
阅读 100
收藏 4

通常,我们会采用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查询耗时也有数十倍增加,显然不科学。今天我们就来分析下,如何能优化这个分页方案。 一般滴,想要优化分页的终极方案就是:没有分页,哈哈哈~~~,不要说我讲废话,确实如此,可以把分页算法交给Sphinx、Lucence等第三方解决方案,没必要让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条记录即可

据此,我们有两种相应的改写方法:子查询、表连接,即下面这样的:

#采用子查询的方式优化,在子查询里先从索引获取到最大id,然后倒序排,再取10行结果集
#注意这里采用了2次倒序排,因此在取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: <derived2>
 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: <derived2>
 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个优化后的新SQL执行时间:

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)
#采用子查询优化,从profiling的结果来看,相比原来的那个SQL快了:28.2%

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)
#采用INNER JOIN优化,从profiling的结果来看,相比原来的那个SQL快了:30.8%

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

#原始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)

#采用子查询优化
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: <derived2>
         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)
#采用子查询优化,从profiling的结果来看,相比原来的那个SQL快了:10.6%


#采用INNER JOIN优化
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优化,从profiling的结果来看,相比原来的那个SQL快了:30.2%

至此,我们看到采用子查询或者INNER JOIN进行优化后,都有大幅度的提升,这个方法也同样适用于较小的分页,虽然LIMIT开始的 start 位置小了很多,SQL执行时间也快了很多,但采用这种方法后,带WHERE条件的分页分别能提高查询效率:24.9%、156.5%,不带WHERE条件的分页分别提高查询效率:554.5%、11.7%,各位可以自行进行测试验证。单从提升比例说,还是挺可观的,确保这些优化方法可以适用于各种分页模式,就可以从一开始就是用。 我们来看下各种场景相应的提升比例是多少:

  大分页,带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,以保证每次都是直接数据文件或索引文件中读取。如果数据经过预热后,查询效率会一定程度提升,但但上述相应的效率提升比例还是基本一致的。

2014/07/28后记更新:

其实如果是不带任何条件的分页,就没必要用这么麻烦的方法了,可以采用对主键采用范围检索的方法,例如参考这篇:Advance for MySQL Pagination

本文转载自:http://imysql.com/2014/07/26/mysql-optimization-case-paging-optimize.shtml

运维技术
粉丝 108
博文 108
码字总数 111996
作品 1
广州
部门经理
私信 提问
关于tid与表分页

tid相关文档: Is there a rowid in Ingres? Working with large tables of Ingres 表分页trick: 分页查询的那些坑和各种技巧 [MySQL优化案例]系列 — 分页优化 select _ from *___table wh...

无敌小瘪四
2015/04/02
8
1
深入理解MySQL 5.7 GTID系列(九):实际案例一

导 读 作者:高鹏(重庆八怪) 原文地址: https://www.jianshu.com/p/2c25842d58d3 深入理解MySQL 5.7 GTID系列文章共十篇,本文为第四篇,点击查看: 第一篇:深入理解MySQL 5.7 GTID系列(...

老叶茶馆
2018/04/13
0
0
GROUP BY另类优化技巧

分享嘉宾:知数堂〖SQL开发优化班〗讲师郑松华,韩国Infobridge的SQL优化专家&7年SQL开发和调优经验&资深数据库工程师。 本次主题《GROUP BY另类优化技巧》,主要内容是从 GROUP BY、ORDER B...

iMySQL | 老叶茶馆
2017/04/15
0
0
优化案例 | CASE WHEN进行SQL改写优化

导读 今天给大家分享一个通过SQL改写而独辟蹊径的SQL优化案例 待优化场景 发现SLOW QUERY LOG中有下面这样一条记录: 实话说,看到这个SQL我也忍不住想骂人啊,究竟是哪个脑残的XX狗设计的?...

iMySQL | 老叶茶馆
2017/04/14
0
0
热门又高薪的大数据技术,怎么上车,前景如何?

IT江湖百晓生 主讲嘉宾:王晓伟微信号:goodwxw 萌萌哒 ^_^ 大牛背景: 本科:计算机学院-大连海事大学 硕士:软件工程硕士(MSE) -软件学院-清华大学 职场经历: 系统工程师-畅游 Hadoop工...

n88lpo
2017/11/23
0
0

没有更多内容

加载失败,请刷新页面

加载更多

用Python帮你上马,哪里无码打哪里

目录 0 引言 1 环境 2 需求分析 3 代码实现 4 代码全景展示 5 后记 0 引言 所谓的像素图,就是对图像做一个颗粒化的效果,使其产生一种妙不可言的朦胧感。费话不多说,先来看一张效果图。 <c...

上海小胖
22分钟前
1
0
python from import与import as 的含义

from os import makedirs, unlink, sep #从os包中引入 makedirs.unlink,sep类 from os.path import dirname, exists, isdir, splitext 从 os包中的path类中引入 dirmame exists 等方法 impo......

dillonxiao
23分钟前
1
0
【转】URL最大长度问题

今天在测试Email Ticket的时候发现在进行Mark as Read/Unread操作时,请求是通过GET方式进行的。URL中列出了所有参与该操作的Ticket Id。于是,我想起GET请求是有最大长度限制的。遂输入超长...

ZhangLG
25分钟前
0
0
Segment段

CurrentHashMap和HashMap相比支持并发操作,整个CurrentHashMap是由一个个的Segment组成的,也是就是常说的分段锁 Segment继承了重入锁ReentrantLock来进行加锁, 可以简单的把CurrentHashMa...

周慕云
26分钟前
0
0
JS Date 自定义格式化方法

JS Date 自定义格式化方法 Date 时间对象 快速 自定义格式化 定义方法 // 自定义格式化方法Date.prototype.format = function(fmt) { var o = { "M+" : this.getMonth()+1, ......

DrChenXX
31分钟前
1
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部