mysql index
クエリにindexを使用するのは、一つしか使えない
mysqlでは、一つのテーブルにつきいくつインデックスがはってあっても一つしか使えない
Indexマージという機能が、Mysql5.6から備わったがあまり使えない
これは、indexのタイプが違うと使えない不具合があがってる。
(複合インデックスなら使えるみたい?)
インデックスと複合インデックスの違い
インデックス
インデックスは、並び順序のこと
イメージとしては、インデックスをつけるとデータベース内のデータ列がインデックスをもとに並べられているイメージ
それで例えば、idにIndexを貼ったときだと、idを検索対象にするとDBMSがインデックスを利用して高速の検索を行ってくれる。
複合インデックス
複数の並び替え条件でインデックスを作る
複数のインデックスを考慮して、並び替えてくれる
主キーのインデックスと普通のインデックス
インデックスという点では違いがないが、主キーはDBが自動でインデックスをつけるため削除不可である
すべてにインデックスをはるデメリット
- DBMSがまちがったIndexを使ってしまう可能性がある
- 登録時にインデックスの再生成が行われる際に、おおいと多いだけ時間を使ってしまう ###デメリット操作時の対策 インデックスを指定する(ヒント句を使用する)でやれば、indexを指定できるが、めんどくさいのと、上記より登録に時間を要してしまう
インデックスは、DBMSのクエリ評価エンジンの中のオプティマイザが判断して、使っているが、オプティマイザは、その判断基準として、テーブルの統計情報を利用している
テーブルの統計情報とは
・テーブル統計(行数・ブロック数)
・列統計(カージナリティ・NULL数・最小・最大)
・索引統計(リーフブロック数・ツリーの高さ)
・システム統計(CPU性能 I/O性能)
統計情報
統計情報は、すぐに更新されるわけではない。たいていは、一定期間ごとに更新が設定されていたりするが、データ追加直後は統計情報は切り替わらない
そういった場合で、大量データだった場合しっかりメンテナンスしてあげる必要がある。
RDBMS(Oracle、Sql Server、MySql、PostgleSql)のおおまか
SQLの実行流れ
パーサー
パースを行い、RDBMSが処理しやすい形式に変換する
1、命令文のチェック(整合性)
2、定型文に変えることで、このあと機能で処理する部分が楽になる
オプティマイザ
1、カタログマネージャーから受け取った統計情報を元に、最適なデータアクセス方法を決める
カタログマネージャ
オプティマイザに情報提供する(統計情報)
https://qiita.com/NagaokaKenichi/items/5b6eb9887f88046a594d
Explain type
type速さ順
- 1.const pk or uniqueインデックスを使ったアクセス(検索)
- 2.eq_ref joinにおいてのconstと同じ
- 3.ref constでないインデックスを使って等価検索(where k = v)を行った時に使用されるアクセス
- 4.range indexを用いた範囲検索
- 5.index フルインデックススキャン、インデックス全体をスキャンしているので遅い
- 6.ALL フルテーブルスキャン、インデックスが全く使用されていないことを示す
※All以外はIndexが使用されている
複合Indexのよい利用
複合Indexをうまく効かせるには、Indexの順番を意識して使う
例えば、where文とorder by句が入っている命令文があったら、基本的にwhereしてから、order byがはたらく、つまり、whereのほうにindexの順番が前のもののほうが、有効に働く
(データ的にそう並んでいるため)これがwhereにindexの後ろのものを利用したりすると、有効に働かない
posgre index種類 チューニング
mysql と postgresql比較記事
mysql
単純なWebシステムの際に使用する
postgresql
カスタム性が高い
どっちも高機能にUpdateされるため、つかいたいほうでいいという平和的結論