[MariaDB] 존재하는 인덱스 모두 조회 (SELECT ALL INDEXES)

철수 전 기록 남기기 - 1.

 

SELECT INDEX_SCHEMA
     , INDEX_NAME
     , GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) AS INDEX_COLUMNS
     , INDEX_TYPE
     , CASE NON_UNIQUE WHEN 1 THEN 'NON_UNIQUE' ELSE 'UNIQUE' END AS IS_UNIQUE
     , TABLE_NAME
  FROM INFORMATION_SCHEMA.STATISTICS
 WHERE TABLE_SCHEMA NOT IN ('INFORMATION_SCHEMA', 'MYSQL', 'PERFORMANCE_SCHEMA', 'SYS')
   AND INDEX_NAME NOT IN ('PRIMARY')
 GROUP BY INDEX_SCHEMA
        , INDEX_NAME
        , INDEX_TYPE
        , NON_UNIQUE
        , TABLE_NAME
 ORDER BY INDEX_SCHEMA
        , INDEX_NAME;

 

PRIMARY KEY 까지 필요할 시 조건절만 수정하면 된다.

 

 

각 컬럼에 대한 설명은 이렇다.

Columns

• index_schema - index schema (database)
• index_name - name of the index
• index_columns - list of index columns separated by ","
• index_type
  • BTREE
  • RTREE
  • FULLTEXT
  • HASH
  • SPATIAL
• is_unique - whether index is unique
  • Unique
  • Not unique
• table_name - name of the table

* 참고 : https://dataedo.com/kb/query/mariadb/list-all-indexes-in-the-database