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工具
页:
[1]