abs130：mysql中索引失效的几种情况

环境

数据库版本: 5.7.28
测试库：用mysql中自带的sakila库
测试表:
actor

create table actor( actor_id smallint unsigned auto_increment primary key, first_name varchar(45) not null, last_name varchar(45) not null, last_update timestamp default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP) charset = utf8;create index idx_actor_last_name on actor (last_name);

基于原来的rental表复制了一张rental_test表

create table rental_test( rental_id int auto_increment primary key, rental_date datetime not null, inventory_id mediumint unsigned not null, customer_id smallint unsigned not null, return_date datetime null, staff_id tinyint unsigned not null, last_update timestamp default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP) charset = utf8;create index idx_fk_customer_id on rental_test (customer_id);create index idx_fk_inventory_id on rental_test (inventory_id);

索引失效的几种情况

1. 索引列使用or连接，且or连接的字段中部分没有索引

explain select * from rental_test where customer_id=130 or inventory_id=4272;
1.1 只给customer_id添加索引，此时执行计划为

1.2 customer_id，inventory_id都添加索引

2. 某些数据类型隐式转换

2.1 字段类型为int, 用字符串查询会使用索引
explain select * from rental_test where customer_id='130';

2.2 字段类型为字符串, 用字数字查询不会使用索引
explain select * from actor where last_name=23;

3. 有 !=、>、<、算数运算、函数

3.1 explain select * from rental_test where customer_id!=130;

3.2 explain select * from rental_test where customer_id>130;

3.3 explain select * from rental_test where customer_id+1=130;

3.4 explain select * from rental_test where abs(customer_id)=130;

4. 有null、not null、not in

4.1 explain select * from rental_test where customer_id not in(130);

4.2 explain select * from rental_test where customer_id is null;

4.3 explain select * from rental_test where customer_id is not null;

5. 使用like 模糊匹配，%在字符串的开头

explain select * from actor where last_name like '%hehe';