MySQL Index 確認とは？INFORMATION_SCHEMA.STATISTICSを確認
MySQL Index 確認とは？
MySQLではPRIMARY KEY（PK）、FOREIGN KEY（FK）、UNIQUEはINFORMATION_SCHEMA.TABLE_CONSTRAINTSで参照しますが、単一カラムIndexやマルチカラムIndexはINFORMATION_SCHEMA.STATISTICSで参照することになります。
ここではSQL文を発行しながら単一カラムIndexやマルチカラムIndexの追加や削除を行いその結果を確認していきます。
使用したMySQLのバージョンとデータ
MySQLは、mysql-installer-community-8.0.24.0.msiをDeveloperDefaultモードでインストールして使用しています。
使用したデータはMariaDB Sample Databaseよりnation.zipをダウンロード後解凍して使用しています。解凍後Windows PowerShellを起動して以下の手順でMySQLに取込んでください。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
PS C:\> mysql -u root -p
Enter password:
～
mysql> source /path/to/nation.sql
以下の様に表示されます
mysql> use nation;
Database changed
mysql> SHOW TABLES;
+-------------------+
| Tables_in_nation |
+-------------------+
| continents |
| countries |
| country_languages |
| country_stats |
| guests |
| languages |
| region_areas |
| regions |
| vips |
+-------------------+
|
テスト用データを作成する
nationデータベースにはIndexを持つテーブルが存在しないので、テスト用に下記のように追加します。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
DROP TABLE IF EXISTS `vips_idx`;
SET character_set_client = utf8mb4 ;
CREATE TABLE `vips_idx` (
`vip_id` int(11) NOT NULL,
`name` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
LOCK TABLES `vips_idx` WRITE;
INSERT INTO `vips_idx` VALUES (1,'Jane'),(2,'Charles'),(3,'John'),(4,'Wolverine'),(5,'Rogue');
UNLOCK TABLES;
DROP TABLE IF EXISTS `regions_mlt`;
SET character_set_client = utf8mb4 ;
CREATE TABLE `regions_mlt` (
`region_id` int(11) NOT NULL,
`name` varchar(100) NOT NULL,
`continent_id` int(11) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8;
LOCK TABLES `regions_mlt` WRITE;
INSERT INTO `regions_mlt` VALUES (1,'Caribbean',1),(2,'Southern and Central Asia',2),(3,'Central Africa',3);
UNLOCK TABLES;
|
成功すれば以下の様に表示されます。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
mysql> SHOW TABLES;
+-------------------+
| Tables_in_nation |
+-------------------+
| continents |
| countries |
| country_languages |
| country_stats |
| guests |
| languages |
| region_areas |
| regions |
| regions_mlt |
| vips |
| vips_idx |
+-------------------+
|
Indexを追加する
vips_idxテーブルに単一カラムIndexとしてidx_vip_idを追加してみましょう。
MySQLでは以下のSQL文を実行します。
ALTER TABLE テーブル名 ADD INDEX Index名(カラム名);
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
mysql> ALTER TABLE vips_idx ADD INDEX idx_vip_id(vip_id);
mysql> SHOW INDEX FROM vips_idx\G
*************************** 1. row ***************************
Table: vips_idx
Non_unique: 1
Key_name: idx_vip_id
Seq_in_index: 1
Column_name: vip_id
Collation: A
Cardinality: 5
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
|
Table:vips_idxのColumn_name:vip_idにKey_name:idx_vip_idの追加が確認できます。
次にregions_mltにマルチカラムIndexとしてidx_multiを追加してみましょう。
MySQLでは以下のSQL文を実行します。
ALTER TABLE テーブル名 ADD INDEX Index名(カラム名1, カラム名2, ・・・);
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
|
mysql> ALTER TABLE regions_mlt ADD INDEX idx_multi(region_id, continent_id);
mysql> SHOW INDEX FROM regions_mlt\G
*************************** 1. row ***************************
Table: regions_mlt
Non_unique: 1
Key_name: idx_multi
Seq_in_index: 1
Column_name: region_id
Collation: A
Cardinality: 3
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
*************************** 2. row ***************************
Table: regions_mlt
Non_unique: 1
Key_name: idx_multi
Seq_in_index: 2
Column_name: continent_id
Collation: A
Cardinality: 3
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
|
Table:regions_mltのColumn_name:region_idとColumn_name:continent_idにKey_name:idx_multiの追加が確認できます。
INFORMATION_SCHEMA.STATISTICSを確認する
それではMySQLのテーブルのIndex情報を提供するINFORMATION_SCHEMA.STATISTICSを確認します。
|
1
2
3
4
5
6
7
8
9
10
|
mysql> SELECT TABLE_SCHEMA, TABLE_NAME, NON_UNIQUE, INDEX_SCHEMA, INDEX_NAME, SEQ_IN_INDEX, COLUMN_NAME
-> FROM INFORMATION_SCHEMA.STATISTICS
-> WHERE TABLE_SCHEMA = 'nation' AND INDEX_NAME <>'PRIMARY' AND INDEX_NAME <> COLUMN_NAME ;
+--------------+-------------+------------+--------------+------------+--------------+--------------+
| TABLE_SCHEMA | TABLE_NAME | NON_UNIQUE | INDEX_SCHEMA | INDEX_NAME | SEQ_IN_INDEX | COLUMN_NAME |
+--------------+-------------+------------+--------------+------------+--------------+--------------+
| nation | vips_idx | 1 | nation | idx_vip_id | 1 | vip_id |
| nation | regions_mlt | 1 | nation | idx_multi | 1 | region_id |
| nation | regions_mlt | 1 | nation | idx_multi | 2 | continent_id |
+--------------+-------------+------------+--------------+------------+--------------+--------------+
|
追加したIndexが確認できました。
表示する項目は以下から適宜選択してください。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
mysql> desc INFORMATION_SCHEMA.STATISTICS;
+---------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+---------------+------+-----+---------+-------+
| TABLE_CATALOG | varchar(64) | YES | | NULL | |
| TABLE_SCHEMA | varchar(64) | YES | | NULL | |
| TABLE_NAME | varchar(64) | YES | | NULL | |
| NON_UNIQUE | int | NO | | 0 | |
| INDEX_SCHEMA | varchar(64) | YES | | NULL | |
| INDEX_NAME | varchar(64) | YES | | NULL | |
| SEQ_IN_INDEX | int unsigned | NO | | NULL | |
| COLUMN_NAME | varchar(64) | YES | | NULL | |
| COLLATION | varchar(1) | YES | | NULL | |
| CARDINALITY | bigint | YES | | NULL | |
| SUB_PART | bigint | YES | | NULL | |
| PACKED | binary(0) | YES | | NULL | |
| NULLABLE | varchar(3) | NO | | | |
| INDEX_TYPE | varchar(11) | NO | | | |
| COMMENT | varchar(8) | NO | | | |
| INDEX_COMMENT | varchar(2048) | NO | | NULL | |
| IS_VISIBLE | varchar(3) | NO | | | |
| EXPRESSION | longtext | YES | | NULL | |
+---------------+---------------+------+-----+---------+-------+
|
EXPLAINの出力
EXPLAINを使用して、SELECTが使用するテーブルの情報を確認します。
possible_keys:idx_vip_id
key:idx_vip_id
Extra: Using index
このように、Indexが使用されていることが確認できます。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
mysql> EXPLAIN SELECT vip_id FROM vips_idx GROUP BY vip_id HAVING COUNT(vip_id) = 1
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: vips_idx
partitions: NULL
type: index
possible_keys: idx_vip_id
key: idx_vip_id
key_len: 4
ref: NULL
rows: 5
filtered: 100.00
Extra: Using index
|
同様にEXPLAINを使用して、SELECTが使用するテーブルの情報を確認します。
possible_keys:idx_multi
key:idx_multi
Extra: Using index
このように、Indexが使用されていることが確認できます。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
mysql> EXPLAIN SELECT region_id, continent_id FROM regions_mlt GROUP BY region_id, continent_id HAVING COUNT(region_id) = 1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: regions_mlt
partitions: NULL
type: index
possible_keys: idx_multi
key: idx_multi
key_len: 8
ref: NULL
rows: 3
filtered: 100.00
Extra: Using index
|
INFORMATION_SCHEMA.TABLE_CONSTRAINTSを確認する
MySQLのテーブルにある制約の情報を提供するINFORMATION_SCHEMA.TABLE_CONSTRAINTSを確認します。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
mysql> SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_SCHEMA = 'nation';
+--------------------+-------------------+--------------------------+--------------+-------------------+-----------------+----------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE | ENFORCED |
+--------------------+-------------------+--------------------------+--------------+-------------------+-----------------+----------+
| def | nation | PRIMARY | nation | continents | PRIMARY KEY | YES |
| def | nation | country_code2 | nation | countries | UNIQUE | YES |
| def | nation | country_code3 | nation | countries | UNIQUE | YES |
| def | nation | PRIMARY | nation | countries | PRIMARY KEY | YES |
| def | nation | countries_ibfk_1 | nation | countries | FOREIGN KEY | YES |
| def | nation | PRIMARY | nation | country_languages | PRIMARY KEY | YES |
| def | nation | country_languages_ibfk_1 | nation | country_languages | FOREIGN KEY | YES |
| def | nation | country_languages_ibfk_2 | nation | country_languages | FOREIGN KEY | YES |
| def | nation | PRIMARY | nation | country_stats | PRIMARY KEY | YES |
| def | nation | country_stats_ibfk_1 | nation | country_stats | FOREIGN KEY | YES |
| def | nation | PRIMARY | nation | guests | PRIMARY KEY | YES |
| def | nation | PRIMARY | nation | languages | PRIMARY KEY | YES |
| def | nation | PRIMARY | nation | region_areas | PRIMARY KEY | YES |
| def | nation | PRIMARY | nation | regions | PRIMARY KEY | YES |
| def | nation | regions_ibfk_1 | nation | regions | FOREIGN KEY | YES |
| def | nation | PRIMARY | nation | vips | PRIMARY KEY | YES |
+--------------------+-------------------+--------------------------+--------------+-------------------+-----------------+----------+
|
Indexはこのテーブルに含まれていない事が確認できます。
Indexを削除する
MySQLでは以下のSQL文を実行してIndexを削除します。
ALTER TABLE テーブル名 DROP INDEX Index名;
|
1
2
|
mysql> ALTER TABLE vips_idx DROP INDEX idx_vip_id;
mysql> ALTER TABLE regions_mlt DROP INDEX idx_multi;
|
INFORMATION_SCHEMA.STATISTICSを確認してみましょう。
|
1
2
3
4
|
mysql> SELECT TABLE_SCHEMA, TABLE_NAME, NON_UNIQUE, INDEX_SCHEMA, INDEX_NAME, SEQ_IN_INDEX, COLUMN_NAME
-> FROM INFORMATION_SCHEMA.STATISTICS
-> WHERE TABLE_SCHEMA = 'nation' AND INDEX_NAME <>'PRIMARY' AND INDEX_NAME <> COLUMN_NAME ;
Empty set (0.11 sec)
|
Indexが削除されたことが確認できました。
MySQLのIndexを試してみよう
いかがでしたでしょうか。MySQLで単一カラムIndexやマルチカラムIndexの追加や削除を行いその結果をINFORMATION_SCHEMA.STATISTICSで確認しました。
ぜひご自身で実際にSQLを実行して理解を深めてください。
FEnetを運営しているネプラス株式会社はサービス開始から10年以上
『エンジニアの生涯価値の向上』をミッションに掲げ、
多くのインフラエンジニア・ネットワークエンジニアの就業を支援してきました。
ネプラス株式会社はこんな会社です
秋葉原オフィスにはネプラス株式会社をはじめグループのIT企業が集結！
数多くのエンジニアが集まります。
-
インフラ業界に特化
ネットワーク・サーバー・データベース等、ITインフラ業界に特化。Cisco Systemsプレミアパートナーをはじめ各種ベンダーのパートナー企業です。
業界を知り尽くしているからこそ大手の取引先企業、経験豊富なエンジニアに選ばれています。
-
正社員なのにフリーランスのような働き方
正社員の方でも希望を聞いたうえでプロジェクトをアサインさせていただいており、フリーランスのような働き方が可能。帰社日もありません。
プロジェクト終了後もすぐに次の案件をご紹介させていただきますのでご安心ください。
-
大手直取引の高額案件
案件のほとんどが大手SIerやエンドユーザーからの直取引のためエンジニアの皆様へに高く還元できています。
Ciscoをはじめ、Juniper、Azure、Linux、AWS等インフラに特化した常時300件以上の案件があります。
-
スキルアップ支援
不要なコストを削減し、その分エンジニアの方へのスキルアップ支援(ネットワーク機器貸出、合格時の受験費用支給など)や給与で還元しています。
受験費用例）CCNP,CCIE:6-20万円、JNCIS:3-4万円、AWS:1-3万円など
※業務に関連する一定の資格のみ。各種条件がありますので詳しくは担当者へにお尋ねください。
-
現給与を保証します！※
前職の給与保証しており、昨年度は100%の方が給与アップを実現。収入面の不安がある方でも安心して入社していただけます。
※適用にはインフラエンジニアの業務経験1年以上、等一定の条件がございます。
-
インセンティブ制度
ネットワーク機器の販売・レンタル事業等、売上に貢献いただいた方にはインセンティブをお支払いしています。
取引先企業とエンジニア側、双方にメリットがあり大変好評をいただいています。
-
社会保険・福利厚生
社員の方は、社会保険を完備。健康保険は業界内で最も評価の高い「関東ITソフトウェア健康保険組合」です。
さらに様々なサービスをお得に利用できるベネフィットステーションにも加入いただきます。
-
東証プライム上場企業グループ
ネプラスは東証プライム上場「株式会社夢真ビーネックスグループ」のグループ企業です。
安定した経営基盤とグループ間のスムーズな連携でコロナ禍でも安定した雇用を実現させています。
ネプラス株式会社に興味を持った方へ
ネプラス株式会社では、インフラエンジニアを募集しています。
年収をアップしたい！スキルアップしたい！大手の上流案件にチャレンジしたい！
オンライン面接も随時受付中。ぜひお気軽にご応募ください。
MySQL新着案件New Job
-
サーバ構築・運用設計/東京都渋谷区/【WEB面談可/インフラサーバ経験者/20～40代の方活躍中】/在宅勤務
年収588万～588万円東京都渋谷区(渋谷駅) -
【高額年収】/基盤運用保守/東京都千代田区/【WEB面談可/インフラサーバ経験者/20～40代の方活躍中】/在宅勤務
年収600万～720万円東京都千代田区(溜池山王駅) -
【高額年収】/国内クラウドシステムのサーバ運用保守/東京都新宿区/【WEB面談可/インフラサーバ経験者/20～40代の方活躍中】/在宅勤務
年収600万～720万円東京都新宿区(東新宿駅) -
国内クラウドシステムのサーバ構築のテスター/東京都新宿区/【WEB面談可/インフラサーバ経験者/20～40代の方活躍中】/在宅勤務
年収300万～420万円東京都新宿区(東新宿駅) -
【高額年収】/社内情報システムインフラ運用保守/Windows/東京都多摩北部/【WEB面談可】/在宅勤務/20代～30代の方活躍中
年収600万～720万円東京都多摩北部(-駅) -
社内情報システムインフラ構築のテスター/Windows/東京都多摩北部/【WEB面談可】/在宅勤務/20代～30代の方活躍中
年収300万～420万円東京都多摩北部(-駅)
関連記事Related Posts
-
MySQLクライアントとは？MySQL Serverのインストール方法・MySQL Command Line ClientからMySQLに接続する方法
MySQLクライアントとは？ MySQLクライアントとは、MySQLサーバ対するクライアントのことを指します。MySQLサーバにクライアントとしてに接続し、データベース上のデータにアクセスしてデータを抽出したり、データベ […]
-
[MySQL]CSVファイルのエクスポート・インポート方法とは？基本的な使い方・オプションの指定方法
[MySQL]CSVファイルのエクスポート・インポート方法とは？ 今回は、MySQLでのCSVファイルのエクスポート・インポート方法について説明します。MySQLでは、SELECT結果をCSVファイルにエクスポートできま […]
-
[MySQL]ストアドプロシージャ（Procedure）の使い方とは？Procedureの表示や削除
[MySQL]ストアドプロシージャ（Procedure）の使い方とは？ 今回は、MySQLでのストアドプロシージャ（Procedure）について説明します。Procedureとは、DBに対する一連の操作をまとめ、関数のよ […]
-
MySQL設定とは？MySQL設定方法・初期設定・文字コード・データベース
MySQLとは？ MySQLとは、端的に言うとオープンソースのデータベース管理システムです。 簡単な個人事業主が使用する顧客リストから社内ネットワークの膨大な情報など、さまざまなデータベースを取り扱えます。 現在ではOr […]
