• 如何精确计算mysql的索引长度


    一、前言

    MySQL Innodb 对于索引长度的限制为 767 字节,并且UTF8mb4字符集是4字节字符集,则 767字节 / 4字节每字符 = 191字符(默认索引最大长度),所以在varchar(255)或char(255) 类型字段上创建索引会失败,提示最大索引长度为767字节。

    二、环境准备

    创建表

    CREATE TABLE `t` (
      `a` int(5) NOT NULL,
      `b` int(5) NOT NULL,
      `c` int(2) NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

    创建联合索引

    create index idx_all on t(a,b,c);

    三、试验

      1、执行下面两条语句

    explain select * from t where a=1 and c=1;
    explain select * from t where a=1 and b=1;

    语句1的执行计划为:

     语句2的执行计划为:

      2、思路

    查看表的索引

    show index from t;

    将组合索引想成书的一级目录、二级目录、三级目录,如index(a,b,c),相当于a是一级目录,b是一级目录下的二级目录,c是二级目录下的三级目录。要使用某一目录,必须先使用其上级目录,除了一级目录除外。

    所以

    where a=1 and c=1只使用了一级目录,c在三级目录,没有使用二级目录,那么三级目录就没法使用

    where a=1 and b=1只使用了一级目录、二级目录。

    于是第二条查询的key_len更大。

    但是,具体key_len怎么计算的,上面怎样计算出是4和8的呢?

      3、索引长度的计算规则

    1.所有的索引字段,如果没有设置not null,则需要加一个字节。
    
    2.定长字段,int占四个字节、date占三个字节、char(n)占n个字符。
    
    3.对于变成字段varchar(n),则有n个字符+两个字节。
    
    4.不同的字符集,一个字符占用的字节数不同。latin1编码的,一个字符占用一个字节,gbk编码的,一个字符占用两个字节,utf8编码的,一个字符占用三个字节。
    
    5.索引长度 char()、varchar()索引长度的计算公式:
    
    (Character Set:utf8mb4=4,utf8=3,gbk=2,latin1=1) * 列长度 + 1(允许null) + 2(变长列)

      4、再看sql语句

    SQL1

    select * from t where a=1 and c=1;

    执行计划:

    key_len = 4 (a是int类型 4字节) = 4

    所以得出结论,是命中了索引列a

    SQL2

    explain select * from t where a=1 and b=1;

    执行计划:

    key_len =4 (a是int类型 4字节) + 4(b是int类型 4字节)  = 8

    所以得出结论,是命中了索引列a和索引列b

    四、再实验

      1、创建表

    create table t2(id int(5) not null,name varchar(5) not null) engine=innodb default charset=latin1;

      2、创建索引

    create index idx_2 on t2(id,name);

      3、计算索引字段

    sql为:

    explain select * from t2 where name="001" and id=1;

     执行计划为:

     key_len =4 (id是int类型 4字节) + 5(name是varcharr类型 5字节)*1(字符集是latin1)+变列长2  = 11

    知道、想到、做到、得到
  • 相关阅读:
    精算师的前世今生
    失落的C语言结构体封装艺术
    关于联合的一些介绍
    变量的声明和定义
    C/C++内存分配区
    探寻周瑜“前世今生”
    SpringBoot中使用AOP
    springBoot中的事物管理
    springBoot整合多数据源
    spingBoot整合mybatis+generator+pageHelper
  • 原文地址:https://www.cnblogs.com/Durant0420/p/15330363.html
Copyright © 2020-2023  润新知