技术分享 | 在长字符串上创建索引
作者：姚远
本文来源：原创投稿
* 爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。
当在很长的字符串的字段上创建索引时，索引会变得很大而且低效，一个解决办法是 crc32 或 md5 函数对长字符串进行哈希计算，然后在计算的结果上创建索引。在 MySQL 5.7 以后的版本，可以创建一个自动生成的字段，例如可以创建下面一个表：
create table website(
id int unsigned not null,
web varchar(100) not null,
webcrc int unsigned generated always as (crc32(web)) not null,
primary key (id)
);
向这个表中插入记录：
mysql> insert into website(id,web) values(1,"https://www.scutech.com");
Query OK, 1 row affected (0.07 sec)
mysql> select * from website;
+----+-------------------------+-----------+
| id | web | webcrc |
+----+-------------------------+-----------+
| 1 | https://www.scutech.com | 851176738 |
+----+-------------------------+-----------+
1 row in set (0.00 sec)
可以看到字段 webcrc 中自动生成了 web 字段的循环冗余校验值，在这个字段上创建索引，可以得到一个占用空间少，而且高效的索引。
在 MySQL 8.0.13 以后的版本，可以直接创建函数索引，例如：
create table website8(
id int unsigned not null,
web varchar(100) not null,
primary key (id),
index ((crc32(web)))
);
查询这个表上的索引：
mysql> show index from website8\G
*************************** 1. row ***************************
Table: website8
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
*************************** 2. row ***************************
Table: website8
Non_unique: 1
Key_name: functional_index
Seq_in_index: 1
Column_name: NULL
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: crc32(`web`)
2 rows in set (0.00 sec)
可以看到第一个索引是主键，第二个索引是函数索引。
解决索引字段长的另一个办法是创建前缀索引（prefix index），前缀索引的创建语法是：col_name(length)，前缀索引是对字符串的前面一部分创建索引，支持的数据类型包括：CHAR 、VARCHAR 、BINARY 和 VARBINARY 。创建前缀索引的关键是选择前缀的字符串的长度，长度越长，索引的选择性越高，但存储的空间也越大。
sbtest2 表中 c 字段是 120 长度的字符串，下面的 SQL 语句查询在不同长度时索引的选择性：
mysql> select
count(distinct(left(c,3)))/count(*) sel3,
count(distinct(left(c,7)))/count(*) sel7,
count(distinct(left(c,9)))/count(*) sel9,
count(distinct c)/count(*) selectivity
from sbtest1;
+--------+--------+--------+-------------+
| sel3 | sel7 | sel9 | selectivity |
+--------+--------+--------+-------------+
| 0.0120 | 0.9959 | 1.0000 | 1.0000 |
+--------+--------+--------+-------------+
1 row in set (1.66 sec)
可以看到在这个字段的前 9 位创建索引即可达到 1 的选择性，再增加这个索引的前缀位数，索引的选择性并不会提高，下面是创建索引的命令：
mysql> alter table sbtest2 add index (c(9));本文参与 腾讯云自媒体同步曝光计划，分享自微信公众号。
原始发表：2021-07-12，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server （TencentDB for SQL Server）是业界最常用的商用数据库之一，对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权，可持续为用户提供最新的功能，避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
腾讯云开发者
