Database

[DATABASE, MYSQL]DB 인덱스, 쿼리 튜닝

개요

인덱스 튜닝은 데이터베이스 성능 최적화의 중요한 부분으로, 인덱스를 효과적으로 사용하여 쿼리 성능을 향상시키는 작업이다. 인덱스는 데이터베이스 테이블의 특정 열에 대해 빠른 검색을 가능하게 하는 자료 구조이며, 올바르게 구성된 인덱스는 데이터 접근 시간을 크게 줄일 수 있다.

MySQL을 기준으로 어떻게 기존 데이터베이스의 인덱스를 통해 속도, 성능을 개선할 수 있는지 알아보기로 했다.

옵티마이저

데이터베이스를 튜닝하기에 앞서 가장 먼저 알아야 할 요소는 옵티마이저이다.
데이터베이스 관리 시스템(DBMS)의 핵심 구성 요소 중 하나로, SQL 쿼리의 실행 계획을 최적화하여 성능을 향상시키는 역할을 한다.
파서를 통해 sql을 최소단위로 분리한 후, 옵티마이저를 통해 비용을 계산(join, sort, scan 방식 결정)

옵티마이저는 주로 아래와 같은 4단계를 거쳐 SQL쿼리의 실행계획을 최적화 한다.

쿼리 파싱
논리적 계획 생성
계획 최적화
실행 계획 생성

Mysql에서는 Explain 키워드를 통해 옵티마이저의 실행 계획을 볼 수 있으며, Workbench에서는 Visual Explain기능을 통해 이를 시각화 시킬 수 있다.

EXPLAIN
	SELECT *
		FROM store JOIN customer
		ON store.store_id = customer.store_id;

인덱스 간단 설명

백과 사전의 색인과 같이 동작하는 장치로써, 데이터 검색 시, 빠른 검색을 도와준다.

하지만, 데이터의 추가, 삭제, 변경 시에는 인덱스의 구조 또한 변화하므로 많은 인덱스는 성능 저하 의 원인이 될 수 있다.

장점
테이블을 조회하는 속도와 그에 따른 성능을 향상시킬 수 있다.전반적인 시스템의 부하를 줄일 수 있다.

단점
인덱스를 관리하기 위해 DB의 약 10%에 해당하는 저장공간이 필요하다.인덱스를 관리하기 위해 추가 작업이 필요하다.인덱스를 잘못 사용할 경우 오히려 성능이 저하되는 역효과가 발생할 수 있다.

인덱스 조회, 생성, 삭제 코드

-- 인덱스 조회
SHOW INDEX FROM [table]

-- 인덱스 생성
CREATE INDEX [index_name] ON [table_name](col1, col2)

-- 인덱스 삭제
DROP INDEX [index_name] ON [table_name]

MySQL 인덱스 방식

mysql의 innoDB에서는 B-트리를 확장한 B+트리를 활용(b-트리에서 같은 레벨 노드에 링크드 리스트 적용)

아래 글에 B- Tree 자료구조에 대해서 자세하게 나와있다.

Introduction of B-Tree - GeeksforGeeks

A Computer Science portal for geeks. It contains well written, well thought and well explained computer science and programming articles, quizzes and practice/competitive programming/company interview Questions.

www.geeksforgeeks.org

같은 레벨에서 링크드 리스트를 적용하여 범위 검색시 효율적인 성능을 보여준다.
모든 리프 노드가 동일한 깊이를 가지므로 최악의 경우에도 검색시간이 일정하게 유지된다.

데이터 스캔 방식

테이블 풀 스캔

아래 사진은 인덱스를 사용하지 않고 전체 테이블을 조회하는 테이블 풀 스캔 방식이다. 테이블 풀 스캔 방식은 순서대로 조회하며, 한 번에 여러 개의 블록들를 불러와 읽어들인다.

인덱스 범위 스캔

상황에 따라 두 방법 중 빠른 쪽이 다를 수 있다.

랜덤 액세스는 single block 으로 접근하고, 시퀀셜은 multi block 단위로 조회하기 때문에 검색량 등 다양한 조건에 따라 스캔되는 결과가 달라진다.
인덱스를 활용한 다양한 스캔 방법이 존재한다. → 옵티마이저가 비용 계산 후 실행 계획 수립

인덱스 범위 스캔이 더 느릴 수도 있는 경우 & 인덱스 손익 분기점

시퀀셜 액세스는 multi block 단위 스캔 & 랜덤 액세스는 single block 단위 스캔

→ 조회 해야 하는 데이터가 많아질 수록 인덱스 범위 스캔은 느려짐

ex) 어차피 전체 데이터를 조회한다면 인덱스를 사용한 single block보다 테이블 전체를 순서대로 multiblock을 가져오는게 훨씬 빠르다.

인덱스를 사용하지 않도록 힌트를 주었을 때의 옵티마이저 동작 (Full Index Scan은 Full Table Scan보다 조금 더 빠른 성능을 보여준다.)

