서론

클린코드 서적스터디를 완료한 후, 더 실용적이고 현재 교육과정에 적합한 책을 찾고 있었다.
그 결과, DB 관련 서적(업무에 바로 쓰는 SQL 튜닝)을 두 번째 서적 스터디로 선택하게 되었다.

업무에 바로 쓰는 SQL 튜닝? 왜 이책을 골랐는가?

• 현재 교육과정과의 연관성
• 적절한 난이도
• 실제 개발에 적용 가능한 내용

목표

이번 스터디를 통해 mySQL에 대한 깊이 있는 이해와 실무 적용 능력을 향상시키는 것이 목표이다.

자 그럼 각설하고 1장으로 넘어가보자.

1장 MySQL 과 MariaDB 개요

1. MySQL 과 MariaDB

MySQL

• 탄생 배경: 1995년 오픈소스로 배포된 무료 DBMS.
• 현재 (2024년 기준): MySQL은 오라클에 인수된 이후, 상용 버전과 커뮤니티 버전으로 나누어짐.
• 특징:
  • 안정성: 수년간의 발전과 커뮤니티 지원을 통해 안정성을 유지.
  • 보안: 다양한 보안 기능과 업데이트를 통해 보안성이 높음.
  • 확장성: 대규모 애플리케이션에서도 활용 가능하며, 다양한 엔진을 지원.
  • 편의성: 다양한 도구와 플러그인을 통해 관리가 용이함.

MariaDB

• 탄생 배경: 2010년 MySQL이 오라클로 인수된 이후 오픈소스를 지향하는 핵심 개발자들이 MariaDB를 만듦.
• 현재 (2024년 기준): MariaDB는 2012년 이후 독자적인 버전 관리(현재 10.6.x)로 발전 중이며, 여러 글로벌 기업에서 사용.
• 특징:
  • 오픈소스 지향: 완전한 오픈소스 프로젝트로, 투명성과 커뮤니티 참여를 중시.
  • 고성능: 향상된 성능과 최적화를 통해 빠른 데이터 처리 가능.
  • 호환성: MySQL과 높은 호환성을 유지하며, 쉽게 전환 가능.
  • 추가 기능: MySQL에서 제공하지 않는 추가적인 저장 엔진과 기능 제공.

일부 기능과 옵티마이저의 성능 차이, 시스템 변수, 상태 등에서 점점 차이가 나고 있긴 하지만 SQL문의 주요 뼈대는 크게 다르지 않다.

해당 챕터에서는 MySQL로 실습을 진행한다.

MySQL 버전 확인 방법

show variables like 'version';

-- 또는

SELECT @@version;

1.1 현황

2024년 기준 Oracle과 MySQL이 1, 2위를 다투고 있다.

상용 DB인 오라클과 MySQL과 MariaDB의 구조와 기능, SQL문을 비교한다.

MySQL

• 상용 버전 : 다양한 보안 패치와 개선 기능 제공
• 무료 버전 : 제약된 기능과 GPL 라이선스

GPL 라이선스 누구나 소프트웨어를 자유롭게 사용하고, 수정하고, 배포할 수 있도록 하는 오픈소스 소프트웨어 버전

핵심 조항

1. 자유로운 사용: 누구나 소프트웨어를 어떤 목적으로든 자유롭게 사용할 수 있습니다.
2. 소스 코드 공개: 소프트웨어를 배포할 때 소스 코드도 함께 제공해야 합니다.
3. 변경 및 배포: 소프트웨어를 수정하고 변경된 버전을 배포할 수 있지만, 변경된 소프트웨어도 원래의 GPL 라이선스 하에 배포되어야 합니다.
4. 동일 라이선스 적용: GPL로 배포된 소프트웨어를 기반으로 한 파생물도 GPL로 배포되어야 합니다. 이는 "카피레프트" 원칙이라고 불립니다.

다만 GPL 라이선스라고 하더라도 이미 고객 장비에 MySQL 이 설치된 상태에서는 소스코드 공개는 의무사항이 아님

MariaDB

• 모든 버전이 GPL v2 라이선스 를 따르는 완전한 오픈소스 소프트웨어.

Oracle Database

