MySQL索引可能失效的几种情况

zitangkou
4,500 阅读3分钟

1、查询条件包含or，可能导致索引失效

create table t_goods (
  -- 主键索引
  id varchar(32) primary key,
  name varchar(100),
  user_id varchar(32),
  type_id varchar(32),
  merchant_id varchar(32)
);
-- 创建普通索引
create index inx_goods_name on t_goods(name);
create index inx_goods_user_id on t_goods(user_id);
create index inx_goods_merchant_id on t_goods(merchant_id);
-- 显示索引
show index from t_goods;
-- 插入数据
insert into t_goods(id, name, user_id, type_id, merchant_id) values ('111', '蓝光电视', '333', '222', '250');
-- 使用索引
explain select * from t_goods where id = '111' or name = '蓝光电视';
-- 索引失效
explain select * from t_goods where id = '111' or type_id = '222';

2、查询条件为字符串类型（数字字符串），未使用''可能导致索引失效

insert into t_goods(id, name, user_id, type_id, merchant_id) values ('123', '西瓜', '333', '456', '250');
-- 索引失效（字符串与数字比较，类型不匹配，数据库引擎默认隐式转换为浮点数再比较）
explain select * from t_goods where id = 123;
-- 使用索引
explain select * from t_goods where id = '123';

3、通配符‘like’、‘_’可能导致索引失效

-- 索引失效
explain select * from t_goods where name like '%电视';
explain select * from t_goods where name like '%光电%';
explain select * from t_goods where name like '__电视';
explain select * from t_goods where name like '_光电_';
-- 使用索引
explain select * from t_goods where name like '蓝光%';
explain select * from t_goods where name like '蓝光__';
-- 使用索引（覆盖索引，只查询索引字段）
explain select name from t_goods where name like '%电视';
-- 使用索引（主键索引）
explain select id, name from t_goods where name like '%电视';
-- 索引失效
explain select id, name, user_id from t_goods where name like '%电视';

4、联合索引，不符合最左匹配原则，索引可能失效

create table t_merchant (
  -- 主键索引
  id varchar(32) primary key,
  name varchar(100),
  phone varchar(20),
  address varchar(200)
);
insert into t_merchant(id, name, phone, address) values ('250', '胡歌', '17635124678', '湖南省长沙市');
-- 创建联合索引
alter table t_merchant add index inx_merchant_name_phone_address(name, phone, address);
-- （非联合索引）create index inx_merchant_name_phone on t_merchant(name, phone, address);
-- 使用索引
explain select * from t_merchant where name = '胡歌';
explain select * from t_merchant where name = '胡歌' and phone = '17635124678';
explain select * from t_merchant where name = '胡歌' and phone = '17635124678' and address = '湖南省长沙市';
-- 索引不失效（优化器优化？）
explain select * from t_merchant where phone = '17635124678';
explain select * from t_merchant where phone = '17635124678' and address = '湖南省长沙市';

5、在索引列上使用MySQL内置函数，索引可能失效

-- 索引失效
explain select * from t_goods where concat(name, '1') = '蓝光电视1';

6、在索引列上使用+、-、*、/运算，索引可能失效

-- 索引失效
explain select * from t_goods where type_id + '1' = '2221';

7、在索引列上使用!=、<>、not in等，索引可能失效

-- 索引不失效（优化器优化？）
explain select * from t_goods where name != '蓝光电视1';

8、索引列上将is null、is not null与or一起使用，索引可能失效

-- 索引失效
explain select * from t_goods where name is null or user_id is null;

9、左连接或右连接字段，编码不一样可能导致索引失效

-- 查询表的字符编码
show create table t_merchant;
-- 修改表的字符编码
alter table t_merchant character set utf8mb4, collate utf8mb4_0900_ai_ci;
-- 修改表列的字符编码
alter table t_merchant change id id varchar(32) character set utf8mb4, collate utf8mb4_0900_ai_ci;

10、数据库优化器觉得使用全表扫描快，可能不使用索引

文章来源

avatar
文章
阅读
粉丝