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

Latest commit

 

History

History
447 lines (348 loc) · 17.8 KB

Handler_read.md

File metadata and controls

447 lines (348 loc) · 17.8 KB
Raw

Handler_read_* を理解する

MySQL の SHOW STATUS LIKE 'handler_read% で取れる統計値が何を意味する数値なのかまとめます

  • Handler_read_first
  • Handler_read_last
  • Handler_read_key
  • Handler_read_next
  • Handler_read_prev
  • Handler_read_rnd
  • Handler_read_rnd_next

についてのモデル図とサンプルクエリを載せています

前置き

  • 簡略化のために InnoDB の primary key (クラスタインデックス) だけ元に図にしています
    • セカンダリインデックスも考えると大変そうなので ...
  • モデル図は kazeburo さんのグレートスライド http://www.slideshare.net/kazeburo/isucon-summerclass2014action2final をまねて書いています
  • モデル図はソースやマニュアル、B+木の仕組みから「おそらくこうだろう」という推測で書いています 👻 そのため正確さを欠いている点がある場合は了承ください (間違いはご指摘いただけるとありがたいです)

SEE ALSO

Handler_read_* の説明は MySQL :: MySQL 5.6 Reference Manual :: 5.1.6 Server Status Variables らへんを読むとよいでしょう。ただし、これ読んでも具体的な挙動が分からないってのが本音です ...

Handler_read_first

インデックスの最初のエントリを読み取るとカウントされる

2014-08-25 16 08 57

昇順で (フル)インデックススキャン する際に Handler_read_next と一緒に使われます

Handler_read_last

インデックスの最後のエントリを読み取るとカウントされる

2014-08-25 16 10 03

降順で (フル)インデックススキャン する際に Handler_read_prev と一緒に使われます

Handler_read_key

インデックスを使って対象のレコードを見つけるとカウントされる

2014-08-25 16 11 27

サンプルクエリ

SELECT * FROM foo WHERE id = 3
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | foo   | const | PRIMARY       | PRIMARY | 4       | const |    1 | NULL  |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+

クエリの種類として最速。

+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 0     |
| Handler_read_key      | 1     |
| Handler_read_last     | 0     |
| Handler_read_next     | 0     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+

SELECT id FROM foo WHERE id in (?,?,?) にするとどうなるか?

2014-08-25 17 55 03

in にマッチする行をそれぞれ取りにいって、 Handler_read_key でカウントされます

サンプルクエリ

SELECT * FROM foo WHERE id in (2,4,6);
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | foo   | range | PRIMARY       | PRIMARY | 4       | NULL |    3 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+

+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 0     |
| Handler_read_key      | 3     |
| Handler_read_last     | 0     |
| Handler_read_next     | 0     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+

SELECT * FROM foo WHERE id in (?,?,?) にするとどうなるか?

SELECT * にすると、 in を使っていても結果が異なります。フルテーブルスキャンになってしまいました

サンプルクエリ

SELECT * FROM foo WHERE id in (2,4,6,8)
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | foo   | ALL  | PRIMARY       | NULL | NULL    | NULL |    8 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 1     |
| Handler_read_key      | 1     |
| Handler_read_last     | 0     |
| Handler_read_next     | 0     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 9     |
+-----------------------+-------+

SELECT * でかつ in に指定したレコード数が、テーブル全体のレコード数の半数に達しているので、オプティマイザがフルテーブルスキャンの方がよいと選択したか?

( Handler_read_rnd_next の説明は別に書きます )

Handler_read_next

赤矢印とオレンジ矢印が Handler_read_next としてカウントされる動きです

2014-08-25 17 39 17

上記は レンジスキャン になっているモデルとなります

  1. インデックスで対象のレコードを見つける
  • Handler_read_key でカウントされます
  1. インデックスで昇順に次のレコードを探す (赤矢印)
  • Handler_read_next でカウントされます
    • B+木インデックスでは隣のリーフへのポインタが用意されている (赤矢印)
    • このポインタを辿ることで 次のレコード を探すことができる
  1. range 検索では隣のリーフ(レコード)も range に収まるかどうかの判定が必要 (オレンジ矢印)
  • Handler_read_next でカウントされます
  • 隣のリーフが存在しない/マッチケースでも Handler_read_next がカウントされるのに注意。つまり Haandler_read_next は fetch するレコード数と一致しない

サンプルクエリ

# 3,4,5,6 を返す
SELECT * FROM foo WHERE 2 < id and id < 7;

# BETWEEN 使っても同じ
SELECT * FROM foo WHERE id BETWEEN 3 AND 6

EXPLAIN は type = range になっています

+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | foo   | range | PRIMARY       | PRIMARY | 4       | NULL |    3 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+

+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 0     |
| Handler_read_key      | 1     |
| Handler_read_last     | 0     |
| Handler_read_next     | 4     | # <= 読み取れる行より +1 多い
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+

