MySQL - 테이블의 Index 확인하는 방법들 정리

 

Index 확인을 위한 테스트 테이블입니다.

create table TEST_TABLE
(
    id      bigint auto_increment primary key,
    level   int default 0 not null comment '레벨',
    created datetime      not null comment '생성일',
    updated datetime      not null comment '수정일'
);
create index idx_level_updated on TEST_TABLE (level, updated);
create index idx_updated on TEST_TABLE (updated);

 

SHOW INDEX FROM TableName;

가장 쉽게 INDEX를 조회하는 방법은 다음과 같습니다.

SHOW INDEX FROM TEST_TABLE;

조회하기는 쉽지만 멀티칼럼 인덱스인 경우에는 한눈에 안 들어옵니다.

 

 

DataGrip - Cmd + b, Declaration 확인

JetBrains DataGrip을 사용한다면 유용한 방법입니다.

테이블에 키보드 커서를 옮긴 다음 Cmd + b를 누릅니다. 그러면 다음과 같은 Choose Declaration 선택창이 나타납니다. console이 적혀 있는 건 아까 테이블 생성할 때 사용한 쿼리로 가는 거고 아래쪽에 console이 없는 것을 선택합니다.

아래처럼 자동으로 생성된 테이블 생성문과 인덱스 생성문을 볼 수 있습니다.

인덱스를 생성할 때 사용하는 명령문이 나타나기 때문에 개발 환경에서 테스트가 완료되면 리얼 환경에 테이블 생성할 때 유용합니다. 개인적으로는 이렇게 보는 게 눈에도 더 잘 들어오더군요.

 

information_schema.STATISTICS 테이블 내용 조합

아래와 같이 information_schema.STATISTICS를 조회해 보면 show index과 비슷한 결과를 확인할 수 있습니다.

SELECT *
FROM information_schema.STATISTICS
WHERE TABLE_NAME = 'TEST_TABLE';

 

이 정보를 아래의 쿼리를 이용해서 잘 조합하면

SELECT INDEX_NAME                                                     AS `Index Name`,
       GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX SEPARATOR ', ') AS `Columns`,
       IF(MAX(NON_UNIQUE) = 1, 'No', 'Yes')                           AS `Unique`
FROM information_schema.STATISTICS AS s
WHERE s.table_schema = DATABASE()
  AND s.TABLE_NAME = 'TEST_TABLE' -- 테이블명 지정
GROUP BY INDEX_NAME
ORDER BY IF(INDEX_NAME = 'PRIMARY', '1', INDEX_NAME);

다음과 같이 index 정보를 정리해서 볼 수 있습니다.