mysql之6中索引失效情况

1. 对索引使用左或者左右模糊匹配

mysql> explain select * from user where name='王五';
+----+-------------+-------+------+---------------+------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key        | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+------------+---------+-------+------+-------------+
|  1 | SIMPLE      | user  | ref  | name_index    | name_index | 768     | const |    1 | Using where |
+----+-------------+-------+------+---------------+------------+---------+-------+------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from user where name like '%王五';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

如果使用 name like ‘%林’ 方式来查询，因为查询的结果可能是「陈林、张林、周林」等之类的，所以不知道从哪个索引值开始比较，于是就只能通过全表扫描的方式来查询
2. 对索引使用函数

mysql> explain select * from user where length(name)=6;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

因为索引保存的是索引字段的原始值，而不是经过函数计算后的值，自然就没办法走索引了。

3. 对索引进行表达式运算

mysql> explain select * from user where id + 1 = 10;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.01 sec)

因为索引存储的是索引值,检索匹配的时候是根据索引匹配,而不是表达式匹配
4. 对隐式类型转换
mysql中,会自动把查询语句中的字符串转换为数字 (如果需要转换的话),即参数类型与条件类型不匹配.
验证MySQL自动将字符串转换为数字

mysql> select '10'>9;
+--------+
| '10'>9 |
+--------+
|      1 |
+--------+
1 row in set (0.01 sec)

当索引类型是字符串,但是where 索引 条件 数字,会进行全表扫描

mysql> explain select * from user where name = 10;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | user  | ALL  | name_index    | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.01 sec)

因为这条语句相当于使用了函数

不失效的情况

mysql> explain select * from user where id = '1';
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | user  | const | PRIMARY       | PRIMARY | 4       | const |    1 |       |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.01 sec)
	

5. 联合索引不遵循最左匹配

因为先根据前面的排序,前面的相同,在根据后面的索引排序

索引截断

6. or中有不是索引列

mysql> explain select * from user where name='1' or age=1;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | user  | ALL  | name_index    | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.01 sec)

mysql> explain select * from user where age=1 or name='1';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | user  | ALL  | name_index    | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

因为age不是索引类
or需要两个有一个满足就行,所以age条件需要全表扫描
7. MySQL 使用 like “%x“，索引一定会失效吗？
当查询的列都能在二级索引中找到(索引覆盖),而又左模糊查询,会对二级索引全表扫描,因为二级索引只记录 索引列 -主键,而聚簇索引记录了主键值、事务 id、用于事务和 MVCC 的回滚指针以及所有的剩余列。所以扫描二级索引.