MySQL的查询计划中ken_len的值计算方法

key_len的含义

在MySQL中,可以通过explain查看SQL语句所走的路径,如下所示：

mysql> create table t(a int primary key, b int not null, c int not null, index(b));
 Query OK, 0 rows affected (0.01 sec)
 mysql> explain select b from t ;
 +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
 | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra    |
 +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
 | 1 | SIMPLE   | t   | index | NULL     | b  | 4    | NULL |  1 | Using index |
 +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
 1 row in set (0.00 sec)

其中,key_len表示使用的索引长度,是以字节为单位。在上面的例子中,由于int型占用4个字节,而索引中只包含了1列,所以,key_len是4。

下面是联合索引的情况：

mysql> alter table t add index ix(b, c);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain select b, c from t ;
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra    |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE   | t   | index | NULL     | ix  | 8    | NULL |  1 | Using index |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

联合索引ix包含了2列,并且都使用到了,所以,这里ken_len是8。

到这里,我们已经可以理解key_len的含义了,似乎已经没有什么可讲的了,但是,MySQL中key_len的计算还有很多需要注意的地方。

例如,我们将b这一列的NOT NULL约束去掉,然后ken_len就和我们预期不一样了,如下所示：

mysql> alter table t modify b int;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
 
mysql> explain select b from t;
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra    |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE   | t   | index | NULL     | b  | 5    | NULL |  1 | Using index |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

MySQL中key_len计算规则

MySQL中,key_len的计算规则如下：

• 如果列可以为空,则在数据类型占用字节的基础上加1,如int型,不能为空key_len为4,可以为空key_len为5
• 如果列是变长的,则在数据列所占字节的基数上再加2,如varbinary(10),不能为空,则key_len为10 + 2 ,可以为空则key_len为10+2+1
• 如果是字符型,则还需要考虑字符集,如某列的定义是varchar(10),且是utf8,不能为空,则key_len为10 * 3 + 2,可以为空则key_len为10*3+2+1
• 此外,decimal列的计算方法与上面一样,如果可以为空,则在数据类型占用字节的基础上加1,但是,decimal本身所占用字节数,计算就比较复杂。

根据官方文档可以知道,decimal定义为decimal(M,D),其中,M是总的位数,D是小数点后保留的位数。小数点前与小数点后的数字分开存储,且以9位数为1组,用4个字节保存,如果低于9位数,需要的字节数如下：

Leftover Digits Number of Bytes
-----------------------------
|0 |0 |
|1-2 |1 |
|3-4 |2 |
|5-6 |3 |
|7-9 |4 |
-----------------------------

例如：

decimal(20,6)=> 小数点左边14位,小数点右边6位 => 小数点左边分组为5 + 9,需要3个字节+4个字节存储,小数点一个分组,需要3个字节存储 => 总共需要10个字节
decimal(18,9)=> 小数点左边9位数,小数点右边9位数 => 分别使用4个字节存储 => 共需要 8个字节
decimal(18,2)=> 小数点左边16位数,小数点右边2位数 => 分组为7 + 9,需要8个字节存储,小数点右边1个字节存储 => 共需要9个字节

通过key_len分析联合索引

如下所示,我们定义了一个表t,表t包含a、b、c、d共4列：

mysql> show create table t\G
*************************** 1. row ***************************
    Table: t