• 오라클 데이터베이스는 1979년에 처음 출시된, 매우 안정적이고 성능이 뛰어난 상용 데이터베이스 관리 시스템이다.
• 상용 라이선스로 사용하려면 비용이 지불된다.

1.2 Oracle DB와 MySQL(Maria DB 포함)의 차이점

구조적 차이

• Oracle : 스토리지를 하나로 공유 (Shared Everything)
  • 오라클 DB는 하나의 DB 스토리지를 공유하므로 어떤 DB 서버에 접속하여 SQL문을 수행하더라도 같은 결과를 출력 가능.
• MySQL : 물리적인 DB 서버마다 독립적으로 스토리지를 할당 (Shared Nothing)

  • 이를 이중화하기 위해 클러스터나 복제 구성을 사용

    마스터-슬레이브 구조:

    • 마스터 노드: 데이터의 읽기와 쓰기 작업을 모두 처리
    • 슬레이브 노드: 읽기 작업만 처리

    즉, 여러 서버가 있더라도 각 서버의 역할이 다를 수 있음. 예를 들어, 마스터 서버는 데이터 추가나 수정 작업을 처리하고, 슬레이브 서버는 데이터 조회 작업만 처리함.

    이러한 구조는 SQL 튜닝(쿼리 최적화)에도 영향을 미칠 수 있음.

    쿼리가 실행되는 서버의 위치를 알고 튜닝을 하면, 물리적인 위치 특성을 고려한 최적화를 할 수 있다는 의미

그렇다면 어떻게 물리적인 서버의 위치로 MySQL 튜닝을 진행할 수 있을까?

애플리케이션 단에서 쿼리 오프로딩을 적용되므로 마스터노드에서는 보통 쓰기 트랜잭션을 진행하고 슬레이브노드에서는 읽기 트랜잭션을 시행한다.만약 쓰기 트랜잭션을 슬레이브 노드에 접속해서 수행한다면? 정상적인 쿼리 튜닝 결과가 도출되지 않을 것 이다.

DB 서버의 구조를 충분히 이해하고 적합한 서버에 접근하여 쿼리 튜닝을 수행하는 일은 매우 중요하다.

쿼리 오프로딩: DB 서버의 트랜잭션에서 쓰기 트랜잭션과 읽기 트랜잭션을 분리하여 DB 처리량을 증가시키는 성능 향상 기법입니다.

• 쓰기 트랜잭션: UPDATE, INSERT, DELETE
• 읽기 트랜잭션: SELECT

예시

• 새로운 사용자 추가:
  • 애플리케이션에서 새로운 사용자를 추가하는 요청이 들어옴.
  • SQL 쿼리 예시: INSERT INTO users (name, email) VALUES ('John Doe', 'john@example.com');
  • 슬레이브 노드에서 실행 시:
    • 슬레이브 노드는 읽기 전용 작업을 처리하도록 설계됨.
    • 쿼리가 실패하거나 데이터베이스에 예기치 않은 문제가 발생할 수 있음.
    • 슬레이브 노드는 데이터 쓰기 작업을 처리하지 않음.
• 사용자 정보 조회:
  • 애플리케이션에서 특정 사용자의 정보를 조회하는 요청이 들어옴.
  • SQL 쿼리 예시: SELECT * FROM users WHERE email = 'john@example.com';
  • 마스터 노드에서 실행 시:
    • 읽기 작업이 마스터 노드에 부하를 줄 수 있음.
    • 마스터 노드는 주로 쓰기 작업을 처리하도록 설계됨.
    • 읽기 작업이 많아지면 마스터 노드의 성능이 저하될 수 있음.

지원 기능 차이

1. Join 알고리즘 기능 차이

MySQL 과 오라클 DB에서 Join 알고리즘 기능에는 차이가 있음.

• MySQL : 대부분 중첩 루프 조인 방식으로 조인 수행 (8.0 부터 제약적으로 해시조인 제공)
• 오라클 DB : 중첩 루프 조인과 정렬 병합 조인, 해시 조인 방식 제공

1. Plug & Play 와 낮은 메모리 사용.

