[MySQL] Index가 사용되지 않는 6가지 경우 ( with 10만 건 예제 데이터를 통한 실습 )

# 인덱스가 궁금하신 분들은 아래 글을 참고해 주세요 !

[MySQL] Index, B-Tree란 ? ( 인덱스 키 값 계산 )

🐳 사전 지식

먼저 인덱스가 언제 사용되지 않는지 알기에 앞서 인덱스가 '사용' 된다는 의미를 이해할 필요가 있다.

인덱스가 사용되는 방식

조회시에 인덱스는 크게 두 가지 방법으로 사용된다.

인덱스가 사용되는 두 가지 방법에 대해 살펴보자

1. 작업 범위 결정 조건

작업 범위 결정 조건은 말 그대로 작업의 범위를 결정하는 조건이다.

쉽게 생각하면 조회의 범위를 좁히는데 사용되는 조건이다.

 

작업 범위 결정 조건의 특징은 아래와 같다.

1. 많으면 많을 수록 성능 향상에 영향을 준다
2. 인덱스를 효율적으로 사용했다는 의미이다.

2. 체크 조건 / 필터링 조건

체크 조건은 조회한 데이터를 사용자가 지정한 조건에 맞게 결과를 필터링 한다는 의미이다.

 

예를 들어 1~10 번 까지 10개의 데이터를 조회한 후에 홀수번 데이터만 사용한다고 하면

짝수번 데이터는 필터링되고 홀수번 데이터만 얻을 수 있다.

 

여기서 중요한 것은 처음부터 홀수번만 조회했으면 5개의 데이터만 조회하면 되는데

10개의 데이터를 조회하고 5개의 짝수번 데이터를 필터링 했다는 것이다.

 

체크 조간의 특징은 아래와 같다.

1. 성능 향상에 영향을 주지 않는다 ( 오히려 악영향을 주는 경우가 더 많다 )
2. 인덱스를 효율적으로 사용하지 못한 경우를 의미한다.

 

위의 두 가지 방법 중 조회의 성능을 향상 시켜주는 조건은 작업 범위 결정 조건이다.

 

이 글에서 인덱스를 '사용'한다라는 것은 인덱스가 작업 범위 결정 조건으로 사용된다라는 의미로 사용하겠다.

 

🐳 인덱스가 사용되지 않는 경우

 

인덱스가 사용되는 방법은 무궁무진 하므로 언제 사용이 안 되는지 아는 것이 더 중요할 것이다.

 

인덱스가 사용되지 않는 조건은 아래와 같다.

1. NOT EQUAL 조건
2. LIKE %문자열 조건
3. 인덱스 칼럼이 변형된 후 비교된 경우
4. NOT-DETERMINISTIC 속성의 스토어드 함수가 비교 조건에 사용된 경우
5. 데이터 타입이 서로 다른 비교 ( 타입을 변환해야 비교가 가능한 경우 )
6. 문자열 데이터의 collation이 다른 경우

 

0. 테스트를 위한 더미 데이터 생성

인덱스 사용 테스트를 위해 10만 개의 더미 데이터를 생성하자.

 

먼저 테이블을 생성하자

CREATE TABLE NBA_PLAYER (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    height INT,
    team_name VARCHAR(255)
    age INT
);

# Team Name 은 실제 NBA 30개 구단의 이름을 사용한다.
      'Atlanta Hawks', 'Boston Celtics', 'Brooklyn Nets', 'Charlotte Hornets',
      'Chicago Bulls', 'Cleveland Cavaliers', 'Dallas Mavericks', 'Denver Nuggets',
      'Detroit Pistons', 'Golden State Warriors', 'Houston Rockets', 'Indiana Pacers',
      'LA Clippers', 'Los Angeles Lakers', 'Memphis Grizzlies', 'Miami Heat',
      'Milwaukee Bucks', 'Minnesota Timberwolves', 'New Orleans Pelicans', 'New York Knicks',
      'Oklahoma City Thunder', 'Orlando Magic', 'Philadelphia 76ers', 'Phoenix Suns',
      'Portland Trail Blazers', 'Sacramento Kings', 'San Antonio Spurs', 'Toronto Raptors',
      'Utah Jazz', 'Washington Wizards'

 

테이블 속성은 아래와 같다.

