기술노트 블로그

안녕하세요, 유튜브 SQL 튜닝 강의를 보고 학습한 자료를 남깁니다.

감사합니다.

실습

테이블 생성을 하였다.

성능 소요시간을 확인해보니 499/ms 정도 된다.

created_at 컬럼에 인덱스를 생성하니 

소요시간이 167/ms로 단축되었다.

type을 통해 인덱스 레인지 스캔이 이루어진 것을 볼 수 있었다.

그리고 rows는 1,068 정도 조회된 것을 확인할 수 있다.

-> Filter: (users8.department = 'Sales')  (cost=1174.84 rows=107) (actual time=4.144..135.122 rows=108 loops=1)
    -> Index range scan on users8 using idx_created_at, with index condition: (users8.created_at >= <cache>((now() - interval 3 day)))  (cost=1174.84 rows=1067) (actual time=1.616..134.444 rows=1067 loops=1)

INDEX RANGE SCAN을 했는데 날짜 값의 조건을 만족하는 인덱스들 만을 가져온다. 1067행을 조회하였고 접근한 수 자체가 적다보니 134ms밖에 안 걸렸다.

이렇게 해당하는 값들을 가져오고 그 다음에 Filtering 작업을 거친다. 날짜 조건을 만족하는 데이터들 중에서 department='Sales' 인 값들을 가져온 것이다. rows=108이므로 총 108개의 데이터가 출력이 될 것이다.

속도가 왜 빨라졌어? 아 INDEX RANGE SCAN으로 딱 필요한 데이터에 대해서만 접근을 했기 때문에 속도가 빨라졌구나 추측을 할 수 있다.

이번에는 department 컬럼에 인덱스를 생성하고

조회를 해 보았다.

소요 시간이 2,157ms가 소요되는 것을 볼 수 있다.

-> Filter: (users8.created_at >= <cache>((now() - interval 3 day)))  (cost=15571.68 rows=63765) (actual time=23.752..2113.186 rows=106 loops=1)
    -> Index lookup on users8 using idx_department (department='Sales')  (cost=15571.68 rows=191314) (actual time=9.402..2092.389 rows=100000 loops=1)

옵티마이저가 두개의 인덱스 중 더 효율적인 인덱스를 골라서 INDEX RANGE SCAN을 적용하였다.

한 개의 인덱스만 사용을 하였다.

1,043rows를 확인했다.

따라서 중복 정도가 낮은 컬럼을 골라서 인덱스를 생성하라.

항상 실행 계획을 보면서 판단을 하면 좋다.

멀티 컬럼 인덱스

기존의 인덱스를 모두 지우고

인덱스의 순서를 created_at 그리고 department 순으로 만들었다.

SELECT 시 조회 성능이 매우 좋은 것을 볼 수 있다.

이번에는 인덱스 순서를 바꾸어서

department, created_at 순서로 멀티 컬럼 인덱스를 생성해보았다.

비슷한 성능을 내는 것을 볼 수 있다.

멀티 컬럼 인덱스 사용 시 소요 시간이나 created_at 컬럼에 인덱스를 활용시 소요 시간이나 비슷한 것을 볼 수 있다.

이런 경우 created_at 인덱스에만 인덱스를 사용을 하는 것이 좋다.

왜냐하면 인덱스를 최소한으로 거는 것이 더 중요하기 때문이다.

정말 감사합니다.

https://www.youtube.com/watch?v=BpWzWoS9f1g&list=PLtUgHNmvcs6rJBDOBnkDlmMFkLf-4XVl3&index=16

안녕하세요, 유튜브 SQL 튜닝 강의를 보고 학습한 자료를 남깁니다.

감사합니다.

최근 3일 이내에 가입한 유저 조회하기

테이블 생성 및 더미 데이터 입력

위 그림 대로 테이블을 생성하고

100만건의 더미 데이터를 입력하였다.

의미 있는 데이터를 입력하였다.

테스트를 해보겠다.

created_at 컬럼에 인덱스 생성 전에는 소요시간이 144/ms이다.

그런데 

인덱스를 생성하고 조회를 하면

