MySQL中计算索引长度

首先,我们来看一道题目,针对表t,包含了三个字段a、b、c,假设其默认值都非空,现创建组合索引index(a,b,c) 分析select * from t where a=1 and c=1 和select * from t where a=1 and b=1区别?

首先创建表


分别执行这两条语句


发现,两则区别主要是在于key_len上,为什么二则区别不一样呢?

我的理解是:

我们可以讲组合索引想成书的一级目录、二级目录、三级目录,如index(a,b,c),相当于a是一级目录,b是一级目录下的二级目录,c是二级目录下的三级目录。要使用某一目录,必须先使用其上级目录,除了一级目录除外。

所以

where a=1 and c=1只使用了一级目录,c在三级目录,没有使用二级目录,那么三级目录就没法使用

where a=1 and b=1只使用了一级目录、二级目录。

于是第二条查询的key_len更大。


但是,具体key_len怎么计算的,上面怎样计算出是4和8的呢?之前没怎么关注过。在通过explain分析SQL查询语句的性能的时候,之前,我更多关注的是select_type、type、possible_key、key、ref、rows、extra,这次,我觉得有必要弄清楚key_len的计算.

1.所有的索引字段,如果没有设置not null,则需要加一个字节。

2.定长字段,int占四个字节、date占三个字节、char(n)占n个字符。

3.对于变成字段varchar(n),则有n个字符+两个字节。
4.不同的字符集,一个字符占用的字节数不同。latin1编码的,一个字符占用一个字节,gbk编码的,一个字符占用两个字节,utf8编码的,一个字符占用三个字节。

因此可以得出

where a=1 and c=1而言,key_len=4

where a=1 and c=1而言,key_len=4+4=8


现在再来做一道题,创建一个t2表,数据结构如下


求执行explain select * from t2 where name="001" and id=1 \G;的key_len是多少呢?

分析key_len=4+5*1+2=11,因为字段都是not null,int类型4个字节,varchar(5) 占用5个字符+2个字节,latin1编码的表一个字符占1个字节,故varchar(5) 占用7个字节。结构如下图


补充

因为MySQL具有查询优化器,所以对where a=1 and c=1类型的查询,字段顺序没有任何影响,查询优化器会自动优化。where c=1 and a=1会被优化成where a=1 and c=1,但是建议还是使用where a=1 and c=1吧,便于理解以及查询缓冲。因为查询缓冲,hashkey值,是以sql语句来计算的,且区分大小写,所以在写SQL语句的时候,尽量保存一致,防止相同的查询被缓存多次。

<think>好的,我现在需要回答用户关于MySQL索引长度限制及最大值的问题。首先,我得仔细理解用户的问题。用户提到了几个引用资料,里面涉及不同版本MySQL索引长度限制,以及字符集的影响,还有如何修改设置来突破限制。我需要把这些信息整理清楚,确保回答准确。 首先,我要回忆一下MySQL不同版本对索引长度的处理。根据引用[1],MySQL 5.7默认启用了innodb_large_prefix,解除了767字节的限制,但单列索引最大还是3072字节。引用[2]提到在utf8mb4下每个字符占4字节,所以索引长度限制为191字符。这里需要说明不同字符集对索引长度的影响,比如utf8是3字节,latin1是1字节。然后,引用[3]补充了关于NULL和可变长度类型需要额外字节的信息。 接下来,我需要分点回答,可能包括限制说明、影响因素、最大值以及如何修改设置。要确保每个部分都有对应的引用支持。例如,在讲如何修改时,引用[2]提到了设置row_format为DYNAMIC或COMPRESSED,以及启用innodb_large_prefix。需要注意的是,不同版本可能有不同的默认设置,比如MySQL 5.7默认开启,而旧版本可能需要手动开启。 另外,用户可能想知道具体怎么计算索引长度,比如如何根据字符集计算最大允许的varchar长度。例如,对于utf8mb4,每个字符4字节,所以767/4=191.75,取整191字符。这时候需要引用资料中的例子来说明。 还要注意区分单列索引和组合索引的限制,以及行格式的影响。比如,当使用DYNAMIC行格式时,可以支持更大的索引长度。这部分需要结合引用[2]和引用[1]的信息。 最后,用户可能需要步骤性的指导,如何修改配置和表结构来适应更大的索引长度。这包括修改innodb_large_prefix,调整行格式,以及可能的字符集选择。 需要检查是否有遗漏点,例如NULL标志位和可变长度类型的额外字节,这些会影响实际可用长度。例如,如果字段可为NULL,会占用1字节,而varchar需要2字节存储长度信息,这样计算最大长度时要减去这些开销。例如,假设字段是varchar且可为NULL,那么可用字节数是767-1-2=764,再除以字符字节数得到最大字符数。 可能还需要提到不同存储引擎的限制,但用户的问题集中在InnoDB上,根据引用资料,所以可以专注于InnoDB的情况。 总结回答结构: 1. 索引长度限制的基本说明,包括不同版本的变化。 2. 影响索引长度的因素:字符集、NULL标志、可变长度类型。 3. 单列索引和组合索引的最大值。 4. 如何修改设置来突破限制。 5. 实际应用中的计算示例。 确保每个部分都有对应的引用,并且语言通俗易懂。最后生成相关问题,帮助用户进一步了解。</think>### MySQL索引长度限制及最大值解析 #### 一、索引长度限制说明 1. **默认限制** - 在MySQL 5.7之前,单列索引的最大长度为**767字节**(受限于`innodb_large_prefix=OFF`)[^1][^3]。 - MySQL 5.7及更高版本默认启用`innodb_large_prefix=ON`,单列索引最大长度提升至**3072字节**,但需满足行格式为`DYNAMIC`或`COMPRESSED`[^2]。 2. **组合索引限制** 组合索引的总长度不能超过**3072字节**,与单列索引的最大值一致。 #### 二、影响索引长度的关键因素 1. **字符集类型** - `utf8mb4`:每字符占4字节,最大支持`varchar(191)`(计算:$767 \div 4 \approx 191$)。 - `utf8`:每字符占3字节,最大支持`varchar(255)`(计算:$767 \div 3 \approx 255$)[^3]。 - `latin1`:每字符占1字节,最大支持`varchar(767)`。 2. **附加字节占用** - 若字段允许`NULL`,索引会额外占用**1字节**标识空值。 - 可变长度类型(如`varchar`)需额外**2字节**存储长度信息。 *示例*:`varchar(255) CHARACTER SET utf8mb4 NOT NULL`的索引长度为: $$ (255 \times 4) + 2 = 1022 \text{字节} $$ #### 三、如何突破默认限制? 1. **修改行格式** 将表行格式设置为`DYNAMIC`或`COMPRESSED`,并确保`innodb_large_prefix=ON`(MySQL 5.7默认开启)[^2]: ```sql ALTER TABLE 表名 ROW_FORMAT=DYNAMIC; ``` 2. **配置参数调整** - 在`my.cnf`中设置: ```ini innodb_large_prefix=ON innodb_file_format=Barracuda ``` #### 四、实际应用示例 若需为`varchar(500)`字段(`utf8mb4`编码)创建索引: - 所需字节数:$500 \times 4 + 2 = 2002 \text{字节}$ - 由于2002 < 3072,在MySQL 5.7+中允许创建该索引,但需确保表行格式为`DYNAMIC`[^2]。 --- ###
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值