DBチューニングできますか？(6)

概要

というわけで最近いろいろと面接行ったりしてます。

ルールとコスト

面接官「DBチューニングできますか？」
私「そうですねー(中略)『インデクスとクエリからオプティマイザが何しようとするか程度は意識してます。』」

この後面接官はこう言いました。

面接官「あ、じゃあルールベースオプティマイザとコストベースオプティマイザの違いを説明してもらえる？」

しどろもどろに説明する

とても疲れましたが何とか説明できた(ような)気がしています。

とはいえもう一度同じ事聞かれるとつらいので流暢に答える練習をしておきたいのです。

ルールベースオプティマイザ

たとばこんなクエリがあるとします。mySQLでinnoDBだとしてAとBはともにインデックスが生成されているものとします。

select * from TABLE
where A = '10' and B like '123%';

この場合どのようにデータを取得すると一番効率が良いのか？を判断する必要があるのですが、
その方法の一つがルールベースオプティマイザです。

上記の場合、=とlikeのどちらが優先順位が高いかという判断が必要なのですが、この場合'='だと仮定します。おそらくオプティマイザは

「最初にA='10'なインデックスファイルを読み込もう。そうするとそのレコードのプライマリキーを取得できる。そのプライマリキーのインデックスファイルを読み込むとクラスタードインデックスなのでそこからB like '123%'なレコードを特定すれば速いんじゃないかな！」

ルールベースオプティマイザの限界

話は突然変わるのですが、貴方はとある結活サークルを運営しているとします。
参加している男性陣と女性陣とでそれぞれおつきあいしても良いという相手を投票してもらい、両想いの人を探すという企画を立てました。

テーブルでいうとこんな感じでしょうか。だいぶ適当ですけど。

create table men (
	id INT(11) NOT NULL AUTO_INCREMENT,
	woman_id INT(11),
	PRIMARY KEY (id)
);

create table women (
	id INT(11) NOT NULL AUTO_INCREMENT,
	man_id INT(11),
	PRIMARY KEY (id)
);

さてさて、貴方はお手伝いしてくれるスタッフに次のように伝えました

貴方「とりあえず男性会員のデータを全部見て、どの女性を好きかを調べてほしい。それから、その女性もその男性を好きかどうかをチェックしてくれる？」
お手伝いさん「はい。承知しました。」

さてさて、そのあと数時間ほどしてから戻ってくるとまだ作業をしているスタッフを発見しました。

貴方「おや？まだ終わらなそう？大変な作業をおしつけちゃったねぇ」
お手伝いさん「はい、女性会員は50人ぐらいなんですけど、男性会員は10,000人いるんです」
貴方「!!」

さて、上記のたとえ話ですけれども「女性会員がどの男性を好きかを先に調べればすぐに終わる作業」だったのに、という意図が伝わったでしょうか？
なぜそうなったのかというと、単純に作業指示者がこの「統計データ」を知らなかったので「より良い作業指示」を計画できなかったところです。

統計データを取得する事によってより最適な探索ルートを探す方法が考えだされまして、それがコストベースオプティマイザです。

コストベースオプティマイザ

コストベースオプティマイザはさまざまな統計を元にいくつかの処理方法を検討し、それらがどれぐらいコストのかかる作業なのかを計算してからどの処理方法を選ぶかを決定します。

ここでいう統計には色々な要素が含まれています。

テーブルの行数
ブロック数
ブロック数あたりの平均行数
行の平均サイズ
分布度
インデックスの深さ
CPU使用率
I/O

などなど。いろいろな要素がありますが、この中でも最も大事なのは分布だと思います。

先ほどの例に戻る

さきほどの例に出たクエリです。

select * from TABLE
where A = '10' and B like '123%';

次のような情報を得ていた場合、あなたはどちらのインデックスを使いますか？

実はAという列は10='男', 20='女'という２種類の値のみで構成されている平均分布度が50%の列です。
Bは0〜9999の値で0.01%の平均分布です。

この場合はBのインデックスが効率が良さそうです。

コストベースオプティマイザのほうが優れているが

上記の例からもわかるようにコストベースオプティマイザのほうが優秀だと思います。
ただしコストベースオプティマイザのほうが優秀と考えるならば統計情報にも感心を持つ必要があります。

とりあえず以上をまとめる

ルールベースオプティマイザとコストベースオプティマイザはともに目的とするデータを探索する経路を決定するための考え方です。

例えば10000人の中から次の条件を満たすバスケ選手を探してスカウトしたい場合を考えます。

1.バスケ歴3年以上であること
2.身長185cm以上であること

この際、「バスケ暦3年以上である」ことを優先するのか「身長185cm以上である」ことを優先するのかを最初にルールとして定めるシンプルなやり方がルールベースオプティマイザです。

対して身長185cm以上の人が少なく、バスケ歴3年以上の人は多いという予測でできたなら身長185cm以上の人から先に絞り込み、そこからバスケ歴3年以上の人を探す
方法を採用するのがコストベースオプティマイザです。ただし、この方法には分布度といった統計など必要になります。

こんなので大丈夫かな？

というわけで

オプティマイザは絶対ではないので場合によっては開発者が探索経路をコントロールしてあげましょう。
以下、うまくコントロールしてあげている例です。わかりやすい良い記事ですね。

http://blog.nomadscafe.jp/2011/08/coverting-index-self-join-mysql.html