当前位置:首页 > 短网址资讯 > 正文内容

MySQL升级5.6 SQL变慢调优案例

www.ft12.com6年前 (2017-07-21)短网址资讯1870


背景:

某业务DB从5.5升级5.6后,同一sql执行时间由毫秒级飙升到了20秒,sql文本如下

select * from big_table as t
where ( t.plan_TYPE = 1 or t.plan_TYPE=3 )
and t.limit_TYPE in (10)
and t.xx_ID = 25773208367
and t.USER_ID in (133174222100)
plan by t.gmt_create desc , t.ID desc limit 1,10

以下是解决过程:

查看短网址当前执行计划

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
         type: range
possible_keys: idx_xx_id,idx_gmt_create
          key: idx_gmt_create
      key_len: 17
          ref: NULL
         rows: 6816016
        Extra: Using index condition; Using where
1 row in set (0.00 sec)

表上短网址数据库的索引分布

  PRIMARY KEY (`id`),
  KEY `idx_xx_id` (`xx_id`,`plan_type`,`user_id`),
  ……
  KEY `idx_gmt_create` (`limit_type`,`xx_id`,`gmt_create`)

该sql选择索引idx_gmt_create,因其符合最左前缀策略,故排序没有使用到filesort,其访问路径大致如下:


  • 反向扫描idx_gmt_create叶子结点,搜寻(limit_type,xx_id)=(10,25773208367)的元组;

  • 回表,验证该元组对应的主键记录是否满足约束(plan_type,user_id)=(1 or 3, 133174222100),满足则计数加1否则丢弃,继续扫描下一个元组;

  • 当计数达到10时,停止扫描,将对应的10条记录返回给客户端; 


root@ 03:20:56>select limit_type,count(*) from big_table group by limit_type;
+-------------+----------+
| limit_type | count(*) |
+-------------+----------+
| NULL | 226865 |
| 9 | 463346 |
| 10 | 13353116 |
+-------------+----------+
3 rows in set (3.13 sec)

作为复合索引的引导列,limit_type字段的选择性惊人的低,这是查询变慢的主要原因之一。之所以要强调”之一”,是因为针对本例,只要其他字段足够给力,即便limit_type=10也能很快执行完毕,查看xx_id的分布情况,也是比较畸形,该sql又很不幸的选择了候选行最多的那个。

root@ 04:01:12>select xx_id,count(*) from big_table where limit_type =10 group by xx_id order by xx_id desc;
+-------------+----------+
| xx_id | count(*) |
+-------------+----------+
| 25773208367 | 13352433 |
| 25770261347 | 2 |
| 258809681 | 148 |
| 1 | 2100 |
+-------------+----------+
4 rows in set (5.79 sec)

如果xx_id=1,该sql最多只需要比较2100条记录即可返回,会很快执行完毕,然后返回短网址背后对应的原始长地址;

即便xx= 25773208367,如果能快速找出满足非索引字段约束的主键记录,sql也会很快执行完毕,mysql是在验证了海量的(limit_type,xx_id)=(10,25773208367)元组后,才凑齐10条同时满足(plan_type,user_id)约束的主键记录,据此我们可以反推出最早满足所有约束条件的user_id,其查询逻辑如下:

select user_id,count(*) from big_table t where limit_type =10 and xx_id =25773208367 and ( t.plan_TYPE = 1 or t.plan_TYPE=3 ) group by user_id having count(*)>=10 order by gmt_create desc limit 1,5; 5 rows in set (1 min 12.42 sec)

执行结果:

+------------+----------+
| user_id | count(*) |
+------------+----------+
| 1851362558 | 15 |
| 2118141658 | 11 |
| 2641244918 | 14 |
| 2448823838 | 17 |
| 16375410 | 32 |
+------------+----------+
5 rows in set (1 min 12.42 sec)

随便挑一个替换25773208367,比如1851362558,执行计划没有变,原本需要运行20多秒的sql却在200毫秒内执行完毕。

 

