MySQL slow_query 느린 쿼리 추출

개요 : slow_query에서 7일 단위로 느린 SQL 총수행시간(=실행건수 * 평균소요시간 ) 기준으로 추출하기

 

-- 튜닝 대상 TOP 50 추출

--1. TOP50 저장 테이블 생성

DROP TABLE SLOW_QUERY_TOP50;

CREATE TABLE SLOW_QUERY_TOP50
(
EXTRACT_DATE DATE NOT NULL,
RN DECIMAL NOT NULL,
START_TIME DATETIME NOT NULL,
USER_HOST VARCHAR(100) COLLATE UTF8MB4_0900_BIN NOT NULL,
ROW_SENT DECIMAL NOT NULL,
ROW_EXMAINED DECIMAL NOT NULL,
MIN_QUERY_TIME VARCHAR(10) COLLATE UTF8MB4_0900_BIN NOT NULL,
MAX_QUERY_TIME VARCHAR(10) COLLATE UTF8MB4_0900_BIN NOT NULL,
AVG_QUERY_TIME VARCHAR(10) COLLATE UTF8MB4_0900_BIN NOT NULL,
CNT DECIMAL NOT NULL,
TOTAL_QUERY_TIME VARCHAR(10) COLLATE UTF8MB4_0900_BIN NOT NULL,
SQL_TEXT MEDIUMBLOB NOT NULL,
TUNNING_YN VARCHAR(100),
PRIMARY KEY(EXTRACT_DATE, RN)
)ENGINE=INNODB DEFAULT CHARSET=UTF8MB4 COLLATE=UTF8MB4_0900_BIN;


--2. 추출 대상 넣기
SET @RN = 0;

INSERT SLOW_QUERY_TOP50
SELECT  NOW(), @RN:=@RN+1 AS RN
      , 최근수행시간, 결과건수, 수행중처리행수, 최소수행시간, 최해대수행시간, 평균수행시간, 수행횟수, 총수행시간, 대상쿼리
 FROM ( 
      SELECT  a.start_time AS 최근수행시간, a.user_hot, a.rows_sent 결과건수
            , a.rows_examined 수행중처리행수, x.min_query_time 최소수행시간, x.max_query_time 최대수행시간
            , x.avg_query_time 평균수행시간, x.cnt 수행횟수
            , SET_TO_TIME(TIME_TO_SEC(x.avg_query_time)*cnt) 총수행시간
            , a.sql_text 대상쿼리
        FROM mysql.slow_log a
       INNER JOIN ( 
                  SELECT  SUBSTRING(INSTR(REGEXP_REPLACE(UPPER(sql_text),'[^[:alpha:]]',''),'SELECT')1,200) SQL_TEXT
                        , MAX(start_time) start_time
                        , MIN(TIME_TO_SEC(query_time)) min_query_time_sec
                        , DATE_FORMAT(MIN(query_time),'%T') min_query_time
                        , DATE_FORMAT(MAX(query_time),'%T') max_query_time
                        , DATE_FORMAT(SEC_TO_TIME(avg(TIME_TO_SEC(query_time))), '%T') avg_query_time
                        , count(*) cnt
                    FROM mysql.slow_log a
                   WHERE user_host not like '%DEV%'
                     AND query_time > '00:00:05' -- 튜닝 대상 시간 선정(5초 이상 수행 쿼리 대상)
                     AND a.sql_text not like '%INSERT%'
                     AND a.sql_text not like '%ApplicationName=DBeaver%'
                     AND a.start_time >= date_sub(now(), INTERVAL 7 day) -- 7일전 부터 실행 쿼리 기준
                     AND LENGTH(REGEXP_REPLACE(sql_text,'[^[:alpha:]]','')) > 50 -- 50자 이상 쿼리
                  GROUP BY SUBSTRING(INSTR(REGEXP_REPLACE(UPPER(a.sql_text),'[^[:alpha:]]',''),'SELECT'),1,200)
                  ) x
                  ON SUBSTRING(INSTR(REGEXP_REPLACE(UPPER(a.sql_text),'[^[:alpha:]]',''),'SELECT'),1,200) = x.sql_text
                 AND a.start_time = x.start_time
                 AND x.min_query_time > '00:00:05'
                 AND x.avg_query_time > '00:00:05'
       WHERE user_host not like '%DEV%'
         AND query_time > '00:00:05'
         AND a.sql_text not like '%INSERT%' -- INSERT 대상 제외
         AND a.sql_text not like '%ApplicationName=DBeaver%'
         AND a.start_time >= date_sub(now(), INTERVAL 7 day) -- STR_TO_DATE('2025-02-05 13:00:00','%Y%m%d %H:%i:%S')
       ORDER BY 총수행시간 DESC
       LIMIT 50
     ) a
    ;


-- 결과 추출 
-- 2회차는 NOT EXISTS 추가
SET @RN = 0;

SELECT EXTRACT_DATE||'_'||LPAD(@RN:@RN+1,2,'0') EXTRACT_RN, RN, EXTRACT_DATE, USER_HOST
     , ROW_EXAMINED, ROW_SENT, MIN_QUERY_TIME, AVG_QUERY_TIME, MAX_QUERY_TIME, CNT
     , TOTAL_QUERY_TIME, SQL_TEXT, TUNNING_YN
  FROM SLOW_QUERY_TOP50 A
 WHERE EXTRACT_DATE = '2025-02-05'
   AND NOT EXISTS(SELECT 1 
                    FROM SLOW_QUERY_TOP50 X 
                   WHERE EXTRACT_DATE !='2025-02-05'
                     AND SUBSTRING(INSTR(REGEXP_REPLACE(UPPER(X.SQL_TEXT),'[^[:alpha:]]',''),'SELECT',1,200) = SUBSTRING(INSTR(REGEXP_REPLACE(UPPER(A.SQL_TEXT),'[^[:alpha:]]',''),'SELECT'),1,200))
;


-- 정규식 시간제한으로 에러 시 방안
-- 1. SQL_TEXT 변경 쿼리 : 좀 더 부 정확함 
SUBSTRING(INSTR(REPLACE(UPPER(sql_text),' ',''),'SELECT'),1,200) SQL_TEXT

-- 2. 중간 테이블 추가하여 추출 (추후)