Post

Real MySQL - 쿼리 성능 테스트와 확장 검색

Posted
Preview Image
By Hyeon Jae Kim
32 min read
Real MySQL - 쿼리 성능 테스트와 확장 검색

들어가며

MySQL에서 쿼리를 작성했다면, 그 성능을 정확하게 측정하는 것이 중요하다. 하지만 쿼리 성능 테스트는 생각보다 많은 변수가 존재한다. 운영체제 캐시, 버퍼 풀, 네트워크 등 다양한 요소가 쿼리 실행 시간에 영향을 미치기 때문이다. 이번 글에서는 쿼리 성능 테스트 시 고려해야 할 요소 들을 살펴보고, MySQL의 강력한 확장 검색 기능인 전문 검색(Full-Text Search) 과 공간 검색(Spatial Search) 에 대해 깊이 있게 다뤄본다.


쿼리 성능 테스트

쿼리의 성능에 영향을 미치는 요소

직접 작성한 쿼리의 성능을 테스트할 때, 가장 큰 변수는 MySQL 서버가 가지고 있는 여러 종류의 버퍼나 캐시 이다. 이런 요소들이 어떻게 영향을 미치는지 살펴보자.

운영체제의 캐시

MySQL 서버는 운영체제의 파일 시스템 관련 기능을 이용해 데이터 파일을 읽어온다. 대부분의 운영체제는 한 번 읽은 데이터를 별도의 캐시 영역에 보관해두고, 다시 해당 데이터가 요청되면 디스크를 읽지 않고 캐시의 내용을 바로 반환한다.

1
2
3
4
5
6
7
8
9
10
11
12
13

─────────────────────────────────────────────────────────────
                    데이터 읽기 흐름                          
─────────────────────────────────────────────────────────────
                                                             
   [MySQL 서버]                                              
        │                                                    
        ▼                                                    
   [운영체제 캐시] ◄─── 캐시 히트 시 디스크 I/O 없음          
        │                                                    
        ▼ (캐시 미스 시)                                     
   [디스크 (데이터 파일)]                                     
                                                            
─────────────────────────────────────────────────────────────

단, InnoDB 스토리지 엔진은 일반적으로 Direct I/O를 사용 하므로 파일 시스템의 캐시나 버퍼를 거치지 않는다. 따라서 InnoDB를 사용할 때는 운영체제 캐시가 그다지 큰 영향을 미치지 않는다.

운영체제 캐시를 초기화하려면 다음 명령을 사용한다.

1
2
3
4
5

# 캐시 내용을 디스크와 동기화
$ sync

# 운영체제 캐시 초기화
$ echo 3 > /proc/sys/vm/drop_caches

MySQL 서버의 버퍼 풀

운영체제와 마찬가지로 MySQL 서버에서도 데이터 파일의 내용을 페이지 단위로 캐시하는 기능을 제공한다.

스토리지 엔진 캐시 이름 캐시 대상
InnoDB 버퍼 풀 인덱스 + 데이터 페이지 + 쓰기 버퍼링
MyISAM 키 캐시 인덱스 데이터만

InnoDB의 버퍼 풀은 인덱스 페이지뿐만 아니라 데이터 페이지까지 캐시하며, 쓰기 작업을 위한 버퍼링까지 겸해서 처리한다. 반면 MyISAM의 키 캐시는 인덱스 데이터에 대해서만 캐시 기능을 제공한다.

콜드 상태 vs 워밍업 상태

쿼리 성능 테스트에서 중요한 개념이 콜드(Cold) 상태 와 워밍업(Warm-up) 상태 이다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

────────────────────────────────────────────────────────────────
                   서버 상태에 따른 성능 차이                    
────────────────────────────────────────────────────────────────
                                                                
  [콜드 상태 (Cold)]                                            
  ┌──────────────────┐                                          
  │ 버퍼 풀: 비어있음   → 모든 데이터를 디스크에서 읽음      
  │ 캐시: 초기화됨      → 쿼리 실행 시간 ↑↑↑                 
  └──────────────────┘                                          
                                                                
  [워밍업 상태 (Warm-up)]                                       
  ┌──────────────────────┐                                          
  │ 버퍼 풀: 데이터 로딩됨   → 대부분 메모리에서 읽음          
  │ 캐시: 준비됨            → 쿼리 실행 시간 ↓↓↓                
  └──────────────────────┘                                          
                                                                
