MySQLのインデックスをチューニングしてクエリのパフォーマンスを最大限に引き出す

2024-04-02

MySQLでデータベースまたはテーブルのインデックスを表示する方法

MySQLでは、いくつかの方法でデータベースまたはテーブルのインデックスを表示できます。

方法 1: SHOW INDEXES ステートメントを使用する

この方法は、特定のデータベースまたはテーブルのすべてのインデックスを表示するのに最も簡単です。

# データベースのすべてのインデックスを表示
SHOW INDEXES FROM <データベース名>;

# 特定のテーブルのインデックスを表示
SHOW INDEXES FROM <データベース名>.<テーブル名>;

出力例:

Table: customers
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 100
Sub_part: NULL
Packed: NULL
Null: NO
Index_type: BTREE
Comment:

出力の見方:

Table: インデックスが属するテーブル名
Non_unique: インデックスがユニークかどうか (0=ユニーク、1=ユニークではない)
Key_name: インデックスの名前
Seq_in_index: インデックス内の列の順序
Column_name: インデックスに含まれる列名
Collation: 文字列の照合順序
Cardinality: インデックス内の異なる値の推定数
Sub_part: 部分インデックスの場合、その長さを示す
Packed: NULL 値の扱い (0=パックなし、1=パックあり)
Null: インデックスに NULL 値が含まれるかどうか (YES=含まれる、NO=含まれない)
Index_type: インデックスの種類 (BTREE=Bツリー、HASH=ハッシュ)
Comment: インデックスに関するコメント

方法 2: INFORMATION_SCHEMA データベースを使用する

INFORMATION_SCHEMA データベースには、MySQL サーバーに関するさまざまな情報が含まれています。このデータベースの TABLES テーブルには、各テーブルのインデックスに関する情報も含まれています。

SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = '<データベース名>'
AND TABLE_NAME = '<テーブル名>';

TABLE_NAME: customers
TABLE_SCHEMA: my_database
ENGINE: InnoDB
VERSION: 10
ROW_FORMAT: Dynamic
TABLE_ROWS: 100
AVG_ROW_LENGTH: 80
DATA_LENGTH: 8000
MAX_DATA_LENGTH: 1048576
INDEX_LENGTH: 1200
DATA_FREE: 0
AUTO_INCREMENT: 11
CREATE_TIME: 2023-11-14 10:23:56
UPDATE_TIME: 2023-11-14 10:23:56
CHECK_TIME: NULL
TABLE_COLLATION: utf8mb4_general_ci
CHECKSUM: NULL
CREATE_OPTIONS: 
TABLE_COMMENT:

TABLE_NAME: テーブル名
TABLE_SCHEMA: データベース名
ENGINE: 使用されているストレージエンジン
VERSION: テーブルフォーマットのバージョン
ROW_FORMAT: 行フォーマット
TABLE_ROWS: テーブル内の行数
AVG_ROW_LENGTH: 平均行長
DATA_LENGTH: データ領域の長さ
INDEX_LENGTH: インデックス領域の長さ
DATA_FREE: 空いているデータ領域
AUTO_INCREMENT: 自動的に増加される列の値
CREATE_TIME: テーブルの作成日時
TABLE_COLLATION: 文字列の照合順序
CREATE_OPTIONS: テーブル作成時のオプション

方法 3: MySQL クライアントツールを使用する

MySQL Workbench などの MySQL クライアントツールを使用して、データベースまたはテーブルのインデックスを表示することもできます。

**ツールによって操作方法は異なりますが、一般的には以下の手順で

# データベースのすべてのインデックスを表示
SHOW INDEXES FROM my_database;

# 特定のテーブルのインデックスを表示
SHOW INDEXES FROM my_database.customers;

SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'my_database'
AND TABLE_NAME = 'customers';

MySQL Workbench の場合:

データベースに接続します。
ナビゲーションパネルで、SCHEMAS > my_database > Tables > customers を展開します。
Indexes タブをクリックします。

その他のツールの場合:

使用しているツールのドキュメントを参照してください。

上記の方法のいずれかを使用して、MySQL データベースまたはテーブルのインデックスを表示できます。

他の方法

EXPLAIN ステートメントは、クエリの実行計画を表示します。この情報を使用して、クエリがどのように実行され、インデックスがどのように使用されているかを理解することができます。

EXPLAIN SELECT * FROM customers;

id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
------- | -------- | -------- | -------- | -------- | -------- | -------- | -------- | -------- | -------- | -------- |
1 | SIMPLE | customers | ALL | NULL | NULL | NULL | NULL | 100 | 100 | Using where; Using index condition; |

id: クエリの実行計画内の行番号
select_type: クエリのタイプ (SIMPLE=単純なSELECT、PRIMARY=主キー検索など)
table: 使用されるテーブル
type: テーブルアクセス方法 (ALL=全行スキャン、index=インデックススキャンなど)
possible_keys: 使用可能なインデックス
key: 実際に使用されたインデックス
ref: インデックス参照
rows: 処理される行数
filtered: フィルター処理される行数
Extra: 追加情報

方法 5: mysqldumpslow ユーティリティを使用する

mysqldumpslow ユーティリティは、実行時間が長いクエリに関する情報を表示します。この情報を使用して、パフォーマンスを向上させるためにインデックスをどのように追加または変更できるかを判断することができます。

mysqldumpslow -s t > slow_queries.log

Count | Time | Query
------- | -------- | --------
100 | 10.00 | SELECT * FROM customers WHERE name LIKE '%a%';

Count: クエリの実行回数
Time: クエリの実行時間
Query: クエリ

方法 6: パフォーマンススキーマを使用する

パフォーマンススキーマは、MySQL サーバーのパフォーマンスに関する情報を収集します。この情報を使用して、インデックスの使用状況を監視し、パフォーマンスの問題を診断することができます。

SELECT * FROM performance_schema.events_waits_summary_by_index;

event_name | index_name | count_star | wait_sum | avg_wait |
------- | -------- | -------- | -------- | -------- |
SELECT | customers_primary | 100 | 10.00 | 0.10 |

event_name: イベント名
count_star: イベント発生回数
wait_sum: イベント待機時間合計
avg_wait: イベント平均待機時間

mysql indexing database-schema