[MySQL] 인덱스(INDEX) 정리(효과적인 INDEX 설계)

개요

MySQL 스키마 설계는 자주 해보았지만 효율적인 데이터타입, 인덱스 설계에 대한 부분은 이해도가 낮은 것 같아 조사 후 남기는 포스팅이다.

---

인덱스의 개념

인덱스는 테이블의 동작속도(조회)를 높여주는 자료구조이다. 인덱스로 데이터의 위치를 빠르게 찾아주는 역할이고

쉽게 예를 들어보면 책 뒷편에 '색인'이 인덱스의 역할과 동일하다고 볼 수 있다.

(예를들어 '홍길동'이라는 단어를 찾고싶으면 색인페이지에서 '홍'으로 시작하거나 'ㅎ'으로 시작하는 색인을 찾아보면 빠르게 찾을 수 있다.)

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

조회속도는 빨라지지만 UPDATE, INSERT, DELETE의 속도는 저하된다는 단점이 있다. (Table의 index 색인 정보를 갱신하는 추가적인 비용 소요)

때문에 효율적인 인덱스 설계로 단점을 최대한 보완하는 방법을 생각해볼 수 있다.

 

인덱스의 특징

인덱스는 하나 혹은 여러 개의 컬럼에 대해 설정할 수 있다. (단일 여러개 또는 여러컬럼을 묶어 복합인덱스)

WHERE절을 사용하지 않고 인덱스가 걸린 컬럼을 조회하는 것은 성능에 아무런 영향이 없다.

 

ORDER BY 와 GROUP BY에 대한 INDEX

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

• 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이다. 1번째 조건과 이를 만족하는 2번째 조건을 함께 INDEX해서 사용한다. (MySQL은 INDEX에 최대 15개 컬럼으로 구성 가능)

다중 컬럼 인덱스는 단일 컬럼 인덱스 보다 더 비효율적으로 INDEX/UPDATE/DELETE를 수행하기 때문에 신중해야한다.

때문에 가급적 UPDATE가 안되는 값을 선정해야한다.

 

단일인덱스와 다중 컬럼 인덱스의 차이점

Table1(단일 인덱스)

CREATE TABLE table1(
    uid INT(11) NOT NULL auto_increment,
    id VARCHAR(20) NOT NULL,
    name VARCHAR(50) NOT NULL,
    address VARCHAR(100) NOT NULL,
    PRIMARY KEY('uid'),
    key idx_name(name),
    key idx_address(address)
)

Table2(다중 컬럼 인덱스)

CREATE TABLE table2(
    uid INT(11) NOT NULL auto_increment,
    id VARCHAR(20) NOT NULL,
    name VARCHAR(50) NOT NULL,
    address VARCHAR(100) NOT NULL,
    PRIMARY KEY('uid'),
    key idx_name(name, address)    
)

QUREY문

SELECT * FROM table1 WHERE name='홍길동' AND address='경기도';

table1의 경우에 각각 컬럼(name),(address)에 INDEX가 걸려있기 때문에 MySQL은 name컬럼과 address컬럼을 보고 둘 중에 어떤 컬럼의 수가 더 빠르게 검색되는지 판단 후 빠른쪽을 먼저 검색하고 그 다음 다른 컬럼을 검색하게 된다.

table2의 경우 바로 원하는 값을 찾는데 그 이유는 INDEX를 저장할 때 name과 address를 같이 저장하기 때문이다. 즉, name과 address의 값을 같이 색인하고 검색에서도 '홍길동경기도'로 검색을 시도하게 된다. 이렇게 사용할 경우 table1보다 table2의 경우가 더 빠른 검색을 할 수 있다.

그렇지만 다중 컬럼 인덱스를 아래와 같이 사용하면 INDEX를 타지 않는다.

SELECT * FROM table2 WHERE address='경기도';

이 경우에는 다중 컬럼 인덱스로 설정되어 있던 name이 함께 검색이 되지 않으므로 INDEX의 효과를 볼 수가 없다.

하지만 조건값을 name='홍길동'으로 준다면 B*Tree 자료구조 탐색으로 인해 name컬럼은 인덱스가 적용된다. 예를들어 key idx_name(name, address, age) 일때 WHERE name = ? AND address = ? 는 인덱스가 적용되지만 WHERE name = ? AND age = ? 에서 age 컬럼은 인덱스 적용이 되지 않는다.

다중 컬럼 인덱스를 사용할 때는 INDEX로 설정해준 제일 왼쪽컬럼이 WHERE절에 사용되어야 한다.

 

★설계방법

• 무조건 많이 설정하지 않는다. (한 테이블당 3~5개가 적당 목적에 따라 상이)
• 조회시 자주 사용하는 컬럼
• 고유한 값 위주로 설계
• 카디널리티가 높을 수록 좋다 (= 한 컬럼이 갖고 있는 중복의 정도가 낮을 수록 좋다.)
• INDEX 키의 크기는 되도록 작게 설계
• PK, JOIN의 연결고리가 되는 컬럼
• 단일 인덱스 여러 개 보다 다중 컬럼 INDEX 생성 고려
• UPDATE가 빈번하지 않은 컬럼
• JOIN시 자주 사용하는 컬럼
• INDEX를 생성할 때 가장 효율적인 자료형은 정수형 자료(가변적 데이터는 비효율적)

 

인덱스 문법

인덱스 생성

-- 단일 인덱스
CREATE INDEX 인덱스이름 ON 테이블이름(필드이름1)

-- 다중 컬럼 인덱스
CREATE INDEX 인덱스이름 ON 테이블이름(필드이름1, 필드이름2, ...)

 

인덱스 조회

SHOW INDEX FROM 테이블이름

 

UNUQUE 인덱스 생성(중복 값을 허용하지 않는 인덱스)

-- 단일 인덱스
CREATE UNIQUE INDEX 인덱스 이름 ON 테이블이름(필드이름1)
-- 다중 컬럼 인덱스
CREATE UNIQUE INDEX 인덱스 이름 ON 테이블이름(필드이름1, 필드이름2, ...)

 

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

CREATE INDEX 인덱스이름 ON 테이블이름 (필드이름 DESC)
CREATE INDEX 인덱스이름 ON 테이블이름 (필드이름 ASC)

 

인덱스 삭제

ALTER TABLE 테이블이름 DROP INDEX 인덱스이름;

 

인덱스 추가

ALTER TABLE 테이블이름 ADD (UNIQUE)INDEX 인덱스이름(컬럼명1, 컬럼명2...);

---

참고 포스팅

https://yurimkoo.github.io/db/2020/03/14/db-index.html

유림's Blog

https://code-factory.tistory.com/24

mysql Index 와 다중 컬럼 인덱스