[INDEX] DB 인덱스 개요

이번주에는 Real My SQL 8.0 1권을 읽고 DB 인덱스를 공부할 것입니다.
왜냐하면 DB 쿼리 성능을 언급하면 빼놓을 수 없는 부분이 인덱스이기 때문입니다 😁

💿 디스크 읽기 방식

컴퓨터의 CPU나 메모리처럼 전기적 특성을 띤 장치의 성능은 빠르게 변화했다.
하지만 디스크 같은 기계식 장치의 성능은 제한적으로 발전한다.
현재 하드 디스크보다 SSD드라이브가 많이 활용되나, 데이터 저장 매체는 컴퓨터에서 가장 느린 부분이다.
데이터베이스의 성능 튜닝은 어떻게 디스크 I/O (입출력)을 줄이느냐!가 관건인 경우가 많다.

랜덤 I/O와 순차 I/O

• 디스크 원판을 가지지않는 SSD는 랜덤과 순차 I/O 차이가 없을 것 같지만, 실제로는 그렇지 않다.
• 랜덤 I/O는 하드 디스크 드라이브의 원판을 돌려 읽어야할 데이터가 저장된 위치로 디스크 헤더를 이동시켜 데이터를 읽는다.
• 순차 I/O또한 과정은 같다.
• 하지만 여러 개 데이터를 입력하면, 순차 I/O는 디스크 헤더를 1번만 움직이고, 랜덤 I/O는 디스크 헤더를 데이터 개수 만큼 움직인다 😂
• 랜덤 I/O는 작업 부하가 훨씬 크다..!
• 디스크의 성능은 디스크 헤더의 위치 이동 없이 얼마나 많은 데이터를 한 번에 기록하느냐로 결정된다.
• 사실 쿼리를 튜닝해 랜덤 I/O -> 순차 I/O로 실행할 방법은 많지 않아서, 꼭 필요한 데이터만 읽도록 쿼리를 개선한다. (랜덤 I/O 자체를 줄임)

📁 인덱스

DBMS에서 모든 데이터를 검색해 결과를 가져오려면 시간이 많이 든다.
따라서 칼럼의 값과, 해당 레코드가 저장된 주소를 key-value로 인덱스를 만든다.

• DBMS의 인덱스는 칼럼의 값을 주어진 순서로 미리 정렬해 보관한다.
• 인덱스는 테이블의 칼럼 단위에 생성되며, 하나의 칼럼에는 하나의 인덱스를 생성한다.
• DBMS의 인덱스는 항상 정렬된 상태를 유지하고, 데이터 파일은 저장된 순서로 별도의 정렬 없이 저장한다.

🧨 인덱스의 장단점

• 저장하는 과정이 복잡하고 느리지만 값을 찾을 땐 아주 빠르다.
• DBMS의 인덱스가 많은 테이블은 INSERT, UPDATE, DELETE가 느리다.
• 하지만 SELECT는 빠르다.

🧨 인덱스 설정 기준

테이블에 인덱스를 추가하려면, 데이터의 저장속도를 어디까지 희생하는지, 읽기 속도를 얼마나 더 빠르게 만들어야하는지에 따라 설정해야한다.

1. 카디널리티 - 높게
2. 선택도 - 낮게 (5~10%)
3. 활용도 - 높게
4. 중복도 - 낮게

🧨 인덱스 동작 확인

• 실행 계획 확인 (EXPLAIN)
• 실행 시간 비교

🧨 인덱스 사용시 주의할 점

• SELETCT는 속도가 빨라지지만, INSERT, UPDATE, DELETE 작업은 오버헤드 발생 가능성이 있음 -> 지나치게 많으면 성능 저하 발생
• ID, 주민번호같이 중복이 적은 컬럼에 지정하는 것이 좋음
• WHERE, JOIN에서 자주 사용하는 컬럼을 인덱스로 사용
• 인덱스로 지정한 컬럼은 변경없이 그대로 사용
• 데이터의 UPDATE 작업이 빈번하면 인덱스 UPDATE 작업 오버헤드 발생 가능

❓ 이름, 국가, 성별이 있는 테이블에서 인덱스를 걸려면?

• 보통 테이블 당, 3~5개 이므로 복합인덱스를 사용해도 될 것 같음

• 카디널리티(중복), 선택도, 활용도(WHERE), 중복인덱스 여부에 따라 선택

  • 중복이 적은 컬럼 고르기 -> 이름일 것
  • 선택도가 낮은 인덱스 고르기 (일반적으로 5~10%) -> (고유 데이터 수 / 전체 데이터 수) * 100 -> 이름이 높을 것으로 생각
  • WHERE, JOIN, ORDER BY에서 자주 사용하는 열에 우선적 인덱스 고려
  • 인덱스가 중복으로 생성되지 않은 경우

📁 클러스터형 인덱스 (Clustered Index) vs 보조 인덱스 (Secondary Index)

클러스터형 인덱스

• 자동으로 생성된 인덱스
• 영어사전과 같은 느낌으로 책 뒤에 찾아보기가 있는 보조인덱스와 반대
• 만약 기본키가 member_id 인 테이블이 있다면, 자동으로 member_id열에 클러스터형 인덱스가 생성
• 따라서 테이블 당 1개만 생성 가능
• 데이터를 인덱스로 지정한 열에 맞춰 자동 정렬
• Leaf 페이지 == Data 페이지

보조 인덱스

• 책 뒤의 찾아보기 느낌
• 앞에서 기본키를 지정하면 클러스터형 인덱스가 생성되었다. 그러면 열을 고유키(UNIQUE)로 지정하면? 보조인덱스 생성
• 보조 인덱스는 테이블에서 여러 개 가능
• 자동 정렬 x
• DB 공간 낭비가 일어날 수 있으니 꼭 필요한 열에만 적절하게 생성하자!
• Leaf 페이지에 데이터 위치 기록