软件开发培训班 >> 编程开发 >> MySQL


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

在使用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%';
  • 执行计划分析：
    • EXPLAIN结果中的partitions列会显示查询涉及的分区，表明查询只扫描了部分数据 。

九、缓存结果

如果模糊查询的结果不经常变化，可以将查询结果缓存起来，减少数据库的查询压力

示例：

  • 使用redis缓存查询结果
  • 设置缓存的过期时间，确保数据的时效性

总结

通过以上方法，可以显著优化MySQL中LIKE模糊查询的性能 。根据具体的业务需求和数据特点，选择合适的优化策略：

  • 前缀匹配：使用普通索引 。
  • 后缀匹配：使用反转索引 。
  • 中间匹配：使用全文索引或外部搜索引擎 。
  • 大数据量：使用分区表或外部搜索引擎 。
  • 高频查询：使用缓存 。

注：了解MySQL-MATCH ... AGAINST工具参考MySQL-MATCH ... AGAINST工具

到此这篇关于MySQL--索引的优化--LIKE模糊查询的文章就介绍到这了,更多相关mysql like模糊查询内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持！