MySQL含列子串搜索与多条件过滤的查询如何优化索引？

数据老陈

2026-10-19

问题核心根因

你之前的索引设计存在两个核心问题：

联合索引顺序错误：price_date索引以聚合字段price为最左列，你的查询未对price设置过滤条件，不符合最左匹配原则，完全无法触发索引。
未对模糊查询条件做前置处理：url LIKE '%/shop%'属于中间模糊匹配，普通B树索引原生不支持这类过滤，需要提前将该匹配逻辑转化为可索引的特征值。

可行优化方案

方案1：表达式索引/生成列+覆盖联合索引（改造成本最低，性能提升最明显）
如果你使用MySQL 8.0及以上版本，可以直接创建带表达式的覆盖联合索引，无需新增字段，也不需要修改原查询语句：
```
CREATE INDEX idx_query_opt ON mytable(domain, (url LIKE '%/shop%'), date, price);
```
低版本MySQL可以新增一个持久化生成列存储url的匹配结果，再建联合索引，生成列会随url字段自动更新，不需要业务代码做额外维护：
```
-- 新增自动维护的生成列，url包含/shop时为1，否则为0
ALTER TABLE mytable ADD COLUMN has_shop_path TINYINT(1) GENERATED ALWAYS AS (url LIKE '%/shop%') STORED;
-- 按「等值过滤列->范围过滤列->聚合列」的顺序创建覆盖索引，完全避免回表
CREATE INDEX idx_query_opt ON mytable(domain, has_shop_path, date, price);
```
生成列方案需要将原查询改写为：
```
SELECT SUM(price) FROM mytable WHERE domain = 'com' AND has_shop_path = 1 AND date BETWEEN '2001-01-01' AND '2007-01-01';
```
该方案可以让查询全程走覆盖索引，不需要扫描全表，也不需要做实时模糊匹配，性能提升可达上千倍。
方案2：分区优化
10亿行大表可以按date字段创建范围分区，比如按年/季度分区，查询时只会扫描2001-2007对应的分区，进一步减少扫描的数据量。
方案3：预聚合汇总表
如果该查询是高频固定查询，可以提前按天/周维度预聚合符合条件的price总和存入汇总表，查询时直接读取预计算结果，响应时间可低至毫秒级。

补充问题解答

这是不是SQL本身的性能局限？
不是。该问题本质是查询模式和索引、schema设计不匹配导致的，并非SQL的能力上限。只要优化逻辑符合存储引擎的索引规则，SQL完全可以支撑10亿级表的这类查询需求。
同类查询在NoSQL数据库中是否能获得更好的性能？
性能差异本质是存储引擎的差异，和是否为NoSQL没有直接关系：
- 如果使用文档型NoSQL（如MongoDB），优化逻辑和SQL完全一致，仍需要针对查询模式创建对应索引，优化后的性能和优化后的SQL数据库不会有明显差异。
- 如果使用列式存储的分析型NoSQL（如ClickHouse），这类聚合查询的性能会比行存SQL数据库更好，因为列式存储只需要加载需要的几列数据，不需要读取整行，天然适合分析类查询。但目前很多NewSQL数据库也已经支持列存引擎，同样可以达到同等性能。

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