SQL 튜닝 - 1 Index 의 생성과 활용

Posted Dec 27, 2023 Updated Dec 28, 2023

By YubinShin 13 min read

🐬 SQL 튜닝

SQL 튜닝이란, 데이터베이스 쿼리의 성능을 최적화하여 더 빠른 실행 시간과 효율적인 자원 사용을 달성하는 과정입니다.

웹 애플리케이션의 백엔드 성능을 높일 때 종종 실행됩니다.

DB와 관련된 백엔드 성능 향상 방식엔 쿼리문 최적화, 캐싱, 샤딩 등 다른 방식의 접근법도 있지만 이번 포스팅에서는 Index를 활용한 SQL 튜닝에 대해 적어보겠습니다.

인덱스를 잘 이해하고 있다면 더 좋은 SQL을 작성할 수 있을 것이고, 훨씬 더 성능 좋은 애플리케이션을 만들 수 있을 것입니다.

쟁점

웹 앱의 경우 실행 시 레이턴시(대기 시간) 대부분은 DB에서 데이터를 가져오는 시간입니다.

DBMS의 인덱스를 사용하지 않을 때, 데이터베이스 시스템은 일반적으로 “풀 테이블 스캔” 방식을 사용합니다. 이러한 경우, DBMS는 각 행을 개별적으로 검사하여 조건에 맞는 데이터를 찾아야 합니다.

이는 데이터베이스에 큰 부하를 주고, 특히 데이터가 많은 경우에는 매우 비효율적일 수 있습니다.

힝.. 🥺

문제가 발생하는 이유

보통 데이터베이스는 저장공간인 디스크에 데이터를 저장합니다. (예 : 하드디스크, 메모리디스크)

때문에 데이터를 DB에서 불러오려면 디스크에 접근해서 가져와야 합니다.

이 과정을 Input/Output(입출력), 줄여서 I/O 이라고 부릅니다.

데이터들이 정렬되지 않은 상태라면 테이블의 모든 행을 하나하나 읽는 풀 테이블 스캔 을 진행하는데, 하나하나 읽을 때마다 디스크에 I/O 이 발생합니다. (랜덤 I/O ) 이 부분이 시간과 비용을 많이 소모합니다.

하지만 Read 할 데이터가 정렬되어 있다면 탐색 알고리즘으로 빠르게 데이터를 찾아 가져올 수 있습니다.

🐬 인덱스

인덱스란 읽기 속도의 향상을 위해 특정 기준에 따라 원본 테이블의 데이터를 정렬하고, 그걸 복사, 저장해둔 테이블입니다.

때문에 인덱스를 활용하면 행이 1억개인 테이블을 다 찾아보는 것보다 훨씬 빨리 데이터를 찾을 수 있습니다.

인덱스의 단점

index 는 원본 테이블을 정렬 + 복사한 것이기 때문에 저장소의 용량을 많이 차지합니다.
Create, Update, Delete 작업 시 index 들과 원본을 동기화 하는 데 비용이 많이 듭니다.

인덱스의 구조

대부분의 DBMS는 B-Tree 계열 인덱스를 사용합니다.

인덱스 종류에 대한 특별한 언급이 없다면 아마도 B-Tree 계열 인덱스를 의미할 것입니다. ( MySQL, PostgreSQL도 기본적으로 B-Tree 계열 인덱스를 사용합니다. )

Binary Serach Tree

2번 이동으로 1~7까지 범위를 찾을 수 있음
이미지 출처 :코딩애플 유튜브

빅오기준 시간복잡도 : O(n)

B-Tree

2번의 이동으로 1~13까지 범위를 찾을 수 있음
이미지 출처 :코딩애플 유튜브

빅오기준 시간복잡도 : O(logn)

항상 균형 잡힌 상태를 유지하는 자가 균형 트리입니다. 이 때문에 모든 연산은 항상 O(logn) 의 시간 복잡도를 가집니다.

B+Tree

이미지 출처 :코딩애플 유튜브

빅오기준 시간복잡도 : O(logn)

B+Tree 역시 B-Tree 와 마찬가지로 자가 균형 트리입니다.

리프노드에만 데이터를 저장하고, 상위 노드들은 가이드만 제공합니다.

B+Tree vs B-Tree

B+Tree 는 리프노드들도 포인터로 다음 노드를 가르키고 있어서 범위(Range)검색이 쉽고 빨라집니다. (예시 : 4~9까지 찾아다 줘!)

적합한 인덱스 생성

스키마 디자인과 달리 인덱스를 넣을 땐 데이터만 보고 테이블에 어떤 인덱스를 넣을지 결정할 수 없습니다. 대신 쿼리를 검사하여 어떤 인덱스가 가장 좋은 성능을 발휘하는지 결정해야 합니다. 즉, 인덱싱은 애플리케이션의 액세스 패턴에 따라 이루어져야 합니다.

