performance_schema 설정

MySQL에서는 performance_schema의 table_io_waits_summary_by_index_usage 테이블을 통해 인덱스의 사용 빈도를 모니터링할 수 있습니다. 이 테이블은 각 테이블의 각 인덱스에 대한 I/O 활동을 수집하며, 이를 통해 사용되지 않는 인덱스를 파악할 수 있습니다. 인덱스 I/O 활동을 확인하려면 먼저 performance_schema 기능이 활성화되어 있어야 합니다. MySQL 8.0에서는 기본적으로 활성화되어 있지만, 비활성화되어 있다면 MySQL 설정 파일에서 다음 설정을 추가해야 합니다.

[mysqld]
performance_schema=ON

performance_schema 옵션이 활성화된 이후부터 인덱스 사용량이 카운팅되기 시작합니다. performance_schema는 MySQL 서버가 실행될 때부터 데이터를 수집하며, 이 옵션이 켜져 있어야만 인덱스 사용 통계를 포함한 다양한 성능 관련 데이터를 모니터링할 수 있습니다.

예를들어 MySQL 서버가 한 달 전에 재시작되었다면, 그 이후의 1개월 치 쿼리들에 대한 인덱스 사용량만 확인하게 됩니다. 만약 1년에 한 번 실행되는 통계 조회용 쿼리가 있다면, 이 쿼리에서 사용되는 인덱스가 나중에 필요해질 수 있습니다. MySQL에서는 performance_schema가 언제 활성화(ON)되었는지 직접적으로 확인할 수 있는 내장된 타임스탬프 기능이 제공되지 않습니다. 그러나 performance_schema는 MySQL 서버가 시작될 때부터 동작하기 때문에, MySQL 서버의 시작 시간을 확인함으로써 언제부터 활성화되었는지 아래 쿼리를 통해 간접적으로 추정할 수 있습니다.

SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(NOW()) - VARIABLE_VALUE) AS server_start_time
FROM performance_schema.global_status
WHERE VARIABLE_NAME = 'Uptime';
server_start_time
2023-01-09 02:21:57.000000

인덱스 읽기 횟수(Index Read)가 0이라고 해도, 용량이 큰 테이블에서 인덱스를 삭제할 때는 보수적으로 접근하는 것이 좋습니다. 인덱스 삭제는 간단하지만, 용량이 큰 테이블에 다시 인덱스를 추가하는 작업은 비용이 많이 들고 시간이 오래 걸릴 수 있습니다. 따라서 테이블별 용량을 먼저 확인한 후, 인덱스를 삭제할지 신중한 판단이 필요합니다. 아래 쿼리를 사용하면 각 테이블의 용량을 확인할 수 있습니다.

SELECT table_name AS 'table_name',
       ROUND((data_length + index_length) / 1024 / 1024 / 1024, 2) AS 'total_Size (GB)',
       ROUND(data_length / 1024 / 1024 / 1024, 2) AS 'data_size (GB)',
       ROUND(index_length / 1024 / 1024 / 1024, 2) AS 'index_size (GB)'
FROM information_schema.tables
ORDER BY (data_length + index_length) DESC;
table_name total_size (GB) data_size (GB) index_size (GB)
a 536.44 535.43 1.01
b 380.86 380.01 0.85
c 287.55 286.17 1.37
d 0.35 0.24 0.11

인덱스 읽기 횟수(index_reads) 확인

아래 쿼리를 통해 인덱스 읽기 횟수(index_reads)를 확인할 수 있습니다. 

SELECT OBJECT_NAME AS table_name,
       INDEX_NAME  AS index_name,
       COUNT_READ  AS index_reads
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE INDEX_NAME IS NOT NULL
  AND OBJECT_SCHEMA = 'schemaName' # 조회할 스키마 이름
ORDER BY COUNT_READ DESC;
table_name index_name index_reads
a PRIMARY 1229852222
a idx_a 459178562
b PRIMARY 351552482
b idx_b 145150959
c PRIMARY 82724129
c idx_c 0

너무 많은 인덱스들이 조회되게 됩니다. 테이블 용량이 큰 a, b, c 테이블의 index count만 확인해보겠습니다. 추가적으로 information_schema.STATISTICS 테이블을 조인하여 인덱스하고 있는 필드까지 확인해보도록 하겠습니다. 아래는 해당 인덱스가 어떤 컬럼을 인덱싱하고 있는지 함께 조회하는 쿼리입니다.

SELECT t.OBJECT_NAME AS table_name,
       t.INDEX_NAME AS index_name,
       CONCAT('[', GROUP_CONCAT(s.COLUMN_NAME ORDER BY s.SEQ_IN_INDEX ASC SEPARATOR ', '), ']') AS indexed_columns,
       t.COUNT_READ AS index_reads
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.OBJECT_NAME IN ('a', 'b', 'c') # a, b, c 테이블만
GROUP BY t.OBJECT_SCHEMA,
         t.OBJECT_NAME,
         t.INDEX_NAME,
         t.COUNT_READ
ORDER BY t.COUNT_READ DESC;
table_name index_name indexed_columns index_reads
a PRIMARY [id] 1229852222
a idx_a [id, name] 459178562
b PRIMARY [id] 351552482
b idx_b [name, type] 145150959
c PRIMARY [id] 82724129
c idx_c [id, createdAt, updatedAt] 0

index 삭제

인덱스를 추가할 때는 MySQL이 해당 테이블의 모든 데이터를 읽고, 이를 정렬하여 인덱스 구조(ex. B-tree)에 맞게 삽입해야 하기 때문에 시간이 많이 소요됩니다. 특히 테이블의 데이터가 많으면 인덱스 생성 시간이 길어집니다.

반면에 인덱스를 삭제할 때는 단순히 메타데이터에서 해당 인덱스를 제거하고, 인덱스에 할당된 리소스를 해제하는 작업이므로 데이터 재정렬이나 추가 작업이 필요하지 않아 상대적으로 빠르게 처리됩니다. 따라서 위의 쿼리를 활용하여 불필요한 인덱스를 파악한 후, 성능 최적화를 위해 적절하게 삭제합니다.

# DROP INDEX 인덱스명 ON 테이블명;
DROP INDEX idx_user_name ON users;

 

저작자표시

티스토리툴바