MySQL インデックス確認｜SHOW INDEXの見方と追加・削除

MySQLでテーブルのインデックスを確認するには、SHOW INDEX FROM テーブル名; を実行します。 インデックス1つにつき「構成する列の数だけ」行が返り、名前、列の順番、ユニークかどうか、値の種類数の推定（Cardinality）、オプティマイザから見えるか（Visible）などが一覧できます。

この記事では、SHOW INDEXの全15列の読み方を一覧表にまとめ、複合インデックス・前方一致インデックス・式インデックスの出力例を示します。さらに、CardinalityがANALYZE TABLEで変わる様子、インデックスの追加・削除・名前変更、INVISIBLEを使った試し外し、sysスキーマで不要・重複インデックスを洗い出すSQLまで、すべて実行した結果つきで順に見ていきます。

確認環境：Ubuntu 24.04、MySQL 8.0.46（InnoDB、mysqlコマンドラインクライアントで実行）。

SHOW INDEXの列の早見表

やりたいこと別のコマンドは次のとおりです。

SHOW INDEXでインデックスを確認する

検証用のテーブル

主キー、ユニークインデックス、2列の複合インデックス、先頭10文字だけの前方一致インデックスを持つテーブルを用意します。

CREATE TABLE orders (
  id          BIGINT AUTO_INCREMENT PRIMARY KEY,
  order_no    VARCHAR(20) NOT NULL,
  member_id   INT NOT NULL,
  status      VARCHAR(10) NOT NULL,
  note        VARCHAR(200) NULL,
  ordered_at  DATETIME NOT NULL,
  UNIQUE KEY uk_order_no (order_no),
  KEY idx_member_ordered (member_id, ordered_at),
  KEY idx_note (note(10))
) ENGINE=InnoDB;

SET SESSION cte_max_recursion_depth = 100000;
INSERT INTO orders (order_no, member_id, status, note, ordered_at)
WITH RECURSIVE seq (n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM seq WHERE n < 50000)
SELECT CONCAT('ORD-', LPAD(n, 8, '0')), 1 + (n % 500),
       ELT(1 + (n % 4), 'new', 'paid', 'shipped', 'done'),
       IF(n % 5 = 0, NULL, CONCAT('memo ', n)),
       '2026-01-01' + INTERVAL n MINUTE
FROM seq;
ANALYZE TABLE orders;

5万行で、member_id は500種類、status は4種類、note は5行に1行がNULLです。

実行結果（複合インデックスを含む出力例）

SHOW INDEX FROM orders;

+--------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table  | Non_unique | Key_name           | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| orders |          0 | PRIMARY            |            1 | id          | A         |       50165 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| orders |          0 | uk_order_no        |            1 | order_no    | A         |       49521 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| orders |          1 | idx_member_ordered |            1 | member_id   | A         |         494 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| orders |          1 | idx_member_ordered |            2 | ordered_at  | A         |       49286 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| orders |          1 | idx_note           |            1 | note        | A         |       40680 |       10 |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+--------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
5 rows in set

インデックスは4つですが、行は5つあります。idx_member_ordered が2行に分かれているのは、2列の複合インデックスだからです。 読み取れることを整理します。

横に長いときは \G で縦に並べる

列が多いので、ターミナルの幅に収まらないときは文末を \G にします。

SHOW INDEX FROM orders\G

*************************** 3. row ***************************
        Table: orders
   Non_unique: 1
     Key_name: idx_member_ordered
 Seq_in_index: 1
  Column_name: member_id
    Collation: A
  Cardinality: 494
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: NULL

（5行のうち3行目を載せています。）

絞り込み・別名・データベース指定

WHERE で列名を使って絞り込めます。SHOW KEYS と SHOW INDEXES は同じ結果を返す別名で、IN データベース名 で別のデータベースのテーブルも指定できます。

SHOW KEYS FROM orders IN t270 WHERE Non_unique = 0;

+--------+------------+-------------+--------------+-------------+-----------+-------------+ ...
| Table  | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | ...
+--------+------------+-------------+--------------+-------------+-----------+-------------+ ...
| orders |          0 | PRIMARY     |            1 | id          | A         |       50165 | ...
| orders |          0 | uk_order_no |            1 | order_no    | A         |       49521 | ...
+--------+------------+-------------+--------------+-------------+-----------+-------------+ ...
2 rows in set

