MySQL执行计划里面的key_len

以前看MySQL的执行计划,感觉内容有些简陋,平时分析主要就是看是否全表扫描,索引使用是否合理等。基本上也能分析出很多问题来,但是显然有时候会有些疑惑,那就是对于复合索引,多列值的情况下,到底启用了那些索引列,这个时候索引的使用情况就很值得琢磨琢磨了,我们得根据执行计划里面的key_len做一个重要的参考。

我们做一个简单的测试来说明。

CREATE TABLE `department` ( `DepartmentID` int(11) DEFAULT NULL, `DepartmentName` varchar(20) DEFAULT NULL, KEY `IND_D` (`DepartmentID`), KEY `IND_DN` (`DepartmentName`) ) ENGINE=InnoDB DEFAULT CHARSET=gbk;

运行语句为:explain select count(*)from department\G

对于这个语句,key_len到底是多少呢?

mysql> explain select count(*)from department\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: department type: index possible_keys: NULL key: IND_D key_len: 5 ref: NULL rows: 1 Extra: Using index 1 row in set (0.00 sec) 在这个例子里面,possible_keys,key,Extra你看了可能有些晕,我们看看key_len的值为5,这个值是怎么算出来的呢,首先表有两个字段,第一个字段的类型为数值,int的长度为4,因为字段可为null,所以需要一个字节来存储,这样下来就是4+1=5了。由此我们可以看到这个语句是启用了索引ind_d.

那我们举一反三,把语句修改一下,看看key_len的变化。

mysql> explain select departmentName from department b where departmentName='TEST'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: b type: ref possible_keys: IND_DN key: IND_DN key_len: 43 ref: const rows: 1 Extra: Using where; Using index 1 row in set (0.09 sec) 从上面可以看到,key_len为43,这个值是怎么算出来的呢,我们来掰扯一下,字段2为字符型,长度20,因为是GBK字符集,所以需要乘以2,因为允许字段为NULL,则需要一个字节,对于变长的类型(在此就是VARCHAR),key_len还要加2字节。这样下来就是20*2+1+2=43

到了这里仅仅是个开始,我们需要看看略微复杂的情况,就需要复合索引了。我们就换一个表test_keylen2

create table test_keylen2 (c1 int not null,c2 int not null,c3 int not null); alter table test_keylen2 add key idx1(c1, c2, c3); 下面的语句就很实际了,

explain SELECT *from test_keylen2 WHERE c1=1 AND c2=1 ORDER BY c1\G

这个语句中,keylen到底是应该为4或者8还是12呢? 我们就需要验证一下了。

mysql> explain SELECT *from test_keylen2 WHERE c1=1 AND c2=1 ORDER BY c1\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: test_keylen2 type: ref possible_keys: idx1 key: idx1 key_len: 8 ref: const,const rows: 1 Extra: Using index 1 row in set (0.07 sec) 显然key_len只计算了where中涉及的列,因为是数值类型,所以就是4+4=8

那下面的这个语句呢。

explain SELECT *from test_keylen2 WHERE c1>=1 and c2=2 \G

我们添加一个范围,看看这个该如何拆分。

mysql> explain SELECT *from test_keylen2 WHERE c1>=1 and c2=2 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: test_keylen2 type: index possible_keys: idx1 key: idx1 key_len: 12 ref: NULL rows: 1 Extra: Using where; Using index 1 row in set (0.07 sec) 在这里就不只是计算where中的列了,而是因为>1的条件直接选择了3个列来计算。

对于date类型的处理,有一个很细小的差别。我们再换一个表,含有事件类型的字段,

CREATE TABLE `tmp_users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `uid` int(11) NOT NULL, `l_date` datetime NOT NULL, `data` varchar(32) DEFAULT NULL, PRIMARY KEY (`id`), KEY `ind_uidldate` (`uid`,`l_date`) ) ENGINE=InnoDB DEFAULT CHARSET=gbk;

下面的语句key_len该如何计算呢。

explain select * from tmp_users where uid = 9527 and l_date >= '2012-12-10 10:13:17'\G

这一点出乎我的意料,按照datetime的印象是8个字节,所以应该是8+4=12,但是这里却偏偏是9,这个数字怎么计算的。 id: 1 select_type: SIMPLE table: tmp_users type: range possible_keys: ind_uidldate key: ind_uidldate key_len: 9 ref: NULL rows: 1 Extra: Using index condition 1 row in set (0.07 sec) 这里就涉及到一个技术细节,是在MySQL 5.6中的datetime的存储差别。在5.6.4以前是8个字节,之后是5个字节

所以按照这个算法就是4+5=9

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2017-09-26

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏惨绿少年

MySQL 索引管理与执行计划

1.1 索引的介绍   索引是对数据库表中一列或多列的值进行排序的一种结构,使用索引可快速访问数据库表中的特定信息。如果想按特定职员的姓来查找他或她,则与在表中...

25700
来自专栏Java架构沉思录

8种常被忽视的SQL错误用法

分页查询是最常用的场景之一,但也通常也是最容易出问题的地方。比如对于下面简单的语句,一般 DBA 想到的办法是在 type, name, create_time...

12530
来自专栏技术小站

(转)MySQL之char、varchar和text的设计

http://www.cnblogs.com/billyxp/p/3548540.html

15120
来自专栏面朝大海春暖花开

解决mysql中limit和in不能同时使用的问题

两种方式推荐第一种。避免了in语句。进行explain诊断会发现第一种效率高很多。

9520
来自专栏个人随笔

MySQL的DML和DQL   增删改查

DML和DQL   增删改查 SELECT * FROM grade --新增 insert -- 向年级表中新增3条数据 INSERT INTO grade...

29780
来自专栏极客慕白的成长之路

知识点、SQL语句学习及详细总结

24920
来自专栏Python

表——完整约束性规则(键)

一 介绍 约束条件与数据类型的宽度一样,都是可选参数 作用:用于保证数据的完整性和一致性 主要分为: primary key (PK) 标识该字段为该表的...

23370
来自专栏杨建荣的学习笔记

oracle中关于小数中0的格式化(55天)

今天碰到一个小问题,分享一下。 oracle中输入0.1查出的时候是.1,现在想把结果格式化成varchar2,格式化成0.10,保留两位精度。 先拿大于1的数...

34150
来自专栏Java帮帮-微信公众号-技术文章全总结

【数据库】MySQL进阶八、多表查询

【数据库】MySQL进阶八、多表查询 MySQL多表查询 一 使用SELECT子句进行多表查询 SELECT 字段名 FROM 表1,表2 … WHERE ...

46140
来自专栏惨绿少年

MySQL-Select语句高级应用

1.1 SELECT高级应用 1.1.1 前期准备工作 本次测试使用的是world数据库,由mysql官方提供下载地址: https://dev.mysql....

32100

扫码关注云+社区

领取腾讯云代金券