MySQL INFORMATION_SCHEMA.key_column_usage 성능 저하 해결 방법

INFORMATION_SCHEMA.key_column_usage 조회가 느릴 때 해결 방법

INFORMATION_SCHEMA.key_column_usage 테이블 조회 시 응답 속도가 지나치게 느린 문제는 InnoDB의 메타데이터 통계 수집 설정 때문에 발생하는 경우가 많습니다. 아래와 같이 GLOBAL 변수를 조정하면 간단히 해결할 수 있습니다.

1. innodb_stats_on_metadata 변수 비활성화

다음 구문을 실행하여 전역 변수를 비활성화합니다.

SET global innodb_stats_on_metadata =0;

innodb_stats_on_metadata가 활성화되어 있으면(기본값), INFORMATION_SCHEMA 테이블 조회나 SHOW STATUS처럼 메타데이터에 접근하는 쿼리를 실행할 때마다 InnoDB가 테이블 및 인덱스 통계를 다시 계산합니다. 이 과정에서 불필요한 디스크 I/O가 대량으로 발생해 조회 속도가 크게 느려집니다. 이 값을 0으로 설정하면 통계 재계산이 생략되어 INFORMATION_SCHEMA.key_column_usage 쿼리 실행 시간이 크게 단축되고 전반적인 성능이 향상됩니다.

2. 설정 적용 후 쿼리 실행 예시

mysql> set global innodb_stats_on_metadata =0;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT REFERENCED_TABLE_NAME,TABLE_NAME,COLUMN_NAME,CONSTRAINT_SCHEMA
    -> FROM INFORMATION_SCHEMA.key_column_usage;

실행 결과

MySQL INFORMATION_SCHEMA.key_column_usage 성능 저하 해결 방법

설정 변경 후 동일한 쿼리가 674개의 행을 0.28초 만에 반환하며, 성능이 눈에 띄게 개선된 것을 확인할 수 있습니다.

추가 참고 사항

SET global로 변경한 값은 MySQL 서버가 재시작되면 기본값으로 초기화될 수 있습니다. 영구적으로 적용하려면 my.cnf(또는 my.ini) 설정 파일의 [mysqld] 섹션에 innodb_stats_on_metadata=0을 추가하는 것이 좋습니다. 다만 통계 자동 갱신이 비활성화되므로, 옵티마이저가 정확한 통계를 필요로 하는 운영 환경이라면 ANALYZE TABLE을 주기적으로 실행해 통계를 수동으로 갱신해 주는 것이 권장됩니다.