一个索引创建错误引发的思考

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 同事反馈说某个 MySQL 数据库创建索引提示错误,模拟报错如下:CREATE INDEX t_reg_code_idx USING BTREE ON t(reg_code)BLOB/TEXT column 'reg_code' used in key specification without a key length从该提示可知,给 T 表的 reg_code 列创建一个 BTREE 索引,而这个 reg_code 列的字段类型是 BLOB 或 TEXT。需要在键的说明中有长度定义,这是什么意思?

背景
同事反馈说某个 MySQL 数据库创建索引提示错误,模拟报错如下:

CREATE INDEX t_reg_code_idx USING BTREE ON t(reg_code)
BLOB/TEXT column 'reg_code' used in key specification without a key length
从该提示可知,给 T 表的 reg_code 列创建一个 BTREE 索引,而这个 reg_code 列的字段类型是 BLOB 或 TEXT。

需要在键的说明中有长度定义,这是什么意思?

表索引前缀长度限制
MySQL 8.0
从 MySQL 8.0 的官方手册可以找到这段对 Index Prefixes 的说明。意思是如果对 BLOB 或者 TEXT 列创建索引,必须指定索引的前缀长度。对于使用 REDUNDANT 或者 COMPACT 行格式的 InnoDB 表,索引前缀最多 767 个字节,对于使用 DYNAMIC 或者 COMPRESSED 行格式的 InnoDB 表,索引前缀的上限最多是 3072 个字节,如果是 MyISAM 表,前缀长度最多可以达到 1000 个字节。

MySQL 5.7
而 MySQL 5.7 官方手册中,对索引前缀的限制有所不同,InnoDB 表的索引前缀最多可以达到 1000 个字节(此处我认为是错误的,应该是 3072),但前提是设置了 innodb_large_prefix(只对 DYNAMIC 或者 COMPRESSED 行格式生效,对 REDUNDANT 或者 COMPACT 行格式无效),否则只能达到 767 个字节。

因此可知,MySQL 8.0 在 InnoDB 表的索引前缀长度限制的设置上有所调整,但是限制还是有,这是和 Oracle 等数据库有所不同的一个特性。

验证
通过实验,验证 MySQL 8.0 对于前缀长度的限制。

创建一张 row format 是 COMPACT 的 InnoDB 表,指定前缀长度 10000,提示最大键的长度只能是 767 个字节。

create table test01 (
id int(30) not null auto_increment,
t_a text,
primary key(id),
index idx_t_a(t_a(10000))
) COLLATE='gbk_chinese_ci' ENGINE=InnoDB ROW_FORMAT=COMPACT;

SQL 错误 [1071] [42000]: Specified key was too long; max key length is 767 bytes
创建一张 row format 是 COMPRESSED 的 InnoDB 表,指定前缀长度 10000,提示最大键的长度只能是 3072 个字节。

create table test01 (
id int(30) not null auto_increment,
t_a text,
primary key(id),
index idx_t_a(t_a(10000))
) COLLATE='gbk_chinese_ci' ENGINE=InnoDB ROW_FORMAT=COMPRESSED;

SQL 错误 [1071] [42000]: Specified key was too long; max key length is 3072 bytes
总结
抛开技术问题,和同事追问了下这个操作的背景。原始需求是某个厂商的 ETL 任务需要从源库将数据导入目标库,源库字段是 VARCHAR 类型,目标库定义为 TEXT,才间接引起的这个问题。推测一种可能的原因,因为 VARCHAR、TEXT 都可以存储字符串类型的数据,所以没做区分,另一种可能,为了图省事儿,不用关注源库和目标库字符串类型定义的长度,直接设置了 TEXT 类型,保证肯定能存下。

无论是何种原因,TEXT 这种大字段类型,一般不推荐作为索引检索字段,因为往往它存储了很多字符,索引存储空间会占用更多,索引的区分度也会有影响。

因此,虽然这个问题表象是个技术问题,但实际上来源于不合理的设计,我们在进行应用设计、数据库设计时,如果能多考虑一些合理性,避免一些所谓的省事儿,可能在实际使用过程中,就会更顺畅,相辅相成的。

更多技术文章,请访问:https://opensource.actionsky.com/

关于 SQLE
爱可生开源社区的 SQLE 是一款面向数据库使用者和管理者,支持多场景审核,支持标准化上线流程,原生支持 MySQL 审核且数据库类型可扩展的 SQL 审核工具。

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
6月前
|
存储 关系型数据库 索引
10. 在一个非主键字段上创建了索引, 想要根据该字段查询到数据, 需要查询几次 ?
在非主键字段上创建索引,查询数据通常需两次。对于MyISAM,先通过索引找到数据行指针,再获取数据;而InnoDB则先找主键ID,再从主键索引中查找数据。
36 0
|
6月前
|
NoSQL MongoDB 数据库
通过优化索引以消除 MongoDB 中的 "查询目标已超过1000个扫描对象/返回的文档数" 警告
MongoDB NoSQL数据库在处理复杂查询时可能出现“查询目标已超过1000个扫描对象/返回的文档数”警告。文章分析了该问题,展示了一个示例集合和相关索引,并提供了查询示例。通过`explain`命令发现查询未有效利用索引。解决方案是遵循ESR规则,创建新索引从而优化查询并消除警告。
160 1
|
5月前
|
SQL 关系型数据库 MySQL
MySQL数据库——索引(5)-索引使用(上),验证索引效率、最左前缀法则、范围查询、索引失效情况、SQL提示
MySQL数据库——索引(5)-索引使用(上),验证索引效率、最左前缀法则、范围查询、索引失效情况、SQL提示
66 0
测试关于索引的操作-创建索引
测试关于索引的操作-创建索引
测试关于索引的操作- 修改索引
测试关于索引的操作- 修改索引
ES删除索引和测试关于文档的操作
ES删除索引和测试关于文档的操作
|
关系型数据库 MySQL 索引
mysql新建数据时判断表,字段,索引是否存在
mysql新建数据时判断表,字段,索引是否存在
121 0
|
存储 SQL 关系型数据库
FAQ系列 | 列类型被自动修改导致复制失败
FAQ系列 | 列类型被自动修改导致复制失败
111 0
|
索引
索引分类、创建索引、删除索引
索引分类、创建索引、删除索引
137 0
索引分类、创建索引、删除索引
|
关系型数据库 MySQL 数据库
创建索引,这些知识应该了解
在 MySQL 中,基本上每个表都会有索引,有时候也需要根据不同的业务场景添加不同的索引。索引的建立对于数据库高效运行是很重要的,本篇文章将介绍下创建索引相关知识及注意事项。
144 0