mysql 执行计划中key_len的计算规则

server/2024/11/25 7:41:21/

mysql_key_len_0">mysql 执行计划中key_len的计算规则

在查看mysql执行计划时，对执行计划中key_len字段计算规则不是很清楚，所以就做了如下资料整理。

+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t2    | NULL       | ref  | idx_t2_name   | idx_t2_name | 6       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

key_len的含义

key_len列表示SQL使用的索引长度，单位为字节。key_len的值能够确定MySQL实际使用组合索引中索引字段是否被用到。如果key列为NULL，则key_len列也为NULL。

key_len计算规则

计算公式

key_len=索引列数据类型本身占用的空间+额外空间（如果索引列为空或者索引列是变长类型计算key_len需要加上额外空间）

当索引为组合索引时，key_len为多个字段累加和。

计算规则

  1. 在计算key_len时，常见类型占用的字节数
    数值类型占用字节数列表
    数据类型占用字节数
    TINYINT1 byte
    SMALLINT2 bytes
    MEDIUMINT3 bytes
    INT, INTEGER4 bytes
    BIGINT8 bytes
    FLOAT§4 bytes if 0 <= p <= 24, 8 bytes if 25 <= p <= 53
    FLOAT4 bytes
    DOUBLE [PRECISION],REAL8 bytes
    DECIMAL(M,D), NUMERIC(M,D)下面详细介绍
    BIT(M)近似 (M+7)/8 bytes

    我们以decimal介绍， decimal类型本身所占字节数的计算方式比较复杂

    DECIMAL(M, D) M指的是总的位数，D指的就是小数位数。M的范围是1~ 65，D的范围是0~ 30，且D的值不能超过M。

    定点数是精确的小数，为了达到精确的目的我们不能把它转换成二进制之后再存储(这可能会产生四舍五入的情况)。Mysql把一个小数分成3块来存储：

    小数点左边的整数，小数点，以及小数点右边的小数。小数点左边及右边的整数以9位数为1组，用4个字节保存。

    如果小数点前后的整数低于9位数，需要的字节数如下：

    位数占用的字节数
    00
    11
    21
    32
    42
    53
    63
    74
    84

    例如：

    decimal(22,6) ： 小数点左边16位，小数点右边6位，小数点左边需要4+4个字节，小数点右边6位需要3个字节，一共需要11个字节；

    decimal(19,8) ：小数点左边11位，小数点右边8位，小数点左边需要4+1个字节，小数点右边需要4个字节，一共需要9个字节；

    --创建测试表
