DB 설계 시 참고 / 테이블 설계 및 DB 성능 튜닝 방법
DB 테이블 설계
테이블 명명규칙
- snake_case 사용하여 소문자로 작성
- tb_ 등 prefix, suffix는 사용은 옛날 방식이므로 권장X
- 기존 테이블 명명 규칙이 있다면 따르기 (다중 규칙X)
컬럼 명명규칙
- snake_case 사용하여 소문자로 작성
- 같은 테이블명 prefix는 가급적 생략하여 간결하게 작성
변수명 짓기 사이트
https://www.curioustore.com
변수명 짓기 사이트 이용 시 시간 단축 가능합니다.
테이블 설계 시 추가할 만한 컬럼
PK 외 필수 컬럼은 없습니다. 테이블 성격이나 기능에 맞게 추가하면 됩니다.
| 컬럼명 | Data Type | 기본값 | Comment |
|---|---|---|---|
| id | int | AUTO_INCREMENT | PK (seq) |
| 테이블명_id | int | FK (타 테이블 Key) | |
| _cd | varchar | 코드명 | |
| _name | nvarchar | 이름 (한글명) | |
| content | mediumtext | 본문 (text < mediumtext < longtext) | |
| sort | int | 1 | 정렬순서 (정렬 필요 시 추가) |
| _cnt | int | 0 | 수 (SUM, 통계) |
| _yn | char(1) | 'Y or N' | 2가지로 나뉠 수 있는 상태값 (Y, N) |
| _status | int | 0 | 3가지 이상으로 나뉠 수 있는 상태값 |
| _type | varchar | '기본타입' | 타입, 유형 |
| file_path | varchar(255) | 파일경로/파일명 | |
| file_org_name | nvarchar(50) | 원본파일명 | |
| create_date | datetime | NOW() | 등록일시 (생성 이력관리 필요 시) |
| create_user_id | varchar | 등록자 (생성 이력관리 필요 시) | |
| update_date | datetime | NOW() | 수정일시 (수정 이력관리 필요 시) |
| update_user_id | varchar | 수정자 (수정 이력관리 필요 시) | |
| use_yn | char(1) | 'Y' | 사용여부 (데이터를 화면에 노출시킬 지 여부) |
| last_use_date | datetime | NOW() | 최근사용일 (마지막 사용일) |
| delete_yn | char(1) | 'N' | 삭제여부 (삭제 기능 존재 시 추가) |
PK 설계 순서
1. 유니크해야 하는 테이블이고 PK가 꼭 필요한지 판단
2. 자주 조인하기 번거로운 복합키 지정이 꼭 필요하지 않다면 단일키(Auto_increment) 사용 고려
외래키(FK)를 논리적(ERD)으로 그리는데 물리적(DB)으로 안거는 이유
- 대량의 쿼리 시 성능이 느려질 수 있습니다.
- 서브 테이블에 테스트 데이터를 만들기 불편합니다.
- 테이블 구조 변경 시 제약사항 발생합니다.
INT 등 다른 타입도 NVARCHAR로 설계하는 이유
형변환에 신경쓰지 않고, 데이터 유연성을 높이기 위해서입니다.
테이블 분할 설계
상세 데이터 중 용량이 큰 컬럼이 있다면 단순 목록 조회 시 부하를 줄이기 위해 상세 테이블을 따로 만들기도 합니다.
용량이 작은 컬럼들만 있다면 하나의 테이블로 만들어도 충분합니다.
DB 테이블 설계 예시
로그 테이블 및 통계 테이블 설계
https://0songha0.github.io/plan-design-db/2022-07-08-1
카테고리 테이블 설계
https://0songha0.github.io/plan-design-db/2022-07-13-1
메뉴 및 권한 테이블 설계
https://0songha0.github.io/plan-design-db/2024-03-18-1
공통코드 테이블 설계
https://0songha0.github.io/plan-design-db/2023-02-16-1
DB 성능 튜닝
DB 검색 성능 향상 방법
검색 속도가 낮다면 인덱스를 안타고 풀스캔 하고 있는지 확인 후 쿼리 튜닝이나 인덱스 최적화로 개선하면 됩니다.
인덱스 최적화
테이블 설계 시 PK만 제대로 잡아두고, 인덱스는 데이터가 많이 쌓여서 쿼리가 느려졌을 때 조정해도 됩니다.
DBMS는 Select 시 인덱스 테이블에서 검색(거의 10번 이내) 후 조건에 맞는 데이터 키를 테이블에서 검색하여 보여줍니다.
인덱스 걸면 인덱스 테이블을 정렬하며 관리하니 Insert, Update, Delete 성능이 안좋고 Select 성능만 좋아집니다.
인덱스 설계 방법
PK를 걸면 PK 컬럼 순서대로 PRIMARY 인덱스 또는 복합 인덱스가 자동 생성됩니다.
PK 없는 대용량 데이터에서 검색이 일어나야 한다면, 검색 조건 컬럼들에 인덱스를 걸어주면 됩니다.
복합 인덱스는 검색 조건에 들어갈 컬럼, 경우의 수가 많은 컬럼 (분포도 높은 컬럼) 순으로 설계하는 것이 좋습니다.
복합 인덱스 첫번째 컬럼이 검색조건에 없다면 나머지 검색조건 컬럼들을 각각 단일 인덱스로 잡아줘야 인덱스를 탑니다.
PK가 A, B, C인데 WHERE A = ‘‘가 있다면 나머지 B, C 검색조건도 인덱스를 타니 단일 인덱스가 없어도 됩니다.
Select 시 검색조건 컬럼 뿐 아니라 Group by 컬럼도 인덱싱 되어있으면 조회 속도가 빠릅니다.
인덱스 변경 시 주의사항
운영 중에 인덱스를 생성하면 락이 걸릴 수 있으므로, 사용자가 적은 운영 시간 외에 변경해야 합니다.
쿼리 성능 개선 방법
WhaTap 같은 서버 모니터링 툴에서 오래 걸리는 쿼리를 확인하고 아래와 같이 개선할 수 있습니다.
실행계획에서 type이 ALL(풀스캔)이고 rows가 많은데 오래걸리는 경우 WHERE 조건에 사용된 해당 테이블 컬럼에 인덱스를 생성하면 쿼리 성능이 개선됩니다.
조건절에서 서브쿼리 사용한 경우
DELETE
FROM
im_tstp_qstn_ai_ans
WHERE
evl_trgt_ai_id IN (
SELECT
evl_trgt_ai_id
FROM
im_evl_trgt_ai
WHERE
evl_trgt_id IN (
SELECT
evl_trgt_id
FROM
im_evl_trgt
WHERE
evl_ditb_id = '키값' )
)
위와 같이, 조건절에서 서브쿼리로 조회하면 성능 저하가 발생합니다.
아래처럼 JOIN으로 변경하면 더 적은 리소스를 사용하여 성능이 향상됩니다.
DELETE a FROM im_tstp_qstn_ai_ans a
JOIN im_evl_trgt_ai b ON a.evl_trgt_ai_id = b.evl_trgt_ai_id
JOIN im_evl_trgt c ON b.evl_trgt_id = c.evl_trgt_id
WHERE c.evl_ditb_id = '키값';
조인 쿼리로 변경 후 아래와 같은 작업이 필요합니다.
- im_evl_trgt_ai 테이블의 evl_trgt_ai_id와 evl_trgt_id 인덱스 추가
- im_evl_trgt 테이블의 evl_trgt_id와 evl_ditb_id 열 인덱스 추가
Leave a comment