[MYSQL] INDEX 확인 및 생성 스크립트

1. 특정 Database 및 Table의 Index 확인 쿼리

SELECT A.TABLE_SCHEMA "DB", 
        A.TABLE_NAME "테이블ID",
        A.TABLE_COMMENT "테이블명",
        B.INDEX_NAME "인덱스명",
        B.COLUMN_NAME "컬럼ID",
        C.DATA_TYPE "데이터타입",
        CASE WHEN C.NUMERIC_SCALE IS NULL OR C.NUMERIC_SCALE = '0' 
             THEN CASE WHEN C.NUMERIC_PRECISION IS NOT NULL 
                  THEN C.NUMERIC_PRECISION 
                  ELSE C.CHARACTER_MAXIMUM_LENGTH END 
             ELSE CONCAT(C.NUMERIC_PRECISION, ',', C.NUMERIC_SCALE) 
        END "데이터사이즈",
        CASE WHEN B.INDEX_NAME = 'PRIMARY' THEN 'Y' ELSE 'N' END "PRIMARY",
        CASE WHEN B.NON_UNIQUE = 0 THEN 'Y' ELSE 'N' END "UNIQUE",
        B.SEQ_IN_INDEX "컬럼순서"
FROM information_schema.`TABLES` A 
 JOIN information_schema.STATISTICS B
   ON A.TABLE_SCHEMA = B.TABLE_SCHEMA 
  AND A.table_name = B.TABLE_NAME
 JOIN information_schema.`COLUMNS` C
   ON A.TABLE_SCHEMA = C.TABLE_SCHEMA 
  AND A.table_name = C.TABLE_NAME
  AND B.COLUMN_NAME = C.COLUMN_NAME
 WHERE A.table_schema = 'oyo'  ## Database명
   AND A.table_name = 'POM901' ## Table명
ORDER BY A.TABLE_SCHEMA, A.TABLE_NAME, CASE WHEN B.INDEX_NAME = 'PRIMARY' THEN 1 ELSE B.INDEX_NAME END, B.SEQ_IN_INDEX;

 

2. Index 생성 스크립트 추출 구문

SELECT
CONCAT('ALTER TABLE `',TABLE_NAME,'` ','ADD ',
IF(NON_UNIQUE = 1,
 CASE UPPER(INDEX_TYPE)
 WHEN 'FULLTEXT' THEN 'FULLTEXT INDEX'
 WHEN 'SPATIAL'  THEN 'SPATIAL INDEX'
 ELSE CONCAT('INDEX `',
 INDEX_NAME,
 '` USING ',
 INDEX_TYPE
 )
 END,
IF(UPPER(INDEX_NAME) = 'PRIMARY',
CONCAT('PRIMARY KEY USING ',INDEX_TYPE),
CONCAT('UNIQUE INDEX `',INDEX_NAME,'` USING ',INDEX_TYPE))
),
'(',
GROUP_CONCAT(
DISTINCT
CONCAT('`', COLUMN_NAME, '`')
ORDER BY SEQ_IN_INDEX ASC
SEPARATOR ', '
),
');'
) AS 'Show_Add_Indexes'
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'dkgcsp'
##and NON_UNIQUE = 1  
GROUP BY TABLE_NAME, INDEX_NAME, NON_UNIQUE, INDEX_TYPE
ORDER BY TABLE_NAME ASC, INDEX_NAME ASC;