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

最新推荐文章于 2024-04-29 13:40:23 发布

栁罗风尘

最新推荐文章于 2024-04-29 13:40:23 发布

阅读量808

点赞数

分类专栏： mysql

mysql 专栏收录该内容

16 篇文章 0 订阅

订阅专栏

大表分页查询优化

原文地址： 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/order-by-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(*)

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

4.3 避免使用offset

我们要改变用户习惯：

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

5 使用更多条件加快搜索

5.1 创建时间分页

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

页码	信息id数	链接
第一页	150 111 102 101 100	Next: page=2&last_seen=100&dir=next
第二页	98 97 96 95 94	Prev: page=1&last_seen=98&dir=prev Next: page=3&last_seen=94&dir=next
第三页	93 92 91 90 89	Prev: page=2&last_seen=93&dir=prev Next: page=4&last_seen=89&dir=next

根据页码信息，我们是按照时间排序，而创建时间和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 投票数分页

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

页码	投票数	链接
第一页	99 99 98 98 98	Next: page=2&last_seen=100&dir=next
第二页	98 98 97 97 10	Prev: page=1&last_seen=98&dir=prev Next: page=3&last_seen=94&dir=next

我们不能使用:

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 都会显著降低性能

在这里插入图片描述

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

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

7. google在做什么

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

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

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

在这里插入图片描述

码字不易，打赏一下小弟吧

栁罗风尘

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
1
评论
mysql大表分页查询优化(翻译)

大表分页查询优化文章目录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 避免使用offset5 使用更多条件加快搜索5.1 创建时间分页5.2 投票数分页5.2.1 寻找额外的条件5.2.2 解决方案5.2...
复制链接

扫一扫