MySQL索引失效之隐式转换

一、常见索引失效场景

root@test 10:50 > show create table t_num\G
*************************** 1. row ***************************
       Table: t_num
Create Table: CREATE TABLE `t_num` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c1` int(11) NOT NULL,
  `c2` varchar(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `ix_c1` (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4
​
root@test 10:51 > select * from t_num;
+----+----+----+
| id | c1 | c2 |
+----+----+----+
|  1 | -2 | -2 |
|  2 | -1 | -1 |
|  3 |  0 |  0 |
|  4 |  1 |  1 |
|  5 |  2 |  2 |
+----+----+----+
​
# 在c1字段上加上索引
root@test 10:52 > alter table t_num add index ix_c1(c1);
​
# 标准使用情况下，索引有效
root@test 10:55 > explain select * from t_num where c1 = -1;
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t_num | NULL       | ref  | ix_c1         | ix_c1 | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+