日向夏特殊応援部隊

俺様向けメモ

Re: MySQL最適化のミニtips

MySQL

元ネタ: http://labs.unoh.net/2007/07/mysqltips.html

あまり具体的じゃないので、僕の考えとか。
正しいかどうかは各自の状況だとか実際試すべきなんだけど、参考になれば。

MyISAM、InnoDBなどテーブルタイプ

僕は断然InnoDB派です。
ただ仰るとおり、ログるだけのテーブルとかならMyISAMでもいいとは思うけど。

トランザクションやロック処理などが必要ない場合など、MyISAM形式にも良いところはあるので検討してみる価値はあるかもしれません。

これだけの指摘だとちょっと微妙な気がするです。

MyISAMの使いどころってのは、

  • ピンで他とリレーションが無い単純追記系のテーブル
  • リレーションがあり、同一トランザクション内での更新系クエリが存在する場合は、トランザクションが期待通りに動かないので、基本的にはInnoDBと混在させるべきではない
  • 全部MyISAMにしたいなら、ミッションクリティカルじゃない案件のなおかつリレーションが少ないデータベースに適用すべき

って感じで僕は考えています。
で、結果として混在を実サービスに投入した事は無いです。*1

あと同一データベース内に異なるテーブルタイプがあるとバックアップが期待どおり動くかどうか分からん。*2
InnoDBの場合はmysql_dump使う事になると思うけど、MyISAMなテーブルは除外すべきだと思うし、その場合は運用がまんどくなりそう。

ほとんどいいこと無いと思うんだけどなぁ。
パフォーマンスもInnoDBをきちんと設計すれば、そこまで遜色ないと思いますけど。

更新(update)・削除(delete)のフラグ化

一概には言えませんが、レコード数が大きくなるとコストが上がってきます。整合性などの注意点もあるので慎重に行いましょう。

「整合性などの注意点もある」ってmyisamchkのこと？
整合性を気にしなきゃいけないテーブルにMyISAMなんぞ使っちゃダメです。
更新やら削除がそれなりにあるテーブルならInnoDB使った方がいい。

あと、スレーブにINDEX張ってる場合なら一時的にレプリケーションしないようにして、
ALTER TABLEで改めてInnoDBにstorage engineを指定するだけでINDEXの最適化は行ってくれる。

多くの場合、データ量は多くなっていくし、更新や削除もあるでしょう。
で、それがコストが高くなるであろう事は当たり前で、チューニングってのはそれでもパフォーマンスを担保したいからこそやるべき事なのでは？

データ量がどうにもならん位増えたら、もう物理的にパーティショニングしてアプリでマージするような設計に変更しなきゃだと思います。

レコード数などの参照は非正規化してみる

都度レコード数を集計するのではなく、一部非正規化して持つなど。問い合わせ発行数を減らすのも改善案のひとつです。

オライリーの「実践ハイパフォーマンスMySQL」に確か書いてあったと思うけど、単純な全レコードカウントはMyISAMの場合は計算済みだったりするので気にせず実行していいはず。
実際試してみれば分かると思うけど異常に高速です。
InnoDBも別に遅くは無いです。

集計関数は限定された人が使う管理系機能でちょろっと出てくるのはいいけども、サービス側で乱用すると確かに遅いと思います。

カウント系の値は正規化を崩す*3ってよりも、既に事前に計算済みのテーブルがあるのがベターかなーと思います。
もちろんそのカウント済みのテーブルを更新する際にアプリ側で余計な処理は増えますけども。

更新系の処理はアプリ側の工夫で時間が掛かってないようなイメージを持たす事は出来るけど、参照形の処理は往々にしてガチンコなんで、得られるメリットのが多いかなーと。


で、本来のリレーション上の正規化を崩すってのは、

  • 理屈上その値をリレーションを駆使して取れるけど、どう見てもJOINしまくりなリレーション
  • リレーション上、隣の隣にいるテーブルとの結合を容易にするための余計なキー