create table test_int (id int not null,id2 bigint not null,id3  float(6) not null,id4  float(30) not null,id5  double not null,id6  decimal(19,8) not null,id7  bit(10) not null);
insert into test_int values(1,23625555,32123.345,2322222.342333333322,5948473.321,234.1234,b'1000001110');create index idx_test_int_id on test_int(id);
create index idx_test_int_id2 on test_int(id2);
create index idx_test_int_id3 on test_int(id3);
create index idx_test_int_id4 on test_int(id4);
create index idx_test_int_id5 on test_int(id5);
create index idx_test_int_id6 on test_int(id6);
create index idx_test_int_id7 on test_int(id7);
explain select * from test_int where id = 1;
--索引字段为int，key_len值为4
mysql> explain select * from test_int where id = 1;
+----+-------------+----------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys   | key             | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test_int | NULL       | ref  | idx_test_int_id | idx_test_int_id | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)
--索引字段为bigint，key_len值为8
mysql>  explain select * from test_int where id2 = 1;
+----+-------------+----------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys    | key              | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test_int | NULL       | ref  | idx_test_int_id2 | idx_test_int_id2 | 8       | const |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
--索引字段为float(6)，key_len值为4mysql>  explain select * from test_int where id3 = 1;
+----+-------------+----------+------------+------+------------------+------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table    | partitions | type | possible_keys    | key              | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+----------+------------+------+------------------+------------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | test_int | NULL       | ref  | idx_test_int_id3 | idx_test_int_id3 | 4       | const |    1 |   100.00 | Using index condition |
+----+-------------+----------+------------+------+------------------+------------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
--索引字段为float(30)，key_len值为8mysql>  explain select * from test_int where id4 = 1;
+----+-------------+----------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys    | key              | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test_int | NULL       | ref  | idx_test_int_id4 | idx_test_int_id4 | 8       | const |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
--索引字段为double，key_len值为8
mysql>  explain select * from test_int where id5 = 1;
+----+-------------+----------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys    | key              | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test_int | NULL       | ref  | idx_test_int_id5 | idx_test_int_id5 | 8       | const |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
--索引字段为decimal(19,8)，key_len值为9，key_len=(4+1)(11整数位，分为9位整数用4个字节，剩余2位用1个字节及4+1)+4(8个小数位用4个字节)
mysql>  explain select * from test_int where id6 = 1;
+----+-------------+----------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys    | key              | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test_int | NULL       | ref  | idx_test_int_id6 | idx_test_int_id6 | 9       | const |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
--索引字段为big，根据公式计算 (10+7)/8 bytes近似值为2，执行计划中key_len值为2,
mysql>  explain select * from test_int where id7 = b'1000001110';
+----+-------------+----------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys    | key              | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test_int | NULL       | ref  | idx_test_int_id7 | idx_test_int_id7 | 2       | const |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
    日期和时间类型占用字节数列表

    对于TIME, DATETIME和TIMESTAMP列，MySQL 5.6.4之前创建的表所需的存储空间与5.6.4以后创建的表不同。这是由于5.6.4中的功能更新，允许这些类型具有小数部分，这需要从0到3个字节。

    数据类型MySQL 5.6.4之前的存储要求MySQL 5.6.4之后的存储要求
    YEAR1 byte1 byte
    DATE3 bytes3 bytes
    TIME3 bytes3 bytes +小数秒存储
    DATETIME8 bytes5 bytes +小数秒存储
    TIMESTAMP4 bytes4 bytes +小数秒存储

    MySQL5.6.4及之后版本，TIME、DATETIME、TIMESTAMP这几种类型添加了对毫秒、微妙的支持。由于毫秒、微秒都不到1秒，所以也被称为小数秒，MySQL最多支持6位小数秒的精度，比如DATETIME(0)表示精确到秒，DATETIME(3)表示精确到毫秒，DATETIME(5)表示精确到微秒。所以如果你在使用TIME、DATETIME、TIMESTAMP这几种类型的时候精确到了小数秒，那么需要额外的存储空间，不同的小数秒精度需要的存储空间不同，如下表：

    小数秒精度存储要求
    00 bytes
    1, 21 byte
    3, 42 bytes
    5, 63 bytes
    --创建测试表
