tom__bo’s Blog

MySQL!! MySQL!! @tom__bo

MySQL 8.0 Invisible indexes

MySQL8.0の機能を調べてまとめている。
invisible indexesについて読んだまとめ。

dev.mysql.com

MySQL 8.0からはinvisible indexesがサポートされる。

invisible indexはoptimizerに使われないindexのこと。 optimizerから見て、visible(利用可能)かinvisible(利用不可)かを選択できるようになった。 invisible indexを利用することで、indexを削除しても影響がないかを確認することが容易になる。
これは結構ありがたい機能で、行数が多かったり、更新頻度が高いテーブルではindexの削除・作成は非常にコストがかかるし、下手にindexを外すとクエリをつまらせてサービスダウンになることもある。

デフォルトではindexはvsibleな状態で、これを変更するにはCREATE TABLE, CREATE INDEX, ALTER TABLE中でVISIBLEINVISIBLEキーワードを指定する。 この機能はprimary key以外に適用される。

CREATE TABLEでのサンプル

mysql> create table t2 (
    -> id int not null auto_increment,
    -> c1 int,
    -> c2 int,
    -> index c1_idx(c1) invisible,
    -> primary key(id)
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> create index c2_idx on t2(c2) invisible;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c1` int(11) DEFAULT NULL,
  `c2` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c1_idx` (`c1`) /*!80000 INVISIBLE */,
  KEY `c2_idx` (`c2`) /*!80000 INVISIBLE */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

ALTER TABLEによるサンプル

mysql> alter table t2 alter index c2_idx visible;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c1` int(11) DEFAULT NULL,
  `c2` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c1_idx` (`c1`) /*!80000 INVISIBLE */,
  KEY `c2_idx` (`c2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> alter table t2 alter index c2_idx invisible;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c1` int(11) DEFAULT NULL,
  `c2` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c1_idx` (`c1`) /*!80000 INVISIBLE */,
  KEY `c2_idx` (`c2`) /*!80000 INVISIBLE */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

前のサンプルのようにshow create table ...で見る以外に、Indexがvisibleかinvisibleかは以下の方法で確認できる。

  • INFORMATION_SCHEMA.STATISTICS table
mysql> select index_name, is_visible from information_schema.statistics where table_name = 't2';
+------------+------------+
| INDEX_NAME | IS_VISIBLE |
+------------+------------+
| c1_idx     | NO         |
| c2_idx     | NO         |
| PRIMARY    | YES        |
+------------+------------+
3 rows in set (0.00 sec)
  • SHOW INDEX
mysql> show index from t2;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| t2    |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| t2    |          1 | c1_idx   |            1 | c1          | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | NO      | NULL       |
| t2    |          1 | c2_idx   |            1 | c2          | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | NO      | NULL       |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set (0.00 sec)

invisible indexの影響の確認

invisibleにしたindexがどのような影響を持っていたかを確認するには以下の方法がある

  • クエリ中でindex hintをつけていた場合、エラーになる
  • Performance Schemaのデータからindexが使えなくなったことによる負荷上昇を発見する
  • EXPLAINすることで使われるindexが変わったことを確認する
  • indexが使えなくなたことでslow queryに出力がでる

強制的にinvisible indexを利用する

indexをinvisibleにしていても、optimizer_switchでuse_invisible_indexesフラグをONにすることで、invisible indexを使ってアクセスすることができる。 当然デフォルトでこのフラグはOFF.
(optimizer_switchはGLOBAL, SESSION単位で変えられる)

mysql> SELECT @@optimizer_switch\G
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on
1 row in set (0.00 sec)

mysql> set session optimizer_switch='use_invisible_indexes=on';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@optimizer_switch\G
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=on,skip_scan=on
1 row in set (0.00 sec)

前出のt2にレコードを入れて試す。

mysql> show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c1` int(11) DEFAULT NULL,
  `c2` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c1_idx` (`c1`) /*!80000 INVISIBLE */,
  KEY `c2_idx` (`c2`) /*!80000 INVISIBLE */
) ENGINE=InnoDB AUTO_INCREMENT=60 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)


mysql> explain select * from t2 where c1 = 1;
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key    | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t2    | NULL       | ref  | c1_idx        | c1_idx | 5       | const |   16 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

OFFに戻せば当然使えなくなる。

mysql> set session optimizer_switch='use_invisible_indexes=off';
Query OK, 0 rows affected (0.00 sec)

mysql> explain select * from t2 where c1 = 1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   48 |     2.08 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

PRIMARY KEYに相当するindexはinvisibleにできない

PRIMARY KEYが明示的に作成されない場合、UNIQ制約のあるkeyをPRIMARY KEYとして利用している。そのため、そのような状況でPKの代替として使われているINDEXをinvisibleにすることはできない。
そういった状況のテーブルに後からPKを追加した場合は、そのindexをinvisibleに設定できるようになる。

テーブルサンプル

mysql> create table t3 (
    -> id int not null,
    -> c1 int not null,
    -> unique key(c1)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> show create table t3\G
*************************** 1. row ***************************
       Table: t3
Create Table: CREATE TABLE `t3` (
  `id` int(11) NOT NULL,
  `c1` int(11) NOT NULL,
  UNIQUE KEY `c1` (`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.01 sec)

c1はPRIMARY KEYの代用として使われているので、invisibleにできない

mysql> alter table t3 alter index c1 invisible;
ERROR 3522 (HY000): A primary key index cannot be invisible

PRIMARY KEYをidに作成するとc1をinvisibleにすることができるようになる

mysql> alter table t3 add primary key (id);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table t3 alter index c1 invisible;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t3\G
*************************** 1. row ***************************
       Table: t3
Create Table: CREATE TABLE `t3` (
  `id` int(11) NOT NULL,
  `c1` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `c1` (`c1`) /*!80000 INVISIBLE */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

references