|
|
|
|
移动端

澳门金沙城娱乐开户:MySQL如何优化大分页查询?

本文来源:http://www.2233122.com/www_abi_com_cn/

太阳城娱乐网最快登入,  中央第十三巡视组专项巡视国家税务总局党组工作动员会召开  根据中央统一部署,2016年7月2日下午,中央第十三巡视组专项巡视国家税务总局党组工作动员会召开。无论是黑科技还是品牌,最终需要的是种瓜得瓜、水到渠成。梵高的弟弟提奥·梵高的曾孙威廉·梵高担任艺术顾问,这是双方首次携手策划、官方呈现的梵高体验展。  曾经有一段时间,只要在菜单上看到“干炒牛河”的字眼,我就像着了魔似的必点无疑,其实根本说不上是超爱吃,只是对于这个名字难以抗拒,难道真的是因为这四个字让我有了选择的理由?对于河粉,我倒也不会抗拒,作为北方姑娘,虽然还是更喜欢吃面条,河粉未免显得过于温柔矫情了。

也同样是当年,大家嘴里念叨的好货,是水货的三星I9000、裸眼3D的LGOptimus3D、甚至是当年指纹识别机摩托罗拉Atrix4G。几乎每次月经偶而就会出现一次,难道是身体老发病  其实经血中有血块是件好事,表示妳没有血友病,妳的血液很健康,除非妳正在服用抗凝血剂,否则在动脉及静脉以外的健康血液都会凝结。其文化内涵也与中国传统文化紧密结合,吉祥寓意丰富多彩,这些内涵有的源于狮子的形象,有的源于谐音,有的与外来文化有关。它是华夏儿女认祖归宗的纽带,是中华民族文化的积淀,是我们心灵的栖息地。

2016.07——,教育部党组书记;中共第十七届中央候补委员、第十八届中央委员。  2~3个月宝宝喝水果汁补充营养?  针对网上流传的2~3个月的宝宝就要喝一些水果汁补充维生素一事,有报道指出,该说法是谣言,宝宝在6个月前还不具备吃辅食的能力,如果过早给孩子喝果汁,可能会导致宝宝腹泻或便秘。此外,它还有精细的个性化分析能力,它能利用文本分析与心理语言学模型对海量社交媒体数据和商业数据进行深入分析,掌握用户个性特质,构建360度个体全景画像。到了三国、两晋和南北朝时期,由于佛教的盛行,狮子的形象频频出现在陵墓神道上和佛窟浮雕中。

大部分开发和DBA同行都对分页查询非常非常了解,看帖子翻页需要分页查询,搜索商品也需要分页查询。那么问题来了,遇到上千万或者上亿的数据量怎么快速的拉取全量;或者拥有百万千万粉丝的公众大号,给全部粉丝推送消息的场景。本文讲讲个人的优化分页查询的经验,抛砖引玉。

作者:Java技术架构杨奇龙来源:今日头条|2019-09-11 10:40

MySQL如何优化大分页查询?

一 背景

大部分开发和DBA同行都对分页查询非常非常了解,看帖子翻页需要分页查询,搜索商品也需要分页查询。那么问题来了,遇到上千万或者上亿的数据量怎么快速的拉取全量,比如大商家拉取每月千万级别的订单数量到自己独立的ISV做财务统计;或者拥有百万千万粉丝的公众大号,给全部粉丝推送消息的场景。本文讲讲个人的优化分页查询的经验,抛砖引玉。

二 分析

在讲如何优化之前我们先来看看一个比较常见错误的写法

  1. SELECT * FROM tablewhere kid=1342 and type=1 order id asc limit 149420 ,20; 

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

  1. order by col limit N,M 

MySQL 执行此类SQL时需要先扫描到N行,然后再去取M行。对于此类操作,获取前面少数几行数据会很快,但是随着扫描的记录数越多,SQL的性能就会越差,因为N的值越大,MySQL需要扫描越多的数据来定位到具体的N行,这样耗费大量的 IO 成本和时间成本。一图胜千言,我们使用简单的图来解释为什么 上面的sql 的写法扫描数据会慢。

t 表是一个索引组织表,key idxkidtype(kid,type) 。

MySQL 如何优化大分页查询?

符合kid=3 and type=1 的记录有很多行,我们取第 9,10行。

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

MySQL 是如何执行上面的sql 的?对于Innodb表,系统是根据 idxkidtype 二级索引里面包含的主键去查找对应的行。对于百万千万级别的记录而言,索引大小可能和数据大小相差无几,cache在内存中的索引数量有限,而且二级索引和数据叶子节点不在同一个物理块儿上存储,二级索引与主键的相对无序映射关系,也会带来大量的随机IO请求,N值越大越需要遍历大量索引页和数据叶,需要耗费的时间就越久。

