技术分享 | 在长字符串上创建索引

作者:姚远

MySQL ACE,华为云 MVP ,专注于 Oracle、MySQL 数据库多年,Oracle 10G 和 12C OCM,MySQL 5.6,5.7,8.0 OCP。现在鼎甲科技任技术顾问,为同事和客户提供数据库培训和技术支持服务。

本文来源:原创投稿

*爱可生开源社区出品,原创内容未经授权不得随意使用,转载请联系小编并注明来源。


当在很长的字符串的字段上创建索引时,索引会变得很大而且低效,一个解决办法是 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));

中国领先的企业数据处理技术整体解决方案提供商,开源数据库领域优秀企业。为大型行业用户的特定场景提...

344 声望
177 粉丝
0 条评论
推荐阅读
新特性解读 | 针对用户定制不同格式执行计划
作者:杨涛涛资深数据库专家,专研 MySQL 十余年。擅长 MySQL、PostgreSQL、MongoDB 等开源数据库相关的备份恢复、SQL 调优、监控运维、高可用架构设计等。目前任职于爱可生,为各大运营商及银行金融企业提供 MyS...

爱可生云数据库

百万并发场景中倒排索引与位图计算的实践
Promise时效控单系统作为时效域的控制系统,在用户下单前、下单后等多个节点均提供服务,是用户下单黄金链路上的重要节点;控单系统主要逻辑是针对用户请求从规则库中找出符合条件的最优规则,并将该规则的时效控...

京东云开发者1阅读 370

封面图
MySql索引下推知识分享
Mysql 是大家最常用的数据库,下面为大家带来 mysql 索引下推知识点的分享,以便巩固 mysql 基础知识,如有错误,还请各位大佬们指正。

京东云开发者2阅读 561

封面图
ElasticSearch 必知必会 - 进阶篇
京东物流:康睿 姚再毅 李振 刘斌 王北永说明:以下全部均基于 ElasticSearch 8.1 版本一.跨集群检索 - ccr官网文档地址: [链接]跨集群检索的背景和意义跨集群检索定义跨集群检索环境搭建官网文档地址: [链接]...

京东云开发者2阅读 334

封面图
Clickhouse查询性能优化
虽然clickhouse在大数据量查询速度会比关系型数据库如mysql或者postrges快很多,但还是有很多地方需要去了解和配置,达到提供最低资源获取最大产出

龚正阳阅读 1.4k

【Postgresql】索引类型(btree、hash、GIST、GIN)
Postgresql 存在许多特定的索引查询类型,和大部分的Btree为基础架构的关系型数据库一样,在创建索引缺省的时候会把btree作为默认值。

Xander阅读 792

MYSQL-INNODB索引构成详解
对于MYSQL的INNODB存储引擎的索引,大家是不陌生的,都能想到是 B+树结构,可以加速SQL查询。但对于B+树索引,它到底“长”得什么样子,它具体如何由一个个字节构成的,这些的基础知识鲜有人深究。本篇文章从MYSQL...

京东云开发者1阅读 361评论 1

封面图

中国领先的企业数据处理技术整体解决方案提供商,开源数据库领域优秀企业。为大型行业用户的特定场景提...

344 声望
177 粉丝
宣传栏