create table test_date (dt date not null,dt2 time not null,dt3 time(1) not null,dt4 datetime(3) not null,dt5 timestamp(5) not null);
insert into test_date values(now(),CURRENT_TIMESTAMP,CURRENT_TIMESTAMP(1),CURRENT_TIMESTAMP(3),CURRENT_TIMESTAMP(5));
--创建索引
create index idx_test_date_dt on test_date(dt);
create index idx_test_date_dt2 on test_date(dt2);
create index idx_test_date_dt3 on test_date(dt3);
create index idx_test_date_dt4 on test_date(dt4);
create index idx_test_date_dt5 on test_date(dt5);
--索引字段为date，key_len值为3
mysql> explain select * from test_date where dt < '2024-01-02 17:10:38';
+----+-------------+-----------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+
| id | select_type | table     | partitions | type  | possible_keys    | key              | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-----------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | test_date | NULL       | range | idx_test_date_dt | idx_test_date_dt | 3       | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-----------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
--索引字段为time，key_len值为3mysql> explain select * from test_date where dt2 < '2024-01-02 17:10:38';
+----+-------------+-----------+------------+-------+-------------------+-------------------+---------+------+------+----------+-----------------------+
| id | select_type | table     | partitions | type  | possible_keys     | key               | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-----------+------------+-------+-------------------+-------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | test_date | NULL       | range | idx_test_date_dt2 | idx_test_date_dt2 | 3       | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-----------+------------+-------+-------------------+-------------------+---------+------+------+----------+-----------------------+
1 row in set, 2 warnings (0.00 sec)
--索引字段为time(1)，key_len值为4及3+1
mysql> explain select * from test_date where dt3 < '2024-01-02 17:10:38';
+----+-------------+-----------+------------+-------+-------------------+-------------------+---------+------+------+----------+-----------------------+
| id | select_type | table     | partitions | type  | possible_keys     | key               | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-----------+------------+-------+-------------------+-------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | test_date | NULL       | range | idx_test_date_dt3 | idx_test_date_dt3 | 4       | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-----------+------------+-------+-------------------+-------------------+---------+------+------+----------+-----------------------+
1 row in set, 2 warnings (0.00 sec)
--索引字段为datetime(3)，key_len值为7及5+2
mysql> explain select * from test_date where dt4 < '2024-01-02 17:10:38';
+----+-------------+-----------+------------+-------+-------------------+-------------------+---------+------+------+----------+-----------------------+
| id | select_type | table     | partitions | type  | possible_keys     | key               | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-----------+------------+-------+-------------------+-------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | test_date | NULL       | range | idx_test_date_dt4 | idx_test_date_dt4 | 7       | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-----------+------------+-------+-------------------+-------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.01 sec)
--索引字段为timestamp(5)，key_len值为7及4+3
mysql> explain select * from test_date where dt5 < '2024-01-02 17:10:38';
+----+-------------+-----------+------------+-------+-------------------+-------------------+---------+------+------+----------+-----------------------+
| id | select_type | table     | partitions | type  | possible_keys     | key               | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-----------+------------+-------+-------------------+-------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | test_date | NULL       | range | idx_test_date_dt5 | idx_test_date_dt5 | 7       | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-----------+------------+-------+-------------------+-------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)explain select * from test_date where dt5 < '2024-01-02 17:10:38';
    字符类型占用字节数列表

    在下表中，M表示声明的列长度，对于非二进制字符串类型以字符为单位，对于二进制字符串类型以字节为单位。L表示给定字符串值的实际字节长度。

    数据类型存储要求
    CHAR(M)紧凑的InnoDB行格式优化了可变长度字符集的存储。参见COMPACT行格式存储特性。否则，M × w字节，<= M <= 255，其中w是字符集中最大长度字符所需的字节数。
    BINARY(M)M 字节, 0 <= M<= 255
    VARCHAR(M), VARBINARY(M)如果列值需要0 ~ 255字节，则L + 1字节;如果列值需要超过255字节，则L + 2字节。但是经过实际验证发现，在计算索引长度的时候，统一加2字节存储该变长列的实际长度，与是否超过255字节无关。
    TINYBLOB, TINYTEXTL + 1 字节, 其中 L < 2^8
    BLOB, TEXTL + 2字节, 其中 L < 2^16
    MEDIUMBLOB, MEDIUMTEXTL + 3字节, 其中 L < 2^24
    LONGBLOB, LONGTEXTL + 4 字节, 其中 L < 2^32
    验证CHAR、BINARY、VARCHAR、VARBINARY
    --创建测试表，验证CHAR、BINARY、VARCHAR、VARBINARY
create table test_var (var char(6) not null,var2 BINARY(6) not null,var3 VARCHAR(6) not null,var4 VARCHAR(300) not null,var5 VARBINARY(6) not null,var6 VARBINARY(300) not null);
insert into test_var values('abc','abc','abc','abc','abc','abc');
--创建索引
create index idx_test_var_var on test_var(var);
create index idx_test_var_var2 on test_var(var2);
create index idx_test_var_var3 on test_var(var3);
create index idx_test_var_var4 on test_var(var4);
create index idx_test_var_var5 on test_var(var5);
create index idx_test_var_var6 on test_var(var6);--索引字段为char(6)，key_len值为18及为6*3
mysql> explain select * from test_var where var ='a';
+----+-------------+----------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys    | key              | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test_var | NULL       | ref  | idx_test_var_var | idx_test_var_var | 18      | const |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
--索引字段为BINARY(6)，key_len值为6
mysql> explain select * from test_var where var2 ='a';
+----+-------------+----------+------------+------+-------------------+-------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table    | partitions | type | possible_keys     | key               | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+----------+------------+------+-------------------+-------------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | test_var | NULL       | ref  | idx_test_var_var2 | idx_test_var_var2 | 6       | const |    1 |   100.00 | Using index condition |
+----+-------------+----------+------------+------+-------------------+-------------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
--索引字段为VARCHAR(6)，key_len值为20及为6*3+2
mysql> explain select * from test_var where var3 ='a';
+----+-------------+----------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys     | key               | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test_var | NULL       | ref  | idx_test_var_var3 | idx_test_var_var3 | 20      | const |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
--索引字段为VARCHAR(100)，key_len值为302及为100*3+2
mysql> explain select * from test_var where var4 ='a';
+----+-------------+----------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys     | key               | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test_var | NULL       | ref  | idx_test_var_var4 | idx_test_var_var4 | 302     | const |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
--索引字段为VARBINARY(6)，key_len值为8及为6+2
mysql> explain select * from test_var where var5 ='a';
+----+-------------+----------+------------+------+-------------------+-------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table    | partitions | type | possible_keys     | key               | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+----------+------------+------+-------------------+-------------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | test_var | NULL       | ref  | idx_test_var_var5 | idx_test_var_var5 | 8       | const |    1 |   100.00 | Using index condition |
+----+-------------+----------+------------+------+-------------------+-------------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
--索引字段为VARBINARY(100)，key_len值为102及为100+2
mysql> explain select * from test_var where var6 ='a';
+----+-------------+----------+------------+------+-------------------+-------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table    | partitions | type | possible_keys     | key               | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+----------+------------+------+-------------------+-------------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | test_var | NULL       | ref  | idx_test_var_var6 | idx_test_var_var6 | 102     | const |    1 |   100.00 | Using index condition |
+----+-------------+----------+------------+------+-------------------+-------------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
    验证TINYBLOB、 TINYTEXT、BLOB、 TEXT、MEDIUMBLOB、 MEDIUMTEXT、 LONGBLOB、LONGTEXT
    --创建测试表，验证TINYBLOB、 TINYTEXT、BLOB、 TEXT、MEDIUMBLOB、 MEDIUMTEXT、 LONGBLOB、LONGTEXT
