Mysql8.0 Invisible Index 사용하기

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: 0 ~~~ ### Index 없이 조회 ~~~ 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 가능