• id : AUTO_INCREMENT
• name : Player + PK
• height : 180~240 사이의 랜덤 값 ( 단위 cm )
• team_name : NBA 30개 구단 중 임의의 구단 한 개 선택
• age : 20~40 사이의 랜덤 값

 

그리고 프로시저를 이용해 10만개의 더미 데이터를 생성해 주자.

프로시저의 코드는 아래와 같다.

CREATE PROCEDURE attendance.GeneratePlayer()
BEGIN
  DECLARE v_max INT DEFAULT 100000;
  DECLARE v_counter INT DEFAULT 0;
  DECLARE v_height INT;
  DECLARE v_age INT;
  DECLARE v_name VARCHAR(255);
  DECLARE v_team_name VARCHAR(255);

  START TRANSACTION;
  WHILE v_counter < v_max DO
    SET v_height = FLOOR(180 + (RAND() * (241 - 180)));
    SET v_age = FLOOR(20 + (RAND() * (41 - 20)));
    SET v_name = CONCAT('Player ', v_counter + 1);
    
    -- 팀 이름 랜덤 선택
    SET v_team_name = ELT(FLOOR(1 + (RAND() * 30)),
      'Atlanta Hawks', 'Boston Celtics', 'Brooklyn Nets', 'Charlotte Hornets',
      'Chicago Bulls', 'Cleveland Cavaliers', 'Dallas Mavericks', 'Denver Nuggets',
      'Detroit Pistons', 'Golden State Warriors', 'Houston Rockets', 'Indiana Pacers',
      'LA Clippers', 'Los Angeles Lakers', 'Memphis Grizzlies', 'Miami Heat',
      'Milwaukee Bucks', 'Minnesota Timberwolves', 'New Orleans Pelicans', 'New York Knicks',
      'Oklahoma City Thunder', 'Orlando Magic', 'Philadelphia 76ers', 'Phoenix Suns',
      'Portland Trail Blazers', 'Sacramento Kings', 'San Antonio Spurs', 'Toronto Raptors',
      'Utah Jazz', 'Washington Wizards');

    INSERT INTO NBA_PLAYER (name, height, age, team_name) VALUES (v_name, v_height, v_age, v_team_name);

    IF v_counter % 1000 = 0 THEN
      COMMIT;
      START TRANSACTION;
    END IF;

    SET v_counter = v_counter + 1;
  END WHILE;
  COMMIT;
END

 

안정적인 데이터 생성을 위해 1000개 단위로 트랜잭션을 나눠 커밋하게 프로시저를 만들었다.

 

그리고 프로시저를 실행해 데이터를 생성해주자 !

 

결과

프로시저를 이용해 생성한 더미 데이터

 

테스트에 필요한 더미 데이터들 10만개를 생성했다 !!!

 

이제 인덱스가 언제 사용되지 않는지 확인해보자 !

 

1. NOT EQUAL 조건

SELECT 
    AGE
FROM NBA_PLAYER
WHERE AGE <> 30 
# <> 는 같지 않다의 의미이다.

 

NBA_PLAYER 테이블에서 나이가 30이 아닌 선수들을 조회한다고 생각해보자.

이럴 때는 데이터베이스의 데이터를 조회한 후에 나이가 30인 선수들을 필터링 하는 체크 조건 방식으로 인덱스가 사용되게 된다.

그러므로 NOT EQUAL 조건에서는 인덱스가 효율적으로 사용되지 못하게 된다.

 

2. LIKE %문자열 조건

LIKE 함수에서 우측 문자열 일치 조건의 경우를 생각해보자.

VARCHAR, TEXT 값은 어떻게 인덱스가 생성되게 될까 ?

문자열의 경우 문자열의 좌측에서부터 사전순서대로 인덱스가 형성된다. ( 문자열이 너무 길어지면 어느 정도까지만 비교한다 )

 

그런데 문자열의 우측이 일치하는 경우 앞에 몇 글자가 오는지, 어떤 글자고 오는지 알 수 없다.

이 말은 인덱스가 어디를 읽어야할지 알 수 없다는 의미이다.

 

그렇기 때문에 LIKE 우측 패턴 일치 조건도 모든 데이터를 조회한 후에 %문자열 조건에 일치하는 데이터를 필터링 하는 식으로 인덱스를 사용한다.

 

그렇기 때문에 인덱스가 효율적으로 사용되지 못한다.

 

