[MySQL]카탈로그를 이용하여 각종 정보 조회

■ 카탈로그를 이용하여 각종 정보 조회

 

▶︎ 최근 사용이 되었던 table

select * 
  from information_schema.TABLES
 order by UPDATE_TIME desc
;

 

▶︎ 테이블 생성시간별 조회

select *
  from information_schema.TABLES
 where table_schema='database'
 order by create_time desc
;

 

▶︎ 활성중인 프로세스 리스트만 확인

select *
  from information_schema.PROCESSLIST
 where 1=1
   and command <> 'Sleep'
   and user='service_user'
   and info is not null
 order by time desc;

 

▶︎ host CONNECTION 갯수

SELECT USER, SUBSTR(HOST, 1,(INSTR(HOST, ':') -1)), COUNT(*) 
  FROM information_schema.processlist
 GROUP BY USER, SUBSTR(HOST, 1,(INSTR(HOST, ':') -1)) ;

 

▶︎ 특정 유저에 대해 한번에 Kill 명령어 만들기

select CONCAT('kill \'',ID, '\';')
  from information_schema.PROCESSLIST
 where user='user';

 

▶︎ 여러 오브젝트 크기 조회

• database 사이즈

select round(sum(DATA_LENGTH)/1024/1024/1024) as DATA_SIZE_GB, round(sum(INDEX_LENGTH)/1024/1024/1024) as INDEX_LENGTH_GB
  from information_schema.TABLES
 where table_schema not in
(
'information_schema',
'performance_schema',
'innodb',
'mysql',
'sys',
'tmp'
);

 

 

 

 

• 모든 Table, index 사이즈

select TABLE_SCHEMA, TABLE_NAME, round(DATA_LENGTH / 1024 / 1024) as DATA_SIZE_MB,
       round(INDEX_LENGTH / 1024 / 1024 ) as INDEX_LENGTH_MB,
       round((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) as TOTAL_SIZE_MB
  from information_schema.TABLES
 where table_schema not in
(
'information_schema',
'performance_schema',
'innodb',
'mysql',
'sys',
'tmp'
)
order by 5 desc
;

 

• 특정 테이블의 테이블 크기와 인덱스 크기

select TABLE_SCHEMA, TABLE_NAME, round(DATA_LENGTH / 1024 / 1024) as DATA_SIZE_MB, round(INDEX_LENGTH / 1024 / 1024 ) as INDEX_LENGTH_MB, round((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) as TOTAL_SIZE_MB
  from information_schema.TABLES
 where table_schema = 'sbtest'
   and table_name='sbtest1'
 order by 5 desc
;

 

• 특정 테이블의 인덱스별 크기

SELECT database_name, table_name, index_name, 
       round(stat_value*@@innodb_page_size/1024/1024) size_in_mb
  FROM mysql.innodb_index_stats
 WHERE stat_name = 'size' AND index_name != 'PRIMARY'
   and table_name='sbtest'
   and index_name in
(
'idx_col_01',
'idx_col_02',
'idx_col_03'
)
ORDER BY 4 DESC;

 

 

• 단편화 비율 및 확보가능용량

SELECT -- ENGINE,
       TABLE_NAME,
       ROUND(DATA_LENGTH/1024/1024) AS DATA_LENGTH_MB ,
       ROUND(INDEX_LENGTH/1024/1024) AS INDEX_LENGTH_MB,
       ROUND((DATA_LENGTH+INDEX_LENGTH)/1024/1024) as USED_MB,
       ROUND(DATA_FREE/1024/1024) AS DATA_FREE
       (DATA_FREE/(INDEX_LENGTH+DATA_LENGTH))*100 AS FRAG_RATIO
  FROM INFORMATION_SCHEMA.TABLES
 WHERE DATA_FREE > 0
 ORDER BY DATA_FREE DESC;

 

 

• 미사용 index 확인

SELECT object_schema, object_name, index_name
  FROM performance_schema.table_io_waits_summary_by_index_usage 
 WHERE index_name IS NOT NULL
   AND count_star = 0
   and object_schema not in
(
'information_schema',
'performance_schema',
'innodb',
'mysql',
'sys',
'tmp'
)
   and OBJECT_NAME='sbtest1'
 ORDER BY object_schema, object_name;