📘 SQL 문제 구상 및 풀이

🧠 실습한 문제

1981년에 입사한 사원들 중, 이름에 'A'가 포함되지 않은 사원의 이름과 입사일을, 입사일 기준 내림차순으로 검색
12월에 입사한 사원들 중, 급여가 2000 이상인 사원 검색
→ 출력: 이름, 입사일, 급여
입사 월이 2월이고, 부서번호가 30인 사원의 사번, 이름, 급여 검색
사원명에 'A'가 포함되고, 입사 연도가 1981년 또는 1987년인 사원 검색

🔧 풀이에 사용한 SQL 문법

LIKE, NOT LIKE: 문자열 포함 여부 판단
BETWEEN A AND B: 범위 조건
ORDER BY: 정렬 (오름차순 / 내림차순)
(공통)날짜 조건 조회 함수

🗂️ DBMS별 날짜 함수 정리

✅ MySQL

YEAR(hiredate)     -- 연도 추출
MONTH(hiredate)    -- 월 추출
DAY(hiredate)      -- 일 추출

✅ Oracle

-- 날짜 → 문자열로 추출
TO_CHAR(hiredate, 'YYYY')  -- 연도
TO_CHAR(hiredate, 'MM')    -- 월
TO_CHAR(hiredate, 'DD')    -- 일

-- 문자열 → 날짜로 변환
TO_DATE('20250708', 'YYYYMMDD')
TO_DATE('2025-07-08', 'YYYY-MM-DD')

✅ MySQL & Oracle 공통

EXTRACT(YEAR FROM hiredate)
EXTRACT(MONTH FROM hiredate)
EXTRACT(DAY FROM hiredate)

🧪 프로젝트

연체 이력 데이터를 기반으로 파티셔닝 방식(RANGE, LIST, HASH 등)을 적용하고, 방식별 쿼리 성능을 분석한 프로젝트입니다.

👥팀소개

이제현	김동민	이기현	장송하
lyjh98	kddmmm	GIHYUN-LEE	jangongha

📌 파티셔닝이란

논리적으로는 하나의 테이블을, 물리적으로 여러 테이블로 나누어 관리하는 기법
대용량 테이블을 분할하여 성능 향상 및 관리 용이성 확보 등의 효과가 있다.
파티셔닝 학습

🎯 목적

MySQL 파티셔닝 방식(RANGE, LIST, HASH, KEY) 이해
파티셔닝 방식별 PROCEDURE를 통해 성능 차이 비교 및 분석

⚙️ 성능 테스트 시나리오

구분	내용
측정 항목	쿼리 실행시간
측정 대상	일반 테이블 및 파티셔닝 적용 테이블 8개 (Range,List,Hash,Key,Range-Hash,Range-Key,List-Hash,List-Key)
비교 조건	1. 파티셔닝 적용 유무에 따른 차이 2. 단일 파티셔닝 방식별 차이 3. 복합 파티셔닝 방식별 차이
사전 조건	대상 테이블 9개에 동일한 데이터(10만건) 삽입
입력 조건	동일 조회쿼리 500회 반복 입력
절 차	1. 대상 테이블에 입력 조건 3회 실행 2. QueryManager로 실행시간 평균값 도출 3. Mysql서버 재시작 4.나머지 테이블에 반복 실행

🛠️ 테스트 환경

DBMS: MySQL 8.2.0
Tool: DBeaver, MySQL
OS: Windows 10 / Ubuntu 22.04

📂 데이터 셋

데이터 출처: https://www.aihub.or.kr 의 금융 데이터(회원정보, 잔액정보) 이용

데이터 크기 : 총 100,000건 | 파일 크기 : 5.6MB (.csv, UTF-8 인코딩)

전처리 과정

1. python을 이용한 테이블 생성
사용자별 연체 위험도를 표시하기 위해 연체 관련 컬럼(일자, 잔액 등)과 회원정보를 추출하여 병합

2. 발급회원번호 format
발급회원번호의 값이 SYN_0형태의 varchar타입으로 제공되어 있어 INT 형식으로 포멧

  -- SYN_0 으로 제공된 값 -> 0 으로 변경
  UPDATE real_dataset SET 발급회원번호 = REPLACE(발급회원번호, 'SYN_', '');

  -- 0,1,2 .. 으로 변경된 값 INT 타입으로 타입변경
  ALTER TABLE real_dataset MODIFY COLUMN 발급회원번호 INT;

