개발자들을 위한 MySQL 사용 가이드

by RastaLion · 2024년 6월 22일

MySQL 개발 Tip

DB를 사용하는데 있어서 개발만 하던 사람이 DB를 같이 공부하면서 두개를 같이 최적화 한다는게 참 어려운 일입니다. 하지만 결국 DB가 들어가는 모든 웹 그리고 앱 서비스들은 결국 DB 최적화에 따라 성능이 결정나곤 합니다. 오늘은 MySQL을 사용하면서 이렇게 맞춰서 개발하면 백엔드 설계에 있어 많이 도움되는 팁을 적어보려 합니다.

데이터 정규화 필수

MySQL은 RDBMS라고 명시된 관계형 데이터베이스
관계형 데이터베이스라 함은 “쪼개서 저장하고 합쳐서 출력한다”를 기본으로 한다.
데이터 모델을 정규화하는 것이 성능과 데이터 정합성과 무결성을 지키는데 유리하다.
정규화는 DB의 I/O를 줄이는 가장 기본적인 방법이며 I/O가 DB의 성능을 좌우한다.
하나의 Row 데이터를 불러오는데 있어 컬럼이 많으면 블록이 많아지고 당연히 I/O가 늘어난다.
NULL 데이터가 많은 것은 인덱스 전략에도 안좋은 선택이다.
JSON 데이터를 하나의 컬럼에 밀어 넣는 구조는 정말 나쁜 구조이다.

데이터 타입 선택

가장 작은 데이터 타입을 사용한다.

1. 저장 공간 절약

작은 데이터 타입을 사용하면 각 행에 할당되는 저장 공간이 줄어들어 전체 데이터베이스 크기를 줄일 수 있습니다. 이는 디스크 사용량을 줄이고, 더 많은 데이터를 동일한 디스크 공간에 저장할 수 있게 합니다
예를 들어, `TINYINT`는 1바이트를 사용하고, `INT`는 4바이트를 사용합니다. 1억 개의 행이 있을 때 `TINYINT`를 사용하면 100MB를 사용하지만, `INT`를 사용하면 400MB를 사용하게 됩니다.

2. 인덱스 크기 축소

인덱스는 테이블의 특정 컬럼에 대해 생성되며, 인덱스 크기는 해당 컬럼의 데이터 타입에 따라 달라집니다. 작은 데이터 타입을 사용하면 인덱스 크기도 작아지므로 인덱스를 메모리에 더 많이 적재할 수 있습니다. 이는 검색 속도를 향상시키고 인덱스의 효율성을 높입니다.

3. 메모리 사용량 감소

작은 데이터 타입을 사용하면 MySQL이 메모리에서 데이터를 처리할 때 필요한 공간이 줄어듭니다. 이는 데이터베이스 캐시나 버퍼 풀의 효율성을 높이고, 성능을 향상시킬 수 있습니다.

4. 네트워크 대역폭 절약

작은 데이터 타입을 사용하면 네트워크를 통해 데이터를 전송할 때 필요한 대역폭이 줄어듭니다. 이는 데이터 전송 속도를 높이고, 네트워크 리소스를 효율적으로 사용할 수 있게 합니다.

문자열 타입에 따른 비교

char

고정 길이 문자열로 지정된 길이보다 짧은 문자열은 나머지 공간을 공백으로 채웁니다.
최대 255 문자 길이까지 사용할 수 있습니다.

장점

고정 길이이므로 성능이 일정하고, 특정 길이의 데이터를 저장할 때 효율적입니다. 따라서 UUID 같이 고정된 길이의 문자열을 저장할 때 사용하는 게 좋습니다.
인덱싱이 효율적입니다.

단점

짧은 문자열에 대해서도 지정된 길이만큼 저장 공간을 사용하므로 비효율적일 수 있습니다.
공백이 포함되므로 실제 데이터보다 더 많은 저장 공간을 차지할 수 있습니다.

varchar

가변 길이 문자열 데이터 타입으로 최대 길이와 실제 데이터 길이에 따라 저장 공간을 사용합니다.
최대 65,535 문자 길이까지 사용할 수 있습니다.

장점

저장 공간을 효율적으로 사용합니다. (실제 데이터 길이만큼 저장)
다양한 길이의 문자열 데이터를 처리하기에 적합합니다.

