Zenn
🏎️

[MySQL]Invisible Indexes が手軽 & 超高速で便利だった（index の不可視）

2024/05/18に公開
MySQL
index
tech

TL;DR

  • MySQL8.0 から利用できる Invisible Indexes で index の可視・不可視を操作できる（既存の index がオプティマイザに使われる状態・使われない状態を簡単かつ高速に作り出せる）
  • 構文としては下記のような書き方
    -- 不可視状態にする
ALTER TABLE <table名> ALTER INDEX <index名> INVISIBLE;

    -- 可視状態にする
ALTER TABLE <table名> ALTER INDEX <index名> VISIBLE;
  • index を削除・追加するよりもはるかに高速

概要

Invisible Indexes は MySQL8.0 から利用できる機能。ざっくり、オプティマイザから任意の index を使えなくしたり、使えるようにしたりといった操作を index の削除・追加を伴わずに行えるものだと認識。
使われない状態にした上で明示的に対象 index を指定するようなクエリを実行した場合はエラーとなるので注意が必要。

8.3.12 不可視のインデックス

検証

検証環境

  • MacBook Pro（チップ：Apple M1 Pro, メモリ：16 GB, OSバージョン：13.6.4）
  • Docker（バージョン：25.0.2）
  • MySQL 公式イメージ（mysql:8.0）

検証内容

適当なテーブルを作成。

CREATE TABLE books (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL
);

[MySQL]CTE を使って簡単なダミーデータをサクッと手軽に大量投入 を参考にダミーレコードを1億件追加。

mysql> SET SESSION cte_max_recursion_depth = 100000000;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO books(name)
    -> WITH RECURSIVE cte (n) AS
    -> (
    ->   SELECT 1
    ->   UNION ALL
    ->   SELECT n + 1 FROM cte WHERE n < 100000000
    -> )
    -> SELECT CONCAT('book_', n) FROM cte;
Query OK, 100000000 rows affected (5 min 4.07 sec)
Records: 100000000  Duplicates: 0  Warnings: 0

index を追加する。
処理時間：3 min 4.96 sec

mysql> ALTER TABLE books ADD INDEX idx_name(name);
Query OK, 0 rows affected (3 min 4.96 sec)
Records: 0  Duplicates: 0  Warnings: 0

index を不可視にする。
処理時間：0.01 sec

mysql> ALTER TABLE books ALTER INDEX idx_name INVISIBLE;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

不可視になっていることの確認。

mysql> EXPLAIN SELECT  * FROM books WHERE name = 'book_9000';
+----+-------------+-------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows     | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+----------+----------+-------------+
|  1 | SIMPLE      | books | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 99744726 |     0.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+----------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

index を可視にする。
処理時間：0.01 sec

mysql> ALTER TABLE books ALTER INDEX idx_name VISIBLE;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

可視になっていることの確認。

mysql> EXPLAIN SELECT  * FROM books WHERE name = 'book_9000';
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | books | NULL       | ref  | idx_name      | idx_name | 1022    | const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

index 削除する。
処理時間：0.04 sec

mysql> DROP INDEX idx_name ON books;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

検証結果のサマリ

処理内容 処理時間
INVISIBLE 0.01 sec
ALTER TABLE ... ADD INDEX 3 min 4.96 sec
VISIBLE 0.01 sec
DROP INDEX 0.04 sec

インデックス削除と不可視に関しての差はないが、再作成する場合と可視にする場合とでは圧倒的なパフォーマンスの差がみられた。

おわりに

15.12.1 オンライン DDL 操作 を見る限り、index 追加・削除はオンライン DDL の実行が可能なので、「Invisible Indexes 使わんでも index 削除して問題あったら再追加すればええやん」くらいの考えだったが、パフォーマンスに圧倒的な差があったので積極利用したいお気持ちになった（あと、FULLTEXT INDEX や SPATIAL INDEX の追加といった同時 DML が実行不可な種類の INDEX 操作においても使えるのは便利そうな予感がした）。

Discussion