1. SQL 실행계획 분석도구
Explain, Anlayze
오라클은 예상 실행계획과 실제 실행 계획이 다른 경우가 많음.
그러나 MySQL은 대부분은 경우 explain에 나온 실행 계획 그대로 실행 됨
Analyze 는 실제 SQL을 수행한 결과 기반
mysql 8.0 이상에서는 analyze explain으로 명령어 변령 됨
explain으로 파악하고 analyze로 같은지 확인
explain 후에 format 옵션을 주어서 격자무늬가아닌 json으로 보면 조회 됨
json으로 조회시 더 많은 항목을 조회 할 수 있음
explain 뒤에 extended 등 키워드가 있는데 이런 키워드들은 사라지는 추세
MariaDB/MySQL Query 실행 구조
DMBS Engine과 Storage Engine으로 나뉘고 Storage Engine에서 필요한 데이터만 가져오는 것이 튜닝의 기본이다.
Cache는 정말 완전히 동일한 조건일 때만 Cache에서 가져옴
MariaDb - Expalin
통계정보 기반으로 실행 계획이 나옴 Analyze를 하면 통계도 최산화 함
Explain은 Analyze에 비해 실제 Query를 수행하지 않기 떄문에 DB에 부하를 주지 않고 실행 계획을 검토할 수 있음
MariaDB10 부터는 Hash Join 지원, 이전에는 Loof Join
select *
from empyees a,
(select emp_no, dept_no
from dept_emp where emp_no > 90000
) b,
salaries c
where a.emp_no = b.emp_no
and a.emp_no = c.emp_no
linline view 에서 조회한 데이터를 어딘가에 저장하는게 아니라, join 해야할 다른 테이블과 merge 되는 것임
조건절 전이되어 emp_no > 9000은 a.emp_no를 보게 됨
실행 계획의 항목
ID
번호가 같으면 같은 SQL 문장 ( SQL 블락 ), 조건절 전이가 일어나면 같은 ID가 됨
select 의 list에 select 가 있는 경우 - oracle에서는 스칼라 쿼리, mysql에서는 서브쿼리
where절에 있는 select는 oracle, mysql에는 동일하게 서브쿼리라고 함
Access Type
Table Access 에 접근한 방법 - 성능에 가장 큰 영향을 미친다.
| const | PK, Unique Key 가 동등 비교(전체 동일), oracle에서는 unique Scan |
| eq_ref | const와 동일, 다른 점은 const는 driving table에 대하여 const 로 나오고 driving table 외에 inner table에 대하여 eq_ref 라고 조회 됨 |
| ref | 인덱스를 이용하여 동등 비교 |
| index | index를 탄다는 의미가 아니라 index Full Scan 을 한다는 의미, 종종 이게 성능이 나올 때도 있지만 대부분 성능 개성 포인트 |
| all | index 는 성능 개선이 필요한 부분 |
| range | between 사용 시 나타남, index를 mbetween을 사용하면 |
| unique_subquery | 해당 테이블은 oder 할 필요 없음 |
| index_subquery | |
| ref_subquery | |
| ref_or_null |
---const, eq_ref, ref 가 나오면 성능 좋은거!!
- index, all 은 는 성능 개선이 필요한 부분
possible_key
Optimizer가 후보로 선정했던 Key 혹은 Index 목록
key, key_len을 이용하여 index에 해당하는 몇 개의 컬럼을 사용했는지 확인 -> index 를 잘 활용했는지를 체크할 수 있다.
possible_key의 ref 는 const를 의미하고 Table의 Access type의 ref, const는 다른 의미!
rows 스토리지엔진에서 엑세스할 것으로 예측한 row수 ( DBMS에서 추려내는 row수가 아님 ) ,
filtered rows에 보여진느 건 수 증 최종에 사용 되는 데이터 건 수 - 높을 수록 좋음, 낮다는 것은 사용하지 않을 데이터를 스토리지엔진에서 가져온 것이기떄문
기타항목 - mysql의 hint 사용여부를 확인
-> using index : 좋은 것, index만으로 쿼리를 실행함을 의미
-> using where : 스토리지 엔진에서 데이터 조회 후 filtering 처리 후에 group by 등 데이터 후행 작업이 있음 -> dbms에서 추가 작업이 있다는 의미, 인덱스가 적절한지 검토해볼 필요가 있음, 스토리지엔진에서 데이터가 filter되어 조회되는 것이 가장 좋음
-> using index condition : 인덱슬르 잘 사용했다면 using index condition이 찍혀야함
-> index_condition_pushdown : 이전에 부정 조건은 스토리지 엔진에서 처리하지 못하고 DBMS엔진에서 처리했음 이 것을 on로 하면 부정조건도 처리하도록 함
-> using temporary
-> using filesort : index로 orderby하면 조회하면서 바로 정렬이 되는데 이것을 활용하지 못해서 별도로 sort를 진행 함.
-> 페이징 처리 쿼리를 조회 했을 때 Extra 항목에 아무것도 없으면 성능이 좋은 것
select type
dependent_subquery : select문이 길 때 이런 서브쿼리가 있구나 파악하기
materialized :
seelct 절에 있는 sub query 가 성능에 문제가 있는지 확인해보고싶다면 하나씩 제거하면서 성능을 비교해보면 됨
--
* from 절의 select 문은 inline view
* 테이블에서는 alias를 꼭 사용하고, 동일한 테이블을 사용하더라도 alias는 모두 다르게 사용하는 것이 꿀팁
요약
table 좋은거 안좋은거
possible_ke 좋은거 안좋은거
Analyze
주어진 쿼리를 실제로 실행한 후 비용을 계산한 결과를 보여줌
예측 값과 실제 값을 함께 보여줌 ( rows -> 예측 값, r_rows -> 실제 값 , 차이가 많이 난다면 통계를 재실행하기 )
r_rows 는 처음 조회되는 것은 실제 데이터 건수, 이후 부터는 각 join마다 조회해야하는 row수, 곱하기 됨
r_total_time_ms 는 json format에서만 조회 됨, 이게 가장 큰 것을 기준으로 튜닝을 진행하자
ex)
r_rows 는 10만, filtered는 50%이면 r_loofs는 10만, filtered가 100이면 r_loofs는 10만
SQL Hint
힌트의 종류를 확인하고 문법을 찾아보고 사용하기, 문법이 생각보다 쓰기 어려움
MariaDB
| Straiget_Join | From 절에 기술한 테이블 순서대로 Join 성능 문제로 잘 사용하지 않음 |
MySQL
옵티마이저 힌트의 영향 범위에 따라 크게 4개 그룹
-> 글로벌 : 전체 쿼리에 영향
-> 쿼리 블록 : SQL 문장의 특정 쿼리 블록에 영향
-> 테이블 : 쿼리 블록 중 특정 테이블
-> 인덱스 : 테이블의 특정 인덱스에 영향을 미치는 힌트
| SEMIJOIN | 많이 사용!! anti semi-join의 최적화에 사용 |
| SUBQQUERY | 많이 사용!! 서브 ㅜ커리의 세미 조인 최적화 전략, in subquery의 최적화에 사용 |
https://dev.mysql.com/doc/search/?d=371&p=1&q=hint
MySQL :: MySQL 8.4 Reference Manual :: Search Results
Search Results https://dev.mysql.com/doc/refman/8.4/en/optimizer-hints.html Another way to control the optimizer is by using optimizer hints, which can be specified within individual statements. Because optimizer hints apply on a per-statement basis, they
dev.mysql.com
--
Join
Nested Loop Join
일반적으로 알려진 Join 방식으로 많이 사용함
데이터가 적은 부분을 선행 테이블로 해야 함
선행 테이블에서 조회된 건수가 성능에 영향을 줌
드라이빙 테이블에서 조회된 순서대로 데이터가 정렬되어 조회 됨 ( 100% 정렬은 아님 )
Block Nested Look Join (BNL Join, MariaDB)
Hash Join
실제 튜닝 사례들..
Table Full Scan 인 경우 -> Index 추가
Index 변경 ->
논리적으로 True인 조건 추가
-> startDt <팝업 조회 일 < endDt 일 때 between만 사용하는게 아니라 now() < endDt 인 논리적으로 T인 조건 추가하여 index 조회 가능하도록 함
데이터 유형 맞추기
-> 숫자와 문자열, 날짜와 문자열
-> 다른 데이터 유형을 비교하면 내부적으로 컬럼에 있는 데이터를 비교 대상 데이터 형식으로 변경하게 됨, 컬럼은 가공하지말고 컬럼 유형에 맞게 검색 조건을 변경하기
-> ex) varchar(1) Col 인데 Col = 1 로 검색 ===> col = "1" 로 변경
-> ex) datetime Col 인데 date_format(Col, '%Y%m') == "2503" 으로 조건 검색 ===> Col = str_to_date("2503", '%Y%m')
-> sysdate() 는 index 불가, now()로 변경 / sysdate는 가변 데이터, now는 쿼리가 실행될때 고정 됨
-> select sysdate() from table limit 10 하면 각 sysdate 값 ms 단위로 다름, row마다 실행되는 가변데이터임
Order By 주의하기
여러 테이블을 Join 할 때 Index가 존재하는 Table의 Alias를 사용하여 order by 진행하기
1개의 조건으로 total count를 조회하는 Left Outer Join 을 여러개 사용해야하는 경우 eq_ref 기 때문에 상위 테이블만 사용하면 됨.
전체 count하는 쿼리와, 데이터를 조회하는 쿼리를 분리해야 함
조건절에 Case 문장 -> OR로 수정
Join Update/Delete
MariaDb/MySql은 Update나 Delete시에 Join을 이용하여 작업하는 것이 가능함, 의외로 Join을 이용한 Update, Delete가 성능에 매우 유용한 경우가 있음
Oracle에서 Mysql 변경 시 자주 보이는 사례
|| => 문자열 합치기
| ENP_NO >= '2024' || '1111' 의 의미는 | |
| oracle | mysql |
| ENP_NO >= '20241111' | ENP_NO >= ('2024' or '1111') |
Order by, Limit 가 존재하는 Select 구문에서 SubQuery 사용하는 경우 꼭 개선해야함
Index 를 실행하지 못하고 전체 데이터를 읽게 됨
--
limit 10 이 있는 경우 driving table에서 1건 조회 후 inner table에서 조회 이미 10개가 채워지면 다음을 진행하지 않음
explain에 type = all 로 되어있어도 limit 가 있으면 성능이 괜찮을 수도 있다.
--
group by 는 merge가 일어나지 않는다
show warings-> 실제 실행되는 쿼리를 보여줌 select * 라면 어떤 컬럼이 읽혔는지 등등
---
PK는 Clustered Index,
Lateral Join
--
운영 중인 서비스에서 index를 만들때 invisable로 만들고 사용하면 됨
--
형 변환 우선순위! 주의
--
window 함수의 장점은 group by 없이 count(*) 등 함수 사용할 수 있음 ex) count(*) over() as tot_cnt