3. PK 설정
파티션 및 서브파티션 KEY로 사용하기 위해 복합PK 설정

  -- 발급회원번호, 연체연도 primary key로 등록
  ALTER TABLE real_dataset ADD PRIMARY KEY (발급회원번호, 연체연도);

4. 데이터 삽입

-- 테이블별 데이터 삽입
INSERT INTO 파티션테이블명
 (발급회원번호, 남녀구분코드, 연령, 거주시도명, 월중평잔_일시불_B0M, 연체일자_B0M, 연체잔액_B0M, 연체잔액_일시불_B0M, 연체잔액_할부_B0M, 연체연도)
 SELECT 발급회원번호, 남녀구분코드, 연령, 거주시도명, 월중평잔_일시불_B0M, 연체일자_B0M, 연체잔액_B0M, 연체잔액_일시불_B0M, 연체잔액_할부_B0M, 연체연도
 FROM real_dataset;

최종 테이블 구조

컬럼명	데이터 타입	데이터 형식
발급회원번호	INT (PK)	0 ,1, 2...
연체연도	INT (PK)	2000,2001...
연체일자_B0M	DATE	2002-02-10, 2024-10-30, ...
남녀구분코드	INT	1(남),2(여),1(남),...
연령	VARCHAR(20)	10대, 20대,30대,..
거주시도명	VARCHAR(20)	서울,부산,..
월중평잔_일시불_B0M	INT	100,220 ,123,321...
연체잔액_B0M	INT	100,000 , 200,100...
연체잔액_일시불_B0M	INT	123,321...
연체잔액_할부	INT	0, 133,122...

✍️ 파티셔닝 적용 테이블 제작

1. 파티셔닝 기준

파티션 개수 5개로 분할(단일 파티셔닝 5개, 복합 파티셔닝 25개)
파티션 키 : 발급회원번호, 연체연도

2. 파티셔닝 테이블 정보 확인 쿼리

  SELECT
    TABLE_NAME, PARTITION_NAME, SUBPARTITION_NAME, PARTITION_METHOD, SUBPARTITION_METHOD, TABLE_ROWS
  FROM
    information_schema.PARTITIONS
    WHERE TABLE_NAME='테이블명';

3. 아래 테이블은 최종 테이블 셋을 토대로 파티셔닝을 적용한 테이블입니다.

1. LIST 파티셔닝

1-1. LIST 파티셔닝

CREATE TABLE list (
  발급회원번호 INT,
  남녀구분코드 INT,
  연령 VARCHAR(20),
  거주시도명 VARCHAR(20),
  월중평잔_일시불_B0M INT,
  연체일자_B0M DATE,
  연체연도 INT,
  연체잔액_B0M INT,
  연체잔액_일시불_B0M INT,
  연체잔액_할부_B0M INT,
  primary key(발급회원번호, 연체연도)
) PARTITION BY LIST (연체연도)(
  	PARTITION P2005 VALUES IN (2000,2001, 2002, 2003, 2004),
  	PARTITION P2010 VALUES IN (2005, 2006, 2007, 2008, 2009),
  	PARTITION P2015 VALUES IN (2010, 2011, 2012, 2013, 2014),
  	PARTITION P2020 VALUES IN (2015, 2016, 2017, 2018, 2019),
  	PARTITION P2025 VALUES IN (2020, 2021, 2022, 2023, 2024, 2025)
  );

LIST 파티셔닝 적용 테이블 확인

1-2. LIST-HASH 파티셔닝

