개발을 하고 서비스를 운영하다 보면 DB에 수 많은 데이터가 쌓이게 된다.
그럼 그 데이터들 중에서 필요한 데이터를 찾기 위해서 그 데이터를 하나하나 살펴봐야할까?특정 데이터를 조회하는데 있어 그 성능을 높이기 위해 INDEX 라는 것을 생성하고 활용한다.
INDEX는 어떻게 생성 되고 어떤 방법으로 적용되는지 알아보자!이 글에서는 별도의 얘기가 없다면 모두 B tree 기반으로 만든 인덱스임을 알자!
인덱스(Index)를 사용하는 이유
인덱스를 사용하는 이유에는 크게 2가지가 있다.
- 조건을 만족하는 레코드(들)을 빠르게 조회하기 위해서.
- DB의 빠른 정렬(order by)이나 그룹핑(group by)을 빠르게 하기 위해서.
여기에서는 조건을 만족하는 레코드(들)을 빠르게 조회하기 위해서 인덱스가 어떻게 사용되는지 확인해보자!
Full Scan vs 인덱스
SELECT *
FROM user
WHERE first_name = "gobeul"이러한 쿼리를 실행한다고 해보자 user테이블에서 first_name 컬럼이 "gobeul" 인 것을 찾는 쿼리문이다.
인덱스가 없다면 user테이블의 모든 레코드들을 확인하면서 first_name 값을 확인할 것이다.
이때 모든 값을 확인함으로 O(N)의 시간이 걸리고 이를 Full Scan 또는 Table Scan 이라고 한다.
반면에 사전에 만들어 놓은 인덱스가 있다면 어떻게 될까?
결론부터 말하자면 B tree 기반으로 만든 인덱스의 경우 O(logN)의 시간이 소요가 된다.
기존 Full scan의 O(N) 보다 매우 빠른 속도로 데이터를 찾을 수 있다.
인덱스 생성하는 방법
그럼 이제 인덱스를 생성하는 방법에 대해 알아보자.
인덱스를 생성하는 방법은 RDBMS마다 차이가 있을 수 있으며 이 글은 MYSQL 기준으로 작성했다.
| id | name | team_id | backnumber |
|---|
이러한 컬럼값을 가지는 player 라는 테이블이 있다고 가정해보자.
중복이 있는 경우
SELECT * FROM player WHERE name = "Sonny";
선수이름을 뜻하는 name 컬럼의 경우 중복이 있을 수 있다.
이렇게 중복의 가능성이 있는 경우 인덱스생성하는 방법은 아래와 같다.
CREATE INDEX {인덱스 이름} ON {테이블 이름} ({컬럼 이름});
CREATE INDEX + ON 을 사용한 쿼리문으로 생성할 수 있다.
{인덱스 이름} : 내가 쓰고 싶은 인덱스 이름을 적는다.
{테이블 이름} : 말 그대로 테이블 이름이다.
{컬럼 이름} : 인덱스를 만들 컬럼 이름을 넣는다.
이를 바탕으로 name 컬럼의 인덱스를 생성해보면
CREATE INDEX player_name_idx ON player (name);
이렇게 생성해볼 수 있을 것이다.
중복이 없는 경우
SELECT * FROM player WHERE team_id = 105 and backnumber = 7;
반대로 위와 같은 상황이면 어떨까? 105번 팀의 등번호가 7번인 선수는 단 1명밖에 존재하지 않을 것이다. 즉 중복이 없다.
중복이 없는 경우 인덱스는 아래 처럼 생성할 수 있다.
CREATE UNIQUE INDEX {인덱스 이름} ON {테이블 이름} ({컬럼 이름});
이렇게 CREATE UNIQUE INDEX 를 사용해 중복없는 인덱스를 만들 수 있다.
이를 바탕으로 위 조건의 인덱스를 생성해 보자.
CREATE UNIQUE INDEX team_id_backnumber_idx ON player (team_id, backnumber);
이 쿼리문을 보면 컬럼이름에 컬럼이 2개 들어있는 것을 알 수 있다.
이는 조건에서 2가지 컬럼으로 데이터를 찾기 때문에 인덱스도 이에 맞춰 생성해야되기 때문이다.
참고 - 테이블 생성시에 인덱스 함께 만들기
CREATE TABLE player (
id INT PRIMARY KEY,
name VARCHAR(20) NOT NULL,
team_id INT,
backunmber INT,
INDEX player_name_idx (name),
UNIQUE INDEX team_id_backnumber_idx (team_id, backnumber)
);테이블을 생성할 때 바로 인덱스를 만들 수도 있다.
위 쿼리문을 참고하자.
💡 2개이상의 컬럼으로 만든 인덱스를 multicolumn index 혹은 composite index 라고 한다.
💡 PRIMARY KEY의 경우 RDBMS에서 자동으로 인덱스를 생성해준다.
생성된 인덱스 확인하기
이번에는 위에서 만든 인덱스를 잘 생성되었는지 확인해보자.
먼저 인덱스를 확인하는 쿼리문은 다음과 같다.
SHOW INDEX FROM {테이블 명};
진행중인 프로젝트가 있다면 확인해 볼 수 있겠지만 없다면 SQL 문제에서도 확인해볼 수 있다.
프로그래머스에서 제공하는 SQL문제에 들어가 위 쿼리를 실행해보자.
이렇게 나오는 것을 알 수 있다. 많은 정보를 확인할 수 있는데 그중 몇개만 살펴보자.
Non_unique : 인덱스가 유니크한 인덱스인지 알려준다. 유니크한 인덱스라면 0, 유니크한 인덱스가 아니라면 1을 보여줄 것이다.
Key_name : 인덱스 이름이다.
Seq_in_idx : 바로 옆에 Colum_name과 함께 하여 인덱스의 몇 번째 컬럼인지 숫자를 볼 수 있다. multicolumn index인덱스가 아니라면 1이 최대일것이고 multicolumn index라면 적용한 컬럼 수 만큼 숫자가 커질 것이다.
위에서 테이블을 만들때 RDBMS가 PRIMARY KEY를 가지고 자동으로 인덱스를 만들어 준다고 했다.
그래서 위처럼 PRIMARY라는 이름의 인덱스를 확인할 수 있는 것이다.
인덱스 동작방식
다음은 B tree 기반의 인덱스가 어떻게 동작되기에 O(logN)의 시간으로 조회가 가능한지 알아보자.
members 테이블의 a 컬럼을 가지고 B tree 기반의 인덱스를 만들었다면 위 사진처럼 데이터가 만들어진다.
a컬럼의 값은 정렬이 되어 있고 ptr는 포인터라는 의미로 members 테이블의 어떤 레코드와 연관되어 있는 것인지 정보를 담고 있다.
a가 정렬되어 있다는 부분에서 이미 알아차린 분도 있겠지만 바로 이진탐색 같은 방식으로 데이터를 조회한다. 이진탐색은 정렬된 자료라면 O(logN)의 속도로 자료를 찾을 수 있는 알고리즘이다.
이제 위 인덱스를 이용해 SELECT * FROM members WHERE a = 7 이러한 쿼리문을 효율적으로 처리할 수 있게 되었다.
그러면 SELECT * FROM members WHERE a = 7 AND b = 95 이런 쿼리문은 어떨까?
저 인덱스가 효율적으로 적용될까?
a = 7 인 부분은 효율적으로 찾을 수 있지만 b = 95를 찾기 위해 ptr을 통해 members 테이블에서 b 컬럼의 값을 확인해봐야 한다.
이건 비효율적이기 때문에 a, b값으로 만든 multicolumn index가 필요하다.
결국은 데이터 조회의 "조건"에 따라 최적의 효율을 얻을 수 있는 인덱스가 있고 이를 잘 선택해서 사용해야 될 것이다.
특정 쿼리에서 사용하고 있는 인덱스 확인하기
조회의 조건에 따라 적절한 인덱스를 사용해야된다는 것을 알았다.
그럼 실제 쿼리문을 실행했을 때, 그 쿼리가 어떤 인덱스를 사용하고 있는지 어떻게 알 수 있을까?
쿼리문 앞에 EXPLAIN을 붙여 간단하게 확인할 수 있다.
이번에도 프로그래머스를 통해 실행해봤다.
possible_keys : 쿼리문에 사용 가능한 인덱스를 나타낸다.
key : 실제 이 쿼리에서 사용한 인덱스를 보여준다.
animal_id라는 PRIMARY KEY를 조건으로 걸었기 때문에 PRIMARY 인덱스가 사용된 것을 알 수 있다.
앞서 말했듯이 PRIMARY 인덱스는 테이블 생성시 자동으로 RDBMS가 만들어준다.
이런 경우에야 존재하는 인덱스가 PRIMARY 밖에 없어서 이걸 사용했다하지만 인덱스가 여러개일때는 뭐가 사용이 될까??
💡 바로 DBMS의optimizer라는 친구가 자동으로 적절한 인덱스를 사용해준다.정말 좋은 친구긴한데
optimizer도 실수를 할 수 있다.SELECT ANIMAL_ID, NAME FROM ANIMAL_INS USE INDEX (myIndex) WHERE animal_id = "A349996"; SELECT ANIMAL_ID, NAME FROM ANIMAL_INS FORCE INDEX (myIndex) WHERE animal_id = "A349996"; IGNORE INDEX -> 특정 인덱스 무시이렇게 쿼리안에 USE INDEX 또는 FORCE INDEX 를 사용하여 사용할 인덱스를 선택할 수 있다.
USE INDEX 는 가급적 사용해달라는 의미이다. 만약
optimizer가 full scan 판단한다면 full scan을 사용할 것이다.FORCE INDEX 는 좀 더 강하게 사용해 달라고 말한다. 이 경우 해당 인덱스로 원하는 데이터를 찾을 수 없을 때만 full scan으로 조회를 한다.
인덱스 생성을 조심 해야되는 이유
조회의 막강한 성능을 보이는 인덱스지만 우리는 이 인덱스 생성을 신중해야한다.
왜 그런지 그 이유를 알아보자.
1. Table에 데이터 추가시에 Index 변경으로 인한 시간 발생
새로운 데이터가 테이블에 들어오면 그에 맞게 만들어진 인덱스에도 그 데이터가 들어가야한다.
그런데 B tree 기반의 인덱스의 경우 새로운 데이터가 바로 리프노드에 붙는 것이 아니다.
조회 성능을 위해 정렬된 위치에 데이터가 들어가야하고 이때문에 데이터양이 많아질수록 시간적 비용이 발생하게 된다.
당연히 생성된 인덱스가 많다면 그만큼 더 시간이 필요하다.
2. 추가적인 공간필요
인덱스도 결국엔 데이터이다. 때문에 인덱스를 만든다는 것은 데이터를 만든다는 의미이므로 불필요한 인덱스는 결국 불필요한 데이터이다.
사진을 보면 player 테이블에 id 인덱스, name 인덱스, team_id와 backnumber로 만든 multicolumn index가 있다.이 경우
WHERE team_id = 100의 쿼리문을 수행하는데 인덱스를 만들어야할까?
답은 No! 이다.
team_id와 backnumber 로 만든 인덱스는 team_id을 우선으로 정렬되어 있기 때문에 이 multicolumn index를 사용해도 같은 효율을 가질 수 있다.그래서 team_id 별도의 인덱스를 만드는 건 비효율적이다.
하지만 만약에
backnumber와 team_id이렇게 team_id가 후순위로 밀려있다면 team_id 별도의 인덱스 생성을 필요로 할 것이다.💡 그래서 우리 개발자들은 생성된 인덱스가 무엇인지 알고 적절한 인덱스를 생성하는 역할이 필요하다.
그외
커버링 인덱스
커버링 인덱스란 인덱스에서 조회할 데이터를 모두 들고 있는 경우를 말한다.
이경우 포인터를 통해 데이터에 접근하는 과정이 불필요해지기 때문에 더 좋은 조회성능을 가질 수 있다.
Hash Index
이름 처럼 해시테이블을 이용한 인덱스이다. 이는 O(1)의 엄청난 조회 성능을 보여주지만 몇가지 단점이 있다.
- rehashing이 부담스럽다.
- 범위 조건을 처리할 수 없다.
예를 들어WHERE a = 7;이런 쿼리는 처리할 수 있지만WHERE a > 7;이러한 쿼리에는 사용될 수 없다. - multicolumn index의 경우 전체 컬럼에 대한 조건만 가능하다.
위에서 a, b 두개로 만든 multicolumn index의 인덱 경우WHERE a = 7;이러한 조건에도 이 인덱스를 사용할 수 있다고 했다.
하지만 HASH 인덱스의 경우 인덱스에 사용된 컬럼이 모두 조건에 있는 경우에만 사용할 수 있다.
Full Scan
재밌게도 풀스캔이 더 좋은 성능을 내는 경우도 있다.
- 데이터의 양이 몇 백건정도로 적은 경우
- 조회하는 데이터가 테이블이 상당 부분을 차지하는 경우
이렇게 두가지 경우는 Full Scan이 더 좋은 성능을 낼 수도 있는데, 이 때 풀스캔을 사용할지 인덱스를 사용하지에 대한 선택 또한 optimizer가 판단하여 처리해준다.
참고
