그래서 실제 조회할 때 “인덱스”를 제대로 타고 있는 걸까?

들어가며

앞에서 VIRTUAL과 PERSISTENT를 비교하면서 계속 신경 쓰였던 부분이 하나 있었습니다.

그래서 실제 조회할 때 인덱스를 제대로 타고 있는 걸까?

생성 컬럼을 만들고 인덱스까지 추가했다고 해서 무조건 안심할 수는 없습니다.

결국 중요한 것은 실제 쿼리를 실행했을 때 옵티마이저가 내가 만든 인덱스를 선택했는지입니다.

그래서 이번에는 EXPLAIN을 사용해서 생성 컬럼 인덱스가 실제 실행 계획에 어떻게 나타나는지 확인해봤습니다.

테스트용 테이블

먼저 앞에서 사용했던 것과 비슷하게 이메일을 소문자로 변환하는 생성 컬럼을 만들어봤습니다.

CREATE TABLE users_virtual (
    id BIGINT NOT NULL AUTO_INCREMENT,
    email VARCHAR(255) NOT NULL,

    lower_email VARCHAR(255)
        AS (LOWER(email)) VIRTUAL,

    PRIMARY KEY (id),
    INDEX idx_lower_email (lower_email)
);

핵심은 이 부분입니다.

lower_email VARCHAR(255)
    AS (LOWER(email)) VIRTUAL

그리고 생성 컬럼에 인덱스를 걸었습니다.

INDEX idx_lower_email (lower_email)

이제 실제 데이터가 있다고 가정하고 다음과 같은 조회를 해보겠습니다.

SELECT *
FROM users_virtual
WHERE lower_email = 'test@example.com';

실행 계획부터 확인했습니다.

EXPLAIN
SELECT *
FROM users_virtual
WHERE lower_email = 'test@example.com';

여기서 가장 먼저 확인할 부분은 key입니다.

EXPLAIN에서 가장 먼저 볼 것

예를 들어 다음과 같은 결과가 나왔다고 해보겠습니다.

+----+-------------+---------------+------+-----------------+-----------------+---------+-------+------+-------------+
| id | select_type | table         | type | possible_keys   | key             | key_len | ref   | rows | Extra       |
+----+-------------+---------------+------+-----------------+-----------------+---------+-------+------+-------------+
|  1 | SIMPLE      | users_virtual | ref  | idx_lower_email | idx_lower_email | 1023    | const |    1 | Using where |
+----+-------------+---------------+------+-----------------+-----------------+---------+-------+------+-------------+

여기서 눈여겨볼 부분은 이것입니다.

possible_keys = idx_lower_email
key           = idx_lower_email

possible_keys에는 사용할 수 있는 인덱스가 표시되고,

key에는 실제로 선택된 인덱스가 표시됩니다.

따라서 여기서는

key = idx_lower_email

이므로 생성 컬럼에 만들어 놓은 인덱스를 실제 조회에 사용하고 있다는 것을 확인할 수 있습니다.

개발하면서 EXPLAIN을 볼 때 저는 possible_keys보다 key를 먼저 보는 편이 훨씬 편했습니다.

사용할 수 있는 인덱스와 실제 사용하는 인덱스는 다를 수 있기 때문입니다.

key가 NULL이면?

반대로 이런 결과가 나올 수도 있습니다.

+----+-------------+---------------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table         | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+---------------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | users_virtual | ALL  | idx_lower_email | NULL | NULL | NULL | 100000 | Using where |
+----+-------------+---------------+------+---------------+------+---------+------+--------+-------------+

이 경우에는 조금 다르게 봐야 합니다.

key = NULL

즉, idx_lower_email을 사용할 수 있는 후보로 알고 있더라도 실제 실행 계획에서는 사용하지 않았다는 의미입니다.

특히

type = ALL

이라면 테이블 전체를 읽는 실행 계획일 가능성이 높습니다.

데이터가 몇백 건 정도라면 별 문제가 없어 보일 수 있습니다.

하지만 데이터가 수십만 건, 수백만 건으로 늘어나면 이야기가 달라집니다.

그래서 생성 컬럼을 추가한 뒤에는 단순히

CREATE INDEX ...

까지만 확인할 것이 아니라,

EXPLAIN SELECT ...

까지 확인하는 습관이 꽤 중요하다고 느꼈습니다.

그런데 WHERE 조건을 이렇게 쓰면?

여기서 조금 재미있는 부분이 있습니다.

우리가 만든 생성 컬럼은 다음과 같습니다.

lower_email AS (LOWER(email)) VIRTUAL

그러면 다음 두 쿼리를 생각해볼 수 있습니다.

1. 생성 컬럼을 직접 조회

SELECT *
FROM users_virtual
WHERE lower_email = 'test@example.com';

2. 원본 컬럼에 함수를 직접 적용

SELECT *
FROM users_virtual
WHERE LOWER(email) = 'test@example.com';

둘이 논리적으로는 같은 조건처럼 보입니다.

하지만 실행 계획이 항상 동일하다고 단정하면 안 됩니다.

특히 MariaDB 버전에 따라 생성 컬럼의 표현식을 옵티마이저가 인식하는 방식에 차이가 있습니다.

따라서 실제 운영 환경에서는 반드시 사용하는 MariaDB 버전에서 EXPLAIN을 확인하는 것이 좋습니다.

MariaDB 11.8부터는 조금 달라졌다

이 부분은 이번 테스트에서 특히 기억해둘 만했습니다.

MariaDB는 버전이 올라가면서 인덱스가 생성된 VIRTUAL 컬럼과 WHERE 조건에 사용된 표현식을 옵티마이저가 연결해서 인식하는 기능이 개선됐습니다.