────────────────────────────────────────────────────────────────

어느 정도 사용량이 있는 서비스라면 콜드 상태에서 워밍업 상태로 전환되는 데 그다지 많은 시간이 걸리지 않는다. 실제 서비스 환경의 쿼리는 대부분 워밍업된 상태에서 실행 된다고 볼 수 있다.

기타 영향 요소

버퍼나 캐시 외에도 다음 요소들이 쿼리 성능에 영향을 미친다.

  • 동시 실행 프로세스 : 웹 서버나 배치 프로그램이 동시에 실행되고 있다면 테스트 결과가 영향을 받는다.
  • 클라이언트 프로그램 : 테스트 쿼리를 실행하는 클라이언트 자체의 성능도 고려해야 한다.
  • 네트워크 : 원격에서 테스트할 경우 네트워크 지연 시간이 결과에 포함된다.

가장 정확한 테스트를 위해서는 MySQL 서버가 설치된 장비에 직접 로그인 해서 테스트하는 것이 좋다.

성능 테스트 시 주의사항

1
2
3
4

-- MySQL 8.0 이전: 쿼리 캐시 비활성화 필요
SET SESSION query_cache_type = OFF;

-- MySQL 8.0 이후: 쿼리 캐시가 제거되어 설정 불필요

MySQL 8.0부터는 쿼리 캐시가 완전히 제거되었다. 따라서 8.0 이상 버전에서는 쿼리 캐시 관련 설정을 신경 쓸 필요가 없다.

운영체제의 캐시나 MySQL의 버퍼 풀은 그 크기가 제한적이라서, 쿼리에서 필요로 하는 데이터나 인덱스 페이지보다 크기가 작으면 플러시와 캐시 작업이 반복 해서 발생한다. 따라서 쿼리를 1번 실행해서 나온 결과를 그대로 신뢰하기 어렵다.

권장 테스트 방법:

  1. 동일 쿼리를 여러 번 실행
  2. 첫 번째 실행 결과는 제외 (콜드 스타트 영향)
  3. 나머지 결과의 평균을 사용


확장 검색

MySQL은 일반적인 B-Tree 인덱스 기반 검색 외에도 전문 검색(Full-Text Search) 과 공간 검색(Spatial Search) 이라는 확장 검색 기능을 제공한다.


전문 검색 (Full-Text Search)

전문 검색은 게시물의 내용이나 제목 등 문장이나 문서의 내용에서 키워드를 검색 하는 기능이다. MySQL 서버는 예전부터 용량이 큰 문서를 단어 수준으로 잘게 쪼개어 문서 검색을 할 수 있도록 전문 검색 기능을 지원해왔다.

전문 검색 인덱스의 생성과 검색

전문 검색 인덱스 생성

전문 검색 인덱스는 FULLTEXT 키워드를 사용하여 생성한다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14

-- 테이블 생성 시 전문 검색 인덱스 포함
CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200),
    body TEXT,
    FULLTEXT INDEX ft_idx (title, body)
) ENGINE=InnoDB;

-- 기존 테이블에 전문 검색 인덱스 추가
ALTER TABLE articles 
ADD FULLTEXT INDEX ft_idx (title, body);

-- CREATE INDEX 구문 사용
CREATE FULLTEXT INDEX ft_idx ON articles (title, body);

인덱싱 방식: 구분자 방식 vs N-gram 방식

MySQL의 전문 검색 인덱스는 크게 두 가지 인덱싱 방식을 지원한다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

────────────────────────────────────────────────────────────────
                    전문 검색 인덱싱 방식                        
