小白学习MySQL - 索引键长度限制的问题

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS PostgreSQL,集群系列 2核4GB
简介: MySQL索引键长度限制的问题

最近在工作中,碰到了个很诡异的问题,需求是在两个MySQL数据库为同一张表增加一个二级索引(单键值字段(x varchar(500))),表结构和加索引的语法,都是相同的,但是一个库执行成功了,一个执行失败了,提示错误如下,

Specified key was too long; max key length is 767 bytes


从字面的意思看,是说指定键超长,而且上限是767字节。这是什么意思?MySQL中还会对索引键的长度有限制?


我们首先从《MySQL 5.6 Reference Manual》的"CREATE INDEX"章节看起,他指出如果是字符串类型的字段,可以指定字符串前多少位创建索引键值,而且键值前缀是存在上限的,在CREATE TABLE、ALTER TABLE、CREATE INDEX语句中,对于非二进制的字符串类型(CHAR、VARCHAR、TEXT),前缀会按照字符个数计算,对二进制的字符串类型(BINARY、VARBINARY、BLOB),前缀会按照字节个数计算,因此,当对非二进制的字符串列明确前缀长度的时候,需要考虑多字节字符集的因素,

image.png

P.S. https://dev.mysql.com/doc/refman/5.6/en/create-index.html


前缀的长度限制,是和存储引擎相关的。如果用的是InnoDB,前缀上限是767字节,当启用innodb_large_prefix时,上限可以达到3072字节。如果用的是MyISAM,前缀上限是1000字节。NDB存储引擎,则根本就不支持前缀这种形式。


之所以可以定义一个字段前缀作为键值,存储效率是考虑的一个因素,如果列名的前10个字符通常都是不同的,检索这10个字符创建的索引应该会比检索整个列作为索引的效率更高,使用列前缀作为索引会让索引树更小,不仅节省空间,还可能提高插入操作的速度。


再看一下《MySQL 5.7 Reference Manual》,相同章节中,多了这段描述,是说当使用CREATE INDEX时,如果指定的索引前缀长度超过了列定义的长度上限,则会出现以下两种场景,

  • 非唯一索引,如果设置innodb_strict_mode=on,该操作就会抛出一个错误,禁止执行,如果设置innodb_strict_mode=off,则索引会自动按照列定义的长度上限进行创建,只会提示一个warning。
  • 唯一索引,无论设置innodb_strict_mode与否,都会提示错误,禁止执行,因为这可能导致非唯一的值插入的到表中,违反唯一性约束。

image.gif

P.S. https://dev.mysql.com/doc/refman/5.7/en/create-index.html


《MySQL 8.0 Reference Manual》的内容和5.7相同,不再展示。这个问题在5.6上测,innodb_strict_mode=off,依然会提示错误,说明在5.7以上,对这个问题的容忍度降低了,

create table t1(id varchar(10));
alter table t1 add index idx_t1_01 (id(15));
SQL 错误 [1089] [HY000]: Incorrect prefix key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique prefix keys


再回到开始问题,一个库创建索引成功了,一个失败了,首先看下两个库的版本,确实不同,成功的是5.7,失败的是5.6.22。


我们先来看下5.7,开启了innodb_large_prefix,Row_format是Dynamic,表定义的字符集utf8,因为要加索引的字段定义是varchar(500),允许存储500个字符,utf8的一个字符是3个字节,500个字符就是1500个字节,从文档我们知道,因为设置了innodb_large_prefix,所以键值上限是3072个字节,1500<3072,加索引的操作,能正常执行,

mysql> show variables like '%innodb_large_prefix%';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| innodb_large_prefix | ON    |
+---------------------+-------+
1 row in set (0.01 sec)
Row_format: Dynamic
CHARSET=utf8


再来看5.6,小版本号是5.6.22,未开启innodb_large_prefixRow_format是Compact,表定义的字符集utf8,因为要加索引的字段定义是varchar(500),允许存储500个字符,utf8的一个字符是3个字节,500个字符就是1500个字节,从文档我们知道,未设置innodb_large_prefix所以键值上限是767个字节,1500>767,索引的操作,不能执行,

mysql> select version();
+------------+
| version()  |
+------------+
| 5.6.22-log |
+------------+
1 row in set (0.00 sec)
mysql> show variables like '%innodb_large_prefix%';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| innodb_large_prefix | OFF    |
+---------------------+-------+
1 row in set (0.01 sec)
Row_format: Compact
CHARSET=utf8


但是更奇怪的,碰巧我在5.6.44小版本进行测试,这个和5.6.22相同的操作过程,竟然能执行,只是提示了warning,

