[데이터베이스] 인덱스 알아보기

인덱스는 where 절에서 사용해야 효과가 있다. 

예를 들어 '책' 테이블의 '출판사', '제목', '작가'가 있다고 하자.
이때 인덱스는 '출판사'에 걸려 있다.
1) SELECT * FROM '책' WHERE '작가' = '짱구';
2) SELECT '제목' FROM '책' WHERE '출판사' = '스타';
위의 두 쿼리 중 인덱스의 효과를 받을 수 있는 쿼리는 2번뿐이다.

인덱스는 무조건 많다고 좋지 않다.

인덱스가 많아질수록 무조건 검색 속도 향상을 기대할 수 있는 것은 아니다. 인덱스는 데이터베이스의 메모리를 사용하여 테이블 형태로 저장되므로 인덱스의 개수와 메모리 사용량은 비례한다. 자주 조회하고 고유한 값 위주로 설정하는 것이 좋다. 

DML의 경우 UPDATE, DELETE에선 WHERE절에 적절한 인덱스를 붙여주면 조회 성능이 크게 감소하지 않으나,

INSERT의 경우는 때에 따라 인덱스 페이지를 이루는 테이블들의 변경이 일어나면서 성능이 저하될 수 있다.

 

효율적인 인덱스 컬럼 설정 방법

후보 컬럼들의 다음 네 가지를 고려한다.

1. 카디널리티

2. 선택도(데이터

3. 활용도 

4. 중복도

 

1. 카디널리티 => 카디널리티가 높을 수록 인덱스 설정에 좋은 컬럼이다. 

카디널리티는 컬럼에 사용되는 값의 다양성 정도, 중복의 정도를 의미한다.

ex) 10개의 row를 가지는 '학생' 테이블에서 '학번', '학년' 컬럼을 갖고 있다고 해보자. '학번'은 고유하므로 10개 값을 가질 수 있지만 '학년'은 3개값을 가질 수 있다. 따라서 중복도가 낮은 '학번'의 카디널리티가 높다.

 

2. 선택도 => 선택도가 낮을 수록 인덱스 설정에 좋은 컬럼이다.(5~10% 적정)

선택도란 데이터에서 특정 값을 얼마나 잘 선택할 수 있는 지에 대한 지표다.

아래와 같이 계산된다.

선택도 = 컬럼의 특정 값의 row 수 / 테이블 총 row 수 * 100

         = 컬럼의 값들의 평균 row 수 / 테이블의 총 row 수 * 100

ex) 10개의 row를 가지는 '학생' 테이블, 컬럼은 '학번', '학년'이 있다고 하자. '학번'은 고유한 값을 가지고 '학년'은 1학년 3명, 2학년 3명, 3학년 4명이다.

이때 '학번'의 선택도 = 1/10*100 = 10% (where절에 '학번'=1 을 넣어줄 때 단 1개의 row가 조회됨, 따라서 특정값은 1)

'학년'의 선택도 = 3.33 / 10 * 100 = 33.3%

(where절에 '학년'=1 을 넣어줄 때 3개의 row가 조회됨, 평균을 계산하면 '학년'의 특정값은 3.33가 됨)

 

3. 활용도 => 실제 작업에 활용도가 높을수록 인덱스 설정에 좋은 컬럼이다.

즉 where절에 얼마나 자주 쓰이는지를 판단하면 된다.

 

4. 중복도 => 중복도가 없을 수록 인덱스 설정에 좋은 컬럼이다.

여기서 중복도란 중복 인덱스 여부에 대한 값이다. 협업의 경우 같은 컬럼에 중복으로 인덱스 설정이 될 수 있다. 인덱스도 테이블 형태로 생성되고 속성을 컬럼으로 가지기 때문에 이 속성이 다르다면 중복으로 인덱스가 만들어질 수 있는 것이다. 

 

 

이 밖에도 다음을 고려해준다.

- 기본 키, 조인의 연결 고리가 되는 컬럼

- Update가 빈번하지 않은 컬럼

- Join시 자주 사용되는 컬럼

- 조건절에 자주 사용하는 컬럼

- 단일 인덱스 여러 개보다는 1개의 다중 컬럼 인덱스가 좋음

- 되도록 동등 비교(=)를 사용

 

 

인덱스 사용 시 주의할 점

인덱스에 가장 적합한 자료형은 정수형 자료다.(TINYINT, SMALLINT, INT, BIGINT 등)

가변적인 크기 및 정규화 할 수 없는 데이터(예를 들면 TEXT 데이터)는 비효율적으로 동작하는데, 이를 위해 FULLTEXT 인덱스를 사용할 수 있다. FULLTEXT 인덱스는 TEXT, BLOB, VARCHAR 등 가변적인 데이터에서 많은 효과를 볼 수 있다.

 

- Index를 사용한 조건 검색이 not, <, >일 경우 인덱스를 사용하지 않는다.(=>, <=, =, Between은 가능)

- Index를 사용한 조건 검색이 like '%갑' 또는 '%값%'이면 인덱스를 사용하지 않는다. ('값%'는 가능)

- Index를 사용한 조건 검색이 다른 필드와 비교일 경우 인덱스를 사용하지 않는다. (where 컬럼1=컬럼2)

 

 

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 절에 다른 표현을 사용

 

 

다중 컬럼 인덱스

다중 컬럼 인덱스는 두 개 이상의 필드를 조합해서 생성한 인덱스다. 1번째 조건과 이를 만족하는 2번째 조건을 함께 인덱스로 사용한다. (MySQL은 인덱스를 최대 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의 효과를 볼 수가 없다.

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

 

 

인덱스 문법

인덱스 생성

-- 단일 인덱스
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://spiderwebcoding.tistory.com/6