MySQL索引失效

1、举例 5 种常见的索引失效
当前使用的 MySQL 版本是 5.7.29，先创建 1 张表

DROP TABLE IF EXISTS `t1`;

CREATE TABLE `t1`(
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`age` int(10) NOT NULL,
`phone` varchar(30) NOT NULL,
PRIMARY KEY(`id`),
KEY `idx_age`(`age`),
KEY `idx_phone`(`phone`)
)ENGINE=InnoDB;

INSERT INTO `t1`(`age`, `phone`) VALUES(19, '13911112222');

（1）被索引字段发生隐式类型转换
查询语句把字符串写成数字，字段 phone从 字符串被隐式转换为数字，所以在explain中 key 为 NULL ，表示没有使用索引。

type 是 ALL 也表示采用了全表扫描的方式、没有使用索引；
除 ALL 外，其他类型都可以用到索引，但效率有所不同，效率从低到高依次为 ALL < index < range < index_merge < ref < eq_ref < const/system。一般希望至少可以使用到 range 级别以上的类型，如果只用到了 ALL 或 index ，可以从 SQL 语句和索引设计的角度上进行改进。

mysql> explain select * from t1 where phone=13911112222;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | idx_phone     | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

把查询语句改为字符串，即可使用索引，可以看到 key 是 idx_phone ：

mysql> explain select * from t1 where phone='13911112222';
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | ref  | idx_phone     | idx_phone | 32      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+

（2）被索引字段发生表达式计算

mysql> explain select * from t1 where age-1=18;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

把表达式换到右边即可：

mysql> explain select * from t1 where age=18+1;
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | ref  | idx_age       | idx_age | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+

（3）被索引字段使用函数

mysql> explain select * from t1 where left(phone,3)='139';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

修改为右模糊匹配：
type是range 表示采用了索引范围扫描；

mysql> explain select * from t1 where phone like '139%';
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | t1    | NULL       | range | idx_phone     | idx_phone | 32      | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+

（4）被索引字段发生 左模糊匹配 或 左右模糊匹配

mysql> explain select * from t1 where phone like '%2222';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

mysql> explain select * from t1 where phone like '%1111%';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

（5）被索引字段 不是联合索引的最左字段
再创建一张有联合索引的表：

DROP TABLE IF EXISTS `t2`;

CREATE TABLE `t2`(
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`age` int(10) NOT NULL,
`phone` varchar(30) NOT NULL,
`name` varchar(30) NOT NULL,
PRIMARY KEY(`id`),
KEY `idx_age_phone`(`age`, `phone`)
)ENGINE=InnoDB;

INSERT INTO `t2`(`age`, `phone`, `name`) VALUES(19, '13911112222', 'Tom');

不是最左字段，没有使用索引：

mysql> explain select * from t2 where phone='13911112222';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

最左字段可以使用索引：

mysql> explain select * from t2 where age=19;
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key           | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t2    | NULL       | ref  | idx_age_phone | idx_age_phone | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-------+

2、针对上面的第（4）点情况，如果索引覆盖，无需回表，还是可以使用索引的，但此时的时间复杂度也不是O(log n)了，而是O(n)。type是index ，只比 ALL 略好，只不过 index 是对索引表进行全扫描。

mysql> explain select id,phone from t1 where phone like '%2222';
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | t1    | NULL       | index | NULL          | idx_phone | 32      | NULL |    1 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+