🔥 热搜 🔥
11'"1'123456kN朱令去世一周年，清华学子控诉清华在朱令案中的冷血和无耻张靓颖抖音鱿鱼游戏朱令
分类
社会娱乐国际人权科技经济其它
🔥 热搜 🔥
11'"1'123456kN朱令去世一周年，清华学子控诉清华在朱令案中的冷血和无耻张靓颖抖音鱿鱼游戏朱令
分类
社会娱乐国际人权科技经济其它
查看原文
其他

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

松华老师&小鹿 知数堂 2023-03-11

 导 读

作者：松华老师&小鹿

松华老师：《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）

      


您可能也对以下帖子感兴趣

文章有问题？点此查看未经处理的缓存