회사에서 제가 맡고 있는 파트는 조회성 API를 제공하고 있습니다.
최근에 DBA 분들과 함께 묵혀뒀던 Slow Query를 개선하는 작업을 진행하게 되었습니다.
쿼리 개선요청 드렸는데 DBA 분들이 제안해주시는 걸 못알아 들으면 안될 것 같아서 회사에 있는 책 한권을 집어왔습니다.
무슨책이냐면... -> 업무에 바로 쓰는 SQL 튜닝
마침 저희 파트도 MySQL을 사용중이거든요!
책을 읽으면서 새롭게 알게된 내용을 정리해보려고 합니다.
MySQL과 MariaDB
- MySQL은 DBMS입니다
- MySQL은 오픈소스로 배포 되었지만 2010년 오라클 인수로 상용버전/커뮤니티 버전(무료)으로 구분됩니다
- MariaDB는 MySQL의 소스를 기반으로 하여 탄생되었고, 완전한 오픈소스 입니다
Oracle과 MySQL
Oracle과 MySQL은 데이터가 저장되는 스토리지 구조에 차이가 있습니다.
- Oracle은 통합된 스토리지 하나를 공유합니다
- 어느 DB 서버에 접속하더라도 SQL는 같은 결과를 출력합니다
- MySQL은 물리적인 DB 서버 마다 독립적으로 스토리지를 할당합니다
- 보통 Master - Slave 구조로 구성합니다
- Master: Read/Write 모두 가능
- Slave: Read만 가능
MySQL에서의 쿼리오프로딩
DB 서버 트랜잭션에서 Read/Write를 분리하여 DB처리량을 증가시키는 성능향상 기법
Oracle과 MySQL은 사용하는 join 알고리즘이 다릅니다.
- Oracle: 중첩 루프 조인(nested loop join), 정렬 병합 조인, 해시 조인 등등
- MySQL: 대부분 중첩 루프 조인
그래서 MySQL 실행계획을 따보면 거의 nested loop join으로 표시된거였군요
Oracle과 MySQL은 구문에 차이가 있습니다.
- NVL(Oracle)/IFNULL(MySQL)
- ROWNUM/LIMIT
- SYSDATE/NOW
- DECODE/IF
- TO_CHAR/DATE_FORMAT
등등...
Oracle과 MySQL은 자동 증갓값 관리방법에 차이가 있습니다.
- Oracle:
sequence라는 오브젝트를 생성하여 사용합니다 - MySQL:
auto_increment를 사용하거나sequence오브젝트를 생성하여 사용합니다
Storage 엔진(= DB 엔진 )?
사용자가 요청한 SQL문을 토대로 DB에 저장된 디스크나 메모리에서 필요한 데이터를 가져오는 역할을 수행하는 것
스토리지 엔진은 여러개가 있을 수 있습니다
- InnoDB, MyISAM, Memory 등
우리 회사도 그렇고, 대부분 InnoDB 엔진을 사용한다고 합니다
MySQL 엔진 vs Storage 엔진?
MySQL 엔진
parser
- 사용자가 요청한 SQL을 최소 단위로 분리 -> Tree로 만듭니다
- Tree로 만들면서 문법 검사도 수행합니다
preprocessor (전처리기)
- parser가 생성한 tree를 토대로 SQL문에 구조적인 문제가 없는지 확인합니다
- DB에 존재하는 TB 대상으로 작성했는지, 접근권한이 있는지 등
optimizer
- 데이터를 빠르게 찾는 경로를 모색합니다 (실행계획 도출)
- 필요하지 않은 조건은 제거하거나 연산과정을 단순화 합니다
engine executor (엔진 실행기)
- optimizer의 실행계획을 참고하여 storage 엔진에서 데이터를 가져옵니다
- 데이터를 가져온 후 정렬하거나 join합니다 -> storage 엔진으로 부터 가져온 데이터 양이 작아야 부하가 줄어듭니다
Storage 엔진
- MySQL 엔진에서 찾아준 경로로 데이터를 가져오고, 데이터를 MySQL 엔진에게 전달해줍니다
DB 오브젝트
테이블
row
- row 수가 많아지면 데이터 접근하는 과정에 시간이 소요 되므로 파티셔닝과 같은 기법을 검토해볼 수 있습니다
column
PK
- 기본키는 index 역할도 수행합니다 (클러스터형 인덱스)
클러스터형 인덱스란?
키 값을 기반으로 테이블이나 뷰의 데이터 행을 정렬하고 저장합니다.
데이터 행 자체는 한 가지 순서로만 저장될 수 있으므로 테이블당 클러스터형 인덱스는 하나만 있을 수 있습니다.
(출처: MicroSoft 설명서)
인덱스
- 데이터 위치를 식별하고, 데이터 접근 속도를 높이고자 생성되는 키 기준으로 정렬된 오브젝트
고유 인덱스(unique index) vs 비고유 인덱스(non-unique index)
- 고유 인덱스(unique index): 인덱스를 구성하는 열들의 데이터가 유일. 중복이 있는지 검증하는 절차가 추가된다
PK와의 차이점은?
PK는 Not Null, Unique Index는 Nullable
ex) 사원TB - 사번(PK), 이름, 전화번호(Unique Index)
- 비고유 인덱스(non-unique index): unique index에서 유일한 속성만 제외한 인덱스
뷰
지금까지 내용은 대학교 Database 전공수업 + SQLD 자격증 준비하면서 배웠었거나 거기서 조금 더 자세한 내용이었어요.
다음에 정리할 내용은 join 알고리즘과 오브젝트 스캔 등 좀 더 튜닝에 관련된 용어 입니다. (책 2.2.5 ~ )