단점

가변 길이 이므로 INSERT, UPDATE 같은 쓰기 작업시 성능이 일정하지 않을 수 있습니다.
문자열 길이를 자주 변경하는 경우 성능이 저하 될 수 있습니다.

TEXT

가변 길이의 매우 큰 문자열 데이터 타입.
최대 65,535 문자 길이까지 저장 가능.
별도의 저장 공간에 저장되고, 실제 데이터가 저장된 위치를 포인터로 참조합니다.

장점

매우 큰 문자열 데이터를 저장할 수 있습니다.
데이터 길이에 제한이 거의 없어서 다양한 용도로 사용 가능합니다.

단점

인덱싱이 제한적이어서 성능이 떨어질 수 있습니다.
전체 행 크기 제한으로 인해 테이블의 다른 컬럼과의 조합에 제약이 있을 수 있습니다.
검색 시 성능이 떨어질 수 있습니다.

날짜와 시간

Datetime과 Timestamp 비교

특성	DATETIME	TIMESTAMP
저장 형식	‘YYYY-MM-DD HH:MM’	유닉스 타임스탬프
범위	‘1000-01-01 00:00:00’ ~ ‘9999-12-31 23:59:59’	‘1970-01-01 00:00:01’ ~ ‘2038-01-19 03:14:07’
시간대 영향	시간대와 무관	서버 시간대에 따라 저장 및 표시
저장 공간	8바이트 (문자)	4바이트 (숫자)
사용 사례	시간대 독립적인 데이터 저장, 넓은 범위 필요 시	시간대 의존적인 데이터 저장, 지역에 따른 자동 시간 설정 필요 시

Timestamp

하나의 서비스에서 글로벌 서비스를 해야할 때

Datetime

고정된 시간대와 넓은 범위의 날짜 저장이 필요할 때

꿀팁!

함수를 이용한 데이터 저장

INET_ATON

IP 저장시 `INET_ATON` 함수를 사용하면 문자열이 아닌 int unsigned 로 저장 할 수 있습니다.

IP 주소를 저장할 때 정수로 변환하여 삽입

INSERT INTO ip_addresses (ip_address) VALUES (INET_ATON('192.168.0.1'));

정수로 변환된 IP 주소를 이용하여 검색

SELECT * FROM ip_addresses WHERE ip_address = INET_ATON('192.168.0.1');
+--------------------------+
| INET_ATON('192.168.0.1') |
+--------------------------+
| 3232235521               |
+--------------------------+

정수로 변환하면 4바이트만 사용합니다.
정수형 데이터로 변환하면 IP 주소 형식의 무결성을 유지할 수 있습니다.
잘못된 형식의 IP 주소가 저장되는 것을 방지 할 수 있습니다.

UUID

UUID_TO_BIN 및 BIN_TO_UUID
- 저장 공간 절약: UUID를 바이너리 형식으로 저장하면 문자열로 저장하는 것보다 공간을 절약할 수 있습니다.
- 성능 향상: 바이너리 형식은 검색 및 비교 시 문자 타입보다 성능이 더 좋습니다.
- 단점: Join key로는 직관적이지 않은 데이터. 함수를 사용하지 않으면 알아보기 힘듬.

Stored Procedure, Trigger, Event Scheduler 사용 자제

1. 유지보수의 어려움

코드 분산: 비즈니스 로직이 데이터베이스에 내장되어 애플리케이션 코드와 분리됩니다. 이는 코드 베이스가 분산되어 유지보수가 어렵게 만듭니다.
디버깅 어려움: 스토어드 프로시저 내의 오류를 디버깅하기가 어렵습니다. 일반적인 IDE에서 제공하는 디버깅 기능을 사용할 수 없고, 디버깅 도구가 제한적입니다.

2. 이식성 문제

DBMS 종속성: 스토어드 프로시저는 특정 DBMS의 문법과 기능에 종속됩니다. 이는 데이터베이스를 다른 DBMS로 마이그레이션할 때 큰 문제가 될 수 있습니다.
버전 호환성: DBMS 버전 업그레이드 시 스토어드 프로시저의 호환성 문제가 발생할 수 있습니다.

3. 성능 문제

