データベースにインデックスを作成する際のメモ

主に、既存のテーブルに貼る際に気をつけるポイント。

MySQL

• 構文
  • CREATE INDEX index_name ON table_name(column_name[, column_name2, column_name3])
  • ALTER TABLE table name ADD INDEX [index_name] column_name[, column_name2, column_name3]
    • カラムのサイズ合計が1000byte以下にしないといけない(ujis/utf8 はchar(1) なら3byte として計算)

• インデックスの作成／再構築は重い
  • 対象テーブルが大きいほど負荷も大きい
    • スロークエリログの設定をしておくと、long_query_time に達した場合は記録される
  • InnoDB だど、張り替えの際にテーブルが再構築されてしまう。これは、CREATE/ALTER TABLE のどちらを使ってインデックスを作成しても発生する
    • 将来的に解決される問題とのこと

• インデックス作成に関連する設定
  • MyISAM
    • myisam_sort_buffer_size

• インデックスを張り替えはwrite ロックがかかる
  • MyISAM では、write ロックとread ロックは競合し、write ロックとread ロックが競合した場合、常にwrite ロックが優先される。よってインデックス構築時にはwrite/read 処理は一切行えない
    • InnoDB ではwrite／read ロックは競合しない。よって、インデックス構築中でもread は行える
  • ロックのこと忘れた際の災難＋その際の対処
    • RwJ:MySQL MyISAMテーブルロックによって最大接続数を埋めてしまったときの対処法

PostgreSQL

• インデックス張り替え中でもread/write できる

参考

• ロック