运维首页 > 数据运维 > mysql 执行计划中key_len的计算规则

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

2024年 1月 3日 67.4k 0

  1. 在计算key_len时，常见类型占用的字节数

    数值类型占用字节数列表
    数据类型 占用字节数
    TINYINT 1 byte
    SMALLINT 2 bytes
    MEDIUMINT 3 bytes
    INT, INTEGER 4 bytes
    BIGINT 8 bytes
    FLOAT§ 4 bytes if 0 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之后的存储要求
    YEAR 1 byte 1 byte
    DATE 3 bytes 3 bytes
    TIME 3 bytes 3 bytes +小数秒存储
    DATETIME 8 bytes 5 bytes +小数秒存储
    TIMESTAMP 4 bytes 4 bytes +小数秒存储

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

    小数秒精度 存储要求
    0 0 bytes
    1, 2 1 byte
    3, 4 2 bytes
    5, 6 3 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 explain select * from test_date where dt2 explain select * from test_date where dt3 explain select * from test_date where dt4 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字节， 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. 索引字段为字符串时，索引列数据类型本身占用的空间跟字符集有关

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

    字符编码 占用字节数
    GBK 2
    UTF8 3
    ISO8859-1 1
    GB2312 2
    UTF-16 2

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

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

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

  3. 索引字段列为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)

    4. 大白菜程序猿
    数据运维
    0 0

    相关文章

    Oracle如何使用授予和撤销权限的语法和示例
    Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
    下载丨66页PDF，云和恩墨技术通讯（2024年7月刊）
    社区版oceanbase安装
    Oracle 导出CSV工具-sqluldr2
    ETL数据集成丨快速将MySQL数据迁移至Doris数据库

    发布评论

    大白菜程序猿

    这个人很懒，什么都没有留下～

    文章8480 评论0