MariaDB(MySQL)のインデックスの確認と設定方法をSQLで解説
データベースを作成してテーブルを作成
解説サンプルとして「db_test」データベースを作成して「t_user」テーブルを作成します。
データベースの作成
CREATE DATABASE IF NOT EXISTS db_test DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
MariaDB [(none)]> CREATE DATABASE IF NOT EXISTS db_test DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; Query OK, 1 row affected (0.007 sec)
データベース切り替え
USE db_test;
MariaDB [(none)]> USE db_test; Database changed
テーブルの作成
テーブルの作成と同時にインデックスも作成します。
CREATE TABLE IF NOT EXISTS t_user(
user_id SERIAL COMMENT 'ユーザーID', -- ユニークインデックス自動作成 user_no VARCHAR(8) NOT NULL DEFAULT '00000000' COMMENT '社員番号', user_name VARCHAR(30) NOT NULL COMMENT 'ユーザー名', user_birth DATE NOT NULL DEFAULT '2000-01-01' COMMENT '誕生日', user_update DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, user_regdate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE INDEX idx_user_no(user_no), -- ユニークインデックスを作成 INDEX idx_user_birth(user_birth) -- インデックスを作成 );
MariaDB [db_test]> CREATE TABLE IF NOT EXISTS t_user(
-> user_id SERIAL COMMENT 'ユーザーID',
-> user_no VARCHAR(8) NOT NULL DEFAULT '00000000' COMMENT '社員番号',
-> user_name VARCHAR(30) NOT NULL COMMENT 'ユーザー名',
-> user_birth DATE NOT NULL DEFAULT '2000-01-01' COMMENT '誕生日',
-> user_update DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-> user_regdate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
-> UNIQUE INDEX idx_user_no(user_no),
-> INDEX idx_user_birth(user_birth)
-> );
Query OK, 0 rows affected (0.022 sec)
インデックスの確認
インデックスの確認は以下コマンドで確認できます。
SHOW INDEX FROM テーブル名;
「t_user」テーブルのインデックスを確認してみます。
MariaDB [db_test]> SHOW INDEX FROM t_user;
+--------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
+--------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| t_user | 0 | user_id | 1 | user_id | A | 0 | NULL | NULL | | BTREE | | | NO |
| t_user | 0 | idx_user_no | 1 | user_no | A | 0 | NULL | NULL | | BTREE | | | NO |
| t_user | 1 | idx_user_birth | 1 | user_birth | A | 0 | NULL | NULL | | BTREE | | | NO |
+--------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
3 rows in set (0.003 sec)
「user_id」と「user_no」カラムにユニークインデックス、 「user_birth」カラムにインデックスが付与されているのが確認できます。
インデックスの作成
インデックスを作成するコマンドは以下です。
CREATE [UNIQUE] INDEX インデックス名 ON テーブル名 (カラム名 [,カラム名 ,...]);
「t_user」テーブルの「user_name」カラムにインデックスを作成してみます。
CREATE INDEX idx_user_name ON t_user(user_name);
MariaDB [db_test]> CREATE INDEX idx_user_name ON t_user(user_name); Query OK, 0 rows affected (0.024 sec) Records: 0 Duplicates: 0 Warnings: 0
インデックスが作成できたか確認します。
MariaDB [db_test]> SHOW INDEX FROM t_user;
+--------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
+--------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| t_user | 0 | user_id | 1 | user_id | A | 0 | NULL | NULL | | BTREE | | | NO |
| t_user | 0 | idx_user_no | 1 | user_no | A | 0 | NULL | NULL | | BTREE | | | NO |
| t_user | 1 | idx_user_birth | 1 | user_birth | A | 0 | NULL | NULL | | BTREE | | | NO |
| t_user | 1 | idx_user_name | 1 | user_name | A | 0 | NULL | NULL | | BTREE | | | NO |
+--------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
4 rows in set (0.002 sec)
インデックスが作成できているのが確認できました。
インデックスの削除
インデックスの削除コマンドは以下です。
DROP INDEX インデックス名 ON テーブル名;
「t_user」テーブルの「idx_user_birth」インデックスを削除してみます。
ROP INDEX idx_user_birth ON t_user;
MariaDB [db_test]> DROP INDEX idx_user_birth ON t_user; Query OK, 0 rows affected (0.013 sec) Records: 0 Duplicates: 0 Warnings: 0
インデックスが削除できたか確認します。
MariaDB [db_test]> SHOW INDEX FROM t_user; +--------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored | +--------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+ | t_user | 0 | user_id | 1 | user_id | A | 0 | NULL | NULL | | BTREE | | | NO | | t_user | 0 | idx_user_no | 1 | user_no | A | 0 | NULL | NULL | | BTREE | | | NO | | t_user | 1 | idx_user_name | 1 | user_name | A | 0 | NULL | NULL | | BTREE | | | NO | +--------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+ 3 rows in set (0.002 sec)