（右側の列は省略しています。）

各列の読み方を詳しく見る

Non_uniqueとKey_name：ユニークかどうかと名前

Non_uniqueは「ユニークではない」の意味なので、0がユニーク、1が重複可です。 直感と逆に感じやすいところなので、「0＝重複なし」と覚えておくと読み違えません。Key_nameが PRIMARY の行が主キーです。

Seq_in_index：複合インデックスの列順

複合インデックスは、Seq_in_indexが1の列から順に絞り込みに使われます。idx_member_ordered (member_id, ordered_at) は、member_id での検索や member_id と ordered_at の両方での検索に使えます。(ordered_at, member_id) という逆順のインデックスとは別物なので、列の順番まで確認することが大切です。

インデックスごとに列を1行にまとめて見たいときは、information_schema.STATISTICS を使います。

SELECT INDEX_NAME, GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) AS cols,
       MAX(NON_UNIQUE) AS non_unique
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 't270' AND TABLE_NAME = 'orders'
GROUP BY INDEX_NAME;

+--------------------+----------------------+------------+
| INDEX_NAME         | cols                 | non_unique |
+--------------------+----------------------+------------+
| idx_member_ordered | member_id,ordered_at |          1 |
| idx_note           | note                 |          1 |
| PRIMARY            | id                   |          0 |
| uk_order_no        | order_no             |          0 |
+--------------------+----------------------+------------+

TABLE_NAME の条件を外せば、データベース内の全テーブルのインデックスを一度に一覧できます。

Cardinality：ユニークな値の数の推定

MySQLのリファレンスマニュアルでは、Cardinalityは「インデックス内のユニークな値の数の推定」と説明されています。整数で保持された統計から数えるため、小さなテーブルでも正確とは限らないこと、値が大きいほど結合でそのインデックスが使われやすいことも書かれています。

実際の値と比べてみます。

SELECT COUNT(*), COUNT(DISTINCT member_id),
       COUNT(DISTINCT member_id, ordered_at), COUNT(DISTINCT LEFT(note,10))
FROM orders;

+----------+---------------------------+---------------------------------------+-------------------------------+
| COUNT(*) | COUNT(DISTINCT member_id) | COUNT(DISTINCT member_id, ordered_at) | COUNT(DISTINCT LEFT(note,10)) |
+----------+---------------------------+---------------------------------------+-------------------------------+
|    50000 |                       500 |                                 50000 |                         40000 |
+----------+---------------------------+---------------------------------------+-------------------------------+

主キーでも50165と、実際の行数50000とは少しずれています。Cardinalityは「だいたいの桁と大小関係」を読むための数字です。行数に近いほど絞り込みやすい（選択性が高い）インデックス、行数に比べて極端に小さいほど絞り込みにくいインデックス、という見方をします。

Sub_part・Collation・Null

• Sub_part：note(10) のように先頭の一部だけを索引にした場合、その文字数が入ります。NULLなら列全体です
• Collation：A は昇順、D は降順です。MySQL 8.0では ordered_at DESC のような降順インデックスを作れ、そのとき D になります
• Null：YES ならNULLを含みうる列です。NOT NULLの列は空文字で表示されます

VisibleとExpression：末尾の2列

2018年ごろの解説で見かける出力は Index_comment までの13列ですが、MySQL 8.0の出力は末尾に Visible と Expression の2列があり、15列です。式インデックス（関数インデックス）と降順インデックスを追加して、表示を確かめます。

ALTER TABLE orders ADD INDEX idx_status (status);
CREATE INDEX idx_ordered_at ON orders (ordered_at DESC);
ALTER TABLE orders ADD INDEX idx_note_upper ((UPPER(note)));
ALTER TABLE orders ADD INDEX idx_member (member_id), ALGORITHM=INPLACE, LOCK=NONE;

SHOW INDEX FROM orders;

追加した4つのインデックスの行を、要点の列に絞って載せます。

| Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Null | Visible | Expression    |
+----------------+--------------+-------------+-----------+-------------+------+---------+---------------+
| idx_status     |            1 | status      | A         |           3 |      | YES     | NULL          |
| idx_ordered_at |            1 | ordered_at  | D         |       48888 |      | YES     | NULL          |
| idx_member     |            1 | member_id   | A         |         502 |      | YES     | NULL          |
| idx_note_upper |            1 | NULL        | A         |       39627 | YES  | YES     | upper(`note`) |

