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

本文主要是介绍MySQL索引的优化之LIKE模糊查询功能实现，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

《MySQL索引的优化之LIKE模糊查询功能实现》：本文主要介绍MySQL索引的优化之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'), ('aljavascriptice'), ('bob'), ('john_smith');

-- 创建索引
CREATE INDEX idxphp_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('helloNnMFdC') 的结果是 '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),
    jsPARTITION 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%';