查询（五）索引长度和索引区分度

港控/mmm° 2022-12-30 13:36 101阅读 0赞

一、索引长度：在SQL执行计划中，key\_len 表示索引长度，经常用于判断复合索引是否被完全使用。

注：在utf8编码方式下，一个字符占3个字节；utf8mb4一个字符占4个字节；gbk中一个字符占2个字节；latin中一个字符占1个字节。索引长度可以指定，不指定的情况下会按照规则使用默认的长度。

1、默认索引长度定义：在没有指定索引长度的情况下，如果索引字段不为空且长度不可变，索引长度等于该字段的长度；可以为null，mysql会用1个字节标识；长度可变，MySQL会使用2个字节标识。以utf8编码为例

（1）如果索引字段不为空且长度不可变，索引长度等于该字段的长度；

CREATE TABLE `test` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` char(20) NOT NULL,
      PRIMARY KEY (`id`),
      KEY `index_name` (`name`)
    ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

随便插入几条数据：

![20201225171517719.png][]

索引查询： key\_len = 60，索引段name的长度是20个字符，key\_len = 20\*3 = 60。

![20201225171534644.png][]

（2）如果索引字段不为空，长度可变：改成varchar：

![2020122517184788.png][]

查询：key\_len = 20\*3 +2= 62

![20201225171919531.png][]

（3）如果索引字段可以为空，长度不可变：

![20201225172020472.png][]

查询：key\_len = 20\*3 +1= 61

![2020122517203699.png][]

（4）以此类推，字段既可以为空长度也可变，索引长度+2+1：

![2020122517213217.png][]

查询：

![20201225172202345.png][]

（5）复合索引：索引列长度之和

CREATE TABLE `test` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(20) DEFAULT NULL,
      `address` varchar(20) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `index_name_address` (`name`,`address`) USING BTREE
    ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

![20201225172539314.png][]

key\_len长度为63，说明只用到了复合索引的前半部分；

![20201225172633450.png][]

key\_len长度为126，说明该SQL查询语句用了整个复合索引。

综上，key\_len 表示索引长度，经常用于判断复合索引是否被完全使用。

2、指定索引长度：

CREATE INDEX index_name ON table_name column_name(length)；

如对于表

![20201225174702652.png][]

设置索引长度：

ALTER TABLE test ADD INDEX index_name_address (`name`(10),`address`(10));

![20201225174728642.png][]

key\_len = 10\*3+2+1+10\*3+2+1=66。

二、索引区分度：区分度百分比 = select count(distinct left(索引字段,索引长度))/count(1) from table。区分度越高，查询越快，如主键索引，主键是唯一的，主键索引的区分度就是1。区分度低的索引原则上已失去意义，没有明显的查询效率，而且添加了索引每次查询会先走索引树，再回表查询，增加了额外的io消耗，就不如直接查询原表来的效率高

三、总结：索引长度和区分度是相互矛盾的，索引长度太短，那么区分度就很低，把索引长度加长，区分度就高，但是索引也是要占内存的，所以我们需要找到一个平衡点。

举个例子：（张，张三，张三哥），如果索引长度取1的话，那么每一行的索引都是 张 这个字，完全没有区分度，无法排序，结果这样三行完全是随机排的，因为索引都一样；如果长度取2，那么排序的时候至少前两个是排对了的，如果取3，区分度达到100%，排序完全正确；但是并不是索引越长越好，比如 (张,李,王) 和 （张三啦啦啦，张三呵呵呵，张三呼呼呼）；前者在内存中排序占得空间少，排序也快，后者明显更慢更占内存，在大数据应用中这一点点影响都是很大的。

[20201225171517719.png]: /images/20221120/c0a5a8bb40cc4b3ab767489dc7c6e2d3.png
[20201225171534644.png]: /images/20221120/4b37100e84694a50b3b3354997727649.png
[2020122517184788.png]: /images/20221120/dd37a64986f44ef0b5712cedabe64dd6.png
[20201225171919531.png]: /images/20221120/788f8772360346149cae287b4fdc5de0.png
[20201225172020472.png]: /images/20221120/f271af5c997546cfbe9a59ecd2066053.png
[2020122517203699.png]: /images/20221120/7c867cb41ba44579a38f94b984b1ddff.png
[2020122517213217.png]: /images/20221120/a0eacdc97c9e48ad9a4f6944e4d864f7.png
[20201225172202345.png]: /images/20221120/baf7119baae34efdbac099e8b47899f3.png
[20201225172539314.png]: /images/20221120/737e847d70964f91bd8852c731bd6044.png
[20201225172633450.png]: /images/20221120/08cf0a44f2a34af0891e7fcebcbb83d3.png
[20201225174702652.png]: https://img-blog.csdnimg.cn/20201225174702652.png
[20201225174728642.png]: https://img-blog.csdnimg.cn/20201225174728642.png