MySQL深分页调优实战
商品评论系统数据量为十亿量级,因此对评论数据库做分库分表,单表的评论数据在百万级别。
每个商品的所有评论都是放在一个库的一张表里,确保作为用户在分页查询一个商品的评论时,一般都是直接从一个库的一张表里执行分页查询语句即可。
热门商品销量多达上百万,商品评论可能多达几十万条。有些用户就喜欢看商品评论,他就喜欢不停对某个热门商品评论不断进行分页,一页一页翻,有时候还会用上分页跳转功能,就是直接输入自己要跳到第几页。
这就涉及针对一个商品几十万评论的深分页问题。
简化后的对评论表进行分页查询的SQL: SELECT * FROM comments WHERE product_id ="xx" and is_good_comment="1" ORDER BY id desc LIMIT 100000,20
比如用户选择了查看某个商品的评论,因此必须限定 Product_id ,同时还选了只看好评,所以 is_good_commit 也要限定,
接着看第5001页评论,则limit的offset=(5001 - 1) * 20,20是每页的数量, 此时起始offset就是100000,所以limit后100000,20。
评论表最核心的索引 index_product_id ,所以正常肯定走这索引: 根据product_id ="xx"条件从表里先筛选出表里指定商品的评论数据 然后按照 is_good_comment="1",筛选出该商品评论数据里的所有好评了! 但 index_product_id 的索引里,并没有is_good_commet字段值,所以此时只能回表。即对该商品的每条评论,都要进行一次回表,根据id找到那条数据,取出is_good_comment字段值,接着对is_good_comment="1"条件做比对,筛选符合条件的数据。假设商品的评论有几十万条,岂不是要几十万次回表?虽然每次回表都是根据id在聚簇索引快速查找,但撑不住你每条数据都回表。 接着对筛选完毕的所有满足 WHERE product_id ="xx" and is_good_comment="1" 的数据,假设有十万条,就按id做倒序排序,此时还得基于临时磁盘文件进行倒序排序,又耗时很久 排序完毕,基于limit 100000,20获取第5001页的20条数据 最后返回
该过程有几十万次回表查询,还有十多万条数据的磁盘文件排序,所以要跑个1~2s。如何优化呢? 之前的案例基于商品品类去查商品表,是尽量避免对聚簇索引进行扫描,因为有可能找不到你指定的品类商品而出现聚簇索引全表扫描问题,所以强制使用联合索引,快速定位到数据,这过程中因无需回表,所以效率较高 还有案例直接根据id临时磁盘文件排序后找到20条分页数据,再回表查询20次,找到20条商品的完整数据。因此当时不涉及大量回表,所以这么做基本合适,性能通常1s内。
但本案例不是这样,因为 WHERE product_id ="xx" and is_good_comment="1"
这俩条件不是一个联合索引,所以会出现大量回表,耗时严重。
因此对该案例,一般采取如下方式改造分页查询语句: SELECT * from comments a, ( SELECT id FROM comments WHERE product_id = "xx" and is_good_comment = "1" ORDER BY id desc LIMIT 100000,20) b WHERE a.id = b.id
该SQL的执行计划就会彻底改变其执行方式。
通常先执行括号里的子查询,子查询反而会使用PRIMARY聚簇索引,按聚簇索引id值的倒序方向进行扫描,扫描过程中就把符合 WHERE product_id ="xx" and is_good_comment="1"
的数据筛选出来。
比如这里筛选出10w条数据,并不需要把符合条件的数据都找到,因为limit 100000,20,理论上,只要有100000+20条符合条件的数据,且按id有序的,此时就能执行根据limit 100000,20提取到5001页的这20条数据。
接着你会看到执行计划里会针对这个子查询的结果集,一个临时表,进行全表扫描,拿到20条数据,再对20条数据遍历,每条数据都按id去聚簇索引查找一下完整数据。
所以本案例,反而是优化成这种方式来执行分页,更合适,他只有一个扫描【聚簇索引】筛选符合你分页所有数据的成本: 分页越深,扫描数据越多 分页越浅,扫描数据就越少
然后再做一页20条数据的20次回表查询即可。当时做了该分页优化后,发现分页语句一下子执行时间降低到了几百ms,达到优化目的。
SQL调优没有银弹: 比如第二个案例,按顺序扫描聚簇索引方案可能会因为找不到数据导致亿级数据量的全表扫描,所以对第二个案例而 言,必须得根据二级索引去查找 但这第三个案例,因为前提是做了分库分表,评论表单表数据一般在百万左右,所以首先,他即使一个商品没有评论,有全表扫描,也绝对不会像扫描上亿数据表那么慢 其次,如果你根据 product_id 二级索引查找,反而可能出现几十w次回表查询,所以二级索引查找方式反而不适合,而按照聚簇索引顺序扫描的方式更好。
不同场景,要具体情况具体分析,到底慢在哪儿,再针对性优化。
准能自主设计地图采集指挥车助力无人驾驶截至3月16日,国家能源集团准能集团自主设计研发的地图采集指挥车已试运行15天,使用效果良好,能提高地图采集与上传的便利性和无人驾驶系统的运行效率。由于黑岱沟露天煤矿生产作业区域变
治理虚假WiFi不能只是打地鼠有事出门在外,又不想离开WiFi大概是很多互联网村民的共同心声。为了节约流量成本,一些用户企图通过连接免费WiFi的方式来获得更好的上网体验。然而,这些号称免费的WiFi软件不仅没
拼多多能否给网友一个回应?近日有一个游戏博主与拼多多的砍价免费拿活动,直播过程中邀请了两万多网友同时砍价,全网近四百万网友的观看,从砍价链接被屏蔽,到突然砍成功,一切来的莫名其妙。拼多多的砍价机制到底是怎样
华为天才少年稚晖君新作被指抄袭,网友却力挺,真相到底是什么?年薪百万的华为天才少年竟然抄袭?日前,就职于华为的硬核理工宅男稚晖君开发了一款又萌又硬的桌面机器人,强大的软硬件设计能力再次让网友们折服,但有一位视频UP主质疑这款软萌的小机器人是
6年重组未成愿史玉柱欲放手Playtika本报实习记者许心怡记者吴可仲北京报道近日,巨人网络(002558。SZ)放出拟出售PlaytikaHoldingCorp。(以下简称Playtika)的消息,巨人网络董事长史玉柱6
区块链的过去与未来在大部分的工作场景中,每个人的工作都或多或少的要与其他人的工作产生关联。在计算机尚未诞生或普及的年代,这种联系可能在文件末尾的各项签名中体现。而在计算机普及的现代,这种联系则体现在
套路不断的拼夕夕,还能走多远6万人砍拼多多到小数点后6位仍失败商家套路深似海,吾辈楷模拼夕夕。2020年底,拼夕夕年活跃买家数就以达到7。884亿,成为中国用户规模最大的电商平台。该平台的熟人社交模式,也曾风
元宇宙世界的虚与实1992年的小说雪崩被认为是美国科幻作家尼尔斯蒂芬森的一部重要作品。30年前的作品重新为人提起,其中一个原因是脸书为了在接下来5年至10年内转型成为元宇宙公司,去年改名Meta,将
国产已经用120W快充,为什么苹果还坚持20W慢充?割韭菜吗?(一)前言iPhone13发布会,这次依然没有采用快充。但是一些国产旗舰,比如iQOO8Pro小米MIX4等早就用上120W快充,并且这些手机的无线充电实力同样不俗。你觉得iPho
拼多多套路多,6万人关注几千人砍价不成功,官方回应来了3月17日,游戏主播超级小桀抱着试试看的心情,在直播过程中发动拼多多砍价免费领手机的活动。在直播间几万名观众的关注下,几千粉丝帮助中砍到了小数点后6位,耗时两个多小时,结果依然未能
vivoXFold曝光,vivoX60首当其冲一夜沦为百元机刷新低价纪录近日,此前曝光的vivo折叠屏手机vivoXFold又有了新消息。消息称,这款手机预计将于4月上市,内部代号为蝴蝶。值得一提的是,由于vivoXFold新机的曝光,导致vivoX6