饿虎岗资源网

MySQL的查询计划中ken_len的值计算方法
数据库 2024/11/15 佚名
3 1
饿虎岗资源网 Design By www.oxmxm.com

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语句并不能够充分利用索引。

饿虎岗资源网 Design By www.oxmxm.com
广告合作：本站广告合作请联系QQ：858582 申请时备注：广告合作（否则不回）
免责声明：本站资源来自互联网收集,仅供用于学习和交流,请遵循相关法律法规,本站一切资源不代表本站立场,如有侵权、后门、不妥请联系本站删除！
饿虎岗资源网 Design By www.oxmxm.com
www.oxmxm.com 饿虎岗资源网
120,135影音资源
344,641技术资源
22,817软件资源
435,032站长资源

更新日志

友情链接

杰晶网络 DDR爱好者之家 桃源资源网 杰网资源 富贵资源网 南强小屋 铁雪资源网 幽灵资源网 万梅资源网 狼山资源网 白云岛资源网 昆仑资源网 相思资源网 明霞山资源网 内蒙古资源网 黑松山资源网 茶园资源网 饿虎岗资源网 大旗谷资源网 常春岛资源网 岱庙资源网 兴国资源网 快活林资源网 蝙蝠岛资源网 帝王谷资源网 白云城资源网 伏龙阁资源网 清风细雨楼 天枫庄资源网 圆月山庄资源网 无争山庄资源网 神水资源网 移花宫资源网 神剑山庄资源网 无为清净楼资源网 金钱帮资源网 丐帮资源网 华山资源网 极乐门资源网 小李飞刀资源网 凤求凰客栈 风云阁资源网 金狮镖局 鸳鸯亭资源网 千金楼资源网 更多链接
饿虎岗资源网 Design By www.oxmxm.com