캐싱 어려움: 데이터베이스 캐싱 솔루션(예: Memcached, Redis)과의 통합이 어렵습니다. 애플리케이션 레벨에서 쿼리를 캐싱하는 것이 더 쉽습니다.
스케일링 어려움: 스토어드 프로시저는 수직 확장(Scale-Up)에는 적합할 수 있지만, 수평 확장(Scale-Out)에는 제한적입니다.

4. 개발 생산성 저하

툴링 및 자동화 부족: 일반적인 애플리케이션 개발 도구와의 통합이 부족하여, 버전 관리, 테스트, 배포 등의 자동화가 어렵습니다.
협업 어려움: 애플리케이션 코드와 별도로 관리되므로 협업이 어렵고, 변경 사항 추적이 어렵습니다.

5. 비즈니스 로직의 중복

코드 중복: 동일한 비즈니스 로직이 애플리케이션 코드와 스토어드 프로시저에 중복될 수 있습니다.
일관성 문제: 비즈니스 로직이 중복될 경우, 애플리케이션과 데이터베이스 간의 일관성을 유지하는 것이 어려울 수 있습니다.

6. 보안 문제

SQL 인젝션: 스토어드 프로시저가 안전하지 않게 작성되면, SQL 인젝션 공격에 취약할 수 있습니다.
권한 관리: 스토어드 프로시저가 다양한 권한으로 실행되기 때문에, 권한 관리가 복잡해질 수 있습니다.

Stored Procedure

Oracle이나 MSSQL을 이용해 개발을 많이 해온 사람들은 프로시저를 적극적으로 사용하고는 합니다. 다만 MySQL의 프로시저는 절대 오라클과 MSSQL 수준의 성능을 기대해서는 안됩니다. 윤하 벨트 터지듯 메모리 터지는 걸 겪으실 수 있습니다.

SQL 파싱에 대한 문제

SQL 파싱은 SQL 문장을 분석하고, 실행 계획을 수립하는 과정입니다. 이 과정은 시간이 걸리며, 여러 번 실행될 경우 성능에 영향을 미칠 수 있습니다. 특히, 스토어드 프로시저 내에서 복잡한 쿼리가 자주 실행될 경우 이러한 파싱 오버헤드가 누적되어 성능 저하를 유발할 수 있습니다.

SQL 파싱 오버헤드: 스토어드 프로시저 내에서 실행되는 각 SQL 문장은 실행될 때마다 파싱됩니다. 이는 성능 저하를 초래할 수 있습니다.
리터럴 파싱: 스토어드 프로시저 내의 SQL 문장에서 리터럴 값을 사용할 때, MySQL은 이러한 리터럴 값을 캐싱하지 않고 매번 파싱합니다.

매번 실행시 컴파일 되는 문제

캐싱 부족: 다른 DBMS와 달리, MySQL은 스토어드 프로시저를 메모리에 캐시하지 않습니다. 따라서 매번 호출 시 프로시저가 다시 컴파일됩니다.
컴파일 오버헤드: MySQL의 스토어드 프로시저는 실행될 때마다 컴파일됩니다. 이는 실행 성능에 부정적인 영향을 미칠 수 있습니다.

인덱스

인덱스 사용할 때 가장 많이 하는 실수

인덱스를 만들때 개발자들이 가장 많이 하는 실수는 컬럼이 조건절에 있다고 개별 컬럼들을 하나씩 싱글 인덱스로 잡는것 입니다. 예를 들면 조건절에 A, B, C 라는 컬럼이 있을때 A인덱스 하나, B에도 인덱스 하나, C에도 인덱스 하나 다 인덱스를 따로 만드는 것인데요. 이 경우 MySQL 옵티마이저는 카디널리티가 가장 높은 컬럼의 인덱스만 사용하고 다른 인덱스는 사용하지 않습니다.

옵티마이저가 첫번째 선택한 컬럼의 인덱스를 제외하고는 불필요한 인덱스만 늘린것입니다. 성능이 우선이라면 카디널리티와 순서를 잘 파악해서 복합 인덱스를 만드는 것이 좋습니다. A-B 조건, A-C조건 이런 식으로 검색이 많이 된다면, 데이터가 별로 없는 초기에는 인덱스가 A만 있으면 되는데, 데이터가 많아지고 쓰기보다 읽기가 중요하다면, A-B, A-C 이렇게 두개의 복합 인덱스를 만드는 방법도 있습니다. 하지만 복합 인덱스는 쓰기에서 느릴 수 있으니 서비스 성향에 맞게 생성을 해야 합니다.

