背景：
?某业务DB从MySQL 5.5升级MySQL 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，其访问路径大致如下：
1 反向扫描idx_gmt_create叶子结点，搜寻(limit_type,xx_id)=(10,25773208367)的元组；
2 回表，验证该元组对应的主键记录是否满足约束(plan_type,user_id)=(1 or 3, 133174222100)，满足则计数加1否则丢弃，继续扫描下一个元组；
3 当计数达到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 plan 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? plan by gmt_create desc limit 1,5;
执行结果
+------------+----------+
?| 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过滤后的候选行非常少，故执行时间很短。
解决方案
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又凑巧满足了各种坑，这算是一个意外吧。