MySQL에서 인덱스 목록을 조회하는 방법

SHOW 명령어로 MySQL 인덱스 확인하기

먼저 MySQL에서 인덱스 정보를 표시하는 방법부터 살펴보겠습니다. 인덱스를 확인할 때는 SHOW 명령어를 사용합니다.

인덱스를 조회하는 쿼리는 다음과 같습니다.

mysql> SHOW INDEX FROM indexingdemo;

위 쿼리의 실행 결과입니다.

+--------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table        | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible |
+--------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| indexingdemo |          1 | indexName |            1 | Name        | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     |
+--------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
1 row in set (0.17 sec)

SHOW INDEX 결과에는 테이블 이름(Table), 중복 허용 여부(Non_unique), 인덱스 이름(Key_name), 컬럼 이름(Column_name), 인덱스 유형(Index_type) 등 해당 인덱스에 대한 상세 정보가 함께 출력됩니다.

INFORMATION_SCHEMA.STATISTICS로 인덱스 목록 조회하기

특정 데이터베이스에 존재하는 모든 인덱스 목록을 한 번에 가져오려면 INFORMATION_SCHEMA.STATISTICS 테이블을 활용하면 됩니다. 다음 쿼리를 통해 MySQL의 인덱스 목록을 손쉽게 얻을 수 있습니다.

먼저 기본 문법부터 확인해 보겠습니다.

SELECT DISTINCT
TABLE_NAME,
INDEX_NAME
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'yourDatabaseName';

이제 위의 문법을 실제 데이터베이스에 적용하여 인덱스 목록을 조회해 보겠습니다.

mysql> SELECT DISTINCT
   ->     TABLE_NAME,
   ->     INDEX_NAME
   -> FROM INFORMATION_SCHEMA.STATISTICS
   -> WHERE TABLE_SCHEMA = 'business';

다음은 'business' 데이터베이스의 인덱스 목록을 표시하는 실행 결과입니다.

+--------------------------+------------------+
| TABLE_NAME               | INDEX_NAME       |
+--------------------------+------------------+
| addcolumntable           | Name_Index       |
| addingautoincrement      | PRIMARY          |
| addingunique             | name             |
| autoincrement            | PRIMARY          |
| autoincrementtable       | PRIMARY          |
| bookindexes              | BookName         |
| childdemo                | ConstChild       |
| clonestudent             | idIndex          |
| clonestudent             | NameStuIndex     |
| college                  | PRIMARY          |
| compositeprimarykey      | PRIMARY          |
| demoauto                 | PRIMARY          |
| demoindex                | PRIMARY          |
| demoschema               | idDemoIndex      |
| duplicatebookindexes     | BookName         |
| employeeinformation      | PRIMARY          |
| foreigntable             | constFKPK        |
| foreigntabledemo         | FKConst          |
| functionindexdemo        | indFirstName     |
| indexingdemo             | indexName        |
| keydemo                  | PRIMARY          |
| lastinsertrecordiddemo   | PRIMARY          |
| multipleindexdemo        | id               |
| nextiddemo               | PRIMARY          |
| parentdemo               | PRIMARY          |
| primarytable             | PRIMARY          |
| primarytable1            | PRIMARY          |
| primarytabledemo         | PRIMARY          |
| schemadatabasemethoddemo | PRIMARY          |
| sequencedemo             | PRIMARY          |
| student                  | idIndex          |
| student                  | NameStuIndex     |
| studentenrollment        | StudCollegeConst |
| tabledemo2               | ConstFK          |
| tabledemo3               | ConstFK          |
| tablepri                 | PRIMARY          |
| tblf                     | ConstFK          |
| tblp                     | PRIMARY          |
| transcationdemo          | PRIMARY          |
| triggedemo               | PRIMARY          |
| uniqueautoid             | id               |
| uniqueconstdemo          | name             |
| uniquedemo               | name             |
| uniquedemo1              | id               |
| updtable                 | PRIMARY          |
| usernameandpassworddemo  | PRIMARY          |
| usernameandpassworddemo  | UserId           |
+--------------------------+------------------+
47 rows in set (0.07 sec)

응용: 특정 테이블의 인덱스만 조회하기

WHERE 절에 TABLE_NAME 조건을 추가하면 데이터베이스 내 원하는 테이블의 인덱스만 골라서 확인할 수 있어 더욱 편리합니다.

SELECT DISTINCT
    TABLE_NAME,
    INDEX_NAME
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'yourDatabaseName'
  AND TABLE_NAME = 'yourTableName';