쿼리튜닝, MySQL 8.0 자주 사용하는 힌트 목록 알아보기

 

MySQL 데이터베이스에서 쿼리 성능을 최적화하기 위해 사용하는 쿼리 힌트는 개발자에게 매우 중요한 도구입니다.

 

 이 포스팅에서는 MySQL 8.0에서 자주 사용되는 쿼리 힌트들의 기능, 사용법, 그리고 실제 적용 사례를 자세히 알아보겠습니다.

---

 

USE INDEX :

 

USE INDEX 힌트는 MySQL 옵티마이저에게 특정 테이블의 인덱스를 사용하도록 권장하는 힌트입니다. 

이는 강제가 아닌 권장이라는 점을 명심해야 합니다.

 

즉, USE INDEX 힌트는 MySQL 옵티마이저가 때로는 최적이 아닌 인덱스를 선택할 때 유용합니다. 

 

특히 복잡한 쿼리나 여러 인덱스가 존재하는 테이블에서 옵티마이저가 잘못된 판단을 할 경우, 개발자가 직접 더 나은 인덱스를 제안할 수 있습니다

또한 쿼리 옵티마이저가 다른 인덱스를 선택하지 않도록하며, 범위 스캔을 피하고자 할때 유용하게 사용되고 있습니다.

 

 

from 테이블 뒤에 힌트를 추가해서 사용

 

 

기본 구문

SELECT * 
FROM TABLE1 
  USE INDEX (COL1_INDEX, COL2_INDEX)
WHERE COL1=1 AND COL2=2 AND COL3=3;

 

 

용도별 사용 구문

-- 레코드 검색용으로만 인덱스 사용
SELECT * FROM employees USE INDEX FOR JOIN (idx_lastname) WHERE last_name = 'Smith';

-- 정렬 용도로만 인덱스 사용
SELECT * FROM employees USE INDEX FOR ORDER BY (idx_lastname) ORDER BY last_name;

-- 그룹화 용도로만 인덱스 사용
SELECT department, COUNT(*) FROM employees USE INDEX FOR GROUP BY (idx_department) GROUP BY department;

 

 

실제 사용 예시

 

대용량 테이블에서 여러 유사한 인덱스가 존재할 때 옵티마이저가 가장 효율적인 인덱스를 선택하지 못하는 경우가 있습니다. 예를 들어, 사용자 테이블에서 이름과 이메일로 검색할 때 다음과 같이 힌트를 줄 수 있습니다.

-- 이메일 인덱스를 사용하도록 권장
SELECT * FROM users USE INDEX (idx_email) WHERE email LIKE 'john%' AND name = 'John Doe';

 

 

IGNORE INDEX :

IGNORE INDEX 힌트는 MySQL 옵티마이저에게 특정 인덱스를 사용하지 않도록 지시합니다.

 

옵티마이저가 잘못된 인덱스를 선택해서 쿼리를 실행할 수 있으므로, 옵티마이저가 선택한 인덱스를 IGNORE INDEX를 이용해 무시하도록 지시 하면 옵티마이저는 필요에 따른 다른 인덱스를 선택한다.

 

 

IGNORE INDEX는 여러 개의 인덱스를 동시에 무시하도록 지정할 수 있습니다(콤마로 구분). 

실제로 MySQL 문서에 따르면 여러 인덱스를 콤마로 구분하여 동시에 무시하도록 지정할 수 있습니다

 

 

 

기본 구문

SELECT * 
FROM TABLE1 
  IGNORE INDEX (COL1_INDEX)
WHERE COL1=1

 

용도별 사용법

-- 레코드 검색용으로만 인덱스 무시
SELECT * FROM products IGNORE INDEX FOR JOIN (idx_product_category) WHERE category = 'Electronics';

-- 정렬 용도로만 인덱스 무시
SELECT * FROM orders IGNORE INDEX FOR ORDER BY (idx_order_date) ORDER BY order_date;

 

실제 사용 예시

-- 카테고리 인덱스가 데이터 분포 때문에 비효율적일 경우
SELECT * FROM products IGNORE INDEX (idx_category) WHERE category = 'General' AND price > 100;

 

 

FORCE INDEX :

FORCE INDEX 힌트는 USE INDEX보다 더 강력하게 옵티마이저에게 특정 인덱스를 사용하도록 강제합니다

특정 인덱스를 사용하도록 강제하지만, IGNORE INDEX와 달리 인덱스 사용을 제한 하는 것은 아닙니다.

 

 

또한 FORCE INDEX는 USE INDEX와 유사하지만, 테이블 스캔이 매우 비용이 많이 드는 것으로 가정하도록 옵티마이저에게 더 강하게 지시합니다.

즉, 지정된 인덱스 중 하나를 사용하는 방법이 없는 경우에만 테이블 스캔이 사용됩니다.

 

FORCE INDEX도 여러 인덱스를 동시에 지정할 수 있습니다(콤마로 구분).

 

중요 주의사항
MySQL 8.4에서는 USE INDEX, FORCE INDEX, IGNORE INDEX 힌트가 향후 더 이상 사용되지 않을 예정(deprecated)이며, 대신 JOIN_INDEX, GROUP_INDEX, ORDER_INDEX, NO_JOIN_INDEX 등의 새로운 옵티마이저 힌트 사용을 권장하고 있습니다.

기본 구문

SELECT * 
FROM TABLE1 
  FORCE INDEX (COL1_INDEX)
WHERE COL1=1

 

 

MAX_EXECUTION_TIME :

MAX_EXECUTION_TIME 힌트는 쿼리의 실행 시간을 제한하는 데 사용됩니다.

지정된 시간 내에 쿼리가 실행되도록 제어 할 수 있습니다.

 

단, 시간을 낮게 설정하면 해당 시간을 초과하는 쿼리는 아래의 오류가 나타면서 쿼리 수행이 중단된다.

 

Query execution was interrupted, maximum statement execution time exceeded

 

 

띠라서 데이터 응답속도가 오래 걸리는 쿼리에 대해서는 시간초를 limit하여 강제로 kill 할 수 있으며, 해당 용도로도 사용이 가능합니다.