실험

# 쿼리 1, 왼쪽 문자열 일치
EXPLAIN
SELECT TEAM_NAME
FROM NBA_PLAYER
WHERE TEAM_NAME LIKE 'Boston%';

# 쿼리 2, 오른쪽 문자열 매치
EXPLAIN
SELECT TEAM_NAME
FROM NBA_PLAYER
WHERE TEAM_NAME LIKE '%Celtics';

 

두 가지 쿼리의 실행계획을 비교해보자

쿼리 1 : 왼쪽 문자열 일치

쿼리 2 : 오른쪽 문자열 일치

 

먼저 쿼리 1 의 실행계획을 살펴보자

 

쿼리 1 ( 왼쪽 문자열 매칭 실행계획 )

 

• type : range는 인덱스의 특정 범위를 스캔하는 것을 의미하며, 이는 특정 범위의 값을 사용해 인덱스 스캔이 효율적으로 이루어 지고 있는 것을 알 수 있다.
• possible_keys : 사용 가능한 인덱스 후보
• key : 실제 사용된 키 값
• key_len : 사용된 인덱스 키 값의 크기
• rows : 조회할 데이터의 수 / 쿼리 1 에서는 3373 개의 data를 조회한다.
• filtered : 조회한 데이터에서 필터링 되고 남은 퍼센트 / 즉 100% 다 원하는 결과이다.
• Extra : Using Index Condition ( 인덱스가 인덱스 조건을 사용하여 인덱스 스캔이 더 효율적으로 이루어지고 있음을 나타낸다 )

쿼리 1 결과

인덱스가 효율적으로 사용되고 있다. 10만 개의 데이터가 30개 구단에 나누어져 있으므로 구단별로 약 3000개의 데이터를 가져야 한다.

쿼리 1의 실행계획은 정확하게 필요한 데이터 3373개만 읽어오는 것을 확인할 수 있다.

 

 

쿼리 2 ( 오른쪽 문자열 매칭 실행계획 )

 

• type : index type은 인덱스 풀 스캔을 한다는 의미이다. 이는 위에서 살펴본 range 에 비해 비효율적으로 인덱스가 사용되고 있음을 알 수 있다.
• rows : 조회할 데이터의 수 / 쿼리 1 에서는 3373 개의 데이터 였던 것 과는 다르게 99,742 개의 데이터를 조회한다. 10만 개의 데이터 중 거의 모든 데이터를 조회한다고 볼 수 있다.
• filtered : 조회한 데이터에서 필터링 되고 남은 퍼센트 / 즉 조회한 rows중 11.11% 정도만이 필요한 결과이다. 나머지 88.88%는 필터링 된 것을 알 수 있다. 필요하지 않은 88%의 데이터를 읽어 왔음을 알 수 있고 이는 인덱스가 비효율적으로 사용되고 있음을 나타낸다.
• Extra
  • Using where : MySQL 서버가 데이터를 필터링하기 위해 추가적인 조건 검사를 수행하고 있음을 나타낸다. 인덱스를 통해 대략적으로 필터링된 rows에서 WHERE 절에 정의된 조건에 부합하는 데이터만 최종적으로 선택한다.
  • Using index : Using index는 쿼리 처리가 인덱스만을 사용하여 완료될 수 있음을 나타낸다. 현재 위의 쿼리는 team_name IDX를 사용하는데 SELECT 문에 team_name을 제외한 다른 것을 조회하지 않는다. 그러므로 IDX만으로 쿼리를 처리할 수 있다.

쿼리 2 결과

쿼리 2에서난 인덱스가 효율적으로 사용되고 있지 않다. 필요한 데이터 3373건을 조회하기 위해 필요하지 않은 약 95,000개의 데이터를 읽어오고 있다. 

 

결론

오른쪽 문자열 매칭의 경우에는 인덱스가 효율적으로 사용되지 못하고 체크 조건으로 사용된다.

 

3. 인덱스 칼럼이 변형된 후 비교된 경우

인덱스는 원본 데이터에 대한 정렬된 맵핑을 유지하지만, 변형된 데이터에 대해서는 아무런 정보를 갖고 있지 않기 때문에

인덱스 칼럼을 변형시킨 경우에는 변형시킨 값에 대해서는 인덱스를 가지고 있지 않아

인덱스가 사용되지 않는다.

 

실험

