全值匹配我最爱，最左前缀要遵守;
带头大哥不能死，中间兄弟不能断;
索引列上少计算，范围之后全失效;
LIKE百分写最右，覆盖素引不写星;
不等空值还有or，索引失效要少用;
查看索引结构
mysql> show index from staffs;
+--------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| staffs | 0 | PRIMARY | 1 | id | A | 3 | NULL | NULL | | BTREE | | |
| staffs | 1 | idx_name_age_pos | 1 | NAME | A | 3 | NULL | NULL | | BTREE | | |
| staffs | 1 | idx_name_age_pos | 2 | age | A | 3 | NULL | NULL | | BTREE | | |
| staffs | 1 | idx_name_age_pos | 3 | pos | A | 3 | NULL | NULL | | BTREE | | |
+--------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
全值匹配我最爱
查询的字段或查询条件字段与建立的索引字段一一对应
mysql> explain select * from staffs where NAME = 'July' and age = 23 and pos = 'dev';
+----+-------------+--------+------+------------------+------------------+---------+-------------------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+------------------+------------------+---------+-------------------+------+-----------------------+
| 1 | SIMPLE | staffs | ref | idx_name_age_pos | idx_name_age_pos | 140 | const,const,const | 1 | Using index condition |
+----+-------------+--------+------+------------------+------------------+---------+-------------------+------+-----------------------+
1 row in set (0.00 sec)
# type为ref，且用到了索引
最佳左前缀法则
含义：如果为多值索引，要遵守最左前缀法则。指的是查询从索引的最左列开始并且不跳过索引中的列。
解释：查询的字段或者条件字段可以不与索引字段全部一致，但开头必须一致，且中间不能隔断。
带头大哥不能死
中间兄弟不能断
# 只有前两个
mysql> explain select * from staffs where NAME = 'July' and age = 23;
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+-----------------------+
| 1 | SIMPLE | staffs | ref | idx_name_age_pos | idx_name_age_pos | 78 | const,const | 1 | Using index condition |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+-----------------------+
1 row in set (0.00 sec)
# 没有‘大哥’
mysql> explain select * from staffs where age = 23 and pos = 'dev';
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | staffs | ALL | NULL | NULL | NULL | NULL | 3 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
# ‘兄弟’间断
mysql> explain select * from staffs where NAME = 'July' and pos = 'dev';
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-----------------------+
| 1 | SIMPLE | staffs | ref | idx_name_age_pos | idx_name_age_pos | 74 | const | 1 | Using index condition |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)
分析：没有‘大哥’就像楼梯没有第一层一样。‘兄弟’间断就像楼梯中间少了一层。
在索引列上做任何操作(计算、 函数、(自动or手动)类型转换)，会导致索引失效而转向全表扫描
# 没有在索引列上做操作
mysql> explain select * from staffs where name='July';
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-----------------------+
| 1 | SIMPLE | staffs | ref | idx_name_age_pos | idx_name_age_pos | 74 | const | 1 | Using index condition |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-----------------------+
1 row in set (0.01 sec)
# 在索引列上做了操作
mysql> explain select * from staffs where left(name,4)='July';
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | staffs | ALL | NULL | NULL | NULL | NULL | 3 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
存储引擎不能使用索引中范围条件右边的列
分析以下代码可以知道，当索引出现范围条件后，其后边的索引列将不能被使用(出现范围条件本身的这个字段还可以被使用)
mysql> explain select * from staffs where name='July' and age=22 and pos='dev';
+----+-------------+--------+------+------------------+------------------+---------+-------------------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+------------------+------------------+---------+-------------------+------+-----------------------+
| 1 | SIMPLE | staffs | ref | idx_name_age_pos | idx_name_age_pos | 140 | const,const,const | 1 | Using index condition |
+----+-------------+--------+------+------------------+------------------+---------+-------------------+------+-----------------------+
1 row in set (0.00 sec)
mysql> explain select * from staffs where name='July' and age>15 and pos='dev';
+----+-------------+--------+-------+------------------+------------------+---------+------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+------------------+------------------+---------+------+------+-----------------------+
| 1 | SIMPLE | staffs | range | idx_name_age_pos | idx_name_age_pos | 78 | NULL | 1 | Using index condition |
+----+-------------+--------+-------+------------------+------------------+---------+------+------+-----------------------+
1 row in set (0.00 sec)
mysql> explain select * from staffs where name='July' and age=15;
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+-----------------------+
| 1 | SIMPLE | staffs | ref | idx_name_age_pos | idx_name_age_pos | 78 | const,const | 1 | Using index condition |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+-----------------------+
1 row in set (0.00 sec)
尽量使用覆盖索引（只访问索引的查询（查询列和索引列保持一致）），减少select*
mysql> explain select * from staffs where name='July' and age=15;
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+-----------------------+
| 1 | SIMPLE | staffs | ref | idx_name_age_pos | idx_name_age_pos | 78 | const,const | 1 | Using index condition |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+-----------------------+
1 row in set (0.00 sec)
mysql> explain select name,age from staffs where name='July' and age=15;
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+--------------------------+
| 1 | SIMPLE | staffs | ref | idx_name_age_pos | idx_name_age_pos | 78 | const,const | 1 | Using where; Using index |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+--------------------------+
1 row in set (0.00 sec)
MySQL在使用不等于(<>或!=)时无法使用索引，会导致全表扫描
mysql> explain select * from staffs where name!='July';
+----+-------------+--------+------+------------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+------------------+------+---------+------+------+-------------+
| 1 | SIMPLE | staffs | ALL | idx_name_age_pos | NULL | NULL | NULL | 3 | Using where |
+----+-------------+--------+------+------------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain select * from staffs where name='July';
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-----------------------+
| 1 | SIMPLE | staffs | ref | idx_name_age_pos | idx_name_age_pos | 74 | const | 1 | Using index condition |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)
is null和is not null也无法使用索引
mysql> explain select * from staffs where name is not null;
+----+-------------+--------+------+------------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+------------------+------+---------+------+------+-------------+
| 1 | SIMPLE | staffs | ALL | idx_name_age_pos | NULL | NULL | NULL | 3 | Using where |
+----+-------------+--------+------+------------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain select * from staffs where name is null;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
1 row in set (0.00 sec)
like以通配符开头(%abc)，MySQL将无法使用索引，导致全表扫描
mysql> explain select * from staffs where name like'%July%';
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | staffs | ALL | NULL | NULL | NULL | NULL | 3 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain select * from staffs where name like'%July';
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | staffs | ALL | NULL | NULL | NULL | NULL | 3 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain select * from staffs where name like'July%';
+----+-------------+--------+-------+------------------+------------------+---------+------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+------------------+------------------+---------+------+------+-----------------------+
| 1 | SIMPLE | staffs | range | idx_name_age_pos | idx_name_age_pos | 74 | NULL | 1 | Using index condition |
+----+-------------+--------+-------+------------------+------------------+---------+------+------+-----------------------+
1 row in set (0.00 sec)
解决%不能在左边
使用覆盖索引
字符串不加单引号引起索引失效
mysql> explain select * from staffs where name = '2000';
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-----------------------+
| 1 | SIMPLE | staffs | ref | idx_name_age_pos | idx_name_age_pos | 74 | const | 1 | Using index condition |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)
mysql> explain select * from staffs where name = 2000;
+----+-------------+--------+------+------------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+------------------+------+---------+------+------+-------------+
| 1 | SIMPLE | staffs | ALL | idx_name_age_pos | NULL | NULL | NULL | 3 | Using where |
+----+-------------+--------+------+------------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
注意：
在sql中varchar一定要加单引号
应该避免隐式或显式的发生类型转换
or也会引起索引失效
mysql> explain select * from staffs where name = 'July' or age =20;
+----+-------------+--------+------+------------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+------------------+------+---------+------+------+-------------+
| 1 | SIMPLE | staffs | ALL | idx_name_age_pos | NULL | NULL | NULL | 3 | Using where |
+----+-------------+--------+------+------------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
[优化总结口诀]
全值匹配我最爱，最左前缀要遵守;
带头大哥不能死，中间兄弟不能断;
索引列上少计算，范围之后全失效;
LIKE百分写最右，覆盖素引不写星;
不等空值还有or，索引失效要少用;