MySQL 如何优化大分页查询?

鉴于上面的大分页查询耗费时间长的原因,我们思考一个问题,是否需要完全遍历“无效的数据”?如果我们需要limit 8,2;我们跳过前面8行无关的数据页遍历,可以直接通过索引定位到第9,第10行,这样操作是不是更快了?依然是一图胜千言,通过这其实也是 延迟关联的 核心思思:通过使用覆盖索引查询返回需要的主键,再根据主键关联原表获得需要的数据,而不是通过二级索引获取主键再通过主键去遍历数据页。

MySQL 如何优化大分页查询?

通过上面的原理分析,我们知道通过常规方式进行大分页查询慢的原因,也知道了提高大分页查询的具体方法 ,下面我们讨论一下在线上业务系统中常用的解决方法。

三 实践出真知

针对limit 优化有很多种方式:

1 前端加缓存、搜索,减少落到库的查询操作。比如海量商品可以放到搜索里面,使用瀑布流的方式展现数据,很多电商网站采用了这种方式。

2 优化SQL 访问数据的方式,直接快速定位到要访问的数据行。

3 使用书签方式 ,记录上次查询最新/大的id值,向后追溯 M行记录。

对于第二种方式 我们推荐使用"延迟关联"的方法来优化排序操作,何谓"延迟关联" :通过使用覆盖索引查询返回需要的主键,再根据主键关联原表获得需要的数据。

3.1 延迟关联

优化前

MySQL 如何优化大分页查询?

其执行时间:

MySQL 如何优化大分页查询?

优化后:

MySQL 如何优化大分页查询?

执行时间:

MySQL 如何优化大分页查询?

优化后 执行时间 为原来的1/3 。

3.2 使用书签的方式

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

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

其次 根据id 大于最小值或者小于最大值 进行遍历。

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

案例

当遇到延迟关联也不能满足查询速度的要求时

  1. SELECT a.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; 

MySQL 如何优化大分页查询?

使用延迟关联查询数据510ms ,使用基于书签模式的解决方法减少到10ms以内 绝对是一个质的飞跃。

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

MySQL 如何优化大分页查询?

太阳城娱乐网最快登入

四 小结

从我们的优化经验和案例上来讲,根据主键定位数据的方式直接定位到主键起始位点,然后过滤所需要的数据 相对比延迟关联的速度更快些,查找数据的时候少了二级索引扫描。但是 优化方法没有银弹,没有一劳永逸的方法。比如下面的例子

MySQL 如何优化大分页查询?

order by id desc 和 order by asc 的结果相差70ms ,生产上的案例有limit 100 相差1.3s ,这是为什么呢?留给大家去思考吧。

最后,其实我相信还有其他优化方式,比如在使用不到组合索引的全部索引列进行覆盖索引扫描的时候使用 ICP 的方式 也能够加快大分页查询。以上是我在优化分页查询方面的经验总结,抛砖引玉,有兴趣的朋友可以多交流,分享你们的优化经验案例。

【编辑推荐】

  1. MySQL单表数据不要超过500万行:是经验数值,还是黄金铁律?
  2. 数据库管理工具,你选对了吗?
  3. 一份完整的MySQL开发规范,进大厂必看!
  4. 记一次MySQL数据库升级导致授权失败的案例
  5. 一文看懂/MySQL数据库LnnoDB崩溃恢复机制
【责任编辑:庞桂玉 TEL:(010)68476606】

点赞 0
大家都在看
猜你喜欢

订阅专栏+更多

这就是5G

这就是5G

5G那些事儿
共15章 | armmay

120人订阅学习

16招轻松掌握PPT技巧

16招轻松掌握PPT技巧

GET职场加薪技能
共16章 | 晒书包

371人订阅学习

20个局域网建设改造案例

太阳城娱乐网最快登入20个局域网建设改造案例

网络搭建技巧
共20章 | 捷哥CCIE

765人订阅学习

读 书 +更多

高质量程序设计指南:C++/C语言(第3版)

本书以轻松幽默的笔调向读者论述了高质量软件开发方法与C++/C编程规范。它是作者多年从事软件开发工作的经验总结。本书共17章,第1章到第4...

订阅51CTO邮刊

点击这里查看样刊

订阅51CTO邮刊

51CTO服务号

51CTO官微

申博手机下载版 申博娱乐现金网直营 菲律宾太阳城申博直营网 申博138微信支付充值 申博会员登入 www.77msc.com
申博138娱乐 申博代理登录 777老虎机支付宝充值 申博免费开户官网登入 申博登录网址 申博管理平台登入
www.3158sun.com 太阳城亚洲官方网址登入 K7娱乐成游戏登入 申博太阳城游戏 太阳城电子游戏 申博正网存取款直营网