• idx_ordered_at は降順なので Collation が D
• idx_note_upper は式インデックスなので Column_name が NULL、Expression に式 が入る
• idx_status の Cardinality は3（実際は4種類）。行数5万に対して極端に小さく、単独では絞り込みにくいインデックスだと読み取れます

SHOW EXTENDED INDEXで内部的な列も見る

EXTENDED を付けると、MySQLが内部で使う隠れた情報も表示されます。InnoDBのセカンダリインデックスには、末尾に主キーの列が付いていることがわかります。

SHOW EXTENDED INDEX FROM orders WHERE Key_name = 'idx_note';

| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | ...
| orders |          1 | idx_note |            1 | note        | A         |       18480 |       10 | ...
| orders |          1 | idx_note |            2 | id          | A         |        NULL |     NULL | ...

（この出力は、あとの節で行を削除したあとに取ったものです。）

CardinalityはANALYZE TABLEで変わる

データを入れた直後は0のままだった

今回、テーブルを作って5万行をINSERTした直後にSHOW INDEXを実行すると、Cardinalityはすべて0でした。

| orders |          0 | PRIMARY            |            1 | id          | A         |           0 | ...
| orders |          0 | uk_order_no        |            1 | order_no    | A         |           0 | ...
| orders |          1 | idx_member_ordered |            1 | member_id   | A         |           0 | ...

ANALYZE TABLE を実行すると、値が入りました。

ANALYZE TABLE orders;

+-------------+---------+----------+----------+
| Table       | Op      | Msg_type | Msg_text |
+-------------+---------+----------+----------+
| t270.orders | analyze | status   | OK       |
+-------------+---------+----------+----------+

| orders |          0 | PRIMARY            |            1 | id          | A         |       50165 | ...
| orders |          0 | uk_order_no        |            1 | order_no    | A         |       49521 | ...
| orders |          1 | idx_member_ordered |            1 | member_id   | A         |         494 | ...

「Cardinalityが0や以前の値のまま」と感じたら、まずANALYZE TABLEを実行してから読み直します。

実行するたびに少し変わる

同じデータのまま、ANALYZE TABLEを2回続けて実行した結果です。

データは1行も変えていませんが、値が動いています。推定値なので、計測のたびに少し揺れます。今回の環境の統計に関する設定は次のとおりでした。

SHOW VARIABLES WHERE Variable_name IN
 ('information_schema_stats_expiry','innodb_stats_persistent_sample_pages',
  'innodb_stats_auto_recalc','innodb_stats_persistent');

+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| information_schema_stats_expiry      | 86400 |
| innodb_stats_auto_recalc             | ON    |
| innodb_stats_persistent              | ON    |
| innodb_stats_persistent_sample_pages | 20    |
+--------------------------------------+-------+

大量に削除したあとの変化

member_id が251以上の行（全体の半分）を削除し、前後で比べます。

DELETE FROM orders WHERE member_id > 250;
SHOW INDEX FROM orders WHERE Key_name = 'idx_member';   -- Cardinality: 496
ANALYZE TABLE orders;
SHOW INDEX FROM orders WHERE Key_name = 'idx_member';   -- Cardinality: 245
SELECT COUNT(DISTINCT member_id) FROM orders;           -- 250

削除の直後は496のままで、ANALYZE TABLEのあとに245（実際は250）へ更新されました。大量のINSERT・DELETE・データ移行のあとは、ANALYZE TABLEを実行してから実行計画やCardinalityを確認するのが確実です。

InnoDBの統計テーブルを直接見る

InnoDBが保持している統計は mysql.innodb_index_stats で見られます。複合インデックスの「何列目までで何種類か」が1行ずつ入っています。

SELECT index_name, stat_name, stat_value, stat_description
FROM mysql.innodb_index_stats
WHERE database_name='t270' AND table_name='orders' AND index_name='idx_member_ordered';

+--------------------+--------------+------------+-----------------------------------+
| index_name         | stat_name    | stat_value | stat_description                  |
+--------------------+--------------+------------+-----------------------------------+
| idx_member_ordered | n_diff_pfx01 |        244 | member_id                         |
| idx_member_ordered | n_diff_pfx02 |      25636 | member_id,ordered_at              |
| idx_member_ordered | n_diff_pfx03 |      24912 | member_id,ordered_at,id           |
| idx_member_ordered | n_leaf_pages |         96 | Number of leaf pages in the index |
| idx_member_ordered | size         |        161 | Number of pages in the index      |
+--------------------+--------------+------------+-----------------------------------+

