들어가며

MariaDB를 사용하다 보면 MySQL 8.0에서 작성했던 SQL을 그대로 가져왔는데 갑자기 Syntax Error가 발생하는 경우가 있습니다.

특히 인덱스를 생성하면서 함수나 표현식을 직접 넣는 경우가 대표적입니다.

MySQL 8.0에서는 다음과 같은 방식으로 함수 기반 인덱스를 만들 수 있습니다.

CREATE INDEX idx_abs_col1
ON t1 ((ABS(col1)));

즉, 컬럼 자체가 아니라 함수의 결과값을 인덱스로 만들어 검색 성능을 높이는 방식입니다.

그런데 MariaDB에서는 이런 MySQL식 직접 표현식 인덱스 문법을 그대로 사용할 수 없습니다.

그렇다면 MariaDB에서는 함수 결과에 인덱스를 걸 수 없는 것일까요?

그렇지는 않습니다.

MariaDB에서는 Generated Column(생성 컬럼)을 이용하면 비슷한 효과를 구현할 수 있습니다.

특히 VIRTUAL 컬럼에 인덱스를 생성하는 방법이 대표적입니다. MariaDB 공식 문서에서도 생성 컬럼에 인덱스를 정의할 수 있으며, 인덱스가 생성된 경우 옵티마이저가 일반 컬럼의 인덱스와 같은 방식으로 활용할 수 있다고 설명합니다.

MySQL과 MariaDB의 가장 큰 차이

먼저 두 데이터베이스의 접근 방식부터 살펴보겠습니다.

MySQL 8.0

MySQL에서는 함수 또는 표현식을 인덱스 정의에 직접 사용할 수 있습니다.

CREATE INDEX idx_abs_col1
ON t1 ((ABS(col1)));

별도의 컬럼을 만들어 줄 필요가 없습니다.

MariaDB

MariaDB에서는 일반적으로 생성 컬럼을 하나 만든 다음 그 컬럼에 인덱스를 생성하는 방식으로 접근합니다.

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL,

    lower_email VARCHAR(255)
        GENERATED ALWAYS AS (LOWER(email)) VIRTUAL
);

CREATE INDEX idx_lower_email
ON users(lower_email);

구조를 간단하게 표현하면 다음과 같습니다.

email
  │
  ▼
LOWER(email)
  │
  ▼
lower_email
  │
  ▼
idx_lower_email

결국 함수의 결과값을 별도의 생성 컬럼으로 만들고 그 컬럼에 인덱스를 생성하는 것입니다.

MariaDB에서는 이런 방식이 오래전부터 사용되어 왔으며, 공식 자료에서도 JSON 값이나 날짜 관련 값을 가상 컬럼으로 만들어 인덱싱하는 사례를 소개하고 있습니다.

MariaDB 버전별로 보면?

여기서 버전 이야기를 조금 정리할 필요가 있습니다.

MariaDB의 Generated Column 자체는 5.2부터 지원됐습니다.

다만 함수 결과를 인덱싱하는 용도로 사용할 때 중요한 변화는 MariaDB 10.2입니다.

MariaDB 10.2에서는 VIRTUAL 컬럼도 인덱싱할 수 있게 되었고, MariaDB 공식 발표에서도 Indexes for virtual columns를 10.2의 주요 기능으로 소개하고 있습니다.

따라서 실무적으로는 다음처럼 이해하는 것이 편합니다.

MariaDB 버전	Generated Column	VIRTUAL 컬럼 인덱싱
5.2 이전	❌	❌
5.2 ~ 10.1	✅	제한적
10.2 이상	✅	✅
12.3	✅	✅

현재 MariaDB 12.3 LTS에서도 이 방식은 사용할 수 있습니다. MariaDB 12.3은 2026년 5월 GA된 LTS 버전이며 2029년 6월까지 지원됩니다.

실제로 만들어보자

예를 들어 사용자 이메일을 대소문자 구분 없이 검색한다고 가정해 보겠습니다.

원본 데이터가 다음과 같이 저장되어 있다고 해보죠.

Test@example.com
TEST@example.com
test@example.com

다음과 같은 검색을 자주 한다면,

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

email 컬럼에 단순 인덱스가 있어도 LOWER(email)이라는 표현식 때문에 원하는 방식으로 인덱스를 활용하지 못할 수 있습니다.

이때 생성 컬럼을 이용할 수 있습니다.

1. 생성 컬럼 만들기

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL,

    lower_email VARCHAR(255)
        GENERATED ALWAYS AS (LOWER(email)) VIRTUAL
);

여기서 핵심은 다음 부분입니다.

GENERATED ALWAYS AS (LOWER(email)) VIRTUAL

