[DB] MySQL Index

인덱스란?

• 데이터베이스 테이블의 검색 속도를 높여주는 자료구조이다. 인덱스는 특정 컬럼에 대한 정렬된 데이터 세트를 제공하여 검색 및 정렬 작업을 빠르게 수행할 수 있도록 한다.

• 인덱스는 MYI(MySQL Index)파일에 저장되며, 인덱스가 설정되지 않았다면 Table Full Scan이 일어나 성능이 저하되거나 치명적인 장애가 발생한다.

• 인덱스는 주로 WHERE 절에 사용되는 조건 검색, ORDER BY절의 정렬, GROUP BY절의 그룹화 작업에 효과적이다. 하지만, 인덱스를 사용하면 읽기 성능은 향상되지만 쓰기 성능은 약간 저하될 수 있다(Table의 index 색인 정보를 갱신하는 추가적인 비용 소요). 따라서 적절한 인덱스를 선택하고 설계하는 것이 중요하다.

특징

• 인덱스는 하나 혹은 여러개의 컬럼에 대해 설정할 수 있다.
• WHERE절을 사용하지 않고 인덱스가 걸린 컬럼을 조회하는것은 성능에 아무런 영향이 없다.

단일 인덱스 (Single Column Index)

• 하나의 컬럼에 대해서만 인덱스를 생성하는 것을 의미한다.
• 단일 인덱스는 해당 열의 값들을 정렬하여 저장하며, 검색 시에는 해당 열에 대한 조건이 포함된 쿼리에서 효과적으로 사용된다.
• 주로 WHERE 절에서 사용되는 열에 대해 단일 인덱스를 생성하는 것이 일반적이다.

CREATE INDEX idx_single_column
ON your_table(column1);

다중 컬럼 인덱스 (Composite Index)

• 다중 컬럼 인덱스는 두개 이상의 컬럼을 조합해서 생성한 Index이다.(MySQL은 Index에 최대 15개 컬럼으로 구성 가능)
• 여러 컬럼의 값들을 복합적으로 정렬하여 저장하며, 해당 컬럼들을 모두 활용하는 쿼리에서 성능을 향상시킬수 있다.
• 단일 컬림 인덱스보다 더 비효율적으로 INSERT/UPDATE/DELETE를 수행하기 때문이 신중해야한다. 때문에 가급적 UPDATE가 안되는 값을 선정해야한다.
• 주로 여러 열에 대한 조건이 포함된 WHERE절이나 ORDER BY, GROUP BY에 활용된다.

CREATE INDEX idx_multi_column
ON your_table(column1, column2);

ORDER BY, GROUP BY에 대한 Index

인덱스는 ORDER BY와 GROUP BY에도 영향을 끼치는데 다음과 같은 경우에는 인덱스를 타지 않는다.

• ORDER BY 인덱스 컬럼1, 컬럼2
  - 복수의 키에 대해서 ORDER BY를 사용한 경우
• WHERE 컬럼1 = '값' ORDER BY 인덱스 컬럼
  - 연속하지 않은 컬럼에 대해 ORDER BY를 실행한 경우
• ORDER BY 인덱스컬럼1 DESC, 인덱스컬럼2 ASC
  - DESC와 ASC를 혼합해서 사용한 경우
• GROUP BY 컬럼1 ORDER BY 컬럼2
  - GROUP BY와 ORDER BY의 컬럼이 다른 경우
• ORDER BY ABS(컬럼)
  - ORDER BY 절에 다른 표현을 사용한 경우

설계방법

• 무조건 많이 설정하지 않는다
• 조건에 자주 사용되는 컬럼
• 고유한 값 위주로 설계
• 카디널리티가 높을 수록 좋다(중복도가 낮을수록 좋다)
• Index 키의 크기는 되도록 작게 설계
• 단일 인덱스 여러개 보다 다중 컬럼 인덱스 생성 고려
• UPDATE가 빈번하지 않은 컬럼
• JOIN시 자주 사용되는 컬럼
• Index를 생성할 때 가장 효율적인 자료형은 정수형

Index 문법

1. 인덱스 생성

-- 단일 인덱스
CREATE [UNIQUE] INDEX index_name
ON table_name(column1)

-- 다중 컬럼 인덱스
CREATE [UNIQUE] INDEX index_name
ON table_name(column1, column2, ...)

• UNIQUE : 중복값을 허용하지 않는 인덱스
• index_name : 생성할 인덱스의 이름을 지정
• table_name : 인덱스를 생성할 테이블의 이름
• column1, column2, ... : 인덱스를 생성할 컬럼명

2. 인덱스 조회

SHOW INDEX FROM table_name;

+------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table      | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| your_table |          0 | PRIMARY    |            1 | id          | A         |      100000 |     NULL | NULL   |      | BTREE      |         |               |
| your_table |          1 | idx_name   |            1 | column1     | A         |       10000 |     NULL | NULL   |      | BTREE      |         |               |
| your_table |          1 | idx_name   |            2 | column2     | A         |      100000 |     NULL | NULL   |      | BTREE      |         |               |
+------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

3. 인덱스 변경

• 이미 존재하는 테이블에 인덱스를 추가하거나 제거하려면 ALTER TABLE문을 사용한다.

-- 인덱스 추가
ALTER TABLE table_name
ADD INDEX index_name (column1, column2, ...);

-- 고유한 인덱스 추가
ALTER TABLE table_name
ADD UNIQUE INDEX index_name (column1, column2, ...);

-- 인덱스 삭제
ALTER TABLE table_name
DROP INDEX index_name;

4. 인덱스 삭제

DROP INDEX index_name
ON table_name;

5. 인덱스 정렬

• 인덱스 생성 시점에 필드의 정렬방식 설정

CREATE INDEX index_name ON table_name (column1 DESC)
CREATE INDEX index_name ON table_name (column2 ASC)