mysql> select version();
+------------+
| version()  |
+------------+
| 5.6.44-log |
+------------+
1 row in set (0.00 sec)
mysql> create table t(id varchar(500))
Query OK, 0 rows affected (0.08 sec)
mysql> alter table t add index (id);
Query OK, 0 rows affected, 1 warning (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 1
mysql> show warnings;
+---------+------+---------------------------------------------------------+
| Level   | Code | Message                                                 |
+---------+------+---------------------------------------------------------+
| Warning | 1071 | Specified key was too long; max key length is 767 bytes |
+---------+------+---------------------------------------------------------+
1 row in set (0.00 sec)


但是通过客户端,能看到这个索引键的长度,限制为了255,按照计算,255*3=765<767,这个应该是utf8字符集能支持字符个数上限,

image.gifimage.png

P.S. 请教一下,如果从命令行,看索引键值长度,应该执行什么?


说明5.6.44对超过索引键值上限的情况,允许增加索引,但是会自动截取。

在5.6.22和5.7对超过索引键值上限的情况,直接禁止执行。


个人理解,对待这种超过索引键值上限的情况,禁止执行,是合理的,因为如果自动对索引前缀进行截取,很可能出现截取的部分无法进行区分,不能起到过滤效果了,即使设置了innodb_strict_mode,都是只提示warning,不是错误error,因此很可能5.6.44对这个场景的支持是个bug,或者以后的版本,关闭了这个特性。


如果这个问题在5.6.22下要执行成功,可能有几种方案,

1. 缩小字段长度,例如x varchar(500),改为x varchar(255)。

2. 创建索引的时候,指定前缀长度,alter table ... add index ... (x(255)),能不能这么做,需要根据字段内容来决定。

3. 开启配置innodb_large_prefix,innodb_file_format改为Barracuda,row_format改为Dynamic,限制就从767改为了3072。

P.S. 这几个参数都是全局改的,能不能改,有什么影响,可能还得评估下。

mysql> set global innodb_large_prefix=on;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%prefix%';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| innodb_large_prefix | ON    |
+---------------------+-------+
1 row in set (0.00 sec)
mysql> set global innodb_file_format=Barracuda;
Query OK, 0 rows affected (0.01 sec)
mysql> show variables like '%innodb_file_format%';
+--------------------------+-----------+
| Variable_name            | Value     |
+--------------------------+-----------+
| innodb_file_format       | Barracuda |
| innodb_file_format_check | ON        |
| innodb_file_format_max   | Antelope  |
+--------------------------+-----------+
3 rows in set (0.00 sec)
mysql> alter table t1 row_format=dynamic;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> alter table t1 add index idx_t1_01(id);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0


对这个问题,从另一个角度,即使是767字节限制,换算成utf8,或者utf8mb4,至少是255或者191个字符的长度,一个单键值索引,如果达到这长度,就得考虑下索引字段的选择是否合理了,当然如果是几个字段组成的复合索引,达到这个长度,合理不合理,就得实际评估了。


MySQL刚开始接触,从这个案例,能体会到他的参数很多很碎,而且环环相扣,这真是得靠经验积累了。

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
打赏
0
0
0
0
1
分享
相关文章
MySQL索引学习笔记
本文深入探讨了MySQL数据库中慢查询分析的关键概念和技术手段。
311 80
MySQL底层概述—8.JOIN排序索引优化
本文主要介绍了MySQL中几种关键的优化技术和概念,包括Join算法原理、IN和EXISTS函数的使用场景、索引排序与额外排序(Using filesort)的区别及优化方法、以及单表和多表查询的索引优化策略。
MySQL底层概述—8.JOIN排序索引优化
MySQL原理简介—9.MySQL索引原理
本文详细介绍了MySQL索引的设计与使用原则,涵盖磁盘数据页的存储结构、页分裂机制、主键索引设计及查询过程、聚簇索引和二级索引的原理、B+树索引的维护、联合索引的使用规则、SQL排序和分组时如何利用索引、回表查询对性能的影响以及索引覆盖的概念。此外还讨论了索引设计的案例,包括如何处理where筛选和order by排序之间的冲突、低基数字段的处理方式、范围查询字段的位置安排,以及通过辅助索引来优化特定查询场景。总结了设计索引的原则,如尽量包含where、order by、group by中的字段,选择离散度高的字段作为索引,限制索引数量,并针对频繁查询的低基数字段进行特殊处理等。
MySQL原理简介—9.MySQL索引原理
MySQL底层概述—6.索引原理
本文详细回顾了:索引原理、二叉查找树、平衡二叉树(AVL树)、红黑树、B-Tree、B+Tree、Hash索引、聚簇索引与非聚簇索引。
MySQL底层概述—6.索引原理
MySQL秘籍之索引与查询优化实战指南
最左前缀原则。不冗余原则。最大选择性原则。所谓前缀索引,说白了就是对文本的前几个字符建立索引(具体是几个字符在建立索引时去指定),比如以产品名称的前 10 位来建索引,这样建立起来的索引更小,查询效率更快!
130 22
 MySQL秘籍之索引与查询优化实战指南
浅入浅出——MySQL索引
本文介绍了数据库索引的概念和各种索引结构,如哈希表、B+树、InnoDB引擎的索引运作原理等。还分享了覆盖索引、联合索引、最左前缀原则等优化技巧,以及如何避免索引误用,提高数据库性能。
【MySQL基础篇】全面学习总结SQL语法、DataGrip安装教程
本文详细介绍了MySQL中的SQL语法,包括数据定义(DDL)、数据操作(DML)、数据查询(DQL)和数据控制(DCL)四个主要部分。内容涵盖了创建、修改和删除数据库、表以及表字段的操作,以及通过图形化工具DataGrip进行数据库管理和查询。此外,还讲解了数据的增、删、改、查操作,以及查询语句的条件、聚合函数、分组、排序和分页等知识点。
【MySQL基础篇】全面学习总结SQL语法、DataGrip安装教程
MySQL中为什么要使用索引合并(Index Merge)?
通过这些内容的详细介绍和实际案例分析,希望能帮助您深入理解索引合并及其在MySQL中的
192 10
索引在手,查询无忧:MySQL索引简介
MySQL 是一款广泛使用的关系型数据库管理系统,在2024年5月的DB-Engines排名中得分1084,仅次于Oracle。本文介绍MySQL索引的工作原理和类型,包括B+Tree、Hash、Full-text索引,以及主键、唯一、普通索引等,帮助开发者优化查询性能。索引类似于图书馆的分类系统,能快速定位数据行,极大提高检索效率。
92 8
【MYSQL】 ——索引(B树B+树)、设计栈
索引的特点,使用场景,操作,底层结构,B树B+树,MYSQL设计栈