mysql大表分页查询优化(翻译)

Myth丶恋晨 2022-03-01 13:52 420阅读 0赞

> 大表分页查询优化

### 文章目录 ###

*   *   *  1. 分页查询核心点
         *  2 索引使用
         *  3. 使用实例
         *   *  3.1 表结构
             *  3.2 典型的查询
             *   *  3.2.1 获取总的记录数
             *  3.2.2 获取当前页信息
             *  3.3 查看性能
             *  3.4 性能影响
         *  4. 解决方案
         *   *  4.1 简单解决方案
             *  4.2 避免使用Count(\*)
             *  4.3 避免使用offset
         *  5 使用更多条件加快搜索
         *   *  5.1 创建时间分页
             *  5.2 投票数分页
             *   *  5.2.1 寻找额外的条件
                 *  5.2.2 解决方案
                 *  5.2.3 更好的方式
         *  6. 为什么不能使用offest
         *  7. google在做什么

原文地址： [https://www.slideshare.net/suratbhati/efficient-pagination-using-mysql-6187107][https_www.slideshare.net_suratbhati_efficient-pagination-using-mysql-6187107]

### 1. 分页查询核心点 ###

– 使用索引来过滤 rows (解决通过 WHERE)  
– 使用相同的索引按排序顺序返回行 (解决通过 ORDER)

参考资料：  
– [http://dev.mysql.com/doc/refman/5.1/en/mysql-indexes.html][http_dev.mysql.com_doc_refman_5.1_en_mysql-indexes.html]  
– [http://dev.mysql.com/doc/refman/5.1/en/order-by-optimization.html][http_dev.mysql.com_doc_refman_5.1_en_order-by-optimization.html]  
– [http://dev.mysql.com/doc/refman/5.1/en/limit-optimization.html][http_dev.mysql.com_doc_refman_5.1_en_limit-optimization.html]

### 2 索引使用 ###

建立索引 a\_b\_c (a, b, c)

`ORDER` 将会使用索引

– ORDER BY a 
    – ORDER BY a,b 
    – ORDER BY a, b, c 
    – ORDER BY a DESC, b DESC, c DESC

WHERE 和 ORDER 都将使用索引:

– WHERE a = const ORDER BY b, c 
    – WHERE a = const AND b = const ORDER BY c 
    – WHERE a = const ORDER BY b, c 
    – WHERE a = const AND b > const ORDER BY b, c

ORDER 将不会使用索引 (文件排序)

– ORDER BY a ASC, b DESC, c DESC /* 多种排序方向 */ 
    – WHERE g = const ORDER BY b, c /* a 前缀不存在 */ 
    – WHERE a = const ORDER BY c /* b 丢失 */ 
    – WHERE a = const ORDER BY a, d /* d 不在索引中 */

### 3. 使用实例 ###

> 我们将通过两种使用情景来分析分页:  
> • 通过时间分页, 最近的消息排序分页 可以直接使用id代替  
> • 通过thumps\_up(投票数), 点赞数多的排前面

#### 3.1 表结构 ####

CREATE TABLE `message` (
      `id` int(11) NOT NULL AUTO_INCREMENT, 
      `title` varchar(255) COLLATE utf8_unicode_ci NOT NULL, 
      `user_id` int(11) NOT NULL, 
      `content` text COLLATE utf8_unicode_ci NOT NULL, 
      `create_time` int(11) NOT NULL, 
      `thumbs_up` int(11) NOT NULL DEFAULT 0, /* Vote Count */ 
      PRIMARY KEY (`id`), 
      KEY `thumbs_up_key` (`thumbs_up`,`id`)
     ) ENGINE=InnoDB

查看表相关信息

mysql> show table status like message \G;
    
      Engine: InnoDB 
      Version: 10 
      Row_format: Compact 
      Rows: 50000040 /* 50 Million */ 
      Avg_row_length: 565 
      Data_length: 28273803264 /* 26 GB */ 
      Index_length: 789577728 /* 753 MB */ 
      Data_free: 6291456 
      Create_time: 2009-04-20 13:30:45

#### 3.2 典型的查询 ####

##### 3.2.1 获取总的记录数 #####

SELECT count(*) FROM message

#### 3.2.2 获取当前页信息 ####

SELECT * FROM message ORDER BY id DESC LIMIT 0, 20

注意: id 是自增的, 和 create\_time 顺序相同, 不需要在create\_time创建索引, 节省空间

#### 3.3 查看性能 ####

mysql> explain SELECT * FROM message ORDER BY id DESC LIMIT 10000, 20 \G;
    
     ***************** 1. row ************** 
     id: 1 
     select_type: SIMPLE 
     table: message 
     type: index 
     possible_keys: NULL 
     key: PRIMARY 
     key_len: 4 
     ref: NULL 
     rows: 10020 
     Extra: 
    
     1 row in set (0.00 sec)

– 能使用索引扫描和读取行并在找到需要的行数后停止.  
– LIMIT 10000, 20 意味着他需要读取 10020 行，并且抛弃前 10000 行, 然后返回接下来的 20 行.

#### 3.4 性能影响 ####

– 非常大的 OFFSET 将会带来数据集的显著增长, MySQL 将携带数据到内存并不会返回给调用者  
– 当您拥有不适合主内存的数据库时，性能问题会更加明显.  
– 使用大型OFFSET的小百分比请求将能够达到磁盘 I/O 瓶颈  
– 为了显示 1000,000 行数据中的 21 - 40数据 , 有时候需要计算1000,000行.

### 4. 解决方案 ###

#### 4.1 简单解决方案 ####

– 不要显示中的记录数, 用户真的会关心吗?  
– 不要让用户跳转到更深层的页码所在的页面

#### 4.2 避免使用Count(\*) ####

1.  绝不显示总的信息, 让用户查看更多信息通过点击next
2.  不要计算每个请求, 缓存他, 显示陈旧的计数, 用户不会关心是 324533 还是 324633
3.  显示 41 to 80 of Thousands
4.  使用预先计算(触发器或缓存), insert/delete 发生时自增或自减相应的值.

更多参考：[https://blog.csdn.net/wujiangwei567/article/details/88721395][https_blog.csdn.net_wujiangwei567_article_details_88721395]

#### 4.3 避免使用offset ####

我们要改变用户习惯：

– 不要直接跳转到第 N 页  
– `LIMIT N` 是好的, 不要使用 `LIMIT M,N`  
– 提供给定起始页面的额外`where`条件  
– 使用给定的线索和更多的`WHERE`条件 以及`ORDER BY`和不带`OFFSET`的 `LIMIT N`

### 5 使用更多条件加快搜索 ###

#### 5.1 创建时间分页 ####

比如说每页显示5条信息 对应的页码，列表id和相应的上一页下一页链接如下

<table> 
 <thead> 
  <tr> 
   <th>页码</th> 
   <th>信息id数</th> 
   <th>链接</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>第一页</td> 
   <td>150 111 102 101 100</td> 
   <td>Next: page=2&amp;last_seen=100&amp;dir=next</td> 
  </tr> 
  <tr> 
   <td>第二页</td> 
   <td>98 97 96 95 94</td> 
   <td>Prev: page=1&amp;last_seen=98&amp;dir=prev Next: page=3&amp;last_seen=94&amp;dir=next</td> 
  </tr> 
  <tr> 
   <td>第三页</td> 
   <td>93 92 91 90 89</td> 
   <td>Prev: page=2&amp;last_seen=93&amp;dir=prev Next: page=4&amp;last_seen=89&amp;dir=next</td> 
  </tr> 
 </tbody> 
</table>

根据页码信息，我们是按照时间排序，而创建时间和id的顺序是一致的，所以我们可以进行如下筛选来达到  
更快的查询体验

Next(下一页)

WHERE id < 100  /* 通过参数 last_seen 获取 */ 
    ORDER BY id DESC LIMIT 5 /* 没有 OFFSET 偏移量，显示5条数据 */

Prev(前一页)

WHERE id > 98  /* 通过参数 last_seen 获取 */ 
    ORDER BY id ASC LIMIT 5 /* 没有 OFFSET 偏移量，显示5条数据 */

这里前一页的数据返回时如果按照最近的思想，我们需要对结果集做一个倒序的排序

性能解析：

mysql> explain SELECT * FROM message WHERE id < 49999961 ORDER BY id DESC LIMIT 20 \G; 
    
    *************************** 1. row *************************** 
    id: 1 
    select_type: SIMPLE 
    table: message 
    type: range 
    possible_keys: PRIMARY 
    key: PRIMARY 
    key_len: 4 
    ref: NULL 
    Rows: 25000020 /* ignore this */ 
    Extra: Using where 
    
    1 row in set (0.00 sec)

#### 5.2 投票数分页 ####

> 我们假设 每页的投票数和对应的链接如下：

我们不能使用:

WHERE thumbs_up < 98 ORDER BY thumbs_up DESC /* 他将会返回很少的行 */

那我们可以这样做么:

WHERE thumbs_up <= 98 AND <额外的条件> ORDER BY thumbs_up DESC

##### 5.2.1 寻找额外的条件 #####

• 考虑thumbs\_up作为主要的编号  
– 如果我们有额外的次要编号，我们可以使用major和minor的组合作为额外条件

• 找到额外的列 (次要编号)  
– 我们可以使用id主键作为次要编号

##### 5.2.2 解决方案 #####

第一页

mysql> SELECT thumbs_up, id FROM message ORDER BY thumbs_up DESC, id DESC LIMIT 5
     +-----------+----+
     | thumbs_up | id | 
     +-----------+----+ 
     | 99 | 14 | 
     | 99 | 2  | 
     | 98 | 18 | 
     | 98 | 15 | 
     | 98 | 13 | 
     +-----------+----+

Next(下一页)

mysql> SELECT thumbs_up, id FROM message WHERE thumbs_up <= 98 AND (id < 13 OR thumbs_up < 98) ORDER BY thumbs_up DESC, id DESC LIMIT 5 
    
     +-----------+----+ 
     | thumbs_up | id | 
     +-----------+----+ 
     | 98 | 10 | 
     | 98 | 6  |
     | 97 | 17 | 
     +-----------+----+

解释一下子： 我们这里投票数 `thumbs_up` 相同的前提下 id做的是倒序排列，那么如果是相同的thumbs\_up  
下一页的id肯定小于 13 或者 thumbs\_up 小于当前数

##### 5.2.3 更好的方式 #####

SELECT * 
     FROM message 
     WHERE thumbs_up <= 98 AND (id < 13 OR thumbs_up < 98) 
     ORDER BY thumbs_up DESC, id DESC LIMIT 20

改写为：

SELECT m2.* 
    FROM message m1, message m2 
    WHERE m1.id = m2.id AND m1.thumbs_up <= 98 AND (m1.id < 13 OR m1.thumbs_up < 98) 
    ORDER BY m1.thumbs_up DESC, m1.id DESC LIMIT 20;

我们看一下性能分析

*************************** 1. row *************************** 
    id: 1 
    select_type: SIMPLE 
    table: m1 
    type: range possible_keys: PRIMARY,thumbs_up_key 
    key: thumbs_up_key /* (thumbs_up,id) */ 
    key_len: 4 
    ref: NULL 
    Rows: 25000020 /*忽略这个 我们仅仅读 20 行*/ 
    Extra: Using where; Using index /* Cover */ 
    
    *************************** 2. row *************************** 
    id: 1 
    select_type: SIMPLE 
    table: m2 
    type: eq_ref 
    possible_keys: PRIMARY 
    key: PRIMARY 
    key_len: 4 
    ref: forum.m1.id 
    rows: 1 
    Extra:

我们仅仅做了一个全连接，m2 仅仅扫描了1行 也是用了key和对应的索引

有的人说使用

SELECT *
    FROM
        message
    WHERE
        (thumbs_up,id) > (98,14)
    ORDER BY thumbs_up, id
    LIMIT 5

这样写确实可以提高效率，但是仍然没有上面的两种写法快

### 6. 为什么不能使用offest ###

> 主键排序 和 次键排序 如果使用offest 都会显著降低性能

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d1amlhbmd3ZWk1Njc_size_16_color_FFFFFF_t_70]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d1amlhbmd3ZWk1Njc_size_16_color_FFFFFF_t_70 1]  
通过统计图表可以看出:  
– Using LIMIT OFFSET, N  
• 600 query/sec

– Using LIMIT N (no OFFSET)  
• 3.7k query/sec

### 7. google在做什么 ###

. 估计有多少结果。再一次，谷歌这样做，没有人抱怨  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d1amlhbmd3ZWk1Njc_size_16_color_FFFFFF_t_70 2]

. 不要显示所有结果。甚至谷歌也不会让你看到百万分之一的结果。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d1amlhbmd3ZWk1Njc_size_16_color_FFFFFF_t_70 3]

. 不显示总计数或其他页面的中间链接。仅显示“下一个”链接。

![在这里插入图片描述][2019032218054755.png]

码字不易，打赏一下小弟吧  
![70][]

[https_www.slideshare.net_suratbhati_efficient-pagination-using-mysql-6187107]: https://www.slideshare.net/suratbhati/efficient-pagination-using-mysql-6187107
[http_dev.mysql.com_doc_refman_5.1_en_mysql-indexes.html]: http://dev.mysql.com/doc/refman/5.1/en/mysql-indexes.html
[http_dev.mysql.com_doc_refman_5.1_en_order-by-optimization.html]: http://dev.mysql.com/doc/refman/5.1/en/order-by-optimization.html
[http_dev.mysql.com_doc_refman_5.1_en_limit-optimization.html]: http://dev.mysql.com/doc/refman/5.1/en/limit-optimization.html
[https_blog.csdn.net_wujiangwei567_article_details_88721395]: https://blog.csdn.net/wujiangwei567/article/details/88721395
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d1amlhbmd3ZWk1Njc_size_16_color_FFFFFF_t_70]: /images/20220301/30d0f28113e6440c8bfc42d1f3b9dc2e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d1amlhbmd3ZWk1Njc_size_16_color_FFFFFF_t_70 1]: /images/20220301/bc510e57ed054f6d875b5f84b0b2bde1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d1amlhbmd3ZWk1Njc_size_16_color_FFFFFF_t_70 2]: /images/20220301/a011116577744dca8c82f703b0f53f21.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d1amlhbmd3ZWk1Njc_size_16_color_FFFFFF_t_70 3]: /images/20220301/8556872b9de54669be428e0ef675d4b0.png
[2019032218054755.png]: /images/20220301/76dcce57ba584e649d7e1e22c8f4bc2b.png
[70]: /images/20220301/3b027de9dbf048febfb9453a33a1c60d.png