数据科学MySQL

MySQL分页技巧

图片来源www.bleepingcomputer.com

最近需要为毕设的资讯系统添加一个类目下的文章列表的功能，需要从MySQL数据库取数据，第一时间想到了使用limit。没错，limit是MySQL为我们提供的一个好工具，但是limit如果使用不当也会造成数据读取速度非常慢。

使用limit m语句

最常见的写法就是

1
SELECT * FROM table_A limit m;

当时这个数据是取出前m行数据，很显然随着m的增大，时间也会逐渐增加，是一个正相关的关系，很显然我们还要使用分页，因此这种方案不被采纳。

使用limit m,n语句

这种方式和上述的方式比较有点不太一样了，LIMIT后面多了两个参数，m在这里表示从哪里开始取出数据，n表示取出多少行数据。大家可能会想，这种方式太棒了，正好符合分页的特征，但是我们看一下这个语句：

1
2
SELECT * FROM table_A limit 10, 10;
SELECT * FROM table_A limit 10000, 10;

很显然，的确都能够取出相关的数据，但是这种方式的弊端也很明显，就是当m非常大的时候，整个SQL语句的性能会急剧下降。前一条语句执行通常可能只需要0.001秒，而后面这条语句可能需要执行10秒，相隔几个数量级了。因为后面需要先扫描10000+10条数据，然后再抛弃前面的10000条数据，这个过程非常耗时。

复合索引

其实这里我提出了另一种解决的办法就是复合索引，如果需要使用WHERE语句和ORDER语句的话，我们可以知道无论LIMIT后面接的是什么数，都是需要等ORDER语句排序先执行完，然后才能开始进行切分。

和上一节一样，也是用limit m,n格式，比如下面这个查询：

1
2
3
4
SELECT title, url, SUBSTRING(content, 1, 150) as content FROM acs_post
WHERE category = "news"
ORDER BY pub_date DESC
LIMIT 10000, 10;

通过EXPLAIN查询它的执行计划时我们可以得到一下信息：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> EXPLAIN SELECT title, url, SUBSTRING(content, 1, 150) as content FROM acs_post WHERE category = "news" ORDER BY pub_date DESC LIMIT 10000, 10 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: acs_post
   partitions: NULL
         type: ref
possible_keys: category_index
          key: category_index
      key_len: 62
          ref: const
         rows: 38964
     filtered: 100.00
        Extra: Using index condition; Using filesort
1 row in set, 1 warning (0.01 sec)

可以看到，虽然使用单一的category_index索引，但是在Extra列还是出现了Using filesort文件排序的信息，说明这个SQL执行的效果很差。如果我们添加了comp_index(category, pub_date)索引之后，效果会有所不同，同样的SQL执行计划就不同了。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> EXPLAIN SELECT title, url, SUBSTRING(content, 1, 150) as content FROM acs_post WHERE category = "news" ORDER BY pub_date DESC LIMIT 10000, 10 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: acs_post
   partitions: NULL
         type: ref
possible_keys: category_index,comp_index
          key: comp_index
      key_len: 62
          ref: const
         rows: 38964
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

这次在Extra列没有了Using filesort文件排序信息了，另一个可以看到执行时间，上一条的执行时间是0.01sec，而这一次的执行时间是0.00sec。

使用where+limit m语句

这种方式通常是有一个顺序ID列的，比如：

1
SELECT * FROM acs_post WHERE id > 10000 LIMIT 10;

这个时候查找的行数就只有10条了，速度非常快，但是缺点就是一旦其中某一些行数被删除了，结果可能会不准确，因此需要建立一个表来维护这个ID列的信息，不管数据的插入还是删除都需要在这个表上同步一下，以后查询的时候直接从这个表取数据会非常快。

总结

优化LIMIT慢SQL的方式有很多，但是要依据自己的实际业务情况进行选择。通常首先做的事情就是检查相关的列有没有进行索引优化。如果没有的话，需要先进行索引优化。这样才能有效提升整体的性能。否则慢SQL可能会使你的系统直接崩掉。

分享到