mysql----如何在使用like %xxx% 的情况下避免索引失效
程序员文章站
2022-03-03 20:01:49
...
背景:当在某列建立索引,并且在查询条件中使用like '%xxx%' 这样的语句时,会使得索引失效。当时实际的环境必须使用 like '%xxx%' 这样的条件,那么应该如何解决这个问题。
答:使用覆盖索引避免这个问题。即:在要查询的列和其他的列之间建立一个复合索引,然后查询时同时查询和复合索引有关的列即可避免全表扫描。
create table user_info(
id int PRIMARY key auto_increment,
name varchar(16) default '',
age tinyint default 0,
address varchar(32) default ''
);
insert into user_info(name,age,address)
values
('1a1',1,'beijing'),
('2b2',2,'shanghai'),
('3c3',3,'tianjin'),
('4a4',4,'chongqi'),
('5b5',5,'guangzhou'),
('6c6',6,'shenzhen');
#创建复合索引
create index idx_n_a on user_info(name,age);
explain select name from user_info where name like '%a%';
1 SIMPLE user_info index idx_n_a 53 6 16.67 Using where; Using index
explain select name,age from user_info where name like '%a%';
1 SIMPLE user_info index idx_n_a 53 6 16.67 Using where; Using index
以下两个例子是查询了不在复合索引中的列进而造成全表扫描
explain select name,age,address from user_info where name like '%a%';
1 SIMPLE user_info ALL 6 16.67 Using where
explain select * from user_info where name like '%a%';
1 SIMPLE user_info ALL 6 16.67 Using where