CREATE TABLE list_hash (
  발급회원번호 INT,
  남녀구분코드 INT,
  연령 VARCHAR(20),
  거주시도명 VARCHAR(20),
  월중평잔_일시불_B0M INT,
  연체일자_B0M DATE,
  연체연도 INT,
  연체잔액_B0M INT,
  연체잔액_일시불_B0M INT,
  연체잔액_할부_B0M INT,
  primary key(발급회원번호, 연체연도)
) PARTITION BY list (연체연도)
  SUBPARTITION BY hash(발급회원번호)
  SUBPARTITIONS 5(
  	PARTITION P2005 VALUES IN (2000,2001, 2002, 2003, 2004),
  	PARTITION P2010 VALUES IN (2005, 2006, 2007, 2008, 2009),
  	PARTITION P2015 VALUES IN (2010, 2011, 2012, 2013, 2014),
  	PARTITION P2020 VALUES IN (2015, 2016, 2017, 2018, 2019),
  	PARTITION P2025 VALUES IN (2020, 2021, 2022, 2023, 2024, 2025)
  );

LIST-HASH 파티셔닝 적용 테이블 확인

1-3. LIST-KEY 파티셔닝

CREATE TABLE list_key (
  발급회원번호 INT,
  남녀구분코드 INT,
  연령 VARCHAR(20),
  거주시도명 VARCHAR(20),
  월중평잔_일시불_B0M INT,
  연체일자_B0M DATE,
  연체연도 INT,
  연체잔액_B0M INT,
  연체잔액_일시불_B0M INT,
  연체잔액_할부_B0M INT,
  primary key(발급회원번호, 연체연도)
) PARTITION BY list (연체연도)
  SUBPARTITION BY key(발급회원번호)
  SUBPARTITIONS 5(
  	PARTITION P2005 VALUES IN (2000,2001, 2002, 2003, 2004),
  	PARTITION P2010 VALUES IN (2005, 2006, 2007, 2008, 2009),
  	PARTITION P2015 VALUES IN (2010, 2011, 2012, 2013, 2014),
  	PARTITION P2020 VALUES IN (2015, 2016, 2017, 2018, 2019),
  	PARTITION P2025 VALUES IN (2020, 2021, 2022, 2023, 2024, 2025)
  );

LIST-KEY 파티셔닝 적용 테이블 확인

2. RANGE 파티셔닝

2-1. RANGE 파티셔닝

CREATE TABLE p_range (
  발급회원번호 INT,
  남녀구분코드 INT,
  연령 VARCHAR(20),
  거주시도명 VARCHAR(20),
  월중평잔_일시불_B0M INT,
  연체일자_B0M DATE,
  연체연도 INT,
  연체잔액_B0M INT,
  연체잔액_일시불_B0M INT,
  연체잔액_할부_B0M INT,
  primary key(발급회원번호, 연체연도)
) PARTITION BY range (연체연도)(
  	PARTITION P2005 VALUES LESS THAN (2005),
  	PARTITION P2010 VALUES LESS THAN (2010),
  	PARTITION P2015 VALUES LESS THAN (2015),
  	PARTITION P2020 VALUES LESS THAN (2020),
  	PARTITION P2025 VALUES LESS THAN MAXVALUE
  );

RANGE 파티셔닝 적용 테이블 확인

2-2. RANGE-HASH 파티셔닝

CREATE TABLE range_hash (
  발급회원번호 INT,
  남녀구분코드 INT,
  연령 VARCHAR(20),
  거주시도명 VARCHAR(20),
  월중평잔_일시불_B0M INT,
  연체일자_B0M DATE,
  연체연도 INT,
  연체잔액_B0M INT,
  연체잔액_일시불_B0M INT,
  연체잔액_할부_B0M INT,
  primary key(발급회원번호, 연체연도)
) PARTITION BY range (연체연도)
  SUBPARTITION BY HASH(발급회원번호)
  SUBPARTITIONS 5(
  	PARTITION P2005 VALUES LESS THAN (2005),
  	PARTITION P2010 VALUES LESS THAN (2010),
  	PARTITION P2015 VALUES LESS THAN (2015),
  	PARTITION P2020 VALUES LESS THAN (2020),
  	PARTITION P2025 VALUES LESS THAN MAXVALUE
  );

RANGE-HASH 파티셔닝 적용 테이블 확인

2-3. RANGE-KEY 파티셔닝

