MySQL45讲 第十一讲 怎么给字符串字段加索引？

MySQL45讲 第十一讲 怎么给字符串字段加索引？

---

一、引言

在数据库应用中，字符串字段的索引创建至关重要。第十一讲将以支持邮箱登录的系统用户表为例，深入探讨字符串字段索引的创建方式及其优缺点。

例子：现在维护一个支持邮箱登录的系统，用户表是这么定义

mysql> create table SUser(
ID bigint unsigned primary key,
email varchar(64),
...
)engine=innodb;

二、前缀索引

（一）概念与创建方式

1. MySQL 支持前缀索引，可定义字符串的一部分作为索引。若创建索引语句不指定前缀长度，索引将包含整个字符串。例如，在用户表SUser的email字段上创建索引：

   • alter table SUser add index index1(email)创建的index1索引包含整个字符串

   • alter table SUser add index index2(email(6))创建的index2索引仅取每个记录email字段的前 6 个字节。

     

（二）数据结构与存储差异

1. 以index1和index2为例，index2因只取前 6 个字节，占用空间更小。但使用前缀索引可能增加额外记录扫描次数。

   如查询语句select id,name,email from SUser where email='zhangssxyz@xxx.com'：

   • 使用index1只需回主键索引取一次数据，系统认为扫描一行；

   • 使用index2则需回主键索引取 4 次数据，扫描 4 行。不过，**若将index2定义为email(7)，则可只扫描一行。**说明定义合适长度的前缀索引可在节省空间的同时控制查询成本。

（三）确定前缀长度的方法

1. 建立索引时关注区分度，可通过统计索引上不同值的数量判断前缀长度。

   首先用select count(distinct email) as L from SUser;算出列上不同值的数量：再用select count(distinct left(email,4))as L4,count(distinct left(email,5))as L5,count(distinct left(email,6))as L6,count(distinct left(email,7))as L7,from SUser;查看不同长度前缀的情况。设定可接受的损失比例（如 5%），在返回结果中找出不小于L * 95%的值，若L6、L7满足，可选择前缀长度为6。

三、前缀索引对覆盖索引的影响

1. 对于语句select id,email from SUser where email='zhangssxyz@xxx.com';，若使用index1（整个字符串索引结构），可利用覆盖索引直接返回结果；若使用index2（email(6)索引结构），需回主键索引判断email字段值。即使将index2定义为email(18)，InnoDB 仍会回主键索引检查，因为系统不确定前缀索引是否截断完整信息。这表明使用前缀索引可能无法享受覆盖索引对查询性能的优化，选择时需考虑此因素。

四、其他索引创建方式

（一）倒序存储

1. 当字符串前缀区分度不够时，如身份证号（前 6 位地址码在同一县可能相同），可使用倒序存储。存储时将身份证号倒过来，查询时使用mysql> select field_list from t where id_card = reverse('input_id_card_string');。由于身份证号最后 6 位无重复逻辑，可能提供足够区分度，但需用count(distinct)验证。倒序存储在主键索引上不消耗额外存储空间，每次读写需调用reverse函数，会增加扫描行数。

（二）Hash 字段

1. 另一种方式是创建hash字段。在表上新增整数字段保存身份证校验码，并在该字段创建索引。插入记录时用crc32()函数计算校验码填入。查询语句为mysql> select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card'。这种方式索引长度变为 4 字节，占用空间小。hash字段方式每次写读需调用crc32()函数，查询性能相对稳定，平均扫描行数接近 1，但存在校验码冲突概率，且不支持范围查询。

五、总结

字符串字段创建索引有多种方式：

• 直接创建完整索引占用空间大；
• 创建前缀索引节省空间但增加查询扫描次数且不能使用覆盖索引；
• 倒序存储再创建前缀索引可解决字符串本身前缀区分度不够问题；
• 创建hash字段索引查询性能稳定，但有额外存储和计算消耗，且不支持范围扫描。

实际应用中需根据业务字段特点选择合适方式。