とかは検討する余地があるし、実際に僕もやったりします。
InnoDBで外部キー使えば、必然的にINDEXも張らざるを得ないし。

indexが適切かどうか

無駄なindexはないかどうかを調査してみましょう。かえって遅くなったりしているかもしれません。

つかそもそも「無駄なINDEX」の定義が分からないので、これじゃ投げっぱなしな気がw

ちなみにINDEXについては、

  • サービス開始時はリレーションに基づく(複合)外部キーによるINDEXのみ
  • 頻度が高いクエリからEXPLAINして、検索条件になるキーに対して(キーの内容に応じて複合)INDEXを張る

でOKだと思う。

リレーションには直接関係無いINDEXを検証無しに張るのが無駄なINDEXだと僕は思う訳です。
MySQLの場合、slow query logはその時遅かったクエリのログで、ゴミも混じってるので

  • slow query logに頻繁に挙がってて
  • EXPlAINの結果も芳しくない

ってのから順にINDEXの検討するのが王道かと。

この辺りにINDEXとかFOREIGN KEYについては書いた。

利用範囲を考慮する

データが利用される場面を想定し、リアルタイム性を求められなかったり、冗長性がないかどうかを調べてみましょう。場合によってはログテーブルなどを利用するのもいいかもしれません。

リアルタイム性が求められない場合は、DBチューニングってよりも、DBに至らない工夫をすべき。memcachedとか使ってアプリ側でキャッシュでしょ。

データの冗長性って具体的になんの事だろ。。。
テーブル設計？それとも無意味に余計なカラムあるいはレコードまで参照する事？
クエリキャッシュを考えたら、発行するクエリを揃えた方がいいので、
むしろ特に参照クエリのパターンがいたずらにそのDBに対して多くならないようにした方が良いと思う。

ログテーブルって5.1からの、
http://dev.mysql.com/doc/refman/5.1/ja/log-tables.html
辺りの事かな？だとしたらクエリログって言った方が通りが良いと思うんですけど。

サマリテーブルの導入を検討する

ランキングなど集計処理はコストが高くつきがちなので、リクエスト毎に生成する必要があるかを検討する。

これはその通り。でもその前にほぼ同等の事を言ってるよーな。

explain で参照処理を検証する

適切なindexが利用されているか、filesortが発生していないか、オーバーヘッドが大きくないかなどの情報を見ることが出来ます。

まぁ、これはその通りかなぁ。
とはいえ、運用段階で適切でない参照クエリを見極め、特定していくのはテーブル数が多いと相当に骨なんで、それ以前にデータベースサーバーの設定を見直すかな。SHOWコマンドで見れる情報を参考にパラメータを再度検討してみるといいと思う。InnoDB専用の

SHOW InnoDB STATUS\G

の結果も相当参考になるはず。

クエリのチューニングはその後！

そもそも必要な処理・問い合わせなのかを見極める

別テーブルや静的ファイルで代用可能かどうかなど、意外な盲点があるかもしれません。

wwww

いや、仰る通り！
つか、運用段階なら真っ先にこれを試すかなー。

テーブル設計とかクエリの設計ってのは、特に事前に検討しておくべき事で、
INDEXやらキャッシュってのは後から如何様にも変更出来るからフレキシブルに対応でおk！

あとDBに関しては、僕の元同僚の某ドイツ人のT氏曰く、
「我々は可哀想なデータベースを労ってやらなければならない」
と言ってたけど本当にその通りで、極力データベースに問い合わせないようなアプリ側の努力も相当大事だと思います。

まとめ

この辺り読んでおけば、ほとんどのケースで大体対応出来ると思うです。
どれも本当にソースレベルの状態からのチューニングが書いてある。

特に公式のドキュメントは良く書かれているので、気になったらまず公式のドキュメント嫁と。

*1:検証はしたことはある

*2:これは未検証。悪しからず。

*3:集計結果を余計に持つのも正規化を崩すというかどうかは分からない