• MySQL은 필요한 DBMS를 설정해 사용할 수 있는 확장성이 특징
• 또한 상대적으로 낮은 메모리 사용으로 저사양 PC에서도 손쉽게 설치 및 개발할 수 있음
  • 오라클은 최소 수백 MB 환경이 필요하지만 MySQL은 1MB 메모리 환경에서도 DB 운영할 수 있을 만큼 오버헤드가 적다.

문법의 차이

1. NULL 값 대체하기

MySQL/MariaDB

-- 문법
IFNULL(열명, '대체값')

-- 예제
SELECT IFNULL(Col1, 'N/A') AS coll FROM tab;

Oracle

-- 문법
NVL(열명, '대체값')

-- 예제
SELECT NVL(Col1, 'N/A') AS coll FROM tab;

2. 페이징 처리

MySQL/MariaDB

-- 문법
LIMIT 숫자

-- 예제
SELECT col1 FROM tab LIMIT 5;

Oracle

-- 문법
ROWNUM < 숫자

-- 예제
SELECT col1 FROM tab WHERE ROWNUM <= 5;

3. 현재 날짜 조회

MySQL/MariaDB

-- 문법
NOW()

-- 예제
SELECT NOW() AS date;

Oracle

-- 문법
SYSDATE

-- 예제
SELECT SYSDATE AS date FROM dual;

4. 조건문

MySQL/MariaDB

-- 문법
IF(조건식, '참값', '거짓값')

-- 예제
SELECT IF(col1='A', 'apple', '-') AS coll FROM tab;

Oracle

-- 문법
DECODE(열명, '값', '참값', '거짓값')

-- 예제
SELECT DECODE(col1, 'A', 'apple', '-') AS coll FROM tab;

5. 날짜 형식 변환

MySQL/MariaDB

-- 문법
DATE_FORMAT(날짜열, '형식')

-- 예제
SELECT DATE_FORMAT(NOW(),'%Y%m%d %H%i%s') AS date;

Oracle

-- 문법
TO_CHAR(날짜열, '형식')

-- 예제
SELECT TO_CHAR(SYSDATE, 'YYYYMMDD HH24MISS') AS date FROM dual;

6. 자동 증가 값

MySQL/MariaDB

sql코드 복사
-- 문법 (AUTO INCREMENT)

-- 예제
CREATE TABLE tab (
  seq INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(20) NOT NULL
);

-- 문법 (MariaDB 10.3 이상, 시퀀스)
CREATE SEQUENCE [시퀀스명] INCREMENT BY [증감숫자] START WITH [시작숫자] NOMINVALUE OR MINVALUE [최솟값] NOMAXVALUE OR MAXVALUE [최댓값] CYCLE OR NOCYCLE CACHE OR NOCACHE;

-- 예제
CREATE SEQUENCE MARIA_SEQ_SAMPLE INCREMENT BY 1 START WITH 1 MINVALUE 1 MAXVALUE 99999999999 CYCLE CACHE;
SELECT NEXTVAL(MARIA_SEQ_SAMPLE);

Oracle

sql코드 복사
-- 문법
CREATE SEQUENCE [시퀀스명] INCREMENT BY [증감숫자] START WITH [시작숫자] NOMINVALUE OR MINVALUE [최솟값] NOMAXVALUE OR MAXVALUE [최댓값] CYCLE OR NOCYCLE CACHE OR NOCACHE;

-- 예제
CREATE SEQUENCE ORACLE_SEQ_SAMPLE INCREMENT BY 1 START WITH 1 MINVALUE 1 MAXVALUE 99999999999 CYCLE CACHE;
SELECT ORACLE_SEQ_SAMPLE.NEXTVAL FROM dual;

참고.

• MySQL/MariaDB: AUTO_INCREMENT 속성을 테이블 열에 직접 적용하거나, MariaDB 10.3 이상에서는 시퀀스 오브젝트를 사용할 수 있음.
  • AUTO_INCREMENT는 테이블당 하나의 열에만 적용 가능하며, 시퀀스 오브젝트는 MariaDB 10.3 이상에서만 사용 가능.
• Oracle: 시퀀스 오브젝트를 사용하여 자동 증가 값을 설정함.
  • 시퀀스 오브젝트를 여러 테이블과 컬럼에서 공유 가능하며, 다양한 옵션을 통해 더 유연하게 사용 가능.

