MySQLノウハウ

いろいろな本からメモってきたメモのメモ。出典を書いておくのを忘れた。思い出し次第補完するかも。

• deleteのコストは高いので、無効化を示すフィールドを作ってupdateすべき
• slow query logに要注意
• 多くのエントリでほとんどのフィールドが同じ値を持つ場合はインデックスの効果が小さい
  • →複合インデックスの効果が大きい
• 複合インデックスは指定の順番が大切。AとBという指定の場合、A単独でもインデックスの効果がある。逆は真でない。
• インデックスが使われる場面は
  • フィールド値を定数と比較するとき (where name = 'hogehoge')
  • フィールド値でJOINするとき (where a.name = b.name)
  • フィールド値の範囲を求めるとき (<,>,between)
  • LIKE句が文字列から始まるとき (where name like 'hoge%')
  • min(),max()
  • 文字列のプレフィクスを基にしたorder by,groupy by
  • WHERE句の全てのフィールドがインデックスの一部の場合はDBをまったく参照せずに結果が返ってくる
• インデックスが使われない場面は
  • LIKE句がワイルドカードから始まるとき (where name like '%hoge')
  • DB全体を読んだほうが早いとMySQLが判断したとき
  • ほかでインデックスを使ったとき
• 遅いクエリをexplain selectで解剖
  • using filesort と using temporary が現れないように
  • using index や where used が現れるとうれしい。where used で type:ALLの場合は適切なインデックスをつけるように
• left join や straight join と using の組み合わせは where より早い
  • left join on と natural join + where だと後者のほうが早いのでそうしていたけれども、usingを一回試してみよう
  • usingは二つのカラム名が同じでないと使えないみたい → usingとonは同じみたい
• where in はかなり早い
• インデックスが適用されないので having はなるべく使わないように
• なるべくシンプルなSQL文を心がける
• update,insert,deleteの最適化
  • multi-row insertをなるべく使うようにする
    • JDBC経由だとどうすればいいんだろう
• select high_priority や update low_priority を使ってみる
• replace のほうが delete → insert より早い
• load data infile, load_file()
• limit句を使って転送量の削減
• joinに使うフィールドは完全に同じ形式にする
  • varchar(64)のフィールドとvarchar(32)のフィールドをJOINさせないように。できればchar()のほうがよりよい
• select ... procedire analyse() で最大必要フィールド長を分析
• 不必要に長いchar,varcharを避ける
• enumの利点
  • エントリごとに1バイトのみ必要
  • 高速化
  • 想定外のデータが入らない
• NULLが入らないフィールドには NOT NULL をつけておく
• varcharよりもなるべくcharを使う。varcharは別テーブルに追いやって全フィールドの固定長化を図る
• key_buffer_size_indexをRAMの4分の1くらいにあげよう
• table_cacheをなるべく多く
• スワップはダメゼッタイ
• NFSを避ける
• 多くのハードディスクに分散させる
• ハードウェアの優先順位は RAM > HDD > CPU > ネットワーク
  • スレーブには鬼のようにメモリを積んでおきたい
• 選択性の高いインデックスだと効果がある
  • PRIMARY KEYは選択性最強。膨大な量のデータがあっても1秒程度で
  • 性別などは選択性の低いインデックス。あまり効果がない。WHEREでつなげるなら後回しに
  • WHERE句でよく一緒に使われる列をインデックスに指定する
  • WHERE句に単独で使われることもある列は、インデックスの最初の列に指定する
  • 選択性の優れている列から順に指定する
  • 選択する列も結合インデックスに含めるとさらに高速化が期待できる → DBにまったくアクセスせずに結果を返せる
    • 参照が早くなる反面更新が遅くなるので注意
• NULLはインデックスに含まれない。NULLの検索をしたいならデフォルト値の設定を検討すべき
• !=,<>の使用に注意。in,orなどの置き換えを考える
• WHERE句の条件に関数を使うとインデックスが使用されない。意味的に等価なSQL文への置き換えを考える
• order by 句にインデックスが指定されている場合、インデックス内ですでにデータがソートされているので高速(not nullの場合)
• distinctを使うと遅くなる。本当に必要なときだけ使う
• unionも重複行を削除する処理を行うので処理が遅くなる。union allを使うように
• group by句を使う場合集計するカラムだけでなく(sum()などの集計関数を使って)集計されるカラムにもインデックスをつけておくと早い