MySQL执行计划explain的key_len剖析
发布时间：2022-03-29 04:57:51 所属栏目：MySql教程 来源：互联网
导读：当用Explain查看SQL的执行计划时，里面有列显示了 key_len 的值，根据这个值可以判断索引的长度，在组合索引里面可以更清楚的了解到了哪部分字段使用到了索引。下面演示中，表结构的合理性这边暂且不说，只是证明一下索引长度的计算方法。目前大部分博文是字
|
当用Explain查看SQL的执行计划时，里面有列显示了 key_len 的值，根据这个值可以判断索引的长度，在组合索引里面可以更清楚的了解到了哪部分字段使用到了索引。下面演示中，表结构的合理性这边暂且不说，只是证明一下索引长度的计算方法。目前大部分博文是字符类型的索引长度计算方法，下面列举几个类型的索引长度计算方法： 1、整数类型 (dg1)root@127.0.0.1 [mytest]> desc table_key; +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | id | int(11) | NO | | NULL | | | sid | bigint(20) | NO | | 0 | | | name | char(10) | YES | | NULL | | | age | tinyint(4) | YES | | NULL | | | sex | tinyint(4) | NO | | NULL | | | address | varchar(10) | YES | MUL | NULL | | +---------+-------------+------+-----+---------+-------+ rows in set (0.01 sec) (dg1)root@127.0.0.1 [mytest]>create index age_index on table_key (age); 来看看tinyint类型的索引长度，在NOT NULL 和 NULL 的时候 分别是1和2,tinyint字段长度为1，因为NULL 需要额外一个字节标记为空 (dg1)root@127.0.0.1 [mytest]> explain extended select * from table_key where age=38; +----+-------------+-----------+------+---------------+-----------+---------+-------+------+----------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------+---------------+-----------+---------+-------+------+----------+-------+ | 1 | SIMPLE | table_key | ref | age_index | age_index | 1 | const | 1 | 100.00 | NULL | +----+-------------+-----------+------+---------------+-----------+---------+-------+------+----------+-------+ row in set, 1 warning (0.00 sec) (dg1)root@127.0.0.1 [mytest]> desc select * from table_key where sid=6; +----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------+ | 1 | SIMPLE | table_key | ref | sid_index | sid_index | 8 | const | 1 | NULL | +----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------+ row in set (0.00 sec) (dg1)root@127.0.0.1 [mytest]> desc select * from table_key where sid=6; +----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------+ | 1 | SIMPLE | table_key | ref | sid_index | sid_index | 9 | const | 1 | NULL | +----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------+ row in set (0.00 sec) (dg1)root@127.0.0.1 [mytest]> desc select * from table_key where sid=6; +----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------+ | 1 | SIMPLE | table_key | ref | sid_index | sid_index | 2 | const | 1 | NULL | +----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------+ row in set (0.00 sec) (dg1)root@127.0.0.1 [mytest]> alter table table_key modify sid smallint ; Query OK, 0 rows affected (0.07 sec) Records: 0 Duplicates: 0 Warnings: 0 (dg1)root@127.0.0.1 [mytest]> desc select * from table_key where sid=6; +----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------+ | 1 | SIMPLE | table_key | ref | sid_index | sid_index | 3 | const | 1 | NULL | +----+-------------+-----------+------+---------------+-----------+---------+-------+------+-------+ row in set (0.00 sec) 2.浮点数类型 表结构 CREATE TABLE `table_key1` ( `id` int NOT NULL AUTO_INCREMENT , `c1` float NOT NULL , `c2` double NOT NULL , `c3` decimal NOT NULL , `c4` date NOT NULL , `c5` timestamp NOT NULL ON UPDATE CURRENT_TIMESTAMP , `c6` datetime NOT NULL , PRIMARY KEY (`id`) ) 看看float类型的索引长度，NOT NULL和NULL的时候，分别是4和5 (dg1)root@127.0.0.1 [mytest]> desc select * from table_key1 where c1 = '3.22'; +----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+ | 1 | SIMPLE | table_key1 | ref | c1_index | c1_index | 4 | const | 8 | Using index condition | +----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+ row in set (0.00 sec) (dg1)root@127.0.0.1 [mytest]> desc select * from table_key1 where c1 = '3.22'; +----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+ | 1 | SIMPLE | table_key1 | ref | c1_index | c1_index | 5 | const | 8 | Using index condition | +----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+ row in set (0.00 sec) 看看double类型的索引长度，NOT NULL和NULL的时候，分别是8和9 (dg1)root@127.0.0.1 [mytest]> alter table table_key1 add key c2_index (c2); Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 (dg1)root@127.0.0.1 [mytest]> 3、看看时间类型 看看date类型的索引长度，在NOT NULL和NULL的时候，分别是3和4 (dg1)root@127.0.0.1 [mytest]> desc select * from table_key1 where c4 = '2015-05-06'; +----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+ | 1 | SIMPLE | table_key1 | ref | c4_index | c4_index | 3 | const | 4 | Using index condition | +----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+ row in set, 3 warnings (0.00 sec) (dg1)root@127.0.0.1 [mytest]> desc select * from table_key1 where c4 = '2015-05-06'; +----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+ | 1 | SIMPLE | table_key1 | ref | c4_index | c4_index | 4 | const | 4 | Using index condition | +----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+ row in set, 3 warnings (0.00 sec) dg1)root@127.0.0.1 [mytest]> alter table table_key1 modify c5 timestamp ON UPDATE CURRENT_TIMESTAMP ; Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 （编辑：威海站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
