MySQL 쿼리 처리 흐름 이해

효율적인 SQL 최적화를 위해서는 MySQL의 내부 실행 프로세스를 이해하는 것이 중요하다.

1. 연결 관리: 클라이언트와의 연결 설정, 권한 검증, 세션 유지 등의 작업을 수행하는 커넥터 단계
2. 쿼리 캐싱: 이전에 실행된 동일한 SELECT 문장과 결과를 메모리에 저장하여 재사용하는 기능
3. 구문 분석: 입력된 SQL 문장을 어휘 분석과 구문 분석을 통해 해석하고 유효성 검사를 진행
4. 실행 계획 수립: 인덱스 선택, 조인 순서 결정 등 쿼리를 효율적으로 수행하기 위한 최적 경로 도출
5. 데이터 조회 및 반환: 스토리지 엔진을 통해 실제 데이터를 가져오고 결과를 클라이언트에 전달하며 캐시에 저장

핵심 SQL 최적화 기법

1. 불필요한 전체 컬럼 조회 지양

SELECT * 사용은 다음과 같은 문제를 야기한다:

• 불필요한 리소스 소비 (메모리, CPU)
• 네트워크 전송 시간 증가
• 인덱스 커버링 활용 불가로 인한 디스크 I/O 증가

-- 비효율적인 방식
SELECT * FROM users;

-- 필요한 컬럼만 명시적으로 조회
SELECT user_id, username, email FROM users;

2. UNION과 UNION ALL 선택적 사용

중복 제거 연산이 필요하지 않은 경우 UNION ALL을 사용하여 성능을 개선할 수 있다.

-- 중복 제거가 필요한 경우
SELECT name FROM employees WHERE dept = 'IT'
UNION
SELECT name FROM contractors WHERE dept = 'IT';

-- 중복 허용 시 더 빠른 UNION ALL 사용
SELECT product_name FROM inventory WHERE category = 'Electronics'
UNION ALL
SELECT product_name FROM archived_inventory WHERE category = 'Electronics';

3. 테이블 조인 전략 - Nested Loop Join 최적화

작은 데이터셋으로 큰 테이블을 필터링하는 것이 효율적이다.

-- 작은 테이블(user_groups)로 큰 테이블(users) 필터링
SELECT u.* FROM users u 
WHERE u.group_id IN (
    SELECT group_id FROM user_groups 
    WHERE active_status = 1 AND member_count > 10
);

-- EXISTS를 활용한 대안 (소형 테이블 기준)
SELECT u.* FROM users u 
WHERE EXISTS (
    SELECT 1 FROM user_groups ug 
    WHERE ug.group_id = u.group_id 
    AND ug.active_status = 1 
    AND ug.member_count > 10
);

4. 인덱스 설계 원칙

• WHERE 절과 ORDER BY 절에 자주 사용되는 컬럼에 인덱스 생성
• 복합 인덱스의 경우 왼쪽부터 순차적으로 사용되어야 효과적
• INSERT/UPDATE 성능 저하를 고려하여 불필요한 인덱스 제거

5. 비교 연산자 최적화

비등가 비교 연산자는 인덱스 활용이 어렵다:

-- 인덱스 스캔 불가능
SELECT * FROM orders WHERE status != 'completed';

-- 범위 조건으로 변경하여 인덱스 활용
SELECT * FROM orders WHERE status IN ('pending', 'processing', 'shipped');

6. OR 조건 처리 개선

-- 인덱스 혼용으로 인한 풀 스캔
SELECT customer_id FROM customers 
WHERE age = 25 OR city = 'Seoul';

-- UNION ALL로 분리하여 각각 인덱스 활용
SELECT customer_id FROM customers WHERE age = 25
UNION ALL
SELECT customer_id FROM customers WHERE city = 'Seoul';

7. NULL 값 처리 최적화

-- 풀 테이블 스캔 유발
SELECT * FROM products WHERE description IS NULL;

-- 기본값 설정으로 개선
ALTER TABLE products ALTER COLUMN description SET DEFAULT '';
SELECT * FROM products WHERE description = '';

8. IN/NOT IN vs BETWEEN 최적화

-- 연속된 값의 경우 BETWEEN 사용
SELECT employee_id FROM staff WHERE salary IN (30000, 35000, 40000);

-- 개선된 버전
SELECT employee_id FROM staff WHERE salary BETWEEN 30000 AND 40000;

-- EXISTS로 대체 가능한 서브쿼리
SELECT p.product_id FROM products p 
WHERE p.category_id IN (
    SELECT c.category_id FROM categories c WHERE c.is_active = 1
);

-- EXISTS로 변경
SELECT p.product_id FROM products p 
WHERE EXISTS (
    SELECT 1 FROM categories c 
    WHERE c.category_id = p.category_id AND c.is_active = 1
);

9. LIKE 패턴 매칭 최적화

-- 좌측 와일드카드로 인해 인덱스 무효화
SELECT * FROM documents WHERE title LIKE '%report%';

-- 우측 와일드카드만 사용하여 인덱스 활용
SELECT * FROM documents WHERE title LIKE 'annual%';

10. 파라미터 및 표현식 처리

-- 함수 적용으로 인한 인덱스 미사용
SELECT * FROM measurements WHERE YEAR(record_date) = 2023;

-- 개선된 방식
SELECT * FROM measurements WHERE record_date >= '2023-01-01' AND record_date < '2024-01-01';

-- 산술 연산 최적화
SELECT * FROM transactions WHERE amount / 100 = 50;

-- 상수로 전환
SELECT * FROM transactions WHERE amount = 5000;

11. CHAR vs VARCHAR 선택

검색 성능과 저장 공간 절약을 위해 VARCHAR 사용이 일반적으로 선호된다.