create table test_var2 (var TINYBLOB not null,var2 TINYTEXT not null,var3 BLOB not null,var4 TEXT not null,var5 MEDIUMBLOB not null,var6 MEDIUMTEXT not null,var7 LONGBLOB not null,var8 LONGTEXT not null);insert into test_var2 values('abc','abc','abc','abc','abc','abc','abc','abc');
insert into test_var2 values('abcde','abcde','abcde','abcde','abcde','abcde','abcde','abcde');
--创建索引
create index idx_test_var2_var on test_var2(var(10));
create index idx_test_var2_var2 on test_var2(var2(10));
create index idx_test_var2_var3 on test_var2(var3(10));
create index idx_test_va2r_var4 on test_var2(var4(10));
create index idx_test_var2_var5 on test_var2(var5(10));
create index idx_test_var2_var6 on test_var2(var6(10));
create index idx_test_var2_var7 on test_var2(var7(10));
create index idx_test_va2r_var8 on test_var2(var8(10));
--索引字段为TINYBLOB且给前10个字节创建索引，key_len值为12及10+2
mysql> explain select * from test_var2 where var='ab';
+----+-------------+-----------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys     | key               | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | test_var2 | NULL       | ref  | idx_test_var2_var | idx_test_var2_var | 12      | const |    1 |   100.00 | Using where |
+----+-------------+-----------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
--索引字段为TINYTEXT且给前10个字符创建索引，key_len值为32及10*3+2
mysql> explain select * from test_var2 where var2='ab';
+----+-------------+-----------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys      | key                | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | test_var2 | NULL       | ref  | idx_test_var2_var2 | idx_test_var2_var2 | 32      | const |    1 |   100.00 | Using where |
+----+-------------+-----------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
--索引字段为BLOB且给前10个字节创建索引，key_len值为12及10+2
mysql> explain select * from test_var2 where var3='ab';
+----+-------------+-----------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys      | key                | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | test_var2 | NULL       | ref  | idx_test_var2_var3 | idx_test_var2_var3 | 12      | const |    1 |   100.00 | Using where |
+----+-------------+-----------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
--索引字段为TEXT且给前10个字符创建索引，key_len值为32及10*3+2
mysql> explain select * from test_var2 where var4='ab';
+----+-------------+-----------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys      | key                | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | test_var2 | NULL       | ref  | idx_test_va2r_var4 | idx_test_va2r_var4 | 32      | const |    1 |   100.00 | Using where |
+----+-------------+-----------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
--索引字段为MEDIUMBLOB且给前10个字节创建索引，key_len值为12及10+2
mysql> explain select * from test_var2 where var5='ab';
+----+-------------+-----------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys      | key                | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | test_var2 | NULL       | ref  | idx_test_var2_var5 | idx_test_var2_var5 | 12      | const |    1 |   100.00 | Using where |
+----+-------------+-----------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
--索引字段为MEDIUMTEXT且给前10个字符创建索引，key_len值为32及10*3+2
mysql> explain select * from test_var2 where var6='ab';
+----+-------------+-----------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys      | key                | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | test_var2 | NULL       | ref  | idx_test_var2_var6 | idx_test_var2_var6 | 32      | const |    1 |   100.00 | Using where |
+----+-------------+-----------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
--索引字段为LONGBLOB且给前10个字节创建索引，key_len值为12及10+2
mysql> explain select * from test_var2 where var7='ab';
+----+-------------+-----------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys      | key                | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | test_var2 | NULL       | ref  | idx_test_var2_var7 | idx_test_var2_var7 | 12      | const |    1 |   100.00 | Using where |
+----+-------------+-----------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
--索引字段为LONGTEXT且给前10个字符创建索引，key_len值为32及10*3+2
mysql> explain select * from test_var2 where var8='ab';
+----+-------------+-----------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys      | key                | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | test_var2 | NULL       | ref  | idx_test_va2r_var8 | idx_test_va2r_var8 | 32      | const |    1 |   100.00 | Using where |
+----+-------------+-----------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

    通过上面的验证可知当索引字段为字符串时，key_len值受如下

    • char、varchar、text、LONGTEXT、MEDIUMTEXT、TINYTEXT字段类型受字符集影响
    • varchar 、VARBINARY、TINYBLOB、 TINYTEXT、BLOB、 TEXT、MEDIUMBLOB、 MEDIUMTEXT、 LONGBLOB、LONGTEXT变长字段，通过验证发现，在计算索引长度的时候，统一加2字节存储该变长列的实际长度，与其他列长度或者实际长度没有关系
  2. 索引字段为字符串时，索引列数据类型本身占用的空间跟字符集有关

    常用的字符编码占用字节数量如下：

    字符编码占用字节数
    GBK2
    UTF83
    ISO8859-11
    GB23122
    UTF-162

    下面测试数据库字符编码格式为UTF8占3个字节，目前测试发现只有char、varchar、text、LONGTEXT、MEDIUMTEXT、TINYTEXT字段类型受字符集影响

    比如char(M)字段占用空间：M*Maxlen(字符集占用字节数)，详细验证步骤可参考字符类型占用字节数列表的验证步骤

    注意：MySQL5.0版本以上，字符串定义列的长度单位为字符

  3. mysql.com/doc/refman/8.0/en/explain-output.html#explain_key_len" rel="nofollow">索引字段列为NULL时，key_len计算时，需要加1；索引字段为NOT NULL时只计算索引列数据类型本身占用的空间
    --创建测试表