size（ページ数）にページサイズを掛けると、インデックスごとのおおよそのサイズがわかります。

SELECT index_name, ROUND(stat_value * @@innodb_page_size / 1024) AS size_kb
FROM mysql.innodb_index_stats
WHERE database_name='t270' AND table_name='orders' AND stat_name='size';

+--------------------+---------+
| index_name         | size_kb |
+--------------------+---------+
| PRIMARY            |    3600 |
| idx_member         |    1552 |
| idx_member_ordered |    2576 |
| idx_note           |    2576 |
| idx_orders_status  |    1552 |
| uk_order_no        |    1552 |
+--------------------+---------+

インデックスを追加する（ADD INDEX・CREATE INDEX）

書き方は ALTER TABLE ... ADD INDEX と CREATE INDEX の2通りあり、どちらも同じ結果になります。先ほど実行した4つの文は、いずれも次のように完了しました。

Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

式インデックスは、式全体をもう一組のかっこで囲む点（((UPPER(note)))）が書き方のポイントです。

ALGORITHM=INPLACE, LOCK=NONE を明示しておくと、その条件で実行できない場合はエラーで知らせてくれます。たとえば全文検索インデックスの追加で指定すると、次のように止まりました。

ALTER TABLE orders ADD FULLTEXT INDEX ft_note (note), ALGORITHM=INPLACE, LOCK=NONE;

ERROR 1846 (0A000): LOCK=NONE is not supported. Reason: Fulltext index creation requires a lock. Try LOCK=SHARED.

稼働中のテーブルへ追加するときに、意図しないロックを避けやすくなります。

追加でエラーになる例

同じ名前のインデックスを追加しようとした場合です。

ALTER TABLE orders ADD INDEX idx_status (status);

ERROR 1061 (42000): Duplicate key name 'idx_status'

重複する値がある列にユニークインデックスを追加しようとした場合です。

ALTER TABLE orders ADD UNIQUE INDEX uk_member (member_id);

ERROR 1062 (23000): Duplicate entry '2' for key 'orders.uk_member'

追加の前にSHOW INDEXで既存の名前と列を確かめておくと、どちらのエラーも事前に避けられます。

インデックスの名前を変える・削除する

RENAME INDEX

ALTER TABLE orders RENAME INDEX idx_status TO idx_orders_status;

Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

インデックスを作り直さずに名前だけが変わります。命名規則をそろえたいときに使えます。

DROP INDEX

こちらも2通りの書き方があります。

ALTER TABLE orders DROP INDEX idx_note_upper;
DROP INDEX idx_ordered_at ON orders;

存在しない名前を指定するとエラーになります。

ALTER TABLE orders DROP INDEX idx_nothing;

ERROR 1091 (42000): Can't DROP 'idx_nothing'; check that column/key exists

1つのALTER TABLEに、削除と追加をカンマで並べて書くこともできます。

ALTER TABLE orders DROP INDEX idx_member, ADD INDEX idx_status_ordered (status, ordered_at);

Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

INVISIBLEで削除前に「試し外し」する

インデックスを削除してから「やはり必要だった」とわかると、大きなテーブルでは作り直しに時間がかかります。MySQL 8.0の不可視インデックス（INVISIBLE）を使うと、インデックスを残したまま、オプティマイザから見えない状態にできます。 問題があれば VISIBLE に戻すだけです。

不可視にして実行計画を比べる

member_id を含む2つのインデックスを不可視にします。

ALTER TABLE orders ALTER INDEX idx_member_ordered INVISIBLE;
ALTER TABLE orders ALTER INDEX idx_member INVISIBLE;
SHOW INDEX FROM orders WHERE Key_name IN ('idx_member_ordered','idx_member');

| Key_name           | Seq_in_index | Column_name | Cardinality | Visible |
+--------------------+--------------+-------------+-------------+---------+
| idx_member_ordered |            1 | member_id   |         494 | NO      |
| idx_member_ordered |            2 | ordered_at  |       49286 | NO      |
| idx_member         |            1 | member_id   |         502 | NO      |

