쿼리 최적화

 

데이터 베이스에서 성능 최적화는 디스크 I/O과 관련이 많다. 즉, 성능개선을 한다고 하는 것은 디스크 I/O를 줄이는 것이 핵심이다. 

 

인덱스를 쓰면 조회는 빨라지지만 데이터 수정, 삭제, 생성은 느려진다는 말이 있다. 그럼에도 불구하고, 인덱스를 쓰는 것이 좋다. 일반적으로 웹서비스의 경우엔 CRUD에서 R(Read)과 CUD(Create, Update, Delete)의 비율이 8:2에서 9:1이기 때문이다. API 호출을 할때, get 요청을 하면 이게 전부 R 작업에 해당하는데, 보통 get 요청이 가장 많다. 

 

인덱스는 데이터베이스 테이블에 대한 검색 성능 속도를 높여주는 자료구조이다. 즉, 인덱스는 책에 있는 목차라고 생각하면 된다. 목차에서 원하는 주제를 찾고 페이지 번호를 찾아가듯이, 인덱스에서 내가 원하는 데이터를 먼저 찾고 저장되어 있는 물리적 주소로 찾아간다. 실제 DB관련 작업을 할 때 대부분의 속도 저하는 바로 SELECT문, 특히 조건 검색 WHERE절에서 발생한다. SQL튜닝에서는 인덱스와 관련된 문제사항과 해결책이 가장 많다.

 

인덱스는 키의 순서대로 정렬되어 저장되며, 검색 시에는 B-트리 구조를 사용하여 매우 빠르게 검색한다. 

 

B-트리구조

 

1. 인덱스(Index) 활용

예시 1: 인덱스의 사용

SELECT * FROM Orders WHERE CustomerID = 12345;

 

위 쿼리는 Orders 테이블에서 CustomerID가 12345인 모든 레코드를 검색한다. 만약 CustomerID에 인덱스가 없다면, 데이터베이스는 전체 테이블을 스캔하여 해당 조건을 만족하는 레코드를 찾는다. 이 경우, 테이블이 크다면 검색 시간이 길어진다. 하지만 CustomerID에 인덱스가 있다면, 데이터베이스는 인덱스를 사용해 검색 범위를 좁힐 수 있으며, 검색 속도가 훨씬 빨라지게 된다.

 

예시 2: 복합 인덱스(Composite Index)

SELECT * FROM Orders WHERE CustomerID = 12345 AND OrderDate = '2024-08-18';

 

이 쿼리에서는 CustomerID와 OrderDate 두 개의 조건을 사용한다. 이 두 컬럼에 대한 복합 인덱스를 생성하면 쿼리의 성능이 더욱 향상된다. 복합 인덱스는 여러 컬럼을 결합하여 인덱스를 만드는 것으로, 특히 두 개 이상의 조건을 사용하는 쿼리에서 유용하다.

 

2. 쿼리 구조 개선

예시 3: SELECT 절에서 불필요한 컬럼 제거

-- 최적화 전
SELECT * FROM Employees;

-- 최적화 후
SELECT EmployeeID, FirstName, LastName FROM Employees;

 

첫 번째 쿼리는 Employees 테이블의 모든 컬럼을 반환한다. 그러나 실제로 필요한 정보는 EmployeeID, FirstName, LastName 뿐이라면, 두 번째 쿼리처럼 필요한 컬럼만 선택하는 것이 좋다. 이는 데이터 전송량을 줄이고, 처리 속도를 개선한다.

 

예시 4: 서브쿼리(Subquery) 대신 JOIN 사용

-- 서브쿼리 사용
SELECT CustomerID, CustomerName 
FROM Customers 
WHERE CustomerID IN (SELECT CustomerID FROM Orders WHERE OrderDate = '2024-08-18');

-- JOIN 사용
SELECT DISTINCT c.CustomerID, c.CustomerName 
FROM Customers c
RIGHT JOIN Orders o ON c.CustomerID = o.CustomerID 
WHERE o.OrderDate = '2024-08-18';

 

