key_len

参考老叶:

10分钟让你明白MySQL是如何利用索引的

https://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ==&mid=506446994&idx=1&sn=b994ef354b8241c04e8cca2ff0527ed8&chksm=3d3b5ef80a4cd7ee68bfe6845679b6defcf470f8d5ee33fe1b994d69ebc2a4bc03114d179e65&mpshare=1&scene=1&srcid=#rd

 

关于字符转换:

对于int 类型: status =1 和 status='1' 效果等同,都可以利用索引

对于字符类型: status='1' 可以利用索引,status=1 不能利用索引

 

EXPLAIN执行计划中有一列 key_len 用于表示本次查询中，所选择的索引长度有多少字节，通常我们可借此判断联合索引有多少列被选择了。

在这里 key_len 大小的计算规则是：

• 一般地，key_len 等于索引列类型字节长度，例如int类型为4 bytes，bigint为8 bytes；

• 如果是字符串类型，还需要同时考虑字符集因素，例如：CHAR(30) UTF8则key_len至少是90 bytes；

• 若该列类型定义时允许NULL，其key_len还需要再加 1 bytes；

• 若该列类型为变长类型，例如 VARCHAR（TEXT\BLOB不允许整列创建索引，如果创建部分索引也被视为动态列类型），其key_len还需要再加 2 bytes;

 

图中一共分了三个部分：

1. Index Key ：MySQL是用来确定扫描的数据范围，实际就是可以利用到的MySQL索引部分，体现在Key Length。

2. Index Filter：MySQL用来确定哪些数据是可以用索引去过滤，在启用ICP后，可以用上索引的部分。

3. Table Filter：MySQL无法用索引过滤，回表取回行数据后，到server层进行数据过滤。

我们细细展开。

 

数字类型
根据字节数即可算出表示的范围了 
TINYINT                                     1 字节 
SMALLINT                                    2 个字节 
MEDIUMINT                                   3 个字节 
INT                                         4 个字节 
INTEGER                                     4 个字节 
BIGINT                                      8 个字节 
FLOAT                                       4 个字节 
DOUBLE                                      8 个字节 
DOUBLE PRECISION                            8 个字节 
REAL                                        8 个字节 
DECIMAL(M,D)                                M字节(D+2 , 如果M < D) 
NUMERIC(M,D)                                M字节(D+2 , 如果M < D)

日期和时间类型
DATE                                        3 个字节 
DATETIME                                    8 个字节 
TIMESTAMP                                   4 个字节 
TIME                                        3 个字节 
YEAR                                        1 字节

字符串类型
CHAR(M)                                     M字节，1 <= M <= 255 
VARCHAR(L)                                  L+1 字节, 在此L <= M和1 <= M <= 255 
TINYBLOB, TINYTEXT                          L+1 字节, 在此L< 2 ^ 8 
BLOB, TEXT                                  L+2 字节, 在此L< 2 ^ 16 
MEDIUMBLOB, MEDIUMTEXT                      L+3 字节, 在此L< 2 ^ 24 
LONGBLOB, LONGTEXT                          L+4 字节, 在此L< 2 ^ 32 

 

Index Key

Index Key是用来确定MySQL的一个扫描范围，分为上边界和下边界。

MySQL利用=、>=、> 来确定下边界（first key），利用最左原则，首先判断第一个索引键值在where条件中是否存在，如果存在，则判断比较符号，如果为(=,>=)中的一种，加入下边界的界定，然后继续判断下一个索引键，如果存在且是(>)，则将该键值加入到下边界的界定，停止匹配下一个索引键；如果不存在，直接停止下边界匹配。

exp:
idx_c1_c2_c3(c1,c2,c3)
where c1>=1 and c2>2 and c3=1
--> first key (c1,c2)
--> c1为 '>=' ，加入下边界界定，继续匹配下一个
--> c2 为 '>'，加入下边界界定，停止匹配

上边界（last key）和下边界（first key）类似，首先判断是否是否是(=,<=)中的一种，如果是，加入界定，继续下一个索引键值匹配，如果是(<)，加入界定，停止匹配。

exp:
idx_c1_c2_c3(c1,c2,c3)
where c1<=1 and c2=2 and c3<3
--> first key (c1,c2,c3)
--> c1为 '<='，加入上边界界定，继续匹配下一个
--> c2为 '='加入上边界界定，继续匹配下一个
--> c3 为 '<'，加入上边界界定，停止匹配

注：这里简单的记忆是，如果比较符号中包含'='号，'>='也是包含'='，那么该索引键是可以被利用的，可以继续匹配后面的索引键值；如果不存在'='，也就是'>','<'，这两个，后面的索引键值就无法匹配了。同时，上下边界是不可以混用的，哪个边界能利用索引的的键值多，就是最终能够利用索引键值的个数。

Index Filter

字面理解就是可以用索引去过滤。也就是字段在索引键值中，但是无法用去确定Index Key的部分。

exp:
idex_c1_c2_c3
where c1>=1 and c2<=2 and c3 =1
index key --> c1
index filter--> c2 c3

这里为什么index filter 只是c1呢？因为c2 是用来确定上边界的，但是上边界的c1没有出现(<=,=)，而下边界中，c1是>=,c2没有出现，因此index key 只有c1字段。c2,c3 都出现在索引中，被当做index filter。

Table Filter

无法利用索引完成过滤，就只能用table filter。此时引擎层会将行数据返回到server层，然后server层进行table filter。