[go: up one dir, main page]
More Web Proxy on the site http://driver.im/

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 で確認することが重要