不用改表，老司机只用了这招提升千倍性能

 导 读

作者：松华老师&小鹿

松华老师：《SQL优化课程》讲师

小鹿：《SQL优化课程》嫡传弟子||知数堂助教妹纸||DBA一枚

新公司报到第一天，就遇到了一条慢SQL，无法改动的低效率索引

紧急求助了松华老师，妥妥地，轻松带飞~

知数堂只分享干货，各精品课程讲授的都是职场实用技能

一、概述

有些情况下，线上环境会有一些不允许做变更的大表，存在低效率的索引，导致某些查询非常缓慢。由于不能变更，又想提高SQL执行效率，就需要一些剑走偏锋的技巧。

二、 先看慢SQL

• 查看SQL执行时长，执行时间为22s

1. SELECT date_format(CreateTime, '%Y-%m-%d') AS createtime, COUNT(*) AS count FROM card  WHERE userid <> 10000   AND createtime >= '2018-09-21' GROUP BY date(CreateTime) ;

2. +------------+-------+

3. | createtime | count |

4. +------------+-------+

5. | 2018-09-21 | 306 |

6. +------------+-------+

7. 1 row in set (22.72 sec)

• 查看SQL执行计划，并未使用到任何索引，属于全表扫描，并且由于使用了group by，所以还出现了Using temporary; Using filesort 等降低SQL执行效率的情况。 
  

1. desc SELECT date_format(CreateTime, '%Y-%m-%d') AS createtime, COUNT(*) AS count FROM card WHERE userid <> 10000 AND createtime >= '2018-09-21' GROUP BY date(CreateTime) \G

2. *************************** 1. row ***************************

3.           id: 1

4.  select_type: SIMPLE

5.        table: card

6.         type: ALL

7. possible_keys: idx_UserId,idx_State_CreateTime

8.          key: NULL

9.      key_len: NULL

10.          ref: NULL

11.         rows: 7433073

12.        Extra: Using where; Using temporary; Using filesort

13. 1 row in set, 1 warning (0.00 sec)

三、 思路以及背景

1. 了解SQL含义

该SQL的含义就是在card表中找出userid不在10000中，且时间大于'2018-09-21' 的数据，然后根据日期进行分组，并统计每个日期有多少条数据。 
表数据量

1. mysql> select count(*) from card;

2. +----------+

3. | count(*) |

4. +----------+

5. | 7433073|

6. +----------+

7. 1 row in set (1.04 sec)

2.使用虚拟列

如果为MySQL 5.7版本，可以考虑增加一个虚拟列date(CreateTime)，并增加相应的索引,达到提高SQL执行效率的目的。 
但是很可惜MySQL版本为5.6.35，还没有虚拟列这个功能，并且不能进行修改表结构，此方案驳回。

1. select version();

2. +------------+

3. | version() |

4. +------------+

5. | 5.6.35-log |

6. +------------+

7. 1 row in set (0.00 sec)

3.考虑增加索引

由于 userid <> 10000 采用的是不等于，所以必定不能增加userid的索引；考虑增加createtime的索引，时间字段一般分布均匀。 
但是由于不能进行修改表结构，此方案驳回。

4. 利用已有的索引

• 经查看，表中存在一个索引idx_State_CreateTime (state，CreateTime )，可以注意到由于CreateTime 的位置为索引的第二列，且state并未在语句中出现，所以无法用到该索引。 
  

1. show index from card\G

2. *************************** 1. row ***************************

3.        Table: card

4.   Non_unique: 0

5.     Key_name: PRIMARY

6. Seq_in_index: 1

7.  Column_name: AutoId

8.    Collation: A

9.  Cardinality: 7433215

10.     Sub_part: NULL

11.       Packed: NULL

12.         Null:

13.   Index_type: BTREE

14.      Comment:

15. Index_comment:

16. *************************** 2. row ***************************

17.        Table: card

18.   Non_unique: 1

19.     Key_name: idx_UserId

20. Seq_in_index: 1

21.  Column_name: UserId

22.    Collation: A

23.  Cardinality:4141

24.     Sub_part: NULL

25.       Packed: NULL

26.         Null:

27.   Index_type: BTREE

28.      Comment:

29. Index_comment:

30. *************************** 3. row ***************************

31.        Table: card

32.   Non_unique: 1

33.     Key_name: idx_State_CreateTime

34. Seq_in_index: 2

35.  Column_name: CreateTime

36.    Collation: A

37.  Cardinality: 7433215

38.     Sub_part: NULL

39.       Packed: NULL

40.         Null:

41.   Index_type: BTREE

42.      Comment:

43. Index_comment:

• 查看state数据, state表分成7种数据 0,1,2,3,4,5,6，state的量不多。

1. select distinct state FROM card;

2. +-------+

3. | state |

4. +-------+

5. | 0 |

6. | 1 |

7. | 2 |

8. | 3 |

9. | 4 |

10. | 5 |

11. | 6 |

12. +-------+

13. 7 rows in set (0.01 sec)

• 改写SQL，增加 state in (0,1,2,3,4,5,6) ，让SQL利用已有低效率索引，SQL执行速度从22.72 s-->0.01s,效率优化了2272倍。
  

1. #SQL执行计划

2. desc SELECT date_format(CreateTime, '%Y-%m-%d') AS createtime, COUNT(*) AS count FROM card WHERE userid <> 10000 and state in (0,1,2,3,4,5,6) AND createtime >= '2018-09-21' GROUP BY date(CreateTime) \G

3. *************************** 1. row ***************************

4.           id: 1

5.  select_type: SIMPLE

6.        table: card

7.         type: range

8. possible_keys: idx_UserId,idx_State_CreateTime

9.          key:idx_State_CreateTime

10.      key_len:9

11.          ref:NULL

12.         rows: 322

13.        Extra: Using index condition；Using where; Using temporary; Using filesort

14. 1 row in set, 1 warning (0.00 sec)

• 改写SQL，小技巧使用order by null 去掉using filesort 
  

1. #SQL执行计划

2. desc SELECT date_format(CreateTime, '%Y-%m-%d') AS createtime, COUNT(*) AS count FROM card WHERE userid <> 10000 and state in (0,1,2,3,4,5,6) AND createtime >= '2018-09-21' GROUP BY date(CreateTime)  order by null \G

3. *************************** 1. row ***************************

4.           id: 1

5.  select_type: SIMPLE

6.        table: card

7.         type: range

8. possible_keys: idx_UserId,idx_State_CreateTime

9.          key:idx_State_CreateTime

10.      key_len:9

11.          ref: NULL

12.         rows:439

13.        Extra: Using index condition；Using where; Using temporary;

14. 1 row in set, 1 warning (0.00 sec)

四、后记

其实优化这个SQL并不难，难点在于不能对该表结构做任何的修改。如果是MySQL5.7 可以通过虚拟列的方式进行优化；针对idx_State_CreateTime索引，该索引将选择度低的字段放到了前面，并不是一个很好的索引，但是非常情况非常手段，我们选择了将state字段所有可能性放入SQL中，使之能利用到索引。 

中秋节快乐

知数堂《SQL优化课程》

郑松华老师讲授，第5期开课中

上课时间：每周五、日，20:30-22:30

希望给大家漫漫的SQL优化之旅，带来不一样的火花

扫码直达郑老师试听课

《MySQL 8.0 SQL 新特性-CTE 、窗口函数的应用和优化》

加入QQ技术交流群

扫码加入知数堂4群-王者峡谷

（QQ群号：650149401）