情報スキーマテーブル、システムビュー、クライアントツールを活用：MySQL/MariaDBのインデックス列を詳細にリスト

2024-05-21

MySQL/MariaDB で使用するインデックス列のリスト

インデックス列は、クエリのパフォーマンスを向上させるために選択する列です。適切なインデックス列を選択することで、データベースがデータをより早く検索できるようになり、クエリの実行時間が短縮されます。

インデックス列を選択する際のヒント

頻繁に使用する列を選択する: WHERE 句や ORDER BY 句で使用される列をインデックス化すると、効果的です。
結合に使用する列を選択する: 複数のテーブルを結合する場合は、結合条件に使用する列をインデックス化すると効果的です。
選択列よりも大きい列を選択する: インデックス列は、選択列よりも大きい列を含めることができます。たとえば、WHERE id = 123 というクエリを実行する場合は、id 列だけでなく、id_index という id 列を含むインデックスを作成することもできます。
複合インデックスを使用する: 複数の列をまとめてインデックス化することができます。複合インデックスは、複数の列で検索を行う場合に効果的です。

インデックスの注意点

インデックスは、データの更新処理を遅くする可能性があります。インデックスを作成する前に、その影響を考慮する必要があります。
不要なインデックスは削除する必要があります。不要なインデックスは、ディスク領域を占有し、クエリのパフォーマンスを悪化させる可能性があります。

MySQL/MariaDB でインデックス列をリストする

以下のコマンドを使用して、MySQL/MariaDB でテーブルのインデックス列をリストすることができます。

SHOW INDEXES FROM table_name;

このコマンドを実行すると、テーブルの各インデックスについて、次の情報が表示されます。

Name: インデックスの名前
Column: インデックス列
Type: インデックスの種類 (btree、fulltext など)
Cardinality: インデックス内のエントリ数
Size: インデックスのサイズ (バイト単位)

例

次のコマンドを実行すると、customers テーブルのインデックス列が表示されます。

SHOW INDEXES FROM customers;

このコマンドの出力例を次に示します。

+----------------------------+------------+-------------------+----------+-------------+
| Name                        | Column     | Type             | Cardinality | Size       |
+----------------------------+------------+-------------------+----------+-------------+
| PRIMARY                     | id         | btree            |       123 |   492       |
| idx_first_name              | first_name | btree            |     10000 |   40000     |
| idx_last_name              | last_name  | btree            |     10000 |   40000     |
| idx_email                   | email     | btree            |      5000 |   20000     |
+----------------------------+------------+-------------------+----------+-------------+

サンプルコード：MySQL/MariaDB で顧客テーブルのインデックスを作成する

id 列のインデックス (主キー)
first_name 列のインデックス

CREATE TABLE customers (
  id INT PRIMARY KEY AUTO_INCREMENT,
  first_name VARCHAR(50) NOT NULL,
  last_name VARCHAR(50) NOT NULL,
  email VARCHAR(100) NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_first_name ON customers (first_name);
CREATE INDEX idx_last_name ON customers (last_name);

説明

CREATE TABLE ステートメントを使用して、customers テーブルを作成します。
id 列は、主キーとして定義されます。主キーは、各行を一意に識別する列です。
first_name 列と last_name 列は、NOT NULL 制約で定義されます。つまり、これらの列は空にすることはできません。
email 列は、UNIQUE 制約で定義されます。つまり、この列の値はすべて一意である必要があります。
created_at 列は、現在のタイムスタンプで自動的に設定されます。
CREATE INDEX ステートメントを使用して、first_name 列と last_name 列にインデックスを作成します。

このコードを実行すると、customers テーブルに 3 つのインデックスが作成されます。これらのインデックスにより、first_name 列または last_name 列で顧客を検索するクエリのパフォーマンスが向上します。

追加の例

複合インデックスを作成するには、複数の列を CREATE INDEX ステートメントに指定します。たとえば、次のコマンドを実行すると、first_name 列と last_name 列の複合インデックスを作成します。

CREATE INDEX idx_name ON customers (first_name, last_name);

CREATE FULLTEXT INDEX idx_ft_name ON customers (first_name, last_name);

これらの例はほんの一例です。MySQL/MariaDB でインデックスを効果的に使用する方法は他にもたくさんあります。詳細については、MySQL/MariaDB のドキュメントを参照してください。

MySQL/MariaDB でインデックス列をリストするその他の方法

情報スキーマテーブルを使用する

MySQL/MariaDB には、information_schema というデータベースがあり、その中には、データベース内のすべてのテーブルとインデックスに関する情報が含まれるテーブルがあります。これらのテーブルを使用して、インデックス列をリストすることができます。

SELECT TABLE_NAME, INDEX_NAME, COLUMN_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'your_database'
AND TABLE_NAME = 'customers'
AND INDEX_NAME IS NOT NULL;

MySQL クライアントツールを使用する

MySQL Workbench や Navicatなどの MySQL クライアントツールを使用すると、グラフィカルインターフェースを使用してインデックス列をリストすることができます。

システムビューを使用する

MySQL/MariaDB には、システムビューと呼ばれる、データベースのパフォーマンスに関する情報を提供するビューがいくつかあります。これらのビューを使用して、インデックス列をリストすることができます。

SELECT i.INDEX_NAME, c.COLUMN_NAME
FROM innodb_index i
JOIN innodb_columns c ON i.TABLE_ID = c.TABLE_ID
AND i.INDEX_ID = c.INDEX_ID
WHERE i.TABLE_NAME = 'customers';

使用する方法は、ニーズによって異なります。