인덱스를 타지 않는 경우

인덱스를 타지 않는 경우 

• 테이블에 인덱스를 제대로 생성을 했지만 인덱스를 타지 않는 경우
• 인덱스는 제대로 걸려있지만 왜 인덱스를 안타는지에 대한 경우를 나누어서 직접 조회해보며 정리
  • notice 테이블 기반으로 확 
  • 이를 통해 인덱스를 잘 탈 수 있게 쿼리 튜닝을 할 수 있다.

-- 2023.01.15 ~ 2023.02.14 한달 데이터 조회

explain
SELECT * FROM notice
WHERE createDate BETWEEN '2023-01-15 00:00:00' AND '2023-02-14 23:59:59'
;

• explain을 붙여서 실행하여 실행계획을 확인해보면 key 칼럼 idx_notice_createDate 인덱스를 타는것을 확인 가능

 

1. 함수나 연산자를 사용하는 경우

• 쿼리에서 컬럼 값에 함수나 연산을 적용하면 인덱스가 효과적으로 사용되지 않을 수 있습니다.
• 예를 들어, WHERE UPPER(column_name) = 'VALUE'와 같이 함수를 사용하는 경우에는 인덱스가 효과적으로 사용되지 않을 수 있습니다.

-- createDate에 함수를 사용
explain
SELECT * FROM notice WHERE DATE_FORMAT(createDate, '%Y-%m-%d') = '2023-05-06';

• DATE_FORMAT 함수를 사용했더니 possible_keys가 NULL임을 확인 가능

 

2. LIKE문 검색에서 와일드카드의 위치

• LIKE 연산자를 사용하는 경우, 와일드카드(%, _)가 문자열의 시작 부분에 위치하면 인덱스가 효과적으로 사용되지 않을 수 있습니다.
• 예를 들어, LIKE '%pattern' 형태의 쿼리는 인덱스를 활용할 수 없게 됩니다.

explain
SELECT * FROM notice WHERE createDate LIKE '%2023-12%'
;

 

explain
SELECT * FROM notice WHERE createDate LIKE '%2023-12'
;

• 위 두 LIKE를 활용한 예시를 보면 possible_keys가 NULL인걸 확인 가능하다.

 

3. OR절을 사용하는 경우

• OR 절을 사용하는 경우, 해당 조건들 간에는 최적화가 어려울 수 있습니다. 
• 개별 조건이 인덱스를 사용할 수 있더라도, OR 절은 최적화를 어렵게 만들 수 있습니다.

 

4. NULL 값을 비교하는 경우

• 특정 컬럼에 대해 NULL 값을 검색하는 경우, 인덱스가 효과적으로 사용되지 않을 수 있습니다.
•  NULL 값은 인덱스에 별도의 처리가 필요하므로 주의가 필요합니다.

-- NULL 값을 사용하는 경우:
explain
SELECT * FROM notice WHERE createDate IS NULL;

•  WHERE createDate IS NULL를 사용하여 NULL 값을 검색하였을 때 별도의 처리를 안해주어서 인덱스 사용을 안하는 결과를 확인 할 수 있다.

 

5. 컬럼의 자료형이 다른 검색을 하는 경우

• 컬럼의 자료형이 서로 다른 경우, 인덱스가 효과적으로 사용되지 않을 수 있습니다.
• 자료형이 다르면 인덱스의 키와 비교할 값의 자료형이 일치하지 않아 인덱스를 사용할 수 없게 됩니다.

 

 

6. IN 연산자를 사용한 검색에서 IN 목록의 개수가 많은 경우

• IN 연산자를 사용하여 검색하는 경우, IN 목록의 개수가 많을수록 인덱스의 효과가 감소할 수 있습니다. 
• 많은 항목을 비교하는 경우에는 인덱스를 사용하는 것보다 다른 최적화 전략을 고려해야 합니다.

explain
SELECT * FROM notice WHERE createDate IN (
'2023-01-15 14:41:49','2023-05-07 14:50:25', '2023-03-04 14:41:49', '2023-03-01 14:41:49'
...'2023-05-06 17:43:51','2023-05-06 17:43:52'
);

 

• IN 목록안에 적당한 개수의 생성 날짜를 넣었을 때는 key를 정상적으로 사용하지만 많이 넣을수록 인덱스의 효과가 감소하고 양이 많아지면 key를 사용하지 않는 경우도 발