而5.5版本的执行计划为:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
         type: range
possible_keys: idx_xx_id
          key: idx_xx_id
      key_len: 18
          ref: NULL
         rows: 116
        Extra: Using where; Using filesort

虽然采用了filesort,但是经索引idx_xx_id过滤后的候选行非常少,故执行时间很短。


 

解决方案

修改sql,添加force index (idx_xx_id),此方案不够灵活;

修改sql,将排序字段从gmt_create改为gmt_modified,因无法采用索引排序5.6会选择idx_xx_id,此方案可能造成返回数据有误;

修改sql,将t.limit_TYPE in (10)改为t.limit_TYPE >9 and t.limit_TYPE <11,优化器会认为sql没有满足索引最左前缀便不再使用idx_gmt_create,这招似乎有点贱,同时说明MySQL优化器还不够智能。

 

结束语

5.6优化器做了大量改进,以本sql为例,让其选择了idx_gmt_create从而省去了filesort,之所以运行变慢了是因为表字段数据分布太不均匀,而本sql又凑巧满足了各种坑,这算是一个意外吧。




扫描二维码推送至手机访问。

版权声明:本文由短链接发布,如需转载请注明出处。

本文链接:https://www.ft12.com/article_313.html

分享给朋友:

相关文章

月薪5万的人都经历过什么?

为了方便起见,人们习惯用数字概括不同的生活状态,比如:《月薪5千穿搭指南》、《月薪2万餐厅指南》。如果你生活在北上广这些竞争压力较大的城市,会发现这类流行的指南所覆盖的人群,大多对自己的薪资并不满意。很多年轻人在新世相后台焦虑地留言,描述自...

淘宝&支付宝:连续一段时间未登录将销号

淘宝&支付宝:连续一段时间未登录将销号

日前阿里巴巴对旗下淘宝、支付宝平台的服务协议进行了修改,对于用户的权利和义务进行了更细致的界定,同时规定在淘宝、支付宝平台的非活跃用户在一定时间之后将会进行销号处理。淘宝平台方面,此次新增加规定,用户不得以任何方式转让,否则淘宝平台有权追究...

论坛私信推广的准确操作姿态

论坛私信推广的准确操作姿态

本人操作过的项目许多,每一个简直都是从零起步,从没想过把一切资源整合到一同,错过了太多用户,错过了太多粉丝,我在短网址行业没有一个兄弟,多年来陪我的即是那几台电脑和日夜运行的软件。但是今天我不是来抱怨的,言归正传,分享给咱们一个亲自操作的案...

高效排查短网址系统故障:高并发引起的系统设计“踩坑”案例

高效排查短网址系统故障:高并发引起的系统设计“踩坑”案例

短网址的判定逻辑十分复杂,一方面是各种类型的网址,有正常的短链接,另外是短网址保证用户的使用,特别是商家的影响面非常广,任何一个小故障都可能引发一些社会问题,所以FT12短网址对产品的质量,对服务的连续性有严格的要求。FT12短网址的技术人...

生成的短链接的后面几位字母忘了怎么办?

 在短链接的日常使用中,经常会发生一件很尴尬的事情:当你想要打开一个自己以前生成的短连接的时候,发现最后几位字母忘了,特别是短连接最后几位还是区分大小的。如果大小写记不清楚,仍然无法打开正确的网址。那么这个时候该怎么办呢?小编这里...

详解KAFKA是如何做到1秒发布百万级条消息的

详解KAFKA是如何做到1秒发布百万级条消息的

KAFKA是分布式发布-订阅音讯体系,是一个分布式的,可划分的,冗余备份的持久性的日志服务。它首要用于处理活跃的流式数据。如今被广泛地应用于构建实时数据管道和流应用的场景中,具有横向拓展,容错,快等优点,并现已运行在许多大中型公司的出产环境...

发表评论

访客

◎欢迎参与讨论,请在这里发表您的看法和观点。