MySQL 8.0 では invisible index が実装されました。
これは MySQLのオプティマイザーからインデックスを見えなくする（使われなくする）ものです。
MySQL :: MySQL 8.0 Reference Manual :: 8.3.12 Invisible Indexes
invisible index の挙動を確認する
テストデータの作成
以下のようなテーブルを作成しました。
|
1 2 3 4 5 |
mysql> CREATE TABLE `t3` ( `c1` int(11) NOT NULL AUTO_INCREMENT, `c2` varchar(255) DEFAULT NULL, PRIMARY KEY (`c1`) ); |
MySQL sandbox で建てた MySQL 8.0 に以下のようにして100万レコード投入します。
|
1 |
$ moldova -t "INSERT INTO t3 (c1, c2) VALUES (null, '{ascii:length:255}');" -n 1000000 | ./sandboxes/msb_8_0_11/use test |
実行されるSQLは以下のようになります。c2にランダムで255文字が入ります。
|
1 |
INSERT INTO t3 (c1, c2) VALUES (null, '41ugwonexvhmig93dygiwh5w2oerdc0hhjlb3b9lutxfbki1pek5b4wabg2g39r5ig3pn1ixtj2u8igiyof61ew0os01q2ij979q5j2pv5bv3tvoulux5l8kxkujwwbdbsc3o39g3dssohtjy8j13ray57f3fkaama5dxfhlj9lri65setqw7xh19954oa4qmd83864lljekvgrggbu9ho2eh6rf4mq5un95fdretqevq5dpfc8qqdnnnevpgmi'); |
100万レコードの投入が完了しました。
|
1 2 3 4 5 6 7 |
mysql > select count(*) from t3; +----------+ | count(*) | +----------+ | 1000000 | +----------+ 1 row in set (0.23 sec) |
パフォーマンス比較
インデックスを作成する
|
1 2 3 |
mysql> create index idx_c2 on t3 (c2); Query OK, 0 rows affected (12.06 sec) Records: 0 Duplicates: 0 Warnings: 0 |
作成には12秒かかりました。
インデックスを削除する
|
1 2 3 |
mysql> drop index idx_c2 on t3; Query OK, 0 rows affected (0.11 sec) Records: 0 Duplicates: 0 Warnings: 0 |
インデックスの削除は0.11秒と比較的高速に完了しました。
インデックスを INVISIBLE に変更する
|
1 2 3 |
mysql > alter table t3 alter index idx_c2 invisible; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 |
こちらは0.01秒で完了しました。
インデックスを VISIBLE に変更する
|
1 2 3 |
mysql > alter table t3 alter index idx_c2 visible; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 |
実際のINDEX作成とは異なり、0.03秒で完了しました。
やはり実際にインデックスを作ったり削除するよりも高速でした。
インデックスの表示、非表示の管理
CREATE TABLE を見ると INVISIBLE になっているインデックスには /*!80000 INVISIBLE */ が付与されています。
|
1 2 3 4 5 6 |
CREATE TABLE `t3` ( `c1` int(11) NOT NULL AUTO_INCREMENT, `c2` varchar(255) DEFAULT NULL, PRIMARY KEY (`c1`), KEY `idx_c2` (`c2`) /*!80000 INVISIBLE */ ) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
SHOW INDEX でも Visible の項目が追加されました。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
mysql > show index from t3 where key_name = 'idx_c2'\G *************************** 1. row *************************** Table: t3 Non_unique: 1 Key_name: idx_c2 Seq_in_index: 1 Column_name: c2 Collation: A Cardinality: 982067 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: Visible: NO 1 row in set (0.01 sec) |
information_schema.statistics にも IS_VISIBLE の項目が追加されています。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
mysql> select * from information_schema.statistics where table_schema = 'test' and table_name = 't3' and index_name = 'idx_c2'\G *************************** 1. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: test TABLE_NAME: t3 NON_UNIQUE: 1 INDEX_SCHEMA: test INDEX_NAME: idx_c2 SEQ_IN_INDEX: 1 COLUMN_NAME: c2 COLLATION: A CARDINALITY: 982067 SUB_PART: NULL PACKED: NULL NULLABLE: YES INDEX_TYPE: BTREE COMMENT: INDEX_COMMENT: IS_VISIBLE: NO 1 row in set (0.00 sec) |
インデックスが使われるかどうかの確認
インデックスが見えている状態だとEXPLAINの結果を見てもインデックスが使われることがわかります。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
mysql> explain select * from t3 where c2 like 'test%'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t3 partitions: NULL type: range possible_keys: idx_c2 key: idx_c2 key_len: 1023 ref: NULL rows: 1 filtered: 100.00 Extra: Using where; Using index 1 row in set, 1 warning (0.00 sec) |
同様に、インデックスが見えていない状態だと使われないことがわかります。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
mysql> alter table t3 alter index idx_c2 invisible; Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> explain select * from t3 where c2 like 'test%'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t3 partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 982067 filtered: 11.11 Extra: Using where 1 row in set, 1 warning (0.00 sec) |
注意が必要なのは、INVISIBLE の場合、USE INDEX や FORCE INDEX を使うとインデックスが見えてないため、エラーになります。
|
1 2 |
mysql > explain select * from t3 FORCE INDEX (idx_c2) where c2 like 'test%'\G ERROR 1176 (42000): Key 'idx_c2' doesn't exist in table 't3' |
|
1 2 |
mysql > explain select * from t3 USE INDEX (idx_c2) where c2 like 'test%'\G ERROR 1176 (42000): Key 'idx_c2' doesn't exist in table 't3' |
カジュアルにインデックスを INVISIBLE にすると、場合によってはエラーになってしまうかもしれません。
使われていないインデックスを探す
MySQLでは起動してから使われていないインデックスは sys.schema_unused_indexes テーブルに記録されています。
|
1 2 3 4 5 6 7 |
mysql > select * from sys.schema_unused_indexes where object_schema not in ('performance_schema'); +---------------+-------------+------------+ | object_schema | object_name | index_name | +---------------+-------------+------------+ | test | t2 | idx_c3 | +---------------+-------------+------------+ 1 row in set, 1 warning (0.00 sec) |
こういった情報を元に使われていないインデックスかどうかの判断をすることになるかと思います。
INVISIBLE 時のインデックスの更新
カーディナリティは 982067 とほぼ100万に近い値です。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
mysql > show index from t3 where key_name = 'idx_c2'\G *************************** 1. row *************************** Table: t3 Non_unique: 1 Key_name: idx_c2 Seq_in_index: 1 Column_name: c2 Collation: A Cardinality: 982067 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: Visible: N 1 row in set (0.00 sec) |
インデックスを INVISIBLE に変更してさらに100万行を追加しました。
|
1 2 |
mysql > alter table t3 alter index idx_c2 invisible; Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 |
analyze table を実行してインデックの統計情報を更新します。（インデックスデータ自体を更新しているわけではありません）
|
1 2 3 4 5 6 7 |
mysql > analyze table t3; +---------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +---------+---------+----------+----------+ | test.t3 | analyze | status | OK | +---------+---------+----------+----------+ 1 row in set (0.03 sec) |
カーディナリティが 1913219 と200万に近い値に変化しました。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
mysql > show index from t3 where key_name = 'idx_c2'\G *************************** 1. row *************************** Table: t3 Non_unique: 1 Key_name: idx_c2 Seq_in_index: 1 Column_name: c2 Collation: A Cardinality: 1913219 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: Visible: NO 1 row in set (0.00 sec) |
このことから、インデックスを INVISIBLE に変更しても、実際のインデックスデータは更新され続けていることがわかります。
そのため、例えば長期間に渡って INVISIBLE にしたインデックスであっても VISIBLE に変更した直後から問題なく使えることがわかります。
まとめ
この機能を使うことで、インデックスを削除した場合、どれくらいSELECTのパフォーマンスに影響が出るか簡単に調べることができます。（インデックスの削除や作成を伴わないので比較的簡単に試せる）
ただし、以下の点で注意が必要です。
- USE INDEX、FORCE INDEX でインデックスを使っていると、そのインデックスを INVISIBLE にするとエラーになってしまう。（事前に sys.schema_unused_indexes テーブルの情報や、アプリなどで使われていないかの確認が必要です）
- INVISIBLE にしても、インデックスそのものは存在するため、更新処理にかかる負荷は変化しません。