→ 실제 실행 시간이 옵티마이저의 계산과는 다른 것을 볼 수 있다.

위의 결과는 추출할 데이터가 많아지면 많아질 수록 발생하는 현상인데 아래와 같이 서로 교차되는 점을 손익분기점이라고 부른다.

이 손익분기점은 클러스터링 팩터에 따라서 성능차이가 발생하기도 한다.

클러스터링 팩터 : 물리적인 데이터 저장 위치가 인덱스 순서에 따라 모여 있는 지에 대한 정도

물리적인 데이터 저장위치가 모여 있다면 블록 단위 데이터 조회 시 일정 기간 해당 블록에 대한 연결을 유지하는데 다음 데이터가 해당 블록을 가리킨다면, 래치 획득과 체인 스캔과정을 거치치 않고 기존 연결된 블록을 가져오므로 블록 단위 I/O 발생이 줄어 들 수 있다.

MySQL의 InnoDB는 PK를 생성하면 자동으로 클러스티드 인덱스가 되는데 해당 클러스터링은 다른 DB와는 달리 default값이며,메모리에 순차적으로 저장되는 인덱스가 만들어진다.

OLTP vs BATCH

온라인 프로그램 튜닝 vs 배치 프로그램 튜닝

온라인 프로그램 튜닝은 보통 작은 양의 데이터를 읽고 갱신하기 때문에 인덱스를 효과적으로 활용하는 것이 매우 중요하다.
배치 프로그램 튜닝은 항상 전체 범위 처리 기준으로 튜닝해야 한다. 즉, 처리 대상 집합 중 일부를 빠르게 ****처리하는 것이 아닌 전체를 빠르게 처리하는 것을 목표로 해야 한다.

처리방식

대량 데이터를 빠르게 처리하기 위해서는 인덱스를 활용하지 않고, Table Full Scan을 활용하는 편이 좋다.
소량 데이터를 빠르게 처리하기 위해서는 인덱스를 활용한 Index Range Scan을 활용하는 것이 좋다.

쿼리 튜닝

ORDER BY 인덱스

정렬 시에 인덱스를 활용하면 빠른 정렬이 가능하다.

위와 같이 쿼리가 인덱스 스캔을 한다면 쿼리 내용은 자동으로 salary를 기준으로 정렬되게 된다. 이를 기준으로 order by를 생략할 수 있으며, sort연산을 줄여 성능 최적화가 가능하다.

컬럼 가공

인덱스에 활용되는 컬럼을 가공하면 인덱스를 타기 어렵다. 최대한 가공하지 않고 사용하는 것이 중요하다. 또는 최대한 가공된 컬럼을 조건절 뒤쪽에 넣는 것이 좋다.

-- 인덱스 입힌 컬럼을 가공
WHERE SUBSTR(컬럼명, 1,4) = ‘2019’ 해결 → WHERE 컬럼명 LIKE ‘2019%’

-- 인덱스가 있는 열 이름에는 함수나 연산을 가함
WHERE count*10=100 해결 →  WHERE count=100/10

-- 인덱스 컬럼의 묵시적 형변환(같은 타입으로 비교해야함)
WHERE 컬럼명 = ‘20190730’ 해결 → WHERE 컬럼명 = TO_DATE(‘20190730’, ‘YYYYMMDD’)

-- 인덱스 컬럼 부정형 비교.
WHERE 컬럼명 != ‘10’ 해결 → WHERE 컬럼명 IN(‘20’, ‘30’)

-- LIKE %가 앞에 위치.
WHERE 컬럼명 LIKE ‘%2019’ 해결 → or 조건 사용 WHERE 컬럼명 IN(‘102019’,‘202019’,‘302019’)

create table test2(
id int primary key auto_increment,
email varchar(255),
salary int
);
alter table test2 add index salaryindex(salary);
drop index salaryindex on test2;

select max(cast(salary as char(20))) from test2; 
select cast(max(salary) as char(20)) from test2; //가공하고 뽑아낸것이 아닌 뽑아내고 가공하기

인덱스 순서

인덱스 순서 설정 잘하기(순서에 맞게) 또 등치 조건(=)일때 가장 효율적이다.

예제

select 해당층, 펑당가, 입력일, 해당동, 매물구분, 연사용일수, 중개업소코드
from 매물아파트매매
where 아파트시세코드 'A01011350900056'
	and 평형 = '59'
	and 평형타입 = 'A'
	and 인터넷매물 between '1' and '3'
order by 입력일 desc

Between을 In-List로 전환

원래 쿼리

select 해당층, 펑당가, 입력일, 해당동, 매물구분, 연사용일수, 중개업소코드
from 매물아파트매매
where 인터넷매물 between '1' and '3'
	and 아파트시세코드 'A01011350900056'
	and 평형 = '59'
	and 평형타입 = 'A'
