MySQL query plan key_ How to calculate len

Recently, I was analyzing the execution efficiency of a joint index , among key_len You can analyze this sql Which columns of the union index can be used . Collect data afterwards and summarize .

key_len explain

desc Medium key_len Indicates the index length used , It's in bytes . According to this value , You can judge the index usage , Especially when using joint index , Determine how many index fields can be used by query .

key_len The rules

• If the index column is Character , such as VARCHAR(M) The space occupied by the type is M * Maxlen .

  • Character type calculation key_len The character set needs to be considered ,Maxlen Represents the maximum number of bytes required for a character in a character set ,utf8 Maxlen by 3,utf8mb4 Maxlen by 4.
  • If the index column becomes longer ( such as varchar,varbinary), On the basis of the space occupied by the index column data type itself 2 , such as varchar(3) utf8mb4 null, Then the actual occupied space is 3*4+2 = 14.
  • If the index column can be empty , Add... To the space occupied by the index column data type itself 1. such as varchar(3) utf8mb4 not null, Then the actual occupied space is 3*4+2+1 = 15.

• If the index column is Integers See the following figure for the type ：
  

• If the index column is Date and time See the following figure for the type ：
  
  MySQL5.6.4 And later ,TIME、DATETIME、TIMESTAMP These types add pairs of milliseconds 、 Subtle support . Due to milliseconds 、 It's less than a microsecond 1 second , So it is also called decimal second ,MySQL Most support 6 Precision of decimal second , such as DATETIME(0) It means accurate to seconds ,DATETIME(3) It is accurate to milliseconds ,DATETIME(5) It means accurate to 10 Microsecond . So if you're using TIME、DATETIME、TIMESTAMP These types of times are accurate to decimal seconds , Then you need additional storage space , Different decimal second precision requires different storage space , The following table ：
  

• If the index column is Floating point numbers See the following figure for the type ：
  

• If the index column is Fixed-point number type ：
  DECIMAL(M, D) M It refers to the total number of digits ,D It refers to the number of decimal places .M The range is 1 - 65,D The range is 0 - 30, And D The value of cannot exceed M. Fixed point numbers are exact decimals , The numbers before and after the decimal point are stored separately , And in 9 Bits are 1 Group , use 4 Byte save , If lower 9 digit , The number of bytes required is as follows ：
  
  for example ：

• decimal(20,6)=> To the left of the decimal point 14 position , To the right of the decimal point 6 position => The group to the left of the decimal point is 5 + 9, need 3 Bytes +4 Byte store , A group to the right of the decimal point , need 3 Byte store => All in all 10 Bytes

• decimal(18,9)=> To the left of the decimal point 9 digit , To the right of the decimal point 9 digit => Separate use 4 Byte store => A common need 8 Bytes

• decimal(18,2)=> To the left of the decimal point 16 digit , To the right of the decimal point 2 digit => The group is 7 + 9, need 8 Byte store , To the right of the decimal point 1 Byte store => A common need 9 Bytes

key_len actual combat

Finally, use a practical example to calculate which columns are cited by the joint cable .

Joint index ：

create index idx_user_id_task_code_is_deleted_task_complete_status
    on u_task_progress (user_id, task_code, is_deleted, task_complete_status);

Of each column ddl Definition ：

user_id                  bigint(50)            not null comment ' user ID'
task_code                varchar(50)           not null comment ' Task code '
is_deleted               tinyint(10) default 0 not null comment ' Whether or not to delete , Default 0,1: Delete '
task_complete_status     tinyint(10) default 1 not null comment ' Task completion status ,1: Finishing ,2: Completed , Default 0'

some sql After execution key_len by 211, Now let's analyze which columns are executed by this joint index .

• user_id by bigint not null, So it is 8
• task_code For variable length characters mb4 not null, So it is 4*50+2=202
• is_deleted by tinyint not null, So it is 1

Finally, it adds up to 211, So only the first three index fields are left .key_len It is helpful to know whether the index we set is reasonable , Is it convenient ？

Acknowledgement

MySQL Query plan key_len I know it all
Mysql Indexes key_len The calculation method is the strongest analysis