【MySQL】MySQLのインデックスあれこれ

MySQL

■ はじめに

https://dk521123.hatenablog.com/entry/2010/04/01/175501

でやったインデックスを行ったが、MySQLに絞って書く。

目次

【１】インデックス種類
【２】インデックス作成
【３】インデックス削除
【４】インデックス情報表示
【５】インデックスが使用しているか確認

 【１】インデックス種類

１）インデックス
２）ユニークインデックス
３）複合インデックス
４）部分インデックス
 ... 何バイト目までをインデックス対象とするかを指定
 ... 最大「255」まで指定。

 【２】インデックス作成

１）テーブル作成時にインデックス作成 

CREATE TABLE テーブル名
  (カラム名1 型1,カラム名2 型2, ... INDEX(カラム名1);

２）テーブル作成後にインデックス追加 

CREATE INDEX インデックス名
 ON テーブル名(カラム名1, カラム名2, ...);

３）テーブル作成後にインデックス変更 

ALTER TABLE テーブル名
 ADD INDEX(カラム名);

 サンプル

１）テーブル作成時にインデックス作成 

CREATE TABLE `employee` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `employee_no` VARCHAR(10) NOT NULL,
  `first_name` VARCHAR(50) NULL DEFAULT NULL,
  `family_name` VARCHAR(50) NULL DEFAULT NULL,
  `sex` CHAR(1) NULL DEFAULT NULL,
  `birth_date` DATE NOT NULL,
  `position_type` CHAR(1) NOT NULL,
  `remarks` VARCHAR(200) NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX(`birth_date`) -- ★
)
COMMENT='従業員'
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

２）テーブル作成後にインデックス追加 

CREATE INDEX `position_type_INDEX` ON `employee`(`position_type`);

-- ★複合インデックス★
CREATE INDEX `full_name_INDEX` ON `employee`(`first_name`, `family_name`);

３）テーブル作成後にインデックス変更 

-- ★ユニークインデックス★
ALTER TABLE `employee` ADD UNIQUE INDEX(`employee_no`);

-- ★部分インデックス★(指定したカラムの20バイト目までをインデックス対象とする)
ALTER TABLE `employee` ADD INDEX(`remarks`(20));

 【３】インデックス削除

DROP INDEX インデックス名 ON テーブル名;

 サンプル

-- [a] DROP INDEX インデックス名 ON テーブル名;
DROP INDEX `birth_date_INDEX` ON `employee`;

 【４】インデックス情報表示

Case１ 

SHOW INDEX FROM [データベース名.]テーブル名;

Case２ 

SHOW INDEX FROM テーブル名 FROM データベース名;

 サンプル

-- [i] SHOW INDEX FROM [データベース名.]テーブル名;
SHOW INDEX FROM employee;

-- [ii] SHOW INDEX FROM テーブル名 FROM データベース名;
SHOW INDEX FROM employee FROM sampledb;

 【５】インデックスが使用しているか確認

* EXPLAINステートメントを使用する
* EXPLAINステートメント については、以下の関連記事を参照のこと

https://dk521123.hatenablog.com/entry/2020/08/30/000000

 サンプル

EXPLAIN
SELECT * FROM employee AS e
 WHERE e.birth_date BETWEEN '1980-01-01' AND '1989-12-31';

 参考文献

https://weblabo.oscasierra.net/mysql-index-operation-1/
http://phpjavascriptroom.com/?t=mysql&p=index
http://tech.pjin.jp/blog/2017/05/31/mysql-key-sql2/
EXPLAINステートメント
https://thinkit.co.jp/article/9658
 公式サイト
https://dev.mysql.com/doc/refman/5.6/ja/optimization-indexes.html

 関連記事

インデックス ～ 入門編 ～
https://dk521123.hatenablog.com/entry/2010/04/01/175501
EXPLAINステートメント
https://dk521123.hatenablog.com/entry/2020/08/30/000000