MySQL前缀索引

正确地创建和使用索引是实现高性能查询的基础。本文旨在介绍关于长字符列如何高效地使用索引，如果需要可以查阅MySQL索引类型了解MySQL常用的索引类型。

前缀索引

场景

有时需要在很长的字符列（如BLOB、TEXT或很长的VARCHAR类型的列）上创建索引，这会造成索引特别大且慢。

解决方案

为了避免产生大且慢的索引，一种策略是使用MySQL索引类型中提到过的模拟哈希索引，另一种策略就是使用前缀索引。

优缺点

前缀索引就是选择索引列的最左n个字符来建立索引。这样就大大节约了索引空间，进而提高索引效率。但其缺点就是：MySQL无法使用前缀索引做ORDER BY 、GROUP BY 和覆盖扫描。

举例

创建前缀索引的关键在于选择足够长的前缀以保证较高的索引选择性。索引选择性越高查询效率就越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的数据行。为方便举例，创建author表如下：

CREATE TABLE `author` (
   `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
   `name` VARCHAR(32) NOT NULL COMMENT '姓名',
   `gender` TINYINT(1) NOT NULL COMMENT '性别，0-男，1-女',
   `age` TINYINT(3) NOT NULL DEFAULT '0' COMMENT '年龄',
   `email` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '邮箱',
   `homepage` VARCHAR(128) NOT NULL DEFAULT '' COMMENT '主页',
   `add_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间',
   `update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '修改时间',
   PRIMARY KEY (`id`)
 ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

// email列创建前缀索引
CREATE INDEX idx_author_email ON author(email(3));

// 插入5条数据
insert into `author` (`name`, `gender`, `age`, `email`) values('xx','0','20','xx@126.com');
insert into `author` (`name`, `gender`, `age`, `email`) values('yy','1','18','yy@126.com');
insert into `author` (`name`, `gender`, `age`, `email`) values('zz','0','25','zz@126.com');
insert into `author` (`name`, `gender`, `age`, `email`) values('xyz123','0','30','xyz123@126.com');
insert into `author` (`name`, `gender`, `age`, `email`) values('xyz123','0','120','xxx@163.com');

根据email执行相关查询如下：

从执行计划看，无论是精确查询或是模糊查询，都使用了idx_author_email索引。