왜 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개 검색은 순식간이겠죠?

    • (•́ ✖ •̀)
      알 수 없는 사용자
    • index가 내부적으로 구성되는 원리 예제 들어서 설명해주시니 이해하기 좋네요. 알 수 없는 사용자 2016.3.30 09:56
  • 우선 인덱스의 특징을 살펴보면,

    1. 인덱스 목적이 질의 결과를 빠르게 찾는 데 목적이 있습니다.
    2. 데이터베이스에 레코드가 삽입, 삭제될 때마다 인덱스가 변경되어야 합니다.
    3. 그런데 데이터베이스에서 인덱스는 별도의 자료 구조, 예를 들어 B-Tree와 같은 형태로 관리하게 됩니다. 인덱스 자료 구조도 디스크에 저장하여, 질의가 있을 경우 읽어와 사용하게 됩니다.

    여기서 3번이 인덱스가 많아지면 문제가 될 수 있는 부분입니다.

    레코드의 삽입 삭제가 이루어질 때마다, 해당 레코드에 의해 변경되어야 하는 모든 인덱스에 연산을 취하고 그 결과를 디스크에 다시 저장해야 합니다.

    예를 들어 테이블에 주키(Primary Key)만 있는 경우에, 레코드 삽입 삭제시 일어나는 연산을 아주 간단히 나타내면 다음과 같습니다.

    • 레코드 1개 삽입: <테이블에 레코드 추가 저장>, <주키 인덱스 삽입 연산 후 저장>
    • 레코드 1개 삭제: <테이블에 레코드 삭제(혹은 삭제 표기)>, <주키 인덱스 삭제 연산 후 저장>

    이제 테이블에 또 다른 키에 대한 인덱스 가와 인덱스 나가 있다고 가정해보면 다음과 같이 될겁니다.

    • 레코드 1개 삽입: <테이블에 레코드 추가 저장>, <주키 인덱스 삽입 연산 후 저장>, <인덱스 가 삽입 연산 후 저장>, <인덱스 나 삽입 연산 후 저장>
    • 레코드 1개 삭제: <테이블레 레코드 삭제>, <주키 인덱스 삭제 연산 후 저장>, <인덱스 가 삭제 연산 후 저장>, <인덱스 나 삭제 연산 후 저장>

    위와 같이 이뤄지게 됩니다. DBMS의 구현에 따라 각 연산의 속도차이가 있을 뿐, 일의 양이 변화하지는 않을 겁니다. 즉 인덱스가 많아지면 많아질 수록 레코드 삽입과 삭제시 할일이 많아지는 데, 이 일이 모두 디스크 I/O와 관련이 있습니다.

    따라서 인덱스를 많이 작성하게 되면 삽입/삭제에서 성능이 차이가 날 수 밖에 없습니다. 다만 검색에서는 많은 인덱스를 사용할 수 있으니 다양한 검색 조건에서 성능을 낼 수 있을 겁니다. 사용하는 질의어가 대부분 프로그래밍에 의해서 결정되는 것이라면, 이미 어떤 열(속성, 컬럼)에 의해 검색되는 지 알고 있으므로, 이에 맞게 인덱스를 생성해야 효율적으로 사용할 수 있을 겁니다.

    그래서 데이터베이스를 사용하려고 하는 성격, 검색 비중이 높은가? 혹은 삽입/삭제가 빈번하게 있는가? 에 따라, 전략적으로 운영하셔야 합니다.

답변을 하려면 로그인이 필요합니다.

프로그래머스 커뮤니티는 개발자들을 위한 Q&A 서비스입니다. 로그인해야 답변을 작성하실 수 있습니다.

(ಠ_ಠ)
(ಠ‿ಠ)