Gidhub BE Developer

인덱스(Index)

2018-10-06
goodGid
Technology

Index란?

  • SQL의 수행 속도를 향상시키기 위해서 테이블과는 별도로 생성되는 오브젝트이다.

  • 테이블 내의 원하는 레코드를 빠르게 찾아갈 수 있도록 만들어진 목차같은 역할을 하는 것이다.

  • 찾으려는 데이터의 주소를 순서대로 가지고 있는 집합이다.

  • 해당 테이블에 한개 이상의 컬럼에 대하여 Index 생성이 가능하다.

  • 또한 해당 테이블에 여러 개의 Index 생성이 가능하다.

  • 테이블의 값을 빠르게 엑세스 하도록 하는 것이 Index의 역할인데 SQL 구문 실행에 있어 속도 향상을 기대할 수 있다.

  • 그러나 잘못된 Index는 오히려 SQL(DML) 속도 저하의 원인이 되므로 Index 생성할 때는 신중히 생각하고 만들어야 한다.

  • Index가 불필요한 경우는 다음과 같다.

    • 데이터가 적은(수천전 미만) 경우
    • 조회 보다 삽입, 수정, 삭제 처리가 많은 테이블일 경우
    • 조회 결과가 전체행의 15% 이상 검색 작업이 예상되는 겅우

테이블과 Index의 관계

Index가 걸려있는 Table에 어떤 데이터가 삽입되는 상황

  • 데이터를 다시 정렬해서 Index 테이블에 새로 들어온 데이터를 추가하게 된다.

  • Table에 Data가 Insert되면 Index Split 과정을 거쳐서 Index 테이블에 Index가 없다면 테이블에 데이터만 추가한다.

  • 만약 걸려있는 Index가 있다면 Index 테이블을 모두 뜯어내어(Index Split) 성능을 감소 시킨다는 단점이 있다.

Index가 걸려있는 Table에 데이터가 Delete되는 상황

  • 테이블에 데이터가 삭제되면 Index는 해당 데이터를 지우는게 아니라 사용 안함 표시만 한 상태로 두게 된다.

  • 즉 Row수는 변하지 않는다.

  • 삭제 후 다시 데이터가 Insert될 경우에 Index Split가 일어나며 Insert되고, 다시 삭제해도 Index에서는 사용 안함 표시만 하고 삭제되지 않는다.

  • 이와 같은 작업이 반복되면 실제 데이터는 10만건 인데 Index에는 데이터가 100만건 있는 결과를 낳을 수도 있다.

  • 이렇게 되면 더이상 Index는 그 역할을 못하게 되는 상황이 발생한다.

  • 이런 상황에서 실행 계획이 Index Range Scan이라면 Index가 무용지물이므로 차라리 강제로 Hint를 주어 Full Scan하는 것이 나을 것이다.

Index가 걸려있는 테이블에 Update를 하는 상황

  • Delete(데이터 삭제)의 개념과는 다르게 Update는 이전 데이터가 삭제되고 그자리에 새 데이터가 들어오는 개념이다.

  • 그렇기 때문에 Index에 Delete 과정과 Insert 과정 모두가 나타난다.

  • 즉 변경 전 데이터는 삭제 되지 않고, Insert로 인한 Index Split가 일어나게 된다.

Index 생성 과정 및 작동 원리

  • Index는 일단 Data에 아무도 접근 못하도록 한 후 정렬 작업을 수행한다.

  • 정렬 작업이 끝난 후 Block에 기록한다.

  • 사용자가 SQL문을 날리게 되면 그 Table의 해당 컬럼의 Index 존재 유무부터 검사 한다.

  • Index가 존재한다면 그 Index에서 주소를 찾은 다음, 바로 그 주소에 쓰여진 부분만 조회 한다.

  • 모든 MySQL 데이터 타입은 Index가 될 수 있다.

  • 상호 연관된 컬럼에서 Index를 사용하는 것이 SELECT 연산 성능을 향상시키는 최선의 방법이다.

  • 테이블 당 최대 Index 숫자 및 최대 Index 길이는 스토리지 엔진 별로 정의 된다.

  • 모든 스토리지 엔진은 테이블 당 최소 16개의 Index와 최소 256 바이트의 Index 길이를 지원한다.