# 7feet Player를 찾는 쿼리 (7feet = 약 213cm)
EXPLAIN
SELECT 
    NAME, HEIGHT
FROM
    NBA_PLAYER
WHERE
    HEIGHT / 30.42 >= 7;
    # cm -> feet로 단위 환

# 7feet Player를 찾는 쿼리를 단위환산 없이 실
EXPLAIN
SELECT 
    NAME, HEIGHT
FROM
    NBA_PLAYER
WHERE
    HEIGHT >= 213;

 

위의 예시를 살펴보자

 

미국은 키를 나타낼 때 ft 단위를 주로 사용한다. ( 1ft = 30.42cm )

NBA에서 키 큰 선수를 7feet 선수라고 하는데 나는 7feet 선수를 조회하기 위해 위와 같이 쿼리문을 작성했다.

 

쿼리 1 의 경우에는 height 에서 사용하는 cm 단위를 feet 로 변형한 후에 WHERE 절에서 비교를 한다.

쿼리 2 의 경우에는 height 값을 변형 없이 바로 비교를 했다.

 

각 쿼리의 결과를 살펴보자

 

쿼리 1 결과

 

• type : ALL 한다는 의미이다. 풀 테이블 스캔을 한다는 의미이다. 인덱스를 가장 비효율 적으로 사용하고 있다고 볼 수 있다.
• possible_keys , key : 분명 height에 인덱스를 생성했음에도 불구하고 아무런 키가 사용되고 있지 않다 !!!
  • 이는 HEIGHT 값을 그대로 쓰는 것이 아니라 인덱스 키 값을 변형해서 비교했기 때문이다 !
• rows : 조회할 데이터의 수 / 쿼리 1 에서는 99,742 개의 데이터를 조회한다. 10만 개의 데이터 중 거의 모든 데이터를 조회한다고 볼 수 있다.
• filtered : 조회한 데이터에서 필터링 되고 남은 퍼센트가 100% 이다 ! 모든 데이터가 다 필요한 것이 아니라 사용된 인덱스가 없기 때문에 모든 데이터를 가지고 온 후 WHERE 절에 맞게 필터링 하기 때문에 filtered 값이 100%로 나왔다.
• Extra
  • Using where : MySQL 서버가 데이터를 필터링하기 위해 추가적인 조건 검사를 수행하고 있음을 나타낸다. rows에서 WHERE 절에 정의된 조건에 부합하는 데이터만 최종적으로 선택한다.

쿼리 2 결과

• type : range 는 인덱스를 활용해서 인덱스 범위 스캔을 시행했다는 것을 알 수 있다. 이는 인덱스를 효율적으로 사용하고 있음을 나타낸다.
• possible_keys , key : HEIGHT 인덱스를 사용하고 있다.
• key_len : int 형은 기본 4Byte에 추가적으로 메모리 주소에 해당하는 바이트가 추가 돼 5Byte 값이 나온 것으로 보인다.
• rows : 조회할 데이터의 수 / 키가 213cm 이상인 49871개의 데이터만 조회를 했다. 
• filtered : 인덱스를 이용해 가지고 온 49,871 개의 데이터 중 100%가 원하는 결과이기 때문에 filtered 값이 100%로 나왔다.
• Extra
  • Using Index Condition : MySQL 서버가 데이터를 필터링하기 위해 추가적인 조건 검사를 수행하고 있음을 나타낸다. rows에서 WHERE 절에 정의된 조건에 부합하는 데이터만 최종적으로 선택한다.

결론

인덱스를 지정한 값을 변형한 경우에는 인덱스가 사욛되지 않는 것을 확인할 수 있다 !!

 

 

4. NOT-DETERMINISTIC 속성의 스토어드 함수가 비교 조건에 사용된 경우

NOT-DETERMINISTRIC 속성의 스토어드 함수란 ?
동일한 입력값에 대해 같은 결과를 보장하지 않는 함수
즉, 함수가 외부 변수나 데이터베이스의 상태 등에 의존해 실행시마다 같은 결과를 보장하지 않는 함수를 의미한다.

 

이 부분은 당연한 것으로 예상된다.

그때그때 바뀌는 값에 어떻게 인덱스를 사용할 수 있겠는가?

 

5. 데이터 타입이 서로 다른 비교

이번에도 3번에서와 비슷한 쿼리를 이용해 결과를 확인해보자.

 

