mysql 索引优化案例（索引失效）

注：内容来转自尚硅谷课程

CREATE TABLE staffs(
id INT PRIMARY KEY auto_increment,
name VARCHAR(24) NOT NULL DEFAULT '' COMMENT '姓名',
age INT NOT NULL DEFAULT 0 COMMENT '年龄',
pos VARCHAR(20) NOT NULL DEFAULT '' COMMENT '职位',
add_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间'
)COMMENT '员工记录表';


CREATE INDEX idx_staffs_nameAgePos ON staffs(name,age,pos);

1、全值匹配我最爱

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_staffs_nameAgePos | idx_staffs_nameAgePos | 74      | const |    1 | Using index condition |
+----+-------------+--------+------+-----------------------+-----------------------+---------+-------+------+-----------------------+

mysql> EXPLAIN SELECT * FROM staffs WHERE name = 'July' AND age=25;
+----+-------------+--------+------+-----------------------+-----------------------+---------+-------------+------+-----------------------+
| id | select_type | table  | type | possible_keys         | key                   | key_len | ref         | rows | Extra                 |
+----+-------------+--------+------+-----------------------+-----------------------+---------+-------------+------+-----------------------+
|  1 | SIMPLE      | staffs | ref  | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 78      | const,const |    1 | Using index condition |
+----+-------------+--------+------+-----------------------+-----------------------+---------+-------------+------+-----------------------+

mysql> EXPLAIN SELECT * FROM staffs WHERE name = 'July' AND age=25 AND pos='dev';
+----+-------------+--------+------+-----------------------+-----------------------+---------+-------------------+------+-----------------------+
| id | select_type | table  | type | possible_keys         | key                   | key_len | ref               | rows | Extra                 |
+----+-------------+--------+------+-----------------------+-----------------------+---------+-------------------+------+-----------------------+
|  1 | SIMPLE      | staffs | ref  | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 140     | const,const,const |    1 | Using index condition |
+----+-------------+--------+------+-----------------------+-----------------------+---------+-------------------+------+-----------------------+

mysql> EXPLAIN SELECT * FROM staffs WHERE age=25 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 |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+

mysql> EXPLAIN SELECT * FROM staffs WHERE 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 |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+

mysql> EXPLAIN SELECT * FROM staffs WHERE name = 'July' AND pos='dev';（此时只用到了name索引，中间断了）
+----+-------------+--------+------+-----------------------+-----------------------+---------+-------+------+-----------------------+
| id | select_type | table  | type | possible_keys         | key                   | key_len | ref   | rows | Extra                 |
+----+-------------+--------+------+-----------------------+-----------------------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | staffs | ref  | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 74      | const |    1 | Using index condition |
+----+-------------+--------+------+-----------------------+-----------------------+---------+-------+------+-----------------------+

2、最佳左前缀法则：

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

3、不要在索引上做任何操作

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_staffs_nameAgePos | idx_staffs_nameAgePos | 74      | const |    1 | Using index condition |
+----+-------------+--------+------+-----------------------+-----------------------+---------+-------+------+-----------------------+

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 |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+

4、范围之后全失效（name用于索引，age用于范围，pos失效）

mysql> EXPLAIN SELECT * FROM staffs WHERE name = 'July' AND age>25 AND pos='dev';
+----+-------------+--------+-------+-----------------------+-----------------------+---------+------+------+-----------------------+
| id | select_type | table  | type  | possible_keys         | key                   | key_len | ref  | rows | Extra                 |
+----+-------------+--------+-------+-----------------------+-----------------------+---------+------+------+-----------------------+
|  1 | SIMPLE      | staffs | range | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 78      | NULL |    1 | Using index condition |
+----+-------------+--------+-------+-----------------------+-----------------------+---------+------+------+-----------------------+

5、尽量使用覆盖索引，减少select * 

mysql> EXPLAIN SELECT * FROM staffs WHERE name = 'July' AND age=25 AND pos='dev';
+----+-------------+--------+------+-----------------------+-----------------------+---------+-------------------+------+-----------------------+
| id | select_type | table  | type | possible_keys         | key                   | key_len | ref               | rows | Extra                 |
+----+-------------+--------+------+-----------------------+-----------------------+---------+-------------------+------+-----------------------+
|  1 | SIMPLE      | staffs | ref  | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 140     | const,const,const |    1 | Using index condition |
+----+-------------+--------+------+-----------------------+-----------------------+---------+-------------------+------+-----------------------+

mysql> EXPLAIN SELECT name,age,pos FROM staffs WHERE name = 'July' AND age=25 AND pos='dev';
+----+-------------+--------+------+-----------------------+-----------------------+---------+-------------------+------+--------------------------+
| id | select_type | table  | type | possible_keys         | key                   | key_len | ref               | rows | Extra                    |
+----+-------------+--------+------+-----------------------+-----------------------+---------+-------------------+------+--------------------------+
|  1 | SIMPLE      | staffs | ref  | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 140     | const,const,const |    1 | Using where; Using index |
+----+-------------+--------+------+-----------------------+-----------------------+---------+-------------------+------+--------------------------+

mysql> EXPLAIN SELECT name,age,pos FROM staffs WHERE name = 'July' AND age>25 AND pos='dev';（注意对比第四条的执行计划）
+----+-------------+--------+------+-----------------------+-----------------------+---------+-------+------+--------------------------+
| id | select_type | table  | type | possible_keys         | key                   | key_len | ref   | rows | Extra                    |
+----+-------------+--------+------+-----------------------+-----------------------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | staffs | ref  | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 74      | const |    1 | Using where; Using index |
+----+-------------+--------+------+-----------------------+-----------------------+---------+-------+------+--------------------------+

