SQL文でのINDEX句、CONSTRAINT句について

2017/04/18投稿ではてなブログから移行した内容です.

雑談

バイトで開発環境を構築している際に、DBのテーブルがうまく作成されていないことに気づき、
SQL文を見てみるとみたことないのが...
DBの勉強は本当の基礎しかやってこなかったので、皆さんは知っている内容だと思う。

INDEX

データの検索速度を向上させるために、どの行がどこにあるかを示す索引のことらしい。
目的のデータが見つけるまですべての行を1行ずつ調べるよりも、

索引を利用して目的の行の場所を見つけてから、そのデータを読み取った方が効率的だよねって感じらしい。

テーブルが大きいほど、効果が大きくなるみたい。

基本構文

CREATE INDEX インデックス名 ON テーブル名(列名 ASC);　※指定しなくてもASC

重複を許さない

CREATE UNIQUE INDEX インデックス名 ON テーブル名(列名 ASC);

降順

CREATE INDEX インデックス名 ON テーブル名(列名 DESC);

インデックスの削除

DROP INDEX インデックス名;

インデックスの利用

パフォーマンスの向上

表の結合条件に使用される列に対してインデックスを作成するとパフォーマンスが向上。
値の分布が大きな(異なる値が多い)列に対してインデックスを作成するとパフォーマンスが向上。

パフォーマンスの低下

値の分布が小さな列に対してインデックスを作成するとパフォーマンスが低下。
テーブルを更新すると、インデックスも更新される。
よって、更新頻度が高い場合にインデックスを利用するとパフォーマンス低下。

CONSTRAINT

英語で『強制、圧迫、束縛、制約するもの、窮屈な感じ』という意味らしい。

CONSTRAINT句はインデックスに似ているらしく、違う点としては、他のテーブルとのリレーションシップも設定できる。
MySQLは、関連データのテーブルにまたがる相互参照を可能にする外部キーと、この分散したデータの整合性を維持するために役立つ外部キー制約をサポートする。

基本構文

CONSTRAINT 制約の名前 制約

記述例

CONSTRAINT `制約の名前`
  FOREIGN KEY (`このテーブルの列名を外部キーに設定`)
  REFERENCES `データベース名`.`テーブル名` (`カラム名`)
  ON DELETE NO ACTION ←親テーブルの削除時何もしない
  ON UPDATE NO ACTION ←親テーブルの更新時何もしない