7. 문자열 결합

MySQL/MariaDB

-- 문법
CONCAT(열값 또는 문자열, 열값 또는 문자열)

-- 예제
SELECT CONCAT('A', 'B') AS TEXT;

Oracle

-- 문법
열값 또는 문자열 || 열값 또는 문자열
CONCAT(열값 또는 문자열, 열값 또는 문자열)

-- 예제
SELECT 'A' || 'B' AS TEXT FROM dual;
SELECT CONCAT('A', 'B') AS TEXT FROM dual;

8. 문자열 추출

MySQL/MariaDB

-- 문법
SUBSTRING(열값 또는 문자열, 시작 위치, 추출하려는 문자 개수)

-- 예제
SELECT SUBSTRING('ABCDE', 2, 3) AS sub_string;

Oracle

-- 문법
SUBSTR(열값 또는 문자열, 시작 위치, 추출하려는 문자 개수)

-- 예제
SELECT SUBSTR('ABCDE', 2, 3) AS sub_string FROM dual;

1.3 MySQL과 MariaDB 튜닝의 중요성

최근 많은 서비스들이 MySQL 으로 여러 노드 구조를 채택하여 진행하고 있지만 내부를 들여다 보면 MySQL의 Shared Nothing Storage 구조를 신경쓰지 않은 쿼리들을 볼 수 있음.

기능적 제약사항

• 대다수의 SQL 문이 중첩 루프 조인 알고리즘으로 수행
• 상용 DBMS와는 다르게 메모리에 적재되는 캐시 기능에 한계
  • 데이터 변경 시 캐시된 내용 모두 삭제

→ 일반적인 쿼리 작성 및 튜닝이 통하지 않을 수 있음

그러므로 이러한 문제를 잘 이해하고 MySQL DBMS에 적용해보는 훈련이 필요하다!

또한 MySQL 과 MariaDB 로 서비스를 진행 시 이러한 SWOT 분석을 인지하고.

약점과 기회를 SQL 튜닝을 통해 헤쳐나갈 수 있을 것.

---

[스터디에 물어봤던 이슈 정리]

이슈 1. 마스터 슬레이브 패턴?

저는 오라클 db 와 MySQL 의 구조적 차이를 설명하면서 마스터 슬레이브 패턴의 예시를 들었는데 해당 부분이 잘 이해가 안갑니다!
스프링 애플리케이션이라면

spring:
  datasource:
    master:
      url: jdbc:mysql://master-db-url:3306/mydb
      username: username
      password: password
    slave:
      url: jdbc:mysql://slave-db-url:3306/mydb
      username: username
      password: password

이런식으로 따로 2개의 DB를 만들어 진행해서 읽기 작업과 쓰기 작업의 차이를 둔다는 것 맞나요?
슬레이브 DB는 마스터 DB 의 데이터를 복제해서 최신상태를 계속 유지한다는 거라고 이해했습니다.

해당 부분에 GaHee99님이 이해하기 쉽게 설명을 해주셨다.

[답변정리] : 마스터 슬레이브 패턴으로 구성한 DB 레플리카와 이점.

1. 스케일 아웃
   성능을 향상하기 위해 여러 개의 복제본에 분산을 부하할 수 있습니다.
   모든 쓰기와 수정 작업은 마스터에서 진행합니다. 그러나 읽기는 레플리카에서 할 수 있습니다.
   여러 개의 레플리카를 추가함으로 읽기 속도를 올림과 동시에 쓰기의 성능을 향상할 수 있습니다.
2. 데이터 보호
   레플리카는 복제 과정을 중단할 수 있기 때문에, 원본 데이터를 손상시키지 않고 레플리카에서 백업 서비스를 가동할 수 있습니다.
3. 분석
   원본 데이터에 실시간 데이터가 생성되고, 원본 데이터에 성능 이슈없이 레플리카에서 분석을 할 수 있습니다
4. 원거리 데이터 분산
   원본데이터에 접근하지 않고도, 원격 사이트에서 사용 할 로컬 데이터 복사본을 생성할 수 있습니다.

관련 아티클
Mysql Replication 구성하기