网站导航
> 文章列表 > MySQL - explan - key_len

MySQL - explan - key_len

网友: 文章列表 2024-03-22 02:18:15

MySQL - explan - key_len

key_len

表示查询用到的索引key的长度(字节数)。如果单列索引，那么就会把整个索引长度计算进去，如果是联合索引，不是所有的列都用到，那么就只计算实际用到的列，因此可以根据key_len来判断联合索引是否生效。

1. 注意:

key 太长会导致一个页当中能够存放的 key 的数目变少，间接导致索引树的页数目变多，索引层次增加，从而影响整体查询变更的效率。

2.索引长度计算规则:

a.基础

  1. TextField是不支持建立索引的

  2. MySQL对索引字段长度有限制

    • innodb引擎的每个索引列长度限制为767字节（bytes），所有组成索引列的长度和不能大于3072字节（注意版本不同，函数不同，767不一样）
    • myisam引擎的每个索引列长度限制为1000字节，所有组成索引列的长度和不能大于1000字节

  3. varchar的最大长度是指字符长度，若数据库字符集为utf-8，则一个字符占3个bytes。因此在utf-8字符集下，innodb引擎创建的单列索引长度不能超过255个字符

b.mysql版本不同而导致的索引长度限制不同

  • 在MySQL5.5版本，引入了innodb_large_prefix，用来禁用大型前缀索引，以便与不支持大索引键前缀的早期版本的InnoDB兼容
  • 开启innodb_large_prefix可以使单索引的长度限制达到3072字节（但是联合索引总长度限制还是3072字节），禁用时单索引的长度限制为767字节
  • 在MySQL5.5版本与MySQL5.6版本，innodb_large_prefix是默认关闭的，在MySQL5.7及以上版本则默认开启
  • 在MySQL8.0版本中，innodb_large_prefix已被移除
  • 查看某个参数是否存在 `show variables like ‘%innodb_large_prefix%’;
  • 在这里插入图片描述

这就是（MySQL8.0）上可以创建1024字符（utf8字符集下表示3072字节）长的索引，而在服务器（MySQL5.5）上不行的原因

测试索引长度限制的脚本：

use test;
drop table if exists test_index_len;
create table 
test_index_len(long_char varchar(1025) primary key) ENGINE=InnoDB charset=utf8;
use test;
drop table if exists test_index_len;
create table 
test_index_len(long_char varchar(24),origin_str varchar(1000),key test_index(long_char, origin_str)) ENGINE=InnoDB charset=utf8;

c.计算

  1. 所有的索引字段，如果没有设置 Not Null，则需要加一个字节(这也是我们为什么建议建表时不要有 Null 字段的原因之一)。

  2. 对于定长字段，int 类型占四个字节、date 占三个字节、char(n) 占 N 个字符。

    • int 类型 4字节 。备注：一个字节8位，即有32位，存在一个符号位。所以能存的数值大小为-2的31次方到2的31次方；

    • tinyint 类型 1字节。2^8， 8/8=1

    • smallint 类型 2字节。2^16 ， 16/8=2

    • mediumint 类型 3字节。2^24 ，24/8=3

    • bigint 类型 8字节。2^64 ，64/8=8

    • float 类型 4字节。 单精度浮点数。单精度有效数字是7位

    • double 类型 8字节 双精度浮点数。双精度有效数字是16位

      • double占用的内存是float的两倍，所以float的运算速度要快，可能的条件下尽可能的使用float。

  3. 对于变长字段 varchar(n)，则是 N 个字符 + 两个字节。

  4. 不同的字符集，一个字符占用的字节数不同。

    • latin1编码的，一个字符占用一个字节，
    • gbk编码的，一个字符占用两个字节，
    • utf8编码的，一个字符占用三个字节。

  5. 计算公式

    Character Set：(utf8mb4=4,utf8=3,gbk=2,latin1=1) * 列长度 + 1(允许 Null) + 2(变长列)

测试：

CREATE TABLE `tb_item` (`id` BIGINT NOT NULL COMMENT '书籍id，同时也是书籍编号',`title` VARCHAR ( 100 ) NOT NULL COMMENT '书籍名称',`sell_point` VARCHAR ( 500 ) DEFAULT NULL COMMENT '书籍卖点',`price` BIGINT NOT NULL COMMENT '书籍价格，单位为：分',`num` INT NOT NULL COMMENT '库存数量',`barcode` VARCHAR ( 30 ) DEFAULT NULL COMMENT '书籍条形码',`image` VARCHAR ( 500 ) DEFAULT NULL COMMENT '书籍图片',`cid` BIGINT NOT NULL COMMENT '所属类目，叶子类目',`status` TINYINT NOT NULL DEFAULT '1' COMMENT '书籍状态，1-正常，2-下架，3-删除',`created` datetime NOT NULL COMMENT '创建时间',`updated` datetime NOT NULL COMMENT '更新时间',`upload_id` BIGINT DEFAULT NULL,PRIMARY KEY ( `id` ),KEY `cid` ( `cid` ),KEY `status` ( `status` ),KEY `updated` ( `updated` ),
KEY `tb_item_title_price_num` ( `title`, `price`, `num` ) 
) ENGINE = INNODB DEFAULT CHARSET = utf8 COMMENT = '书籍表';explain select * from tb_item where title = '编译原理' and price = 45 and num = 23232//跳过了 price 字段，所以联合索引中只会有 title 字段生效，剩余部分都会失效
explain select * from tb_item where title = '编译原理' and num = 23232

在这里插入图片描述
参考：
https://www.cnblogs.com/luozx207/p/11934499.html
https://www.cnblogs.com/zhoudl/p/16584548.html

网络标签：

相关问题