웹사이트를 운영하다 보면 시간이 지나면서 데이터가 쌓이고, 어느 순간부터 갑자기 페이지 로딩이 느려지는 경험을 하게 된다. 특히 PHP에서 MySQL 쿼리를 실행할 때 어떤 쿼리가 느린지, 왜 느린지 파악하기가 쉽지 않다. 직접 쿼리를 날려보거나 전체 쿼리를 로깅하는 것도 방법이지만 효율적이지 못하다. 

 

이럴 땐 MySQL의 느린 쿼리 로그(Slow Query Log) 기능을 활용하면 일정 시간 이상 걸리는 쿼리들을 자동으로 기록할 수 있어서 성능 최적화의 첫 단계로 매우 유용하다. 다만 느린 쿼리 로그도 설정이 필요하고, 로그를 해석하는 방법도 알아야 하기 때문에 이번에는 실제 설정 방법과 활용 예제를 소개하겠다. 

 

MySQL 느린 쿼리 로그 설정

먼저 MySQL 서버의 설정 파일(my.cnf 또는 my.ini)을 수정해야 한다. 일반적으로 /etc/mysql/my.cnf 또는 /etc/my.cnf 에 위치한다.

# my.cnf 파일 수정
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 2
log_queries_not_using_indexes = 1

각 옵션의 의미는 다음과 같다:

  • slow_query_log = 1 : 느린 쿼리 로그 활성화
  • slow_query_log_file : 로그 파일 저장 경로 (디렉토리가 있고 MySQL 프로세스가 쓰기 권한이 있어야 함)
  • long_query_time = 2 : 2초 이상 걸리는 쿼리 기록 (필요에 따라 0.1 같이 조정 가능)
  • log_queries_not_using_indexes = 1 : 인덱스를 사용하지 않는 쿼리도 기록 (선택사항)

설정을 변경한 후 MySQL을 재시작한다.

# CentOS/RHEL
sudo systemctl restart mysqld

# Ubuntu/Debian
sudo systemctl restart mysql

 

느린 쿼리 로그 확인하기
# 로그 파일 직접 확인
tail -f /var/log/mysql/slow-query.log

# 또는 MySQL 명령어로 확인
mysql> SHOW VARIABLES LIKE 'slow_query%';
mysql> SELECT * FROM mysql.slow_logG;

느린 쿼리 로그의 형태는 다음과 같다:

# Time: 2024-01-15T10:30:45.123456Z
# User@Host: user@[192.168.1.100]
# Query_time: 5.234567  Lock_time: 0.001234  Rows_sent: 1000  Rows_examined: 500000
SELECT * FROM users u 
JOIN orders o ON u.id = o.user_id 
WHERE u.status = 'active' AND o.created_at > DATE_SUB(NOW(), INTERVAL 1 MONTH);
  • Query_time : 쿼리 실행 시간 (초)
  • Lock_time : 테이블 락 시간
  • Rows_sent : 반환된 행 수
  • Rows_examined : 스캔한 행 수 (이 값이 Rows_sent 보다 훨씬 크면 비효율적)

 

PHP에서 로그 파싱하기

느린 쿼리 로그를 정기적으로 모니터링하려면 PHP로 로그 파일을 파싱해서 분석하는 것도 좋다.

<?php
// 느린 쿼리 로그 파싱
$logFile = '/var/log/mysql/slow-query.log';
$handle = fopen($logFile, 'r');
$queries = array();
$currentQuery = array();

while(($line = fgets($handle)) !== false) {
   
   if(strpos($line, '# Time:') === 0) {
      if(!empty($currentQuery)) {
         $queries[] = $currentQuery;
      }
      $currentQuery = array(
         'time' => trim(str_replace('# Time:', '', $line)),
         'query_time' => 0,
         'query' => ''
      );
   }
   
   if(strpos($line, 'Query_time:') === 0) {
      preg_match('/Query_time: ([d.]+)/', $line, $matches);
      $currentQuery['query_time'] = (float)$matches[1];
   }
   
   // 실제 쿼리문은 SELECT, INSERT, UPDATE, DELETE 등으로 시작
   if(preg_match('/^(SELECT|INSERT|UPDATE|DELETE|JOIN)/', trim($line))) {
      $currentQuery['query'] .= $line;
   }
}

fclose($handle);

// 쿼리 시간순으로 정렬
usort($queries, function($a, $b) {
   return $b['query_time'] - $a['query_time'];
});

// 상위 10개 출력
echo '<h3>상위 10개 느린 쿼리</h3>';
echo '<table border="1">';
echo '<tr><th>시간(초)</th><th>쿼리</th></tr>';

for($i = 0; $i < min(10, count($queries)); $i++) {
   echo '<tr>';
   echo '<td>' . number_format($queries[$i]['query_time'], 3) . '</td>';
   echo '<td><pre>' . htmlspecialchars($queries[$i]['query']) . '</pre></td>';
   echo '</tr>';
}
echo '</table>';
?>

 

성능 개선 체크리스트

느린 쿼리를 찾았다면 다음을 차례대로 점검해보자:

  • 인덱스 확인 : WHERE 절이나 JOIN 조건의 컬럼에 인덱스가 있는가?
  • Rows_examined 확인 : 스캔한 행이 반환된 행보다 훨씬 많은가? (비효율적)
  • SELECT * 피하기 : 필요한 컬럼만 명시적으로 선택
  • 불필요한 JOIN 제거 : 실제로 필요한 데이터만 JOIN
  • LIMIT 추가 : 대량의 데이터 조회 시 페이징 처리
  • EXPLAIN으로 실행계획 확인 : EXPLAIN SELECT ... 로 쿼리 실행 계획 분석

 

느린 쿼리 로그는 한 번 설정해놓으면 자동으로 기록되기 때문에 정기적으로 확인하기만 하면 된다. 특히 새 기능을 추가하거나 데이터가 크게 증가한 후에는 주기적으로 로그를 확인해서 성능 저하를 조기에 발견할 수 있다. 다만 로그 파일이 계속 쌓이기 때문에 주기적으로 정리하거나 로테이션하는 것도 잊지 말자.