인덱스가 있을 때 DML 작업이 느려지는 이유

인덱스 자체에 대한 업데이트

트리 구조 업데이트: 대부분의 인덱스는 B-트리 또는 변형된 B-트리 구조로 구현됩니다. 새로운 데이터를 삽입하거나 기존 데이터를 변경할 때, 트리 구조를 유지하기 위해 노드 분할, 병합, 재배치 등의 작업이 필요할 수 있습니다.
추가 작업: 데이터를 삽입하거나 업데이트할 때마다, MySQL은 인덱스 구조를 갱신해야 합니다. 이는 추가적인 I/O와 계산을 필요로 하며, 그 결과 쓰기 작업이 느려질 수 있습니다.

인덱스가 많을 때 발생하는 문제

다중 인덱스: 하나의 테이블에 여러 개의 인덱스가 존재할 경우, 각 인덱스를 개별적으로 갱신해야 합니다. 인덱스 수가 많을수록 쓰기 작업이 느려질 수 있습니다.
각 인덱스의 갱신 비용: 각 인덱스는 해당 인덱스가 적용된 컬럼의 값을 기준으로 업데이트되어야 하므로, 인덱스가 많을수록 이 작업의 비용이 증가합니다

복합 인덱스 사용

복잡한 인덱스: 복합 인덱스(여러 컬럼을 포함하는 인덱스)나 표현식 기반 인덱스는 더 복잡한 구조를 가지며, 이를 갱신하는 작업이 단순 인덱스보다 더 많은 자원을 필요로 할 수 있습니다.
유지 관리 비용 증가: 복합 인덱스의 경우, 각 구성 요소의 값을 모두 고려하여 갱신해야 하므로, 단일 인덱스에 비해 쓰기 작업이 더 느려질 수 있습니다.

인덱스 생성 Tip

필요한 인덱스만 생성합니다.
쓰기가 많은 LOG 성 테이블인 카디널리티가 높은 단일 컬럼을 기준으로 인덱스를 생성하는게 좋습니다.
읽기 속도가 중요한 데이터는 복합 인덱스 사용시 빠른 응답속도를 보장합니다. 카디널리티가 높은 컬럼부터 순서대로 조합합니다.
인덱스에서 가장 마지막에 동작하는 것이 범위 검색(Range Scan) 입니다. 컴파운드 인덱스 생성시 레인지 컬럼이 다른 조건의 컬럼 앞에 있으면, 레인지 조건 뒤에 위치한 컬럼들은 의미가 없어 집니다.
인덱스가 있어도 조건 절에 함수를 사용하여 데이터 변형이 발생하면 인덱스를 타지 않습니다. 이럴땐 Function Based Index를 사용할 수 있습니다.
Like 절을 사용시 ‘%단어’ 로 시작하는 구문은 무조건 Table Full Scan으로 동작하여, DB에 부하, 장애를 일으킬 확률이 높습니다. 문자열 검색이 많은 경우 Fulltext 인덱스를 사용하고, 데이터가 너무 많아져 Fulltext에도 문제가 생시면 서치 엔진을 사용할 수 있는 MongoDB나 엘라스틱 서치로 이관하세요.

MySQL에서 하지말아야 할 것들

Count(*) 함수를 검증 로직에 태우는 것

MySQL은 Oracle 처럼 row_num을 저장하지 않기 때문에 카운트 쿼리가 매우매우 느립니다.
특히 개발자 분들은 페이지 네이션 할 때 전체 카운트를 세는걸 많이 하는데, 데이터가 많아지면 결과값 리턴이 엄청 늦어집니다.
검증 로직이 아니더라도, 운영중에 데이터가 많은 테이블에 count(*) 하는건 매우 위험한 일입니다.

Primary Key는 되도록 int 계열 데이터 타입에 Auto Increment를 사용