────────────────────────────────────────────────────────────────
                                                                
  [구분자(Stopword) 방식]                                       
 ┌──────────────────────────────────────────────────────────┐
    "Happy Birthday to You"                                 
           ↓ 공백으로 분리                                 
    "Happy" | "Birthday" | "to" | "You"              
                                                      
    장점: 영어 등 공백으로 단어가 분리되는 언어에 적합 
    단점: 한글/중국어/일본어 등 CJK 언어에 부적합        
 └──────────────────────────────────────────────────────────┘
                                                                
  [N-gram 방식]                                                 
 ┌──────────────────────────────────────────────────────────┐
    "슈퍼컴퓨터" (ngram_token_size=2)                   
           ↓ 2글자씩 분리                              
    "슈퍼" | "퍼컴" | "컴퓨" | "퓨터"                     
                                                        
    장점: CJK 언어 지원, 부분 문자열 검색 가능            
    단점: 인덱스 크기 증가, 의도치 않은 결과 포함 가능       
 └──────────────────────────────────────────────────────────┘ 
                                                                
────────────────────────────────────────────────────────────────

한글 검색을 위한 N-gram 파서 사용

1
2
3
4
5
6
7

-- N-gram 파서를 사용한 전문 검색 인덱스 생성
CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200),
    body TEXT,
    FULLTEXT INDEX ft_idx (title, body) WITH PARSER ngram
) ENGINE=InnoDB;

N-gram 토큰 사이즈 설정

N-gram의 토큰 사이즈는 ngram_token_size 시스템 변수로 설정한다. 기본값은 2(bigram)이며, 1~10까지 설정 가능하다.

1
2
3

# my.cnf
[mysqld]
ngram_token_size = 2

1
2
3
4
5
6
7

-- 현재 설정 확인
SHOW VARIABLES LIKE 'ngram_token_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| ngram_token_size | 2     |
+------------------+-------+

ngram_token_size는 서버 시작 시에만 설정할 수 있다. 런타임에 변경하려면 서버를 재시작해야 한다.

토큰 사이즈 선택

토큰 사이즈 특징 권장 상황
1 단일 문자 검색 가능, 인덱스 크기 최대 단일 글자 검색이 필요한 경우
2 (권장) CJK 언어에 범용적으로 적합 대부분의 한글 검색
3 이상 더 긴 토큰 검색, 인덱스 크기 감소 특정 용어 검색에 집중

ngram_token_size보다 짧은 검색어는 결과를 반환하지 않는다. 기본값 2에서는 1글자 검색어로 검색할 수 없다. 프론트엔드에서 최소 검색어 길이를 2자 이상으로 제한하는 것이 좋다.

불용어(Stopword) 처리

전문 검색에서는 의미 없는 단어(불용어)를 인덱스에서 제외할 수 있다.

1
2
3
4
5
6
7
8
9
10
11

-- 기본 불용어 목록 확인
SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD;
+-------+
| value |
+-------+
| a     |
| about |
| an    |
| are   |
| as    |
...

불용어 기능 비활성화

1
2
3

-- 세션/전역 레벨에서 불용어 비활성화
SET GLOBAL innodb_ft_enable_stopword = OFF;
SET SESSION innodb_ft_enable_stopword = OFF;

사용자 정의 불용어 테이블

1
2
3
4
5
6
7
8
9

-- 사용자 정의 불용어 테이블 생성
CREATE TABLE my_stopwords (
    value VARCHAR(30)
) ENGINE=InnoDB;

INSERT INTO my_stopwords VALUES ('은'), ('는'), ('이'), ('가');

-- 사용자 정의 불용어 테이블 적용
SET GLOBAL innodb_ft_server_stopword_table = 'mydb/my_stopwords';

전문 검색 쿼리 모드

전문 검색 쿼리는 MATCH() … AGAINST() 구문을 사용한다. MySQL은 세 가지 검색 모드를 지원한다.

자연어 검색 모드 (Natural Language Mode)

가장 기본적인 검색 모드로, 검색어에 포함된 단어들이 존재하는 레코드를 찾는다.

1
2
3
4
5
6
7

-- 자연어 모드 (기본값)
SELECT * FROM articles 
WHERE MATCH(title, body) AGAINST('데이터베이스');

-- 명시적으로 자연어 모드 지정
SELECT * FROM articles 
WHERE MATCH(title, body) AGAINST('데이터베이스' IN NATURAL LANGUAGE MODE);

자연어 모드의 특징

  • 검색어를 단어 단위로 분리하여 OR 조건 으로 검색
  • 매치율(relevance score)에 따라 결과 정렬
  • 전체 레코드의 50% 이상이 포함한 단어는 검색에서 제외 (MyISAM만 해당)
