前のページ
tech

データによって実行計画に違いに差が出るのはなぜ？？

実行計画について

DB はオプティマイザと呼ばれる機能によって問い合わせされたクエリの最適化を行います

これが実行計画と呼ばれるものです

具体的には、EXPLAINを使うことでその最適化の計画をみることができます

例えば、

EXPLAIN SELECT * FROM users where name = 'hoge';

という式によって、以下のような実行計画を確認することができます

+----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key          | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | users | NULL       | ref  | ix_user_name  | ix_user_name | 47      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

実行計画がどのようなものか分かってきたところで、この記事のタイトルにあった「データによって実行計画に違いに差が出るのはなぜ？？」についてへと説明を進めていこうと思います

テーブルの準備

まずはデータによって実行計画の差を出すためにデータの準備をします

DB の環境は docker でデータの準備をしておきました
https://github.com/mergitto/docker-index-sample

リポジトリの README を参考に DB を作成しておいてください

これから利用するテーブルは users は以下のようなスキーマをもっていることとします。

CREATE TABLE `users`(
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR (45) NOT NULL,
  `age` INT (11) NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `ix_user_name` (`name` ASC),
  INDEX `ix_user_age` (`age` ASC)
);

users テーブルの中身には以下のようなシンプルなデータを入れています

INSERT INTO `sample`.`users` (`id`,`name`,`age`) VALUES (12,'test',10);
INSERT INTO `sample`.`users` (`id`,`name`,`age`) VALUES (13,'test1',20);
INSERT INTO `sample`.`users` (`id`,`name`,`age`) VALUES (14,'test2',30);
INSERT INTO `sample`.`users` (`id`,`name`,`age`) VALUES (15,'test3',35);
INSERT INTO `sample`.`users` (`id`,`name`,`age`) VALUES (16,'test4',33);
INSERT INTO `sample`.`users` (`id`,`name`,`age`) VALUES (17,'test5',37);
INSERT INTO `sample`.`users` (`id`,`name`,`age`) VALUES (18,'test6',38);
INSERT INTO `sample`.`users` (`id`,`name`,`age`) VALUES (19,'test7',50);
INSERT INTO `sample`.`users` (`id`,`name`,`age`) VALUES (20,'test8',70);
INSERT INTO `sample`.`users` (`id`,`name`,`age`) VALUES (21,'test9',80);
INSERT INTO `sample`.`users` (`id`,`name`,`age`) VALUES (22,'test10',90);
INSERT INTO `sample`.`users` (`id`,`name`,`age`) VALUES (23,'test11',100);

データの中身によって実行計画が変わる！？

  • データの確認
    • users.age には index を付与してあります

Imgur
Imgur

  • age を 90 ~ 99 の範囲指定で検索したときの実行計画

Imgur
Imgur

  • age を 30 ~ 39 の範囲指定で検索したときの実行計画

Imgur
Imgur

画像を見ていただいたらわかりますが、age の範囲を 90 ~ 100 で指定した時と、 30 ~ 40 で指定した時では type の値が違いますね

  • 90 ~ 100: range
  • 30 ~ 40: ALL

age には index を付与しているので、どちらの範囲指定でも index が使われて欲しいのですが、30 ~ 40 の場合にはフルスキャンが走ってしまいます

なぜこのような 2 つの範囲指定で type の値が変わってしまうのでしょうか

index を効かすためには検索結果が全体のデータの 30%未満であること

index というのはよく本の目次に例えられます
その本の中身の章立てが細かく分かれていればいるほど、目次の意味が出てきますね。特定の章を見つければその中に目当ての情報があることがわかりますから。

逆に、目次が大雑把に作られていたらどうでしょうか。例えば、500p の本で 2 章しかなかったらどうでしょう。目当ての情報が 1 章の方かもと思い開いてみると、0p ~ 250p まで範囲があり全く絞り込めないですよね。こうなってしまうと、とりあえず前から順番に読まざるを得なくなります。発狂ものですね。

MySQL の index では B tree-index というアルゴリズムでインデックスが作成されます。これについてはクックパッドさんが書かれた記事がわかりやすいと思います
参照: MySQL with InnoDB のインデックスの基礎知識とありがちな間違い

MySQL の公式を読んでみると、

https://dev.mysql.com/doc/refman/5.6/ja/where-optimizations.html

オプティマイザがテーブルスキャンを使用する方が効率的であると判断しないかぎり、各テーブルインデックスがクエリーされ、最適なインデックスが使用されます。かつて、スキャンは、最適なインデックスがテーブルの 30% 超にまたがっているかどうかに基づいて使用されていましたが、固定のパーセンテージによって、インデックスを使用するか、スキャンを使用するかの選択が決定されなくなりました。

と記述されています。このリファレンスのバージョンは 5.6 ですが、それ以前までは 30%を目安として、フルスキャンを走らせるのか、index を使うのかをオプティマイザが判定していたみたいです。また、こうも書かれていました

現在のオプティマイザは複雑になり、テーブルサイズ、行数、I/O ブロックサイズなどの追加の要因に基づいて推定します。

つまり、現在は具体的な数値で決められるほど単純な最適化では無くなったということです。具体的な数字はなくなりましたが、20 ~ 30% 周辺は index が用いられるかどうかの判断材料になるかもしれません。

さて、ここで今回用いたデータを振り返ってみましょう

Imgur
Imgur

データの範囲を 10 ずつで区切ってみた時のデータの割合を出してみました（少数の兼ね合いで 100%にならないかったです）

 0 ~  9:  => 00
10 ~ 19: 1 => 8%
20 ~ 29: 1 => 8%
30 ~ 39: 5 => 42%
40 ~ 49: 0 => 0%
50 ~ 59: 1 => 8%
60 ~ 69: 0 => 0%
70 ~ 79: 1 => 8%
80 ~ 89: 1 => 8%
90 ~ 99: 1 => 8%
100 ~ 109: 1 => 8%

これをみると、30 ~ 39 の間だけ全体のデータ量に対して 42%を占めるデータの量になっていますね。

ここまできたら、先ほどのクエリで、index が使われたり使われなかったりした理由が分かったのではないでしょうか

EXPLAIN SELECT * FROM sample.users WEHRE age between 30 and 39;
EXPLAIN SELECT * FROM sample.users WEHRE age between 90 and 99;

30 ~ 39 の範囲選択の場合に type=ALL でテーブルフルスキャンが走っていたのはテーブル全体に対して 42%という割合のデータ量があり、オプティマイザがインデックスを使うよりもシーケンシャルに読み込む方が良い！と判断したのです

EXPLAIN SELECT * FROM sample.users WEHRE age between 30 and 39;

対照的に 90 ~ 99 の範囲には全体に対して 8%という割合のデータ量だったため、インデックスを利用した方が検索効率が良い！とオプティマイザが判断したのですね。

EXPLAIN SELECT * FROM sample.users WEHRE age between 90 and 99;

まとめ

いかがだったでしょうか。何も考えずに index をつけるだけで終わっていたら今回の場合のようなことに気付けなかったですよね。

開発環境と本番環境では入るデータの量も質も、傾向も変わります。開発環境でうまくいっているからと言って本番環境でもうまくいくわけではないということを念頭に DB 設計をしていけるようになりたいですね。

© 2019 - 2024 onemuri.space
このページでは、Google Analyticsを利用しています。