[Section9] 인덱스2

[gonasooc]

아래 형식으로 공유할 내용을 남겨주세요~!

새롭게 알게 된 점
어려웠거나 궁금했던 점
업무와 관련된 내용
기타 (자유 내용)

[gonasooc]

새롭게 알게 된 점

• 옵티마이저가 인덱스를 사용하는 것이 비효율적이라 판단하면 인덱스가 존재하더라도 포기하고 풀 테이블 스캔을 하는 ‘인덱스 손익분기점’이 인상적이었습니다. 결국 개발은 코스트 기반으로 판단하는 것..
• 랜덤 I/O와 순차 I/O에 대한 설명을 듣고, 쿼리를 조회한다는 걸 HDD, SDD 같은 하드웨어로 연결해서 생각해 본 적은 없었는데, 왜 탐색이 더 빠르거나 느린지 하드웨어 관점으로 이해할 수 있어서 좋았습니다.

기타 (자유 내용)

• 랜덤 I/O, 순차 I/O

  SQL 연산	주로 사용하는 I/O 방식	설명
  Index Range Scan	랜덤 I/O	인덱스를 탐색하며 여기저기 흩어진 블록을 접근
  Point Query (PK 조회)	랜덤 I/O	인덱스를 통해 특정 레코드로 직접 이동
  Full Table Scan	순차 I/O	테이블 전체를 연속해서 읽음
  Sort / Group By 대량 연산	순차 I/O	임시 테이블을 순차적으로 읽고 쓰며 정렬

• 옵티마이저가 범위 검색(range scan)을 내부적으로 동등 비교의 여러 묶음으로 변환해서 처리한다는 내용이 좀 더 궁금해서 문서만 조금 체크해봤습니다.

  • Equality Range Optimization of Many-Valued Comparisons - https://dev.mysql.com/doc/refman/8.4/en/range-optimization.html

[ZetBe]

새롭게 알게 된 점

index 1 & 2모두 본 기준

요약

일단 기본적인 인덱스는
미리 테이블을 정렬방식으로 재가공하여 빠르게 인덱스만 뽑아 기존 테이블에 접근함(기존 풀 스캔 대비)

하지만 이렇게 해서 기존 테이블에 접근하려는 순간도 많은 비용이 발생할 수 있어
인덱스에 컬럼을 추가하여 인덱스를 만들 수 있음(커버링 인덱스)

인덱스를 만들 때, 각 컬럼마다 GROUP BY 처럼 옆 컬럼 값에 따라 부분부분 정렬되어 있을 수도 있기 때문에,
맨 왼쪽의 컬럼을 명확하게 where로 = 지정하면 좋음

B 트리는 어떻게 움직일까

B 트리가 어떻게 움직이는 지 궁금하여 찾다보니 시뮬레이션해주는 사이트가 있어 공유 드립니다 https://www.cs.usfca.edu/~galles/visualization/BTree.html

개인적으로 데이터가 오름, 내림차순으로 들어오며 가운데를 어떻게 맞추나 궁금했는데,
숫자 삽입을 계속 수행하다 한쪽으로 숫자들이 쏠린다 싶으면

(숫자 개수가 최대 2개 기준 한 노드에 3개가 될 경우)
가운데 숫자를 부모 노드로 올려버립니다.(만약 부모 노드가 없다면 가운데 숫자가 부모노드가 됩니다.)

이를 통해 기존의 크면 오른쪽, 작으면 왼쪽 자식노드로 보내는 단순한 트리 구조와 비교해보면 관점의 차이로 보아 부모 위치에서 자식 위치로 보내어 자식을 만드는 구조가 아닌, 자식 중 하나가 부모가 되는 것을 알 수 있었습니다.

기타

개인적으로 이 말이 가장 인상 깊었습니다.

이 말은 다른 개발 분야에서도 통하는 말일 것 같아서요!

그리고 요즘에 mysql workbench에서 data grip으로 바꿨는데 확실히 data grip이 쓰기 좋은 것 같습니다.