（要点の列に絞っています。）Visibleが NO になりました。この状態で実行計画を見ます。

EXPLAIN SELECT * FROM orders WHERE member_id = 10;

+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | orders | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 48888 |     0.19 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+

type が ALL（全行の読み取り）になり、key が NULL です。インデックスが使われなくなったことがわかります。VISIBLEに戻すと、元の実行計画に戻ります。

ALTER TABLE orders ALTER INDEX idx_member_ordered VISIBLE;
EXPLAIN SELECT * FROM orders WHERE member_id = 10;

| id | select_type | table  | type | possible_keys      | key                | key_len | ref   | rows | filtered | Extra |
|  1 | SIMPLE      | orders | ref  | idx_member_ordered | idx_member_ordered | 4       | const |  100 |   100.00 | NULL  |

（要点の列に絞っています。）

不可視インデックスの決まりごと

最後の use_invisible_indexes をonにすると、不可視のままでもそのセッションだけはインデックスが使われました。

SET SESSION optimizer_switch = 'use_invisible_indexes=on';
EXPLAIN SELECT member_id FROM orders FORCE INDEX (idx_member) WHERE member_id = 10;

| id | select_type | table  | type | possible_keys | key        | key_len | ref   | rows | Extra       |
|  1 | SIMPLE      | orders | ref  | idx_member    | idx_member | 4       | const |  102 | Using index |

（要点の列に絞っています。）

新しいインデックスを INVISIBLE で作っておき、自分のセッションだけで効果を確かめてから VISIBLE にする、という逆向きの使い方もできます。

flowchart LR
  A["削除候補を見つける"] --> B["ALTER INDEX ... INVISIBLE"]
  B --> C{"しばらく様子を見る"}
  C -->|遅くなるクエリが出た| D["ALTER INDEX ... VISIBLE で戻す"]
  C -->|影響なし| E["DROP INDEX で削除"]

sysスキーマで不要・重複インデックスを洗い出す

schema_redundant_indexes：重複しているインデックス

sys.schema_redundant_indexes は、ほかのインデックスと重複している、またはほかのインデックスがあれば足りるインデックスを表示するビューです。

SELECT * FROM sys.schema_redundant_indexes WHERE table_schema = 't270'\G

*************************** 1. row ***************************
              table_schema: t270
                table_name: orders
      redundant_index_name: idx_member
   redundant_index_columns: member_id
redundant_index_non_unique: 1
       dominant_index_name: idx_member_ordered
    dominant_index_columns: member_id,ordered_at
 dominant_index_non_unique: 1
            subpart_exists: 0
            sql_drop_index: ALTER TABLE `t270`.`orders` DROP INDEX `idx_member`

idx_member (member_id) は、idx_member_ordered (member_id, ordered_at) の先頭列と同じなので、重複として検出されました。sql_drop_index 列に、削除用のALTER TABLE文がそのまま入っています。 先ほど idx_member を削除して idx_status_ordered (status, ordered_at) を追加したあとにもう一度見ると、今度は idx_orders_status (status) が検出されました。

| redundant_index_name | redundant_index_columns | dominant_index_name | dominant_index_columns | sql_drop_index                                             |
| idx_orders_status    | status                  | idx_status_ordered  | status,ordered_at      | ALTER TABLE `t270`.`orders` DROP INDEX `idx_orders_status` |

schema_unused_indexes：使われていないインデックス

（この節の出力は、idx_member を削除する前に取ったものです。）

SELECT * FROM sys.schema_unused_indexes WHERE object_schema = 't270';

+---------------+-------------+--------------------+
| object_schema | object_name | index_name         |
+---------------+-------------+--------------------+
| t270          | orders      | idx_member_ordered |
| t270          | orders      | idx_note           |
| t270          | orders      | idx_orders_status  |
| t270          | orders      | idx_member         |
+---------------+-------------+--------------------+

このあと、idx_orders_status を使うクエリを1回実行します。

SELECT COUNT(*) FROM orders FORCE INDEX (idx_orders_status) WHERE status = 'paid';
SELECT * FROM sys.schema_unused_indexes WHERE object_schema = 't270';

+---------------+-------------+--------------------+
| object_schema | object_name | index_name         |
+---------------+-------------+--------------------+
| t270          | orders      | idx_member_ordered |
| t270          | orders      | idx_note           |
| t270          | orders      | idx_member         |
+---------------+-------------+--------------------+

