首页 > 数据库 >一篇搞定MySQL索引长度(key_len)计算规则

一篇搞定MySQL索引长度(key_len)计算规则

时间:2023-06-03 13:22:26浏览次数:45  
标签:test3 name idx len key MySQL NULL id

MySQL索引长度(key_len)计算
 计算规则
  • 索引字段:没有设置 NOT NULL,则需要加 1 个字节。
  • 定长字段:tinyint 占 1 个字节、int 占 4个字节、bitint 占 8 个字节、date 占 3个字节、datetime 占 5 个字节、char(n) 占 n 个字节。
  • 变长字段:varchar (n) 占 n 个字符 + 2 个 字节。

注意(字符和字节在不同编码之间的转换) 

不同的字符集,一个字符占用的字节数不同
  • latin1 编码: 每个字符占用一个字节。
  • gbk编码:每个字符占用两个字节。
  • utf8编码:每个字符占用三个字节。
  • utf8mb4编码:每个字符占用四个字节。
案例分析
(一)、编码为utf8
  1. 创建测试表
    CREATE TABLE `test3` (
      `id` int NOT NULL,
      `name` varchar(20) NOT NULL COMMENT '姓名',
      `sex` tinyint NOT NULL COMMENT '性别,1:男,2:女',
      `email` varchar(20) DEFAULT NULL,
      `age` tinyint default 0,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  2. 创建索引
    mysql> show index from test3;
    +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
    | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
    +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
    | test3 |          0 | PRIMARY  |            1 | id          | A         |           2 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
    | test3 |          1 | idx_age  |            1 | age         | A         |           2 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
    | test3 |          1 | idx_name |            1 | name        | A         |           2 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
    | test3 |          1 | idx_eml  |            1 | email       | A         |           2 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
    +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
  3. 插入测试数据
    insert into test3(id, name, sex, email, age) values(1, 'tom', 1, '[email protected]', 16),(2, 'lucy', 2, '[email protected]', 18); 
  4. 分析查询计划
  • 根据主键 id 分析,由于 id 为 int 类型,设置的NOT NULL ,key_len为 4 
    mysql> explain select * from test3 where id =1; 
    +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
    | id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
    +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
    |  1 | SIMPLE      | test3 | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
    +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

 

  • 根据 age 分析,由于 age 为 tinyint 类型,可以为NULL ,key_len为 1 + 1  = 2
    mysql> explain select * from test3 where age =16;
    +----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+
    | id | select_type | table | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
    +----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+
    |  1 | SIMPLE      | test3 | NULL       | ref  | idx_age       | idx_age | 2       | const |    1 |   100.00 | NULL  |
    +----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+

 

  • 根据 name 分析,由于 name 为 varchar 类型,设置NOT NULL ,key_len为 20 *3 + 2  =  62 
    mysql> explain select * from test3 where name = 'tom'; 
    +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
    | id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
    +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
    |  1 | SIMPLE      | test3 | NULL       | ref  | idx_name      | idx_name | 62      | const |    1 |   100.00 | NULL  |
    +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+

 

  • 根据 email 分析,由于 email  为 varchar 类型,没有设置NOT NULL  ,key_len为 20 *3 + 2 + 1  =  63
    mysql> explain select * from test3 where email = '[email protected]'; 
    +----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+
    | id | select_type | table | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
    +----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+
    |  1 | SIMPLE      | test3 | NULL       | ref  | idx_eml       | idx_eml | 63      | const |    1 |   100.00 | NULL  |
    +----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+

 

(二)、编码为utf8mb4
  1. 创建测试表
     CREATE TABLE `test4` (
      `id` int NOT NULL,
      `name` varchar(20) NOT NULL COMMENT '姓名',
      `sex` tinyint NOT NULL COMMENT '性别,1:男,2:女',
      `email` varchar(20) DEFAULT NULL,
      `age` tinyint default 0,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
  2. 创建索引
    mysql> show index from test3;
    +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
    | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
    +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
    | test3 |          0 | PRIMARY  |            1 | id          | A         |           2 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
    | test3 |          1 | idx_age  |            1 | age         | A         |           2 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
    | test3 |          1 | idx_name |            1 | name        | A         |           2 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
    | test3 |          1 | idx_eml  |            1 | email       | A         |           2 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
    +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
  3. 插入测试数据
    1 insert into test4(id, name, sex, email, age) values(1, 'tom', 1, '[email protected]', 16),(2, 'lucy', 2, '[email protected]', 18); 
  4. 分析查询计划
  • 根据 name 分析,由于 name 为 varchar 类型,设置NOT NULL ,key_len为 20 *4 + 2  =  82
    mysql> explain select * from test4 where name = 'tom'; 
    +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
    | id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
    +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
    |  1 | SIMPLE      | test4 | NULL       | ref  | idx_name      | idx_name | 82      | const |    1 |   100.00 | NULL  |
    +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+

 

  • 根据 email 分析,由于 email 为 varchar 类型,没有设置NOT NULL ,key_len为 20 *4 + 2 +1  =  83
    mysql> explain select * from test4 where email = '[email protected]'; 
    +----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
    | id | select_type | table | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra |
    +----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
    |  1 | SIMPLE      | test4 | NULL       | ref  | idx_email     | idx_email | 83      | const |    1 |   100.00 | NULL  |
    +----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+

 

标签:test3,name,idx,len,key,MySQL,NULL,id
From: https://www.cnblogs.com/it-ws/p/17453801.html

相关文章

  • 无法删除索引 1553 - Cannot drop index ‘fk_pptn_r_emtc‘: needed in a foreign ke
    标题标题:解决问题:1553-无法删除索引‘fk_pptn_r_emtc’:外键约束需要引言:在数据库管理中,经常会遇到各种问题和错误。其中之一是"1553-无法删除索引‘fk_pptn_r_emtc’:外键约束需要"错误。这个错误可能会导致数据库操作受阻,影响系统的正常运行。在本篇博客中,我们将深入探讨这......
  • MySQL导入SQL文件过大或连接超时的解决办法
    使用navicatmysql导入sql总会出现许多的error,导致导入的sql不完整。这个问题说到底是mysql执行时间、文件大小限制,只要这样配置下,就可以正常oerror导入了。setglobalmax_allowed_packet=100000000;setglobalnet_buffer_length=100000;SETGLOBALinteractive_timeout=2......
  • docker安装mysql
    1.从DockerHub下载MySQL镜像:dockerpullmysql2.运行MySQL容器,并将主机的3306端口映射到容器的3306端口:dockerrun-p3306:3306--namemysql-eMYSQL_ROOT_PASSWORD=your_password-dmysql其中,--namemysql指定容器的名称为mysql,-p3306:3306将容器的3306端口映射......
  • MYSQL:无锁变更工具Pt-online-schema-change
    一、MySQL常用的无锁变更工具OnlineSchemaChange:OnlineSchemaChange(OSC)工具是MySQL官方提供的一种无锁变更工具,它可以在不停止MySQL服务器的情况下对表结构进行修改。OSC利用了InnoDB存储引擎的特性,使用复制和重放日志的方式来实现无锁变更。pt-online-schema-change:pt-......
  • MySQL同一字段取反处理
    在改BUG中遇到了这个问题一张表的字段比如是否可用标志取值取反了本来是0的写成了1 1写成了0可使用下面的语句 UPDATE(表名)SET字段名= CASE字段名WHEN (值) THEN(值)WHEN (值) THEN(值)WHEN (值) THEN(值)ENDWHERE(条件)举个例子:UPDA......
  • GOM引擎启动后M2提示Invalid filename报错的解决办法
    在架设一个GOM引擎版本的时候,启动M2就提示Invalidfilename,之后的网关就没有办法再启动了,研究了半天也终于是弄好了,其实也简单,就是路径设置的不对,所以无法完成启动,很多人以为在控制台设置好了路径生成就行了,实际上有时候MIR200\!Setup.txt内的路径并未完全更改,所以导致报错。好了,......
  • 首次进入Mysql修改密码报“The MySQL server is running with the --skip-grant-table
    第一次安装完mysql,修改默认密码的时候,报“TheMySQLserverisrunningwiththe--skip-grant-tablesoptionsoitcannotexecutethisstatement”。先刷新mysql然后再重新修改密码即可。mysql>ALTERUSER'root'@'localhost'IDENTIFIEDBY'123456';ERROR1290(H......
  • keydb 6.3. 3发布
    就在最近keydb发布了6.3.3版本,属于最后支持一些老版本linux的系统了(比如centos7,ubuntu16,18),核心主要是rocksdb对于系统支持的问题同时此版本也进行了不少功能上的fix,最好还是进行系统升级吧,尤其是centos7现在越来越多的软件运行有问题了参考资料https://docs.keydb.dev/htt......
  • 关于MySQL数据库的外键作用及如何创建?
    一、外键的作用:外键的主要作用是保证数据的一致性和完整性,并且减少数据冗余。主要体现在以下两个方面:1、阻止执行从表插入新行,其外键值不是主表的主键值便阻止插入。从表修改外键值,新值不是主表的主键值便阻止修改。主表删除行,其主键值在从表里存在便阻止删除(要想删除,必须先删除......
  • mac电脑git配置sshKey后不能下拉代码
    配置全局gitconfig--globaluser.name用户名gitconfig--globaluser.email邮箱gitconfig--list//查看配置的用户ssh-keygen-trsa-C248******@qq.com//输入邮箱,一直回车(遇到y/n,选y)ls-al~/.ssh//查看是否生成了私钥,公钥(id_rsa是私钥id_rsa.pub是公钥)......