Mysql 8.0 버전부터는 그동안 Oracle에서만 사용가능했던 Invisible Index 사용이 가능합니다.
Invisilbe index는 간단하게 말하면 index를 on / off 시키는 기능이라고 생각하면 됩니다.
장점으로는 index 를 추가했을 때 갑자기 다른 쿼리에서 영향을 받는 경우, drop 시키지 않고 invisible 시킬 수 있는 등 인덱스 성능 테스트가 매우 용이합니다.
테스트 데이터 생성
mysql> create table index_test
-> ( a int auto_increment,
-> b int,
-> c varchar(100),
-> primary key (a));
Query OK, 0 rows affected (0.11 sec)
mysql> insert into index_test select null,round(rand()*100),uuid()
-> from information_schema.columns a1, information_schema.columns b1
-> limit 1000000;
Query OK, 1000000 rows affected (7 min 26.96 sec)
Records: 1000000 Duplicates: 0 Warnings: 0Index 없이 조회
mysql> explain select * from index_test order by c limit 10;
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| 1 | SIMPLE | index_test | NULL | ALL | NULL | NULL | NULL | NULL | 927780 | 100.00 | Using filesort |
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+----------------+
1 row in set, 1 warning (0.00 sec)Invisible Index 생성 후 데이터 조회
mysql> alter table index_test add index ix_c (c) invisible;
Query OK, 0 rows affected (27.78 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain select * from index_test order by c limit 10;
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| 1 | SIMPLE | index_test | NULL | ALL | NULL | NULL | NULL | NULL | 927791 | 100.00 | Using filesort |
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+----------------+
1 row in set, 1 warning (0.00 sec)
=> invisible index 로 생성했기 때문에 인덱스를 생성 못하고 Using filesort 발생함
Invisible index 생성 확인
-
show create table
mysql> show create table index_test; +------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | index_test | CREATE TABLE `index_test` ( `a` int(11) NOT NULL AUTO_INCREMENT, `b` int(11) DEFAULT NULL, `c` varchar(100) DEFAULT NULL, PRIMARY KEY (`a`), **KEY `ix_c` (`c`) /*!80000 INVISIBLE */** ) ENGINE=InnoDB AUTO_INCREMENT=1000014 DEFAULT CHARSET=utf8 | +------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)=> KEY 'ix_c' ('c') /!80000 INVISIBLE /
- information_schema 메타데이터 조회
mysql> SELECT INDEX_NAME, IS_VISIBLE FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = 'testdb' AND TABLE_NAME = 'index_test'; +------------+------------+ | INDEX_NAME | IS_VISIBLE | +------------+------------+ | ix_c | NO | | PRIMARY | YES | +------------+------------+ 2 rows in set (0.01 sec)
invisible index Visible로 전환
mysql> alter table index_test alter index ix_c visible;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SELECT INDEX_NAME, IS_VISIBLE FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = 'testdb' AND TABLE_NAME = 'index_test';
+------------+------------+
| INDEX_NAME | IS_VISIBLE |
+------------+------------+
| ix_c | YES |
| PRIMARY | YES |
+------------+------------+
2 rows in set (0.00 sec)
mysql> explain select * from index_test order by c desc limit 10;
+----+-------------+------------+------------+-------+---------------+------+---------+------+------+----------+---------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+---------------+------+---------+------+------+----------+---------------------+
| 1 | SIMPLE | index_test | NULL | index | NULL | ix_c | 303 | NULL | 10 | 100.00 | Backward index scan |
+----+-------------+------------+------------+-------+---------------+------+---------+------+------+----------+---------------------+
1 row in set, 1 warning (0.00 sec)
=> index를 통해 Using filesort 미발생 확인
index invisible 재설정
mysql> alter table index_test alter index ix_c invisible;
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain select * from index_test order by c desc limit 10;
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| 1 | SIMPLE | index_test | NULL | ALL | NULL | NULL | NULL | NULL | 927791 | 100.00 | Using filesort |
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+----------------+
1 row in set, 1 warning (0.00 sec)=> index visible 후 다른 쿼리 악영향 확인시, index를 drop 시키지않고도 바로 ON/OFF 가능