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累加计算实现方法详解  # 为空  # 所示  # 则在  # 可以看到  # 包含了  # 上加  # 这一  # 没有什么  # 可以通过  # 还需要  # 在上面  # 充分利用  # 所占  # 再加  # 还有很多  # 需要注意  # 计算方法  # 数为  # 有一个  # 也就是说 

相关文章： 网站制作和推广的区别,想自己建立一个网站做推广，有什么快捷方法马上做好一个网站？  如何快速搭建高效香港服务器网站？  如何零基础开发自助建站系统？完整教程解析  如何在云指建站中生成FTP站点？  如何快速搭建高效简练网站？  c++23 std::expected怎么用 c++优雅处理函数错误返回【详解】  如何用花生壳三步快速搭建专属网站？  模具网站制作流程,如何找模具客户？  山东网站制作公司有哪些,山东大源集团官网？  香港服务器WordPress建站指南：SEO优化与高效部署策略  如何快速辨别茅台真假？关键步骤解析  如何破解联通资金短缺导致的基站建设难题？  Python文件管理规范_工程实践说明【指导】  如何快速搭建二级域名独立网站？  金*站制作公司有哪些,金华教育集团官网？  建站之星如何实现PC+手机+微信网站五合一建站？  c++怎么使用类型萃取type_traits_c++ 模板元编程类型判断【方法】  Avalonia如何实现跨窗口通信 Avalonia窗口间数据传递  如何在万网开始建站？分步指南解析  c# await 一个已经完成的Task会发生什么  高防服务器租用如何选择配置与防御等级？  c# Task.Yield 的作用是什么 它和Task.Delay(1)有区别吗  ui设计制作网站有哪些,手机UI设计网址吗？  电商网站制作价格怎么算,网上拍卖流程以及规则？  建站主机服务器选型指南与性能优化方案解析  如何快速生成橙子建站落地页链接？  武汉外贸网站制作公司,现在武汉外贸前景怎么样啊？  制作网站的公司有哪些,做一个公司网站要多少钱？  如何获取上海专业网站定制建站电话？  如何在建站之星网店版论坛获取技术支持？  如何用y主机助手快速搭建网站？  如何在企业微信快速生成手机电脑官网？  深圳网站制作费用多少钱,读秀，深圳文献港这样的网站很多只提供网上试读，但有些人只要提供试读的文章就能全篇下载，这个是怎么弄的？  如何通过NAT技术实现内网高效建站？  阿里云网站搭建费用解析：服务器价格与建站成本优化指南  网站设计制作公司地址,网站建设比较好的公司都有哪些？  宝塔新建站点报错如何解决？  如何快速查询网址的建站时间与历史轨迹？  专业网站制作企业网站,如何制作一个企业网站，建设网站的基本步骤有哪些？  如何快速启动建站代理加盟业务？  小型网站建站如何选择虚拟主机？  如何选择服务器才能高效搭建专属网站？  儿童网站界面设计图片,中国少年儿童教育网站-怎么去注册？  建站之星如何快速解决建站难题？  宝塔Windows建站如何避免显示默认IIS页面？  学校为何禁止电信移动建设网站？  长沙企业网站制作哪家好,长沙水业集团官方网站？  电商网站制作公司有哪些,1688网是什么意思？  C#如何使用XPathNavigator高效查询XML  Java解压缩zip - 解压缩多个文件或文件夹实例