create table test_null (id int,name1 varchar(10),name2 varchar(10) not null);
insert into test_null values(1,'wang','wang');
insert into test_null values(2,'li','wang');
create index idx_test_null_name1 on test_null(name1);
create index idx_test_null_name2 on test_null(name2);
--索引字段name1为空时，使用索引idx_test_null_name1,key_len=10*3+2(可变长字段的长度)+1(索引字段为NULL)
mysql> explain select * from test_null where name1 = '1';
+----+-------------+-----------+------------+------+---------------------+---------------------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys       | key                 | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------------+---------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test_null | NULL       | ref  | idx_test_null_name1 | idx_test_null_name1 | 33      | const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+---------------------+---------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
--索引字段name2不为空时，使用索引idx_test_null_name2，key_len=10*3+2(可变长字段的长度)
mysql> explain select * from test_null where name2 = '2';
+----+-------------+-----------+------------+------+---------------------+---------------------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys       | key                 | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------------+---------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test_null | NULL       | ref  | idx_test_null_name2 | idx_test_null_name2 | 32      | const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+---------------------+---------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

key_len的作用

MySQL执行计划中输出key_len列主要是为了让我们区分使用组合索引的查询语句具体用了几个索引列，另外由于组合索引有最左前缀的特性，如果组合索引能全部使用上，则是组合索引字段key_len为各索引字段的索引长度之和，通过索引长度的比对就可以判断索引是部分使用，还是全部使用。

总结

经过对大部分字段类型的测试，索引字段的key_len的值受如下的因素影响：

  1. 数值类型中，DECIMAL(M,D), NUMERIC(M,D)字段类型精度和BIT(M)字段类型长度会影响key_len值，其他数值类型占用存储空间都为固定字节数
  2. 日期时间类型中，TIME、DATETIME、TIMESTAMP的字段类型得时间精度会影响key_len值，剩余的year、date占用存储空间为固定字节数
  3. char、varchar、text、LONGTEXT、MEDIUMTEXT、TINYTEXT字段类型受字符集影响，比如字符集为UTF8时，char(M)字段占用空间：M*Maxlen(字符集占用字节数)
  4. varchar 、VARBINARY、TINYBLOB、 TINYTEXT、BLOB、 TEXT、MEDIUMBLOB、 MEDIUMTEXT、 LONGBLOB、LONGTEXT变长字段，通过验证发现，在计算索引长度的时候，统一加2字节存储该变长列的实际长度，与其他列长度或者实际长度没有关系
  5. 索引字段列为NULL时，key_len计算时，需要加1
  6. TINYBLOB、 TINYTEXT、BLOB、 TEXT、MEDIUMBLOB、 MEDIUMTEXT、 LONGBLOB、LONGTEXT创建索引时，只支持前缀索引及指定在前L个字符（TEXT）或者L个字节（字节）上加索引，在计算key_len值时使用的是指定索引字段的长度L