1
2
3
4
5
6

-- 매치율 확인
SELECT id, title, 
       MATCH(title, body) AGAINST('MySQL 데이터베이스') AS score
FROM articles 
WHERE MATCH(title, body) AGAINST('MySQL 데이터베이스')
ORDER BY score DESC;

불린 검색 모드 (Boolean Mode)

각 키워드의 포함/불포함을 논리 연산자로 조합하여 검색한다.

1
2
3

-- 불린 모드
SELECT * FROM articles 
WHERE MATCH(title, body) AGAINST('+MySQL -Oracle' IN BOOLEAN MODE);

불린 모드 연산자

연산자 의미 예시
+ 필수 포함 (AND) +MySQL +튜닝 : 둘 다 포함
- 제외 (NOT) +MySQL -Oracle : MySQL 포함, Oracle 제외
없음 선택적 포함 (OR) MySQL Oracle : 둘 중 하나
* 와일드카드 (접두사 검색) 데이터* : 데이터로 시작하는 단어
"..." 구문 검색 "MySQL 튜닝" : 정확한 구문
> < 관련성 증가/감소 +MySQL >튜닝 : 튜닝 포함 시 점수 증가
() 그룹화 +(MySQL Oracle) +튜닝 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

-- 'MySQL'과 '인덱스'를 모두 포함하는 문서
SELECT * FROM articles 
WHERE MATCH(title, body) AGAINST('+MySQL +인덱스' IN BOOLEAN MODE);

-- 'MySQL'은 포함하지만 'Oracle'은 제외
SELECT * FROM articles 
WHERE MATCH(title, body) AGAINST('+MySQL -Oracle' IN BOOLEAN MODE);

-- 정확한 구문 검색
SELECT * FROM articles 
WHERE MATCH(title, body) AGAINST('"쿼리 최적화"' IN BOOLEAN MODE);

-- 접두사 검색 (N-gram에서 유용)
SELECT * FROM articles 
WHERE MATCH(title, body) AGAINST('데이터*' IN BOOLEAN MODE);

쿼리 확장 검색 모드 (Query Expansion Mode)

2단계에 걸쳐 검색을 수행하는 고급 모드이다.

1
2

SELECT * FROM articles 
WHERE MATCH(title, body) AGAINST('database' WITH QUERY EXPANSION);

동작 방식:

  1. 1단계: 자연어 검색으로 관련 문서 검색
  2. 2단계: 1단계 결과에서 자주 등장하는 단어를 추출하여 검색어에 추가
  3. 확장된 검색어로 다시 검색

쿼리 확장 모드는 노이즈(관련 없는 결과)가 많이 포함될 수 있다. 검색 결과의 품질보다 재현율(recall)이 중요한 경우에 사용한다.

N-gram에서의 검색 모드 차이

N-gram 파서를 사용할 때 자연어 모드와 불린 모드의 동작 차이를 이해해야 한다.

1
2
3
4
5
6
7
8
9
10
11
12

-- '군필남자' 검색 시 (ngram_token_size=2)
-- 토큰화: "군필" | "필남" | "남자"

-- 자연어 모드: 토큰의 합집합 (OR)
-- → '군필여자', '미필남자'도 검색될 수 있음
SELECT * FROM articles 
WHERE MATCH(content) AGAINST('군필남자' IN NATURAL LANGUAGE MODE);

-- 불린 모드: 토큰의 순서까지 고려 (구문 검색처럼 동작)
-- → '군필남자'만 정확히 검색
SELECT * FROM articles 
WHERE MATCH(content) AGAINST('군필남자' IN BOOLEAN MODE);

전문 검색 인덱스 디버깅

전문 검색이 예상대로 동작하지 않을 때, MySQL은 디버깅을 위한 시스템 테이블을 제공한다.

innodb_ft_aux_table 설정

디버깅할 테이블을 지정한다.

1
2

-- 디버깅 대상 테이블 설정
SET GLOBAL innodb_ft_aux_table = 'mydb/articles';

인덱싱된 토큰 확인

1
2
3
4
5
6
7
8
9
10
11

