실제로 효과가 있는 5가지 데이터베이스 쿼리 최적화 팁

Source: Dev.to

수년간 데이터베이스 기반 애플리케이션을 관리하면서, 쿼리 최적화가 애플리케이션 성능을 좌우한다는 것을 배웠습니다. 여기 제가 정기적으로 사용하는 실용적인 다섯 가지 팁을 소개합니다.

1. 외래 키에 인덱스 만들기

모든 외래 키에는 인덱스가 있어야 합니다:

CREATE INDEX idx_user_id ON orders(user_id);

적절한 인덱스가 없으면 테이블이 커질수록 JOIN 연산이 매우 느려집니다.

2. EXPLAIN을 사용해 현재 상황 파악하기

쿼리에 EXPLAIN을 실행해 MySQL이 어떻게 실행하는지 확인하세요:

EXPLAIN SELECT * FROM products WHERE category_id = 5;

다음 항목을 살펴보세요:

• 테이블 스캔 (나쁨)
• 인덱스 사용 (좋음)
• 검사된 행 수 vs. 반환된 행 수

3. 필요한 컬럼만 선택하기

불필요한 컬럼을 가져오면 메모리와 대역폭을 낭비합니다.

// 나쁨
$query = "SELECT * FROM users";

// 좋음
$query = "SELECT id, name, email FROM users";

4. 연결 재사용 및 배치 삽입

데이터베이스 연결을 여는 비용이 크므로, 지속적인 연결(persistent connection)으로 재사용하세요:

// PHP에서 지속적인 연결 사용
$pdo = new PDO($dsn, $user, $pass, [
    PDO::ATTR_PERSISTENT => true
]);

한 번에 하나씩 삽입하는 대신 여러 행을 배치로 삽입합니다:

// 이렇게 하지 말고
foreach ($items as $item) {
    $db->insert($item);
}

// 이렇게 하세요
$db->insertBatch($items);

이렇게 하면 대량 작업 속도가 10~100배 빨라질 수 있습니다.

5. MySQL 슬로우 쿼리 로그 활성화

임계값을 초과하는 쿼리를 캡처해 나중에 분석할 수 있도록 설정합니다:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;

2초 이상 걸리는 모든 쿼리가 로그에 기록됩니다.

데이터베이스 최적화는 지속적인 과정입니다. 이 기본 사항부터 시작해 결과를 측정하고 반복하세요. 더 빠른 응답 시간을 통해 사용자들이 감사할 것입니다!