MySQLクエリプランでken_lenの値を計算する方法

key_lenの意味

MySQL では、次に示すように、explain を使用して SQL ステートメントがたどったパスを表示できます。

mysql> テーブル t(a int 主キー、b int not null、c int not null、インデックス(b)) を作成します。
 クエリは正常、影響を受けた行は 0 行 (0.01 秒)
 mysql> explain t から b を選択します。
 +----+-------------+-------+-------+---------------+-------+-------+------+------------+------------+
 | id | select_type | テーブル | タイプ | possible_keys | key | key_len | ref | 行 | 追加 |
 +----+-------------+-------+-------+---------------+-------+-------+------+------------+------------+
 | 1 | SIMPLE | t | インデックス | NULL | b | 4 | NULL | 1 | インデックスの使用 |
 +----+-------------+-------+-------+---------------+-------+-------+------+------------+------------+
 セット内の 1 行 (0.00 秒)

このうち、key_len は使用されるインデックスの長さ（バイト単位）を表します。上記の例では、int 型が 4 バイトを占め、インデックスには 1 つの列のみが含まれるため、key_len は 4 になります。

共同インデックスは次のようになります。

mysql> テーブル t を変更し、インデックス ix(b, c) を追加します。
クエリは正常、影響を受けた行は 0 行 (0.03 秒)
レコード: 0 重複: 0 警告: 0
mysql> explain select b, c from t;
+----+-------------+-------+-------+---------------+-------+-------+------+------------+------------+
| id | select_type | テーブル | タイプ | possible_keys | key | key_len | ref | 行 | 追加 |
+----+-------------+-------+-------+---------------+-------+-------+------+------------+------------+
| 1 | SIMPLE | t | インデックス | NULL | ix | 8 | NULL | 1 | インデックスの使用 |
+----+-------------+-------+-------+---------------+-------+-------+------+------------+------------+
セット内の 1 行 (0.00 秒)

結合インデックス ix には 2 つの列が含まれており、両方が使用されているため、ken_len は 8 になります。

この時点で、key_lenの意味はすでに理解できており、これ以上言うことはないように思えます。しかし、MySQLにおけるkey_lenの計算には、まだ注意すべき点がたくさんあります。

たとえば、列 b の NOT NULL 制約を削除すると、ken_len は次のように予想したものと異なります。

mysql> テーブル t を変更して b int を変更します。
クエリは正常、影響を受けた行は 0 行 (0.01 秒)
レコード: 0 重複: 0 警告: 0
 
mysql> explain t から b を選択します。
+----+-------------+-------+-------+---------------+-------+-------+------+------------+------------+
| id | select_type | テーブル | タイプ | possible_keys | key | key_len | ref | 行 | 追加 |
+----+-------------+-------+-------+---------------+-------+-------+------+------------+------------+
| 1 | SIMPLE | t | インデックス | NULL | b | 5 | NULL | 1 | インデックスの使用 |
+----+-------------+-------+-------+---------------+-------+-------+------+------------+------------+
セット内の 1 行 (0.00 秒)

MySQL の key_len の計算ルール

MySQL では、key_len の計算規則は次のとおりです。

列が空になる可能性がある場合は、データ型が占めるバイト数に 1 を加算します。たとえば、int 型が空になる可能性がない場合、key_len は 4 になり、空になる可能性がある場合は、key_len は 5 になります。
列が可変長の場合は、データ列が占める基本バイト数に 2 を加算します。たとえば、varbinary(10) が空にできない場合、key_len は 10 + 2 になります。空にできる場合、key_len は 10+2+1 になります。
文字型の場合は、文字セットも考慮する必要があります。たとえば、列が varchar(10) として定義され、utf8 であり、空にできない場合、key_len は 10 * 3 + 2 になります。空にできる場合、key_len は 10*3+2+1 になります。
また、小数列の計算方法は上記と同じです。空になる可能性がある場合は、データ型が占めるバイト数に 1 を加算します。ただし、小数自体が占めるバイト数の計算はより複雑です。