[mixxeo]

새롭게 알게 된 점

• 인덱스를 사용하면 검색의 양을 줄일 수는 있지만, 원본 테이블을 참조하는 과정에서 랜덤 I/O 발생
  • 일반적으로 전체 테이블의 20~25 퍼센트 이상을 조회하는 경우 풀테이블 스캔이 더 효율적이라고 알려져있다.
• 등호 조건은 앞으로, 범위 조건은 뒤로 정의해야한다.
  • 범위 조건 때문에 두번째 컬럼을 사용하지 못하는 경우 IN 절을 사용하면 해결되는 경우가 많다

기타

• 클러스터형 인덱스와 보조 인덱스
  • https://dev-jwblog.tistory.com/162
• Clustered Index
  Clustered Index는 테이블의 데이터를 물리적으로 정렬하여 저장하는 인덱스입니다.
  실제 데이터 행들이 인덱스 키 값의 순서대로 디스크에 저장됩니다.
  테이블당 1개만 생성할 수 있고 보통 Primary Key Index가 자동으로 생성됩니다.
• Secondary Index
  Secondary Index는 Clustered Index가 아닌 모든 인덱스를 말합니다. 실제 데이터와 별도로 존재합니다.
  리프 노드가 실제 데이터가 아닌 Primary Key 값을 가리킴
  데이터 접근 시 Secondary Index → Primary Key → 실제 데이터 순으로 조회

[sylee6529]

새롭게 알게 된 점

인덱스 손익분기점

• 전체 20-25 이상 조회해야하면 풀스캔이 더 효율적이므로, 조회하려는 데이터 양이 손익분기점을 넘어가면 인덱스 사용을 포기한다

랜덤IO

• 인덱스를 통해 여러 행에 접근하는 것은 여러 번에 랜덤IO를 유발할 수 있으므로 순차IO인 풀스캔이 더 빠를 수 있다

커버링인덱스

• 이 단어자체는 처음 들었는데 알게 모르게 잘 쓰이고 있었나보구나 생각하였다

복합인덱스

• 등호조건컬럼, 범위 조건 컬럼 순서로 구성하는 것이 최적화에 도움됨
• 범위가 한정적인 컬럼으로 조건을 건다면 IN로직을 고려해보자

기타

• 인덱스 잘못했다가 큰일이 난 남의 집 사례(..?)가 있는지 궁금해서 한번 찾아봤습니다 -> 보니까 index 걸어서 해결한건아니네요 풀스캔이 되려 나은 케이스라고 생각해주시면 될 것 같습니다
  mysql장애를 어떻게 해결했는가

[witch-factory]

대부분 DB 수업 때 들었던 기억이 나는 내용이고, 쿼리 옵티마이저에 대한 이야기 정도가 새로웠다.

• 옵티마이저가 인덱스 사용이 오히려 비효율이라고 판단하면 인덱스를 포기하고 풀 테이블 스캔을 할 수도 있다. 일반적으로 손익분기점은 full table scan의 악 20-25% 정도
• 커버링 인덱스 단어는 좀 멋있게 이름 붙였는데 사실 select쿼리 대상 컬럼이 전부 인덱스일 경우 커버링 인덱스인거..
• 값들의 고유성이 높아야 인덱스를 걸 때 좋다. 그래야 인덱스를 통해서 더욱 탐색 범위를 줄일 수 있기 때문이다.

우연히 검색하다가 새로 알게 된 것

count(*)와 count(id)는 다르다. count(*)는 완전히 모든 행을 세고, count(id)는 id가 null이 아닌 행만 센다. 만약 고유한 id 값을 세고 싶다면 count(distinct id) 사용. 단 이런 중복 제거는 당연히 성능 이슈가 있다. id에 유니크 인덱스가 걸려 있다면 빠르겠지만 유니크 인덱스가 걸려 있다면 distinct를 쓸 필요가 없다. https://www.reddit.com/r/SQL/comments/1bp0mtw/count_vs_countid_what_is_the_difference/

