【HBZ分享】Mysql索引的失效场景 以及 创建索引失败报错的原因
如何查看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优化器会进行判断,是走全表扫描快,还是走索引快,如果走全表扫描更快,则即使有索引那也不会走的。所以我们上面有个特别注意点:就是数据量一定要大!!!, 如果全表就几条数据,那即使建立了索引,也不会走的。