MySQL 5.6中查询仅含'A'字符的Person表数据性能优化问题

问题根源分析 #

你遇到的问题确实很典型：MySQL的B-tree索引无法支持LIKE '%B%'或NOT LIKE '%B%'这类前缀通配的模糊查询，因为索引是按字符串前缀顺序存储的，这种匹配需要遍历整个表才能找到符合条件的记录，自然性能拉胯，加普通索引也没用。

针对MySQL 5.6的可行解决方案 #

下面给你几个适配MySQL 5.6的优化方案，按推荐优先级排序：

1. 新增存储型生成列+索引（最推荐）

MySQL 5.6支持存储型生成列（STORED），我们可以新增一个列来标记当前记录的parents是否全为'A'，然后给这个列建索引，这样查询时就能直接利用索引快速定位。

步骤如下：

• 首先添加生成列：

  ALTER TABLE Person 
  ADD COLUMN is_all_a TINYINT(1) 
  AS (CASE WHEN parents NOT LIKE '%B%' THEN 1 ELSE 0 END) STORED;
  

  这里STORED表示这个列的值会实际存在磁盘上，而不是查询时临时计算（MySQL 5.6不支持虚拟列建索引，所以必须用STORED）。
• 给生成列建索引：

  CREATE INDEX idx_person_is_all_a ON Person(is_all_a);
  

• 之后查询就可以改成：

  SELECT * FROM Person WHERE is_all_a = 1;
  

  这个查询会直接走索引，性能会有质的提升，唯一的代价是占用一点点额外的磁盘空间，但对于字符串列来说这点开销完全值得。

2. 利用字符串长度匹配替代LIKE

如果不想新增列，可以用字符串函数来判断parents中是否包含'B'：当parents的长度和把所有'B'替换为空后的长度相等时，说明没有'B'，也就是全为'A'。

查询语句如下：

SELECT * FROM Person 
WHERE LENGTH(parents) = LENGTH(REPLACE(parents, 'B', ''));

这个写法还是会全表扫描，但REPLACE+LENGTH的计算效率比LIKE的模糊匹配要高一些，适合数据量不算特别大的场景，或者临时查询用。

3. 应用层维护标记字段（适合能修改业务代码的场景）

如果你的业务代码可以修改，那在插入或更新Person记录时，直接在应用层判断parents是否全为'A'，然后给一个比如is_all_a的字段赋值（1表示全A，0表示有B），之后查询直接用这个字段过滤。

这种方案性能最优，因为查询时完全不需要计算，直接走索引，而且不需要数据库维护生成列的开销，但需要修改业务代码逻辑。

4. 全文索引（不推荐，仅作补充）

虽然全文索引主要用于自然语言文本，但理论上可以通过调整配置来适配这种场景，但操作复杂且性价比极低：

• 需要把parents列改成全文索引
• 还要修改MySQL的ft_min_word_len参数为1（默认是4，这样才能识别单个字符'A'/'B'）
• 之后用MATCH AGAINST来查询，但这种方式对于短字符串的匹配效率远不如前面的方案，所以不推荐。

总结 #

优先选择方案1（生成列+索引），不需要改业务代码就能获得极佳的查询性能；如果能改代码，方案3是最优的；方案2适合临时应急或者小数据量场景。

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