参考

[1]https://www.modb.pro/db/52861


http://www.ppmy.cn/server/38365.html

相关文章

centos7.9下安装mysql8

centos7.9下安装mysql8

在mysql官网下载mysql-8.0.28-1.el7.x86_64.rpm-bundle.tar查看是否已经安装了mysql rpm -qa |grep mysql rpm -qa | grep mysql 查看是否已经安装了mariadb rpm -qa | grep mariadb [rootbogon software]# rpm -qa | grep mariadb mariadb-libs-5.5.68-1.el7.x86_64 卸载…
阅读更多...
【Git】Git学习-12：关联本地仓库和远程仓库

【Git】Git学习-12：关联本地仓库和远程仓库

学习视频链接&#xff1a;【GeekHour】一小时Git教程_哔哩哔哩_bilibili​编辑https://www.bilibili.com/video/BV1HM411377j/?vd_source95dda35ac10d1ae6785cc7006f365780 在github上建立仓库 根据指引将本地仓库push到github上 git remote add origin gitgithub.com:JVZO/f…
阅读更多...
iOS ------ 内存五大分区

iOS ------ 内存五大分区

1&#xff0c;内存的概念&#xff1a; 虚拟内存&#xff08;Virtual Memory&#xff09;&#xff1a;虚拟内存是操作系统提供的一种机制&#xff0c;它使得应用程序能够访问超出物理内存限制的内存空间。虚拟内存将应用程序的内存地址空间分割成固定大小的页面&#xff08;Pag…
阅读更多...
Docker-Consul容器服务更新与发现

Docker-Consul容器服务更新与发现

前言 Docker Compose 则进一步简化了多个容器应用的编排与管理。另一方面&#xff0c;Consul 作为一款先进的服务发现工具&#xff0c;为分布式和微服务架构提供了可靠的服务注册与发现机制。本文将探讨 Docker Compose 和 Consul 在容器化环境中的协同作用&#xff0c;以及它…
阅读更多...
深入理解WPF的ResourceDictionary

深入理解WPF的ResourceDictionary

深入理解WPF的ResourceDictionary 介绍 在WPF中&#xff0c;ResourceDictionary用于集中管理和共享资源&#xff08;如样式、模板、颜色等&#xff09;&#xff0c;从而实现资源的重用和统一管理。本文详细介绍了ResourceDictionary的定义、使用和合并方法。 定义和用法 Res…
阅读更多...
MindSponge分子动力学模拟——定义一个分子系统

MindSponge分子动力学模拟——定义一个分子系统

技术背景 在前面两篇文章中&#xff0c;我们分别介绍了分子动力学模拟软件MindSponge的软件架构和安装与使用。这里我们进入到实用化阶段&#xff0c;假定大家都已经在本地部署好了基于MindSpore的MindSponge的编程环境&#xff0c;开始用MindSponge去做一些真正的分子模拟的工…
阅读更多...
深度学习——前馈全连接神经网络

深度学习——前馈全连接神经网络

前馈全连接神经网络 1.导入需要的工具包2.数据导入与数据观察&#xff08;1&#xff09;读取csv的文件信息&#xff1a;&#xff08;2&#xff09;训练数据前5行&#xff08;3&#xff09;打印第一个图&#xff08;4&#xff09;观察数据中的信息&#xff08;5&#xff09;查看…
阅读更多...
设计模式——策略模式（Strategy）

设计模式——策略模式（Strategy）

策略模式&#xff08;Strategy Pattern&#xff09;是一种行为型设计模式&#xff0c;它允许在运行时动态地改变一个对象的行为。策略模式定义了一系列的算法&#xff0c;并将每一个算法封装起来&#xff0c;使它们可以互相替换。策略模式使得算法可以独立于使用它的客户端变化…
阅读更多...
最新文章

Copyright @ 2022~2023