本文已参与「新人创作礼」活动，一起开启掘金创作之路。
索引失效分析
1.全值匹配我最爱
mysql> explain select * from t_user where name='kojon' and age=1 and sex='1';
2.最佳左前缀法组合索引
组合索引
带头索引不能死，中间索引不能断
如果索引了多个列，要遵守最佳左前缀法则。指的是查询从索引的最左前列开始 并且不跳过索引中的列。
错误的示例：
带头索引死：
mysql> -- 带头索引死
mysql> explain select * from t_user where age=23;
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t_user | ALL | NULL | NULL | NULL | NULL | 1 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
中间索引断（带头索引生效，其他索引失效）：
mysql> -- 中间索引断
mysql> explain select * from t_user where name='aa' and sex='1';
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t_user | ALL | NULL | NULL | NULL | NULL | 1 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
比较
mysql> explain select * from t_user where name='aa' and sex='1' and age=23;
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t_user | ALL | NULL | NULL | NULL | NULL | 1 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
mysql> explain select * from t_user where name='aa' and sex=1 and age=23;
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t_user | ALL | NULL | NULL | NULL | NULL | 1 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
3.不要在索引上做计算
mysql> #不要进行这些操作:计算、函数、自动/手动类型转换，不然会导致 索引失效而转向全表扫描
mysql> explain select * from t_user where login_name='xh';
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
1 row in set (0.00 sec)
mysql> -- 不要在索引上做计算
mysql> explain select * from t_user where left(login_name,1)='xh';
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t_user | ALL | NULL | NULL | NULL | NULL | 1 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
4.范围条件右边的列失效
mysql> -- 范围条件右边的列失效
mysql> explain select * from t_user where name='abc' and age>20 and sex='1';
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t_user | ALL | NULL | NULL | NULL | NULL | 1 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
5.尽量使用覆盖索引
mysql> -- 尽量使用覆盖索引(只查询索引的列)，也就是索引列和查询列一 致，减少select *
mysql> explain select * from t_user ;
+----+-------------+--------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | t_user | ALL | NULL | NULL | NULL | NULL | 1 | NULL |
+----+-------------+--------+------+---------------+------+---------+------+------+-------+
mysql> -- 未使用覆盖索引
mysql> explain select name,login_name from t_user ;
+----+-------------+--------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | t_user | ALL | NULL | NULL | NULL | NULL | 1 | NULL |
+----+-------------+--------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)
mysql> -- 使用覆盖索引
mysql> explain select name,age,sex from t_user ;
+----+-------------+--------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | t_user | ALL | NULL | NULL | NULL | NULL | 1 | NULL |
+----+-------------+--------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)
mysql> -- 使用索引
mysql> explain select login_name from t_user ;
+----+-------------+--------+-------+---------------+----------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+---------------+----------------+---------+------+------+-------------+
| 1 | SIMPLE | t_user | index | NULL | idx_login_name | 403 | NULL | 1 | Using index |
+----+-------------+--------+-------+---------------+----------------+---------+------+------+-------------+
6.索引字段上不要使用不等
mysql> -- 索引字段上使用等于
mysql> explain select * from t_user where login_name='xh';
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
1 row in set (0.00 sec)
mysql> -- 索引字段上使用不等于
mysql> explain select * from t_user where login_name!='xh';
+----+-------------+--------+------+----------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+----------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t_user | ALL | idx_login_name | NULL | NULL | NULL | 1 | Using where |
+----+-------------+--------+------+----------------+------+---------+------+------+-------------+
7.主键索引字段上不可以判断null
mysql> # 主键字段上不可以使用 null
mysql> explain select * from t_user where name is null;
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t_user | ALL | NULL | NULL | NULL | NULL | 1 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> # 索引字段上使用 is null 判断时，可使用索引
mysql> explain select * from t_user where login_name is null;
+----+-------------+--------+------+----------------+----------------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+----------------+----------------+---------+-------+------+-----------------------+
| 1 | SIMPLE | t_user | ref | idx_login_name | idx_login_name | 403 | const | 1 | Using index condition |
+----+-------------+--------+------+----------------+----------------+---------+-------+------+-----------------------+
mysql> -- 主键非空 不使用索引
mysql> explain select * from t_user where id is not null;
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t_user | ALL | PRIMARY | NULL | NULL | NULL | 1 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
8.索引字段使用like不以通配符开头
mysql> -- 不以%开头
mysql> explain select * from t_user where login_name like 'x%';
+----+-------------+--------+-------+----------------+----------------+---------+------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+----------------+----------------+---------+------+------+-----------------------+
| 1 | SIMPLE | t_user | range | idx_login_name | idx_login_name | 403 | NULL | 1 | Using index condition |
+----+-------------+--------+-------+----------------+----------------+---------+------+------+-----------------------+
1 row in set (0.00 sec)
mysql> -- 索引字段使用like以通配符开头(‘%字符串’)时，会导致索引失效而转向全表扫描
mysql> explain select * from t_user where login_name like '%x';
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t_user | ALL | NULL | NULL | NULL | NULL | 1 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
由结果可知，like以通配符结束相当于范围查找，索引不会失效。与范围条件（bettween、<、>、in等）不同的是：不会导致右边的索引失效。
9.索引字段字符串要加单引号
隐式转换问题
索引字段是字符串，但查询时不加单引号，会导致索引失效而转向全表扫描
mysql> # 索引字段字符串要加单引号
mysql> explain select * from t_user where name=123;
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t_user | ALL | NULL | NULL | NULL | NULL | 1 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
10.索引字段不要使用or
索引字段使用 or 时，会导致索引失效而转向全表扫描
mysql> # 索引字段不要使用or
mysql> explain select * from t_user where name='asd' or age=18;
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t_user | ALL | NULL | NULL | NULL | NULL | 1 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
总结
[优化总结口诀] 全值匹配我最爱，最左前綴要遵守; 带头大哥不能死，中间兄弟不能断; 索引列上少计算，范围之后全失效; LIKE百分写最右，覆盖索引不写星; 不等空值还有or，索引失效要少用.