MySQL PK는 기본적으로 클러스터드 인덱스이기 때문에 내림차순에 의한 물리적인 재배열이 일어납니다.
유니크 하다고 해서 순서가 없는 랜덤 값으로 PK가 설정되는 경우, 문자나 숫자의 순서대로 재배열이 일어날 수 있습니다. 따라서 PK로 생성 시점에 따른 정렬이 어려울 수 있고, 데이터 재배열이 자주 일어날 수 있습니다.
물론 성능을 위해 uuid v1의 시퀄셜 데이터를 binary 함수를 이용해 키로 사용하는 방법도 있습니다. 다만 FK로 사용할 경우 바이너리 형식의 데이터는 직관적이지 않고, FK키를 이용해 join 및 조건을 주는 경우, 다수에 의해 동시에 개발과 운영이 되는 환경에서는 적합하지 않을 수 있습니다.

복합키(Composite Key)로 PK를 만드는 것

여러 키를 조합해서 유니크 값을 가질때 그 것을 PK로 사용할 수 있지만, 문제가 발생할 수 있습니다.

인덱스 크기 증가: 복합키를 기본 키로 사용할 경우, 인덱스의 크기가 증가하게 됩니다. 이는 데이터베이스의 전체 성능에 영향을 미칠 수 있습니다.
인덱스 검색 비용 증가: 인덱스 검색 시 복합키의 각 구성 요소를 순차적으로 비교해야 하므로, 단일 컬럼 키에 비해 검색 비용이 증가할 수 있습니다.
쿼리 복잡성 증가: 복합키를 사용하면 쿼리를 작성할 때 항상 여러 컬럼을 함께 사용해야 하므로 쿼리가 복잡해질 수 있습니다.
가독성 저하: 여러 컬럼을 조건으로 사용하는 쿼리는 가독성이 떨어지고, 유지보수가 어려워질 수 있습니다.
데이터 중복 가능성: 복합키가 아닌 개별 컬럼에 대해 중복된 값이 허용될 수 있습니다. 이는 데이터 무결성에 영향을 미칠 수 있습니다.
외래 키 참조 복잡성: 다른 테이블에서 복합키를 외래 키로 참조하려면 여러 컬럼을 함께 참조해야 하므로, 외래 키 관계가 복잡해질 수 있습니다.
참조 무결성 유지 어려움: 복합키를 외래 키로 참조하는 경우, 참조 무결성을 유지하는 것이 더 어렵고, 쿼리 작성 시 실수가 발생할 가능성이 높아집니다.
인덱스 순서 중요성: 복합키의 컬럼 순서에 따라 인덱스의 효율성이 달라질 수 있습니다. 잘못된 순서로 설정된 복합키는 성능에 부정적인 영향을 미칠 수 있습니다.

물리적 모델에 FK 적용하는 것

ERD를 그리거나 테이블 정의서를 작성할 때는 FK를 표기하나, 실제 데이터 모델에서 FK를 적용하면 문제가 많이 발생합니다.

쓰기 작업의 성능 저하: 외래 키 제약 조건은 삽입, 업데이트, 삭제 작업 시 추가적인 무결성 검사를 요구합니다. 이는 쓰기 작업의 성능을 저하시킬 수 있습니다.
잠금 경합: FK 제약 조건으로 인해 참조 무결성을 유지하기 위해 관련 테이블에 잠금이 발생할 수 있습니다. 이는 동시성 문제를 일으키고, 시스템의 전체 성능을 저하시킬 수 있습니다.
데이터 마이그레이션: 데이터베이스 스키마가 자주 변경되거나, 데이터 마이그레이션 작업이 빈번한 경우 FK 제약 조건은 이러한 작업을 복잡하게 만들 수 있습니다.
데이터 통합: 다양한 소스에서 데이터를 통합하거나, 분산 데이터베이스 환경에서 데이터를 처리할 때 FK 제약 조건은 제약이 될 수 있습니다.
분산 처리의 어려움: 분산 데이터베이스 환경에서는 물리적으로 분리된 노드 간의 FK 제약 조건을 유지하기 어렵습니다. 각 노드가 독립적으로 작동 함으로 참조 무결성을 보장하기 어렵습니다.
어플리케이션에서의 관리: 외래 키 제약 조건을 어플리케이션 레벨에서 관리하면 더 유연하고, 성능 최적화가 가능합니다. 이는 특히 고성능, 고가용성 시스템에서 중요합니다.
복잡한 로직 처리: 일부 복잡한 비즈니스 로직은 데이터베이스의 FK 제약 조건으로 표현하기 어렵습니다. 이러한 경우, 어플리케이션 코드에서 로직을 처리하는 것이 더 적합합니다.
이식성: 여러 종류의 데이터베이스 시스템을 사용하거나, 데이터베이스 시스템을 교체할 계획이 있는 경우, FK 제약 조건을 물리적 모델에서 제외하면 이식성이 높아집니다. 데이터베이스 시스템마다 FK 제약 조건의 구현 방식이나 성능 특성이 다를 수 있기 때문입니다.
개발 및 테스트 편의성: 개발 및 테스트 환경에서는 데이터베이스의 제약 조건을 최소화하여 더 빠르게 테스트 데이터를 생성하고, 다양한 시나리오를 실험해 볼 수 있습니다. FK 제약 조건이 있으면 이러한 작업이 번거로울 수 있습니다.

