mysql 선택도 나쁜 인덱스 조회 방법

카디널리티(분포도)를 기준으로 테이블 로우수로 나누어, 선택도가 좋지 않은 인덱스 정보를 조회한다.

 

SELECT
  t.TABLE_SCHEMA,        -- DB명
  t.TABLE_NAME,        -- TABLE명
  s.INDEX_NAME,        -- 인덱스명
  s.COLUMN_NAME,        -- 컬럼명
  s.SEQ_IN_INDEX,        -- 1로 시작하는 인덱스의 열 시퀀스 번호
        ( SELECT MAX(SEQ_IN_INDEX)
          FROM INFORMATION_SCHEMA.STATISTICS s2
          WHERE s.TABLE_SCHEMA = s2.TABLE_SCHEMA
          AND s.TABLE_NAME = s2.TABLE_NAME
          AND s.INDEX_NAME = s2.INDEX_NAME
       ) AS `COLS_IN_INDEX`,        --> 복합인덱스 안에서 컬럼 개수
  s.CARDINALITY AS `CARD`,        -- 인덱스 안의 유니크한 값 예상 개수 (높을 수록 인덱스 사용 가능성이 크다.)
  t.TABLE_ROWS AS `ROWS`,        -- 테이블 로우 수 근사치 (실제 카운트 수와 40~50% 다를 수 있음.)
  ROUND(((s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0.01)) * 100), 2) AS `SEL %`        -- 카디널리티를 로우수로 나눈 퍼센트 (만약 100개 중 90개가 유니크하면, 90%, 선택도 좋음)
FROM INFORMATION_SCHEMA.STATISTICS s        -- STATISTICS 테이블 : 테이블 인덱스에 대한 정보
  INNER JOIN INFORMATION_SCHEMA.TABLES t        -- TABLES 테이블 : 테이블에 대한 정보
    ON s.TABLE_SCHEMA = t.TABLE_SCHEMA
    AND s.TABLE_NAME = t.TABLE_NAME
WHERE t.TABLE_SCHEMA != 'mysql’        -- "System DB 제외"
AND t.TABLE_ROWS > 10        -- “테이블 로우 수가 10건 이상인 테이블"
AND s.CARDINALITY IS NOT NULL        -- "카디널리티가 널이 아닌 조건"
AND (s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0.01)) < 1.00        -- “카디널리티를 로우수로 나눴을 때, 1이면 PK인덱스 이므로, PK제외하기 위한 조건" (만약 100개 중 1개가 유니크하면 0.01이고, 100개중 100개가 유니크하면1임 -> 1이라는건 완전 유니크한 PK라는거니까 제외)
ORDER BY `SEL %`, TABLE_SCHEMA, TABLE_NAME;        -- "선택도 안좋은 순서"

 

참고

https://gist.github.com/benders/114299

https://dev.mysql.com/doc/mysql-infoschema-excerpt/5.6/en/statistics-table.html

https://dev.mysql.com/doc/mysql-infoschema-excerpt/5.6/en/tables-table.html