-- 전문 검색 인덱스에 저장된 토큰 확인
SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;
+------+--------------+-------------+-----------+--------+----------+
| WORD | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION |
+------+--------------+-------------+-----------+--------+----------+
| 데이 |            2 |           5 |         3 |      2 |       10 |
| 이터 |            2 |           5 |         3 |      2 |       13 |
| 터베 |            2 |           5 |         3 |      2 |       16 |
| 베이 |            2 |           5 |         3 |      2 |       19 |
| 이스 |            2 |           5 |         3 |      2 |       22 |
...

캐시된 토큰 확인

새로 삽입된 데이터의 토큰은 캐시에 먼저 저장된다.

1
2

-- 캐시에 있는 토큰 확인
SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE;

전문 검색 설정 확인

1
2
3
4
5
6
7
8
9
10

-- 전문 검색 관련 설정 확인
SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_CONFIG;
+---------------------------+-------+
| KEY                       | VALUE |
+---------------------------+-------+
| optimize_checkpoint_limit | 180   |
| synced_doc_id             | 6     |
| stopword_table_name       |       |
| use_stopword              | 1     |
+---------------------------+-------+

디버깅 체크리스트

전문 검색이 동작하지 않을 때 확인해야 할 사항:

  1. 인덱스 타입 확인 : WITH PARSER ngram 옵션이 필요한지
  2. 토큰 사이즈 확인 : 검색어 길이가 ngram_token_size 이상인지
  3. 불용어 확인 : 검색어가 불용어 목록에 포함되어 있지 않은지
  4. MATCH 컬럼 확인 : FULLTEXT 인덱스에 포함된 컬럼과 일치하는지
1
2

-- 인덱스 정보 확인
SHOW INDEX FROM articles WHERE Index_type = 'FULLTEXT';


공간 검색은 위치 기반 데이터 를 저장하고 검색하는 기능이다. 근처 맛집 찾기, 배달 가능 지역 확인 등 위치 기반 서비스에 필수적인 기능이다.

용어 설명

공간 데이터를 다루기 전에 알아야 할 주요 용어들을 정리한다.

OGC (Open Geospatial Consortium)

개방형 공간 정보 컨소시엄 으로, 위치 기반 데이터에 대한 표준을 수립하는 단체이다. 전 세계 500개 이상의 기업, 정부, 학교가 참여하고 있다.

OpenGIS

OGC에서 제정한 지리 정보 시스템(GIS) 표준 이다. WKT, WKB 같은 지리 정보 데이터 표기 방법과 SRID 등의 표준을 포함한다. OpenGIS 표준을 준수하면 서로 다른 시스템 간에 위치 데이터를 변환 없이 교환할 수 있다.

WKT와 WKB

위치 좌표의 표현 방법이다.

형식 설명 예시
WKT (Well-Known Text) 사람이 읽기 쉬운 텍스트 형식 POINT(127.0 37.5)
WKB (Well-Known Binary) 컴퓨터 저장용 이진 형식 바이너리 데이터 
1
2
3
4
5

-- WKT 형식으로 Point 생성
SELECT ST_GeomFromText('POINT(127.0 37.5)');

-- WKT 형식으로 Polygon 생성
SELECT ST_GeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))');

SRS (Spatial Reference System)

SRS 는 공간 참조 시스템, 즉 좌표계 를 의미한다. 같은 위치라도 어떤 좌표계를 사용하느냐에 따라 표현 방법이 달라진다.

좌표계의 종류

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

────────────────────────────────────────────────────────────────
                       좌표계 분류                              
────────────────────────────────────────────────────────────────
                                                         
  ┌──────────────────────────────────────────────────────────┐ 
     GCS (Geographic Coordinate System) - 지리 좌표계    
     ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 
     • 지구 구체 표면의 위치를 표현                        
     • 위도(Latitude), 경도(Longitude) 사용              
     • 각도(degree) 단위                                
     • 예: WGS84 (SRID 4326) - GPS 표준                
  └──────────────────────────────────────────────────────────┘ 
                         ↓ 투영                                
  ┌──────────────────────────────────────────────────────────┐ 
     PCS (Projected Coordinate System) - 투영 좌표계      
     ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 
     • 구체를 평면에 투영한 좌표계                         
     • 미터(meter) 등 선형 단위                           
     • 지도 제작에 사용                                   
     • 예: Web Mercator (SRID 3857)                     
  └──────────────────────────────────────────────────────────┘ 
                                                               
  ┌──────────────────────────────────────────────────────────┐ 
     평면 좌표계 (SRID 0)                                    
     ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 
     • 단순 2D 평면 좌표                                     
     • 단위 없음, X/Y 축 제한 없음                           
     • 지구 곡률 미반영                                      
  └──────────────────────────────────────────────────────────┘ 
                                                                
