mysql like‘%xxx%‘优化

CREATE TABLE test (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50),
  INDEX idx_name (name)
) CHARACTER SET utf8;

select * from test where name like '%abc%';

常规优化

select * from test where name like 'abc%'
union
select * from test where name like 'cba%';

1、建立虚拟列

alter table test add column `v_name` varchar(50) generated always as (reverse(name));

2、为虚拟列创建索引

alter table test add index `idx_name_virt`(v_name);

3、union联合使用虚拟队列进行查询

select * from test where v_name like 'cba%'
union
select * from test where name like 'abc%';