lower_email은 직접 값을 입력하는 일반 컬럼이 아닙니다.

email 값이 변경되면 LOWER(email)을 기준으로 값이 만들어집니다.

그리고 VIRTUAL이므로 생성된 값을 일반 컬럼처럼 테이블에 별도로 저장하지 않습니다. MariaDB 공식 문서에서는 PERSISTENT/STORED는 값을 저장하고, VIRTUAL은 값을 테이블에 저장하지 않는 방식으로 설명합니다.

2. 생성 컬럼에 인덱스 만들기

이제 생성된 컬럼에 인덱스를 추가합니다.

CREATE INDEX idx_lower_email
ON users(lower_email);

여기까지 하면 구조는 다음과 같습니다.

email
 ↓
LOWER(email)
 ↓
lower_email
 ↓
idx_lower_email

MariaDB 입장에서는 lower_email이라는 일반적인 인덱스 대상 컬럼이 존재하기 때문에 인덱스를 구성할 수 있습니다.

3. 생성 컬럼을 직접 사용하기

가장 명확한 방법은 생성 컬럼을 WHERE 조건에서 직접 사용하는 것입니다.

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

이 경우 idx_lower_email 인덱스를 사용할 수 있습니다.

실제 실행 계획은 EXPLAIN으로 확인하는 것이 좋습니다.

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

실무에서는 단순히 “인덱스가 만들어졌다”에서 끝내지 말고 실제 실행 계획에서 해당 인덱스가 선택되는지 확인하는 것이 중요합니다.

---

원래 함수 조건을 그대로 사용하면?

여기서 MariaDB 버전에 따라 차이가 있습니다.

예전 MariaDB에서는 다음과 같이 원래 표현식을 사용했을 때 생성 컬럼의 인덱스를 옵티마이저가 자동으로 연결하지 못하는 경우가 있었습니다.

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

하지만 MariaDB 11.8부터는 옵티마이저가 WHERE 조건의 표현식과 인덱스가 만들어진 VIRTUAL 컬럼을 인식해 range/ref 접근에 활용할 수 있도록 개선됐습니다.

따라서 최신 MariaDB를 사용한다면 이 부분은 예전 자료와 다르게 봐야 합니다.

특히 MariaDB 12.x 계열을 사용한다면 EXPLAIN을 통해 실제 실행 계획을 확인하는 것이 가장 정확합니다.

VIRTUAL과 PERSISTENT 중 무엇을 써야 할까?

생성 컬럼에는 크게 두 가지 방식이 있습니다.

VIRTUAL

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

값 자체는 테이블에 저장하지 않는 방식입니다.

대신 해당 컬럼에 인덱스를 만들면 인덱스에는 검색을 위한 값이 저장됩니다.

따라서 “VIRTUAL이면 아무런 디스크 공간도 사용하지 않는다”라고 표현하면 정확하지 않습니다.

테이블 데이터 영역에는 생성값을 별도로 저장하지 않지만, 인덱스를 생성했다면 인덱스 공간은 사용합니다.

PERSISTENT

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

PERSISTENT는 생성된 값을 실제로 저장합니다.

따라서 조회 시 값을 다시 계산하는 부담을 줄일 수 있지만 그만큼 저장 공간을 사용합니다.

MariaDB 공식 문서에서도 PERSISTENT는 생성된 값을 저장하고 VIRTUAL은 저장하지 않는 방식으로 구분합니다.

어떤 방식을 선택할까?

간단하게 정리하면 다음과 같습니다.

구분	VIRTUAL	PERSISTENT
생성값 테이블 저장	❌	✅
인덱스 생성	✅	✅
테이블 저장공간	상대적으로 적음	증가
계산 결과 저장	❌	✅
함수 결과를 인덱싱	가능	가능
일반적인 선택	⭐ 많이 사용	상황에 따라 선택

단, 실제 성능은 표현식의 복잡도와 데이터 규모, 인덱스 크기, 조회 패턴 등에 따라 달라집니다.

무조건 VIRTUAL이 빠르거나 PERSISTENT가 빠르다고 단정하기보다는 실제 쿼리를 기준으로 EXPLAIN과 성능 테스트를 해보는 것이 좋습니다.

JSON 데이터에서도 상당히 유용하다

이 방식은 단순히 LOWER() 같은 문자열 함수에만 사용할 수 있는 것이 아닙니다.

MariaDB에서 JSON 데이터의 특정 값을 자주 검색해야 할 때도 상당히 유용합니다.

예를 들어 다음과 같은 JSON 데이터가 있다고 가정해 보겠습니다.

{
    "name": "홍길동",
    "color": "white"
}

JSON 내부의 color 값을 검색하는 경우,