order by 입력일 desc

최적화 후

위와 같은 경우와 아래의 경우 뭐가 달라지는 부분일까? 한번 생각해보자.

select 해당층, 평당가, 입력일, 해당동, 매물구분, 연사용일수, 중개업소코드
from 매물아파트매매
where 인터넷매물 in ('1', '2', '3')
	and 아파트시세코드 'A01011350900056'
	and 평형 = '59'
	and 평형타입 = 'A'
order by 입력일 desc

데이터가 정수로 떨어지는 경우 Between을 쓴다면 데이터베이스는 1 2 3 순서로 가는지 아니면 1.1 1.2 1.3 형식으로 가는지 알지 못한다. 그래서 맞는 조건이 끝나더라도 1과 3사이의 숫자를 모두 탐색하게 된다.

하지만 숫자가 1, 2, 3으로 딱 떨어지는 것을 알고 있는 경우라면, IN-List로 변경하여 최적화 한다면 정확하게 1인 숫자 2인 숫자 까지만 탐색하게 되므로 쓸 데 없는 탐색을 줄일 수 있다.

** 단, 주의할 점이 있다. 너무 많은 IN-List를 사용하게 된다면 오히려 비효율적인 탐색을 하는데 트리형태로 탐색하므로 순차적으로 탐색하는 거보다 느려질 수 있기 때문이다. (아래 그림 참조)

OR조건절

OR조건절에서도 인덱스를 활용하지 못하는 경우가 많다.

select * from 거래
where (:cust_id is null or 고객ID = :cust_id)
and 거래일자 between :dt1 and :dt2

위처럼 선두 컬럼을 가공했을 때 인덱스를 사용할 수 없게 되므로 가능한 테이블 필터 조건으로만 사용하는 것이 좋다.

Between 과 Like

인덱스 활용에서는 like는 범위가 넓어지기 때문에 가능한 between 사용하는 것이 좋다.

아래 2번째 쿼리의 경우 201913이 존재할 수 있는지 없는지 데이터 베이스는 모르기 때문에 이미 끝났더라도 끝까지 탐색하게 된다. 그래서 Between을 통해 명확하게 범위를 설정해주는 것이 좋다.

where 판매월 between '201901' and '201912'
and 판매구분 = 'A'

where 판매월 like '2019%'
and 판매구분 = 'A'

인덱스 설계

가장 중요한 선택기준

1. 조건절에 항상 사용되거나, 자주 사용하는 컬럼을 선정한다.
2. ‘=’조건으로 자주 조회하는 컬럼을 앞에둔다.

주의 사항

between 연산을 했을 때 조회구간이 짧다면 효율 성능에 미치는 영향이 크지 않다.(인덱스 순서)
인덱스 스캔보다 테이블 액세스가 더 큰 부하요소이다.
대용량 데이터가 자주 입력되는 경우, 클러스터형 인덱스의 경우 빈번한 페이징이 일어나기 때문에 부하가 생긴다. 따라서 인덱스가 필요한 경우 primary(클러스터) 대신 unique만 설정하는 게 좋을 수 있다.
데이터 중복도가 높은 열은 인덱스 효과가 없다. 예를 들어 성별 열에 M, F만 있다고 하면 인덱스를 안쓰는 게 낫다. 따라서 일반 보조 인덱스보다 unique 보조 인덱스가 빠른 이유가 이것이다.자주 사용되지 않으면 성능 저하를 초래할 수 있다. (INSERT만 주구장창 하는 시스템이라면, 사용해보지도 못하고 데이터 입력에 걸리는 작업량만 많아진다.

효율적인 인덱스 설계

WHERE 절에 사용되는 열 (WHERE 절에 사용되는 열이라도 자주 사용해야 가치가 있음) SELECT 절에 자주 등장하는 컬럼들을 잘 조합해서 INDEX로 만들어두면 INDEX 조회 후 다시 데이터에서 조회할 필요가 없으므로 빠르게 검색이 가능하다. JOIN절에 자주 사용되는 열에는 인덱스의 효율이 좋음. ORDER BY 절에 사용되는 열은 데이터 페이지가 자동 정렬되어 있기 때문에 클러스터형 인덱스가 유리하다.

참고 도서

친절한 SQL 튜닝 - 조시형

친절한 SQL 튜닝 - 예스24

책 제목은 필자가 애청하는 라디오 프로그램 ‘손에 잡히는 경제’ 중 ‘친절한 경제’라는 코너에서 착안했다. 어려운 경제 이슈를 일반인 눈높이에 맞게 풀어서 설명해 주는 진행자를 보면서

www.yes24.com

'Database' 카테고리의 다른 글

Redis? 언제 쓰는건데? (1)	2023.10.23

Contents

당신이 좋아할만한 콘텐츠

Redis? 언제 쓰는건데? 2023.10.23

새소식

인기 검색어