0. 背景

最近在研究上亿的数据在MySQL中如何优化存储时，偶然间看到一篇文章提到，对于优化索引大小可以采用前缀索引来减小索引体积。之前确实没有特别关注过前缀索引，于是便做了一些了解。

1. 概念

定义：当您为列创建二级索引时，MySQL会将列的值存储在单独的数据结构中，例如B-Tree和Hash。如果列是字符串列，则索引将占用大量磁盘空间并可能减慢INSERT操作速度。为解决此问题，MySQL允许您使用以下语法为字符串列的列值的前导部分创建索引。请注意，前缀支持和前缀长度（如果支持）取决于存储引擎。对于带REDUNDANT或COMPACT行格式的InnoDB表 ，最大前缀长度为767字节。但是，对于带有DYNAMIC或COMPRESSED行格式的InnoDB表 ，前缀长度为3,072字节。MyISAM表的前缀长度最多为1,000个字节。

说大白话，其实就是为字符串类型（包括二进制字符串类型）的值的前面N个字符或字节单独创建索引。针对的字段类型包括CHAR/VARCHAR/TEXT/BINARY/VARBINARY/BLOB等。

2. 如何创建

MySQL允许使用如下语法创建前缀索引：

1
column_name(length)

例如，为一个现有的数据表中，某一个字段创建前缀索引：

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

3. 示例

假设我们有一张如下结构的数据表：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
CREATE TABLE `product`
(
    `id` INT AUTO_INCREMENT COMMENT 'ID',
    `product_name` VARCHAR(200) NOT NULL COMMENT '商品名称',
    `product_image` VARCHAR(255) NULL COMMENT '商品图',
    `create_time` TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP(3) NOT NULL COMMENT '创建时间',
    CONSTRAINT product_pk PRIMARY KEY (`id`)
);

INSERT INTO `product` (product_name, product_image)
VALUES ('蓝米手机0992857732', 'https://www.baidu.com/img/PCfb_5bf082d29588c07f842ccde3f97243ea.png'),
       ('蓝米手机13431243', 'https://www.baidu.com/img/PCfb_5bf082d29588c07f842ccde3f97243ea.png'),
       ('蓝米手机22324124', 'https://www.baidu.com/img/PCfb_5bf082d29588c07f842ccde3f97243ea.png'),
       ('蓝米手机33212156745', 'https://www.baidu.com/img/PCfb_5bf082d29588c07f842ccde3f97243ea.png'),
       ('蓝米手机45134135345', 'https://www.baidu.com/img/PCfb_5bf082d29588c07f842ccde3f97243ea.png'),
       ('蓝米手机56578763', 'https://www.baidu.com/img/PCfb_5bf082d29588c07f842ccde3f97243ea.png'),
       ('蓝米手机641236346', 'https://www.baidu.com/img/PCfb_5bf082d29588c07f842ccde3f97243ea.png'),
       ('蓝米手机7356436341', 'https://www.baidu.com/img/PCfb_5bf082d29588c07f842ccde3f97243ea.png'),
       ('蓝米手机814532543', 'https://www.baidu.com/img/PCfb_5bf082d29588c07f842ccde3f97243ea.png'),
       ('蓝钻手机11352577', 'https://www.baidu.com/img/PCfb_5bf082d29588c07f842ccde3f97243ea.png'),
       ('蓝钻手机277456342', 'https://www.baidu.com/img/PCfb_5bf082d29588c07f842ccde3f97243ea.png'),
       ('蓝钻手机3432567843', 'https://www.baidu.com/img/PCfb_5bf082d29588c07f842ccde3f97243ea.png'),
       ('蓝钻手机434236347', 'https://www.baidu.com/img/PCfb_5bf082d29588c07f842ccde3f97243ea.png'),
       ('蓝钻手机5234236856', 'https://www.baidu.com/img/PCfb_5bf082d29588c07f842ccde3f97243ea.png'),
       ('蓝钻手机614234322', 'https://www.baidu.com/img/PCfb_5bf082d29588c07f842ccde3f97243ea.png'),
       ('蓝钻手机757909088', 'https://www.baidu.com/img/PCfb_5bf082d29588c07f842ccde3f97243ea.png'),
       ('华威手机1870986', 'https://www.baidu.com/img/PCfb_5bf082d29588c07f842ccde3f97243ea.png'),
       ('华威手机26759067', 'https://www.baidu.com/img/PCfb_5bf082d29588c07f842ccde3f97243ea.png'),
       ('华威手机337653867990', 'https://www.baidu.com/img/PCfb_5bf082d29588c07f842ccde3f97243ea.png'),
       ('华威手机43765867590', 'https://www.baidu.com/img/PCfb_5bf082d29588c07f842ccde3f97243ea.png'),
       ('华威手机5375689796', 'https://www.baidu.com/img/PCfb_5bf082d29588c07f842ccde3f97243ea.png');

当我们需要查询『蓝米』开头的手机时，会写这样一个SQL：

1
2
3
SELECT product_name, product_image
FROM product
WHERE product_name LIKE '蓝米%';

通过EXPLAIN分析得出结果为：

image-20210527102352814

因为product_name没有索引，所以查询优化器必须扫描所有行才能返回结果。这时我们需要添加一个索引来优化查询效率。

我们的目的是保证列中值的唯一性最大化。那么如何找出最合适的前缀长度呢？使用以下语句尝试看：

1
2
SELECT COUNT(DISTINCT(LEFT(product_name, 5)))
FROM `product`;

经过多次尝试，前缀索引长度为5时，能实现唯一性的最大化。即前缀索引长度为5时，所有值都是唯一的。

这时我们创建前缀索引：

1
2
CREATE INDEX idx_product_name
    ON product (product_name(5));

再一次执行EXPLAIN分析查询语句，得到如下结果：

image-20210527102236622

此时type已经为range级别了，rows显示9。我们的前缀索引就创建好了。