MySQL 字符串前缀索引实操指南：省空间提性能的核心玩法字符串前缀索引一、定义二、创建操作三、实战案例四、黄金

字符串前缀索引

一、定义
二、创建操作
三、实战案例
四、黄金原则：前缀长度的选择
- 怎么选择合适的长度？
五、避坑

一、定义

前缀索引是一种特殊的索引，它只对字符串列的前 N 个字符（或字节）建立索引，而不是对整个字符串。

适用场景：当字符串列很长（如 VARCHAR(255)、TEXT），如果对整个列建索引，会占用大量磁盘空间，且写入速度慢。

二、创建

1、对已有表创建前缀索引

CREATE INDEX index_name
ON table_name (column_name(length));

-- 为 first_name 列的前 2 个字符创建索引
CREATE INDEX idx_first_name_prefix_2 
ON customer(first_name(2));

2、建表时定义前缀索引

对于 CHAR、VARCHAR、TEXT 等非二进制类型，length 指的是字符数。
对于 BINARY、VARBINARY、BLOB 等二进制类型，length 指的是字节数。

CREATE TABLE table_name(
  column_list,
  INDEX(column_name(length))
);

-- 建customer表时定义前缀索引
CREATE TABLE customer (
    ...
    first_name VARCHAR(45),
    INDEX (first_name(2))  -- 直接在建表语句中定义
);

三、实战案例

——默认创建customer表，创建frist_name的索引（customer 表的 first_name 列）

-- 但没创建 前缀索引
EXPLAIN
SELECT *
FROM customer
WHERE first_name LIKE 'GE%';
>>查询的语句是索引失效的，走的全表扫描。

问题：first_name 列没有索引，执行 WHERE first_name LIKE 'GE%' 时，优化器只能全表扫描。
解决方案：创建前缀索引 idx_first_name_prefix_2，只对前 2 个字符建索引。
```
CREATE INDEX idx_first_name_prefix_2
ON customer(first_name(2));
```
效果：再次执行查询时，优化器选择使用前缀索引，执行计划从 ALL（全表扫描）变为 range（索引范围扫描），性能大幅提升。

四、黄金原则：前缀长度的选择

前缀索引的效率，完全取决于你选择的前缀长度。核心原则是：

尽量让前缀保持足够的唯一性，唯一性越强，索引效率越高。

怎么选择合适的长度？

测试唯一性：通过查询统计不同前缀长度下的唯一值数量。

-- 统计前 2 个字符的唯一值数量
SELECT COUNT(DISTINCT LEFT(first_name, 2)) FROM customer;

五、避坑

不支持全值匹配优化：前缀索引无法用于 ORDER BY 或 GROUP BY，因为索引中没有完整的字符串值。
不支持覆盖索引：如果查询需要返回完整的字符串列，必须通过回表查询，无法直接从索引中获取数据。
仅支持前缀匹配：查询 WHERE first_name LIKE '%GE' 无法使用前缀索引，只有 WHERE first_name LIKE 'GE%' 才能命中。

MySQL 字符串前缀索引实操指南：省空间提性能的核心玩法