2009年9月16日水曜日

MySQLでUNIQUE制約はインデックスを作成するか?

スズキです。

答えは、「します」。

以下、検証結果です。

まず、下記のテーブルを用意します。

--------【SQL】--------
CREATE TABLE CMN_PREF (
  PREF_ID INTEGER AUTO_INCREMENT,
  PREF_CD CHARACTER(2) NOT NULL,
  PREF_NAME VARCHAR(255) NOT NULL,
  PREF_NAME_KANA VARCHAR(255) NOT NULL,
  INS_DATETIME DATETIME NOT NULL,
  UPD_DATETIME DATETIME,
  DEL_DATETIME DATETIME,
  PRIMARY KEY(PREF_ID)
);
--------

次にインデックスの確認です。

mysql> show index from CMN_PREF\G
*************************** 1. row ***************************
Table: CMN_PREF
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: PREF_ID
Collation: A
Cardinality: 47
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:

主キーに対するインデックスが作成されていることがわかります。

下記に、上記の項目に対する詳しい説明があります。
http://nippondanji.blogspot.com/2009/03/mysqlexplain.html

このテーブルに対して、下記SQLのように、実行計画を調べてみます。

--------【SQL】--------
EXPLAIN SELECT *
FROM CMN_PREF
WHERE
  PREF_CD = '01' AND
  DEL_DATETIME IS NULL
--------
id: 1
select_type: SIMPLE
table: CMN_PREF
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 47
Extra: Using where

PREF_CDにはインデックスが作成されていないので、
"type"が"ALL"になっており、"rows"が"47"になってしまっています。

つまり、フルテーブルスキャンがおきています。

そして下記SQLにて、PREF_CD(とDEL_DATETIME)に
UNIQUE制約をつけます。

--------【SQL】--------
ALTER TABLE CMN_PREF ADD UNIQUE (
  PREF_CD,
  DEL_DATETIME
)
--------

同様にインデックスの状況を見てみると、以下のように、
今度は、PREF_CDに対するインデックスが作成されていることがわかります。

mysql> show index from CMN_PREF\G
*************************** 1. row ***************************
Table: CMN_PREF
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: PREF_ID
Collation: A
Cardinality: 47
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
*************************** 2. row ***************************
Table: CMN_PREF
Non_unique: 0
Key_name: U1
Seq_in_index: 1
Column_name: PREF_CD
Collation: A
Cardinality: NULL
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
*************************** 3. row ***************************
Table: CMN_PREF
Non_unique: 0
Key_name: U1
Seq_in_index: 2
Column_name: DEL_DATETIME
Collation: A
Cardinality: NULL
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:

同じSQLで実行計画を確認すると、
今回は、インデックスが利用されていることがわかります。
("type"が"ref"で"rows"が"1"になっています)

id: 1
select_type: SIMPLE
table: CMN_PREF
type: ref
possible_keys: PREF_CD
key: PREF_CD
key_len: 15
ref: const,const
rows: 1
Extra: Using where

ということで上記を反映したら、今、動かしてるSQLが、かなり速くなりました…

--------
http://www.suz-lab.com

0 コメント: