MySQL中等规模表列后缀匹配查询的性能优化方案咨询

针对你1500万条记录的中等规模表，要匹配某列（长度7或8位）结尾等于7位值的查询，不用反转列的话，有几个高效的方案，根据你的MySQL版本来选：

1. MySQL 8.0+：使用函数索引 #

这是最直接的方案，不需要额外存储列，直接基于RIGHT()函数的结果创建索引：

CREATE INDEX idx_col_suffix7 ON your_table (RIGHT(your_column, 7));

之后你的查询可以写成：

SELECT * FROM your_table WHERE RIGHT(your_column, 7) = '{value}';

这个索引会直接存储每一行your_column最后7位的值，查询时MySQL可以通过索引快速定位到匹配的行，避免全表扫描。

2. MySQL 5.7+：使用虚拟生成列 #

如果你的版本是5.7（不支持函数索引），可以创建虚拟生成列（不占用实际存储），再给这个列建索引：

ALTER TABLE your_table 
ADD COLUMN col_suffix7 VARCHAR(7) GENERATED ALWAYS AS (RIGHT(your_column, 7)) VIRTUAL;

CREATE INDEX idx_col_suffix7 ON your_table (col_suffix7);

查询时同样用生成列匹配：

SELECT * FROM your_table WHERE col_suffix7 = '{value}';

虚拟生成列的值是在查询时动态计算的，但索引会预先存储计算后的结果，所以性能和函数索引接近，又不会增加表的存储负担。

3. 兼容旧版本（MySQL 5.6及以下）：拆分查询条件+分区优化 #

如果你的版本更老，只能用这种折衷方案：

• 首先按列的长度分区，减少扫描范围：

ALTER TABLE your_table 
PARTITION BY LIST(CHAR_LENGTH(your_column)) (
    PARTITION p7 VALUES IN (7),
    PARTITION p8 VALUES IN (8)
);

• 然后把查询拆成两个明确的条件，利用普通索引优化：

SELECT * FROM your_table 
WHERE (CHAR_LENGTH(your_column) = 7 AND your_column = '{value}')
   OR (CHAR_LENGTH(your_column) = 8 AND RIGHT(your_column, 7) = '{value}');

这个方案中，第一个条件your_column = '{value}'可以用到your_column的普通索引，第二个条件会扫描p8分区（只占表的一部分数据），比全表扫描高效很多。

为什么不直接用LIKE '%{value}'？ #

因为前缀通配符的LIKE查询会让MySQL无法使用普通索引，只能进行全表扫描，对于1500万条记录的表来说，性能会非常差。

内容的提问来源于stack exchange，提问作者Aviran Ben David