[MySQL] index 생성 시 고려할 점

MySQL 사용 시 고려할 점과 튜닝 방법(241027 최신 업데이트)

EXPLAN을 이용하여 조회 시 사용된 INDEX 확인

 EXPLAN을 사용하여 데이터를 읽을 때 어떻게 읽는지 type을 통해 데이터를 읽는 방법(접근 방식)을 보고, 쿼리 성능을 분석 및 최적화하는 것이 좋다.

# 예시
explain
select * from test1 where id = 1;

+--+-----------+-----+----------+-----+-------------+-------+-------+-----+----+--------+-----+
|id|select_type|table|partitions|type |possible_keys|key    |key_len|ref  |rows|filtered|Extra|
+--+-----------+-----+----------+-----+-------------+-------+-------+-----+----+--------+-----+
|1 |SIMPLE     |test1|null      |const|PRIMARY      |PRIMARY|4      |const|1   |100     |null |
+--+-----------+-----+----------+-----+-------------+-------+-------+-----+----+--------+-----+

 

검색 성능이 좋은 순으로 정리하면 아래와 같다.

type	설명
system	하나의 행만 있는 테이블
const	PK나 UK 인덱스를 통한 단일 행 조회
eq_ref	PK나 UK 인덱스를 통한 조인하여 단일 행 조회
ref	일반 인데스를 통한 다중 행 조회
fulltext	Full-Text 인덱스를 이용한 조회
ref_or_null	인덱스에서 null 값을 포함한 다중 행 조회
index_merge	두 개 이상의 인덱스를 병합하여 조회
range	인덱스 범위 검색
index	인덱스 풀 스캔
ALL	테이블 풀 스캔

 

 

 

단일 인덱스, 복합 인덱스 생성 시 고려할 점

1. 인덱스 선택

 인덱스의 카디널리티(Cardinality)가 가장 높은 컬럼을 선택해야 한다. 검색 시 인덱스를 통하여 최대한 많은 로우들을 걸러내야 하기 때문에 카디널리티가 높은 컬럼을 선택하여 효율을 높이는 것이 좋다.

카디널리티(Cardinallity)란?

 특정 데이터 집합의 유니크(Unique)한 값의 개수를 뜻한다. 즉, 중복(같은 값)이 낮으면 카디널리티가 높다고 표현하고, 증복(같은 값)이 많으면 카디널리티가 낮다고 표현한다.
카디널리티가 높다 = 중복 값이 많다
카디널리티가 낮다 = 중복 값이 별로 없다

 

 

2. 과도한 인덱스 생성을 막아라

 인덱스도 하나의 데이터로 저장이 된다. 따라서 특정 테이블의 조회 성능을 높이기 위해 인덱스를 과도하게 만들게 되면 데이터 변경(삽입, 업데이트, 삭제) 작업 시 성능이 저하가 된다. 따라서 조회 시 성능 개선에 꼭 필요하다고 생각되는 컬럼만 인덱스를 생성하거나 여러 개의 단일 인덱스 대신 다중 인덱스를 적절히 설계하는 것이 좋다.

 

 또한 조건절(WHERE, JOIN, ORDER BY, GROUP BY) 등 에서 자주 사용 되는 컬럼을 인덱스로 생성하는 것이 좋다.

 

 

3. 다중 인덱스 생성 시 순서에 유의하라

 다중 인덱스에서는 컬럼 순서가 매우 종요하다. 보통 WHERE 절에 자주 등장하는 컬럼을 인덱스의 첫 번째로 두는 것이 좋다. 순서에 따라 인덱스를 사용이 안 될 수도 있기 때문에 해당 테이블을 조회할 때 어떤 조건들이 사용되는지 분석하는 것이 좋다.

 

