组合索引列顺序变更致LIKE语句性能骤降问题咨询

问题描述 #

表结构 #

CREATE TABLE tm(
    id INT AUTO_INCREMENT PRIMARY KEY,
    source TEXT(7000) NOT NULL,
    target TEXT(6000) NOT NULL,
    language CHAR(3),
    customer VARCHAR(10))

索引与查询耗时对比 #

1. 使用组合索引 (source(755), customer, language) 时，两条查询耗时均为0.015s：

   SELECT source FROM tm WHERE customer = 'Customer A' AND language = 'ENU' AND source LIKE '%.net%';  -- 0.015s
   SELECT source FROM tm WHERE source LIKE '%.net%' AND customer = 'Customer A' AND language = 'ENU';  -- 0.015s
   

2. 使用组合索引 (customer, language, source(755)) 时，两条查询耗时大幅增加至1.7s：

   SELECT source FROM tm WHERE customer = 'Customer A' AND language = 'ENU' AND source LIKE '%.net%';  -- 1.7s
   SELECT source FROM tm WHERE source LIKE '%.net%' AND customer = 'Customer A' AND language = 'ENU';  -- 1.7s
   

EXPLAIN结果补充 #

• 使用(source(755), customer, language)索引时，查询的EXPLAIN显示命中该索引，且存在Using index（覆盖索引）标识，扫描的索引行数较少。
• 使用(customer, language, source(755))索引时，查询的EXPLAIN显示扫描行数远大于前者，部分场景可能未有效利用索引或需要回表查询。

问题分析与解答 #

关于“左前缀规则遇LIKE停止”的说法纠正 #

网上的该说法存在前提偏差：它仅适用于前缀匹配LIKE（如'abc%'）的场景——此时左前缀索引会生效到该LIKE字段，后续字段无法利用索引；但如果是包含式/后缀匹配LIKE（如'%abc%'、'%abc'），该字段上的索引完全无法用于数据过滤，只能作为覆盖索引的一部分发挥作用。

性能差异的核心原因 #

1. (source(755), customer, language)索引的优势：覆盖索引
   你的查询仅返回source字段，而该索引包含了source(755)、customer、language三个字段，MySQL可以直接扫描索引完成查询，无需回表读取原表数据（即Using index）。即使source是%.net%的模糊匹配，扫描索引的成本远低于扫描全表，因此耗时极短。

2. (customer, language, source(755))索引的劣势：无法利用source索引过滤，且可能需要回表
   虽然customer和language是等值匹配，可以利用索引前缀过滤出符合条件的行，但source LIKE '%.net%'无法利用source部分的索引进行过滤。如果customer='Customer A' AND language='ENU'过滤后剩余的行数极多，MySQL需要扫描这些行的source字段进行匹配，而source是TEXT类型，即使索引里有前755个字符，也无法满足%.net%的匹配需求，大概率需要回表读取完整的source值，导致耗时剧增。

总结 #

你遇到的情况并非网上说法错误，而是该说法的适用场景被忽略了。对于包含式LIKE查询，覆盖索引的优先级远高于前缀匹配的索引结构，这就是你得到相反结果的核心原因。

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