MySQL SYS Schema 를 이용한 미사용/중복 Index 확인

Last Updated on 2월 15, 2021 by 태랑(정현호)


Index라는 것이 잘 사용 되면 RDB에서 가장 쉽고 편하게 사용할 수 있는 조회 성능의 핵심 기능 입니다.

그런 좋은 Index 라도 Index에 의해서 차지하는 공간과 DML 에 따른 Index 갱신,Index Split 등을 고려 하여 어느 일정한 개수로 한정적으로 만들수 밖에는 없습니다.


그런 의미로 Mysql 에서 제공되는 SYS Schema를 통해서 사용되지 않는 Index와 중복된 Index 를 조회하는 내용을 확인 해보려고 합니다.

먼저 SYS Schema 는 Mysql 5.7.7 버전부터 기본포함이며  5.6 버전 부터 별도로 설치가 가능 합니다(View의 차이는 있음)


향후 관련해서는 다시 포스팅 하도록 하겠습니다.





먼저 테스트 테이블과 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`) ;



-- 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 의 목록을 확인 할 수 있습니다
     생성 직후 조회한 내역이 없기 때문에 모두 확인이 되고 있습니다.




-- 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 |
+------+------+
1 row in set (0.00 sec)



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 인덱스가 바로 제외 되는 것을 확인 할 수 있었습니다.


이번 포스팅은 schema_unused_indexes 와 schema_redundant_indexes 에 대해서 확인 해보았습니다

SYS Schema 에는 그 외 볼만한 좋은 View가 더많이 있습니다.
다음에 정리하여 포스팅 하도록 하겠습니다.


관련된 다른 글

 

 

 

 

 

 

답글 남기기