公式文書によると、小数は、decimal(M,D) と定義されています。ここで、M は合計桁数、D は小数点以下の桁数です。小数点の前後の数字は別々に格納され、9桁を1つにまとめて4バイトを使用して格納されます。9桁未満の数値の場合、必要なバイト数は次のとおりです。

残りの桁数バイト数
-----------------------------
|0 |0 |
|1-2 |1 |
|3-4 |2 |
|5-6 |3 |
|7-9 |4 |
-----------------------------

例えば：

小数点(20,6) => 小数点の左側に14桁、小数点の右側に6桁 => 小数点の左側の数字は5 + 9にグループ化され、保存には3バイト + 4バイトが必要で、小数点は保存に3バイトにグループ化され => 合計10バイトが必要です
小数点(18,9) => 小数点の左側に9桁、小数点の右側に9桁 => それぞれ4バイトを使用して格納 => 合計8バイト
小数点の左側に16桁、小数点の右側に2桁 => 7 + 9としてグループ化され、保存に8バイト必要、小数点の右側に1バイト => 合計9バイト必要

key_lenによる結合インデックスを分析する

以下に示すように、4 つの列 a、b、c、d を含むテーブル t を定義します。

mysql> テーブル t\G の作成を表示します
************************** 1. 行 ****************************
    表: t
テーブルの作成: CREATE TABLE `t` (
 `a` int(11) NULLではない、
 `b` int(11) デフォルト NULL,
 `c` int(11) デフォルト NULL,
 `d` int(11) デフォルト NULL,
 主キー (`a`)、
 キー `ix_x` (`b`,`d`,`c`)
) エンジン=InnoDB デフォルト文字セット=utf8
セット内の 1 行 (0.00 秒)

実行される SQL ステートメントは次のようになります。

b = 5、d = 10 の場合に t から a を選択し、c で並べ替えます。

インデックス ix_x(b,d,c) があり、explain を通じて次の出力が返されるとします。

mysql> explain select a from t where b = 5 and d = 10 order by c;
+----+-------------+-------+-------+---------------+-------+-----------+------+----------------------------+
| id | select_type | テーブル | タイプ | possible_keys | key | key_len | ref | 行 | 追加 |
+----+-------------+-------+-------+---------------+-------+-----------+------+----------------------------+
| 1 | SIMPLE | t | ref | ix_x | ix_x | 10 | const,const | 1 | where の使用; index の使用 |
+----+-------------+-------+-------+---------------+-------+-----------+------+----------------------------+
セット内の 1 行 (0.00 秒)

ご覧のとおり、クエリステートメントは結合インデックスの b 列と d 列を使用してデータをフィルター処理します。

定義する結合インデックスが `ix_x(b, d, c)` ではなく `ix_x(b, c, d)` の場合、explain によって取得される入力は次のようになります。

mysql> テーブル t を変更してインデックス ix_x を削除します。
mysql> テーブル t を変更し、インデックス ix_x(b, c, d) を追加します。
mysql> explain select a from t where b = 5 and d = 10 order by c;
+----+-------------+-------+-------+---------------+-------+------+------+------+---------------------------+
| id | select_type | テーブル | タイプ | possible_keys | key | key_len | ref | 行 | 追加 |
+----+-------------+-------+-------+---------------+-------+------+------+------+---------------------------+
| 1 | SIMPLE | t | ref | ix_x | ix_x | 5 | const | 2 | where の使用; index の使用 |
+----+-------------+-------+-------+---------------+-------+------+------+------+---------------------------+
セット内の 1 行 (0.00 秒)

key_len は 5 です。つまり、結合インデックスの最初の列のみが使用されます。結合インデックスにはクエリするすべての列が含まれていますが、定義順序のため、SQL ステートメントはインデックスを十分に活用できないことがわかります。

以下もご興味があるかもしれません: