MySQLのTips

mysql

http://forge.mysql.com/wiki/Top10SQLPerformanceTipsというのがあったので、和訳してみる。
(11/23 追記)id:pekeqさんとsodaさんのコメントを受け一部更新
(4/27 追記と修正)id:hirose31さんの指摘を受け修正。あと元のサイトが構成変更していたので追従

クエリのパフォーマンスに関するTips(データベースのデザインとインデックスについても)

  1. EXPLAINを使ってクエリの実行プロファイルを取れ
  2. スロークエリログを使え(常に有効にしておけ！)
  3. GROUP BYを使っているか使えるなら、DISTINCTを使うな
  4. Insertのパフォーマンス
    1. バッチ処理によるINSERTとREPLACE
    2. INSERTの代りにLOAD DATAを使う
  5. LIMIT m,nは案外速くない
  6. 2000件以上のレコードに対してORDER BY RAND()を使ってはいけない
  7. 頻繁に更新するデータに対してや大きな結果を取得するときにはSQL_NO_CACHEを付けろ *1
  8. LIKEクエリの先頭にワイルドカードを使わないこと*2
  9. 相関副問い合わせ、WHERE句の中のIN SELECTを避けること(出来ればIN自体使わない)
  10. 比較時に演算されないようにする、インデックスされたカラムはそのまま使う(関数などを適用しない)
  11. ORDER BYとLIMITは等式とインデックスがあるときに有効に働く
  12. textやblobをメタデータから分離する。 必要でもないのにtextやblobを結果に入れないこと。
  13. FROM句のサブクエリで生成した抽出テーブルはBLOBをソートしないで取得するときに便利。(IDなどで検索してから残りを取得するようなケースで自己結合を使うと高速化できます)
  14. 時系列でソートされているデータに対してはALTER TABLE ... ORDER BY という文を使うと、特定のフィールドで並べ直すことができる。このフィールドに対するクエリは高速化されます。
  15. どんなときに複雑なクエリを分解したり、単純なクエリをまとめるかを知ること
  16. こまごまと余計なデータを掃除すること
  17. キャッシュが有効となるように似たクエリには完全に同一の物を発行すること
  18. SQLの標準に準拠しましょう
  19. 古くて廃止予定の機能は使わないこと
  20. 5.0未満では複数のインデックスがあるときのORをUNION結合にすることで速くなる(LIMIT句があるとき)、5.0以降はindex_mergeが処理する。 *3
  21. InnoDBにおいて毎回 COUNT *は使ってはいけない。使うとしたらすこしだけにしておくかサマリテーブルに対してにしておくべき。全ての行数を数えること必要なら、SQL_CALC_FOUND_ROWSとSELECT FOUND_ROWS()を使うべし
  22. 事前にSELECTになくていいようにINSERT .. ON DUPLICATE KEYによる更新を使う
  23. サブクエリの代りにGROUP BYのmaxを使う

スケーリングに関するTips

  1. ベンチマークすべし
  2. 管理作業と顧客のパフォーマンスを分離せよ(バックアップとか)
  3. デバッグしてんじゃねぇ、テストしろ！
  4. データの増加にともなって、(カーディナリティや選択度の点において)インデックスは変化する。構造を変えたくなるはず。スキーマをコードからモジュラー化しておけば、コードはスケールするようになる。計画し変化を受容せよ、プログラマーにも同じことをさせること。

ネットワークのパフォーマンスに関するTips

  1. 欲しいデータだけ取得してトラフィックを最小に
    1. ページングやチャンキングで情報取得に制限をつける
    2. SELECT *を使うな
    3. 長いクエリをもっと効率的にできるなら、大量の小さくて短時間で終わるクエリを注意すること
  2. ラウンドトリップの時間を減らせるようなら multi_queryを使う

OSのパフォーマンスに関するTips

  1. 適切なデータパーティションを使う
    1. 必要になる*前に*クラスタを考慮せよ
  2. 可能な限りデータベースのホストはきれいにしておくこと。サーバでウィンドウシステムって本当に要る？
  3. OSの長所を活用しよう
  4. cronスクリプトを削減する
  5. テスト環境を作ること
  6. スキーマや設定ファイルはバージョン管理下におく
  7. LVM上のInnoDBのバックアップを異なるMySQLインスタンスでレストアすると、ロールフォワードできる*4。
  8. 適切にパーティショニングすること
  9. 実データが得られてからパーティショニングすること。実際のデータを手にする前に勝手な仮定を立てない

MySQLサーバ全般についてのTips

  1. innodb_flush_commit=0にしておくとスレーブとのずれがなくなる
  2. データ型を最適化し、一貫したデータ型を使うこと。 PROCEDURE ANALYSE()を使うと必要最低限のデータ型を判断するときの手助けになる。
  3. ペシミスティックロックではオプティミスティックロックを使う。出来れば排他ロックではなく、共有ロックを使う。FOR UPDATEとLOCK IN SHARED MODE。
  4. 出来るならtextやblobは圧縮すること
  5. 静的なデータは圧縮する
  6. 静的なデータを頻繁にバックアップしない
  7. 効くようならクエリキャッシュを有効にしてバッファキャッシュを増やす
  8. 設定パラメータについて。http://docs.cellblue.nl/2007/03/17/easy-mysql-performance-tweaks/ がいいリファレンスです。
  9. 設定変数とtips
    1. 標準添付の設定のひとつを使う
    2. key_buffer、UNIXキャッシュ、コネクション毎の変数、innodbのメモリ周りの変数
    3. システム全体でのメモリとコネクション毎のメモリについて注意する
    4. SHOW STATUSとSHOW VARIABLES(5.0以上の場合GLOBALとSESSION毎)でチェックする
    5. スワップに気をつけること、特にLinuxで(OSのファイルキャッシュをバイパスするにはinnodb_flush_method=O_DIRECTを付ける、但しOS依存)
    6. テーブルのデフラグ、インデックスの再構築、テーブルのメンテ
    7. innodb_flush_log_at_trx_commit=1を使うなら、バッテリバックアップされたキャッシュコントローラを使うこと
    8. たくさんのRAMは速いディスクよりも幸せ
    9. 64-bitアーキテクチャ使う
  10. --skip-name-resolve *5
  11. 大きなINSERT用に myisam_sort_buffer_sizeを増やすこと(これはコネクション毎の変数)
  12. INSERT時のキャッシュのメモリチューニングパラメータについて見ておくこと
  13. データウェアハウス環境ではtempテーブルのサイズを大きくすること(デフォルトは32Mb)。ちなみに、テーブルはディスクに書き込まれない。(あとデフォルトが16Mbのmax_heap_table_sizeにも制約される)
  14. SQL_MODE=STRICTで実行すると詳細な警告が得られる
  15. /tmpはバッテリバックアップされたライトバックキャッシュのある場所に置く
  16. innodbのログファイルをバッテリバックアップされたRAMディスク上に置くことを検討する
  17. クライアントでは--safe-updatesを使う*6
  18. 冗長なデータは冗長

ストレージエンジンについてのTips

  1. InnoDBは常に主キーをインデックスの一部として保持する、そのため主キーが大きくなりすぎないようにすること。
  2. マスタとスレーブで異なるストレージエンジンを使うとよい。たとえば全文検索インデックスとか
  3. ログなどの用途でBLACKHOLEエンジンとレプリケーションの組み合わせはFEDERATEDテーブルよりも速い。
  4. 今使っているストレージエンジン、どれが用途最適か、その他のエンジンがあることを知るべし
    1. 例えばログ用にMERGEテーブルとARCHIVEテーブル
    2. 古いデータの保管。やたらと溜めこむべからず。これらの用途にはARCHIVEテーブルとMERGEテーブル
  5. OLTP用途にはテーブルレベルロックではなく行レベルロックを使うこと
  6. スキーマやストレージエンジンはテスト環境で試してから選ぶ

データベースデザインとパフォーマンスのTips

  1. まっとうなクエリスキーマをデザインすること。テーブルの結合を怖がることはない、大抵非正規化よりも速い
  2. booleanフラグは使うべからず
  3. インデックスを使う
  4. かといって全部にインデックスを張らない
  5. 重複したインデックスを張らない
  6. SELECTとINSERTの比が小さいときは大きなカラムに対してインデックスを作らないこと
  7. インデックスに冗長なカラムが含まれていないかを注意する
  8. まずは正規化、その後で必要なところを非正規化
  9. MAX()の代りによく考えられたキーとORDER BYを使う
  10. データベースは表計算ソフトじゃない、Accessは非常に似てるけどな。 んで、Accessは本当のデータベースじゃない。
  11. IPアドレスにはINET_ATONとINET_NTOAを使え。charやvarcharにするべからず
  12. ドメインの検索が楽になるから、メールアドレスはREVERSE()で逆にすることを習慣にする
  13. NULLデータ型はNOT NULLよりも大きな容量を格納できる
  14. 適切な文字コードを選択すること。 UTF16はどんな文字だろうと個々の文字を2バイトで格納する。Latin1はUTF8より速い。
  15. 上手くトリガを使おう
  16. min_rowsとmax_rowsを使うと正確な容量が見積もることができる。これによって予め確保すべき容量や基準となる点が計算できます。
  17. 同じようなプレフィックスを持つデータに対してHASHインデックスを使う
  18. intデータに対してmyisam_pack_keysを使う
  19. 動いているコードに触ることなくスキーマを変更できるようにしておくこと
  20. テーブルとデータベースは分離しておくこと、これによって異なる設定変数の恩恵に被かれる。

その他

  1. MySQL認定DBAを雇う
  2. MySQLプロフェッショナルサービスと同じぐらいのコンサルティング会社は多数ある
  3. MySQL Planetを読んだり書いたりすること
  4. 毎年実施されるMySQL ConferenceやExpoに参加する。また他のイベントのMySQLのトラックに参加する
  5. 地元のMySQLユーザグループを支援する

*1:query cacheが汚れるからだと思われる

*2:%で始まるLIKE条件にはインデックスが使われないため

*3:5.0未満では1クエリニ対して1インデックスしか使われないため複数の条件がORで結合されているときには、条件毎にクエリを実行しその結果をUNION結合(つまりORで集める)するという最適化をやってくれる。5.0からはindex_mergeという機能が入ったため、こちらが使われる。

*4:InnoDBではトランザクションログと実データが分離されている。前者は通常コミットのタイミングでディスクへ同期書き込みされるので、どの瞬間に取ったLVMのスナップショットであってもトランザクションログから一貫性の保たれた状態へロールフォワード出来るということ

*5:名前索きの抑止

*6:誤操作防止のため、where句なしでのupdateを出来なくしてくれる