[Real Mysql] 인덱스 - part.2 (8.5 - 8.10)

 

 

전문 검색 인덱스

전문 검색 인덱스는 텍스트 기반의 검색을 효율적으로 수행하기 위한 특수한 인덱스이다.

우리가 자주 사용하는 LIKE 쿼리의 예시인 LIKE '%키워드%' 와 같은 패턴 검색보다 훨씬 빠르고 정교한 검색이 가능하다.

 

알고리즘에는 아래 두개에 대한 설명이 나와있는데,

1. 어근 분석 알고리즘

어근 분석 알고리즘은 단어를 기본 형태로 변환하여 인덱싱하는 방법이다.

• 작동 방식: 단어의 접미사를 제거 후 어근 추출
  • e.g) running, runs, ran -> run
  • 영어 위주로 최적화되어 있으며, Poter Stemmer 알고리즘 사용
    • Poter Stemmer 알고리즘에 대한 설명
• 장점: 다양한 형태의 단어를 하나의 어근으로 통합하여 검색 가능
• 단점: 한국어, 중국어, 일본어 등 비영어권 언어에서 부적합

2. n-gram 알고리즘

n-gram 알고리즘은 텍스트를 n개의 연속된 문자 단위로 분리하여 인덱싱하는 방식이다.

• 작동방식: 텍스트를 설정된 크기(보통 2글자)로 분할
• 장점: 한국어, 중국어, 일본어에 적합, 형태소 분석이 어려운 언어에 적합, 부분 문자열 검색 가능
• 단점: 인덱스 크기가 어근 분석보다 큼

 

3. 불용어 (Stopwords) 관리

불용어: 검색에서 제외할 의미 없는 단어들

 

1) 기본 불용어 확인

-- InnoDB 기준
SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD;

 

2) 불용어 비활성화

-- 전역 설정
SET GLOBAL innodb_ft_enable_stopword = OFF;

-- 세션 설정
SET SESSION innodb_ft_enable_stopword = OFF;

 

 

3) 사용자 정의 불용어 사용

-- 1. 불용어 테이블 생성
CREATE TABLE stopwords_tb (
    value VARCHAR(30)
) ENGINE = INNODB;

-- 2. 불용어 추가
INSERT INTO stopwords_tb VALUES 
('그'),
('이'),
('저'),
('것'),
('및'),
('등');

-- 3. 불용어 테이블 지정
SET GLOBAL innodb_ft_server_stopword_table = '데이터베이스/stopwords_tb';

-- 4. 전문 검색 인덱스 생성 (불용어 테이블 설정 후)
CREATE FULLTEXT INDEX idx_content ON articles(content) WITH PARSER ngram;

 

 

함수 기반 인덱스

1) 가상 컬럼을 이용한 인덱스: 가상 컬럼을 먼저 생성한 후 해당 컬럼에 인덱스를 생성하는 방식

 

-- 테이블 생성 시 가상 컬럼 추가
CREATE TABLE users (
    id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    created_at DATETIME,
    -- 가상 컬럼 정의
    full_name VARCHAR(101) AS (CONCAT(first_name, ' ', last_name)) VIRTUAL,
    email_domain VARCHAR(50) AS (SUBSTRING_INDEX(email, '@', -1)) STORED,
    created_year INT AS (YEAR(created_at)) VIRTUAL
) ENGINE=InnoDB;

-- 가상 컬럼에 인덱스 생성
CREATE INDEX idx_full_name ON users(full_name);
CREATE INDEX idx_email_domain ON users(email_domain);
CREATE INDEX idx_created_year ON users(created_year);

 

 

VIRTUAL과 STORED 방식 사용 가능

-- VIRTUAL: 디스크에 저장하지 않고 조회 시 계산 (기본값)
-- - 저장 공간 절약
-- - 인덱스 생성 가능
ALTER TABLE users 
ADD COLUMN lower_email VARCHAR(100) AS (LOWER(email)) VIRTUAL;

-- STORED: 디스크에 실제로 저장
-- - 조회 성능이 더 빠름
-- - 저장 공간 더 사용
ALTER TABLE users 
ADD COLUMN upper_name VARCHAR(50) AS (UPPER(first_name)) STORED;

 

 

