ITPub博客

首页 > 数据库 > MySQL > 排序sql升级5.6变慢原因分析

排序sql升级5.6变慢原因分析

原创 MySQL 作者:myownstars 时间:2015-10-24 01:57:56 2 删除 编辑
背景:
某业务DB从5.5升级5.6后,同一sql执行时间由毫秒级飙升到了20秒,sql文本如下

点击(此处)折叠或打开

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

摸索过程:

点击(此处)折叠或打开

  1. 查看当前执行计划
  2. *************************** 1. row ***************************
  3.            id: 1
  4.   select_type: SIMPLE
  5.         table: t
  6.          type: range
  7. possible_keys: idx_xx_id,idx_gmt_create
  8.           key: idx_gmt_create
  9.       key_len: 17
  10.           ref: NULL
  11.          rows: 6816016
  12.         Extra: Using index condition; Using where
  13. 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,其访问路径大致如下:
1 反向扫描idx_gmt_create叶子结点,搜寻(limit_type,xx_id)=(10,25773208367)的元组;
2 回表,验证该元组对应的主键记录是否满足约束(plan_type,user_id)=(1 or 3, 133174222100),满足则计数加1否则丢弃,继续扫描下一个元组;
3 当计数达到10时,停止扫描,将对应的10条记录返回给客户端;


点击(此处)折叠或打开

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

作为复合索引的引导列,limit_type字段的选择性惊人的低,这是查询变慢的主要原因之一。

之所以要强调”之一”,是因为针对本例,只要其他字段足够给力,即便limit_type=10也能很快执行完毕,
查看xx_id的分布情况,也是比较畸形,该sql又很不幸的选择了候选行最多的那个


点击(此处)折叠或打开

  1. root@ 04:01:12>select xx_id,count(*) from big_table where limit_type =10 group by xx_id order by xx_id desc;
  2. +-------------+----------+
  3. | xx_id | count(*) |
  4. +-------------+----------+
  5. | 25773208367 | 13352433 |
  6. | 25770261347 | 2 |
  7. | 258809681 | 148 |
  8. | 1 | 2100 |
  9. +-------------+----------+
  10. 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,其查询逻辑如下


点击(此处)折叠或打开

  1. select user_id,count(*) from big_table t
  2. where limit_type =10 and xx_id =25773208367
  3. and ( t.plan_TYPE = 1 or t.plan_TYPE=3 )
  4. group by user_id having count(*)>=10 plan by gmt_create desc limit 1,5;
  5. 执行结果
  6. +------------+----------+
  7. | user_id | count(*) |
  8. +------------+----------+
  9. | 1851362558 | 15 |
  10. | 2118141658 | 11 |
  11. | 2641244918 | 14 |
  12. | 2448823838 | 17 |
  13. | 16375410 | 32 |
  14. +------------+----------+
  15. 5 rows in set (1 min 12.42 sec)

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

而5.5版本的执行计划为


点击(此处)折叠或打开

  1. *************************** 1. row ***************************
  2.            id: 1
  3.   select_type: SIMPLE
  4.         table: t
  5.          type: range
  6. possible_keys: idx_xx_id
  7.           key: idx_xx_id
  8.       key_len: 18
  9.           ref: NULL
  10.          rows: 116
  11.         Extra: Using where; Using filesort

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


解决方案
1 修改sql,添加force index (idx_xx_id),此方案不够灵活;
2 修改sql,将排序字段从gmt_create改为gmt_modified,因无法采用索引排序5.6会选择idx_xx_id,此方案可能造成返回数据有误;
3 修改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又凑巧满足了各种坑,这算是一个意外吧。


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15480802/viewspace-1815984/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-03-18

  • 博文量
    375
  • 访问量
    3091430