加入收藏 | 设为首页 |

海底总动员-MySQL 怎么优化大分页查询?

海外新闻 时间: 浏览:360 次

专心于Java范畴优质技能,欢迎重视

作者:杨奇龙 javaadu

一 布景

大部分开发和DBA同行都对分页查询十分十分了解,看帖子翻页需求分页查询,查找产品也需求分页查询。那么问题来了,遇到上千万或许上亿的数据量怎样快速的拉取全量,比方大商家拉取每月千万等级的订单数量到自己独立的ISV做财政计算;或许具有百万千万粉丝的大众大号,给悉数粉丝推送音讯的场景。本文讲讲个人的优化分页查询的经历,抛砖引玉。

二 剖析

在讲怎么优化之前咱们先海底总动员-MySQL 怎么优化大分页查询?来海底总动员-MySQL 怎么优化大分页查询?看看一个比较常见过错的写法

SELECT * FROM tablewhere kid=1342 and type海底总动员-MySQL 怎么优化大分页查询?=1 order id asc limit 149420 ,20;

该SQL是一个十分典型的排序+分页查询:

order by col limit N,M

MySQL 履行此类SQL时需求先扫描到N行,然后再去取M行。关于此类操作,获取前面少量几行数据会很快,可是跟着扫描的记载数越多,SQL的功能就会越差,由于N的值越大,MySQL需求扫描越多的数据来定位到详细的N行,这样消耗许多的 IO 本钱和时刻本钱。一图胜千言,咱们运用简略的图来解说为什么 上面的sql 的写法扫描数据会慢。

t 表是一个索引安排表,key idxkidtypebeta(kid,type) 。


契合kid=3 and type=1 的记载有许多行,咱们取第 9,10行。

select * from t where kid =3 and type=1 order by id desc 8,2;

MySQL 是怎么履行上面的sql 的?关于Innodb表,体系是依据 idxkidtype 二级索引里边包括的主键去查找对应的行。关于百万千万等级的记载而言,索引巨细或许和数据巨细相差无几,cache在内存中的索引数量有限,并且二级索引和数据叶子节点不在同一个物理块儿上存储,二级索引与主键的相对无序映射联系,也会带来许多的随机IO恳求,N值越大越需求遍历许多索引页和数据叶,需求消耗的时刻就越久。


鉴于上面的大分页查询消耗时刻长的原因,咱们考虑一个问题,是否需求彻底遍历“无效的数据”?假如咱们需求limit 8,2;咱们越过前面8行无关的数据页遍历,能够直接经过索引定位到第9,第10行,这样操作是不是更快了?依然是一图胜千言,经过这其实也是 推迟相关的 中心思思:经过运用掩盖索引查询回来需求的主键,再依据主键相关原表取得需求的数据,而不是经过二级索引获取主键再经过主键去遍历数据页。


经过上面的原理剖析,咱们知道经过惯例办法进行大分页查询慢的原因,也知道了进步大分页查询的详细办法 ,下面咱们讨论一下在线上事务体系中常用的解决办法。

三 实践出真知

针对limit 优化有许多种办法:

1 前端加缓存、查找,削减落到库的查询操作。比方海量产品能够放到查找里边,运用瀑布流的办法展示数据,许多电商网站采用了这种办法。

2 优化SQL 拜访数据的办法,直接快速定位到要拜访的数据行。

3 运用书签办法 ,记载前次查询最新/大的id值,向后追溯 M行记载。

关于第二种办法 咱们引荐运用"推迟相关"的办法来优化排序操作,何谓"推迟相关" :经过运用掩盖索引查询回来需求的主键,再依据主键相关原表取得需求的数据。

3.1 推迟相关

优化前

其履行时刻:


优化后:

履行时刻:


优化后 履行时刻 为本来的1/3 。

3.2 运用书签的办法

首先要获取复合条件的记载的最大 id和最小id(默许id是主键)

select max(id) as maxid ,min(id) as minid from t where kid=2333 and type=1;

其次 依据id 大于最小值或许小于最大值 进行遍历。

select xx,xx from t where kid=2333 and type=1 and id >=min_id order by id asc limit 100;
select xx,xx from t where kid=2333 and type=1 and id <=max_id order by id desc limit 100;

事例

当遇到推迟相关也不能满意查询速度的要求时

SELECT a海底总动员-MySQL 怎么优化大分页查询?.id as id, clientid, adminid, kdtid, type, token, createdtime, updatetime, isvalid, version FROM t1 a, (SELECT id FROM t1 WHERE 1 and client_id = 'xxx' and is_valid= '1' order by kdt_id asc limit 267100,100 ) b WHERE a.id = b.id;



运用推迟相关查询数据510ms ,运用依据书签形式的解决办法削减到10ms以内 肯定是一个质的腾跃。

SELECT * FROM t1 where clientid='xxxxx' and isvalid=1 and id<47399727 order by id desc LIMIT 100;



四 小结

从咱们的优化经历和事例上来讲,依据主键定位数据的办法直接定位到主键开始位点,然后过滤所需求的数据 相比照推迟相关的速度更快些,查找数据的时分少了二级索引扫描。可是 优化办法没有银弹,没有一了百了的办法。比方下面的比如


order by id desc 和 order by asc 的成果相差70ms ,生产上的事例有limit 100 相差1.3s ,这是为什么呢?留给我们去考虑吧。

最终,其实我信任还有其他优化办法,比方在运用不到组合索引的悉数索引列进行掩盖索引扫描的时分运用 ICP 的办法 也能够加速大分页查询。以上是我在优化分页查询方面的经历总结,抛砖引玉,有爱好的朋友能够多沟通,共享你们的优化经历事例。