Part 5-3-6. SQL 튜닝 : 인덱스 컬럼 순서

 

 

🌞 SQL 튜닝

 

 지난 시간에는 북마크 룩업이라는 개념에 대해서 알아보고, 이를 어떻게 줄이면 좋을지에 대한 다양한 방법이 대해서 알아보았다. 이번 시간에는 이전 시간에 학습한 Covered Index와 같이 복합 인덱스를 사용할 때 인덱스의 컬럼 순서를 어떻게 설정하냐에 따른 실행 속도에 대해서 알아보는 시간을 가져보도록 하자. 😎 

 

 마찬가지로 이번 시간에서도 저번 시간과 마찬가지로 BaseballData 데이터베이스를 사용하는 것이 아닌, Northwind 데이터베이스를 사용할 것이다. 

 

---

 

🙋‍♀️ 복습

 

 먼저 오늘도 인덱스 컬럼 순서에 대해서 알아보기 전에 이전에 학습한 것을 짧게 복습해보자.

 

-- 논클러스터드
--   1
-- 2 3 4

-- 클러스터드
--   1
-- 2 3 4

-- Heap Table [ {Page}, {Page} ]

 

 

 이전과 같이 간단하게 논클러스터드와 클러스터드의 Leaf Page를 위와 같이 구성했다고 예를 들자.  이전 시간에 우리는 북마크 룩업을 어떻게 최소화할까에 대해서 학습했다. 그러나 과연 '북마크 룩업을 최소화하는 것이 최적화의 끝인가?'라고 묻는다면 "그걸로는 끝이 아니다." 라고 대답할 수 있다. 왜냐하면 아직도 데이터를 조회할 때에 Leaf Page에 대한 탐색은 여전히 존재하기 때문이다.

 

 예를 들어보자. 우리가 어떤 데이터베이스에서 [레벨, 종족]에 대해서 검색을 하기 위해 복합 인덱스를 걸었고 이를 통해 (56, 휴먼)을 찾는다고 가정해보자.

 

 클러스터드 인덱스를 먼저 기준으로 찾을 경우 56, 휴먼이 딱 하나만 존재한다는 보장이 없다.

-- 클러스터드
--   1
-- 2 3 4[(56 휴먼) (56 휴먼) (56 휴먼) (56 휴먼) (56 휴먼)...]

 

 

 따라서 위와 같이 4번 Leaf Page에서 (56, 휴먼)이 아닐때까지 4번 페이지의 내용을 모두 스캔을 처리해서 조사해야 한다. 따라서 인덱스를 사용했음에도 불구하고 Leaf Page에 접근해 페이지를 스캔해야 하는 필요성이 생긴다. 보다 극단적인 예로는 (56~60, 휴먼)을 찾는다고 할 경우 테이블 스캔의 범위는 지금보다 훨씬 더 넓어진다는 문제가 생긴다는 것이다. 따라서 예제와 같이 설정한 인덱스의 순서가 테이블을 조회할 때 굉장히 큰 영향을 주는 것을 알 수 있다.

 

---

 

🙋‍♀️ 인덱스 컬럼 순서

 

 늘 그렇듯이 기존에 사용하던 테이블을 삭제하고, 새롭게 만들어주자. 오늘도 마찬가지로 Select Into를 이용하여 테이블을 생성할 것이다.

-- 테이블 생성
SELECT *
INTO TestOrders
FROM Orders;

-- 데이터 조회
SELECT *
FROM TestOrders;
-- 830개의 행이 나타나는 것을 볼 수 있다.

 

 오늘은 더미 데이터를 통해 테이블의 양을 늘려주는 작업이 필요하다. 더미 데이터를 생성하자.

-- 더미 데이터 생성
DECLARE @i INT = 1;
DECLARE @emp INT;
SELECT @emp = MAX(EmployeeID) FROM Orders;

-- 반복문을 통한 데이터 삽입
WHILE (@i < 1000)
BEGIN
	INSERT INTO TestOrders(CustomerID, EmployeeID, OrderDate)
	SELECT CustomerID, @emp + @i, OrderDate
	FROM Orders;
	SET @i = @i + 1;
END

-- 데이터 조회
SELECT COUNT(*)
FROM TestOrders;
-- 830 * 1000으로 인한 83만개의 데이터가 생긴것을 볼 수 있다.

 

 이제 오늘의 주인공인 복합 인덱스를 선 EmployeeID, 후 OrderDate 인덱스와 선 OrderDate, 후 EmployeeID를 가지는 인덱스를 2개 생성 후, 어느 것이 더 빠를지 테스트해보자.

 

