[DataBase] 인덱스??

어떤분의 블로그를 보다가 인덱스라는 개념이 나왔다. 나에게 인덱스란 단순히 검색 쿼리를 빠르게 할 수 있다 정도였다. 맞는 말이지만 해당 블로그에서는 인덱스를 어떻게 적용하는지 적용해서 어떤 결과가 나왔는지와 같은 얘기가 나왔는데 단순히 빠르다 라는 개념밖에 모르니...이해하기 쉽지 않아서 자세하게 정리해본다

 

 

Index??

 

인덱스란 정렬이다.

인덱스는 저장공간을 활용하여 데이터베이스 테이블의 검색속도를 향상시키기 위한 자료구조이다. 

 

예를 들어 내가 원하는 내용을 찾기 위해 책의 모든 페이지를 찾는 것은 시간이 오래걸린다. 이때 책의 색인("찾아보기")와같은 인덱스 역할을 하는 것을 통해 빠르게 찾을 수 있다.

영어 사전에서도 알파벳순 정렬도 마찬가지다. 알파벳순으로 정렬되어있기때문에 빠르게 찾을 수 있다

 

물론 인덱스가 존재하지 않아도 데이터베이스를 작동함에 있어서 큰문제가 없다. 그러나 만약 억단위로 데이터베이스의 크기가 크다면 인덱스가 필요하다. 왜냐면 인덱스가 없다면 처음부터 끝가지 테이블을 뒤져보기때문이다.

 

하지만 여기서 주의할 점은 인덱스가 적용된 칼럼에 insert,update,delete가 수행된다면 각각 인덱스에 추가(삭제)하는 작업을 진행해야한다. 그에따라 오버헤드가 발생할 수 있다.

다만 여기서 오해하지 말것은 update, delete 행위가 느린것이지, update,delete를 하기위해 해당 데이터를 찾는 것은 인덱스가 있으면 빠르다. 또한 만약 인덱스가 없는 칼럼을 조건으로 update,delete를 하게 되면 굉장히 느려 많은 양의 데이터를 삭제할때는 인덱스로 지정된 칼럼을 기준으로 진행하는 것이 좋다

 

그래서 간단히 정리하자면 

인덱스를 활용함으로써 검색소도를 크게 향상시키지만 해당 인덱스 생성을 위한 추가 공간이 필요하다

또한 만약 insert,update,delete같은 데이터 변경 쿼리가 잦은 경우 성능이 악화될 수 있다

 

 

그래서 그럼 언제 인덱스를 사용하는 것이 좋은데??

 

간단하게 말하자면 규모가 작지 않은 테이블

insert,update,delete가 자주 발생하지 않는 칼럼

join이나 where또는 Orderby에 자주 사용하는 칼럼

데이터 중복도가 낮은 칼럼

 

 

 

---

근데 그래서 인덱스는 어떻게 저장되는 거여??? 

 

인덱스를 구현하는 다양한 자료구조가 있지만 대표적으로 해시테이블과 B+트리가 있다

 

해시테이블

 

해시테이블은 많이 들어봤을 것이다. key와 value로 데이터를 저장하는 자료구조 중 하나로 빠른 데이터 검색이 필요할때 유용하다. 해시 에티블은 key값을 이용해 고유한 인덱슬르 생성하여 그 인덱스에 저장된 값을 꺼내오는 구조다

해시 테이블의 시간복잡도 O(1)이다. 따라서 조회속도가 매우 바르다

 

다만 해시테이블은 "=" 등호 연산에만 특화되어있다. 해시함수 값이 하나라도 달라지면 완전히 다른 해시값을 생성하는데, 이런 특성때문에 부등호 연산이 자주 사용되는 데이터베이스 검색은 해시테이블이 적합하지 않다.

또한 범위로 묶어서 보관하는 인덱스(B트리)가 아니므로 데이터개수가 증가함에따라 더 큰 저장공간이 필요로하다

 

 

B- 트리

 

B트리를 이야기하자면

B트리 최상위에 하나의 루트 노드가 존재하고 그 하위에 자식 노드가 붙어있는 형태이다.

트리 구조의 가장 하위노드에는 리프노드라고 하고, 리프노드도 아니고 루트노드도 아닌 것 중간노드를 브랜치 노드라고 한다.

 

• 어떤 데이터를 탐색하든 루트에서 브랜치, 리프, 디스크 저장순으로 진행된다 
• 인덱스의 두번째 칼럼은 첫번째 칼럼에 의존해서 정렬되어있다 
  •  즉 두번재 칼럼 정렬은 첫번재 칼럼의 똑같은 열에서만 의미가 있다
  • 만약 3,4번재 칼럼도 잇다면 3번재 칼럼도 2번재 칼럼에 의존하게 된다