────────────────────────────────────────────────────────────────

SRID (Spatial Reference ID)

SRID 는 특정 SRS를 지칭하는 고유 번호이다. MySQL 8.0은 5,000개 이상의 SRS를 지원한다.

SRID 좌표계 설명
0 평면 좌표계 단순 2D 평면, 단위 없음
4326 WGS84 GPS 표준, 위/경도(degree)
3857 Web Mercator 웹 지도 표준, 미터 단위 
1
2
3
4

-- MySQL이 지원하는 SRS 목록 확인
SELECT SRS_ID, SRS_NAME, ORGANIZATION, DEFINITION
FROM INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS
WHERE SRS_ID IN (0, 4326, 3857);

투영 좌표계와 평면 좌표계

평면 좌표계 (SRID 0)

SRID를 지정하지 않으면 기본값으로 SRID 0 (평면 좌표계) 이 사용된다. 이는 지구의 곡률을 고려하지 않는 단순한 2D 평면 좌표계이다.

1
2
3
4
5
6

-- SRID 0 (평면 좌표계)
SELECT ST_Distance(
    ST_GeomFromText('POINT(0 0)'),
    ST_GeomFromText('POINT(3 4)')
) AS distance;
-- 결과: 5 (피타고라스 정리: √(3² + 4²) = 5)

주의 : 평면 좌표계에서 계산된 거리는 실제 지구상의 거리가 아니다. 위/경도 좌표를 평면 좌표계로 계산하면 완전히 잘못된 결과가 나온다.

투영 좌표계

투영 좌표계 는 지구 구체를 평면에 투영한 좌표계이다. 대표적으로 SRID 3857 (Web Mercator) 가 있으며, 구글 지도, 네이버 지도 등 대부분의 웹 지도 서비스에서 사용된다.

지리 좌표계

지리 좌표계(GCS) 는 지구 구체 표면의 위치를 위도와 경도로 표현한다. GPS 좌표가 대표적인 예이다.

WGS84 좌표계 (SRID 4326)

SRID 4326 은 GPS의 기준이 되는 WGS84 좌표계이다. 대부분의 위치 기반 서비스에서 이 좌표계를 사용한다.

1
2
3
4
5
6
7
8
9
10
11
12

-- SRID 4326으로 Point 생성
-- 서울시청 좌표: 위도 37.5666, 경도 126.9784
CREATE TABLE locations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    location POINT NOT NULL SRID 4326,
    SPATIAL INDEX (location)
);

-- 데이터 삽입 (경도, 위도 순서 주의!)
INSERT INTO locations (name, location) 
VALUES ('서울시청', ST_GeomFromText('POINT(126.9784 37.5666)', 4326));

중요 : WKT 형식에서 Point는 POINT(경도 위도) 순서이다. 일반적으로 사용하는 (위도, 경도) 순서와 반대이므로 주의해야 한다.

공간 인덱스 사용

공간 인덱스는 R-Tree 자료구조를 사용한다.

1
2
3
4
5
6
7
8
9

-- 공간 인덱스 생성
ALTER TABLE locations ADD SPATIAL INDEX idx_location (location);

-- EXPLAIN으로 인덱스 사용 확인
EXPLAIN SELECT * FROM locations 
WHERE ST_Contains(
    ST_Buffer(ST_GeomFromText('POINT(126.9784 37.5666)', 4326), 0.01),
    location
);

거리 계산

두 지점 사이의 거리를 계산하는 함수들이 있다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

