インデックス：データ定義（SQL文）インデックスの作成・削除・情報表示・追加
インデックス作成
unknown
CREATE TABLE テーブル名(カラム名 型,カラム名 型, ... INDEX(カラム名);
または
CREATE INDEX インデックス名 ON テーブル名(カラム名);
または
ALTER TABLE テーブル名 ADD INDEX インデックス名(カラム名);
または
CREATE INDEX インデックス名 ON テーブル名(カラム名);
または
ALTER TABLE テーブル名 ADD INDEX インデックス名(カラム名);
検索するデータ量が多い場合など、検索対象のカラムにインデックスを作成しておきましょう。
インデックスを作成しないと、検索対象のカラムのデータを全部検索するため、データベースに負担がかかりすぎ落ちることもあります。
インデックスを作成しておけば、カラムに設定されたインデックスを検索するので、データベースの負担も軽減し、なおかつ検索効率がアップします。
主キーを設定したカラム名には、自動的にインデックスが作成されますので、あえて作成する必要はありません。
データベース作成時のCREATE TABLE文でインデックス作成
データベース作成時に、インデックスも一緒に作成できます。
CREATE TABLE tbl_Address(
UserID INT NOT NULL PRIMARY KEY,
Zip CHAR(8) DEFAULT NULL,
Ken CHAR(5) DEFAULT NULL,
Addr VARCHAR(200) DEFAULT NULL,
INDEX idx_zip(Zip)
);
CREATE INDEX文によるインデックス作成
# tbl_Addressテーブルのインデックス情報を確認 mysql> SHOW INDEX FROM tbl_Address; Enter +-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | tbl_Address | 0 | PRIMARY | 1 | UserID | A | 4 | NULL | NULL | | BTREE | | +-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 1 row in set (0.00 sec) # Zipフィールドにインデックス idx_zip を作成 mysql> CREATE INDEX idx_zip ON tbl_Address(Zip); Enter Query OK, 4 rows affected (0.51 sec) Records: 4 Duplicates: 0 Warnings: 0 # インデックス idx_zip が作成されているか確認 mysql> SHOW INDEX FROM tbl_Address; Enter +-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | tbl_Address | 0 | PRIMARY | 1 | UserID | A | 4 | NULL | NULL | | BTREE | | | tbl_Address | 1 | idx_zip | 1 | Zip | A | NULL | NULL | NULL | YES | BTREE | | +-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 2 rows in set (0.01 sec)
ALTER TABLE文によるインデックス作成
テーブル作成後でも、後付でインデックスを作成することができます。
# tbl_Addressテーブルのインデックス情報を確認 mysql> SHOW INDEX FROM tbl_Address; Enter +-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | tbl_Address | 0 | PRIMARY | 1 | UserID | A | 4 | NULL | NULL | | BTREE | | +-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 1 row in set (0.00 sec) # Zipフィールドに インデックス idx_zip を追加作成 mysql> ALTER TABLE tbl_Address ADD INDEX idx_zip(ZIP); Enter Query OK, 4 rows affected (0.51 sec) Records: 4 Duplicates: 0 Warnings: 0 # インデックス idx_zip が作成されているか確認 mysql> SHOW INDEX FROM tbl_Address; Enter +-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | tbl_Address | 0 | PRIMARY | 1 | UserID | A | 4 | NULL | NULL | | BTREE | | | tbl_Address | 1 | idx_zip | 1 | Zip | A | NULL | NULL | NULL | YES | BTREE | | +-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 2 rows in set (0.01 sec)
部分インデックスの作成
unknown
CREATE TABLE テーブル名(カラム名 型,カラム名 型, ... INDEX(カラム名(バイト数));
または
CREATE INDEX インデックス名 ON テーブル名(カラム名(バイト数));
または
ALTER TABLE テーブル名 ADD INDEX インデックス名(フィールド名(バイト数));
または
CREATE INDEX インデックス名 ON テーブル名(カラム名(バイト数));
または
ALTER TABLE テーブル名 ADD INDEX インデックス名(フィールド名(バイト数));
バイト数には、指定したカラムの何バイト目までをインデックス対象とするかを指定します。
最大「255」までの数値を指定できます。
データベース作成時のCREATE TABLE文で部分インデックス作成
CREATE TABLE tbl_Customer(
UserID INT NOT NULL PRIMARY KEY,
FirstName VARCHAR(32) NOT NULL,
LastName VARCHAR(32) NOT NULL,
Sex CHAR(2) DEFAULT 0,
Age INT DEFAULT NULL,
INDEX part_of_FistName(FirstName(5))
);
CREATE INDEX文による部分インデックス作成
# tbl_Customerテーブルのインデックス情報を確認 mysql> SHOW INDEX FROM tbl_Customer; Enter +--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | tbl_Customer | 0 | PRIMARY | 1 | UserID | A | 7 | NULL | NULL | | BTREE | | +--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 1 row in set (0.00 sec) # FirstNameフィールドの先頭から5バイトまでをインデックス化 mysql> CREATE INDEX part_of_FirstName ON tbl_Customer(FirstName(5)); Enter Query OK, 7 rows affected (0.13 sec) Records: 7 Duplicates: 0 Warnings: 0 # 部分インデックス part_of_FirstName が作成されているか確認 mysql> SHOW INDEX FROM tbl_Customer; Enter +--------------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +--------------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | tbl_Customer | 0 | PRIMARY | 1 | UserID | A | 7 | NULL | NULL | | BTREE | | | tbl_Customer | 1 | part_of_FirstName | 1 | FirstName | A | NULL | 5 | NULL | | BTREE | | +--------------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 2 rows in set (0.01 sec)
ALTER TABLE文による部分インデックス作成
# tbl_Customerテーブルのインデックス情報を確認 mysql> SHOW INDEX FROM tbl_Customer; Enter +--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | tbl_Customer | 0 | PRIMARY | 1 | UserID | A | 7 | NULL | NULL | | BTREE | | +--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 1 row in set (0.00 sec) # FirstNameフィールドの先頭から5バイトまでをインデックス化 mysql> CREATE INDEX part_of_FirstName ON tbl_Customer(FirstName(5)); Enter Query OK, 7 rows affected (0.13 sec) Records: 7 Duplicates: 0 Warnings: 0 # 部分インデックス part_of_FirstName が作成されているか確認 mysql> SHOW INDEX FROM tbl_Customer; Enter +--------------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +--------------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | tbl_Customer | 0 | PRIMARY | 1 | UserID | A | 7 | NULL | NULL | | BTREE | | | tbl_Customer | 1 | part_of_FirstName | 1 | FirstName | A | NULL | 5 | NULL | | BTREE | | +--------------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 2 rows in set (0.00 sec)
ユニークインデックスの作成
unknown
CREATE UNIQUE INDEX インデックス名 ON テーブル名(カラム名);
インデックスの対象となるカラムに含まれる値に重複する値が含まれないように制限をかけたい場合に使用します。
# tbl_Addressのインデックス情報を確認 mysql> SHOW INDEX FROM tbl_Address; Enter +-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | tbl_Address | 0 | PRIMARY | 1 | UserID | A | 4 | NULL | NULL | | BTREE | | +-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 1 row in set (0.00 sec) # Zipフィールドに idx_zip という名前のユニークインデックスを作成 mysql> CREATE UNIQUE INDEX idx_zip ON tbl_Address(Zip); Enter Query OK, 4 rows affected (0.10 sec) Records: 4 Duplicates: 0 Warnings: 0 # ユニークインデックス idx_zip が作成されているか確認 mysql> SHOW INDEX FROM tbl_Address; Enter +-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | tbl_Address | 0 | PRIMARY | 1 | UserID | A | 4 | NULL | NULL | | BTREE | | | tbl_Address | 0 | idx_zip | 1 | Zip | A | NULL | NULL | NULL | YES | BTREE | | +-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 2 rows in set (0.00 sec) # tbl_Addressのテーブル情報を確認 # ユニークインデックスを作成したZipフィールドが「MUL」になっていればOK mysql> DESCRIBE tbl_Address; Enter +--------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+--------------+------+-----+---------+----------------+ | UserID | int(11) | | PRI | NULL | auto_increment | | Zip | varchar(8) | YES | MUL | NULL | | ←MUL | Ken | varchar(5) | YES | | NULL | | | Addr | varchar(200) | YES | | NULL | | +--------+--------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec)
複合インデックスの作成
unknown
CREATE INDEX インデックス名 ON テーブル名 (カラム名1([数値])[, カラム名2([数値]), ...);
以下の例では、「姓＋名」のフィールドを合わせたインデックスを作成しています。
フルネームで検索するケースがある場合に、検索スピードがアップします。
# tbl_Customerテーブルのインデックス情報を確認 mysql> SHOW INDEX FROM tbl_Customer; Enter +--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | tbl_Customer | 0 | PRIMARY | 1 | UserID | A | 7 | NULL | NULL | | BTREE | | +--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 1 row in set (0.01 sec) # LastNameフィールドとFirstNameフィールドの複合インデックスを作成 mysql> CREATE INDEX idx_FullName ON tbl_Customer(LastName, FirstName); Enter Query OK, 7 rows affected (0.46 sec) Records: 7 Duplicates: 0 Warnings: 0 # 部分インデックス part_of_FirstName が作成されているか確認 mysql> SHOW INDEX FROM tbl_Customer; Enter +--------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +--------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | tbl_Customer | 0 | PRIMARY | 1 | UserID | A | 7 | NULL | NULL | | BTREE | | | tbl_Customer | 1 | idx_FullName | 1 | LastName | A | NULL | NULL | NULL | | BTREE | | | tbl_Customer | 1 | idx_FullName | 2 | FirstName | A | NULL | NULL | NULL | | BTREE | | +--------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 3 rows in set (0.01 sec)
インデックス削除
unknown
ALTER TABLE テーブル名 DROP INDEX インデックス名;
テーブルから、インデックスを削除します。
# tbl_Addressテーブルのインデックス情報を確認 mysql> SHOW INDEX FROM tbl_Address; Enter +-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | tbl_Address | 0 | PRIMARY | 1 | UserID | A | 4 | NULL | NULL | | BTREE | | | tbl_Address | 0 | idx_zip | 1 | Zip | A | NULL | NULL | NULL | YES | BTREE | | +-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 2 rows in set (0.00 sec) # tbl_Addressテーブルから、インデックス idx_zip を削除 mysql> ALTER TABLE tbl_Address DROP INDEX idx_zip; Enter Query OK, 4 rows affected (0.50 sec) Records: 4 Duplicates: 0 Warnings: 0 # インデックス idx_zip が削除されているか確認 mysql> SHOW INDEX FROM tbl_Address; Enter +-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | tbl_Address | 0 | PRIMARY | 1 | UserID | A | 4 | NULL | NULL | | BTREE | | +-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 1 row in set (0.00 sec)
インデックス情報表示
SHOW INDEX
unknown
SHOW INDEX FROM [データベース名.]テーブル名;
または
SHOW INDEX FROM テーブル名 FROM データベース名;
または
SHOW INDEX FROM テーブル名 FROM データベース名;
指定したテーブルのインデックス情報を表示します。
# インデックス情報取得 mysql> SHOW INDEX FROM tbl_Customer; Enter +--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | tbl_Customer | 0 | PRIMARY | 1 | UserID | A | 7 | NULL | NULL | | BTREE | | +--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 1 row in set (0.09 sec) # データベース選択も同時に行う場合 mysql> SHOW INDEX FROM tbl_Customer FROM db_Customer; Enter +--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | tbl_Customer | 0 | PRIMARY | 1 | UserID | A | 7 | NULL | NULL | | BTREE | | +--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 1 row in set (0.01 sec)
インデックスにソート方法を指定する場合
unknown
CREATE TABLE テーブル名(カラム名 型,カラム名 型, ... INDEX(カラム名 ソート);
または
CREATE INDEX インデックス名 ON テーブル名(カラム名 ソート);
または
ALTER TABLE テーブル名 ADD INDEX インデックス名(カラム名 ソート);
または
CREATE INDEX インデックス名 ON テーブル名(カラム名 ソート);
または
ALTER TABLE テーブル名 ADD INDEX インデックス名(カラム名 ソート);
ソートには、ASC（昇順）またはDESC（降順）を指定できます。
ソートの指定がない場合は、デフォルトのASC（昇順）が適用されます。
データベース作成時にインデックスを作成
データベース作成時に、ソート方法を指定ありのインデックスも一緒に作成できます。
CREATE TABLE tbl_Address(
UserID INT NOT NULL PRIMARY KEY,
Zip CHAR(8) DEFAULT NULL,
Ken CHAR(5) DEFAULT NULL,
Addr VARCHAR(200) DEFAULT NULL,
INDEX idx_zip(Zip DESC)
);
CREATE INDEX文の使用例
# tbl_Addressテーブルのインデックス情報を確認 mysql> SHOW INDEX FROM tbl_Address; Enter +-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | tbl_Address | 0 | PRIMARY | 1 | UserID | A | 4 | NULL | NULL | | BTREE | | +-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 1 row in set (0.00 sec) # インデックス idx_Zip を降順で作成 mysql> CREATE INDEX idx_Zip ON tbl_Address (Zip DESC); Enter Query OK, 4 rows affected (0.12 sec) Records: 4 Duplicates: 0 Warnings: 0 # インデックス idx_Zip が作成されたか確認 mysql> SHOW INDEX FROM tbl_Address; Enter +-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | tbl_Address | 0 | PRIMARY | 1 | UserID | A | 4 | NULL | NULL | | BTREE | | | tbl_Address | 1 | idx_Zip | 1 | Zip | A | NULL | NULL | NULL | YES | BTREE | | +-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 2 rows in set (0.00 sec)
ALTER TABLE文の使用例
# tbl_Addressテーブルのインデックス情報を確認 mysql> SHOW INDEX FROM tbl_Address; Enter +-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | tbl_Address | 0 | PRIMARY | 1 | UserID | A | 4 | NULL | NULL | | BTREE | | +-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 1 row in set (0.00 sec) # インデックス idx_Zip を昇順で作成 mysql> ALTER TABLE tbl_Address ADD INDEX idx_Zip(Zip ASC); Enter Query OK, 4 rows affected (0.14 sec) Records: 4 Duplicates: 0 Warnings: 0 # インデックス idx_Zip が作成されたか確認 mysql> SHOW INDEX FROM tbl_Address; Enter +-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | tbl_Address | 0 | PRIMARY | 1 | UserID | A | 4 | NULL | NULL | | BTREE | | | tbl_Address | 1 | idx_Zip | 1 | Zip | A | NULL | NULL | NULL | YES | BTREE | | +-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 2 rows in set (0.00 sec)