• 디스크에서 읽는 것은 메모리에서 읽는 거보다 성능이 휠씬 떨어진다
  • 인덱스 향상은 디스크 저장소에 덜 접근하겠다는 의미다. 결과적으로 인덱스 root에서 leaf까지 오고 가는 회수를 얼마나 줄이느냐에 달려있다
• 인덱스는 3-4개 정도가 적당하다
  • 너무 많은 인덱스는 새로운 행을 추가할때마다 인덱스에 등록해줘야하고, 수정/삭제시에도 인덱스 수정이 필요하다
  • 또한 인덱스 도 저장공간을 차지 한다
  • 많은 인덱스로 옵티마이저가 잘못된 인덱스를 선택할 수 있게된다(옵티마이저 : 가장 효율적인 방법으로 SQL을 수행할 최적의 처리 경로를 생성해주는 DBMS의 핵심 엔진)

 

 

인덱스 어떤 칼럼을 기준으로 해야하나...?

 

만약 1개의 칼럼으로 인덱스를 건다면 카디널리티가 가장 높은 것을 잡아야하낟

여기서 카디널리티란 해당 컬럼의 중복된 수치를 말한다. 예를 들어 주민번호는 카디널리티가 높다고 말한다

 

인덱스로 효율성을 내려면 해당 인덱스로 많은 부분을 걸러내야하기때문에, 만약 인덱스를 남녀로 한다면 아마 50%밖에 걸러내지 못할것이다. 

 

하지만 주민번호같은 경우 인덱스를 통해 대부분을 걸러낼 수 있어 빠르게 검색가능하다

 

그렇다면 여러개의 칼럼을 기준으로 한다면 어떤 순서로 인덱스를 구성해야하는가??

카디널리티가 높은 순에서 낮은 순으로 구성하는 것이 좋다

 

이와 관련하여 테스트하신분의 블로그를 첨부한다

https://jojoldu.tistory.com/243

[mysql] 인덱스 정리 및 팁

 

 

인덱스 조회시 주의사항

 

일단 만약 여러 칼럼으로 인덱스를 구성했으때 만약 조회쿼리에서 첫번째로 지정한 인덱스 칼럼을 빼면 인덱스를 못 사용할 수 있다.

조회 쿼리 사용시에 인덱스를 사용하려면 조회조건에 포함되어야한다

 

또한 만약 인덱스에 사용된 칼럼에 범위조건(between, like, <,>)등을 사용하면 해당 칼럼은 인덱스를 타지만, 그 뒤에 지정한 인덱스 칼럼들은 인덱스에 적용되지 않는다

-> Id,date,number 라고 인덱스로 등록했을때 만약 date > now와 같이 되어있다면 number는 인덱스가 적용되지 않는다

 

그러나 =, in은 다음 칼럼도 인덱스를 사용한다

다만 서브쿼리에 넣게되면 성능상 이슈가 발생할 수 있다 -> in의 인자로 서브쿼리가 들어가면 서브쿼리의 외부가 먼저 실행되고 ,in은 체크 조건으로 실행되기 때문이다

 

and연산자는 Row수를 줄이지만 or 연산자는 row수가 늘어나기때문에 풀 테이블 스캔이 발생할 확률이 높다

-> where 에서 or에 대한 사용을 주의해야한다

 

인덱스로 사용된 칼럼값은 그대로 사용해야만 인덱스가 사용된다

-> 가공되 칼럼은 적용되지 않는다. 예를 들어 where price*100>5000은 인덱스를 못타지만 where price>5000/100은 인덱스를 사용할 수 있다

 

null값의 경우 is null 조건으로 인덱스 레인지 스캔 가능하다(인덱스 레인지 스캔  : 인덱스 레인지 스캔(Index Range Scan)은 데이터베이스에서 인덱스를 사용하여 특정 범위의 값을 검색하는 방법 중 하나)

 

 

 

 

---

참고블로그

https://jojoldu.tistory.com/243

[mysql] 인덱스 정리 및 팁

https://inpa.tistory.com/entry/MYSQL-%F0%9F%93%9A-%EC%9D%B8%EB%8D%B1%EC%8A%A4index-%ED%95%B5%EC%8B%AC-%EC%84%A4%EA%B3%84-%EC%82%AC%EC%9A%A9-%EB%AC%B8%EB%B2%95-%F0%9F%92%AF-%EC%B4%9D%EC%A0%95%EB%A6%AC

[MYSQL] 📚 인덱스(index) 핵심 설계 & 사용 문법 💯 총정리

https://mangkyu.tistory.com/96

[Database] 인덱스(index)란?