실험

# HEIGHT를 형변환 하여 비교
EXPLAIN
SELECT 
    NAME, HEIGHT
FROM
    NBA_PLAYER
WHERE
    CAST(HEIGHT AS CHAR) >= '213';

# HEIGHT를 형변환 없이 비교
EXPLAIN
SELECT 
    NAME, HEIGHT
FROM
    NBA_PLAYER
WHERE
    HEIGHT >= 213;

 

1번 쿼리는 HEIGHT를 형변환 한 후에 비교를 시행하고

2번 쿼리는 HEIGHT를 형변환 하지 않고 비교한다.

 

1번 쿼리문의 결과를 확인해보자 ! ( 2번 쿼리문의 결과는 3. 인덱스 값을 변형 한 경우에 인덱스가 사용되지 않는다 를 보면 결과를 알 수 있다. ) 

 

1번 쿼리 ( 형변환을 한 경우의 결과 )

 

• type : ALL 한다는 의미이다. 풀 테이블 스캔을 한다는 의미이다. 인덱스를 가장 비효율 적으로 사용하고 있다고 볼 수 있다.
• possible_keys , key : 분명 height에 인덱스를 생성했음에도 불구하고 아무런 키가 사용되고 있지 않다 !!!
  • 이는 HEIGHT 값을 그대로 쓰는 것이 아니라 인덱스 키 값을 변형해서 비교했기 때문이다 !
• rows : 조회할 데이터의 수 / 쿼리 1 에서는 99,742 개의 데이터를 조회한다. 10만 개의 데이터 중 거의 모든 데이터를 조회한다고 볼 수 있다.
• filtered : 조회한 데이터에서 필터링 되고 남은 퍼센트가 100% 이다 ! 모든 데이터가 다 필요한 것이 아니라 사용된 인덱스가 없기 때문에 모든 데이터를 가지고 온 후 WHERE 절에 맞게 필터링 하기 때문에 filtered 값이 100%로 나왔다.
• Extra
  • Using where : MySQL 서버가 데이터를 필터링하기 위해 추가적인 조건 검사를 수행하고 있음을 나타낸다. rows에서 WHERE 절에 정의된 조건에 부합하는 데이터만 최종적으로 선택한다.

결론

인덱스키 값을 형변환 한 경우에는 인덱스가 사용되지 않는 것을 확인할 수 있다 !!

 

6. 문자열의 Collation이 다른 경우

마지막 상황은 문자열의 Collation이 다른 경우입니다.

 

이번에도 예제와 함께 살펴봅시다 !

실험

# team_name의 collation은 utf8mb4_9000_ai_ci 를 사용중입니다.

# 쿼리 1 : team_name 과는 다른 collation으로 비교하는 경우
EXPLAIN
SELECT team_name ,name
FROM NBA_PLAYER
WHERE team_name = 'Los Angeles Lakers' COLLATE utf8mb4_unicode_ci;

# 쿼리 2 : team_name 과 같은 collation으로 비교하는 경우
EXPLAIN
SELECT team_name ,name
FROM NBA_PLAYER
WHERE team_name = 'Los Angeles Lakers';

 

위의 두 쿼리의 실행계획을 살펴보며 차이점을 살펴봅시다 ! 

 

1번 쿼리 ( 다른 Collation으로 비교한 경우 )

 

key 값을 보면 분명 team_name에 인덱스를 생성했고, 동등 조건을 사용했기 때문에 인덱스가 사용되어야 정상인 쿼리문인데 인덱스가 사용되고 있지 않음을 실행계획을 통해서 확인할 수 있다 !!!

 

 

key 값을 보면 분명 team_name에 인덱스를 생성했고, 동등 조건을 사용했기 때문에 인덱스가 사용되어야 정상인 쿼리문인데 인덱스가 사용되고 있지 않음을 실행계획을 통해서 확인할 수 있다 !!!

 

2번 쿼리 ( 같은 Collation으로 비교한 경우 )

 

다른 것이라고는 collation을 어떤 것을 사용했냐 차이 뿐인데 바로 인덱스가 잘 사용되고 있음을 확인할 수 있다 !!!

 

결론

문자열을 비교할 때는 collation도 반드시 확인해야 한다 !!!

 

🐳 참고

• [도서] Real MySQL 8.0