JSON_VALUE(attr, '$.color')

라는 표현식을 사용할 수 있습니다.

이 값을 생성 컬럼으로 만들어보겠습니다.

ALTER TABLE products
ADD attr_color VARCHAR(32)
    AS (JSON_VALUE(attr, '$.color'));

그리고 인덱스를 생성합니다.

CREATE INDEX idx_products_attr_color
ON products(attr_color);

이제 다음과 같이 검색할 수 있습니다.

SELECT *
FROM products
WHERE attr_color = 'white';

MariaDB 공식 자료에서도 JSON 값 추출을 VIRTUAL 컬럼으로 만들고 해당 컬럼에 인덱스를 생성하는 방법을 예제로 소개하고 있습니다.

---

주의해야 할 점

생성 컬럼을 이용한다고 해서 모든 함수를 자유롭게 사용할 수 있는 것은 아닙니다.

특히 결과가 매번 달라질 수 있는 비결정적 함수는 인덱스에 사용할 생성 컬럼을 설계할 때 주의해야 합니다.

대표적으로 다음과 같은 함수가 있습니다.

NOW()
RAND()

예를 들어 NOW()는 실행 시점에 따라 결과가 달라집니다.

NOW()

오늘 실행했을 때와 내일 실행했을 때 결과가 같을 수 없습니다.

이런 값을 인덱스의 기준으로 사용하는 것은 적절하지 않습니다.

따라서 생성 컬럼을 인덱싱할 때는 동일한 입력값에 대해 안정적으로 동일한 결과를 반환하는 표현식인지 확인하는 것이 중요합니다.

또한 생성 컬럼의 결과가 SQL Mode 등의 환경 설정에 영향을 받을 수 있는 경우에도 주의해야 합니다. MariaDB 공식 문서에서도 생성 컬럼의 표현식과 SQL Mode 일관성에 관한 제한사항을 별도로 설명하고 있습니다.

---

MariaDB 12.x에서는 어떻게 봐야 할까?

MariaDB 12.3은 현재 LTS 버전으로 제공되고 있습니다.

그리고 MariaDB 12.x 계열에서는 가상 컬럼과 옵티마이저의 연계도 계속 개선되고 있습니다.

예를 들어 MariaDB 12.1에서는 GROUP BY, ORDER BY에서 기존 VIRTUAL 컬럼과 일치하는 표현식을 옵티마이저가 인식해 해당 인덱스를 사용할 수 있도록 개선됐습니다.

따라서 과거에 작성된 MariaDB 관련 글에서

“MariaDB에서는 함수 표현식으로 검색하면 생성 컬럼 인덱스를 절대 사용할 수 없다.”

라고 단정적으로 설명하고 있다면 사용 중인 MariaDB 버전을 함께 확인하는 것이 좋습니다.

---

마무리

MySQL 8.0에서 함수 기반 인덱스를 접한 뒤 MariaDB로 넘어오면 가장 먼저 느끼는 차이가 바로 이 부분입니다.

MySQL은 다음처럼 표현식을 인덱스 정의에 직접 사용할 수 있습니다.

CREATE INDEX idx_test
ON users ((LOWER(email)));

반면 MariaDB에서는 다음과 같이 접근하는 것이 기본적인 방법입니다.

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL,

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

CREATE INDEX idx_lower_email
ON users(lower_email);

결국 핵심은 어렵지 않습니다.

함수 결과를 생성 컬럼으로 만들고 → 그 컬럼에 인덱스를 생성한다.

이것이 MariaDB에서 함수 결과를 인덱싱할 때 알아두면 좋은 핵심 패턴입니다.

특히 문자열 가공, JSON 값 추출, 날짜 변환, 특정 값 계산처럼 동일한 표현식을 반복해서 조회하는 시스템이라면 상당히 유용하게 활용할 수 있습니다.

그리고 마지막으로 하나 기억해두면 좋습니다.

인덱스를 만들었다고 성능이 자동으로 좋아지는 것은 아니다.
실제 쿼리에서는 반드시 EXPLAIN으로 실행 계획을 확인하자.

MariaDB의 옵티마이저는 버전별로 생성 컬럼 인덱스를 활용하는 방식도 계속 개선되고 있기 때문에, 사용 중인 MariaDB 버전을 기준으로 테스트하는 것이 가장 정확합니다.

핵심만 다시 정리하면

MySQL 8.0
함수/표현식
    ↓
Functional Index
    ↓
인덱스

MariaDB
함수/표현식
    ↓
Generated Column
    ↓
VIRTUAL / PERSISTENT
    ↓
INDEX

즉, MariaDB에서는 생성 컬럼을 중간에 하나 두는 것이 핵심입니다.