针对含CHAR列与关键词搜索的MySQL大数据集查询性能优化问询

问题根源 #

你的查询慢主要踩了这几个坑：

1. %xxx%这种前后通配的模糊搜索，普通B树索引完全用不上，只能全量扫描符合client_id的行，500万条里扫150万，耗时拉满
2. ORDER BY name, surname触发了Using filesort（文件排序），大结果集排序要占用大量IO和内存资源
3. 现有索引仅覆盖client_id，没法同时过滤disabled、支持排序，优化效果杯水车薪

具体优化措施 #

1. 针对性构建复合索引 #

因为你绝大多数场景查的是disabled='N'的活跃账户，优先给主流场景建复合索引：

CREATE INDEX idx_client_dis_name_surname ON account(client_id, disabled, name, surname);

这个索引能快速定位client_id=? AND disabled='N'的行，且数据已经按name,surname排好序，直接避免文件排序。如果需要兼容偶尔查询全部状态的场景，可以改用这个索引：

CREATE INDEX idx_client_name_surname_dis ON account(client_id, name, surname, disabled);

不过这个索引对disabled的过滤效果稍弱，优先推荐第一种。

2. 替换模糊搜索为全文索引 #

LIKE %xxx%是性能杀手，换成MySQL InnoDB的全文索引优化搜索效率：
先创建联合全文索引：

ALTER TABLE account ADD FULLTEXT INDEX ft_acc_search(username, name, surname);

然后修改查询语句为全文搜索语法：

SELECT * 
FROM account
WHERE client_id = ?
  AND disabled IN (?)
  AND MATCH(username, name, surname) AGAINST(? IN BOOLEAN MODE)
ORDER BY name, surname
LIMIT ?;

注意：默认全文索引不支持短词（小于4字符），需调整ft_min_word_len参数；中文搜索需要额外配置ngram分词插件。

3. 缩小排序数据集范围 #

如果暂时不想改索引结构，可以先通过索引过滤出小范围候选行，再在候选集内做模糊匹配和排序：

SELECT *
FROM (
    -- 先通过索引快速获取符合client_id和disabled的行
    SELECT account_id, name, surname, username, disabled
    FROM account
    WHERE client_id = ? AND disabled IN (?)
) AS temp
-- 在小结果集内执行模糊匹配
WHERE username LIKE '%?%' OR name LIKE '%?%' OR surname LIKE '%?%'
ORDER BY name, surname
LIMIT ?;

这样排序的行数大幅减少，文件排序的成本会显著降低。

4. 按client_id做数据分区 #

client_id基数仅1500，按它做哈希分区，每个客户的数据单独存储在一个分区：

ALTER TABLE account PARTITION BY HASH(client_id) PARTITIONS 1500;

查询特定client_id时，MySQL只会扫描对应分区，直接减少扫描行数。

5. 缓存高频查询结果 #

对于客户常用的搜索关键词，将查询结果缓存到Redis等内存数据库中，设置5分钟左右的过期时间，避免重复查询数据库，大幅提升响应速度。

优化效果验证 #

优化后查看执行计划，若Extra字段不再出现Using filesort，rows列的数值大幅降低，type字段为ref或range，则说明优化生效。

内容的提问来源于stack exchange，提问作者Ashik K