Index 구성요소

  • INDEX 컬럼의 값(Index key값) : Index를 생성하는 컬럼 값

  • ROW ID : 해당 Row를 엑세스할 수 있는 주소 값

Index의 장점

  1. 키 값을 기초로 하여 테이블에서 검색과 정렬 속도를 향상시킨다.

  2. 질의나 보고서에서 그룹화 작업의 속도를 향상시킨다.

  3. Index를 사용하면 테이블 행의 고유성을 강화시킬 수 있다.

  4. 테이블의 기본 키는 자동으로 Index가 된다.

  5. 필드 중에는 데이터 형식 때문에 Index 될 수 없는 필드도 존재한다.

  6. 여러 필드로 이루어진(다중 필드) Index를 사용하면 첫 필드 값이 같은 레코드도 구분할 수 있습니다.

Index의 단점

  1. Index의 장점 뿐만 아니라 단점도 있어 Index를 모든 경우에 생성하지 않는다.

  2. Index를 만들면 .mdb 파일 크기가 늘어난다.

  3. 여러 사용자 응용 프로그램에서의 여러 사용자가 한 페이지를 동시에 수정할 수 있는 병행성이 줄어든다.

  4. Index 된 필드에서 데이터를 수정하거나, 레코드를 추가 또는 삭제할 때 성능이 떨어진다.

  • 따라서 어느 필드를 Index 해야 하는지 미리 시험해 보고 결정하는 것이 좋다.

  • Index를 추가하면 쿼리 속도가 1초 정도 빨라지지만, 데이터 행을 추가하는 속도는 2초 정도 느려지게 되어 여러 사용자가 사용하는 경우 레코드 잠금 문제가 발생할 수 있다.

  • 필드에 저장될 값이 각양각색으로 서로 다를 경우 or 필드 값이 비슷한 경우가 많을 때는 쿼리에 Index를 사용해도 속도가 크게 향상되지 않는다.

  • 예를 들어, 테이블에 주거하는 집의 층과 호수가 이미 Index된 경우에 우편 번호 필드를 추가로 Index에 포함해도 성능이 거의 향상되지 않는다.

  • 만드는 쿼리의 종류와 관계 없이 가장 고유한 값을 갖는 필드만 Index해야 한다.

Index를 생성해야 하는 경우와 그렇지 않은 경우

  • 자주 검색하는 필드, 정렬하는 필드, 쿼리에서 다른 테이블에 있는 필드로 조인하는 필드에 주로 Index를 만든다.

  • 그러나 추가 쿼리와 같은 실행 쿼리 작업이 수행되는 동안 여러 필드의 Index들이 업데이트되어야 할 때는 속도가 느려질 수 있다.

  • 테이블의 기본 키에는 자동으로 Index가 만들어지지만, 데이터 형식이 OLE 개체인 필드에는 Index를 만들 수 없다.

  • 다음과 같은 경우에 Index를 만들면 좋다.

  1. 필드의 데이터 형식이 텍스트, 숫자, 통화, 날짜/시간 중 하나인 경우

  2. 필드에 저장된 값을 찾는 작업이 예상되는 경우

  3. 필드의 값을 정렬하는 작업이 예상되는 경우

  • Index는 열 단위로 생성된다.

  • WHERE절에서 사용되는 컬럼을 Index로 만든다.

  • 데이터의 중복도가 높은 열은 Index로 만들어도 효용이 없다. (예 : 성별, 타입이 별로 없는 경우, 적은경우)

  • 외래키가 사용되는 열에는 Index를 되도록 생성해주는 것이 좋다.

  • JOIN에 자주 사용되는 열에는 Index를 생성해주는 것이 좋다.

  • INSERT / UPDATE / DELETE가 얼마나 자주 일어나는지를 고려한다.

  • 사용하지 않는 Index는 제거하자