서브쿼리를 사용하는 첫 번째 예시는 데이터베이스가 서브쿼리를 먼저 실행한 후 결과를 사용해 외부 쿼리를 실행해야 하기 때문에 비효율적일 수 있다. 반면에 JOIN을 사용한 두 번째 예시는 두 테이블을 직접 연결하여 데이터를 검색하므로 성능이 향상된다.

 

3. 쿼리 실행 계획 분석

예시 5: 실행 계획(Execution Plan) 확인

EXPLAIN SELECT * FROM Orders WHERE CustomerID = 12345;

 

EXPLAIN 명령어를 사용하면 데이터베이스가 쿼리를 어떻게 실행하는지에 대한 계획을 확인할 수 있다. 이 정보를 통해 쿼리가 테이블 스캔을 하는지, 인덱스를 사용하는지, 어떤 조인이 발생하는지 등을 파악할 수 있다. 이를 바탕으로 쿼리를 최적화할 수 있다.  EXPLAIN 함수를 사용했을 때의 결과 해석은 이 글을 참고하도록 하자.

 

 

4. 데이터 정규화 및 비정규화

예시 6: 데이터 비정규화(Denormalization)

정규화된 데이터베이스는 중복을 최소화하여 데이터 무결성을 유지하지만, 복잡한 조인 쿼리로 인해 성능 저하가 발생할 수 있다. 이럴 때 데이터 비정규화를 고려할 수 있다.

 

예를 들어, 자주 조회되는 Orders 테이블과 Customers 테이블의 조인을 피하기 위해 Orders 테이블에 CustomerName을 추가하는 방법이 있다. 이는 데이터 중복을 증가시킬 수 있지만, 조회 성능은 개선된다.

 

 

5. 이 외에 쿼리 최적화 방법들

1. SELECT 시에는 꼭 필요한 칼럼만 불러오자.

이유는 많은 필드 값을 불러올수록 DB는 더 많은 로드를 부담해야하기 때문이다.

-- X
select * from tabel;

-- O
select column1 from table;

 

6. 좌변을 연산하지 마라.

SELECT * FROM table WHERE YEAR(date) = 2024;

 

위 쿼리와 같이 데이터 원본을 변형하여, 내가 찾고자 하는 범위와 비교하는 연산은 데이터베이스가 인덱스를 제대로 활용할 수 없게 만든다. 마치 책의 페이지 번호를 함수 연산하여 계산하며 찾는 것과 같은 방식이다.

 

인덱스는 원본 데이터를 그대로 가지고 만들어진다. 예를들어 date 칼럼에 대한 인덱스는 '2024-07-09'와 같은 날짜 값 자체를 가지고 구성된다. 

 

그래서 YEAR(date) = 2024왁 같이 데이터를 변형하여 연상을 수행하면, 모든 행을 전부 일일이 연산을 수행하고, 그 결과가 2024인지 확인하는 과정을 거쳐야한다. 결국 모든 데이터를 처음부터 끝까지 훑어봐야한다.

 

여기에 대한 대안은 아래와 같이 우변을 활용하는 것이다.

SELECT * FROM table
WHERE date between '2024-01-01' and '2025-01-01';

 

 

7. like 함수의 경우 와일드 카드 문자열(%)를 String 앞부분에는 배치하지 말자.

Index를 활용할 수 있는 column1 in (...), column1 = "...", column1 like "...%"는 인덱스를 활용할 수 지만, column1 like "%..."는 Full Table Scan을 활용한다. 

-- X
select column1
from table
where column1 like "%cde";

-- O
select column1
from table
where column1 in ('abcde', 'cde');

 

 

8. 중복을 제거하는 distinct 연산은 최대한 사용하지 않는다.

중복 값을 제거하는 연산은 시간이 많이 걸린다. 이를 대체하는 방법은로 exists를 활용하는 방법이 있다.

 

