小白学习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),前缀会按照字节个数计算,因此,当对非二进制的字符串列明确前缀长度的时候,需要考虑多字节字符集的因素,

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与否,都会提示错误,禁止执行,因为这可能导致非唯一的值插入的到表中,违反唯一性约束。

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_prefix,Row_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字符集能支持字符个数上限,

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

小白学习MySQL,

小白学习MySQL - MySQL会不会受到“高水位”的影响?

小白学习MySQL - 数据库软件和初始化安装

小白学习MySQL - 闲聊聊

近期更新的文章:

积累一些SQL

创建主键的三种方式对指定索引表空间操作的纠正

Oracle优化器的“短路”

MySQL行转列的小需求

Oracle的greatest和least函数

我的股市生涯

非Oracle Linux下Oracle 19c CDB数据库安装

Redis和Sentinel的安装部署和配置

“火线”和“零线”

通过索引提升SQL性能案例一则

如何手动添加jar包到maven本地库?

1元股权转让的一点思考

如何打造一个经常宕机的业务系统?

Linux恢复误删文件的操作

Linux的scp指令使用场景

Oracle处理IN的几种方式

如何搭建一支拖垮公司的技术团队?

IP地址解析的规则

MySQL的skip-grant-tables

国产数据库不平凡的一年

Oracle要求顺序的top数据检索问题

日常工作中碰到的几个技术问题

了解一下sqlhc

Oracle的MD5函数介绍

Oracle 19c的examples静默安装

sqlplus登录缓慢的解决

VMWare 11安装RedHat Linux 7过程中碰到的坑

COST值相同?是真是假?

Oracle 11g的examples静默安装

文章分类和索引:

公众号700篇文章分类和索引

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值