JSON 타입 컬럼 사용시 나타나는 문제점

MySQL8 버전부터는 JSON 컬럼을 지원합니다. 하지만 물론 JSON 처리할 수 있는 다른 DB들과 비교했을 때 MySQL의 JSON 컬럼은 아무런 이점이 없고, 아직까지는 단점 투성 입니다.

파싱 오버헤드: JSON 데이터를 삽입하거나 조회할 때 MySQL은 JSON 문자열을 파싱해야 합니다. 이 과정은 시간이 걸리며 성능에 부정적인 영향을 미칠 수 있습니다.
인덱스 제한: JSON 컬럼 내부의 데이터는 인덱싱이 제한적입니다. MySQL은 JSON 컬럼 자체에는 인덱스를 만들 수 없으며, 특정 키나 값에 대한 인덱싱은 가상 컬럼(generated column)을 사용해야 합니다.
유효성 검증: JSON 컬럼은 구조가 유연하여 스키마 유효성 검증을 수행하지 않습니다. 이로 인해 데이터 일관성이 떨어질 수 있습니다.
데이터 무결성: 잘못된 데이터 형식이나 구조를 가진 JSON 데이터를 삽입하는 것을 방지하기 어렵습니다.
복잡한 쿼리: JSON 데이터를 쿼리하는 구문이 복잡해질 수 있습니다. 특히 중첩된 구조를 다룰 때 쿼리의 가독성과 유지보수성이 떨어집니다.
JOIN 제한: JSON 컬럼 내부 데이터를 다른 테이블과 JOIN하는 작업이 어렵습니다.
참조 무결성: JSON 컬럼 내부의 데이터는 외래 키 제약 조건을 통해 참조 무결성을 유지하기 어렵습니다.
데이터 일관성: 서로 다른 레코드 간의 JSON 데이터 일관성을 유지하는 것이 어렵습니다.
저장 공간 비효율성: JSON 데이터는 문자열로 저장되므로, 같은 데이터를 구조화된 형식(예: INT, VARCHAR)으로 저장하는 것보다 더 많은 저장 공간을 차지할 수 있습니다.
메모리 사용 증가: JSON 데이터를 처리할 때 파싱과 관련된 메모리 사용량이 증가할 수 있습니다.
제한된 SQL 기능: JSON 컬럼은 일반적인 SQL 기능(예: 그룹화, 집계 함수)과의 호환성이 떨어질 수 있습니다.
호환성 문제: JSON 데이터를 사용하는 다른 애플리케이션이나 서비스와의 호환성 문제가 발생할 수 있습니다.
JSON 컬럼을 사용하고 싶다면 그냥 MongoDB를 사용하는 것을 추천.

MySQL을 사용해서 무언가 개발을 한다고 할 때 이 정도만 지켜줘도, DBA나 다른 DevOps 엔지니어들로부터 DB 좀 치는데, 개발 좀 하는데 소리 들을 수 있습니다. 그리고 나중에 DB 마이그레이션이나 고도화 할 때도 훨씬 쉽게 할 수 있습니다.

칭찬도 받고 실력도 키우고, 성능도 잡을 수 있는 그런 개발 라이프 즐기시길 바랍니다.

Post Views: 96

소셜 미디어로 공유하기