MySQL で複合インデックスを作成する際には必ず Explain の key_len を確認すべきという話
「Rails Developers Meetup 2018 で「MySQL/InnoDB の裏側」を発表しました」でちゃんと触れられてないので今更ながら key_len について補足します。発表で触れた内容については言及しないので、storage engine や B+ tree といった用語がよくわからない方は発表内容を参照してください。
なお、MySQL のバージョンは 5.7.38 です。
mysql> SELECT @@version;
+-----------+
| @@version |
+-----------+
| 5.7.38 |
+-----------+
1 row in set (0.00 sec)
事前準備
sample-data-railsdm-2018 の orders テーブルを少しいじって、キャンセル時刻（canceled_at）、配送予定時刻（delivered_at）カラムを追加してみます。また、同じ商品に対する注文の数を増やしてみます。
具体的には次のような変更を加えます。
diff --git a/Rakefile b/Rakefile
index 6d1d89d..01df6dd 100644
--- a/Rakefile
+++ b/Rakefile
@@ -35,8 +35,8 @@ namespace :db do
user_count = 100
shop_count = 10
- product_count = 5_000
- order_count = 10_000
+ product_count = 10
+ order_count = 200_000
prng = Random.new(42)
Faker::Config.random = prng
@@ -71,11 +71,13 @@ namespace :db do
product_id = prng.rand(product_count) + 1
count = (prng.rand(5) + 1)
product = id_to_product[product_id]
+ created_at = Faker::Time.between_dates(from: product.started_at, to: product.ended_at)
orders << {
user_id: user_id,
product_id: product_id,
count: count,
- created_at: Faker::Time.between_dates(from: product.started_at, to: product.ended_at),
+ created_at: created_at,
+ delivered_at: created_at + 86400 * (prng.rand(7) + 1),
}
if orders.size == 1_000
Order.insert_all!(orders)
diff --git a/Schemafile b/Schemafile
index b40e65f..1327c6d 100644
--- a/Schemafile
+++ b/Schemafile
@@ -19,9 +19,12 @@ add_foreign_key 'products', 'shops', name: 'fk_products_shops'
create_table 'orders', unsigned: true, force: :cascade do |t|
t.references 'user', unsigned: true, null: false
- t.references 'product', unsigned: true, null: false
+ t.references 'product', unsigned: true, null: false, index: false
t.integer 'count', unsigned: true, null: false
- t.datetime 'created_at', precision: 0, null: false
+ t.datetime 'created_at', precision: 0, null: false
+ t.datetime 'canceled_at', precision: 0
+ t.datetime 'delivered_at', precision: 0, null: false
end
+add_index 'orders', ['product_id', 'canceled_at', 'delivered_at'], name: 'ix_product_id_canceled_at_delivered_at'
add_foreign_key 'orders', 'users', name: 'fk_orders_users'
add_foreign_key 'orders', 'products', name: 'fk_orders_products'
最終的なテーブルの定義は次のとおりです。
CREATE TABLE `orders` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`user_id` bigint(8) unsigned NOT NULL,
`product_id` bigint(8) unsigned NOT NULL,
`count` int(10) unsigned NOT NULL,
`created_at` datetime NOT NULL,
`canceled_at` datetime DEFAULT NULL,
`delivered_at` datetime NOT NULL,
PRIMARY KEY (`id`),
KEY `ix_product_id_canceled_at_delivered_at` (`product_id`,`canceled_at`,`delivered_at`),
KEY `index_orders_on_user_id` (`user_id`),
CONSTRAINT `fk_orders_products` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`),
CONSTRAINT `fk_orders_users` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=200001 DEFAULT CHARSET=utf8;
key_len とは何か
key_len は explain に表示される情報の 1 項目で、storage engine が B+ tree を辿る際にインデックスの先頭何バイトを使ったかを意味します。
例えば、explain の結果が次のようになったとします。
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: orders
partitions: NULL
type: range
possible_keys: ix_product_id_canceled_at_delivered_at
key: ix_product_id_canceled_at_delivered_at
key_len: 19
ref: NULL
rows: 17190
filtered: 100.00
Extra: Using index condition
1 row in set, 1 warning (0.00 sec)
ix_product_id_canceled_at_delivered_at は (product_id, canceled_at, delivered_at) から成る複合インデックスで、product_id は非 null な bigint なので 8 バイト、canceled_at は nullable な datetime なので 6 バイト、delivered_at は 非 null な datetime なので 5 バイトであるため、合計 19 バイトです。
つまり、上記の explain の結果から、この SQL はインデックスを構成する 3 カラム全ての情報が使われていることがわかります。もし他の SQL で key_len が 8 になったら、それは 1 カラムの情報しか使われていないことを意味します。同様に、key_len が 14 なら 2 カラムです。
なお、各型のサイズは MySQL :: MySQL 5.7 Reference Manual :: 11.7 Data Type Storage Requirements に記載されています。
nullable なカラムは通常の型のサイズに加えて 1 バイトか 2 バイト必要です。
An SQL NULL value reserves one or two bytes in the record directory. An SQL NULL value reserves zero bytes in the data part of the record if stored in a variable-length column. For a fixed-length column, the fixed length of the column is reserved in the data part of the record. Reserving fixed space for NULL values permits columns to be updated in place from NULL to non-NULL values without causing index page fragmentation.
cf. MySQL :: MySQL 5.7 Reference Manual :: 14.11 InnoDB Row Formats
key_len の例
例えば、次の商品を取得するのに必要な SQL を考えます。
- 商品 ID が 1
- キャンセルされていない
- 配送予定時刻が 2018-04-01 00:00:00 以降
SELECT
*
FROM
orders
WHERE
product_id = 1
AND canceled_at IS NULL
AND delivered_at >= '2018-04-01 00:00:00'
;
この SQL の explain 結果は次のとおりです。
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: orders
partitions: NULL
type: range
possible_keys: ix_product_id_canceled_at_delivered_at
key: ix_product_id_canceled_at_delivered_at
key_len: 19
ref: NULL
rows: 17190
filtered: 100.00
Extra: Using index condition
1 row in set, 1 warning (0.00 sec)
これは key_len の説明で使用した explain 結果で、前述のとおり、この SQL ではインデックスを構成する 3 カラム全ての情報が使われていることがわかります。
ここで、元の SQL から canceled_at の条件を外してみます。
SELECT
*
FROM
orders
WHERE
product_id = 1
AND delivered_at >= '2018-04-01 00:00:00'
;
すると、explain の結果は次のように変わります。
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: orders
partitions: NULL
type: ref
possible_keys: ix_product_id_canceled_at_delivered_at
key: ix_product_id_canceled_at_delivered_at
key_len: 8
ref: const
rows: 42826
filtered: 33.33
Extra: Using index condition
1 row in set, 1 warning (0.01 sec)
key_len が 8 になったので、product_id の情報のみが使われていることがわかります。canceled_at が NULL のレコードも非 NULL のレコードも全て取得する必要があるので当然ですね。ICP で delivered_at の条件が使われるので、クラスタインデックスへのアクセスは減らせますが、key_len が 19 の場合に比べると B+ tree から受けられる恩恵は少ないです。
もしインデックスを最大限利用したい場合は (product_id, delivered_at) の複合インデックスを別途作成する必要があります。
今度は次のような SQL を考えます。
SELECT
*
FROM
orders
INNER JOIN
products
ON
orders.product_id = products.id
WHERE
canceled_at IS NULL
AND delivered_at <= '2017-01-01 00:00:00'
AND shop_id = 3
;
この SQL の explain 結果は次のとおりです。
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: products
partitions: NULL
type: ref
possible_keys: PRIMARY,ix_shop_id_ended_at
key: ix_shop_id_ended_at
key_len: 8
ref: const
rows: 3
filtered: 100.00
Extra: NULL
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: orders
partitions: NULL
type: ref
possible_keys: ix_product_id_canceled_at_delivered_at
key: ix_product_id_canceled_at_delivered_at
key_len: 14
ref: railsdm2018.products.id,const
rows: 22179
filtered: 33.33
Extra: Using index condition
2 rows in set, 1 warning (0.00 sec)
まず最初に、products テーブルから shop_id = 3 にマッチするレコードを取得しており、rows の情報からマッチしたレコードは 3 レコードです。
次に、取得した product レコードごとに、orders テーブルから条件を満たすレコードを取得しています。ところが、ix_product_id_canceled_at_delivered_at の key_len は 14 であり、delivered_at の情報が使われないことがわかります。実は delivered_at <= '2017-01-01 00:00:00' の条件を満たすレコードは存在しないのですが、delivered_at の情報が使われないこともあって rows は 22179 と大きな数字になっています。
shop_id = 3 にマッチするレコードは次のとおりです。
mysql> SELECT id, name FROM products WHERE shop_id = 3;
+----+------------------------------+
| id | name |
+----+------------------------------+
| 10 | Eyeless in Gaza |
| 6 | The Soldier's Art |
| 2 | Let Us Now Praise Famous Men |
+----+------------------------------+
3 rows in set (0.00 sec)
MySQL の join は nested loop join であることから、orders テーブルからレコードを取得する処理は、上記の products.id を指定した次の SQL と等価と言えます。
SELECT
*
FROM
orders
WHERE
product_id IN (10, 6, 2)
AND canceled_at IS NULL
AND delivered_at <= '2017-01-01 00:00:00'
;
こちらの SQL の explain 結果は次のとおりです。
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: orders
partitions: NULL
type: range
possible_keys: ix_product_id_canceled_at_delivered_at
key: ix_product_id_canceled_at_delivered_at
key_len: 19
ref: NULL
rows: 3
filtered: 100.00
Extra: Using index condition
1 row in set, 1 warning (0.00 sec)
key_len が 19 なので、インデックスを構成する全てのカラムの情報が使われています。rows も 3 に減りました。
JOIN が絡むと key_len が 14 になってしまう理由はよくわかっていないですが、key_len を意識することの重要性がよくわかる例じゃないでしょうか。
なお、上記のようなケースでは、クライアント側で最初に shop_id = 3 にマッチする products.id を取得し、その情報を使って SQL を組み立てる必要があるんじゃないかと思います。key_len を指定するためのヒントがあれば良いんですが…
まとめ
- key_len は storage engine が B+ tree を辿る際にインデックスの先頭何バイトを使ったかを意味する
- 複合インデックスを作成しても、一部のカラムの情報しか使われないことがある
- 複合インデックスが意図したとおりに使われているか explain で確認することが重要