一度使われた idx_orders_status が一覧から消えました。どのインデックスが何行読まれたかは sys.schema_index_statistics で見られます。

SELECT index_name, rows_selected, rows_inserted
FROM sys.schema_index_statistics
WHERE table_schema='t270' AND table_name='orders';

+--------------------+---------------+---------------+
| index_name         | rows_selected | rows_inserted |
+--------------------+---------------+---------------+
| idx_orders_status  |         12500 |             0 |
| uk_order_no        |             1 |             0 |
| PRIMARY            |             0 |             0 |
| idx_member_ordered |             0 |             0 |
| idx_note           |             0 |             0 |
| idx_member         |             0 |             0 |
+--------------------+---------------+---------------+

リファレンスマニュアルには、schema_unused_indexesは「サーバーが十分に長く稼働し、処理内容が普段の傾向を表すようになってから」役に立つ、とあります。起動直後や、月に一度の集計だけで使うインデックスがある場合は、一覧に出ていても必要なインデックスかもしれません。一覧は「削除の候補」として扱い、INVISIBLEでの試し外しを挟んでから削除する流れが安心です。

見直しの手順

つまずきやすい点と確認ポイント

よくある質問

Q. SHOW INDEXとSHOW CREATE TABLE、どちらで確認するのがよいですか？ A. 定義だけを手早く見るなら SHOW CREATE TABLE、Cardinality・Visible・列順など統計や状態まで見るなら SHOW INDEX です。データベース全体を横断して調べたいときは information_schema.STATISTICS をSELECTします。

Q. Cardinalityが低いインデックスは削除したほうがよいですか？ A. 低いだけでは決められません。今回の status（Cardinality 3）のような列は単独では絞り込みにくいものの、(status, ordered_at) のように複合インデックスの先頭列としては役に立つことがあります。sys.schema_index_statistics で読まれているかを確かめ、INVISIBLEで試してから判断します。

Q. インデックスを追加するとテーブルはロックされますか？ A. 今回は、通常のインデックスで ALGORITHM=INPLACE, LOCK=NONE を明示した追加が完了しました。全文検索インデックスでは同じ指定が ERROR 1846 で止まり、LOCK=SHARED を案内されました。この2つを書いておくと、ロックが必要な操作かどうかを実行前に見分けられます。

Q. 不可視インデックスは、更新の負荷も減りますか？ A. 減りません。リファレンスマニュアルに、不可視でも行の変更に合わせて更新され続けるとあります。更新の負荷を減らすには、試し外しで影響がないことを確かめたうえでDROP INDEXします。

まとめ

• インデックスの確認は SHOW INDEX FROM テーブル名;。横に長いときは \G、絞り込みは WHERE Key_name = ...
• Non_uniqueは0がユニーク。同じKey_nameが複数行あれば複合インデックスで、Seq_in_indexが列順
• Cardinalityは推定値。実行するたびに少し変わり、大量の変更後はANALYZE TABLEで更新する
• VisibleとExpressionは8.0の出力で末尾にある2列。不可視インデックスと式インデックスの状態がわかる
• 追加はADD INDEX／CREATE INDEX、削除はDROP INDEX、名前変更はRENAME INDEX
• 削除の前にINVISIBLEで試し外し。問題があればVISIBLEに戻すだけ
• sys.schema_redundant_indexesとschema_unused_indexesで候補を洗い出す。削除用のSQLも列に入っている

参考・出典

• MySQL 8.0 Reference Manual「SHOW INDEX Statement」 https://dev.mysql.com/doc/refman/8.0/en/show-index.html
• MySQL 8.0 Reference Manual「Invisible Indexes」 https://dev.mysql.com/doc/refman/8.0/en/invisible-indexes.html
• MySQL 8.0 Reference Manual「The schema_unused_indexes and x$schema_unused_indexes Views」 https://dev.mysql.com/doc/refman/8.0/en/sys-schema-unused-indexes.html
• MySQL 8.0 Reference Manual「The schema_redundant_indexes and x$schema_flattened_keys Views」 https://dev.mysql.com/doc/refman/8.0/en/sys-schema-redundant-indexes.html

株式会社RJC ― SI事業・SES事業・AI駆動開発。RJCは一緒に成長を楽しめる会社です。