30/ms로 조회되는 것을 확인할 수 있다.

인덱스 표를 참조해서 

특정 범위의 인덱스 값을 활용해서

데이터를 조회해 온 것이다.

rows 수 또한 기존의 약 100만건에서 1,000건으로 줄어든 것을 볼 수 있다.

Full Table Scan 그림

테이블의 모든 행을 조회하기 때문에 느릴 수 밖에 없다.

INDEX RANGE SCAN 그림

INDEX의 범위의 값을 활용해서 테이블의 값을 조회하는 것이다.

정말 감사합니다.

https://www.youtube.com/watch?v=S-DzOseVss4&list=PLtUgHNmvcs6rJBDOBnkDlmMFkLf-4XVl3&index=15

안녕하세요, 유튜브 SQL 튜닝 강의를 보고 학습한 자료를 남깁니다.

감사합니다.

SQL 문의 '실행 계획' 사용해보기

옵티마이저는 SQL 요청이 들어오면

어떤 방식으로 어떻게 처리할지를 계획해요.

실행 계획을 통해서 이를 직접 알아볼 수 있다.

SQL 튜닝은 이 계획을 바꾸어주는 것이다.

옵티마이저 실행 계획 확인 방법

실행계획 타입

테이블은 PK를 기준으로 인덱스가 형성되어 있기 때문에

age 컬럼으로 조건절로 조회를 하면 

Full Table Scan을 하게 된다.

다음은 INDEX FULL SCAN을 통해서 데이터를 가져온 것이다.

FULL TABLE SCAN을 한 방법보다 

훨씬 더 빠르게 SELECT가 되는 것을 볼 수 있다.

UNIQUE가 아닌 경우라면 해당 컬럼에 데이터가 중복되어서 입력이 된다.

그래서 const가 출력될 수 없다.

중복되지 않은 컬럼에서 데이터 1건을 찾는 순간

나머지 데이터는 볼 필요가 없어진다. 훨씬 빠른 것이다.

성능 개선이 필요 없을 정도로 제일 좋다.

type이 const인 것을 볼 수 있다.

인덱스이기 때문에 한 번에 찾을 수 있는 것이다.

인덱스로 범위 형태의 데이터를 조회하는 것이다.

하지만 찾아야할 범위가 너무 크다면, 인덱스에서 찾아서 다시 실제 테이블에서 찾아야 하기 때문에

성능저하의 요인이 될 수 있다.

다음과 같이 인덱스를 갖은 컬럼을 기준으로 RANGE INDEX SCAN을 하였다.

EXPLAIN 명령어로 조회시 type에 range가 나오는 것을 볼 수 있다.

비고유 인덱스 스캔을 하는 것이다.

인덱스 표에 데이터들이 정렬되어 있기 때문이다.

type에 ref가 나오는 것을 볼 수 있다.

비고유 인덱스로 데이터를 찾았다.

이외에도 type들이 많다.

eq_ref, index_merge, ref_or_nul 등이 있다.

정말 감사합니다.

https://www.youtube.com/watch?v=4a747XKKkkw&list=PLtUgHNmvcs6rJBDOBnkDlmMFkLf-4XVl3&index=11

안녕하세요, 유튜브 SQL 튜닝 강의를 보고 복습한 자료를 남깁니다.

감사합니다.

1. PK 인덱스 효과 (클러스터형 인덱스)

PRIMARY KEY인 컬럼은 클러스터형 인덱스가 자동으로 생성된다.

테이블이 PRIMARY KEY 컬럼을 기준으로 정렬된다.

그래서 SELECT시 성능이 빠르다.

2. UNIQUE 인덱스 효과 (보조 인덱스)

UNIQUE 컬럼은 보조 인덱스가 생성된다.

테이블은 정렬되지 않지만

책의 찾아보기와 같은 역할을 수행한다.

SELECT 시 성능이 빠르다.

클러스터형 인덱스가 더 빠르다.

조회하는 페이지가 더 적기 때문이다.

users 테이블은 PRIMARY 인덱스만 있고,

