MySQL은 대규모 데이터베이스 시스템에서 매우 인기 있는 관계형 데이터베이스 관리 시스템(RDBMS)입니다. MySQL을 사용할 때 인덱스는 데이터베이스 성능과 쿼리 처리 속도에 큰 영향을 미치는 중요한 요소입니다. 이 글에서는 MySQL 인덱스의 작동 방식과 최적화를 위한 방법, 그리고 인덱스를 활용한 효율적인 쿼리 실행에 대해 알아보겠습니다.
MySQL 인덱스 개요
1. 인덱스의 개념과 역할
인덱스는 데이터베이스에서 데이터를 빠르게 검색하고 조회하기 위한 자료 구조입니다. 인덱스는 특정 칼럼 또는 칼럼의 조합에 대한 값과 해당 값이 존재하는 테이블 내의 물리적인 위치를 매핑합니다. 이를 통해 데이터베이스는 인덱스를 사용하여 효율적으로 데이터를 검색하고 필요한 정보를 빠르게 가져올 수 있습니다.
인덱스는 데이터베이스의 성능을 향상시키는 데 중요한 역할을 합니다. 인덱스를 사용하면 데이터베이스는 전체 테이블을 스캔하지 않고도 원하는 데이터를 찾을 수 있으므로, 쿼리의 실행 속도를 향상시킬 수 있습니다. 인덱스는 주로 SELECT 문에서 WHERE 절에 사용되는 칼럼이나 JOIN 연산에 사용되는 칼럼에 생성하는 것이 일반적입니다.
2. MySQL에서 지원하는 다양한 인덱스 유형 소개
MySQL은 다양한 유형의 인덱스를 지원하여 다양한 상황과 요구에 맞게 인덱스를 활용할 수 있습니다. 가장 일반적인 인덱스 유형은 다음과 같습니다:
B-Tree 인덱스: 가장 일반적으로 사용되는 인덱스 유형으로, 검색 및 정렬 작업에 효과적입니다. B-Tree 인덱스는 데이터를 정렬된 트리 구조로 저장하여 빠른 검색을 가능하게 합니다.
해시 인덱스: 해시 함수를 사용하여 데이터를 해시 테이블에 저장하는 인덱스입니다. 해시 인덱스는 정확한 일치 검색에 최적화되어 있으며, 주로 등호 연산(=)에 사용됩니다. 그러나 범위 검색이나 정렬에는 적합하지 않을 수 있습니다.
전문 텍스트 인덱스: 텍스트 기반의 데이터를 검색하는 데 특화된 인덱스입니다. 전문 텍스트 인덱스는 텍스트의 단어나 구를 색인화하여 효율적인 텍스트 검색을 제공합니다. 전문 텍스트 인덱스는 MATCH AGAINST 문을 사용하여 텍스트 검색에 사용됩니다.
공간 인덱스: 공간 데이터를 처리하는 데 사용되는 인덱스입니다. 공간 인덱스는 좌표 값에 대한 효율적인 검색을 가능하게 합니다. 공간 인덱스는 GIS(Geographic Information System) 데이터베이스에서 사용될 수 있으며, 공간 데이터 타입(Geometry, Point, Polygon 등)을 지원하는 컬럼에 대해 공간 인덱스를 생성할 수 있습니다. 이를 통해 공간 데이터의 거리 계산이나 영역 검색과 같은 공간 연산을 효율적으로 수행할 수 있습니다.
전체 텍스트 인덱스: 전체 텍스트 데이터에서 단어 기반의 검색을 지원하는 인덱스입니다. 전체 텍스트 인덱스는 일반 텍스트 데이터에서 단어를 추출하여 인덱싱하고, 단어 기반의 검색 쿼리를 수행할 때 사용됩니다. 전체 텍스트 인덱스는 MATCH 문이나 CONTAINS 문을 사용하여 검색할 수 있습니다.
MySQL은 이외에도 몇 가지 다른 유형의 인덱스를 지원합니다. 예를 들어, 열 기반 인덱스(Columnstore Index)는 대량의 데이터를 효율적으로 압축하여 분석 쿼리를 최적화하는 데 사용됩니다. 또한, 칼럼 크기가 큰 칼럼에 대해 인덱스를 일부만 저장하는 기술인 인덱스 접두사 압축(Index Prefix Compression)도 제공됩니다.
인덱스의 선택은 데이터베이스의 특성과 쿼리 패턴에 따라 달라질 수 있으며, 적절한 인덱스 유형을 선택하여 데이터베이스의 성능을 최적화할 수 있습니다.
인덱스 설계 원칙
1. 적절한 칼럼 선택
- 인덱스를 생성할 필드 선택 기준
검색 빈도: 자주 검색되는 필드일수록 인덱스를 생성하는 것이 좋습니다.
카디널리티(Cardinality): 해당 필드의 고유한 값의 수가 많을수록 인덱스의 효과가 큽니다.
필터링과 정렬: WHERE 절에서 필터링이나 ORDER BY 절에서 정렬에 사용되는 필드는 인덱스를 생성하는 것이 유리합니다.
2. 중복 인덱스 피하기
- 중복 인덱스로 인한 오버헤드 방지 방법
중복 인덱스는 같은 필드나 필드의 조합에 대해 중복해서 생성되는 인덱스를 말합니다. 중복 인덱스는 저장 공간을 낭비하고 업데이트 성능을 저하시킬 수 있으므로 피해야 합니다.
중복 인덱스를 피하기 위해서는 중복되는 인덱스를 삭제하고, 필요한 경우에만 새로운 인덱스를 생성하여 오버헤드를 방지할 수 있습니다.
3. 인덱스 칼럼의 정렬 순서
B-Tree 인덱스의 정렬 방식 이해
mysql에서 사용하는 B-Tree 인덱스는 칼럼의 값을 변형하지 않고, 원래의 값을 이용해 인덱싱하는 알고리즘입니다.
정렬 순서가 일치하는 경우에만 인덱스를 사용하여 정렬이 이루어지므로, 쿼리에서 자주 사용되는 정렬 순서에 맞게 인덱스를 생성하는 것이 좋습니다.
예를 들어, last_name과 first_name 두 개의 칼럼을 가진 테이블이 있을 때, last_name을 오름차순으로 정렬하고 first_name을 내림차순으로 정렬하여 인덱스를 생성하려면 다음과 같이 작성할 수 있습니다:
CREATE INDEX idx_name ON table_name (last_name ASC, first_name DESC);위 예제에서는 table_name 테이블에서 last_name 칼럼을 오름차순으로, first_name 칼럼을 내림차순으로 정렬하는 복합 인덱스를 생성하는 것을 보여줍니다.
인덱스 칼럼의 정렬 순서를 적절하게 선택하면 쿼리의 성능을 향상시킬 수 있습니다. 쿼리에서 자주 사용되는 정렬 방식에 맞게 인덱스를 설계하면 데이터베이스의 응답 시간을 최적화할 수 있습니다.
B-Tree 는 최상위에 하나의 루트 노드가 존재하고 그 하위에 자식 노드가 붙어있는 형태입니다.
출처
트리 구조의 가장 하위에는 리프 노드라고 하고 트리구조에서 루트노드도 아니고 리프노드도 아닌 중간의 노드를 브랜치 노드라고 합니다.
이 인덱스의 최대 장점은 어떤 데이터를 조회하든지, 이에 사용하는 조회 과정의 길이 및 비용이 균등 합니다.
단, 어떤 데이터를 조회 하든지 Root 에서 부터 Leaf 페이지를 모두 거처야 하기 때문에 데이터가 적은 테이블등의 단순 조회로 데이터를 조회하는 과정이 대비 조회 속도가 느린 단점이 있습니다.
4. 금지해야할 인덱스 설계
- 대용량 데이터가 자주 입력되는 경우
클러스터형 인덱스의 경우 빈번한 페이징이 일어나기 때문에 부하가 생깁니다.
따라서 인덱스가 필요한 경우 primary(클러스터) 대신 unique만 설정하는 게 좋을 수 있습니다. - 데이터 중복도가 높은 열은 인덱스 효과가 없음.
예를 들어 성별 열에 M, F만 있다고 하면 인덱스를 안쓰는 게 낫습니다.
따라서 일반 보조 인덱스보다 unique 보조 인덱스가 빠른 이유가 이것입니다. - 자주 사용되지 않으면 성능 저하를 초래할 수 있음.
INSERT만 빈번한 시스템이라면, 사용해보지도 못하고 데이터 입력에 걸리는 작업량만 많아질 수 있습니다.
5. 인덱스를 사용할 때 주의할 점
- 데이터 변경(삽입, 수정, 삭제) 작업이 얼마나 자주 일어나는지 고려해야합니다.
단일 테이블에 인덱스가 많으면 속도가 느려질 수 있습니다. (테이블당 4~5개 권장) - 검색할 데이터가 전체 데이터의 20% 이상이라면, MySQL에서 인덱스를 사용하지 않습니다. (강제로 사용할 시 성능 저하를 초래할 수 있음)
전체 페이지의 대부분을 읽어야 하고, 인덱스 관련 페이지도 읽어야 해서 작업량이 크기 때문입니다. - 사용하지 않는 인덱스는 제거하는 것이 바람직함. (실무에서 사용하지 않는 보조 인덱스를 몇개 삭제했을 때 성능이 향상되는 경우도 많음)
- 클러스터형 인덱스는 테이블당 하나만 생성할 수 있습니다.
테이블에 클러스터형 인덱스가 아예 없는 것이 좋은 경우도 있음
6. Table에 있는 Index 확인 명령
SHOW INDEX
FROM 테이블이름6. Table에 있는 Index 크기 확인 명령
show table status like 테이블명7. Create Index
-- 따로 인덱스 생성
CREATE INDEX 인덱스명 ON 테이블명 (컬럼명); -- 보조 인덱스 생성 (중복 허용)
CREATE UNIQUE INDEX 인덱스명 ON 테이블명 (컬럼명); -- 보조 인덱스 생성 (중복 비허용)
CREATE FULLTEXT INDEX 인덱스명 ON 테이블명 (컬럼명); -- 클러스터 인덱스 생성
CREATE UNIQUE INDEX 인덱스명 ON 테이블명 (컬럼명1, 컬러명2); -- 다중 컬럼 인덱스 생성
ANALYZE TABLE 테이블명; -- !! 생성한 인덱스 적용 !!8. Delete Index
ALTER TABLE 테이블이름
DROP INDEX 인덱스이름기본 키 및 외래 키 인덱스
1. 기본 키(primary key)의 역할과 설정 방법
기본 키(primary key)는 테이블의 각 레코드를 고유하게 식별하는 역할을 합니다. 기본 키는 다음과 같은 역할과 특징을 가지고 있습니다:
- 유일성: 기본 키는 각 레코드를 고유하게 식별해야 하므로 중복된 값이 없어야 합니다.
- 무결성 유지: 기본 키를 사용하면 데이터의 무결성을 유지할 수 있습니다. 즉, 잘못된 데이터나 중복 데이터의 삽입을 방지할 수 있습니다.
- 검색 성능 개선: 기본 키는 인덱스로 구성되어 있으므로 효율적인 데이터 검색을 가능하게 합니다.
기본 키는 테이블 생성 시 또는 이미 생성된 테이블에 ALTER TABLE 문을 사용하여 설정할 수 있습니다. 일반적으로 기본 키는 AUTO_INCREMENT와 같은 자동 생성 값을 사용하여 설정합니다. 예를 들어, 다음과 같이 테이블 생성 시 기본 키를 설정할 수 있습니다:
CREATE TABLE my_table (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50)
);위 예제에서는 id 칼럼을 기본 키로 설정하고 있으며, AUTO_INCREMENT를 사용하여 값이 자동으로 생성되도록 설정하고 있습니다.
2. 외래 키(foreign key)와 인덱스의 관계
외래 키(foreign key)는 다른 테이블의 기본 키와 연결된 칼럼으로, 다른 테이블과의 관계를 표현하는 데 사용됩니다. 외래 키는 부모 테이블의 기본 키와 대응되는 값이어야 합니다.
외래 키를 사용하면 다음과 같은 장점을 얻을 수 있습니다:
- 데이터 무결성 유지: 외래 키를 사용하여 부모 테이블과의 관계를 정의하면, 부모 테이블에서 레코드를 삭제하거나 변경할 때 관련된 자식 테이블의 데이터 무결성을 유지할 수 있습니다.
- 데이터 일관성 유지: 외래 키를 사용하여 관계를 설정하면, 관련된 테이블 간에 데이터 일관성을 유지할 수 있습니다.
외래 키는 기본 키와 관련이 있으므로, 기본 키와 동일한 방식으로 인덱스를 생성합니다.
일반적으로 외래 키는 자동으로 인덱스가 생성되지만, 명시적으로 인덱스를 생성할 수도 있습니다.
외래 키를 가진 칼럼에 인덱스를 생성하면 외래 키 관련 작업의 성능을 향상시킬 수 있습니다.
인덱스를 생성하는 방법은 다음과 같습니다:
ALTER TABLE child_table
ADD INDEX fk_index_name (foreign_key_column);위 예제에서는 child_table에서 foreign_key_column에 대한 인덱스를 생성하는 방법을 보여줍니다. 이렇게 하면 외래 키를 사용하는 쿼리의 실행 속도가 향상될 수 있습니다.
외래 키와 인덱스는 데이터베이스 관계를 유지하고 성능을 향상시키는 데 중요한 역할을 합니다. 기본 키와 외래 키를 올바르게 설정하고 인덱스를 적절하게 활용하여 데이터의 무결성을 유지하고 검색 성능을 개선할 수 있습니다.
특성(UNIQUE) 인덱스
1. UNIQUE 인덱스의 활용 방법과 장점
UNIQUE 인덱스는 특정 칼럼 또는 칼럼의 조합에 대해 고유한(unique) 값을 가지도록 하는 인덱스입니다. UNIQUE 인덱스를 활용하는 방법과 그 장점은 다음과 같습니다:
- 데이터 무결성 유지: UNIQUE 인덱스는 중복 데이터의 삽입을 방지하여 데이터의 무결성을 유지합니다. 특정 칼럼 또는 칼럼의 조합에 대해 고유한 값이 필요한 경우 UNIQUE 인덱스를 사용하여 중복 데이터를 방지할 수 있습니다.
- 검색 성능 개선: UNIQUE 인덱스를 사용하면 해당 칼럼 또는 칼럼 조합에 대한 검색 성능이 향상됩니다. 인덱스를 통해 중복을 허용하지 않는 값에 대한 빠른 검색이 가능하므로 쿼리의 실행 속도가 향상됩니다.
- 자동 생성: UNIQUE 인덱스는 칼럼에 UNIQUE 제약 조건을 추가함으로써 자동으로 생성할 수 있습니다. 이는 데이터베이스 시스템이 중복 데이터를 방지하는 데 도움을 줍니다.
2. 중복 데이터 방지를 위한 UNIQUE 인덱스 사용법
UNIQUE 인덱스를 사용하여 중복 데이터를 방지하려면 다음과 같은 방법을 따를 수 있습니다:
- CREATE TABLE 시 UNIQUE 제약 조건 추가: 테이블을 생성할 때 UNIQUE 제약 조건을 특정 칼럼 또는 칼럼 조합에 추가하여 UNIQUE 인덱스를 생성할 수 있습니다.
- 예를 들어, 다음과 같이 UNIQUE 제약 조건을 추가합니다:
CREATE TABLE my_table (
id INT,
name VARCHAR(50),
UNIQUE (id)
);- ALTER TABLE 시 UNIQUE 제약 조건 추가: 이미 생성된 테이블에 UNIQUE 인덱스를 추가하려면 ALTER TABLE 문을 사용하여 UNIQUE 제약 조건을 추가합니다.
- 예를 들어, 다음과 같이 ALTER TABLE 문을 사용합니다:
ALTER TABLE my_table ADD UNIQUE (id);UNIQUE 인덱스 생성: CREATE INDEX 문을 사용하여 UNIQUE 인덱스를 직접 생성할 수도 있습니다.
- 예를 들어, 다음과 같이 CREATE INDEX 문을 사용합니다:
CREATE UNIQUE INDEX idx_unique_id ON my_table (id);UNIQUE 인덱스를 사용하여 중복 데이터를 방지하려면 해당 칼럼 또는 칼럼 조합에 대해 고유한 값을 지정해야 합니다.
- 중복된 값을 삽입하려고 하면 UNIQUE 인덱스가 중복 데이터를 감지하여 삽입을 방지합니다.
- UNIQUE 인덱스를 사용하여 중복 데이터를 방지하면 데이터의 무결성을 유지하고 정확성을 보장할 수 있습니다.
UNIQUE 인덱스를 활용하여 중복 데이터를 방지하고 데이터의 무결성을 유지하는 것은 MySQL 데이터베이스에서 효과적인 데이터 관리의 한 부분입니다.
UNIQUE 인덱스를 적절히 활용하여 데이터의 정확성과 검색 성능을 동시에 향상시킬 수 있습니다.
복합 인덱스
1. 복합 인덱스의 개념과 사용 시 고려해야 할 사항
복합 인덱스는 두 개 이상의 칼럼을 조합하여 생성하는 인덱스입니다. 이를 통해 여러 칼럼에 대한 검색 조건을 동시에 처리할 수 있습니다. 복합 인덱스는 다음과 같은 장점을 가지고 있습니다:
- 여러 칼럼의 조합으로 인덱스를 구성하므로, 해당 칼럼들을 동시에 검색하는 쿼리에서 성능 향상을 기대할 수 있습니다.
- 복합 인덱스를 사용하면 인덱스의 크기를 줄일 수 있습니다. 단일 인덱스보다 적은 공간을 차지하므로, 디스크 I/O 및 메모리 사용량을 감소시킬 수 있습니다.
복합 인덱스를 사용할 때 고려해야 할 몇 가지 사항은 다음과 같습니다:
- 칼럼 순서: 인덱스에서 첫 번째 칼럼은 가장 중요한 순서로 고려되며, 두 번째, 세 번째 순서로 갈수록 중요도가 낮아집니다. 따라서 자주 사용되는 칼럼은 앞에 위치시키는 것이 성능 향상에 도움이 됩니다.
- 선택도: 복합 인덱스의 선택도는 각 칼럼의 고유값의 수와 관련이 있습니다. 선택도가 낮은 칼럼은 인덱스에 포함하는 것이 효과적이지 않을 수 있습니다. 따라서 칼럼 조합을 선택할 때 선택도를 고려해야 합니다.
- 쿼리 패턴: 복합 인덱스는 해당 칼럼들을 모두 활용하는 쿼리에 최적화되어 있습니다. 따라서 인덱스를 생성할 때 어떤 쿼리 패턴에 가장 적합한지 고려해야 합니다.
2. 인덱스의 칼럼 순서가 쿼리 성능에 미치는 영향
- 복합 인덱스에서 칼럼의 순서는 쿼리의 성능에 영향을 미칩니다. 일반적으로 복합 인덱스를 사용한 쿼리에서는 인덱스의 칼럼 순서와 일치하는 칼럼들을 검색 조건으로 사용하는 경우에 가장 효율적입니다. 따라서 다음 사항을 고려해야 합니다:
- 가장 선택도가 높은 칼럼을 첫 번째로 위치시키는 것이 좋습니다. 선택도가 높은 칼럼은 더 적은 레코드를 반환하므로 인덱스를 먼저 사용할 수 있습니다.
- 자주 사용되는 칼럼을 앞에 위치시키는 것이 좋습니다. 자주 사용되는 칼럼은 인덱스의 첫 번째 칼럼으로 두면, 해당 칼럼만으로도 검색 조건을 충족시킬 수 있어 성능이 향상됩니다.
- 범위 검색이 필요한 경우, 범위 검색에 가장 적합한 칼럼을 인덱스의 마지막에 위치시키는 것이 좋습니다. 범위 검색에는 뒤쪽 칼럼들이 사용되므로, 해당 칼럼들이 인덱스에 더 가까이 위치할수록 성능이 향상됩니다.
인덱스 성능 최적화
1. 쿼리 실행 계획 분석 및 최적화
쿼리 실행 계획은 데이터베이스가 쿼리를 어떻게 실행할지 결정하는 정보입니다.
이를 분석하고 최적화하여 쿼리의 실행 속도를 향상시킬 수 있습니다.
쿼리 실행 계획은 EXPLAIN 명령을 사용하여 확인할 수 있습니다. 실행 계획을 분석하여 인덱스가 적절히 활용되고 있는지, 테이블 스캔이 필요한 부분은 없는지 등을 확인해야 합니다.
필요한 경우 인덱스를 추가하거나 기존 인덱스를 수정하여 쿼리 실행 계획을 최적화할 수 있습니다.
2. 인덱스 통계 정보 활용 방법
인덱스 통계 정보는 데이터베이스가 인덱스를 선택하고 사용하는 데 도움을 줍니다. 이를 통해 쿼리 옵티마이저는 최적의 실행 계획을 수립할 수 있습니다.
MySQL에서는 ANALYZE TABLE 명령을 사용하여 테이블의 인덱스 통계 정보를 수집할 수 있습니다. 이를 통해 인덱스의 선택도(카디널리티)와 분포도를 확인할 수 있습니다.
인덱스 통계 정보를 정기적으로 업데이트하고 관리하여 최신 정보를 유지해야 합니다. 데이터의 분포가 변경되었을 때는 통계 정보를 업데이트해야 합니다.
인덱스의 제약 사항과 주의사항
1. 인덱스 생성 시 고려해야 할 사항
인덱스 생성은 쿼리의 읽기 성능을 향상시키지만 쓰기 성능에는 영향을 미칠 수 있습니다. 따라서 쓰기 작업이 많은 테이블에 인덱스를 생성할 때는 주의해야 합니다.
인덱스 크기는 디스크 공간을 차지하므로, 큰 테이블에 대해 인덱스를 지나치게 많이 생성하면 저장 공간 문제가 발생할 수 있습니다.
복합 인덱스를 사용할 경우 인덱스의 칼럼 순서를 고려해야 합니다. 쿼리에서 자주 사용되는 칼럼을 앞에 위치시키는 것이 성능에 도움이 됩니다.
2. 중복 인덱스 제거하기
중복 인덱스는 동일한 칼럼 또는 칼럼 조합에 대해 여러 개의 인덱스를 생성하는 것을 의미합니다. 중복 인덱스는 저장 공간을 낭비하고 업데이트 연산에 비효율적입니다. 따라서 중복 인덱스를 제거하는 것이 좋습니다.
3. 주의할 사항
인덱스는 쿼리 실행 계획에 영향을 주므로, 인덱스의 선택과 사용에 대한 테스트와 분석이 필요합니다.
인덱스는 데이터베이스의 통계 정보에 기반하여 작동하기 때문에, 통계 정보의 업데이트와 관리가 중요합니다.
데이터의 변경 작업(INSERT, UPDATE, DELETE)이 빈번하게 발생하는 테이블에서는 인덱스 유지 및 관리에 주의해야 합니다.
