Last Updated on 4월 16, 2023 by Jade(정현호)
안녕하세요~
이번 포스팅은 미사용 그리고 중복된 Index에 대한 정보를 조회하는 내용을 확인 해보려고 합니다.
Intro
Index 라는 오브젝트는 잘 사용 되면 RDBMS 에서 가장 쉽고 편하게 사용할 수 있는 조회 성능의 핵심 기능 입니다.
그런 좋은 Index 라도 Index 에 의해서 차지하는 공간과 DML 에 따른 Index 갱신, Index Split 등을 고려 하여 어느 일정한 개수로 한정적으로 만들수 밖에는 없습니다.
그런 의미로 MySQL 에서 제공되는 여러가지 정보를 통해서 통해서 사용 되지 않는 Index 와 중복된 Index 를 조회하는 내용을 확인 해보려고 합니다.
포스팅에서 사용되는 SYS Schema 는 MySQL 5.7.7 버전부터 기본포함이며 5.6 버전 부터 별도로 설치가 가능 합니다
(View의 차이는 있음)
schema_unused_indexes
기능 확인을 위해서 먼저 테스트 테이블과 Index 를 생성 해보도록 하겠습니다
테이블 생성
SQL> CREATE TABLE IF NOT EXISTS `emp` ( `empno` int(11) NOT NULL, `ename` varchar(10) DEFAULT NULL, `job` varchar(9) DEFAULT NULL, `mgr` int(11) DEFAULT NULL, `hiredate` datetime DEFAULT NULL, `sal` double DEFAULT NULL, `comm` double DEFAULT NULL, `deptno` int(11) DEFAULT NULL, PRIMARY KEY (`EMPNO`), KEY `PK_EMP` (`DEPTNO`) ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci; SQL> insert into `emp` values(1,'a','a1','101',sysdate(),10000,10000,100); SQL> commit;
Index 생성
SQL> ALTER TABLE `emp` ADD INDEX emp_idx1 (`job`,`mgr`) ; SQL> ALTER TABLE `emp` ADD INDEX emp_idx2 (`job`,`mgr`) ;
sys.schema_unused_indexes 조회
mysql> select * from sys.schema_unused_indexes; +---------------+-------------+------------+ | object_schema | object_name | index_name | +---------------+-------------+------------+ | test | emp | emp_idx2 | | test | emp | emp_idx1 | | test | emp | PK_EMP | +---------------+-------------+------------+
=> 생성 직후 조회결과로 미사용 중인 Index 의 목록을 확인 할 수 있습니다
생성 직후 조회한 내역이 없기 때문에 모두 확인이 되고 있습니다.
Note
DB 인스턴스 별로 인덱스 사용 패턴이 다를 수 있습니다.
그렇기 때문에 사용중인 모든 인스턴스, Primary(Master) 와 Replica(Slave) 모두에서 조회해서 미사용 여부를 확인 해야 합니다.
sys.schema_redundant_indexes 조회 결과
mysql> select * from sys.schema_redundant_indexes\G *************************** 1. row *************************** table_schema: test table_name: emp redundant_index_name: emp_idx2 redundant_index_columns: job,mgr redundant_index_non_unique: 1 dominant_index_name: emp_idx1 dominant_index_columns: job,mgr dominant_index_non_unique: 1 subpart_exists: 0 sql_drop_index: ALTER TABLE `test`.`emp` DROP INDEX `emp_idx2` 1 row in set (0.00 sec)
=> 위에서 테스트를 위해 job, mgr 컬럼을 가지는 인덱스를 2개 생성 하였습니다.
중복된 인덱스 조회 결과에 위와 같이 컬럼과 인덱스명 이 확인 됩니다.
그리고 중복되는 인덱스 중 1개를 삭제하는 명령어(sql_drop_index) 까지 출력 됩니다.
Index 를 사용하여 조회
mysql> explain -> select job,mgr from `test`.`emp` -> where job='a1'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: emp partitions: NULL type: ref possible_keys: emp_idx1,emp_idx2 key: emp_idx1 key_len: 30 ref: const rows: 1 filtered: 100.00 Extra: Using index 1 row in set, 1 warning (0.00 sec)
=> explain 에서는 Index를 사용하는 것으로 플랜이 확인 됩니다.
쿼리 조회 후 sys.schema_unused_indexes 를 다시 조회
mysql> select job,mgr from `test`.`emp` where job='a1'; +------+------+ | job | mgr | +------+------+ | a1 | 101 | +------+------+ mysql> select * from sys.schema_unused_indexes; +---------------+-------------+------------+ | object_schema | object_name | index_name | +---------------+-------------+------------+ | test | emp | emp_idx2 | | test | emp | PK_EMP | +---------------+-------------+------------+
=> 위에서 emp_idx1 를 사용한 조회가 수행되었음으로 sys.schema_unused_indexes 뷰에서는 idx1 인덱스가 바로 제외 되는 것을 확인 할 수 있었습니다.
table_io_waits_summary_by_index_usage
sys.schema_unused_indexes 뷰를 조금 더 자세하게 살펴 보도록 하겠습니다.
해당 뷰에 대해서 show create 문을 통해 생성 구문을 확인 해보면 몇 개의 딕셔너리가 조인된 뷰 형태라는 것을 알수 있게 됩니다.
mysql> show create table sys.schema_unused_indexes;
그 중에서 미 사용 인덱스의 판단 기준에 대한 정보는 performance_schema 의 table_io_waits_summary_by_index_usage 를 통해서 정보를 가져 온다는 것을 알 수 있게 됩니다.
그렇다면 미사용 인덱스 라는 기준 이외에 사용 되는 정도나 비중 또는 횟수를 확인 해보고자 한다면 해당 테이블을 직접 조회 해보면 알수도 있게 됩니다(table_io_waits_summary_by_index_usage)
table_io_waits_summary_by_index_usage 테이블은 wait/io/table/sql/handler 에 의해 생성된 모든 테이블 인덱스 I/O 대기 이벤트를 집계 정보를 담고 있습니다.
count_star 가 0 인 인덱스가 미사용 인덱스 이며 0 보다 큰 인덱스는 한번 이라도 사용된 인덱스가 되게 됩니다.
아래와 같이 performance_schema 나 mysql 그리고 PK 를 제외한 인덱스에 대해서 사용 횟수 정보를 확인 할 수 있습니다.
mysql> SELECT object_schema, object_name,index_name, count_star
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE 1=1
AND index_name <> 'PRIMARY'
AND object_schema NOT IN ('performance_schema','mysql')
ORDER BY count_star, count_star, object_schema, object_name,index_name asc;
추가로 미사용 인덱스를 조회 하는데 있어서 sys.schema_unused_indexes 와 performance_schema.table_io_waits_summary_by_index_usage 2개의 차이점이 있습니다.
확인 해보기 위해서 아래와 같은 간단한 테스트 테이블을 생성해 보도록 하겠습니다.
create table t1 (id int not null primary key, col1 varchar(50), col2 varchar(50), unique key ux_col1(col1), key ix_col2(col2) );
• sys.schema_unused_indexes 조회
mysql> select * from sys.schema_unused_indexes where object_name='t1'; +---------------+-------------+------------+ | object_schema | object_name | index_name | +---------------+-------------+------------+ | test | t1 | ix_col2 | +---------------+-------------+------------+
• performance_schema.table_io_waits_summary_by_index_usage 조회
mysql> select object_schema, object_name,index_name, count_star from performance_schema.table_io_waits_summary_by_index_usage where 1=1 and index_name <> 'primary' and count_star=0 and object_name='t1'; +---------------+-------------+------------+------------+ | object_schema | object_name | index_name | count_star | +---------------+-------------+------------+------------+ | test | t1 | ux_col1 | 0 | | test | t1 | ix_col2 | 0 | +---------------+-------------+------------+------------+
2개의 조회 결과의 차이는 schema_unused_indexes 에서는 Unique Index 에 대한 미사용 정보가 확인 되지 않는 것이며, 반대로 table_io_waits_summary_by_index_usage 에서는 Unique Index 가 확인 할 수 있습니다.
이유는 schema_unused_indexes(view) 의 실제 쿼리에서 information_schema.s.NON_UNIQUE = 1 조건에 의해서 Unique Index 는 조회 대상에서 제외 하였기 때문에 조회 결과의 차이가 발생하게 됩니다.
그래서 이런 부분 까지 고려해서 조회 대상의 딕셔너리를 선택하시면 됩니다.
Note
DB 인스턴스 별로 인덱스 사용 패턴이 다를 수 있습니다.
그렇기 때문에 사용중인 모든 인스턴스, Primary(Master) 와 Replica(Slave) 모두에서 조회해서 미사용 여부를 확인 해야 합니다.
이번 포스팅은 미사용 인덱스와 인덱스 사용 횟수 관련된 뷰 와 테이블에 대해서 간략 하게 확인 해보았습니다.
SYS Schema 나 performance_schema 에는 추가적으로 볼만한 좋은 뷰나 테이블이 많이 있습니다.
다음에 추가적으로 정리하여 포스팅 해보도록 하겠습니다.
관련된 다른 글
Principal DBA(MySQL, AWS Aurora, Oracle)
핀테크 서비스인 핀다에서 데이터베이스를 운영하고 있어요(at finda.co.kr)
Previous - 당근마켓, 위메프, Oracle Korea ACS / Fedora Kor UserGroup 운영중
Database 외에도 NoSQL , Linux , Python, Cloud, Http/PHP CGI 등에도 관심이 있습니다
purityboy83@gmail.com / admin@hoing.io
