八、索引区分度、索引长度
索引长度越长,索引区分度越高。索引长度越短,索引区分度越低。区分度越高,意味着排序越快,或者说本身就是按照大概率正确的顺序排好的。但索引长度越长,也意味着相同空间内能容纳的数目越少,所以需要找到一个平衡点。
(一) 索引区分度
索引区分度即是否能够区分不同索引,或者说是不同索引的数目。如用户表有三条数据,其中mobile字段分别为"110"、“119”、“120”,如果以前一个字符为前缀索引,则只会有"1"这一个索引,没有索引区分度,该三条记录的排序将会是乱序。如果取前两个字符,则有"11"、"12"这两种索引,则"120"会排在最后,"110"和"119"则可能会乱序。如果取三个字符,则索引区分度可以100%,三条记录的排序则会完全正确。
(二) 索引长度
索引长度的单位是字节,对于不同版本、不同引擎、不同编码方式以及不同类型的索引长度是不同的。
因为工作原因,本文只讨论MySQL8 + utf8mb4编码 + InnoDB引擎的索引情况。
(1) 索引长度上限
网上很多文章有关于索引长度上限是多少,但都是MySQL5.*版本的,不适用于MySQL8.*版本,包括参数变量也在8版本中被移除了。MySQL8.*索引长度上限官网地址
MySQL8.*的索引长度上限由建表时候的ROW_FORMAT(缺省值是Dynamic)决定。REDUNDANT和COMPACT的上限为767字节,DYNAMIC和COMPRESSED上限为3072字节。如下图:
(2) 索引长度计算
以下计算基于utf8mb4编码,且默认为非null情况。如果字段允许为null,则以下的索引长度还要各加上1的长度。
类型 | 索引长度 |
---|---|
int | 固定为4 |
bitint | 固定为8 |
char(n) | n * 4 |
varchar(n) | n * 4 + 2 因为varchar是变长,需要额外占用1-2个字节来存储字段长度信息 |
--------------------------------------------: | |
int | 固定为4 |
bitint | 固定为8 |
char(n) | n * 4 |
varchar(n) | n * 4 + 2 因为varchar是变长,需要额外占用1-2个字节来存储字段长度信息 |
针对超出长度限制的字段建立索引的话,会报错,可以通过前缀索引的方式来避免。
explain查看sql执行计划的时候,key_len属性就是用到的索引的索引长度总长。