フレームワーク開発時代のSQLチューニング基礎（２）SQLのやっちゃだめ前編 について確認してみた

追加情報をこちらに書きました。合わせてどうぞ。

フレームワーク開発時代のSQLチューニング基礎（２）SQLのやっちゃだめ前編






こちらの記事をみて、いくつか気になったので確認してみました。
ただ、あらかじめ言っておくと、この記事の主張は概ね正しいと思います。
ちょっと重箱の角をつつくような内容なので、あしからず。


まずはこの文章。

　ワイルドカードを使った場合、必要のないデータまでメモリ上に展開されますが、項目を指定した場合は必要なデータのみメモリ上に展開されます。メモリは高速ですが、無限の容量がある訳ではないので、効率よく使用するために項目指定をするようにしましょう。

これだけ読むと、データのI/Oが列単位に行われているような印象を受けてしまいます。
InnoDBでは、ページ単位のI/Oで、バッファキャッシュへのキャッシュもページ単位のはずなのでSELECT句の列を＊で書いても、特定の列だけ書いてもメモリーの使用量は変わらないはず。
MyISAMだとどうなんだろう。昔BtrieveというISAM系のDBを使ってたことがありますけど、I/Oはページ単位だったので、多分MyISAMでも一緒じゃないかと予想。←すいません、マニュアルも見ないで書いてます。

結論としての、SELECT * は使うな、には100%同意です。

また、項目指定の方が明らかに速い検索も存在します。例をみてみましょう。

こちらは、カバリングINDEXによって、INDEXスキャンのみで検索が完了できる例ですね。
これは、SELECT句の列は最小限にすること＋INDEXは複数列の索引で作成の2つの組み合わせによって、表スキャンが必要なくするテクニックとして多くの人に知ってもらいたい内容ですね。

範囲検索はBETWEENを使う

これについてはちょっと気になったので、色々調べてみました。

以上以下の複数の条件を各よりSQLのBETWEENを使った方が高速になります。

とありますが、Oracleでは、BETWEENで書いたSQLを、Oracleがパースしたタイミングで、列名＜値 AND 列名＞値　の形に変換してから、選択性の評価を行っていたように記憶しています。
（今手元に使えるOracle環境がなくて未検証です。識者の突っ込み待ちです）

DB2でも、BETWEENを投げると、DB2が勝手に＜ AND ＞ の式に変換してたように記憶していたので、これは検証してみました。
以下、DB2 9.1のSAMPLEデータベースでの検証内容です。

db2 SET CURRENT EXPLAIN MODE YES

db2 -tvf "$DB2PATH/misc/EXPLAIN.DDL"

db2 SET CURRENT EXPLAIN MODE YES

db2 "select * from employee where empno between '000020' and '000050'"

この時の実行計画などをdb2exfmtで確認します。
結果が結構長いので、一部抜粋

db2exfmt -d sample -t -1

Optimized Statement:
-------------------
SELECT Q1."EMPNO" AS "EMPNO", Q1."FIRSTNME" AS "FIRSTNME", Q1."MIDINIT" AS
"MIDINIT", Q1."LASTNAME" AS "LASTNAME", Q1."WORKDEPT" AS "WORKDEPT",
Q1."PHONENO" AS "PHONENO", Q1."HIREDATE" AS "HIREDATE", Q1."JOB" AS
"JOB", Q1."EDLEVEL" AS "EDLEVEL", Q1."SEX" AS "SEX", Q1."BIRTHDATE"
AS "BIRTHDATE", Q1."SALARY" AS "SALARY", Q1."BONUS" AS "BONUS",
Q1."COMM" AS "COMM"
FROM HITAI.EMPLOYEE AS Q1
WHERE (Q1."EMPNO" <= '000050') AND ('000020' <= Q1."EMPNO") ←　ここに注目！



Predicates:
----------
2) Stop Key Predicate
Comparison Operator: Less Than or Equal (<=)
Subquery Input Required: No
Filter Factor: 0.0952381　　←　ここ

Predicate Text:
--------------
(Q1."EMPNO" <= '000050')

3) Start Key Predicate
Comparison Operator: Less Than or Equal (<=)
Subquery Input Required: No
Filter Factor: 0.97619　　　←　ここ

Predicate Text:
--------------
('000020' <= Q1."EMPNO")