2) 함수를 이용한 인덱스

MySQL 8.0.13 이상부터는 가상 컬럼 없이 직접 함수 표현식에 인덱스를 생성할 수 있다.

-- 함수 표현식에 직접 인덱스 생성
CREATE INDEX idx_lower_email ON users((LOWER(email)));
CREATE INDEX idx_year_created ON users((YEAR(created_at)));
CREATE INDEX idx_json_field ON users((JSON_EXTRACT(profile, '$.age')));

-- 복합 인덱스도 가능
CREATE INDEX idx_name_year ON users(last_name, (YEAR(created_at)));

 

 

자주 사용되는 계산이라면 가상 컬럼 방식을 사용하고, 간단하고 일회성 계산이라면 함수를 이용한 인덱스를 사용하는 것이 권장된다.

 

멀티 밸류 인덱스

MySQL 8.0.17부터 지원되는 기능으로 하나의 레코드에 여러 값을 가지는 배열이나 JSON 필드에 대해 인덱스를 생성할 수 있다.

일반적으로 인덱스는 컬럼당 하나의 값만 인덱싱 가능하다.

다만 JSON 처럼 하나의 컬럼에 여러 값이 들어있다면 각각의 값에 대해 효율적인 검색이 필요하고, 이때 멀티 밸류 인덱스가 필요하다.

 

-- 아래와 같이 데이터가 있다고 가정
{
  "id": 1,
  "name": "MacBook Pro",
  "tags": ["laptop", "apple", "computer", "m3"]
}



-- 1. 특정 태그를 가진 상품 검색 (인덱스 활용됨)
SELECT * FROM products
WHERE JSON_CONTAINS(tags, '"laptop"');

SELECT * FROM products
WHERE JSON_OVERLAPS(tags, '["apple", "computer"]');

-- 2. 특정 카테고리 ID를 가진 상품 검색
SELECT * FROM products
WHERE JSON_CONTAINS(
    specifications->'$.category_ids', 
    '5'
);

-- 3. MEMBER OF 연산자 사용 (MySQL 8.0.17+)
SELECT * FROM products
WHERE 'laptop' MEMBER OF(tags->'$[*]');

SELECT * FROM products
WHERE 5 MEMBER OF(specifications->'$.category_ids[*]');

 

클러스터링 인덱스

테이블의 데이터 자체가 인덱스 키 순서대로 물리적으로 정렬되어 저장되는 인덱스이다.

InnoDB 스토리지 엔진은 항상 클러스터링 인덱스를 가지며, MySQL에서 테이블을 생성하면 자동으로 설정된다.

 

클러스터링 인덱싱을 위한 우선순위는 PK, UNIQUE NOT NULL, 내부적으로 자동 생성된 숨겨진 6바이트 정수 순으로 정해진다.

-- 1순위: PRIMARY KEY
CREATE TABLE users (
    id INT PRIMARY KEY,  -- 클러스터링 인덱스
    name VARCHAR(100),
    email VARCHAR(100)
);

-- 2순위: UNIQUE NOT NULL 인덱스 (PK가 없을 때)
CREATE TABLE sessions (
    session_id VARCHAR(100) UNIQUE NOT NULL,  -- 클러스터링 인덱스
    user_id INT,
    created_at DATETIME
);

-- 3순위: 내부적으로 자동 생성된 숨겨진 6바이트 정수 (DB_ROW_ID)
-- PK도 UNIQUE NOT NULL도 없을 때 InnoDB가 자동 생성
CREATE TABLE logs (
    message TEXT,
    created_at DATETIME
);
-- InnoDB가 내부적으로 DB_ROW_ID를 클러스터링 인덱스로 사용

 

만약 데이터가 아래와 같이 저장된다면,

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10, 2),
    category_id INT,
    INDEX idx_category (category_id)
);