# one_field, two_field 순서로 다중 인덱스를 만든 후 조회 결과
# index idx_col1_col2 (one_field, two_field)
explain
select * from test1 where one_field = 2 and two_field = 3;
+--+-----------+-----+----------+----+-------------+-------------+-------+-----------+----+--------+-----+
|id|select_type|table|partitions|type|possible_keys|key          |key_len|ref        |rows|filtered|Extra|
+--+-----------+-----+----------+----+-------------+-------------+-------+-----------+----+--------+-----+
|1 |SIMPLE     |test1|null      |ref |idx_col1_col2|idx_col1_col2|18     |const,const|1   |100     |null |
+--+-----------+-----+----------+----+-------------+-------------+-------+-----------+----+--------+-----+


explain
select * from test1 where one_field = 2;
+--+-----------+-----+----------+----+-------------+-------------+-------+-----+----+--------+-----+
|id|select_type|table|partitions|type|possible_keys|key          |key_len|ref  |rows|filtered|Extra|
+--+-----------+-----+----------+----+-------------+-------------+-------+-----+----+--------+-----+
|1 |SIMPLE     |test1|null      |ref |idx_col1_col2|idx_col1_col2|9      |const|1   |100     |null |
+--+-----------+-----+----------+----+-------------+-------------+-------+-----+----+--------+-----+


explain
select * from test1 where two_field = 3;
+--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+-----------+
|id|select_type|table|partitions|type|possible_keys|key |key_len|ref |rows|filtered|Extra      |
+--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+-----------+
|1 |SIMPLE     |test1|null      |ALL |null         |null|null   |null|9834|10      |Using where|
+--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+-----------+

 

위 결과를 보면 세 번째 쿼리가 테이블 풀 스캔으로 조회된 것을 볼 수 있다.

 

 추가로 위에서 설명한 카디널리티(Cardinallity)가 높은 순서로 다중 인덱스를 생성하는 것이 좋다. 예를 들어, (성별, 나이) 순으로 다중 인덱스를 생성하여도 성별이 "남", "여" 두 값만 존재한다면 인덱스를 사용하는 효과가 거의 안 나타나게 된다. 하지만 카디널리티가 더 높은 순인 (나이, 성별) 순으로 다중 인덱스를 생성하면 검색 성능이 향상된다.

 

 

Prefix Index

 Prefix Index는 문자열 데이터의 일부만 인덱싱하여 성능과 저장 공간을 최적화하는 기법이다. 특히, 긴 문자열 (VARCHAR, TEXT, BLOB 등)에 전체 길이로 인덱스를 생성하면 저장 공간이 많이 차지하고 성능이 떨어질 수 있는데, 이런 경우 문자열의 앞부부만을 인덱싱하여 효율성을 높일 수 있다.

-- 문자열의 처음 10자만 인덱싱
CREATE INDEX idx_name_prefix ON users (name(10));

 

장점

• 저장 공간 절약 - 메모리와 저장 공간을 줄일 수 있다.
• 인덱싱 속도 개선 - 인덱스 크기가 작아지기 때문에 검색, 삽인, 삭제 성능이 향상된다.
• 긴 문자열 인덱싱 기능 - TEXT나 BLOB와 같은 열은 인덱스 길이 제한이 있기 때문에 Prefix Index가 필수이다.  

단점

• 정확한 고유성 제한 - 일부만 인덱싱 하기 때문에 고유성을 보장하지 않는다.
• 전체 문자열 일치 검색에 효율적이지 않다.
• 데이터 타입별로 Prefix Idex 길이에 제한이 있기 때문에 적절히 길이를 조절해야 한다.

 

인덱스가 적용되지 않는 경우

1. LIKE 연산자

'%검색%'처럼 와일드카드가 앞에 있는 경우 인덱스를 사용할 수 없다. 반면, '검색%'처럼 와일드카드가 뒤에만 있을 경우에는 인덱스가 적용된다.

 

2. 함수 사용 시 인덱스가 사용되지 않을 수 있다.

 WHERE UPPER(name) = 'JOHN'처럼 함수가 적용된 컬럼은 인덱스를 타지 않는다. 이 경우 함수 기반 인덱스를 이용해야 한다.