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

最新推荐文章于 2022-12-31 17:50:11 发布

老叶茶馆_

最新推荐文章于 2022-12-31 17:50:11 发布

阅读量337

点赞数

导读

作者：松华老师&小鹿

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

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

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

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

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

一、概述

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

二、先看慢SQL

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

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) ;
+------------+-------+
| createtime | count |
+------------+-------+
| 2018-09-21 | 306 |
+------------+-------+
1 row in set (22.72 sec)

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

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
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: card
type: ALL
possible_keys: idx_UserId,idx_State_CreateTime
key: NULL
key_len: NULL
ref: NULL
rows: 7433073
Extra: Using where; Using temporary; Using filesort
1 row in set, 1 warning (0.00 sec)

三、思路以及背景

1. 了解SQL含义

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

mysql> select count(*) from card;
+----------+
| count(*) |
+----------+
| 7433073|
+----------+
1 row in set (1.04 sec)

2.使用虚拟列

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

select version();
+------------+
| version() |
+------------+
| 5.6.35-log |
+------------+
1 row in set (0.00 sec)

3.考虑增加索引

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

4. 利用已有的索引

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

show index from card\G
*************************** 1. row ***************************
Table: card
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: AutoId
Collation: A
Cardinality: 7433215
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: card
Non_unique: 1
Key_name: idx_UserId
Seq_in_index: 1
Column_name: UserId
Collation: A
Cardinality:4141
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 3. row ***************************
Table: card
Non_unique: 1
Key_name: idx_State_CreateTime
Seq_in_index: 2
Column_name: CreateTime
Collation: A
Cardinality: 7433215
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:

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

select distinct state FROM card;
+-------+
| state |
+-------+
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
+-------+
7 rows in set (0.01 sec)

改写SQL，增加 state in (0,1,2,3,4,5,6) ，让SQL利用已有低效率索引，SQL执行速度从22s-->0s

#SQL执行计划
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
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: card
type: range
possible_keys: idx_UserId,idx_State_CreateTime
key:idx_State_CreateTime
key_len:9
ref:NULL
rows: 322
Extra: Using index condition；Using where; Using temporary; Using filesort
1 row in set, 1 warning (0.00 sec)

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

#SQL执行计划
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
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: card
type: range
possible_keys: idx_UserId,idx_State_CreateTime
key:idx_State_CreateTime
key_len:9
ref: NULL
rows:439
Extra: Using index condition；Using where; Using temporary;
1 row in set, 1 warning (0.00 sec)

四、后记

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

中秋节快乐