1. 대량의 데이터 삽입 최적화
대량의 데이터를 데이터베이스에 적재할 때는 일반적인
INSERT 문보다 효율적인 방법을 선택해야 합니다.
LOAD DATA 명령 활용
텍스트 파일 등에 저장된 대량의 데이터를 가져올 때는
LOAD DATA 구문이 가장 빠릅니다.
LOAD DATA LOCAL INFILE '/data/user_logs.csv'
INTO TABLE member_data
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';
InnoDB 테이블을 위한 튜닝
InnoDB 엔진을 사용하는 테이블의 경우, 다음 설정을 통해 삽입 속도를 높일 수 있습니다.
- PK 순서 유지: 데이터를 기본키(Primary Key) 순서로 정렬하여 삽입하면 클러스터링 인덱스 재구성 비용이 줄어듭니다.
- 고유성 체크 일시 중지: 인덱스가 많은 경우
SET UNIQUE_CHECKS=0;을 통해 체크를 비활성화한 후 작업을 마치고 다시 활성화(1)합니다.
- 자동 커밋 비활성화:
SET AUTOCOMMIT=0;을 실행하여 매 행마다 발생하는 트랜잭션 커밋 부하를 줄이고, 작업 완료 후 수동으로 커밋합니다.
2. INSERT 구문 구조 개선
여러 번의 네트워크 왕복(Round-trip)을 줄이는 것이 핵심입니다.
배치 삽입(Multi-value Insert)
개별
INSERT 문을 여러 번 실행하는 대신, 하나의 구문에 여러 로우를 포함합니다.
-- 비효율적인 방식
INSERT INTO employee_contacts VALUES (1, 'Alice', '010-1111-2222');
INSERT INTO employee_contacts VALUES (2, 'Bob', '010-3333-4444');
-- 최적화된 방식
INSERT INTO employee_contacts VALUES
(1, 'Alice', '010-1111-2222'),
(2, 'Bob', '010-3333-4444'),
(3, 'Charlie', '010-5555-6666');
트랜잭션 묶기
수동 트랜잭션을 사용하여 여러 삽입 작업을 하나의 단위로 처리하면 디스크 I/O 발생 빈도를 낮출 수 있습니다.
3. ORDER BY 정렬 최적화
MySQL의 정렬 방식은 크게 두 가지입니다. 인덱스를 이용하는
Using index와 별도의 메모리 공간에서 정렬하는
Using filesort입니다. 성능을 위해서는 전자를 유도해야 합니다.
커버링 인덱스 활용
SELECT 절에 포함된 컬럼과
ORDER BY 컬럼이 모두 인덱스에 포함되어 있으면, 별도의 정렬 작업 없이 결과를 반환할 수 있습니다.
-- 최적화된 형태 (id와 office_code가 인덱스로 구성된 경우)
EXPLAIN SELECT id, office_code FROM branch_offices ORDER BY office_code;
정렬 방향 일관성
복합 인덱스를 사용할 때 정렬 조건은 모두 오름차순(ASC)이거나 모두 내림차순(DESC)이어야 인덱스를 탑니다. 혼합된 경우(ASC/DESC 혼용)
filesort가 발생할 확률이 높습니다.
Filesort 성능 튜닝
부득이하게
filesort가 발생한다면,
sort_buffer_size와
max_length_for_sort_data 시스템 변수를 적절히 조절하여 메모리 내 정렬 효율을 높일 수 있습니다.
4. GROUP BY 최적화
GROUP BY 작업은 내부적으로 정렬을 동반합니다. 만약 결과의 순서가 중요하지 않다면 정렬을 명시적으로 차단하여 성능을 높일 수 있습니다.
-- 정렬 부하 제거 (MySQL 5.7 이하 버전 유효)
SELECT category_id, COUNT(*)
FROM product_list
GROUP BY category_id
ORDER BY NULL;
또한,
GROUP BY 컬럼에 인덱스를 적용하면 임시 테이블(Temporary table) 생성을 최소화할 수 있습니다.
5. 서브쿼리 대신 조인(JOIN) 사용
서브쿼리(특히
IN 절을 포함한)는 옵티마이저가 최적의 실행 계획을 세우기에 한계가 있는 경우가 많습니다. 가급적
JOIN으로 변환하여 처리하는 것이 효율적입니다.
-- 서브쿼리 방식
SELECT * FROM orders
WHERE customer_id IN (SELECT id FROM customers WHERE status = 'active');
-- 조인 방식 최적화
SELECT o.*
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
WHERE c.status = 'active';
6. OR 조건을 UNION으로 대체
하나의 쿼리에
OR 조건이 포함되면 복합 인덱스를 효율적으로 사용하지 못하고 전체 테이블 스캔(Full Table Scan)을 수행할 수 있습니다. 각 조건을
UNION으로 분리하면 개별 인덱스를 활용할 수 있습니다.
-- OR 사용 (인덱스 효율 저하 가능성)
SELECT * FROM site_users WHERE email = 'test@example.com' OR phone = '010-1234-5678';
-- UNION 사용 (각각의 인덱스 활용)
SELECT * FROM site_users WHERE email = 'test@example.com'
UNION
SELECT * FROM site_users WHERE phone = '010-1234-5678';
7. 페이징 처리(LIMIT) 최적화
페이지 번호가 뒤로 갈수록
LIMIT offset, rows 방식은 느려집니다. 앞선 행들을 모두 읽은 후 버려야 하기 때문입니다.
지연된 조인(Late Row Lookup)
인덱스만으로 먼저 식별자(ID)를 필터링한 뒤, 해당 행들의 상세 데이터만 가져오는 방식입니다.
SELECT * FROM board b
JOIN (SELECT id FROM board ORDER BY id LIMIT 500000, 10) as temp ON b.id = temp.id;
ID 기반 범위 탐색(Seek Method)
마지막으로 조회한 ID 값을 기준으로 다음 데이터를 조회합니다.
-- 이전 페이지의 마지막 ID가 500일 때
SELECT * FROM board WHERE id > 500 ORDER BY id ASC LIMIT 10;
8. 인덱스 힌트 사용
옵티마이저가 항상 최선의 판단을 하는 것은 아닙니다. 명시적으로 특정 인덱스를 사용하도록 강제하거나 제외할 수 있습니다.
- USE INDEX: 특정 인덱스를 사용하도록 권장합니다.
- IGNORE INDEX: 특정 인덱스를 무시합니다.
- FORCE INDEX: 옵티마이저의 판단을 무시하고 강제로 해당 인덱스를 사용하게 합니다.
SELECT * FROM account_records FORCE INDEX (idx_transaction_date)
WHERE transaction_date BETWEEN '2023-01-01' AND '2023-01-31';