users2 테이블은 PRIMARY 인덱스와 UNIQUE 인덱스 이렇게 2개가 있다.

용량의 차이가 users 테이블은 16K

users2 테이블은 32K 인 것을 확인할 수 있다.

감사합니다.

#1

https://www.youtube.com/watch?v=QilztrTTMCw&list=PLtUgHNmvcs6rJBDOBnkDlmMFkLf-4XVl3&index=8

#2

https://www.youtube.com/watch?v=P0f0uB9tCYM&list=PLtUgHNmvcs6rJBDOBnkDlmMFkLf-4XVl3&index=9

안녕하세요, 유튜브 SQL 튜닝 강의를 보고 학습한 자료를 남깁니다.

감사합니다.

인덱스 직접 설정해보기

그림에 맞도록 데이터베이스와 users 테이블을 생성해보았다.

그리고 더미 데이터를 100만건 생성하였다.

유튜버의 코드를 공유한다.

재귀 횟수를 100만건 선언한다.

그리고 cte라는 임시 테이블을 생성하고 

cte 임시 테이블안에 100만건의 데이터를 적재한다.

그리고 그 임시 테이블에서 

n을 조회해서 id를 만들고, 1부터 1000까지의 랜덤값을 age로 만들어서 조회한다.

그리고 그 조회된 데이터들을 users 테이블의 id와 age 컬럼에 맞게 INSERT한다.

이렇게 100만건의 더미 데이터를 만들었다.

그리고 조회를 한다.

나이가 23세인 회원만 조회를 하였는데, 소요시간이 약 345(ms) 정도가 소요된다.

나이 컬럼에 인덱스를 생성하고 

다시 23세인 회원을 조회를 하니 소요 시간이 159(ms) 정도 소요되었다.

그 이유는

나이 컬럼에 인덱스를 생성하여

속도가 빨라진 것이다.

인덱스를 생성하면 위 그림과 같이 정렬된 나이 인덱스 표를 기준으로 데이터를 조회하기 때문이다.

정말, 감사합니다.

https://www.youtube.com/watch?v=VI30oDxOlGU&list=PLtUgHNmvcs6rJBDOBnkDlmMFkLf-4XVl3&index=7

안녕하세요, 유튜브 SQL 튜닝 강의를 보고 학습한 자료를 남깁니다.

감사합니다.

인덱스(Index)란?

데이터베이스 테이블에 대한 검색 성능의 속도를 높여주는 자료 구조를 뜻한다.

데이터를 빨리 찾기 위해 특정 컬럼을 기준으로 미리 정렬해놓은 표

예시를 통해 인덱스의 의미를 직관적으로 이해해보자.

만일 데이터가 섞여 있다면, Full Table Scan을 해야 한다.

시간이 오래걸릴 수 밖에 없다.

그래서 인덱스를 사용해서 해결할 수 있다.

미리 정렬되어 있는 시스템 내부적인 표가 있다면

쉽게 조회가 가능하다.

정말 감사합니다.

https://www.youtube.com/watch?v=VvYh8HBM0A8&list=PLtUgHNmvcs6rJBDOBnkDlmMFkLf-4XVl3&index=6

안녕하세요, 유튜브 SQL 튜닝 강의를 보고 학습한 자료를 남깁니다.

감사합니다.

들어가면서

성능 개선을 위해서 MySQL 구조 먼저 파악하겠습니다.

그리고 SQL 튜닝의 핵심이 무엇인지 짚고 넘어갈 것이다.

-> 어떤 부분에서 MySQL의 성능을 많이 잡아 먹는지, 어떤 요인이 주로 문제를 일으키는지 파악할 수 있어야 한다.

MySQL의 아키텍처

MySQL이라는 서버가 있고

그 안에 MySQL엔진이라는 공간이 있고

스토리지 엔진이라는 공간이 있다.

과정을 알아보자.

1. 클라이언트가 DB에 SQL 요청을 보낸다.

2. MySQL 엔진에서 성능 최적화를 해주는 옵티마이저가 먼저 SQL문을 분석한다. 

