案例：前缀匹配和后缀匹配，根据【手机号135****6764】，或根据【用户名首字符和手机尾号】，使用【计算列+前缀索引】

 表数据tb_person，参考测试java程序插入单表2000万条数据（MySQL,PostgreSQL,达梦）

1、场景一：根据手机号135****6764搜索

方式一：没有索引select *，查询时间 13s

select * from tb_person where tel like '135%' and tel like '%6764';

 

方式二：

没有索引，查询耗时 4.3s

select id from tb_person where tel like '135%' and tel like '%6764';

没有索引，关联查询，查询耗时 4.6s

select p.* from tb_person p join(
select id from tb_person where tel like '135%' and tel like '%6764'
) a on a.id=p.id;

没有索引，子查询，查询耗时 4.6s

select p.* from tb_person p where id in(
select id from tb_person where tel like '135%' and tel like '%6764'
);

 

方式三：

给 tel 字段创建索引

CREATE INDEX idx_tel ON tb_person(tel);

使用索引 idx_tel，关联查询，查询耗时 0.4s

select p.* from tb_person p join(
select id from tb_person where tel like '135%' and tel like '%6764'
) a on a.id=p.id;

 

方式四：计算列+前缀索引

-- 添加计算列 tel_lastfour
ALTER TABLE tb_person
ADD column tel_lastfour CHAR(4) GENERATED ALWAYS AS (RIGHT(tel, 4)) VIRTUAL;

-- 建立前缀索引，tel(3)取tel字段前3个字符
CREATE INDEX idx_tel_lastfour ON tb_person(tel(3),tel_lastfour);

查询耗时 0.01s

select p.* from tb_person p join(
select id from tb_person where tel like '135%' and tel_lastfour = '6764'
) a on a.id=p.id;

 

2、场景二：根据【用户名首字符和手机尾号】搜索

方式一：在字段 name,tel 上建立复合索引

根据【用户名首字符和手机尾号】搜索，比如 李*，****6764

-- 建立前缀索引，name(1)取name字段前1个字符
CREATE INDEX idx_name_tel ON tb_person(name(1),tel);

查询耗时 5.2s

select p.* from tb_person p join(
select id from tb_person where name like '李%' and tel like '%6764'
) a on a.id=p.id;

 

方式二：计算列+前缀索引

-- 添加计算列 tel_lastfour
ALTER TABLE tb_person
ADD column tel_lastfour CHAR(4) GENERATED ALWAYS AS (RIGHT(tel, 4)) VIRTUAL;

-- 建立前缀索引，name(1)取name字段前1个字符
CREATE INDEX idx_name_tellastfour ON tb_person(name(1),tel_lastfour);

查询耗时 0.01s

select p.* from tb_person p join(
select id from tb_person where name like '李%' and tel_lastfour = '6764'
) a on a.id=p.id;