MySQL中分页优化的实例详解_MySQL-Mysql Tutorial-php.cn

Home

Database

Mysql Tutorial

MySQL中分页优化的实例详解_MySQL

Jun 01, 2016 pm 01:00 PM

mysql

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

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

Copy after login

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

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

Copy after login

一般而言，分页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)

Copy after login

可以看到，随着分页数量的增加，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

Copy after login

可以看到，虽然通过主键索引进行扫描了，但第二个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

Copy after login

#采用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

Copy after login

然后我们来对比下这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%

Copy after login

我们再来看一个不带过滤条件的分页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%

Copy after login

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

201558115710029.jpg (649×215)

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

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

Statement of this Website

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn