[SQL] Index 를 이용해서 효율적인 쿼리 작성하기
by 소라고동_이번 포스팅에서는 SQL쿼리 작성을 할 때 성능을 높혀줄 수 있는 Index 에 대해 이야기를 해보려 합니다.
어떻게 Index 를 설정하는지에 대한 내용은 담지 않을 예정이며, Index 를 활용하면 어떤 부분이 좋은지, 어떻게 활용하면 되는지 등에 대한 활용에 대한 내용만 담을 예정입니다.
0. 인덱스(Index) 란?
우선 이해를 돕기 위해 인덱스의 사전적 정의에 대해 살펴봅시다.
인덱스 (index)
- 책 속의 내용 중에서 중요한 단어나 항목, 인명 따위를 쉽게 찾아볼 수 있도록 일정한 순서에 따라 별도로 배열하여 놓은 목록
(출처 : 네이버 어학사전)
위 정의를 더 간단하게 이야기를 해보면 '인덱스는 하나의 책갈피이다.' 라고 생각해볼 수 있습니다.
우리가 찾으려는 내용을 보다 쉽고 빠르게 찾아낼 수 있도록 도와주는 역할을 하는 것이죠.
여기서 한번 더 생각을 해보면 위 문장을 이렇게 풀어볼 수 있습니다.
우리가 찾으려는 내용을 보다 쉽고 빠르게 찾아낼 수 있도록 도와주는 역할
= SQL 에서 select 문을 통해 추출하려는 데이터를 성능이 좋은 방식으로 추출할 수 있도록 도와주는 역할
즉, SQL 을 활용해 효율적으로 쿼리를 작성할 때 고려해야할 사항 중 하나라고 볼 수 있다는 것이죠.
그럼 어떻게 Index 가 작동하길래 이렇게 성능에 영향을 미치는것일까요?
Index 의 원리와 작동 방식에 대해 먼저 간단하게 살펴보고 넘어가도록 합시다.
1. 인덱스(Index)의 구조와 작동 원리
먼저 인덱스가 어떻게 이루어져있는지에 대해 알아봅시다.
인덱스는 어려 자료구조를 통해 구현할 수 있는데, 대표적으로 해시 테이블(Hash Table)과 B-Tree, B+Tree 가 있습니다.
각각의 자료 구조를 살펴보면 다음과 같습니다.
1.1. 해시 테이블 (Hash Table)
해시 테이블은 Key 값에 Data(Value) 를 한 쌍으로 데이터를 저장하는 자료구조입니다.
python 에서의 Dictionary 를 생각하면 편한데, 하나의 Key 값에 대응하는 Value 가 존재하고 이러한 Key-Value 로 구성되어 있는 자료구조를 활용하여 원하는 값을 빠르게 찾아낼 수 있게 도와줍니다.
해시 테이블 (Hash Table)
장점 : 테이블에 존재하는 전체 값을 살펴볼 필요 없이, 찾으려는 값을 아주 빠르게 바로 찾아낼 수 있다
단점 : 해시테이블 내의 데이터들은 정렬되어있지 않다.
즉, 해시 테이블은 동등 연산(=)에 대해 최적화되어 있고, SQL에서 자주 사용되는 부등호 연산(<, >)에는 적합하지 않다.
왜냐하면 정렬되어있지 않기 때문에 특정 기준보다 크거나 작은 값을 빠르게 찾아낼 수 없기 때문이다.
위에서 살펴봤듯 해시테이블은 빠르다는 장점이 있지만, 동등 연산에 대해 최적화되어있기 때문에 인덱스로 잘 사용되지는 않습니다.
해시 테이블에 대해서는 아래의 링크에서 보다 자세히 살펴볼 수 있습니다.
해시 테이블(Hash Table)
개요 해시 테이블(Hash Table)은 많은 양의 데이터의 인덱싱(indexing)을 빠르게 해 줌으로써 탐색(Search), 삽입(Insert), 삭제(Delete)의 속도를 높여주는 자료구조다. 그렇기 때문에 해시 테이블은 Database i
ferrante.tistory.com
1.2. B-Tree
B-Tree 는 우리가 흔히 알고 있는 Tree 형태의 자료 구조를 이야기합니다.
그리고 B-Tree 는 탐색 성능을 높이기 위해 균형 있게 Tree 의 Depth 를 유지해주는 Balanced Tree 의 일종입니다.
즉, 알아서 모든 node 에 대한 Depth 가 같은 레벨로 유지되도록 자동으로 밸런스를 맞춰줍니다.
이 Tree 구조에 들어있는 모든 Key 값들은 정렬이 되어있으며, 정렬된 Key 값을 활용하여 인덱싱을 하게 됩니다.
아래의 그림을 통해 살펴보면 보다 쉽게 이해할 수 있습니다.
위의 그림처럼 B-Tree 구조에서는 Key 값들이 정렬되어있고 이런 특성을 이용하여 우리가 찾으려는 값을 빠르게 찾을 수 있도록 합니다.
자세한 흐름은 아래의 링크에서 확인할 수 있습니다.
[DB] 10. B-Tree (B-트리)
[목차] 1. B-Tree란? 2. B-Tree의 key 검색 3. B-Tree의 key 삽입 4. B-Tree의 key 삭제 참고) emplam27.log 블로그 https://hyungjoon6876.github.io/jlog/2018/07/20/btree.html https://helloinyong.tistory.co..
rebro.kr
이렇게 B-Tree 자료구조를 활용한 인덱스를 사용하면 전체 값을 살펴볼 필요 없이 정렬된 Key 값에 따라 필요한 값을 빠르게 추출할 수 있게 됩니다.
하지만 인덱스에 주로 활용되는 자료구조는 B-Tree 가 아닌, 이를 개선한 B+Tree 구조라고 합니다.
그러니 마지막으로 B+Tree 구조를 살펴보겠습니다.
1.3. B+Tree
B+Tree 구조는 B-Tree 구조와 거의 비슷하지만 두 가지의 차이점을 가지고 있습니다.
B-Tree 와 B+Tree 의 차이점
1. B-Tree 는 모든 node 에 데이터를 저장하고 있지만, B+Tree 는 마지막 node (= Leaf node) 에만 데이터를 저장한다.
2. B-Tree 와는 다르게 B+Tree 는 Leaf node 들이 연결되어 있다.
이를 그림으로 나타내면 아래와 같이 나타낼 수 있습니다.
이러한 차이점을 가지고 있는데, 이로부터 생겨나는 장단점은 아래와 같습니다.
B+Tree의 장점
1. leaf node 에만 데이터를 저장하기 때문에 메모리를 더 확보할 수 있다.
따라서 더 많은 포인터를 가질 수 있게 되고, 이로부터 Tree 의 높이가 낮아지므로 검색 속도가 빨라진다.
2. Full scan 을 해야할 경우 B-Tree 는 모든 node 를 확인해야하지만, B+Tree 는 특정 포인트 이후의 Leaf node 만 확인하면 된다.
B+Tree의 단점
1. B-Tree 는 상위 node 에서 key 를 찾아 빠르게 인덱싱을 마칠 수 있지만, B+Tree 는 반드시 Leaf node 까지 내려가야 한다.
이러한 장단점을 가지고 있는 B+Tree 를 인덱스로 주로 활용하는 이유는 SQL 에서는 부등호 연산이 많이 발생하기 때문입니다.
B+Tree 는 Leaf node 에 모든 데이터가 저장되어있고 각각의 Leaf node 가 연결되어 있기 때문에 순차적인 연산이 아주 편리합니다.
즉, 부등호 연산이 이루어 질 때 보다 효율적으로 인덱싱을 진행할 수 있다는 의미입니다.
정리를 해보면 SQL에서 인덱싱을 할 땐, 주로 B+Tree 구조를 활용한 인덱스를 이용해서 인덱싱을 하게 됩니다.
이 내용을 간단히 풀어 말하면 Tree 구조를 가지고 있는 정렬된 Key 값으로부터 필요한 값에 빠르게 접근하여 추출할 수 있도록 인덱스가 형성되어 있다고 이야기할 수 있습니다.
2. 인덱스(Index) 활용하기
이렇게 인덱스가 어떤 구조로 이루어져있고 어떤 방식으로 값을 찾아주는지에 대해 살펴봤는데요.
그렇다면 인덱스를 어떻게 SQL 에서 활용할 수 있는지 알아봅시다.
2.1. SQL 에서 인덱스 활용하기
SQL에서 인덱스를 활용하는 가장 기본적인 방법은 where 절에 인덱스가 설정되어 있는 컬럼을 사용하는 것입니다.
최근에 회사에서 겪은 상황을 예시로 이야기해보겠습니다.
< Index 의 성능을 체감한 상황>
1. 회사에서 원천테이블을 사용해서 작업할 일이 생김
2. 원천 테이블의 크기가 너무 커서 특정 지역, 기간에 해당하는 데이터만 먼저 Hue 에 저장을 해놓고 다시 불러와서 작업을 하기로 함
(약 74개의 시군구 단위, 2018년 01월 ~ 2021년 12월 (48개월) 기간의 데이터를 추출)
3. 아무 생각 없이 74개의 시군구를 기준으로 하나씩 저장을 하기로 마음먹음
4. 그런데 시간이 너무 오래걸리고 Zeppelin 이 계속 퍼지는 상황이 발생
5. 개발팀장님께서 where 절에 시군구 말고 인덱스가 설정되어 있는 연,월 기준으로 필터링을 하라고 조언
6. 순식간에 쿼리가 다 돌아감
이 순간 '아.. 인덱스의 힘이 엄청나구나' 라는 생각을 하게되었습니다.
이렇듯 sql 에서 인덱스를 활용하는 방법은 간단합니다.
인덱스가 설정되어 있는 컬럼을 where 절에 사용하기
그런데 무조건 where 절에 인덱스가 설정된 컬럼을 쓴다고 해서 인덱스의 성능을 활용할 수 있는 것은 아닙니다.
그러니 쿼리가 인덱스를 활용할 수 없는 상황을 알아보고, 그 상황을 피해 인덱스를 활용하는 쿼리를 작성할 필요가 있습니다.
2.2. 인덱스를 활용하지 않는 검색 조건
인덱스가 제대로 사용되지 않는 5가지 Case 를 살펴봅시다.
1) LIKE 연산자(중간 일치, 후방 일치)를 사용할 때
where 절에 인덱스가 설정되어 있는 컬럼을 사용한다고 하더라도 like 연산자를 이용할 경우엔 인덱스의 성능을 기대하기가 어렵습니다.
예를 들어보겠습니다.
select order_id
from orders
where shop_name like "%갈비%" ;위 쿼리는 shop_name 에 '갈비' 라는 문자가 들어가는 order_id 만 추출하는 쿼리입니다.
그리고 shop_name 에 인덱스가 설정되어 있다고 해봅시다.
인덱스가 설정되어 있는 컬럼을 where 절에 사용했으니 효율적으로 쿼리가 작동할 것이라고 생각되지만, 사실 그렇지 않습니다.
왜냐하면 LIKE 연산자를 사용했기 때문입니다.
LIKE 연산자를 이용하여 shop_name 컬럼에 '갈비'라는 문자를 포함했는지를 조건에 넣었기 때문에 sql에서는 '해당 문자에 혹시라고 갈비가 들어있지 않을까?' 라고 생각하며 모든 열을 스캔하게 됩니다.
그렇기 때문에 인덱스가 제대로 활용되지 않는 것이죠.
아래의 경우도 마찬가지로 인덱스가 제대로 활용되지 않는 경우입니다.
select order_id
from orders
where shop_name like "%갈비" ;
그렇지만 LIKE 를 사용할 때 전방 일치(갈비%)의 경우엔 인덱스가 적용된다고 합니다.
따라서 LIKE 연산자를 사용할 때, 인덱스를 활용하고 싶다면 아래와 같은 방식으로만 활용을 할 수 있습니다.
select order_id
from orders
where shop_name like "갈비%" ;
2) 인덱스 컬럼으로 연산하는 경우
인덱스 컬럼을 이용하여 연산을 하는 경우에도 인덱스를 제대로 활용할 수 없습니다.
select *
from temp_tb
where col_1 * 1.1 > 100 ;위의 쿼리처럼 col_1 컬럼에 인덱스가 설정되어 있고 where 절에 col_1 * 1.1 > 100 이라는 조건을 넣은 경우에는 인덱스가 사용되지 않습니다.
왜냐하면 인덱스 내부에 존재하는 값은 col_1 의 값이지 col_1 * 1.1 의 값이 아니기 때문입니다.
따라서 위 경우에는 col_1 * 1.1 값을 연산하기 위해 모든 테이블을 스캔하게 됩니다.
만약 연산이 필요한 경우라면 아래와 같이 쿼리를 작성함으로써 인덱스를 활용할 수 있게 됩니다.
select *
from temp_tb
where col_1 > 100/1*1 ;인덱스가 설정되어있는 컬럼을 그대로 두는 것이 포인트입니다.
3) 인덱스 컬럼에 함수를 사용하는 경우
마찬가지로 인덱스 컬럼에 함수를 사용하는 경우에도 인덱스가 사용되지 않습니다.
select *
from temp_tb
where length(col_1) = 10 ;위와 같이 인덱스 컬럼에 함수를 사용했을 때 인덱스가 적용되지 않는 이유는 '인덱스 컬럼에 연산을 하는 경우'와 같은 이유입니다.
인덱스로 설정된 값은 col_1 의 값이지 length(col_1) 의 값이 아니기 때문에 Full Table Scan 이 발생하게 됩니다.
4) IS NULL 을 사용하는 경우
IS NULL 을 사용하는 경우에도 인덱스를 사용할 수 없습니다.
왜냐하면 일반적으로 인덱스를 설정해놓은 컬럼에는 NULL 값이 존재하지 않기 때문입니다.
따라서 인덱스가 설정된 컬럼에 IS NULL 을 사용할 일도 없겠지만, 만약 사용하게 된다면 Full Table Scan 이 발생하게 됩니다.
select *
from temp_tb
where col_1 is null ;
5) 부정형을 사용하는 경우
마지막으로 부정형(<>, != , NOT IN)을 사용하는 경우에도 인덱스를 사용할 수 없습니다.
단순히 생각을 해봤을 때 Tree 구조에서 해당 값을 찾아 내려가는 방식으로 인덱싱이 진행되는데,
부정형을 사용하면 이러한 방식이 이루어질 수 없을테니 인덱스가 활용되지 않을것이라고 생각할 수 있습니다.
select *
from temp_tb
where col_1 <> 100 ;
그렇기 때문에 이러한 부정형은 긍정형으로 바꾸어 사용해주면 인덱스를 활용할 수 있습니다.
위의 예시 쿼리를 아래와 같이 수정해본다면 인덱스를 활용할 수 있게 됩니다.
select *
from temp_tb
where (col_1 > 100 or col_1 < 100) ;쿼리만 살펴본다면 가독성이 떨어지지만 인덱스를 활용하는 관점에서는 잘 짜여진 쿼리라고 생각할 수 있습니다.
3. 마치며
이렇게 이번 포스팅에서는 인덱스의 구조와 SQL 쿼리를 작성할 때 인덱스를 잘 활용하는 방법에 대해 알아봤습니다.
사실 SQL 쿼리를 작성하는 입장에서는 인덱스의 구조는 잘 모르더라도 인덱스를 활용하는 방법은 알고 있으면 좋겠다는 생각이 들었습니다.
따라서 인덱스를 활용하기 위해 사용하려는 테이블의 인덱스 컬럼을 먼저 확인해보고 이를 인지하고 있는 습관을 가지는 것이 좋아 보입니다.
이번 포스팅이 도움이 되셨길 바라며 이만 포스팅을 마칩니다!
'#1 언어 노트 > #1.1. SQL 노트' 카테고리의 다른 글
| [빅쿼리/SQL] 날짜 및 시간 함수 기본기 다잡기 (0) | 2023.03.25 |
|---|---|
| [SQL] with 절을 효율적으로 사용하기 (3) | 2022.10.02 |
| [SQL] MEDIAN 함수 없이 중앙값 구하기 (2) | 2022.07.24 |
| [SQL] SQL에서 정규표현식 활용하기 (3) | 2022.06.10 |
| [SQL] 윈도우 함수(Window Function)의 소중함을 느껴보자 (6) | 2021.12.19 |
블로그의 정보
고동의 데이터 분석
소라고동_