MySQL索引的优化之LIKE模糊查询功能实现_Mysql

在使用mysql进行模糊查询时，like语句的性能可能会受到较大影响，尤其是在数据量较大的情况下。

但本质上，用like进行模糊查询，只有以下三种情况：

前缀匹配：如果模糊查询是前缀匹配（如 like '%abc' ）,mysql可以使用索引来加速查询。确保在相关列上创建了索引
后缀匹配：对于后缀匹配（如like 'abc%'），mysql无法使用普通的b-tree索引。可以考虑使用反向索引（reverse index）或全文索引（full-text index）
中间匹配：对于中间匹配（如like '%abc%'）,mysql也无法使用普通的b-tree索引。全文索引或搜索引擎（如elasticsearcha）可能是更好的选择。

一、前缀匹配优化

前缀匹配（如like 'abc%'）可以使用b-tree索引，因此性能较好。确保在相关列上创建索引

示例：

-- 创建表
create table users (
    id int primary key auto_increment,
    username varchar(255) not null
);

-- 插入数据
insert into users (username) values ('john_doe'), ('jane_doe'), ('alice'), ('bob'), ('john_smith');

-- 创建索引
create index idx_username on users(username);

-- 前缀匹配查询
explain select * from users where username like 'john%';

执行计划分析
- 如果使用了索引，explain结果中的key列会显示idx_username,表明查询使用了索引
- type会显示range，表示使用了索引范围扫描

二、后缀匹配优化

后缀匹配（如like '%abc'），无法直接使用b-tree索引，可以通过反转字符串并创建索引来优化

示例：

-- 添加反转列
alter table users add column reversed_username varchar(255);

-- 更新反转列数据
update users set reversed_username = reverse(username);
-- reverse('hello') 的结果是 'olleh'

-- 创建反转列索引
create index idx_reversed_username on users(reversed_username);

-- 后缀匹配查询（转换为前缀匹配）
explain select * from users where reversed_username like reverse('doe') + '%';

执行计划分析
- 查询反转后的列时，explain结果中的key列会显示idx_reversed_username，表明使用了索引
- type列会显示range，表示使用了索引范围扫描

三、中间匹配优化

中间匹配（如like '%abc%'）无法使用b-tree索引。可以考虑使用全文索引或外部搜索引擎

示例（使用全文索引）

-- 创建全文索引
create fulltext index idx_username_fulltext on users(username);

-- 全文索引查询
explain select * from users where match(username) against('doe');

执行计划分析：
- explain结果中的key列会显示idx_username_fulltext，表明使用了全文索引
- type列会显示fulltext，表示使用了全文索引

四、覆盖索引优化

如果查询只需要返回索引列，可以使用覆盖索引（covering index），避免回表操作

示例：

-- 创建覆盖索引
create index idx_username_covering on users(username, id);

-- 覆盖索引查询
explain select username from users where username like 'john%';

五、减少查询范围

通过其他条件缩小查询范围，减少模糊查询的数据量

示例：

-- 假设有一个注册时间列
alter table users add column registered_at datetime;

-- 插入数据
update users set registered_at = now() - interval floor(rand() * 365) day;

-- 缩小查询范围
explain select * from users 
where registered_at > '2023-01-01' 
and username like 'john%';

执行计划分析
- explain结果中的key列会显示idx_username,表明使用了索引
- rows列的值会减少，表明查询范围缩小

六、避免通配符开头

尽量避免在like语句中使用通配符开头（如%abc），因为这种查询无法使用索引

示例：

-- 不推荐的查询
explain select * from users where username like '%doe';

-- 优化后的查询（使用全文索引）
explain select * from users where match(username) against('doe');

执行计划分析：
- 不推荐的查询中，type列会显示all，表示全表扫描。
- 优化后的查询中，type列会显示fulltext，表示使用了全文索引。

七、使用外部搜索引擎

对于复杂的模糊查询需求，尤其是大数据量场景，可以使用外部搜索引擎（如elatsticsearch）

示例

将数据同步到elasticsearch。
使用elasticsearch进行模糊查询。

八、分区表优化

如果数据量非常大，可以使用分区表（partitioning），来较少每次查询需要扫描的数据量

示例：

-- 创建分区表
create table users_partitioned (
    id int primary key auto_increment,
    username varchar(255) not null,
    registered_at datetime
) partition by range (year(registered_at)) (
    partition p0 values less than (2020),
    partition p1 values less than (2021),
    partition p2 values less than (2022),
    partition p3 values less than (2023),
    partition p4 values less than maxvalue
);

-- 插入数据
insert into users_partitioned (username, registered_at) 
select username, registered_at from users;

-- 分区表查询
explain select * from users_partitioned 
where registered_at > '2023-01-01' 
and username like 'john%';