아 이런 이런 데이터를 가져오는구나 그래서 어떻게 데이터를 가져오면 더 빠르겠다 이렇게 판단을 한 뒤 계획을 세운다.

그 계획에는 어떤 순서로 어떻게 테이블에 접근할지, 인덱스를 사용할지, 어떤 인덱스를 사용할 지 등을 결정한다.

하지만 옵티마이저가 세운 계획은 완벽하지는 않다. 그래서 SQL 튜닝이 필요하다. 옵티마이저가 잘 알아 듣을 수 있도록 튜닝이 필요하다.

3. 옵티마이저가 세운 계획을 바탕으로 스토리지 엔진에서 데이터를 가져온다.

옵티마이저가 세운 계획대로 스토리지 엔진에 데이터가 있는데, 이 데이터를 가져온다.

DB 성능에 문제가 생기는 원인의 대부분은 스토리지 엔진으로부터 데이터를 가져올 때 발생이 된다. 

데이터를 찾기가 어려워서 오래 걸리거나, 가져올 데이터가 너무 많아서 오래 걸린다.

SQL 튜닝의 핵심은 스토리지 엔진으로부터 되도록이면 데이터를 찾기 쉽게 바꾸고, 적은 데이터를 가져오도록 바꾸는 것을 말한다.

4. MySQL 엔진에서 정렬, 필터링 등의 마지막 처리를 한 뒤에 클라이언트에게 SQL 결과를 응답한다.

스토리지 엔진에서 가져온 데이터를 MySQL 엔진에서 정렬을 진행하고, 필터링을 진행하는 여러 처리를 거친 다음에 

클라이언트에게 SQL 결과를 응답합니다.

SQL 튜닝의 핵심

SQL 튜닝이란 옵티마이저의 실행 계획이 완벽하지 않다. 그래서 우리가 튜닝하는 것을 SQL 튜닝이라고 한다.

이 데이터를 얼마나 쉽게, 적은 데이터를 효율적으로 가져오게 할 것인지가 핵심이다.

1. 스토리지 엔진에서 데이터를 찾기 쉽게 바꾸기

2. 스토리지 엔진으로부터 가져오는 데이터의 양 줄이기

가장 많이 활용되는 방법은 바로 인덱스의 활용이다.

인덱스를 적절하게 사용해야만 DB 성능이 개선이 된다.

정말 감사합니다.

https://www.youtube.com/watch?v=VVszO0lktE4&list=PLtUgHNmvcs6rJBDOBnkDlmMFkLf-4XVl3&index=5

안녕하세요, 유튜브 SQL 튜닝 강의를 보고 학습한 자료를 남깁니다.

감사합니다.

DB 성능 방법들의 예시

- SQL 튜닝

-  캐싱 서버 활용 (Redis 등)

-  레플리케이션 (Master/Slave 구조)

-  샤딩

-  스케일업 (CPU, Memory, SSD 등 하드웨어 업그레이드)

SQL 튜닝을 가장 먼저 고려해야 하는 이유

1. 추가적인 시스템을 구축해야 한다.

SQL 튜닝을 제외한 다른 방법들은 추가적인 시스템을 구축해야 한다.

금전적, 시간적 그리고 관리적 비용이 증가한다.

단순히 하나의 서버만 아니라 여러 대의 서버를 동시에 관리하고 모니터링하는 것에 있어서 비용이 커진다.

근데 그에 비해 SQL 튜닝은 기존의 시스템 변경없이 성능 개선을 할 수 있다.

2. 근본적인 문제를 해결하는 방법이 SQL 튜닝일 가능성이 높다.

SQL 자체가 비효율적으로 작성했다면 아무리 시스템적으로 성능을 개선한다고 하더라도 한계가 있다.

하지만 SQL 튜닝을 통해 기본적으로 성능을 향상시킨다면, 시스템적인 성능 개선이 필요없거나 훨씬 간단한 개선으로 큰 성능 개선 효과를 얻을 수 있다.

정말 감사합니다.

https://www.youtube.com/watch?v=vbatA68GL1I&list=PLtUgHNmvcs6rJBDOBnkDlmMFkLf-4XVl3&index=4