Mysql 如何模糊匹配后匹配优化

一、背景

线上存在业务代码，需要模糊匹配且进行后匹配

select * from test where id like "%1231"

二、优化过程

mysql的后模糊匹配是不走索引的，所以数量级增大后，sql执行速度会越来越慢，但是业务一定要保留该功能，且频繁调用；

处理方案：

1. 使用表中已存在的其他列索引

如果查询的表存在其他列索引，可以直接使用模糊匹配查询完整的主键信息，再进行业务查询

select id from test where id like "%1231"
select * from test where id = #{id}

该方案简单迅速，查询索引级别一般可以达到index

2. 使用缓存记录模糊匹配列所有数据

先进行缓存记录所有模糊列数据，再经过缓存进行数据匹配，最后进行业务查询。
该方案需要保证缓存可靠，并且及时更新

3. 数据库增加相反列，并设置对应索引

例如，原模糊列为id，值为123，相反列起名id_reverse，值则为321
当进行模糊匹配时，先对传入参数进行却反，123取反为321，最后用相反列进行匹配

select * from test where id_reverse like "321%"

如果需要保证该条件同时满足前匹配和后匹配

select * from test where id like "123%" or id_reverse like "321%"

该方案需要保证相反列和模糊列数据保持相反，并同步更新，索引级别可达range

type索引类型：

ststem > const > eq_ref > ref > range > index > all

优化级别从左往右递减，没有索引的一般为’all’，需要对type进行优化前提是有索引。

其中’system’和’const’只是理想型，实际只能达到’ref’和’range’。