-- ST_Distance: SRID에 따라 다르게 동작
-- SRID 0: 평면 거리 (유클리드 거리)
-- SRID 4326: 지구 타원체 기준 거리 (MySQL 8.0+)
SELECT ST_Distance(
    ST_GeomFromText('POINT(126.9784 37.5666)', 4326),
    ST_GeomFromText('POINT(127.0276 37.4979)', 4326)
) AS distance;

-- ST_Distance_Sphere: 항상 구체 표면 거리 (미터)
-- 지구 반지름을 사용하여 계산
SELECT ST_Distance_Sphere(
    ST_GeomFromText('POINT(126.9784 37.5666)', 4326),
    ST_GeomFromText('POINT(127.0276 37.4979)', 4326)
) AS distance_meters;
-- 결과: 약 8,900 미터 (서울시청 ↔ 강남역)

반경 검색

특정 위치에서 반경 N km 이내의 데이터를 검색하는 예제:

1
2
3
4
5
6
7
8
9
10
11
12

-- 서울시청에서 5km 이내의 장소 검색
SELECT id, name,
       ST_Distance_Sphere(
           location, 
           ST_GeomFromText('POINT(126.9784 37.5666)', 4326)
       ) AS distance_meters
FROM locations
WHERE ST_Distance_Sphere(
    location, 
    ST_GeomFromText('POINT(126.9784 37.5666)', 4326)
) <= 5000
ORDER BY distance_meters;

성능 주의 : ST_Distance_Sphere를 WHERE 절에서 직접 사용하면 공간 인덱스를 활용할 수 없다. 대량 데이터에서는 ST_Contains나 ST_Within과 함께 바운딩 박스를 사용하는 것이 좋다.

공간 인덱스를 활용한 최적화

1
2
3
4
5
6
7
8
9
10
11
12

-- 공간 인덱스를 활용한 반경 검색 (최적화)
SELECT id, name,
       ST_Distance_Sphere(
           location, 
           ST_GeomFromText('POINT(126.9784 37.5666)', 4326)
       ) AS distance_meters
FROM locations
WHERE ST_Contains(
    ST_Buffer(ST_GeomFromText('POINT(126.9784 37.5666)', 4326), 0.045),
    location
)
ORDER BY distance_meters;

ST_Buffer로 원형 영역을 만들고 ST_Contains로 포함 여부를 확인하면 공간 인덱스를 활용할 수 있다.

위도/경도 좌표에서 0.045도는 약 5km에 해당한다 (위도에 따라 달라짐).

SRID 불일치 문제

공간 인덱스가 동작하지 않는 가장 흔한 원인은 SRID 불일치 이다.

1
2
3
4
5
6
7
8
9
10
11
12
13

-- 컬럼에 SRID가 지정되어 있는 경우
CREATE TABLE places (
    id INT PRIMARY KEY,
    point POINT NOT NULL SRID 4326,
    SPATIAL INDEX (point)
);

-- 잘못된 예: SRID 없이 삽입 시도 → 에러 발생
INSERT INTO places VALUES (1, ST_GeomFromText('POINT(127 37)'));
-- ERROR: The SRID of the geometry does not match the SRID of the column

-- 올바른 예: SRID 명시
INSERT INTO places VALUES (1, ST_GeomFromText('POINT(127 37)', 4326));

공간 인덱스 활용 체크리스트:

  1. 컬럼에 SRID가 지정되어 있는지 확인
  2. 삽입/검색 시 동일한 SRID를 사용하는지 확인
  3. ST_Contains, ST_Within 등 포함 관계 함수 사용
  4. EXPLAIN으로 인덱스 사용 여부 확인


마치며

이번 글에서는 MySQL의 쿼리 성능 테스트 방법과 확장 검색 기능에 대해 살펴보았다.

확장 검색 기능을 잘 활용하면 Elasticsearch 같은 별도의 검색 엔진 없이도 MySQL만으로 강력한 검색 기능을 구현할 수 있다.

하지만 공간검색이나 지리 데이터 부문에서는 MySQL이 이것이 메인인 RDBMS가 아니기에 PostgreSQL나 Redis 등 잘 구현되어있는 것을 사용하는것도 더 좋을 수 있다.

References

데이터베이스
Database mysql
This post is licensed under CC BY 4.0 by the author.