MySQL InnoDB Deep Talk #1 復習
というわけで、InnoDB Deep Talk #1に参加して発表をしてきました。準備のために今月ちょっと睡眠不足でしたが、かなり刺激になったので参加してよかったです。いちいさんを始め運営のみなさま、雨の降るなか参加されたみなさまどうもありがとうございました。
書籍の紹介
当日木村さんと初めてお会いして、今月発売になった書籍「プロになるための データベース技術入門 〜MySQLforWindows困ったときに役立つ開発・運用ガイド
プロになるための データベース技術入門 ?MySQLforWindows困ったときに役立つ開発・運用ガイド
- 作者: 木村明治
- 出版社/メーカー: 技術評論社
- 発売日: 2012/03/16
- メディア: 大型本
- 購入: 2人 クリック: 101回
- この商品を含むブログ (4件) を見る
」と比べると、松信さんの本は比較的レイヤの低いインフラ寄りの内容、木村さんの本はレイヤの高いアプリケーション寄りの内容となっています。タイトルは似ていますが内容は意外なほど重複していないので、両方あわせて読まれることをおすすめいたします。以下、私が特にいいなと思ったところをご紹介します。
- MySQL 5.5をメインターゲットとした初めての書籍だと思います。
- 3.3章「Windows PowerShell」ではコマンドラインからConnector/Netを経由してMySQLにアクセスする手法が紹介されていて、大変興味深いです。
- 4.1章「トランザクションとロック」はInnoDBの若干クセのあるロック方式にも触れられていて、貴重な資料だと思います。
- 4.3章「文字コード」は24ページを割いて詳細に解説されており、cp932における重複コードの存在など高度なトピックも扱われています。
- 5.1章「ストアドルーチンの利用」はMySQLのストアドルーチンに関する数少ない情報源ではないかと思います。付録C「SQL/PSMミニリファレンス」も便利です。
- 5.4章「プランとヒント」は当日ライトニングトークをしていただいた内容で、私の発表の前提知識にもなるかと思います。
ありがたいことに書籍から私のブログにいくつか参照がありましたので、パーマリンクを切らさないよう維持していきたいと思います。
MySQL 5.1におけるInnoDBのレンジ分析
勉強会のときに指摘を受けたのですが、InnoDBのレンジ分析について、プレゼンテーション資料の21ページ目から25ページ目までのアルゴリズムは昔のバージョンにはなかったものです。このように9ページを上限として実際にINDEX RANGE SCANを行う仕組みが導入されたのはMySQL 5.5からで、MySQL 5.1まではINDEX RANGE SCANは行われていませんでした。なお、MySQL 5.1でInnoDB Pluginを利用した場合もINDEX RANGE SCANは行われていません。
MySQL 5.1の場合、読み取りレコード数の推定は下限値が含まれるリーフページと上限値が含まれるリーフページ、それからルートページの情報のみを利用して行います。
ルートページにおいて下限値へのポインタと上限値へのポインタの間にあるエントリ数をn_rows_on_prev_level、それぞれのリーフページに含まれるインデックスエントリの数をn_recs_Nとして、読み取りレコード数の推定値は以下の式で表されます。
読み取りレコード数の推定値 ＝(検索範囲内のリーフページ数)×(リーフページあたりの推定レコード数)×2 ＝(検索範囲内のリーフページ数)×(下限値が含まれるリーフページのインデックスエントリ数＋上限値が〃)÷2×2 ＝n_rows_on_prev_level×(n_recs_1＋n_recs_2)÷2×2 ＝28×(348＋348)÷2×2 ＝19,488
mysql> EXPLAIN SELECT * FROM item WHERE i_id BETWEEN 10001 AND 20000; +----+-------------+-------+-------+---------------+---------+---------+------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+------+-------+-------------+ | 1 | SIMPLE | item | range | PRIMARY | PRIMARY | 4 | NULL | 19488 | Using where | +----+-------------+-------+-------+---------------+---------+---------+------+-------+-------------+
最大11ページを利用するMySQL 5.5以降の推定アルゴリズムに比べると、3ページしか利用しないこのアルゴリズムでは見積もり精度が低くなってしまうことが分かると思います。読み込んだリーフページがたまたま隙間だらけだった場合、推定値は実際の値から大きくかけ離れたものになります。このアルゴリズムであれば、補正係数2の存在も納得できないことはないです。
以下のバグレポートで、MySQL 5.1までのアルゴリズムでは読み取りレコード数の推定値が200倍もずれることがあるという議論がなされています。その後、MySQL 5.5でINDEX RANGE SCANを行う新しいアルゴリズムが導入されました。
