주니어 DBA를 위한 InnoDB 상태 조회 및 설정 관련 쿼리 모음

-- MySQL InnoDB 상태 조회
show engine innodb status\G;

-- MySQL 서버 내에서 실행중인 프로세스 목록 가져오기
SHOW PROCESSLIST;

-- FULL SCAN 탐지 쿼리
select db, query, exec_count, sys.format_time(total_latency) as "formatted_total_latency", rows_sent_avg, rows_examined_avg, last_seen
FROM sys.x$statements_with_full_table_scans
ORDER BY total_latency DESC;

-- Check engine type of specific table
SHOW TABLE STATUS WHERE Name = 'table_name';

-- 현재 SQL데이터베이스 상에서 설정된 sql_mode 가져오기
-- MySQL 5.7부터는 sql_mode라는 개념이 추가되어서 정규화 및 정합성을 위한 규칙을 설정할 수 있게 됨
-- 해당 결과에 "ONLY_FULL_GROUP_BY"가 있다면 GROUP BY 절의 타겟 컬럼에 의해 모든 컬럼들이 그룹화될 수 있어야만 한다.
SELECT @@sql_mode;

-- 이를 변경하는 방법은 아래와 같다:
-- 해당 세션에서만 변경할 때에는 `SET SESSION`
SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
-- 전역적으로 변경할 때에는 `SET GLOBAL`
SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
-- 아래는 MySQL 8.0 기본 설정이다.
-- ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION


-- 잘 사용되지 않는 인덱스 목록을 가져오는 쿼리
select * from sys.schema_unused_indexes where object_schema NOT IN ('performance_schema');

-- 중복 인덱스 목록 가져오기
select * from sys.schema_redundant_indexes;



-- Get list of all threads that are running
SELECT thread_id, name, type, processlist_user, processlist_host
FROM performance_schema.threads order by type, thread_id;


-- Get waiting queries and blocking queries
SELECT
    r.trx_id waiting_trx_id,
    r.trx_mysql_thread_id waiting_thread,
    r.trx_query waiting_query,
    b.trx_id blocking_trx_id,
    b.trx_mysql_thread_id blocking_thread,
    b.trx_query blocking_query
FROM performance_schema.data_lock_waits w
INNER JOIN information_schema.innodb_trx b
    ON b.trx_id = w.blocking_engine_transaction_id
INNER JOIN information_schema.innodb_trx r
    ON r.trx_id = w.requesting_engine_transaction_id;