인덱스(Index)는 데이터베이스 테이블의 일부 또는 전체 열의 값을 사용하여 생성된 키로, 데이터 검색, 조회 및 정렬에 활용되는 자료 구조이다. 인덱스를 사용하면 데이터베이스에서 데이터를 빠르게 검색할 수 있어, SQL 쿼리의 응답 시간을 단축시킬 수 있다. 이는 시스템 성능 향상에 매우 중요한 역할을 한다.
MySQL과 MariaDB와 같은 주요 데이터베이스 관리 시스템들은 다양한 유형의 인덱스를 지원하고 있다. 이러한 인덱스를 효과적으로 활용하면 데이터베이스 성능을 크게 개선할 수 있으며, 대규모 데이터를 다루는 DB에서는 특히 중요하다.
[목차]
3) FullText search index (전문 검색 인덱스)
1. index 생성, 삭제
2가지 인덱스 생성 방법
- CREATE INDEX 문 사용
CREATE INDEX 인덱스명 ON 테이블명(컬럼명);- ALTER TABLE 문을 사용하는 방법
ALTER TABLE 테이블명 ADD INDEX 인덱스명 (컬럼명);위와 같이 ALTER TABLE 문을 사용하여 index를 생성할 수 있습니다. ADD INDEX 절은 index를 추가하는 것을 의미하며, index명과 칼럼명은 CREATE INDEX 문과 동일하게 사용된다.
- index 삭제 방법
DROP INDEX 인덱스명 ON 테이블명;
2. index 종류
1) B-Tree index (B-트리 인덱스)
B-Tree index는 가장 일반적으로 사용되는 유형이다. 일반적으로 키 칼럼의 값과 해당 키 값을 가진 데이터의 위치를 저장한다. 이러한 index는 특정 키 값을 검색하는 데 효율적이며, ORDER BY 절과 같은 정렬 작업에서도 유용하다.
- 설정 방법
CREATE INDEX 인덱스명 ON 테이블명(컬럼명);- 쿼리 예시
SELECT * FROM 테이블명 WHERE 컬럼명 = '조건';2) Hash index (해시 인덱스)
Hash index는 빠른 검색이 필요한 대용량 데이터베이스에서 사용된다. 이 index는 해시 함수를 사용하여 데이터를 빠르게 검색하지만 Hash index는 B-Tree 인덱스보다 훨씬 적은 작업에서만 빠르며, 일부 제약 사항이 있다. 예를 들어 Hash index는 부분 일치 검색을 지원하지 않으며, 정렬 작업에서 사용할 수 없다.
- 설정 방법
CREATE INDEX 인덱스명 ON 테이블명(컬럼명) USING HASH;- 쿼리 예시
SELECT * FROM 테이블명 WHERE 컬럼명 = '조건';3) FullText search index (전문 검색 인덱스)
FullText search index는 텍스트 검색을 위해 사용된다. 이 인덱스는 전문 검색 엔진을 사용하여 텍스트를 색인화하고 검색한다.
- 설정 방법
CREATE FULLTEXT INDEX 인덱스명 ON 테이블명(컬럼명);- 쿼리 예시
SELECT * FROM 테이블명 WHERE MATCH(컬럼명) AGAINST('검색어');4) Spatial index (공간 인덱스)
Spatial index는 지리 정보를 저장하는 데 사용된다. 이 인덱스는 공간 데이터를 지리 정보에 맞게 색인화하고 검색한다.
- 설정 방법
CREATE SPATIAL INDEX 인덱스명 ON 테이블명(컬럼명);- 쿼리 예시
SELECT * FROM 테이블명 WHERE ST_CONTAINS(컬럼명, POINT(경도, 위도));
3. index 설정 확인 및 쿼리 실행 계획
1) 테이블 index 정보 확인 방법
SHOW INDEX FROM 테이블명;2) 쿼리 실행 계획
MySQL에서 EXPLAIN은 SELECT 문을 실행할 때 쿼리 실행 계획을 확인할 수 있는 명령어다.
EXPLAIN을 사용하면 쿼리가 실행되는 방식을 이해하고 쿼리의 성능을 향상하는 데 도움이 된다.
- 쿼리 예시
EXPLAIN SELECT *
FROM orders
WHERE order_date BETWEEN '2022-01-01' AND '2022-01-31'
AND status = 'completed';위의 쿼리에서, BETWEEN 연산자와 AND 연산자를 사용하여 order_date와 status 열을 조건으로 지정한다.
이 쿼리를 실행할 때 EXPLAIN을 사용하여 실행 계획을 확인할 수 있다.
- 실행 결과
-----------------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
-----------------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | orders | NULL | ref | status | status | 102 | const | 1000 | 10.00 | Using where |
------------------------------------------------------------------------------------------------------------------------------------ 각 컬럼의 의미
- id: SELECT 문에서 쿼리의 각 부분마다 할당된 번호
- select_type: 쿼리의 유형입니다. SIMPLE은 단순한 SELECT 문이며, 다른 값을 가질 수도 있다.
- table: 쿼리에서 사용된 테이블의 이름
- partitions: 사용되는 파티션의 목록. NULL 값이면 파티션을 사용하지 않음.
- type: 사용된 JOIN 유형입니다. 쿼리가 단일 테이블을 사용하면 index로 표시.
- possible_keys: 쿼리에 사용될 수 있는 인덱스의 목록.
- key: 쿼리에서 실제로 사용된 인덱스의 이름.
- key_len: 인덱스에서 사용된 바이트 수.
- ref: 인덱스에서 사용된 열의 목록.
- rows: 쿼리에 의해 반환된 행의 수.
- filtered: 필터링된 데이터의 비율.
- Extra: 추가 정보다. Using where은 WHERE 조건을 처리하기 위해 인덱스를 사용했다는 것을 의미한다.
실행 계획을 이해하면, 쿼리에서 발생하는 병목 현상이나 성능 저하 원인을 파악하고 최적화할 수 있다.