MySQL优化 之 慢查询案例分析与优化

1 慢查询优化基本步骤

（1）判断运行时是否真的很慢；

（2）定位性能瓶颈（是因为在数据访问消耗了太多的时间，还是在数据的运算（如分组排序等）方面花费了太多资源）；

（3）采用where条件进行查询，对每个字段分别查询，看哪个字段的区分度最高；

（4）explain查看执行计划；

（5）了解业务方使用场景；

（6）根据需求对索引进行新增或修改，参照建索引规约和索引优化的原则；

（7）观察结果，不符合预期继续从第一步开始分析。

2 慢查询常见案例

以test数据库中的employe表为例，首先看一下存在哪些索引：

SHOW INDEX FROM test.employe;

得到如下结果：

|Non_unique|Key_name   |Seq_in_index|Column_name|Collation|Cardinality|Index_type|
| 0        |PRIMARY    | 1          | id        | A       | 0         | BTREE    | 
| 1        |idx_user_no| 1          | user_no   | A       | 0         | BTREE    | 
| 1        |idx_name   | 1          | name      | A       | 0         | BTREE    |

案例1：条件中带or

EXPLAIN SELECT * FROM employe WHERE user_no='1001' OR age=15;

|id|select_type|table  |type|possible_keys|key|key_len|ref|rows|Extra      | 
|1 |SIMPLE     |employe|ALL |idx_user_no  |   |       |   |3   |Using where|

说明：从上面的例子可知，user_no是索引列，但是age不是索引列，当查询条件里带or，并且存在不是索引列，那么查询是性能最差的全表扫描。

结论：当使用or的情况下，如果不是每一列的条件都有索引，索引失效。

案例2：模糊查询时以%开头

EXPLAIN SELECT * FROM employe WHERE name LIKE '%udy';

|id|select_type|table  |type|possible_keys|key|key_len|ref|rows|Extra      | 
|1 |SIMPLE     |employe|ALL |             |   |       |   |3   |Using where|

说明：从上面的例子可知，name是索引列，但是模糊查询时的匹配规则是以%开始的，那么查询是性能最差的全表扫描。

结论：当使用like的时候，以%开头（%udy 或者 %u%），索引失效。

案例3：类型转化

EXPLAIN SELECT * FROM employe WHERE name = 123;

|id|select_type|table  |type|possible_keys|key|key_len|ref|rows|Extra      | 
|1 |SIMPLE     |employe|ALL | idx_name    |   |       |   |3   |Using where|

说明：从上面的例子可知，name是varchar类型的字符串，但是条件传入的数据是数值类型，Mysql对类型进行了转化，查询是性能最差的全表扫描。

结论：如果列类型是字符串（varchar 、char），那一定要在条件中将数据使用引号引用起来，否则索引失效。

案例4：条件里进行数学计算

EXPLAIN SELECT * FROM employe WHERE id -1 > 123;

|id|select_type|table  |type|possible_keys|key|key_len|ref|rows|Extra      | 
|1 |SIMPLE     |employe|ALL |             |   |       |   |3   |Using where|

说明：从上面的例子可知，id是主键索引，但是条件中存在数学计算，查询是性能最差的全表扫描。

结论：如果条件中存在数学计算或者转换（运算 、时间转化、范围查找等），那么索引失效。

---

第二部分：

假如将上述的user_no、name以及from_date设置为联合索引，那么存在的索引：

SHOW INDEX FROM test.employe;

|Non_unique|Key_name |Seq_in_index|Column_name|Collation|Cardinality|Index_type|
| 0        |PRIMARY    | 1        |id         | A       | 0         | BTREE    | 
| 1        |idx_employe| 1        |user_no    | A       | 0         | BTREE    | 
| 1        |idx_employe| 2        |name       | A       | 0         | BTREE    | 
| 1        |idx_employe| 3        |from_date  | A       | 0         | BTREE    |

最左前缀匹配规则<user_no，name，from_date>

EXPLAIN SELECT * FROM test.employe WHERE user_no='10001';

|id|select_type|table  |type|possible_keys|key        |key_len|ref  |rows|Extra| 
|1 |SIMPLE     |employe|ref |idx_employe  |idx_employe|99     |const|1   |     |

当查询条件精确匹配索引的左边连续一个或几个列时，如<user_no>或<user_no, name>，只能用到一部分索引，即条件所组成的最左前缀。上面的查询从分析结果看用到了PRIMARY索引，但是key_len为4，说明只用到了索引的第一列前缀。

案例5：部分索引列起作用

EXPLAIN SELECT * FROM test.employe WHERE user_no='10001' AND from_date='1986-06-26';

|id|select_type|table  |type|possible_keys|key    |ref  |rows|Extra            | 
|1 |SIMPLE |employe|ref |idx_employe |idx_employe|const|1|Using index condition|

说明：由于中间的某个条件（name）没有提供，所以只使用了索引的第一列（user_no），虽然后面的from_date也在索引中，但是由于name不存在而无法和左前缀连接，因此需要对结果进行扫描过滤from_date。
解决方法：增加一个辅助索引<user_no, from_date>
结论：组合索引如果缺失中间部分的索引列，会导致后面的索引列失效。

案例6：组合索引失效

EXPLAIN SELECT * FROM test.employe WHERE name='Judy' AND from_date='1986-06-26';

|id|select_type|table  |type|possible_keys|key|key_len|ref|rows|Extra      | 
|1 |SIMPLE     |employe|ALL |             |   |       |   |1   |Using where|

说明：组合索引的顺序（<user_no，name，from_date>），如果查询条件中没有匹配到索引的第一列（user_no），会导致后面的索引列失效，查询是性能最差的全表扫描。
结论：组合索引如果缺失第一个索引列，会导致索引失效。

案例7：索引字段上使用is null / is not null

EXPLAIN SELECT * FROM test.employe WHERE name is null;

|id  |select_type  |table  |type   |possible_keys|key    |key_len|ref|rows|Extra      | 
 |1   |SIMPLE       |employe|ALL    |             |       |       |   |1   |Using where|

说明：索引字段（name）上使用 is null / is not null 判断时，会导致索引失效而转向全表扫描。
结论：索引字段使用is null / is not null，会导致索引失效。

更多及时干货，请关注微信公众号：JAVA万维猿圈