DB
MySQL 퍼포먼스 최적화를 읽고(1) - MySQL의 특징
Just a devlog
2023. 10. 21. 23:30
MySQL의 전체적인 구조
크게 서버 엔진과 스토리지 엔진으로 구성된다.
- 서버 엔진 - 클라이언트의 요청을 받아 SQL을 처리하는 DB 자체의 기능적인 역할
- 스토리지 엔진 - 서버 엔진에서 필요한 데이터를 물리 장치에서 가져오는 역할
서버 엔진
- SQL 인터페이스, 파서, 옵티마이저, 캐시 & 버퍼
- 클라이언트가 요청한 쿼리를 파싱
- 스토리지 엔진에 데이터를 요청
- Table Join, Group By, Order By와 같은 일반적인 SQL 처리부터 Function/Procedure, Trigger, Constraint 등의 기능도 서버 엔진에서 처리됨
- (물리적인 저장장치와 직접 통신하는 역할을 제외하고) 클라이언트와 MySQL 사이에서 발생하는 대부분의 프로세스를 담당
스토리지 엔진
물리적인 저장장치에서 데이터를 읽어오는 역할을 담당
MySQL은 다른 DBMS와 다르게 스토리지 엔진이 플러그인 방식으로 동작
여러 개의 스토리지 엔진을 설치하여 사용할 수 있음
서버 엔진은 스토리지 엔진의 API를 호출하며 실제 필요한 데이터를 요청하고 조작함
MySQL에서 스토리지 엔진이란?
MySQL은 다른 상용 DBMS와는 다르게 DB 엔진(스토리지 엔진)에 완벽하게 최적화되어 있지 않다. 이는 다양한 스토리지 엔진에서 동작할 수 있어야 하기 때문이다.
때문에 데이터 처리가 일부 비효율적일 수 있지만 확장성에선 어느 DBMS보다 유연하다.
MySQL5.5 기준 기본 스토리지 엔진 8가지는 아래와 같다. (책에선 실무에서 많이 사용하는 MyISAM, InnoDB, Archive에 중점을 두어 설명하고 있음)
- InnoDB
- MyISAM
- ARCHIVE
- MRG_MYISAM
- BLACKHOLE
- CSV
- MEMORY
- FEDERATED
MyISAM 스토리지 엔진
- MySQL에서 가장 오래된 스토리지 엔진이며 MySQL 5.5 이전까지 기본 스토리지 엔진
- 파일 기반 스토리지 엔진
- 데이터에 대한 키, 즉 인덱스만 메모리에 올려서 처리함
- 데이터는 메모리에 적재하지 않고 디스크에서 바로 접근
- 트랜잭션을 지원하지 않음
- 테이블 단위 잠금 (Table Level Lock)으로 데이터 변경을 처리
- 여러 세션에서 특정 테이블의 데이터를 변경하면 성능이 상당히 저하됨
- 텍스트 전문을 검색하는 풀텍스트 인덱싱과 지리 정보를 저리할 수 있는 지오메트릭 스파셜 인덱싱과 같은 기능을 제공하지만 이 기능들을 사용하면 테이블 파티셔닝을 사용할 수 없다는 제약도 있음
InnoDB 스토리지 엔진
- MySQL 5.5부터 기본 스토리지 엔진
- MySQL에서 유일하게 트랜잭션을 지원함
- 일반적인 서비스에 가장 많이 사용됨
- 다중 버전 동시성 제어 메커니즘 (Multiversion Concurrency Control, MVCC)를 제공
- 행 단위 잠금으로 데이터 변경 작업을 수행
- MyISAM은 인덱스만 메모리에 올리지만 InnoDB는 인덱스와 데이터를 모두 메모리에 올리는 점이 가장 큰 차이점
- 인덱스와 데이터가 메모리에 적재되기 때문에 메모리 버퍼 크기 (InnoDB_Buffer_Pool_Size)가 DB 성능에 큰 영향을 미침
- Primary Key는 클러스터 인덱스(인덱스 순서로 데이터가 저장되어 있는 구조)로 구성됨
- 만약 테이블에 Primary Key가 정의되어 있지 않으면 Unique Key가 클러스터 인덱스로 구성되고, Unique Key 또한 정의되어 있지 않으면 내부적으로 6바이트의 키를 생성해서 Primary Key로 사용 (해당 Key값은 사용자가 참조할 수 없음)
- Primary Key 외에 인덱스는 Primary Key를 값으로 가짐
- 인덱스를 통해 Primary Key를 가져오고, 가져온 Primary Key로 실제 데이터에 접근하는 방식
Archive 스토리지 엔진
- 로그 수집에 적합한 스토리지 엔진
- 데이터가 메모리상에서 압축되고, 압축된 상태로 디스크에 저장
- 행 단위 잠금 가능
- 한번 INSERT 된 데이터는 UPDATE, DELETE 연산이 불가능
- 인덱스를 지원하지 않음
- 원시 로그, 즉 가공이 한 번 필요한 데이터 수집에는 상당히 효율적
- 테이블 파티셔닝을 지원하므로 일별 또는 월별로 데이터를 관리할 수 있음
MySQL은 데이터를 어떻게 처리할까?
단일 코어 처리
기본적으로 MySQL은 SQL을 병렬 처리하지 않고, 단일 코어로만 데이터를 처리한다. 따라서 MySQL 입장에서는 CPU 코어를 늘리는 Scale-Out 보다는 단위 처리량이 좋은 CPU로 Scale-Up 하는 것이 훨씬 좋다.
Nested Loop Join 알고리즘
MySQL은 테이블 조인을 Nested Loop Join 알고리즘으로만 처리한다.
Nested Loop Join은 선행 테이블 A의 조건 검색 결과값 하나하나를 테이블 B와 비교하여 조인하는 방식이다. 프로그램적으로 풀자면 이중 for문과 유사하다.
for each row in t1 matching range { // ➊
for each row in t2 matching reference key { // ➋
for each row in t3 {
if row satisfies join conditions,
send to client
}
}
}
즉, 테이블 B를 스캔하는 횟수는 테이블 A에서 조회된 결과값의 개수이다. 결국 처리할 데이터가 많아지면 쿼리 효율이 기하급수적으로 떨어진다.
때문에 DB 내부에서는 이것보다 한 단계 업그레이드된 Block Nested Loop Join 방식으로 처리한다.
Block Nested Loop Join 알고리즘
Nested Loop로 테이블에 접근하기보다는 조인이 필요한 데이터들을 버퍼에 채우고, 한 번에 테이블에 접근하여 조인을 효율적으로 개선한 방법이다.
먼저 선행 테이블 A에서 조인할 데이터를 찾아서 조인 버퍼가 가득 찰 때까지 채운다. (위 그림에선 테이블 A 연두색만큼의 데이터가 조인 버퍼를 가득 채우는 데이터 양이다.)
조인 버퍼가 가득 차면 테이블 B의 데이터를 스캔하면서 조인 버퍼에 있는 데이터와 매칭되는지 체크한다. 만약 데이터가 일치하면 조인 결괏값으로 내보낸다.
조인 버퍼 안의 모든 데이터를 비교하는 과정이 종료되면 조인 버퍼를 비우고, 다시 테이블 A에서 데이터를 조회하여 조인 버퍼를 채우는 과정을 반복한다.
이러한 과정은 조인 버퍼에 더 이상 데이터를 채울 수 없는 시점, 즉 테이블 A 조건에 해당하는 데이터를 모두 처리할 때까지 반복해서 수행한다.
테이블 B를 스캔하는 횟수는 조인 버퍼에 데이터가 적재되는 횟수와 동일하다.
BNL 성능 테스트
> explain select STRAIGHT_JOIN * from t1 a INNER JOIN t2 b ON a.c1=b.c1 and a.c1 between 1 and 50;
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------+
| 1 | SIMPLE | a | NULL | ALL | NULL | NULL | NULL | NULL | 4956 | 11.11 | Using where |
| 1 | SIMPLE | b | NULL | ALL | NULL | NULL | NULL | NULL | 49720 | 10.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------+
> set profiling=1;
> select STRAIGHT_JOIN * from t1 a INNER JOIN t2 b ON a.c1=b.c1 and a.c1 between 1 and 50;
> select STRAIGHT_JOIN * from t1 a INNER JOIN t2 b ON a.c1=b.c1 and a.c1 between 1 and 500;
> select STRAIGHT_JOIN * from t1 a INNER JOIN t2 b ON a.c1=b.c1 and a.c1 between 1 and 5000;
> show profiles;
+----------+-------------+-------------------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+-------------+-------------------------------------------------------------------------------------------+
| 1 | 0.17276250 | select STRAIGHT_JOIN * from t1 a INNER JOIN t2 b ON a.c1=b.c1 and a.c1 between 1 and 50 |
| 2 | 1.57978475 | select STRAIGHT_JOIN * from t1 a INNER JOIN t2 b ON a.c1=b.c1 and a.c1 between 1 and 500 |
| 3 | 15.68152675 | select STRAIGHT_JOIN * from t1 a INNER JOIN t2 b ON a.c1=b.c1 and a.c1 between 1 and 5000 |
+----------+-------------+-------------------------------------------------------------------------------------------+
BNL DISABLE 후 성능 테스트
> explain select /*+ NO_BNL(b) */ STRAIGHT_JOIN * from t1 a INNER JOIN t2 b ON a.c1=b.c1 and a.c1 between 1 and 50;
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| 1 | SIMPLE | a | NULL | ALL | NULL | NULL | NULL | NULL | 4956 | 11.11 | Using where |
| 1 | SIMPLE | b | NULL | ALL | NULL | NULL | NULL | NULL | 49720 | 10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
> set profiling=1;
> select /*+ NO_BNL(b) */ STRAIGHT_JOIN * from t1 a INNER JOIN t2 b ON a.c1=b.c1 and a.c1 between 1 and 50;
> select /*+ NO_BNL(b) */ STRAIGHT_JOIN * from t1 a INNER JOIN t2 b ON a.c1=b.c1 and a.c1 between 1 and 500;
> select /*+ NO_BNL(b) */ STRAIGHT_JOIN * from t1 a INNER JOIN t2 b ON a.c1=b.c1 and a.c1 between 1 and 5000;
> show profiles;
+----------+-------------+------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+-------------+------------------------------------------------------------------------------------------------------------------+
| 1 | 0.83281350 | select /*+ NO_BNL(b) */ STRAIGHT_JOIN * from t1 a INNER JOIN t2 b ON a.c1=b.c1 and a.c1 between 1 and 50 |
| 2 | 8.31119800 | select /*+ NO_BNL(b) */ STRAIGHT_JOIN * from t1 a INNER JOIN t2 b ON a.c1=b.c1 and a.c1 between 1 and 500 |
| 3 | 85.36058425 | select /*+ NO_BNL(b) */ STRAIGHT_JOIN * from t1 a INNER JOIN t2 b ON a.c1=b.c1 and a.c1 between 1 and 5000 |
+----------+-------------+------------------------------------------------------------------------------------------------------------------+NO_BNL 힌트를 이용해서 BNL을 수행하지 않게 했다. BNL 방식에 비해 5배 이상 느린 것을 확인할 수 있다.
Reference