索引在哪些场景下会失效，通过测试来验证一下。
新建一张表，初始化了 30 万条数据进去。
1
2
3
4
5
6
7
8
9
| CREATE TABLE employees (
emp_no INT NOT NULL,
birth_date DATE NOT NULL,
first_name VARCHAR(14) NOT NULL,
last_name VARCHAR(16) NOT NULL,
gender ENUM ('M','F') NOT NULL,
hire_date DATE NOT NULL,
PRIMARY KEY (emp_no)
);
|
首先我们给这张表添加一个索引，就使用 first_name 字段好了
1
| ALTER TABLE employees ADD INDEX employees_n1 ( first_name );
|
当我们使用 first_name 字段去查询时，索引是生效的
1
2
3
4
5
6
7
8
|
mysql> EXPLAIN SELECT * FROM employees WHERE first_name = 'Mary';
+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+
| 1 | SIMPLE | employees | NULL | ref | employees_n1 | employees_n1 | 16 | const | 224 | 100.00 | NULL |
+
1 row in set, 1 warning (0.00 sec)
|
那在什么场景下才会失效呢？ 以下是可能导致索引失效的原因：
隐式或显式类型转换
first_name 是一个 Varchar 类型的字段，我们使用的值 123 是整数类型，Mysql 会尝试将 first_name 字段中的值转成数字类型，从而导致该索引字段失效
1
2
3
4
5
6
| mysql> EXPLAIN SELECT * FROM employees WHERE first_name = 123;
+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+
| 1 | SIMPLE | employees | NULL | ALL | employees_n1 | NULL | NULL | NULL | 299556 | 10.00 | Using where |
+
|
对索引字段进行运算
下面例子中对 first_name 字段进行了运算导致索引失效
1
2
3
4
5
6
| mysql> EXPLAIN SELECT * FROM employees WHERE LEFT(first_name,1) = 'Mary';
+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+
| 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 299556 | 100.00 | Using where |
+
|
LIKE 左模糊匹配
下面例子中分别对索引字段 first_name 使用 Like 进行匹配，可以发现当出现左模糊匹配时，索引失效。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
| mysql> EXPLAIN SELECT * FROM employees WHERE first_name LIKE '%Mary%';
+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+
| 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 299556 | 11.11 | Using where |
+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT * FROM employees WHERE first_name LIKE '%Mary';
+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+
| 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 299556 | 11.11 | Using where |
+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT * FROM employees WHERE first_name LIKE 'Mary%';
+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+
| 1 | SIMPLE | employees | NULL | range | employees_n1 | employees_n1 | 16 | NULL | 224 | 100.00 | Using index condition |
+
|
OR 连接非索引字段
使用索引字段查询的同时使用 OR 连接一个非索引字段时，会导致该索引字段失效。
但如果改成使用 AND 则不影响
1
2
3
4
5
6
7
8
9
10
11
12
13
14
| mysql> EXPLAIN SELECT * FROM employees WHERE first_name = 'Mary' OR last_name = 'Sluis';
+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+
| 1 | SIMPLE | employees | NULL | ALL | employees_n1 | NULL | NULL | NULL | 299556 | 10.07 | Using where |
+
mysql> EXPLAIN SELECT * FROM employees WHERE first_name = 'Mary' AND last_name = 'Sluis';
+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+
| 1 | SIMPLE | employees | NULL | ref | employees_n1 | employees_n1 | 16 | const | 224 | 10.00 | Using where |
+
|
最左匹配原则
在使用组合索引时，条件中必须出现组合索引中的第一个字段，该索引才会生效
下面我们删除原来的索引，新建一个组合索引
1
2
3
4
5
6
7
8
| mysql> ALTER TABLE employees DROP INDEX employees_n1;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE employees ADD INDEX employees_n1 ( first_name, last_name, gender );
Query OK, 0 rows affected (1.35 sec)
Records: 0 Duplicates: 0 Warnings: 0
|
下面的测试语句中：
第一条语句的条件包含了三个字段，分别都是组合索引中的字段，因此索引是生效的。
1
2
3
4
5
6
7
| mysql> EXPLAIN SELECT * FROM employees WHERE first_name = 'Mary' AND last_name = 'Sluis' AND gender = 'M';
+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+
| 1 | SIMPLE | employees | NULL | ref | employees_n1 | employees_n1 | 35 | const,const,const | 1 | 100.00 | NULL |
+
1 row in set, 1 warning (0.00 sec)
|
第二条语句中，把组合索引中的第一个字段去除了，此时索引没有生效。
1
2
3
4
5
6
7
8
|
mysql> EXPLAIN SELECT * FROM employees WHERE last_name = 'Sluis' AND gender = 'M';
+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+
| 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 299556 | 5.00 | Using where |
+
|
第三条语句中，把条件的位置更换了，没有按组合索引中的字段顺序来查询，此时索引也是生效的。
1
2
3
4
5
6
7
8
|
mysql> EXPLAIN SELECT * FROM employees WHERE last_name = 'Sluis' AND first_name = 'Mary' AND gender = 'M';
+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+
| 1 | SIMPLE | employees | NULL | ref | employees_n1 | employees_n1 | 35 | const,const,const | 1 | 100.00 | NULL |
+
|
第四条语句中，把组合索引中间的字段去除，只加了 first_name 与 gender 两个条件，虽然看起来也走了索引，但实际上 gender 条件索引是失效的，只有 first_name 字段进行了索引
1
2
3
4
5
6
7
| mysql> EXPLAIN SELECT * FROM employees WHERE first_name = 'Mary' AND gender = 'M';
+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+
| 1 | SIMPLE | employees | NULL | ref | employees_n1 | employees_n1 | 16 | const | 224 | 50.00 | Using index condition |
+
|
因此组合索引优化可以考虑：
- 最好包含所有索引字段
- 条件中包含索引中的第一个字段
- 尽量不要跳过中间的字段
比较运算后面的索引失效
为了方便进行比较运算，重新建一下索引
1
2
3
4
5
6
7
| mysql> ALTER TABLE employees DROP INDEX employees_n1;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE employees ADD INDEX employees_n1 ( first_name, birth_date, last_name );
Query OK, 0 rows affected (1.26 sec)
Records: 0 Duplicates: 0 Warnings: 0
|
下面例子中：
第一条语句是三个条件都走了索引
第二个语句把中间字段使用了大小比较运算符，导致后面的字段索引失效。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
| mysql> EXPLAIN SELECT * FROM employees WHERE first_name = 'Mary' AND birth_date = '1958-02-26' AND last_name = 'Sluis';
+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+
| 1 | SIMPLE | employees | NULL | ref | employees_n1 | employees_n1 | 37 | const,const,const | 1 | 100.00 | NULL |
+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT * FROM employees WHERE first_name = 'Mary' AND birth_date > '1958-02-26' AND last_name = 'Sluis';
+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+
| 1 | SIMPLE | employees | NULL | range | employees_n1 | employees_n1 | 19 | NULL | 111 | 10.00 | Using index condition |
+
|