애플리케이션에 대한 액세스 패턴을 작성하는 애플리케이션 개발자라면 인덱스를 디자인하는 데 가장 적합한 후보자입니다. 액세스 패턴이 무엇인지 파악하고 이에 따라 인덱스를 생성하면 됩니다.

인덱스 칼럼 결정하기

다음은 인덱스 사용 여부를 결정할 때 고려해야 할 몇 가지 일반적인 법칙입니다.

쿼리의 where 절에 표시되는 모든 항목에 인덱스가 있어야 한다고 가정하지 마세요. 실행 중인 모든 쿼리와 해당 액세스 패턴을 고려하세요.
모든 열에 인덱스를 생성하지 마십시오. 이렇게 하면 테이블이 기능적으로 복제되어 삽입 속도가 느려집니다. 또한 원하는 만큼 읽는 데 도움이 되지 않습니다.
인덱싱할 열을 결정할 때 전체 쿼리를 고려하십시오. 여기에는 정렬, 그룹화, 결합이 포함됩니다.
모든 쿼리에 대해 완벽한 인덱스를 만들어야 한다고 걱정하지 마십시오. 항상 가능한 것은 아니며 때로는 기존 인덱스를 활용하기 위해 쿼리를 재작업해야 할 수도 있습니다.
1개의 칼럼에만 인덱스를 설정한다면 데이터 중복도가 낮은 컬럼에 인덱스를 설정하는 것이 유리합니다.

🐬 MySQL 로 index 실습

MySQL에 인덱스 추가하기

MySQL에서 테이블에 인덱스를 추가하려면 alter table 테이블명 add index 인덱스명(인덱스 추가할 칼럼명) 을 사용하면 됩니다.

  
alter table people add index birthday_index (birthday);

그럼 birthday 칼럼에 birthday_index라는 인덱스가 생성됩니다.

이제 birthday 칼럼을 기준으로 질의하면 속도가 빨라집니다.

MySQL 인덱스 사용 예시

Equality
생일이 해당 일자와 동일한 요소를 빠르게 찾을 수 있습니다.
1 select * from people where birthday = '1989-02-14';

Ranges

생일을 기준으로 특정 범위에 속한 요소를 빠르게 찾을 수 있습니다.

(제한범위, 무제한 범위 모두 사용가능)

  
 # Unbounded
 select * from people where birthday >= '2006-01-01';
    
 # Bounded
 select * from people where birthday between '2006-01-01' and '2006-12-31';

Sorting 인덱스를 사용하면 정렬된 데이터를 빠르게 반환할 수 있습니다. Index 는 정렬된 데이터로 만들어지기 때문에 추가로 order by 연산을 복잡하게 하지 않아도 됩니다.
1 select * from people order by birthday limit 10;
Grouping 인덱스를 사용하면 전체 테이블 스캔 시 보다 행들을 빠르게 그룹화할 수 있습니다.
1 select birthday,count(*) from people group by birthday;

🐬 부록 : 클러스터형 인덱스 vs 비클러스터형 인덱스

특징	클러스터형 인덱스	비클러스터형 인덱스(보조 인덱스)
정의	키 값을 기반으로 데이터 행을 정렬하고 저장	데이터 행과 별개의 구조를 가지며, 인덱스 키 값이 포함됨
데이터 정렬	테이블이나 뷰의 데이터 행을 인덱스 정의에 포함된 열 기반으로 정렬	데이터 행에 대한 포인터를 포함한 별도의 인덱스 구조
테이블당 인덱스 수	하나만 존재	여러 개 존재 가능
저장 구조	정렬된 순서로 저장 (클러스터형 테이블)	힙 또는 클러스터형 테이블에 따라 행 로케이터 구조가 달라짐
특징적 사용 예시	주 키 (Primary Key)	키가 아닌 열을 포함하여 인덱싱된 쿼리 실행 (fk 로 인덱스를 만들면 JOIN 연산 시 성능이 향상됩니다.)
부재시	테이블에 클러스터형 인덱스가 없으면 데이터행은 순서가 없는 힙 형태로 저장됩니다.	없어도 됩니다.
추가 정보	https://learn.microsoft.com/ko-kr/sql/relational-databases/indexes/create-indexes-with-included-columns?view=sql-server-ver16	https://learn.microsoft.com/ko-kr/sql/relational-databases/indexes/create-indexes-with-included-columns?view=sql-server-ver16, https://learn.microsoft.com/ko-kr/sql/sql-server/maximum-capacity-specifications-for-sql-server?view=sql-server-ver16

이슈명	링크
성능 향상을 위한 SQL 작성법	https://d2.naver.com/helloworld/1155
코딩애플 유튜브-index가 뭔지 설명해보세요 (개발면접시간)	https://www.youtube.com/watch?v=iNvYsGKelYs&t=344s
Introduction to indexes	https://planetscale.com/learn/courses/mysql-for-developers/indexes/introduction-to-indexes

blog, backend

This post is licensed under CC BY 4.0 by the author.