MySQL升级5.6 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`)

• 回表，验证该元组对应的主键记录是否满足约束(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)

而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