왜 DB 테이블의 모든 컬럼에 인덱스를 걸면 안되나요?
조회수 18195회
안녕하세요, 저는 Database 쪽에 좀 약한데요..
쿼리는 어느 정도 작성이 가능한데 인덱스가 아직 많이 부족한 것 같습니다.
흔히들 얘기하시는게 한 테이블에 인덱스를 많이 걸면 오히려 성능이 안좋아진다고 하는데요. 무슨 이유로 성능이 안좋아진다는 것인지 잘 모르겠습니다. 일례로 MySQL에서 한 테이블의 모든 컬럼에 인덱스를 다 걸었을 경우 이건 비효율 적이며 성능 상에도 문제가 있다 종종 듣곤 하는데 아 그렇구나 끄덕끄덕 해도 막상 내부에서 어떤 이유로 성능이 안좋아진다는 것인지 잘 모르겠습니다.
고수님들의 속 시원한 답변 부탁드립니다!
-
(•́ ✖ •̀)
알 수 없는 사용자
2 답변
-
일단 저는 DBA 는 아니고 프로그래머이기 때문에 프로그래머 관점에서 설명드리겠습니다.
DB 를 직접 만든다고 생각해보죠. DB 는 거대한 list 에서 특정 item 을 찾기 위한 시스템입니다.
List<User> users;
list 크기가 작을 때야 그냥 for-loop 돌면서 일일이 비교하면 되겠지만,
for (int i = 0; i != users.Count; ++i) { if (users[i].name == inName) return users[i]; }
list 크기가 커지거나 빠른 검색이 필요하다면 dictionary 같은 것이 필요합니다.
Dictionary<string, User> nameIndex; nameIndex.TryGetValue(inName, outUser);
검색이 빨라진 것까지는 좋은데 여기서 단점이 하나 생깁니다. 데이터를 추가할 때마다 인덱스에 추가를 해주어야 한다는 거죠.
users.Add(newUser); nameIndex.Add(newUser.name, newUser);
모든 컬럼에 인덱스를 추가해 준다면 아래처럼 작동할겁니다.
ageIndex.Add(newUser.age, newUser); jobIndex.Add(newUser.job, newUser); ...
자칫 본체보다 인덱스가 더 큰 용량을 필요로 하게 될겁니다. 거대한 데이터를 처리위해 DB 를 사용하는건데 본체보다 더 거대한 인덱스가 생긴다면 좀 곤란하겠죠?
추가뿐만 아니라 수정할때도 일일이 인덱스에서 찾아 기존 키 삭제하고 새로운 키를 넣어줘야 할겁니다. DB가 알아서 처리해주기 때문에 괜찮아(?)보이는 것일 뿐 직접 프로그래밍한다고 생각하면 끔직한 일이죠.
마지막으로 검색은 괜찮을까요?
예를 들어 지난 주에 접속해서 게임을 플레이했던 유저를 찾아서 보상을 준다고 해봅시다. 단순하게 게임 플레이 시간에 인덱스를 걸어 레코드를 추가해본다고 해보죠.
gameRecord.playTime = Now(); playTimeIndex.Add(gameRecord.playTime, gameRecord)
10000명 유저가 하루 100번 플레이하면 저 인덱스에 들어가는 키 개수는 백만갭니다. 1주일이면 7백만개, 1년이면 25억정도일까요. 1년 정도 데이터가 쌓여있다면 25억개 키 중에 2016-03-14 00:00:00 ~ 2016-03-20 23:59:59 에 포함되는 경우를 찾아야 하는데... 역시 끔찍하죠;;
이것이 바로 아무 컬럼에나 인덱스를 걸면 안 되는 이유입니다.
만약 플레이 시간(time) 대신 플레이 주(week) 컬럼에 인덱스를 걸면 키는 25억개에서 52개 정도로 급격하게 줄어듭니다.
gameRecord.playTime = Now(); gameRecord.playWeek = playTime.ToWeek() playWeekIndex.Add(gameRecord.playWeek, gameRecord)
52개 검색은 순식간이겠죠?
-
(•́ ✖ •̀)
알 수 없는 사용자
-
-
우선 인덱스의 특징을 살펴보면,
- 인덱스 목적이 질의 결과를 빠르게 찾는 데 목적이 있습니다.
- 데이터베이스에 레코드가 삽입, 삭제될 때마다 인덱스가 변경되어야 합니다.
- 그런데 데이터베이스에서 인덱스는 별도의 자료 구조, 예를 들어 B-Tree와 같은 형태로 관리하게 됩니다. 인덱스 자료 구조도 디스크에 저장하여, 질의가 있을 경우 읽어와 사용하게 됩니다.
여기서 3번이 인덱스가 많아지면 문제가 될 수 있는 부분입니다.
레코드의 삽입 삭제가 이루어질 때마다, 해당 레코드에 의해 변경되어야 하는 모든 인덱스에 연산을 취하고 그 결과를 디스크에 다시 저장해야 합니다.
예를 들어 테이블에 주키(Primary Key)만 있는 경우에, 레코드 삽입 삭제시 일어나는 연산을 아주 간단히 나타내면 다음과 같습니다.
- 레코드 1개 삽입: <테이블에 레코드 추가 저장>, <주키 인덱스 삽입 연산 후 저장>
- 레코드 1개 삭제: <테이블에 레코드 삭제(혹은 삭제 표기)>, <주키 인덱스 삭제 연산 후 저장>
이제 테이블에 또 다른 키에 대한 인덱스 가와 인덱스 나가 있다고 가정해보면 다음과 같이 될겁니다.
- 레코드 1개 삽입: <테이블에 레코드 추가 저장>, <주키 인덱스 삽입 연산 후 저장>, <인덱스 가 삽입 연산 후 저장>, <인덱스 나 삽입 연산 후 저장>
- 레코드 1개 삭제: <테이블레 레코드 삭제>, <주키 인덱스 삭제 연산 후 저장>, <인덱스 가 삭제 연산 후 저장>, <인덱스 나 삭제 연산 후 저장>
위와 같이 이뤄지게 됩니다. DBMS의 구현에 따라 각 연산의 속도차이가 있을 뿐, 일의 양이 변화하지는 않을 겁니다. 즉 인덱스가 많아지면 많아질 수록 레코드 삽입과 삭제시 할일이 많아지는 데, 이 일이 모두 디스크 I/O와 관련이 있습니다.
따라서 인덱스를 많이 작성하게 되면 삽입/삭제에서 성능이 차이가 날 수 밖에 없습니다. 다만 검색에서는 많은 인덱스를 사용할 수 있으니 다양한 검색 조건에서 성능을 낼 수 있을 겁니다. 사용하는 질의어가 대부분 프로그래밍에 의해서 결정되는 것이라면, 이미 어떤 열(속성, 컬럼)에 의해 검색되는 지 알고 있으므로, 이에 맞게 인덱스를 생성해야 효율적으로 사용할 수 있을 겁니다.
그래서 데이터베이스를 사용하려고 하는 성격, 검색 비중이 높은가? 혹은 삽입/삭제가 빈번하게 있는가? 에 따라, 전략적으로 운영하셔야 합니다.
댓글 입력