주의 사항

  • Index가 적용되는 필드는 가능한 Null값이 없어야 한다.

  • Index를 사용한 필드를 조건에서 연산,가공하여 사용하면 Index효과는 없다.
    • ex : Select * from 테이블 where Index필드 * 10 > 100
      –> Select * from 테이블 where Index필드 > 100 / 10 으로 사용
  • 다음 연산자는 Index를 타지 않는다.
    • not, <> 는 인덱스 사용못함

    • =, <=는 사용가능

다중 필드 Index

  • 두 개 이상의 필드를 조건으로 찾기나 정렬 작업을 많이 하면 그 필드들을 함께 Index로 지정할 수 있다.

  • 예를 들어, 이름과 성을 다른 필드에서 저장해둔 경우 같은 쿼리에서 두 필드에 대해 조건을 설정하는 일이 많으면 두 필드에 대해 다중 필드 Index를 만든다.

  • 다중 필드 Index로 테이블을 정렬하면
    Index로 정의된 첫 번째 필드로 먼저 정렬하고
    첫 번째 필드가 중복된 값을 가진 레코드가 있으면 Index로 정의된 두번째 필드로 계속 정렬한다.

  • 다중 필드 Index는 최대 10개의 필드를 포함할 수 있습니다.

고민

  • MySQL을 사용하게 되면 사용자가 원하는 내용을 제공해주기 위해서 많은 고민을 하게된다.

  • 빠른 서비스 제공 : 어떻게 하면 빠르게 정보를 검색, 제공할 수 있을까?

  • 효율적인 서비스 운영 : 가능한 적은 자원으로 동일한 서비스를 제공할 수 있을까?

  • 효율적인 서비스 운영에 대한 질문은 쿼리 최적화, 캐싱의 이용과 같은 로우 레벨의 운영부분이다.

  • 그렇다면 빠른 서비스 제공을 하는 방법은 무엇이 있을까?

  • 빠른 속도의 서비스 제공을 하기 위해서는 DB를 정규화하는 방법도 있지만, 다른 한편으로는 Index를 사용하는 방법이 있다.

  • 책과 DB를 비교해보자.

찾아보기 - Index
내용 - 데이터

  • 원하는 책의 내용을 찾고 싶을 땐 찾아보기를 통해 찾고자하는 내용의 위치를 찾는다.

  • 마찬가지로, 원하는 데이터를 찾고 싶을 땐 Index로 해당 데이터의 위치 주소를 찾고 데이터를 찾는다.

  • 찾아보기와 Index의 공통점은 바로 찾기 편하게 만들기 위해서 미리 찾으려는 데이터를 정렬해서 가지고 있다는 점이다.

  • 미리 정렬해서 가지고 있기 때문에 찾을 때 a,b,c,d,… 또는 ㄱ,ㄴ,ㄷ,ㄹ,…과 같이 정렬된 순서대로 빠르게 검색할 수 있다.

DB에서 Index와 데이터 자료 구조

  • Index를 왜 사용하는 지에 대해서 이야기하기에 앞서 Index와 데이터를 저장하는 자료 구조에 대해 알아보자.

  • Index의 자료 구조 : Sorted LIst
    • Sorted LIst는 저장되는 값을 항상 정렬된 상태로 유지하는 자료 구조이다.
  • 데이터의 자료 구조 : ArrayLIst
    • Array LIst는 저장되는 순서에 따라서 정렬없이 저장되는 값을 유지하는 자료 구조이다.

Sorted LIst(= Index의 자료 구조)의 장,단점

  • 장점
    • 이미 정렬 되어있기 때문에 DB에서 SELECT 쿼리를 사용하는 경우, 매우 빠른 속도로 원하는 결과를 검색 가능하다.
  • 단점
    • 데이터가 변화하는 INSERT, UPDATE, DELETE 쿼리를 사용하는 경우 정렬을 해서 데이터를 저장해야하기 때문에 쿼리의 수행시간이 증가한다.

  • 즉, Index를 사용하는 이유는 데이터를 생성(INSERT),변화(UPDATE),삭제(DELETE) 성능은 저하되지만 데이터의 읽기 검색(SELECT)의 성능을 향상 시키기 위함이다.

  • 그래서 한 테이블에서 너무나 많은 컬럼에 Index를 생성하게 되면 데이터 저장 성능이 떨어지게 된다.

Reference

Recommend

Index