from을 굳이 테이블이나 서브쿼리, 뷰로 쓸 필요는 없다. 그냥 정적 값의 목록들에서도 from을 쓸 수 있다.

SELECT
    COUNT(*),
    COUNT(a),
    COUNT(DISTINCT a)
FROM
    (VALUES (0), (1), (2), (2), (NULL)) AS X(a)

궁금했던 점

• 강의가 벌써 끝나가는데, 강의를 듣다 보면 실무에서는 ~~한다, 라는 이야기가 계속 나오고 또 다른 여러 부트캠프의 마케팅?에서는 정말 실무중심의 어쭈구 하는 이야기가 많은데요. 사실 저는 실제로 회사에서 하는 것에 대해서 뭔가 엄청 다르다고 느끼지 못해서..다른 분들은 어떠신지 궁금하네요.

[SANGWOO-JOO]

1. 옵티마이저

   • 정의 : 쿼리 실행 계획을 수립하는 데이터베이스의 두뇌
   • 역할
     • 쿼리 실행 전 최적 계획 수립
       인덱스 사용 여부 자동 결정
       손익분기점 계산 및 적용
       비용 기반으로 현명한 선택

2. 인덱스 장단점

   [idx_items_price 인덱스]          [items 테이블]
   price    item_id(주소)
   ─────────────────────
   50000 ────→ 19                      1번 행
   60000 ────→ 6   ↓                   2번 행
   70000 ────→ 3   ↓ 랜덤 I/O           3번 행  ←┐
   80000 ────→ 14  ↓                   4번 행   │
   90000 ────→ 8   ↓                   5번 행   │
                                       6번 행  ←┤
                                       ...     │
                                       8번 행  ←┤
                                       14번 행 ←┤
                                       19번 행 ←┘
   

   • 장점: 필요한 데이터만 찾음
   • 단점: 여러 위치를 오가는 랜덤 I/O 발생

3. 복합 인덱스 대원칙

   1. 인덱스는 순서대로 사용하라! (왼쪽 접두어 규칙)
   2. 등호(=) 조건은 앞으로, 범위 조건(<, >)은 뒤로!
   3. 정렬(ORDER BY)도 인덱스 순서를 따르라!

4. 커버링 인덱스

   • 정의 : 랜덤 I/O를 제거하기 위해 탄생 → 성능 향상

     • 인덱스 스캔 후 테이블 랜덤 I/O가 병목이었기에, 테이블 접근 자체를 없애기 위해 커버링 인덱스가 탄생
     1. WHERE 절의 등호(=) 조건
     2. ORDER BY 컬럼
     3. GROUP BY 컬럼

[gusdn7142]

주요내용 정리

1. 옵티마이저
   • 가장 비용이 적게 드는 효율적인 방법을 선택해 쿼리를 실행
   • 인덱스가 존재해도 풀 테이블 스캔이 선택될수 있음 (인덱스의 손익분기점 : 25%이상)

2. 커버링 인덱스
   • 쿼리에 필요한 모든 컬럼을 포함하는 인덱스
   • 장점 : SELECT 성능 대폭 향상, 랜덤 I/O 제거
   • 단점 : 인덱스 크기 증가, INSERT/UPDATE/DELETE 성능 저하

3. 복합 인덱스
   • 두 개 이상의 컬럼을 묶어서 하나의 인덱스로 만드는 것
   • 사용 원칙
     • 인덱스를 순서대로 사용하기
     • 등호(=) 조건은 앞으로, 범위 조건(<, >)은 뒤로 보내기
     • 정렬(ORDER BY)도 인덱스 순서를 따르기

4. 인덱스 생성 가이드라인
   • WHERE절에서 자주 사용되는 칼럼
   • JOIN 외래키 칼럼
   • ORDER BY절에서 자주 사용되는 칼럼