그래서 최신 버전에서는 생성 컬럼을 활용한 쿼리의 실행 계획을 볼 때 예전 버전과 결과가 달라질 수 있습니다.

예를 들어 생성 컬럼이

lower_email AS (LOWER(email)) VIRTUAL

이라면 다음과 같은 조건을 사용하는 경우입니다.

WHERE LOWER(email) = 'test@example.com'

이런 상황에서 실제 인덱스가 활용되는지 EXPLAIN으로 확인하는 것이 중요합니다.

즉,

생성 컬럼에 인덱스를 만들었다 → 끝

이 아니라

생성 컬럼에 인덱스를 만들었다 → 실제 쿼리를 EXPLAIN → key 확인

순서로 보는 것이 안전합니다.

rows도 같이 봐야 했다

EXPLAIN에서 key만 보고 끝내면 아쉬운 부분도 있습니다.

저는 다음으로 rows를 확인했습니다.

key  = idx_lower_email
rows = 1

물론 rows는 실제 반환 행 수와 정확히 같은 값이라고 생각하면 안 됩니다.

옵티마이저가 실행 전에 예상하는 값이기 때문입니다.

그래도 전체 테이블이 100만 건인데

rows = 1

처럼 매우 적은 범위로 접근할 것으로 예상하고 있다면 상당히 좋은 신호입니다.

반대로 인덱스를 사용하고 있더라도 예상 읽기 건수가 지나치게 많다면 다른 문제가 있는지 살펴볼 필요가 있습니다.

type도 함께 확인

EXPLAIN에서 type도 같이 봤습니다.

예를 들어

type = ref

라면 일반적인 인덱스 동등 비교에서 자주 볼 수 있는 접근 방식입니다.

반대로

type = ALL

이라면 테이블 전체를 읽는 방식이므로 한 번 더 확인해볼 필요가 있습니다.

다만 type 하나만 보고 무조건 좋다, 나쁘다고 판단하면 안 됩니다.

데이터 양과 조건, 통계 정보, 쿼리 형태에 따라 실행 계획은 달라질 수 있습니다.

결국 중요한 것은 전체 실행 계획을 함께 보는 것입니다.

생성 컬럼 인덱스를 확인할 때 체크할 것

이번에 직접 확인해보면서 정리해보니 EXPLAIN에서 다음 정도를 먼저 확인하면 편했습니다.

항목	확인 내용
possible_keys	사용할 가능성이 있는 인덱스
key	실제 선택된 인덱스
type	테이블 접근 방식
rows	옵티마이저가 예상하는 읽기 행 수
Extra	추가적인 실행 정보

특히 가장 먼저 볼 것은 역시

key

였습니다.

내가 만든

idx_lower_email

이 실제로 선택됐는지를 바로 확인할 수 있기 때문입니다.

VIRTUAL이라고 무조건 느린 것은 아니었다

앞선 VIRTUAL vs PERSISTENT 테스트에서 느꼈던 부분과도 연결됩니다.

처음에는 단순하게 생각했습니다.

VIRTUAL은 조회할 때 계산하니까 느리지 않을까?

그런데 인덱스를 만들어 놓고 실제 실행 계획을 확인해보면 조금 다르게 생각할 필요가 있습니다.

검색 조건에 생성 컬럼 인덱스가 제대로 사용된다면 단순히 VIRTUAL이라는 이유만으로 조회가 느리다고 판단할 수 없습니다.

반대로 PERSISTENT라고 해서 무조건 모든 쿼리가 빨라지는 것도 아닙니다.

결국 중요한 것은

생성 컬럼 방식
       ↓
인덱스 구성
       ↓
실제 WHERE 조건
       ↓
EXPLAIN 실행 계획
       ↓
실제 데이터량에서의 성능

이 흐름으로 확인하는 것이었습니다.

결국 EXPLAIN이 답이었다

이번에 VIRTUAL과 PERSISTENT를 비교하면서 처음에는 저장 방식 자체의 차이에 집중했습니다.

그런데 실제 개발 관점에서는 한 단계 더 들어가야 했습니다.

CREATE INDEX

를 했다고 해서 정말 빨라졌는지 알 수 있는 것은 아니었습니다.

실제로 중요한 것은

EXPLAIN
SELECT ...

으로 확인하는 것이었습니다.

특히 생성 컬럼을 검색 조건으로 사용한다면 다음 세 가지는 꼭 확인하는 것이 좋습니다.

첫 번째, 생성 컬럼에 인덱스가 존재하는가

SHOW INDEX FROM users_virtual;

두 번째, 실행 계획에서 해당 인덱스를 선택했는가

EXPLAIN
SELECT *
FROM users_virtual
WHERE lower_email = 'test@example.com';

세 번째, 실제 데이터가 많아졌을 때도 좋은 실행 계획을 유지하는가

이 세 가지까지 확인해야 비로소 생성 컬럼 인덱스를 제대로 적용했다고 볼 수 있었습니다.

마무리

이번 테스트를 하면서 느낀 건 꽤 단순했습니다.

인덱스는 만드는 것보다 실제로 타는지 확인하는 것이 더 중요했습니다.

특히 MariaDB의 Generated Column을 이용하면 함수 결과를 별도의 컬럼처럼 다루면서 인덱스를 구성할 수 있기 때문에, 함수 기반 검색을 구현할 때 상당히 유용합니다.

하지만

INDEX idx_lower_email (lower_email)

을 추가했다고 바로 성능이 보장되는 것은 아닙니다.

실제 쿼리를 실행하고,

EXPLAIN

으로

key
type
rows
Extra

를 확인해보는 것.

결국 이 과정이 VIRTUAL/PERSISTENT 선택보다 더 현실적인 성능 검증 방법이라는 생각이 들었습니다.