9. 같은 내용의 조건이라면, group by 연산시에는 having 보다는 where 절을 사용하는 것이 좋다.

쿼리 실행 순서에서 where절이 having절보다 먼저 시작이 되기 때문에 where 절로 미리 데이터 크기를 작게 만들면, group by 할 데이터 크기가 작아지기 때문에 더 효율적이다.

 

10. 3개 이상의 테이블을 inner join 할 경우에는 크기가 가장 큰 테이블을 from 절에 배치하고, inner join 절에는 남은 테이블을 작은 순서대로 배치하는 것이 좋다.

inner join 과정에서 최소한의 combination을 탐색하도록 해준다. 

간단한 

 

11. OR 대신 UNION을, UNION보다는 UNION ALL을 활용하라.

인덱스는 단일 값에 대한 빠른 검색을 위해 최적화되어 있는데, OR은 여러 값을 동시에 찾아야하기 때문에 결국 전체 데이터를 모두 뒤져야하는 상황이 벌어진다.

 

UNION을 사용하면, 단일 값을 각각 찾고, 데이터를 합쳐주기 연산을 하기 때문에 인덱스를 잘 활용할 수 있다.

 

만약 중복이 없다는 게 확실하다면 UNION ALL을 사용해 중복 제거 단계를 건너 뛰기 때문에 더 속도를 높일 수 있다.

 

12. NULL 값을 비교하지 않을 수 있으면, 하지않는 것이 좋다.

NULL값을 비교하면 데이터베이스는 인덱스를 활용할 수 없다. 이는 NULL 값은 인덱스에 저장되지 않기 때문이다. 

 

13. IN 연산자를 사용한 검색에서 IN 목록의 개수가 많은 경우.

이 경우 인덱스를 사용하지 않고 수행할 가능성이 높다. 아래 첫번째 쿼리보다는 두번째 쿼리를 사용하는 것이 성능 향상에 도움이 된다.

 

SELECT *
  FROM table_name
 WHERE id IN (1, 2, 3, ..., 1000);

 

-- 개선된 쿼리
SELECT *
  FROM table_name
  JOIN (
          SELECT 1 AS id UNION ALL
          SELECT 2 AS id UNION ALL
          SELECT 3 AS id UNION ALL
          ...
          SELECT 1000 AS id
        ) AS ids 
    ON table_name.id = ids.id;

 

14. 분석 함수를 활용하기

대표적인 분석 함수로는 ROW_NUMBER(), RANK(), DENSE_RANK(), LEAD(), LAG() 등이 있다. 이런 함수들은 복잡한 데이터 집합 내에서 각 row별로 세부적인 계산을 가능하게 한다.

 

먼저, 집계 함수와 달리 사전에 데이터를 그룹화할 필요가 없다. 또한, 복잡한 데이터 분석 과정에서 발생할 수 있는 중간 결과물의 저장과 재처리를 최소화할 수 있다.

 

LEAD()와 LAG()는 현재 row와 관련하여 이전 또는 다음 row의 데이터를 참조할 수 있게 해준다. 이를 활용하면 예를들어 각 직원의 연봉 변화율을 쉽게 계산할 수 있다. 

 

https://community.heartcount.io/ko/query-optimization-tips/

 

 

15. 전체 스캔을 피하자.(Rowid로 조회)

전체 테이블 스캔을 피하기 위한 한 가지 기술은 rowid(또는 동등한  INTEGER PRIMARY KEY)로 조회하는 것이다. 

테이블에 N개의 요소가 있는 경우 원하는 행을 찾는데 필요한 시간은 전체 테이블 스캔에서 N에 비례하는 것이 아니라 logN에 비례한다. 테이블에 1,000만개 요소가 있는 경우 쿼리는 N/logN으로 계산되어 약 100만 배 더 빠르게 된다.

 

16. 인덱스로 조회

현실적으로 rowid로 조회하는 것이 힘들기 때문에 기존 테이블에서 서칭하려는 열에 인덱스를 추가할 수 있다.