ということで、BETWEENでSQLを投げても、内部の最適化処理で＜ and ＞ に変換され、それぞのフィルタファクタ（選択性）の計算を行ってから、実行計画の決定をしているように見えます。
選択性の計算式は、どっかのサイトにあったと思いますが、OracleでもDB2でも基本的な考えはだいたい同じです。
で、おそらくMySQLも一緒じゃないかなーと思って調べてみたのですが、明確なドキュメントは見つけられませんでした。この件も識者の突っ込みをお待ちしています。

で、ドキュメントがないなら、自分で確認すればいいじゃない、ということで最適化された後のSQLを見ようとしたのですが、これも適切な方法がみつけられず。
MySQLでOracleのstatspack的なことをやれるよ、というブログエントリも見つけましたが、多分これも投げられたSQLしかみれないですよね。試してないけど。
そんなわけで、最適化後のSQLの確認方法をご存知の方、教えてください。

で、話を戻すと、BETWEENと＜ AND ＞ のどちらが早いか、についてMySQLでは確認できませんでした。
でも、おそらく最近のバージョンであれば、どちらでも同じように最適化されるはずなので、読みやすい、書きやすい方でもいいかなーとは思います。

GROUP BYやORDER BYにインデックスを使う。

これは製品によって違うかもしれないんですが、Order by や group by のためだけにindexスキャンは選択されない気がします。
→　と書くつもりでいたのすが、試してみるとINDEXフルスキャンで使ってますね。
実行計画のTypeがINDEXとなっているので、INDEXを全件なめて、その順で表アクセスしてるってことですかね。
いくつかのパターンで確認しましたが、ORDER BYのためにINDEXをつかってるように見えますねぇ・・・
WHERE句とORDER BYで同じ列を使っていれば、その列のINDEXによってソート処理がスキップできる。と認識していたのですが、MySQLではちょっと違うのかもしれません。

ここらへんの、オプティマイザの判断基準とか、マニュアルでも見つけられなかったので、ここ読んどけオラっていう情報がありましたら教えてください。
うーん。もやもやする。

キーに演算はしない。

これはその通りで、SQLを書く全ての人に知ってもらいたい内容ですね。

で、よくありがちなので日付の絞りこみです。
ちょっとORACLEでの例になりますが、

SELECT empno, ename, hiredate FROM emp
WHERE TO_CHAR(hiredate, 'YYYY/MM/DD') = '2011/02/16';

よくありがちですが、これはHIREDATE列にINDEXがあっても使えません。
列値が関数などで加工されると、INDEX内の列値を使ってマッチングができないからですね。
日付での絞り込みが遅いシステムの原因としてよく見かけます。
でも、この書き方じゃINDEXが使えないのはわかったので、改良してみます。

SELECT empno, ename, hiredate FROM emp
WHERE hiredate = TO_DATE('2011/02/16', 'YYYY/MM/DD');

で、これを実行すると、データが1件もヒットしなかったりします。
原因は、DATE型は年月日 時分秒を持っているため、「年月日」だけの文字列を変換すると、時刻部分は00:00:00扱いになるためです。
なので、時刻部分は無視できるようにします。

SELECT empno, ename, hiredate FROM emp
WHERE hiredate >= TO_DATE('2011/02/16 00:00:00', 'YYYY/MM/DD HH:MI:SS')
AND hiredate <= TO_DATE('2011/02/16 23:59:59', 'YYYY/MM/DD HH:MI:SS');

betweenのほうがすっきりするかもしれませんが。

同様に、データ型の暗黙変化でもINDEXは使えなくなるので、文字と数値の比較などを行わないように注意が必要です。




以上が気になった点を調べた結果です。
今回は、twitterでうっかりツッコミたい的な発言をしてしまったのですが、結局はツッコミ所はほとんどなく（または自分のスキル不足で確認すらできず）元記事はやっぱり良いエントリだったなーという結果になりました。

逆に、普段仕事でつかってないMySQLの勉強になったのが収穫でしたし、調べきれなくて疑問のままになっている点については、是非、MySQLのえらい人からのツッコミをお待ちしています。
漢の人の鍵本買っておけばよかった。


参考URL
漢のコンピュータ道　MySQLのEXPLAINを徹底解説!!
http://nippondanji.blogspot.com/2009/03/mysqlexplain.html

SH2の日記　MySQL 5.1のmysqldumpslowで快速チューニング
http://d.hatena.ne.jp/sh2/20090414

MySQL 5.1 リファレンスマニュアル
http://dev.mysql.com/doc/refman/5.1/ja/index.html