Create Table: CREATE TABLE `t` (
 `a` int(11) NOT NULL,
 `b` int(11) DEFAULT NULL,
 `c` int(11) DEFAULT NULL,
 `d` int(11) DEFAULT NULL,
 PRIMARY KEY (`a`),
 KEY `ix_x` (`b`,`d`,`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

现在要执行SQL语句如下：

select a from t where b = 5 and d = 10 order by c;

假设我们有一个索引ix_x(b,d,c),通过explain得到如下输出：

mysql> explain select a from t where b = 5 and d = 10 order by c;
+----+-------------+-------+------+---------------+------+---------+-------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref     | rows | Extra          |
+----+-------------+-------+------+---------------+------+---------+-------------+------+--------------------------+
| 1 | SIMPLE   | t   | ref | ix_x     | ix_x | 10   | const,const |  1 | Using where; Using index |
+----+-------------+-------+------+---------------+------+---------+-------------+------+--------------------------+
1 row in set (0.00 sec)

可以看到,查询语句使用了联合索引中的b和d两列来过滤数据。

如果我们定义的联合索引不是`ix_x(b,d,c)`,而是`ix_x(b, c, d)`,通过explain得到的输入如下：

mysql> alter table t drop index ix_x;
mysql> alter table t add index ix_x(b, c, d);
mysql> explain select a from t where b = 5 and d = 10 order by c;
+----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref  | rows | Extra          |
+----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
| 1 | SIMPLE   | t   | ref | ix_x     | ix_x | 5    | const |  2 | Using where; Using index |
+----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)

key_len为5,也就是说,只用到了联合索引中的第一列,可以看到,虽然联合索引包含了我们要查询的所有列,但是,由于定义的顺序问题,SQL语句并不能够充分利用索引。

# MySQL  # ken_len  # mysql计算时间差函数  # Mysql中通过生日计算年龄的多种方法  # 在php和MySql中计算时间差的方法  # mysql 字符串长度计算实现代码(gb2312+utf8)  # Mysql数据库的QPS和TPS的意义和计算方法  # MySQL几点重要的性能指标计算和优化方法总结  # 浅谈mysql explain中key_len的计算方法  # mysql日期和时间的间隔计算实例分析  # MySQL日期加减函数详解  # mysql 触发器创建与使用方法示例  # MySQL触发器基本用法详解【创建、查看、删除等】  # mysql累加计算实现方法详解  # 为空  # 所示  # 则在  # 可以看到  # 包含了  # 上加  # 这一  # 没有什么  # 可以通过  # 还需要  # 在上面  # 充分利用  # 所占  # 再加  # 还有很多  # 需要注意  # 计算方法  # 数为  # 有一个  # 也就是说 

相关文章： ppt制作免费网站有哪些,ppt模板免费下载网站？  如何获取上海专业网站定制建站电话？  如何在香港免费服务器上快速搭建网站？  C#怎么创建控制台应用 C# Console App项目创建方法  电商网站制作公司有哪些,1688网是什么意思？  宿州网站制作公司兴策,安徽省低保查询网站？  电商平台网站制作流程,电商网站如何制作？  建站主机与虚拟主机有何区别？如何选择最优方案？  制作网站的过程怎么写,用凡科建站如何制作自己的网站？  如何在阿里云购买域名并搭建网站？  香港服务器选型指南：免备案配置与高效建站方案解析  行程制作网站有哪些,第三方机票电子行程单怎么开？  网站设计制作企业有哪些,抖音官网主页怎么设置？  网站制作的软件有哪些,制作微信公众号除了秀米还有哪些比较好用的平台？  如何快速搭建支持数据库操作的智能建站平台？  在线制作视频的网站有哪些,电脑如何制作视频短片？  TestNG的testng.xml配置文件怎么写  山东网站制作公司有哪些,山东大源集团官网？  制作电商网页,电商供应链怎么做？  常州自助建站工具推荐：低成本搭建与模板选择技巧  如何解决ASP生成WAP建站中文乱码问题？  制作充值网站的软件,做人力招聘为什么要自己交端口钱？  建站之星如何优化SEO以实现高效排名？  如何选择高效便捷的WAP商城建站系统？  香港服务器网站卡顿？如何解决网络延迟与负载问题？  哪家制作企业网站好,开办像阿里巴巴那样的网络公司和网站要怎么做？  javascript中对象的定义、使用以及对象和原型链操作小结  合肥做个网站多少钱,合肥本地有没有比较靠谱的交友平台？  高端网站建设与定制开发一站式解决方案 中企动力  如何快速使用云服务器搭建个人网站？  ,石家庄四十八中学官网？  如何获取开源自助建站系统免费下载链接？  建站之星后台管理如何实现高效配置？  广州网站制作公司哪家好一点,广州欧莱雅百库网络科技有限公司官网？  打鱼网站制作软件,波克捕鱼官方号怎么注册？  可靠的网站设计制作软件,做网站设计需要什么样的电脑配置？  建站之星×万网：智能建站系统+自助建站平台一键生成  制作企业网站建设方案,怎样建设一个公司网站？  如何在云服务器上快速搭建个人网站？  网站制作网站,深圳做网站哪家比较好？  建站之星展会模版如何一键下载生成？  ,想在网上投简历，哪几个网站比较好？  制作门户网站的参考文献在哪,小说网站怎么建立？  音乐网站服务器如何优化API响应速度？  深圳防火门网站制作公司,深圳中天明防火门怎么编码？  孙琪峥织梦建站教程如何优化数据库安全？  大连网站设计制作招聘信息,大连投诉网站有哪些？  制作销售网站教学视频,销售网站有哪些？  网站制作的方法有哪些,如何将自己制作的网站发布到网上？  建站之星价格显示格式升级，你的预算足够吗？