📻 SQL 성능 튜닝 - (1) 왜 SQL 튜닝을 해야하지? 어떻게 하지?

나는 이번 Space Club 프로젝트를 진행하면서, SQL문을 작성하는 방식에 대해서 고민했었다.

SQL 쿼리를 통해 같은 데이터 다루더라도 작성하는 방법은 여러 방법이 있는데, 내부적으로 DBMS 안에서 어떻게 작동되는지, 어떻게 SQL을 작성하는 것이 효율적인 방법인지 궁금했다.

 

일단 효율을 논하기 전에, 내가 프로젝트에서 썼던 MySQL 구조와 작동 방식부터 학습하는게 필수였다.

구조와 작동 방식보다도, 일단 왜 MySQL을 사용했는지에 대한 이해가 필요했다.

 

🤷 MySQL을 왜 프로젝트에서 사용했는가?

데이터베이스를 다루는 DBMS로는 여러 종류가 존재한다. 그 중, 관계형 DB는
1. 일반적으로 가장 많이 사용하는 데이터베이스이고,

2. 테이블 분리를 통해 중복값에 따른 성능 저하 방지 및 유지보수 하기 용이하다는 장점

위 두 가지 이유 때문에 관계형 DB를 사용했고, opensource이기에 무료이며, opensource 중에 점유율이 가장 높은 이유로 MySQL RDBMS를 사용했다.

 

또한 MySQL은 Oracle과 비교했을때의 장점으로는

1. MySQL에서는 대부분 중첩 루프 조인 알고리즘(NL 조인)으로만 풀리며

2. 필요한 DBMS를 설정해 사용할 수 있다. (스토리지 엔진이 Oracle에는 없다. 스토리지 엔진의 확장성이 뛰어나다 e.g) MyISAM,InnoDB)

3. 상대적으로 낮은 메모리 사용으로 저사양 PC에서도 손쉽게 설치 / 개발 할 수 있다.

 

이런 종합적인 것을 따져보았을 때, MySQL을 프로젝트에 도입하는것이 합리적이라는 결정을 내릴 수 있었다!

2024 DB 엔진 랭킹, 출처: https://db-engines.com/en/ranking_trend

 

⚙️ MySQL의 구조와 작동 방식에 대해서

MySQL은 크게 MySQL 엔진과 스토리지 엔진으로 나뉜다.

 

1. 먼저 우리가 SQL을 통해서 RDBMS인 MySQL에 요청을 한다.

2. 그러면 MySQL엔진에서 먼저 SQL을 받아서 Parser를 통해 MySQL이 이해할 수 있는 최소단위로 구성요소를 분리하고 트리로 만들어 문법 오류를 검사한다. 

3. 그리고 preprocessor가 생성된 트리 결과를 토대로 이미 만들어진 테이블이나 뷰 등으로 구성되지는 않는지, 존재하지 않는 열 포함하지는 않는지, 조회권한이 없는 테이블 조회하는지 등 유효성 검사를 진행한다.

4. 이후, 사용자가 요청한 데이터를 빠르고 효율적으로 optimizer가 전략적 계획인 실행계획을 세운다.

5. 그리고 optimizer가 세운 계획을 토대로 스토리지 엔진에 위치한 데이터까지 찾아간 뒤 해당 데이터를 MySQL 엔진으로 전달한다.

6. 그리고 MySQL 엔진은 전달된 데이터에서 불필요한 부분을 필터링하고 필요한 연산을 수행 한 뒤, 사용자에게 최종 결과를 알려준다.

MySQL 수행 프로세스/ 출처: 아래 Reference 책

즉 정리하자면, 스토리지 엔진은 optimizer가 세운 계획 대로 DB에 저장된 디스크나 메모리에서 필요한 데이터를 가져오는 역할을 한다!

그리고 그 데이터를 MySQL 엔진으로 보내준다. 

MySQL 엔진은 SQL문 넘겨받아 검사를 진행하고 데이터를 어떻게 빠르게 찾아갈지 경로를 찾는 역할을 수행한다!

 

이 구조에서 튜닝을 해야하는 이유가 나온다.

 

 🤨 SQL 튜닝은 왜 알아야 할까?

위 MySQL 구조에서 볼 수 있듯, MySQL의 optimizer는 parser tree를 토대로 필요하지 않은 조건을 제거하거나 연산과정을 단순화 한다.

어떤 순서로 테이블에 접근할지, 인덱스를 사용할지, 사용한다면 어떤 인덱스 사용할지, 정렬할때 인덱스 사용할지 or 임시테이블 (temporary table) 사용할지와 같은 실행계획 수립하는데, 이때 옵티마이저가 선택한 최적의 실행 계획이 최상의 실행 계획이 아닐 가능성이 존재한다.

왜냐하면 실행계획 도출 할 수 있는 경우의 수가 너무 많으면? 그리고 연산 과정도 시간과 리소스 제한을 두고 실행 계획을 선정해야 하기 때문이다.

따라서 이 optimizer의 모든 실행계획이 최적의 실행계획은 아닐수도 있기에 튜닝이 필요하다!

 

그리고 두 번째로, InnoDB와 같은 스토리지 엔진을 통해 데이터를 실행계획에 따라 읽어왔을 때, MySQL 엔진에서 읽어온 데이터를 정렬 or 조인하고 불필요한 데이터는 필터링 처리하는 추가 작업을 한다.