CREATE TABLE range_key (
  발급회원번호 INT,
  남녀구분코드 INT,
  연령 VARCHAR(20),
  거주시도명 VARCHAR(20),
  월중평잔_일시불_B0M INT,
  연체일자_B0M DATE,
  연체연도 INT,
  연체잔액_B0M INT,
  연체잔액_일시불_B0M INT,
  연체잔액_할부_B0M INT,
  primary key(발급회원번호, 연체연도)
) PARTITION BY range (연체연도)
  SUBPARTITION BY key(발급회원번호)
  SUBPARTITIONS 5(
  	PARTITION P2005 VALUES LESS THAN (2005),
  	PARTITION P2010 VALUES LESS THAN (2010),
  	PARTITION P2015 VALUES LESS THAN (2015),
  	PARTITION P2020 VALUES LESS THAN (2020),
  	PARTITION P2025 VALUES LESS THAN MAXVALUE
  );

RANGE-KEY 파티셔닝 적용 테이블 확인

3. HASH 파티셔닝

CREATE TABLE hash (
  발급회원번호 INT,
  남녀구분코드 INT,
  연령 VARCHAR(20),
  거주시도명 VARCHAR(20),
  월중평잔_일시불_B0M INT,
  연체일자_B0M DATE,
  연체연도 INT,
  연체잔액_B0M INT,
  연체잔액_일시불_B0M INT,
  연체잔액_할부_B0M INT,
  primary key(발급회원번호, 연체연도)
) PARTITION BY hash (연체연도)
  PARTITIONS 5;

HASH 파티셔닝 적용 테이블 확인

4. KEY 파티셔닝

CREATE TABLE p_key (
  발급회원번호 INT,
  남녀구분코드 INT,
  연령 VARCHAR(20),
  거주시도명 VARCHAR(20),
  월중평잔_일시불_B0M INT,
  연체일자_B0M DATE,
  연체연도 INT,
  연체잔액_B0M INT,
  연체잔액_일시불_B0M INT,
  연체잔액_할부_B0M INT,
  primary key(발급회원번호, 연체연도)
) PARTITION BY key (연체연도)
  PARTITIONS 5;

KEY 파티셔닝 적용 테이블 확인

⌛ 실행 코드

PROCEDURE를 이용하여 파티셔닝 테이블 별 500번씩 연체연도 = 2000 and 발급회원번호=9945 조건을 조회합니다.

📌 PROCEDURE 란

컴퓨터 프로그래밍에서 특정 작업을 수행하기 위해 일련의 명령어들을 모아놓은 것
이러한 명령어 집합은 하나의 단위로서 작동하며, 반복적으로 수행되어야 하는 작업들을 효율적으로 처리할 수 있게 해준다

PROCEDURE 를 이용한 테스트 코드

-- 테스트 프로시져
CREATE PROCEDURE test()
BEGIN
DECLARE i INT DEFAULT 0;
WHILE i < 500 DO
  SELECT * FROM 테이블명 IGNORE INDEX (PRIMARY) WHERE 연체연도=2000 and 발급회원번호=9945;
  SET i = i + 1;
END WHILE;
END;

-- 프로시져 호출
CALL test();

📈 성능 비교 결과

파티셔닝 방식별 테스트 결과

파티셔닝 방식	평균 실행 시간 (s)	단일 테이블 대비 성능 향상률 (%)
파티셔닝 미적용	18s	-
LIST	4.195s	▲ 76.7%
LIST-HASH	1.01s	▲ 94.4%
LIST-KEY	1.001s	▲ 94.4%
RANGE	4.125s	▲ 77.1%
RANGE-HASH	0.98s	▲ 94.6%
RANGE-KEY	1.032s	▲ 94.3%
HASH	4.531s	▲ 74.8%
KEY	4.982s	▲ 72.3%

파티셔닝 방식별 테스트 출력화면

1. 단일테이블 성능

2. LIST 파티셔닝

3. LIST-HASH 파티셔닝

4. LIST-KEY 파티셔닝

5. RANGE 파티셔닝

6. RANGE-HASH 파티셔닝

7. RANGE-KEY 파티셔닝

8. HASH 파티셔닝

9. KEY 파티셔닝

🔍 테스트 결과에 대한 고찰

✅ 단일 테이블 대비 성능 향상

단일 파티셔닝 의 경우 : 약 75% 향상
복합 파티셔닝 의 경우 : 약 94% 향상
복합 파티셔닝은 단일 파티셔닝보다 약 77% 향상

✅ 파티셔닝 방식별 성능

