SQL 쿼리 성능 향상 방법(1/2)
1. 전체범위 처리 & 부분범위 처리
전체범위 혹은 부분범위 처리라는 단어를 들어본 적이 있는가? 퀴즈를 한 번 내보겠다.
SELECT * FROM 테이블명(WHERE절 X)위의 SQL문은 전체범위일까? 부분범위일까?
정답은 부분범위이다.
테이블 전체를 다 읽으니까 대다수 사람들은 전체범위로 생각한다.
그러나 데이터를 처리하는 입장에서 테이블을 끝까지 가서 화면에 보여줄 필요가 없이, 테이블에 들어가 있는 데이터를 그대로 보여주기 때문에 부분범위 처리가 된다.
SELECT를 할 때, SQL 데이터는 한 번에 다 가져가서 보여주는 게 아니라 Array size로 차곡차곡 상자 같은 곳에 담아서 화면에 출력이 된다. 전체 범위 처리는 전체 데이터를 다 읽고, 화면에 보여준다. 반면 부분 범위 처리는 내가 원하는 부분만 읽고 보여준다.
예시로 ORDER BY를 사용했으면 그냥 보여줄 필요가 없기에 정렬을 한 번 한 다음에 그 결과를 보여줘야한다. 이외에도 COUNT 그룹 함수 같은 가공이 한번 일어나는 것들이 있으면 그게 전체범위 처리이다.
반면 WHERE 조건에 쓰인 키워드가 INDEX를 타서 그 부분만 읽는 다던지, 데이터의 가공없이 바로 바로 보여주는 상태가 부분범위 처리이다.
이 둘을 쉽게 판별하는 방법은 툴에서 쿼리를 실행했을 때, 한참 있다가 출력이 되면 전체 범위라 생각하면 된다. 반면 치자마자 바로 나왔다면 부분 범위 처리일 가능성이 크다.
쉽게 말해 데이터를 퍼올려서 바로 보여줄 수 있으면 부분범위 처리라고 판단하면 된다. 이 같은 개념을 알 수 있다면 ¹옵티마이저가 처리하는 방식에 대해 이해하는 기초가 된다.
(1) SQL을 가장 빠르고 효율적으로 수행할 최적의 처리경로를 생성해 주는 DBMS 내부의 핵심 엔진
2. 성능이 좋은 SQL문 작성 팁
2.1) NOT 같은 부정 조건 가급적 쓰지 않기
부정 조건은 전체 범위 처리이다. 보통 부정 조건에 포함되는 걸 보면 NULL을 사용한다. NULL은 값이 없는 것인데, 이걸 DB가 찾으려고 하기 때문에 FULL SCAN을 하게 된다.
예시로 법전 책에서 'ㄱ'이 아닌 걸 찾으라고 하면 처음부터 끝까지 봐야 한다. 반면 가나다에 해당하는 '가정법' 같은 걸 찾고 싶다고 한다면 뒤에 인덱스 색인으로 가정 관련된 것에 가서 그 페이지만 읽으면 된다.
그러므로 '아니다'라는 조건이 있다면 그걸 맞다고 조건을 줄 수 있는 걸 찾아야 한다. 예시로 '2023년 3월 22일'이 아닌 것을 찾기 보다는 '3월 22일'이라는 날짜 조건을 빼놓고 나머지 것을 IN 처리해서 넘기다던지, 부정이라 생각하는 걸 긍정으로 생각해야 한다.
2.2) DISTINCT
DISINCT도 전체 범위 처리이다. 보통 JOIN을 하면 중복돼서 'DISTINCT'를 쓰는 사람이 많다. 그런데 DISTINCT가 들어갔다는 것 자체라 테이블 설계가 잘못됐거나, 테이블의 데이터를 잘못 이해하고 쿼리를 짤 가능성이 크다.
DISTINCT는 중복되는 걸 제거해야하기 때문에. 유지보수하는 입장에서 봤을 때, 데이터를 확실히 알 수 없는 상황이기에 빼지도 못하고, 그대로 두면 효율이 떨어진다.
이렇게 'order by'나, 'distinct' 등 그룹 함수를 최소화할 수 있다면 그런 방향으로 쿼리를 짜는 게 좋다. 물론 이렇게 짜려면 머리가 아플 수가 있다🤣
3. 인덱스 적용
3.1. 정의
인덱스는 색인을 뜻한다. 색인은 책의 색인처럼 쉽게 찾을 수 있도록 일정한 순서로 모아놓은 목록을 뜻한다.
이를 DB에도 적용하면 테이블에 적절한 인덱스를 생성하여 쿼리의 검색 속도를 향상시킬 수 있다. 보통 쿼리의 WHERE 절에서 자주 사용되는 컬럼에 인덱스를 생성하는 것이 일반적이다.
SQL문을 예시로 만약 name에 인덱스를 설정했다면
select * from user where name='김팔순'인덱스가 적용된 대상을 where 절을 통해 검색한다.
반면
select * from user위의 SQL문은 인덱스가 사용되지 않는다. where 절을 통해 name을 검색하지 않았기 때문이다.
3.2. 알고리즘
이진 트리를 했을 때, 시간 복잡도는 O(log n)이 나온다. 그러나 최악의 케이스인 경우 오른쪽의 그림처럼 O(n)이 나올 수가 있다. 이런 이진 트리의 단점을 극복하기 위해서 그 중 B-Tree 알고리즘이 나온다.
B-Tree는 트리 높이가 같고, 자식 노드를 2개 이상 가질 수 있으며 보통 기본 DB 인덱스 구조에서 사용된다.
B 트리는 위의 사진처럼 각각의 데이터를 나눠서 탐색 범위를 줄이게 한다.
실제로 숫자 61을 찾고자 한다면 루트 페이지와 리프 페이지 총 2개의 페이지가 있고, 4번의 검색을 통해 단번에 찾을 수 있다.
이를 통해 인덱스로 SELECT 성능이 향상된 걸 확인할 수 있다. 그러나 INSERT, UPDATE, DELETE는 어떻게 될까?
만약 데이터를 INSERT 한다고 가정해보면 비어 있는 1) 페이지를 확보해야 한다. 그리고 2) 문제가 있는 페이지의 데이터를 공평하게 나누어 저장해야 한다. 이는 DB에 부담이 되는 작업이고, 이를 페이지 분할이라고 한다.
페이지 분할이란 페이지에 새로운 데이터를 추가할 여유 공간이 없어 페이지에 변화가 발생한 걸 뜻한다. 이러면 DB 성능이 느려지고, 성능에 영향을 준다.
그러면 DELETE는 어떻게 할까? 인덱스의 데이터를 실제로 지우지 않고, 사용안함 표시를 한다. UPDATE는 먼저 DELETE처럼 사용안함 표시를 하고, 그다음에 INSERT처럼 변경된 값을 사용한다.
정리하면 인덱스를 사용하면 SELECT로 인한 성능이 향상되지만, INSERT/ UPDATE/ DELETE는 페이지 분할과 사용안함으로 인덱스의 조각화가 심해져 성능이 저하된다.
그래서 삽입과 수정 삭제가 많이 이용되는 도메인에는 인덱스를 사용하지 않는 편이다. 보통 읽기와 쓰기 비율이 8:2인 이하인 경우에는 자주 사용한다고 한다.
3.3. 복합인덱스(Composite Index)
두 개 이상의 칼럼을 합쳐서 인덱스를 만드는 것을 뜻한다. 하나의 인덱스보다 더 적은 데이터 분포를 보여줘서 탐색할 데이터 수가 줄어든다.
보통
select * from A where age <= 26 and name='철수'위의 SQL문처럼 age와 name의 조건을 함께 탐색할 때 사용한다.
복합인덱스를 사용할 때, 칼럼들의 배열 순서는 매우 중요하다. 컬럼의 순서를 잘못 배열하면 결합 인덱스의 실행 확률이 매우 낮아질 수도 있기 때문이다. 따라서 결합 인덱스를 사용하려면 반드시 결합 인덱스의 컬럼 중 선행하는 컬럼부터 조건에 지정해야 한다.
여러 블로그 글에서 카디널리티가 높은 칼럼이 처리 범위를 줄여서 선행 칼럼으로 해야한다는 글들이 많은데, 사실이 아니다. 인덱스의 컬럼이 '='일 경우에 어느 칼럼이 먼저오든 블록의 I/O 개수가 많으므로 성능도 같다. 예시로 [나이+이름] 인덱스와 [이름+나이] 인덱스는 블록 I/O 개수가 같다.
따라서 어느 컬럼을 앞에 둬도 차이가 없다. (참고자료 글)
3.4. 적용
보통 아래의 조건일 때 많이 사용된다.
- 카디널리티(요소의 개수)가 높은 곳 = 중복도가 낮은 곳
- Where, Join, Order by 절에 자주 사용되는 칼럼(조건 절이 없으면 인덱스가 사용되지 않는다.)
- 삽입, 삭제, 변경이 자주 발생하지 않는 칼럼
- 규모가 적지 않은 테이블
인덱스를 사용할 때 아래의 주의사항들을 고려햐야 한다.
- 잘 활용되지 않는 인덱스
- 불필요한 인덱스로 성능저하가 올 수 있다.
- 데이터 중복도가 높은 칼럼은 인덱스 효과가 적다.
- 자주 사용되도 삽입, 삭제, 수정이 자주 일어나는지 고려
- 일반적인 웹 서비스의 경우 쓰기, 읽기 비율은 2:8 혹은 1:9이다.
- 조금 느린 쓰기를 감수하고 빠른 읽기를 선택하는 것도 하나의 방법이다.
4. 캐시 활용
추후 Redis 관련 포스팅을 올리겠다.
5. 쿼리 튜닝 시 검증 팁
쿼리를 튜닝할 때, 진짜 빼도 되야하나 고민할 때가 많다. 최적화 이후에 원래 쿼리처럼 결과물을 다 끄집어 낼 수 있을까 고민이 돼서, 제대로 된 검증을 하기가 매우 힘들다.
DB 전문가 정재욱님은 쿼리 튜닝을 하면 이렇게 된다라는 답이 정해진 상태에서 튜닝을 한다고 한다... 절대 안 틀어진다는 걸 아는 이유가, 쿼리 튜닝 이전에 업무를 먼저 파악. 업무에서 이 데이터가 단건으로 들어온다? 업무의 규칙만 정확히 안다면 얘는 중복이 일어나는지, 아니면 중복이 없는 데이터인지 인지한다고 한다.
업무에 대해 먼저 접근을 한다면, SQL만 보고 짜는 것보다는 절대로 틀릴 일이 없다고 한다. 왜냐하면 고객이 요구하는 쿼리를 짜야하기 때문이다.
테이블 설계가 좀 잘못돼서 DISTINCT가 들어가더라도 결과가 정확히 나온다면, 아무리 튜닝을 해도 그 상황을 고칠 수 없는 상황이라면 그때는 그냥 어쩔 수 없이 둬야한다. 업무만 정확히 파악한다면 N건으로 들어오는 건지, 일대일로 처리되는 건지 알 수 있기에 쿼리를 짠 것에 의심을 하지 않는 경지에 도달한다고 한다.
즉, 나무를 보지 말고 숲을 봐야한다.
다음 2편에서는 조인 최적화와 서브쿼리 최적화, 테이블 파티셔닝에 대해 알아보겠다.