6、mysql在使用不等于（！= 或者<>）的时候无法使用索引，将导致全表扫描。

mysql> EXPLAIN SELECT name,age,pos FROM staffs WHERE name = 'July';
+----+-------------+--------+------+-----------------------+-----------------------+---------+-------+------+--------------------------+
| id | select_type | table  | type | possible_keys         | key                   | key_len | ref   | rows | Extra                    |
+----+-------------+--------+------+-----------------------+-----------------------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | staffs | ref  | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 74      | const |    1 | Using where; Using index |
+----+-------------+--------+------+-----------------------+-----------------------+---------+-------+------+--------------------------+

mysql> EXPLAIN SELECT name,age,pos FROM staffs WHERE name != 'July';
+----+-------------+--------+-------+-----------------------+-----------------------+---------+------+------+--------------------------+
| id | select_type | table  | type  | possible_keys         | key                   | key_len | ref  | rows | Extra                    |
+----+-------------+--------+-------+-----------------------+-----------------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | staffs | index | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 140     | NULL |    3 | Using where; Using index |
+----+-------------+--------+-------+-----------------------+-----------------------+---------+------+------+--------------------------+

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_staffs_nameAgePos | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+--------+------+-----------------------+------+---------+------+------+-------------+

7、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_staffs_nameAgePos | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+--------+------+-----------------------+------+---------+------+------+-------------+

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 |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+

8、like以通配符开头（%abc），也会导致全表扫描

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 |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+

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 |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+

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_staffs_nameAgePos | idx_staffs_nameAgePos | 74      | NULL |    1 | Using index condition |
+----+-------------+--------+-------+-----------------------+-----------------------+---------+------+------+-----------------------+

#解决like %字符串% 索引不被使用的方法？使用覆盖索引（个人理解：查询的字段要被索引字段覆盖）

mysql> EXPLAIN SELECT name,age,pos FROM staffs WHERE name like '%July%';
+----+-------------+--------+-------+---------------+-----------------------+---------+------+------+--------------------------+
| id | select_type | table  | type  | possible_keys | key                   | key_len | ref  | rows | Extra                    |
+----+-------------+--------+-------+---------------+-----------------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | staffs | index | NULL          | idx_staffs_nameAgePos | 140     | NULL |    3 | Using where; Using index |
+----+-------------+--------+-------+---------------+-----------------------+---------+------+------+--------------------------+

mysql> EXPLAIN SELECT age,pos FROM staffs WHERE name like '%July%';（注意此时跳过了符合索引的name，但依然用到了）
+----+-------------+--------+-------+---------------+-----------------------+---------+------+------+--------------------------+
| id | select_type | table  | type  | possible_keys | key                   | key_len | ref  | rows | Extra                    |
+----+-------------+--------+-------+---------------+-----------------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | staffs | index | NULL          | idx_staffs_nameAgePos | 140     | NULL |    3 | Using where; Using index |
+----+-------------+--------+-------+---------------+-----------------------+---------+------+------+--------------------------+

mysql> EXPLAIN SELECT id FROM staffs WHERE name like '%July%';（因为id自带了主键索引）
+----+-------------+--------+-------+---------------+-----------------------+---------+------+------+--------------------------+
| id | select_type | table  | type  | possible_keys | key                   | key_len | ref  | rows | Extra                    |
+----+-------------+--------+-------+---------------+-----------------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | staffs | index | NULL          | idx_staffs_nameAgePos | 140     | NULL |    3 | Using where; Using index |
+----+-------------+--------+-------+---------------+-----------------------+---------+------+------+--------------------------+

mysql> EXPLAIN SELECT id,pos FROM staffs WHERE name like '%July%';
+----+-------------+--------+-------+---------------+-----------------------+---------+------+------+--------------------------+
| id | select_type | table  | type  | possible_keys | key                   | key_len | ref  | rows | Extra                    |
+----+-------------+--------+-------+---------------+-----------------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | staffs | index | NULL          | idx_staffs_nameAgePos | 140     | NULL |    3 | Using where; Using index |
+----+-------------+--------+-------+---------------+-----------------------+---------+------+------+--------------------------+

mysql> EXPLAIN SELECT name,age,pos,add_time 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 |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+

9、字符串不加单引号索引失效（相当于在索引列上做了类型转换，其他类型转换情况相同）

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_staffs_nameAgePos | idx_staffs_nameAgePos | 74      | const |    1 | Using index condition |
+----+-------------+--------+------+-----------------------+-----------------------+---------+-------+------+-----------------------+

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_staffs_nameAgePos | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+--------+------+-----------------------+------+---------+------+------+-------------+

10、少用or，也会导致索引失效

mysql> EXPLAIN SELECT * FROM staffs WHERE name = '张三' or name='July';
+----+-------------+--------+------+-----------------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys         | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+-----------------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | staffs | ALL  | idx_staffs_nameAgePos | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+--------+------+-----------------------+------+---------+------+------+-------------+

mysql> EXPLAIN SELECT * FROM staffs WHERE name in('张三','July');
+----+-------------+--------+------+-----------------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys         | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+-----------------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | staffs | ALL  | idx_staffs_nameAgePos | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+--------+------+-----------------------+------+---------+------+------+-------------+