なお ORDER BY id DESC にすると Handler_read_prev がカウントされます (降順にインデックスを辿るため)

Handler_read_first + Handler_read_key + Handler_read_next

2014-08-25 17 41 00

昇順のフルインデックススキャンのモデルとなります。InnoDB の primary キーの場合は フルインデックススキャン = フルテーブルスキャン に等しいはず (TODO)

  1. id が最小のキーを見つける
  • Handler_read_first と Handler_read_key がカウントされる
  1. 範囲指定されていないのでインデックスを総なめする (赤矢印、オレンジ矢印)
  • Handler_read_next がカウントされる

サンプルクエリ

SELECT * FROM foo ORDER BY id ASC
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------+
|  1 | SIMPLE      | foo   | index | NULL          | PRIMARY | 4       | NULL |    8 | NULL  |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------+

+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 1     |
| Handler_read_key      | 1     |
| Handler_read_last     | 0     |
| Handler_read_next     | 8     | # <= 読み取れる行より +1 多い
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+

サンプルクエリ

SELECT id にしてみるとどうなるか?

SELECT id FROM foo ORDER BY id ASC
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | foo   | index | NULL          | PRIMARY | 4       | NULL |    8 | Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+

+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 1     |
| Handler_read_key      | 1     |
| Handler_read_last     | 0     |
| Handler_read_next     | 8     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+
  • Handler_read_next の数値は一緒だけど Using index が出る。
  • セカンダリインデックスの場合は Covering Index となっていい感じのはず
    • primary キーの場合はどうなんだっけ?

Handler_read_last + Handler_read_key + Handler_read_prev

2014-08-25 17 42 34

降順のフルインデックススキャンのモデルとなります。InnoDB の primary キーの場合は フルインデックススキャン = フルテーブルスキャン に等しいはず

  1. id が最大のキーを見つける
  • Handler_read_last ** と Handler_read_key がカウントされる
  1. インデックスを総なめする (赤矢印、オレンジ矢印)
  • Handler_read_next がカウントされる

サンプルクエリ

SELECT * FROM foo ORDER BY id DESC
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------+
|  1 | SIMPLE      | foo   | index | NULL          | PRIMARY | 4       | NULL |    8 | NULL  |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------+

+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 0     |
| Handler_read_key      | 1     |
| Handler_read_last     | 1     |
| Handler_read_next     | 0     |
| Handler_read_prev     | 8     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+

LIMIT + OFFSET

SELECT id FROM foo LIMIT 3 OFFSET 5

kazeburo さんのエントリにもある通り LIMIT + OFFSET 検索は効率が良くないことが Handler_read_next の数値から読み取れる

+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | foo   | index | NULL          | PRIMARY | 4       | NULL |    8 | Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+

+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 1     |
| Handler_read_key      | 1     |
| Handler_read_last     | 0     |
| Handler_read_next     | 7     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+

Handler_read_rnd_next

インデックスを貼っていないカラムを指定した場合フルテーブルスキャンになります (*1)

この際に Handler_read_rnd_next がカウントされる様子。InnoDB の場合は primary キーがクラスタインデックスなので、インデックスフルスキャンとフルテーブルスキャンは同一視していいんだっけ?

    1. LIMIT で件数絞ったりする場合は例外アリ

サンプルクエリ

SELECT * FROM foo

Handler_read_rnd_next しまくりなクエリ。 ORDER BY id [ASC|DESC] にするとインデックススキャンになる

+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | foo   | ALL  | NULL          | NULL | NULL    | NULL |    8 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+

+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 1     |
| Handler_read_key      | 1     |
| Handler_read_last     | 0     |
| Handler_read_next     | 0     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 9     |
+-----------------------+-------+

LIMIT で件数絞る場合

SELECT * FROM foo WHERE name = 'aaa' LIMIT 1

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | foo   | ALL  | NULL          | NULL | NULL    | NULL |    8 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 1     |
| Handler_read_key      | 1     |
| Handler_read_last     | 0     |
| Handler_read_next     | 0     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 1     |
+-----------------------+-------+
  • LIMIT で件数を絞っていると、指定された件数を見つけた段階で探索は終了する
  • LIMIT 分にマッチする行が無ければフルテーブルスキャンになる

いずれにせよ効率は良くない

Handler_read_rnd, Handler_read_next

ORDER BY RAND() を使うと出ます

サンプルクエリ

SELECT * FROM foo ORDER BY rand()
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                           |
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
|  1 | SIMPLE      | foo   | ALL  | NULL          | NULL | NULL    | NULL |    8 | Using temporary; Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+

今までの結果と比較すると圧倒的に効率悪いのが Handler_read_rnd, Handler_read_rnd_next の数値から分かる

+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 1     |
| Handler_read_key      | 1     |
| Handler_read_last     | 0     |
| Handler_read_next     | 0     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 8     |
| Handler_read_rnd_next | 18    |
+-----------------------+-------+