「ちょっと待て」 真・MySQLのクエリを最適化する10のTips

Jaslabs: High performance phpで紹介された「MySQLのクエリを最適化するための10のTips」に対して、反論している人がいる。

　Jaslabs: High performance phpで紹介された「MySQLのクエリを最適化する10のTips」に対して、反論している人がいる。ブログ「20bits」のJesse氏だ。彼は「10 Tips for Optimizing MySQL Queries (That don’t suck)」というエントリーで、Jaslabs氏の記事は適切でないとしている。

　Jesse氏の経験によれば、SQL最適化で最も重要なことはSQLやDBの基本をしっかりと理解することであり、60％がこれで解決するという。残り35％はDBやクエリの特殊な性質に対する対処であり、最後の5％で発想の転換などを求められる。Jaslabs氏はここにばかり力を入れており、それはまったくもって時間の無駄だと述べている（Jesse氏は「SQL_SMALL_RESULTなんて、生まれてこの方使ったことすらない」とまで言っている）。

　そしてJesse氏は、自身の経験から新たな「MySQLのクエリを最適化する10のTips」を挙げている。以下、簡単に紹介してみよう。

1．ベンチマークを気にせよ

　基本的だがベンチマークを気にすることが重要。何が最悪なクエリなのか、ボトルネックはどこかということ意識する。非常に大変な作業だが、supersmack、ab、SysBenchといったツールを活用しながら行うと良い。

2．プロファイルも気にせよ

　こちらも大変な作業だがメモリ、CPU、ネットワーク、ディスクI/Oの性能を知り、使用状況を把握することが大切だ。そのためにMySQL slow query log を活用したり、mtopをインストールする。これらを使い、アクセス状況の確認や、多大な時間をかけているクエリを見つけ出すべきだ。

3．クエリを書く前にスキーマを設計すべき

　適切でないスキーマ、テーブル設計は余計なメモリーを消費する結果になる。よほど大がかりなデータでない限りuser_idにBIGINTを使う必要はないし、固定長のデータにVARCHARを使うのも余分なバイトを消費することになる。

4．ちゃんと正規化すること

　１つのテーブル内にあるほんの少しのカラムだけが頻繁にアクセスされていることがある。これをちゃんと正規化し、アクセスされるカラムを別テーブルに切り出すだけでキャッシュ効率を上げることができる。

5．人工キーを使いすぎないこと

　人工キーを使いすぎないように。自然キーの方が良いことも多い。

6．インデックスを研究をすること

　インデックスの選択1つがデータベースの成否を握る。インデックスがないために、DBがあらゆる列を走査してしまい、多大な時間をかけていることもある（SELECT * FROM users WHERE last_name = 'Goldstein'といったとき）。たった1つのインデックスがスピードを改善させることすらある。

7．SQLはCではない

　Cは代表的な手続き型プログラミング言語だが、SQLプログラマの悪いところは、「SQLは手続き型言語でない」と理解していないことである（もちろんオブジェクト指向でもない）。これを理解していないと不適切なサブクエリが作られる結果になる。

8．DBエンジンを理解する

　MySQLには、「MyISAM」と「InnoDB」という2台の主要なエンジンがある。これらの特徴を理解すること。例えば、MyISAMは膨大なデータを読み込むのに向き、逆にInnoDBは書き込むのに向いている。

9．MySQL専用の近道

　MySQLには、多くの場面で使えるパフォーマンスを向上させる拡張機能がある。「INSERT ... SELECT」「INSERT ... ON DUPLICATE KEY UPDATE」「REPLACE」である。ただし、「INSERT DELAYED」を使う際は注意が必要である。

10．そして最後に

　Peter Zaitsev氏が書いている「MySQL Performance Blog」が非常に役立つ。ぜひ参考にしてください。

　こうして見てみると、Jesse氏の手法はまさに王道であり、基本を忠実におさえることが最適化の近道だと主張していることがわかる。また、SQLクエリによる最適化を図るJaslabs氏と違い、テーブル設計やハードウェアのチューニングを重視しているあたりに、両氏のアプローチの違いが見て取れる。

　詳細は下記参照のこと。エントリーでは、SQLの例文やより突っ込んだ解説も含めて説明している。

20bits：10 Tips for Optimizing MySQL Queries (That don’t suck)
Jaslabs: High performance php：10 tips for optimizing mysql queries

CodeZine：[PHPプロ!] MySQLのクエリを最適化する10のTips