-- 인덱스 생성
CREATE NONCLUSTERED INDEX idx_emp_ord
ON TestOrders(EmployeeID, OrderDate);

CREATE NONCLUSTERED INDEX idx_ord_emp
ON TestOrders(OrderDate, EmployeeID);

-- 과연 어느쪽이 더 빠를까? 이를 테스트 해보자.
SET STATISTICS TIME ON;
SET STATISTICS IO ON;

 

 이제 인덱스를 생성해주었으니, 테이블을 조회해보자. 단 이때 인덱스를 강제로 지정하지 않으면 데이터베이스가 알아서 최적의 결과를 반환할테니 인덱스를 강제로 지정하여 두 개를 비교하자.

 

-- 인덱스를 강제로 지정하여 두 개를 비교하자.
SELECT *
FROM TestOrders WITH(INDEX(idx_emp_ord))
WHERE EmployeeID = 1 AND OrderDate = CONVERT(DATETIME, '19970101');

SELECT *
FROM TestOrders WITH(INDEX(idx_ord_emp))
WHERE EmployeeID = 1 AND OrderDate = CONVERT(DATETIME, '19970101');

두 SELECT 모두 동일한 논리적 읽기를 나타낸다.

 

 띠용~ 논리적 읽기와 실행 계획이 모두 동일하게 나와버렸다. 뭐가 더 좋은지 알아보려고 했는데 둘 다 논리적 읽기와 실행계획이 같으니 살짝 허무하다. 그렇다면 왜 이게 같이 나왔는지 알아보도록 하자.

 

-- 직접 살펴보자
SELECT *
FROM TestOrders
ORDER BY EmployeeID, OrderDate;

 

 먼저 EmployeeID와 OrderDate로 정렬했을 때 나타나는 결과에 대해서 알아보자. 그러면 EmployeeID가 1인 값들이 먼저 나오고, 그 이후에 OrderDate가 정렬된 모습을 볼 수 있다. 비록 우리는 수동으로 EmployeeID가 1인 값과 OrderDate가 '970101'인 값을 찾았지만 실제로는 인덱스를 통해 한 번에 찾을 수 있게된다. 그리고 이미 모든 데이터가 정렬이 되어있기 때문에 '970106'이 된 순간 비교를 중지하고 값을 찾아낼 것이다.

 

-- 반대로 살펴보자
SELECT *
FROM TestOrders
ORDER BY OrderDate, EmployeeID;

 

 이제 반대로 OrderDate와  EmployeeID로 정렬했을 때 나타나는 결과에 대해서 알아보자. 사실 여기도 크게 다르지 않다. 단지 OrderDate로 데이터를 조회할 때에는 EmployeeID가 1인 것을 먼저 찾기 힘들다. 왜냐하면 정렬 자체가 OrderDate로 되어 있기 때문이다. 따라서 OrderDate가 970101인 친구를 먼저 찾고, EmployeeID를 찾아주면 된다. 따라서 두 케이스 모두 (EmployeeID, OrderDate), (OrderDate, EmployeeID) 인덱스를 사용하더라도 비슷한 성능을 낼 수 있다.

 

 하지만 문제는 여기서 끝나는게 아니다. 만약 특정 날짜를 지정해서 검색하는 것이 아닌 '범위'형태로 검색 할 경우 무슨일이 일어날까? 실제로 범위로 조회하는 예로는 '이벤트를 위해 7월 1일부터 7월 8일까지 일주일간 접속한 유저들에게 아이템을 준다고 할 경우' 범위를 이용해 스캔해야 한다. 따라서 이는 매우 중요한 주제이기도 하다.

 

---

 

 이제 범위를 설정해서 테이블을 조회해보자.

SELECT *
FROM TestOrders WITH(INDEX(idx_emp_ord))
WHERE EmployeeID = 1 AND OrderDate BETWEEN '19970101' AND '19970103';
-- 논리적 읽기 5

SELECT *
FROM TestOrders WITH(INDEX(idx_ord_emp))
WHERE EmployeeID = 1 AND OrderDate BETWEEN '19970101' AND '19970103';
-- 논리적 읽기 16

 

 실행 결과는 이전과 같으나, 논리적 읽기가 5, 16으로 3배 차이가 나는 것을 알 수 있다. 그러면 아까와 결과가 똑같았는데 이번에는 왜 논리적 읽기의 수가 다를까? 마찬가지로 이를 알아보기 위해서 다시 또 직접 알아보는 시간을 가지자.

 

-- 직접 살펴보자.
SELECT *
FROM TestOrders
ORDER BY EmployeeID, OrderDate;

-- 반대로 바꿔서 살펴보자.
SELECT *
FROM TestOrders
ORDER BY OrderDate, EmployeeID;

 

 이 경우 나타나는 결과 프로세스는 위에 서술한 것과 동일한 내용이기에 따로 생략한다. 요약하자면 (OrderDate, EmployeeID) 인덱스로 스캔 할 때에는 OrderDate 순으로 정렬이 되어 있기 때문에 97년 1월 1일부터 3일까지 모든 정보를 확인하고 EmployeeID를 스캔해야 하기 때문에 보다 더 오래 걸리는 것을 알 수 있다.

 

---

 

🙋‍♀️ 중간 결론

 

 따라서 Index(a, b, c)로 구성되었을 때, 선행에 between을 키워드를 사용할 경우 후행은 인덱스 기능을 활용하지 못한다. 그러므로 between을 사용할 경우에는 후행에다가 걸어주도록 인덱스를 잘 배치해야 한다.

 

 '그렇다면 between와 같은 비교가 등장하면 인덱스 순서만 바꿔주면 되는 것일까?' 이 또한 당연히 'NO'이다. 왜냐하면 SQL을 작성할 때 단순히 하나의 조건만으로는 사용하진 않을 것이다. 따라서 다양한 구문을 이용해 테이블을 활용할 것이기에 단순히 '하나의 케이스'만 놓고 인덱스를 추가하고 수정하는 것은 위험할 수 있다. 이에 따라 모든 케이스를 다 보려하고 다른 쿼리에도 영향을 줄지를 같이 생각해서 종합적으로 결정지어야 한다.

 

---

 

🙋‍♀️ IN-LIST를 활용한 인덱스 컬럼 순서

 

 그렇다면 여기서 작은 팁. BETWEEN의 범위가 작을 경우 IN-LIST를 사용하여 대체하는 것을 고려하자.

 

SET STATISTICS PROFILE ON;

-- IN 키워드를 통한 테이블 조회
SELECT *
FROM TestOrders WITH(INDEX(idx_ord_emp))
WHERE EmployeeID = 1 AND OrderDate IN ('19970101', '19970102', '19970103');
-- 논리적 읽기가 16에서 11로 줄어든 것을 볼 수 있다.

 

 위 결과로 논리적 읽기가 16에서 11로 줄어든 것을 볼 수 있다. SET STATISTICS PROFILE ON를 통해 실질적으로 내부 로직이 돌아가는 것을 확인해 보면 OR문을 통해 데이터를 찾는 것을 볼 수 있다. 따라서 970101로 한번, 970102로 한번, 970103으로 한번 조회한다. 사실상 여러번 '='를 사용하는 것이다.

 

 물론 그렇다고 해서 BETWEEN을 항상 IN-LIST로 바꾸는것은 옳지 않다. 기존에 EmployeeID와 OrderDate로 조회하는 SELECT문을 살펴보자.

 

SELECT *
FROM TestOrders WITH(INDEX(idx_emp_ord))
WHERE EmployeeID = 1 AND OrderDate IN ('19970101', '19970102', '19970103');

 

 이 경우, 논리적 읽기가 5가 아닌 11로 오히려 늘어버렸다. 따라서 오히려 성능이 더 떨어진 것을 알 수 있다. 그러므로 무작정 IN-LIST를 사용하는게 좋은 것은 아니다. 중간 결론에서 얘기한 것과 같이 Index(a, b, c)로 구성되었을 때, 선행에 between을 사용할 경우 후행은 인덱스 기능을 활용하지 못한다.'와 같은 상황에서 고려해야 하는 것이다.

 

---

 

🙋‍♀️ 진짜 결론

 

 1. 복합 컬럼 인덱스를 만들 때 (선행, 후행) 순서가 인덱스의 영향을 줄 수 있다.
 2. BETWEEN, 부등호(>, <)가 선행에 들어가면, 후행은 인덱스의 기능을 상실한다.
 3. BETWEEN 범위가 적으면 IN-LIST로 대체하면 좋은 경우도 있다. (선행에 BETWEEN이 들어갈 경우)
 4. 만약 선행이 (=)이고, 후행이 BETWEEN이라면 아무런 문제가 없기 때문에 IN-LIST가 꼭 좋은 것은 아니다.