현재 데이터베이스의 모든 인덱스를 확인하는 방법

게시 2025/08/27 업데이트 2025/08/27

By HyoYoonNam

조회 7 분읽는 시간

해당 포스팅을 읽으면:
특정 테이블에 존재하는 인덱스만을 확인하는 것이 아니라, DB에 존재하는 모든 테이블의 인덱스를 한눈에 확인할 수 있습니다.

Environments

Software: MySQL Server/Workbench 8.0.43
OS: macOS Sequoia 15.6
CPU: x86

1. 서론

기본적으로 여러 강의나 인터넷, 책 등에서 생성된 인덱스를 확인하기 위해 다음과 같은 방법을 제시합니다.

  
-- SHOW INDEXES FROM my_table;
SHOW INDEXES FROM users;

그런데 위 방법은 코드에서 알 수 있듯이 ‘특정 테이블에 대한’ 인덱스 목록만 확인이 가능합니다. 만약 다음과 같은 요구사항이 있다면 어떻게 처리할까요?

“users 테이블 뿐만 아니라 items, employees 등 현재 데이터베이스에 존재하는 모든 테이블의 인덱스를 확인하고 싶어.”

다음과 같이 현재 데이터베이스 상의 테이블 목록을 확인하고, 각 테이블마다 SHOW INDEXES FROM ...; 쿼리를 날려야 할까요? 이런 방법은 상황에 따라서는 특정 테이블의 인덱스만 확인할 수 있기에 유용하겠지만, 지금 우리의 요구사항을 충족하기 위해서는 너무 귀찮은 방법이지 않나요?

  
-- 현재 데이터베이스 상의 테이블 목록 확인
SHOW TABLES; -- returns: employees, items, users, ...

-- 각 테이블마다 인덱스 확인 쿼리를 날림
SHOW INDEXES FROM employees;
SHOW INDEXES FROM items;
SHOW INDEXES FROM users;
SHOW INDEXES FROM ...;

해당 포스팅에서 사용된 예제용 데이터베이스는 김영한의 실전 데이터베이스 기본 강의에 포함된 것임을 알립니다.

2. 본론

결론부터 말하자면, 다음 명령으로 현재 사용중인 데이터베이스에 존재하는 모든 테이블의 인덱스 목록을 확인할 수 있습니다.

코드

  
SELECT    TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX, COLUMN_NAME, INDEX_TYPE
FROM      INFORMATION_SCHEMA.STATISTICS 
WHERE     TABLE_SCHEMA = DATABASE()
ORDER BY  TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX;

결과 예시

그렇다면 이제 위 쿼리가 어떤 원리로 우리의 요구사항을 충족시켰는지 간단하게 알아봅시다!

2.1. INFORMATION_SCHEMA와 STATISTICS 테이블

MySQL 레퍼런스 매뉴얼 - INFORMATION_SCHEMA에서는 INFORMATION_SCHEMA에 대해서 다음과 같이 설명합니다.

데이터베이스나 테이블 이름, 열의 데이터 타입, 접근 권한 등 SQL 서버에서 관리하는 메타데이터에 접근할 수 있는 스키마

우리는 해당 스키마에서 제공하는 테이블 중 STATISTICS 테이블에 집중하면 됩니다. 마찬가지로 MySQL 레퍼런스 매뉴얼 - INFORMATION_SCHEMA Table Reference에서는 해당 테이블에 대해 다음과 같이 설명하네요. 해당 테이블을 통해 테이블(들)에 대한 인덱스 통계 정보를 확인할 수 있을 것으로 기대됩니다.

STATISTICS: Table index statistics

기본적으로 해당 테이블의 모든 정보를 조회해봅시다.

코드

  
SELECT * FROM INFORMATION_SCHEMA.STATISTICS;

실행 결과 예시

앞서 언급했듯이 INFORMATION_SCHEMA 스키마는 ‘현재 SQL 서버에서 관리하는 메타데이터’에 대한 정보를 저장하기 때문에 그 하위인 STATISTICS 테이블에서도 역시 blog_test, my_shop, my_shop2와 같이 여러 스키마에 대한 인덱스 정보가 저장됨을 확인할 수 있습니다.