따라서 MySQL 엔진 부하 줄이려면 스토리지 엔진에서 가져오는 데이터양 줄이는게 매우 중요하다.

 

👀 그럼 어떻게 SQL 튜닝을 해야 하는가?!

바로 Optimizer가 정한 실행계획을 참고해야한다.

Optimizer의 실행계획을 확인하는 방법은 EXPLAIN, DESCRIBE, DESC 키워드 + SQL문; 을 실행하면된다.

어느 것도 결과는 같지만 보통 EXPLAIN 키워드를 사용한다.

그럼 위와 같이 무섭게 생긴 표를 하나 보여준다. 🤯

id, select_type, table.. 각각 무슨 뜻일까?

 

🧐 실행계획, 하나하나 분석해보자!

1. id 

- 실행 순서를 표시하는 숫자이다. 

- id 값이 작을 수록 먼저 수행된 것이고 id 값이 같으면 두 테이블의 조인이 이루어 진 것이다.

→ driving table, driven table 파악이 가능하다. 

 

2. select_type

- SQL을 구성하는 select 문의 유형을 출력하는 항목이다. 

- FROM 절에 위치한 것인지, 서브쿼리인지, Union 절로 묶인 select문인지 등 정보를 제공한다.

자세한건 내 노션을 참고하자

 

3. table

- 테이블 명을 표시하는 항목이다. 서브쿼리나 임시테이블 만들어 별도 작업 수행할 때는 <subquery#>나 <derived#>를 출력한다.

 

4. partitions

- 실행 계획의 부가 정보. 데이터가 저장된 논리적인 영역을 표시하는 단위이다.

전체 파티션 중 특정 파티션에 선택적으로 접근하는 것이 SQL 성능 측면에서 유리하다. 따라서 너무 많은 영역의 파티션에 접근하는 것으로 출력되면 파티션 정의를 튜닝해봐야한다.

 

5. type

- 테이블의 데이터를 어떻게 찾을지에 관한 정보를 제공하는 항목이다.

따라서 테이블을 처음 부터 끝까지 확인할지(테이블 풀 스캔), 인덱스를 통해 바로 데이터를 찾아갈지 해석이 가능하다.

 

6. possible_keys

- 옵티마이저가 SQL 문 최적화하고자 사용할 수 있는 인덱스 목록 출력한다.

→ 실제 사용한 인덱스가 아닌 사용할 수 있는 후보군의 기본 키와 인덱스 목록만 보여주기에 SQL 튜닝 효용성 없다.

 

7. key

- 옵티마이저가 SQL 문 최적화하고자 사용한 PK(기본키) 또는 인덱스 명을 말한다

어느 인덱스로 데이터 검색했는지 확인 가능하기에 비효율적인 인덱스 사용했거나 인덱스 사용 하지 않았으면 SQL 튜닝 대상이 된다.

 

8. key_len

- 인덱스 사용할 때는 인덱스 전체를 사용하거나 일부 인덱스만 사용하는데, 이때 사용한 인덱스의 바이트 수를 의미한다.

 

9. ref

- reference의 약자로 테이블 조인 시 어떤 조건으로 해당 테이블에 엑세스 되었는지 알려주는 정보이다.

 

10. rows

- SQL 문을 수행하고자 접근하는 데이터의 모든 행수를 말한다. 즉, 디스크에서 데이터 파일 읽고 메모리에서 처리해야 할 행 수를 예상하는 값.

SQL 문의 최종 결과 건수와 비교해 rows 수가 크게 차이나면 불필요하게 MySQL 엔진까지 데이터 많이 가져온 것이기에 SQL 튜닝 대상이 된다.

 

11. filtered

- SQL문을 통해 MySQL 엔진으로 가져온 데이터 대상으로 필터 조건에 따라 어느 정도의 비율로 데이터 제거했는지 의미하는 항목이다.

만약 DB 엔진으로 100건 데이터 가져왔을때 이후 WHERE 사원번호 between 1 and 10 → 10 건으로 필터링하면 10이 출력된다. (단위 %)

 

12. extra

- SQL문을 어떻게 수행할 것인지에 관한 추가 정보를 보여주는 항목이다.

이를 통해서 임시테이블을 생성했는지 (Using temporary), 인덱스만 읽어서 SQL문을 처리했는지 (using index), 조인 시 join buffer를 사용했는지, (Using join buffer), 필터 조건을 스토리지 엔진으로 전달해 필터링 작업을 MySQL 엔진 부하를 줄였는지 (Using index condition), 조인 시 배치 키 엑세스 조인 방식을 사용했는지 (Using index condition (BKA)) 등이 있다.

 

다음 2편에서는 이 실행계획을 토대로 어떻게 SQL문을 개선시킬 수 있는지에 대해서 알아보도록 하자!

 

 

📻 SQL 성능 튜닝 - (1) 왜 SQL 튜닝을 해야하지? 어떻게 하지? 링크

📻 SQL 성능 튜닝 - (2) 실행계획을 분석해 성능 튜닝을 해보자! 링크

 

 

'업무에 바로 쓰는 SQL 튜닝' 책을 읽고 정리한 노션 링크

업무에 바로쓰는 SQL 튜닝 | Notion

 

 

Reference

https://ebook-product.kyobobook.co.kr/dig/epd/ebook/E000002942550

업무에 바로 쓰는 SQL 튜닝 | 양바른 | 한빛미디어- 교보ebook