Interpreting the Key_len in the explain implementation plan

interpreting the Key_len in the explain implementation plan

A column in the explain execution plan Key_len is used to indicate how many bytes are in the selected index length in this query, and we can often use this to determine how many columns of the federated index are selected.

Here Key_len the calculation rule of size is: Generally, Key_len equals the byte length of the indexed column type, for example the int type is 4-bytes,bigint 8-bytes; if it is a string type, you also need to consider the character set factor, for example: CHAR (30) UTF8 is key_len at least 90-bytes; If the column type is defined to allow Nulls, its key_len needs to be 1-bytes; If the column type is a variable-length type, such as VARCHAR (Text\blob does not allow an entire column to create an index, if a partial index is created, is also considered a dynamic column type), its key_len also needs to be added 2-bytes;

In summary, look at the following examples:

column Type	Key_len	Notes
ID int	Key_len = 4+1 = 5	Allow null, plus 1-byte
ID int NOT NULL	Key_len = 4	Null not allowed
User char (UTF8)	Key_len = 30*3+1	Allow null
User varchar () NOT NULL UTF8	Key_len = 30*3+2	Dynamic column type, plus 2-bytes
User varchar (UTF8)	Key_len = 30*3+2+1	Dynamic column type, add 2-bytes; allow null, then add 1-byte
Detail text (UTF8)	Key_len = 30*3+2+1	The text column intercepts the part, which is treated as a dynamic column type, plus 2-bytes, and allows null

Note, Key_len only indicates the index column that is selected for conditional filtering in the where, and is the indexed column that does not contain the order By/group by this section is selected.
For example, there is a federated index IDX1 (C1, C2, C3), 3 columns are int not NULL, and in the following SQL execution plan, the Key_len value is 8 instead of 12:

SELECT ... WHERE c1=? and c2=? Order by C1;