INSERT INTO products VALUES
(5, 'Product E', 5000, 2),
(1, 'Product A', 1000, 1),
(3, 'Product C', 3000, 1),
(2, 'Product B', 2000, 2),
(4, 'Product D', 4000, 3);
```

 

클러스터링 인덱스에 의해 아래와 같이 저장되게 된다.

-- 실제 디스크 저장 순서 (PK 기준 정렬)
Page 1: [id=1, name='Product A', ...]
Page 2: [id=2, name='Product B', ...]
Page 3: [id=3, name='Product C', ...]
Page 4: [id=4, name='Product D', ...]
Page 5: [id=5, name='Product E', ...]


--세컨더리 인덱스 (idx_category) 구조
category_id=1 → PK=1, PK=3  (실제 데이터 위치를 PK로 참조)
category_id=2 → PK=2, PK=5
category_id=3 → PK=4

 

조회 성능을 세가지 경우로 두고 비교해 보면,

-- 1. 클러스터링 인덱스 사용 (PK 조회) - 가장 빠름
SELECT * FROM products WHERE id = 3;
-- 단계: PK로 바로 데이터 페이지 접근 → 1번의 인덱스 탐색

-- 2. 세컨더리 인덱스 사용
SELECT * FROM products WHERE category_id = 1;
-- 단계: 
--   1) idx_category 인덱스 탐색 → PK 값(1, 3) 획득
--   2) PK로 클러스터링 인덱스 탐색 → 실제 데이터 읽기
--   총 2번의 인덱스 탐색 필요

-- 3. 인덱스 없는 컬럼 조회 (풀 테이블 스캔)
SELECT * FROM products WHERE name = 'Product C';
-- 모든 데이터 페이지를 순차적으로 읽음

 

조회 시 이점이 많지만 단점의 경우도 명확한데, INSERT 쿼리 시 성능이 저하될 수 있다.

UUID가 PK로 사용된다면, 랜덤한 순서로 삽입되어 페이지 분할이 빈번해지고 데이터를 중간에 끼워넣어야해서 단편화가 발생하게 된다.

이로 인해 I/O가 증가하고, 성능이 저하된다.

 

그 외에 세컨더리 인덱스 크기가 증가하게 되는데, PK가 클수록 모든 세컨더리 인덱스 크기가 증가한다. 그 이유는 각 세컨더리 인덱스가 PK 값을 포함하기 때문이다.

 

 

클러스터링 인덱스 설계의 Best Practices

1) AUTO_INCREMENT 사용
   클러스터링 인덱스 설계의 Best Practices는 AUTO_INCREMENT를 사용하는 것인데, 그 이유는 순차적으로 증가하게 되기 때문이다.

   INSERT 쿼리 시 항상 마지막에 추가되며, 이로 인해 페이지 분할을 최소화하고 단편화가 적다.

 

2) 복합 PK 사용 시 순서 고려

3) UUID 사용 시 순차적 UUID 고려

    랜덤 UUID를 사용하는 것 보다 순차적 UUID를 사용하는게 좋다.

    나의 경우 UUID() 와 같은 식으로 사용했는데, UUID_TO_BIN(UUID(), 1) 이렇게 사용하면 순차적 UUID 적용이 가능하다.

 

4)PK 작게 유지
   예를 들어 email을 사용하게 되는 경우 타입이 VARCHAR(255)라면, 모든 세컨더리 인덱스가 최소 255 bytes씩 포함하게 된다.
   따라서 id INT AUTO_INCREMENT PRIMARY KEY 와 같은 식으로 사용하는 것이 권장된다.

 

 

유니크 인덱스

유니크는 인덱스라기 보다 제약 조건에 가깝다. 테이블이나 인덱스에 같은 값이 2개 이상 저장될 수 없음을 의미하며, MySQL에서는 인덱스 없이 유니크 제약만 설정할 방법이 없다.

또한 유니크 인덱스에 NULL 도 저장 가능하며, NULL의 경우 특정 값이 아니기 때문에 중복 저장이 가능하다.

 

유니크 인덱스 사용 시 주의사항은 '더 좋은 성능'을 위해 불필요하게 설정하지 않는 것이 좋다.

 

외래키

MySQL에서 외래키는 InnoDB 스토리지 엔진에서만 생성 가능하며, 외래키 제약이 설정되면 자동으로 연관되는 테이블의 컬럼에 인덱스도 생성된다.

 

• 테이블의 변경이 발생하는 경우에만 잠금 대기가 발생한다.
• 외래키와 연관되지 않은 컬럼의 변경은 최대한 잠금 대기를 발생시키지 않는다.