단일 파티셔닝 의 경우 : RANGE
복합 파티셔닝 의 경우 : RANGE-HASH
고려사항 : 파티셔닝 방식에 따른 상이한 데이터 타입, 방대한 데이터 및 파티션 개수

⚠️ KEY 파티셔닝의 예외적 결과

8개의 방식 중 유일하게 데이터가 균등하게 분할되지 않았습니다.
KEY는 MySQL 내부의 비공개 해시 알고리즘을 사용하여, 분산 방식의 예측이 불가 하며 데이터가 편중될 수 있다.

✅ 결론

KEY 파티셔닝은 데이터 특성 및 분포에 민감하므로 실무 적용 시 주의 필요
데이터 양과 파티션 개수에 따른 분석 필요

🚀 트러블 슈팅

🧠 초기 아이디어 소통 오류

프로젝트 초반 아이디어를 도출하고 정리하는 과정에서,동일한 주제를 바라보는 관점과 해석이 구성원마다 상이
해결 방법
- 서로가 생각하는 프로젝트의 방향성과 핵심 목표에 대해 구체적으로 대화하는 시간을 마련하였고,
  이를 통해 각자의 관점과 해석 차이 이해
  ✅ 해결 완료

🧹 데이터 import 이슈

.csv 파일을 UTF-8로 저장했음에도 업로드 시 한글 깨짐 현상 발생했습니다.
해결 방법
- 업로드 전 SET NAMES utf8mb4로 문자셋 동기화 처리
  ✅ 문제 해결 완료

🧩 MySQL 파티셔닝 방식 실험

⚙️ 1차 시도: `LIST - RANGE`

목표
성별 기준으로 리스트 분할 후, 연체원금_최근 기준 범위로 서브 파티셔닝

시도한 코드

CREATE TABLE temp_data (
    발급회원번호 VARCHAR(20),
    성별 CHAR(1),
    연체원금_최근 INT
)
PARTITION BY LIST (성별)
SUBPARTITION BY RANGE (연체원금_최근)
(
    PARTITION p_male VALUES IN ('M')
        SUBPARTITIONS 3 (
            SUBPARTITION sp_male_low VALUES LESS THAN (100000),
            SUBPARTITION sp_male_mid VALUES LESS THAN (500000),
            SUBPARTITION sp_male_high VALUES LESS THAN MAXVALUE
        ),
    PARTITION p_female VALUES IN ('F')
        SUBPARTITIONS 3 (
            SUBPARTITION sp_female_low VALUES LESS THAN (100000),
            SUBPARTITION sp_female_mid VALUES LESS THAN (500000),
            SUBPARTITION sp_female_high VALUES LESS THAN MAXVALUE
        )
);

결과
❌ MySQL에서 LIST + RANGE 조합은 직접적인 서브 파티셔닝으로 지원되지 않았습니다.

⚙️ 2차 시도: `RANGE → HASH → LIST` 조합의 3차 파티셔닝

목표
다차원 기준 (연체일자, 정보아이디, 금액)을 모두 반영하기 위해
RANGE → HASH → LIST 구조의 3단계 파티셔닝 시도
결과
❌ 서브 파티셔닝으로 3차는 MySQL에서 허용되지 않았습니다.

📢 결론
MYSQL 기준 2단계 파티셔닝까지 지원하며, 메인 파티셔닝은 RANGE와 LIST 서브 파티셔닝은 HASH와 KEY 사용 가능

⚙️ 파티션 키로 조회 시 탐색하는 row의 수가 1이되는 현상

이슈

explain analyze select * from range_hash
where 연체연도=2000 and 발급회원번호=9945;

row의 수가 서브파티션의 row(약 4000개)로 출력되지 않는 이슈 발생

해결 방법

explain analyze select * from range_hash
IGNORE INDEX (PRIMARY)
where 연체연도=2000 and 발급회원번호=9945;

파티셔닝 기법만으로 성능 비교를 위해 PK 인덱스 탐색을 배제했습니다.

Name		Name	Last commit message	Last commit date
Latest commit History 12 Commits
README.md		README.md
SQL_Study_4Q.sql		SQL_Study_4Q.sql

GIHYUN-LEE/SQL-study

Folders and files

Latest commit

History

Repository files navigation