业务系统数据库设计经验总结(六)-MySQL中ORDER BY LIMIT分页数据性能问题

【问题】
MySQL中,LIMIT的性能随着N的不断增大会急剧下降,但是分页这种设计又是随处可见的。
在上一篇(https://www.cnblogs.com/bruceChan0018/p/15191693.html)通过实际场景分析了为什么LIMIT N当N很大时会消耗系统的性能,并在最后一部分延伸说明了一个优化LIMIT的思路。但是按照其中的说法,要求还是苛刻的,即id不仅要自增,还必须连续。如果系统的数据删除了,id不连续,那不是没法用这个办法了?
------------------------------------------------------------------------------------
【环境】
先试用上一篇中的SQL脚本创建好表和基础数据,然后我们使用一个存储过程,批量插入到100万条数据。
#存储过程插入批量数据
#注意这个过程中最好将autocommit设置为OFF(set autocommit = 0;),关闭自动提交,执行完后再恢复为ON(set autocommit = 1;)。

DROP PROCEDURE IF EXISTS `batch_insert_goods`;
DELIMITER $$
CREATE PROCEDURE batch_insert_goods()
BEGIN
DECLARE increase_id INTEGER;

#上一次插入了27调数据,这里从id=28开始插入
set increase_id=28;

START TRANSACTION;
WHILE increase_id<1000000 DO
INSERT INTO `goods_order`(`id`, `goods_name`, `goods_type_id`, `price`) VALUES (increase_id, 123, 1, ROUND(RAND()*10));
set increase_id=increase_id+1;
END WHILE;
COMMIT;
END$$

DELIMITER ;
#调用存储过程
CALL batch_insert_goods();

------------------------------------------------------------------------------------
【分析】

select * from goods_order order by goods_name,id limit 0,10
select * from goods_order order by goods_name,id limit 1000000,10

这两句在服务器中,第一条是服务器处理10条数据,返回排序好的10条;第二条是服务器要处理10010条数据的排序,最后返回末尾的10条数据,两者的消耗相差好几个数量级。无论MySQL优化器内部怎么优化,第二条的性能肯定都是远远不及第一条。

[注意:此时goods_name未加索引]

业务系统数据库设计经验总结(六)-MySQL中ORDER BY LIMIT分页数据性能问题

按照之前的思路,我们此时应该缩小排序范围,让其在有限的数据中进行排序。如:
select * from goods_order limit where id>=1000004 order by goods_name,id limit 0,10;
业务系统数据库设计经验总结(六)-MySQL中ORDER BY LIMIT分页数据性能问题

如果id自增且连续,或者我们在这里已经能够准确获取到id的临界值,那么构造出来的查询语句性能当然会比我们直接排序10010条数据好很多,当数据量很大且查询服务高频时,这样的优化方向就是我们需要的。但是有两个问题摆在面前:
1.如果id不连续怎么办,这个where中的条件数据该怎么界定?
2.如果不是按照id排序,而是其它的字段,比如一个特殊的金额,日期,字符串时我们又该怎么处理呢?
------------------------------------------------------------------------------------
【缩小范围】
关于缩小范围,常见的做法是子语句来确定范围。无论是直接定位到查找的临界值,还是通过索引覆盖的字段来排序定位到指定的临界值,最终查找外层内容时,这个堆的大小一定是很小的了。

假如我们要对这些数据按照日期进行排序(日期靠后的id也会更大),且日期在表中不重复,那么一个可行的方案就是:
在分页时不允许随机挑选页面码,只能从上到下或者从下到上去翻页。此时在前端请求数据时,可以将上一页的页码以及最大最小id一并传入,后端做判断,可以找到这个where条件对应的id,而limit后只需要设置单页条数即可。

SELECT * FROM goods_order WHERE id>${last_max_id} ORDER BY create_time LIMIT 10;

这其实是一种思路,不管我们是用什么手段,只要我们缩小了范围,那么这种查询效率必定大大提高。

再如:我们知道上下边界值以及对应的页码,那么我们可以在一个合理的范围内,对这批数据进行排序:
SELECT * FROM goods_order WHERE id between ${given_min_id} and ${given_max_id} ORDER BY create_time LIMIT 10;

具体的场景中可以灵活使用这种手段。
------------------------------------------------------------------------------------
【延迟关联】
思路是利用索引在子语句中找到我们需要的关联字段的值,在外层通过关联字段找到我们需要的数据。

[注意:此时goods_name已经添加了索引]

业务系统数据库设计经验总结(六)-MySQL中ORDER BY LIMIT分页数据性能问题
要注意的是,子语句中的查找字段排序字段都需要添加索引。另外外层如果使用where进行范围确定的话,有些时候查询速度会特别慢,这个问题我们放到以后分析。

------------------------------------------------------------------------------------
【终极杀器】
如果我们能够从源头限制住使用这种查询的场景,那么问题自然也就不存在了。比如只允许用户查询固定多少页的数据:
业务系统数据库设计经验总结(六)-MySQL中ORDER BY LIMIT分页数据性能问题

这是淘宝的PC端页面,当你搜索商品时,下面的翻页最大数总是100页。我去看了下京东,也是这样。
而在当当的电子书系列中,我看到了陈列的商品是下拉时才获取最新一页的数据,这就符合我们上述的只让用户查询后一页的数据,所以你无限翻下去性能上也没多大变化。
业务系统数据库设计经验总结(六)-MySQL中ORDER BY LIMIT分页数据性能问题

所以,在进行业务沟通时,我们需要将这种技术上的考虑跟产品进行充分地沟通,并在此基础上设计一个合理的业务方案。

 

业务系统数据库设计经验总结(六)-MySQL中ORDER BY LIMIT分页数据性能问题

上一篇:曹路删除部门权限sql


下一篇:mysql 修改删除表