MySQL 미사용 인덱스 조회

방법1) mysql 5.7 버전

 

SELECT * FROM sys.schema_unused_indexes

SELECT * FROM sys.schema_unused_indexes

-- mysql 5.6에도 sys 스키마를 설치해서 활용할 수도 있음

https://www.percona.com/blog/2016/09/09/basic-housekeeping-for-mysql-indexes/

 

어차피 sys스키마도 저 테이블을 show create table로 보면 performance schema를 이용한다.

CREATE ALGORITHM=MERGE DEFINER=`mysql.sys`@`localhost` SQL SECURITY INVOKER VIEW `schema_unused_indexes` AS select `t`.`OBJECT_SCHEMA` AS `object_schema`,`t`.`OBJECT_NAME` AS `object_name`,`t`.`INDEX_NAME` AS `index_name` from (`performance_schema`.`table_io_waits_summary_by_index_usage` `t` join `information_schema`.`statistics` `s` on(((`t`.`OBJECT_SCHEMA` = `s`.`TABLE_SCHEMA`) and (`t`.`OBJECT_NAME` = `s`.`TABLE_NAME`) and (`t`.`INDEX_NAME` = `s`.`INDEX_NAME`)))) where ((`t`.`INDEX_NAME` is not null) and (`t`.`COUNT_STAR` = 0) and (`t`.`OBJECT_SCHEMA` <> 'mysql') and (`t`.`INDEX_NAME` <> 'PRIMARY') and (`s`.`NON_UNIQUE` = 1) and (`s`.`SEQ_IN_INDEX` = 1)) order by `t`.`OBJECT_SCHEMA`,`t`.`OBJECT_NAME`

 

 

방법2) performance_schema DB이용

performance_schema DB는 MySQL서버 재기동시 데이터가 사라진다.

따라서 show global status like '%uptime%' DB기동시간을 분으로 환산해서, 나누기 24하면, 일단위. 최소 한달 이상이 있어야 신뢰할 수 있다.

PK를 제외하고, 인덱스에 접근한 read, write 수가 0인 인덱스를 조회한다.

SELECT
    object_schema,        -- DB명
    object_name,        -- 테이블명
    index_name        -- 인덱스명
  FROM performance_schema.table_io_waits_summary_by_index_usage        --  wait/io/table/sql/handler 에 의해 생성된 모든 테이블 인덱스 I/O 대기 이벤트를 집계.
 WHERE index_name IS NOT NULL        -- “인덱스명이NULL이 아님"
   AND index_name != 'PRIMARY’        -- “PK인덱스를 제외"
   AND count_star = 0        -- "인덱스에 접근한 read, write row 가 0인 조건" (count_star = count_read + count_write)
   AND object_schema NOT IN ('mysql', 'performance_schema’)        -- “SystemDB 제외"
 ORDER BY object_schema, object_name;

 

 

참고

https://dev.mysql.com/doc/refman/5.6/en/table-waits-summary-tables.html#table-io-waits-summary-by-index-usage-table

https://dev.mysql.com/doc/mysql-perfschema-excerpt/5.6/en/table-io-waits-summary-by-table-table.html