【HBZ分享】Mysql索引的失效场景以及创建索引失败报错的原因

hbz-

已于 2024-08-13 22:26:55 修改

阅读量332

点赞数 3

文章标签： mysql 数据库

于 2024-08-13 06:04:06 首次发布

本文链接：https://blog.csdn.net/a645293829/article/details/141144176

版权

如何查看mysql某个表具有的索引

show INDEX from [表名]

创建索引失败的场景及原因

假设有一张表如下: 使用utf8mb4的字符编码

CREATE TABLE `api_case` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT 'API用例名称',
  `description` varchar(2048) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL COMMENT 'API用例描述',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=128 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

我们设置一个name + description的联合索引
执行【CREATE INDEX idx_name_description on api_case(name, description)】
执行上面语句会发生报错，即索引创建失败：Specified key was too long; max key length is 3072 bytes。这个表示联合索引中，存在某列的长度达到了3072个bytes，当然这里说的就是description字段的长度
混淆：这个3072个字节指的是varchar(2048)的这个2048个字符转换成bytes单位的长度后超过了3072个字符，并不是说索引名字长度是3072个字节，这块别整错了。
2048是如何转换的，为什么转换成bytes单位后超过了767呢？
- 首先innodb引擎的索引中的字段要求最大长度为【767】个字节, myisam的最大长度【1000】bytes，这是默认，当然可以更改，但不建议，更改后会影响索引查询效率。
- 从mysql5.x版本之后，varchar(2048)的这个2048长度指的是字符个数，而不是字节个数
- 我们使用的是utf8mb4字符编码，所以【1个字符 = 4个byte】。如果使用的是utf8mb3则【1个字符=3个byte】
- 我们把2048转换成bytes就是2048 * 3 = 6144个byte，所以6144 > 3072了，所以报错了。
- 正确的范围应该是 767 / 3 = 255个字符以内，即varchar(255)已经是极限了，不能再多了

mysql各类型所占的字节数: 注意这些int家族的就是固定长度，哪怕你写int(10)那占的长度也是4字节

类型	所占字节数
char(n)	n字节
varchar(n)	3n +2 , 这个2是保存字符串长度所需要的字节数是2个
tinyint	1字节
int	4字节
bigInt	8字节
date	3字节
dateTime	8字节
timeStamp	4字节

索引长度key_len的计算方式

表如下：

CREATE TABLE `api_case` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL COMMENT 'API用例名称',
  `description` varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL COMMENT 'API用例描述',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=128 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

# 使用的sql-1
select * from api_case where name='hhh' and descrpition='ss';
# 使用的sql-2
select * from api_case where name='hhh'';

上面sql-1会命中联合索引，则长度是key_len = 20 * 3 +2 + 1 + 100 * 3 + 2 + 1 = 366
计算方式： 20 * 3表示转换成字节个数，2表示记录字符串长度需要2个字符的位置， 1表示记录是否为NULL的这个标识占1字节(当该字段NOT NULL的时候这1个字节就省了。) 然后把所有联合索引中的字段长度相加，就是总索引所占的长度
上面sql-1会命中联合索引，则长度是key_len = 20 * 3 +2 + 1 = 63
计算方式：计算方式都一样，只不过因为只命中了联合索引的name字段，所以只会计算name字段长度，description不会参与计算

如何强制使用指定的索引？通常情况下会让优化器自行选择

# 使用 force index(索引名称)
select * from api_case force index(idx_name) where name='hhh' and descrpition='ss';

误区：int(10) 和 int(4) 和 int(1) 有什么区别

结论：除非给字段加了zerofill，否则没任何区别，int(1) 同样可以存100
不要和varchar(10)和varchar(100)搞混了，varchar这个确实是限制字符长度，但int(10), int(1)可不是
加了zerofile会是啥样? 比如存了10 ,则int(1)就会展示10，而int(4)展示的是0010，索引int(4)这个4表示在有zerofill的情况下，要显示出多少位，要添0补到这个位数，int(10)就是0000000010，并不是说int(1)只能0-9，而int(4)只能0000-9999
切记：int家族的int(1), int(10), int(100)并不是说将该字段长度范围限制在1位，10位还是100位数，而是说使用zerofill后，不足这么多位的会添0补充上，他们能存粗的数字都是 2^32 -1 将近40亿

【联合索引】的失效场景(联合索引前提: name, age,sex 按顺序组成联合索引)

联合索引没遵循【最左匹配原则】，比如name, age, sex3个字段按顺序组成联合索引，但where条件只有age = 18，最左边的name字段没有，那就不会生效，可以是【where age = 19 and name=‘hhh’】, 这种顺序反了不要紧，优化器会自动排序，但name必须要存在于where中。
使用【or】连接了，这时不会生效，哪怕第一个条件是name，使用了or就不会不生效。如果or左右两边的字段都有独立的索引，则可以生效(注意！！！：前提是数据量一定要大，前提是数据量一定要大，前提是数据量一定要大)

# 下面写法用or连接 并且 顺序还不对的不会生效
select * from api_case where age=19 or name='hhh';

部分失效: 跳过中间字段age，则右边sex不生效，当where条件有联合索引的第一个字段name和第三个字段sex时，第二个字段age不存在where条件里，则只会生效第一个字段name的索引，而sex不会生效 (联合索引顺序: name, age, sex)

#  索引只会生效name, sex不会生效，因为联合索引第二个字段age不存在
select * from api_case where name='hhh' and sex = '女';

部分失效：存在范围查询，则本身和左边生效，右边不生效，请看下面示例：

# name， age生效，但sex不生效
# 看似都在联合索引之内，但age是范围，所以根据范围字段本身和左边生效，右边不生效，所以name, age是生效的，但sex不生效
select * from api_case where name = 'hhh' and age > 20 and sex='女';

当加索引的字段重复率非常高的情况，比如name加了索引，但表中name的值100条数据有99条是hhh，只有1条是aaa，则where name = ‘hhh’ 也不会走索引，因为优化器认为不需要走索引了，因为和全表扫描基本一样了。但where name = 'aaa’那就会走索引

踩坑：神奇的现象or连接

场景：即使or左右两边条件都设置了各自独立的索引，但是发现依然走了全表扫描，没走索引，这是什么原因呢？
答： mysql优化器会进行判断，是走全表扫描快，还是走索引快，如果走全表扫描更快，则即使有索引那也不会走的。所以我们上面有个特别注意点：就是数据量一定要大！！！，如果全表就几条数据，那即使建立了索引，也不会走的。