8장. 인덱스

[KKambi]

한비 (1/15)

RAID

Redundant Array of Inexpensive Disk

• 저렴한 디스크를 다수로 배열해놓는 것
• 기업용 데이터베이스 서버는 매우 큰 저장 용량이 필요 -> 여러 개의 디스크를 마치 하나로 사용할 수 있게 함
• OS가 봤을 때 하나처럼 사용
• 일종의 분산 저장이므로 디스크 간 중복 데이터를 저장하는 다양한 방법을 쓴다.
• 캐쉬 메모리가 있어 동기화 횟수를 줄여줄 수 있다. (버퍼 역할)

q

인덱스 스킵 스캔에서, 커버링 인덱스가 아니면 풀 테이블 스캔을 한 이유가

• 실제 레코드를 읽기 위해 랜덤I/O가 많이 발생해야 하기 때문에(비용 매우 증가), 차라리 풀 테이블 스캔을 택했다
• 이게 맞지??

[jjy0918]

B+Tree

• B-Tree와 달리 모든 노드 안에 데이터를 넣지 않음.
• leaf node 안에만 데이터를 넣음
• 리프 노드끼리 linked list로 연결되어 있음
• InnoDB는 기본적으로 B+Tree를 사용하는 것 같다?(https://dba.stackexchange.com/questions/204561/does-mysql-use-b-tree-btree-or-both)

B+Tree 장점

• 리프 노드를 제외하고 데이터를 담지 않기 때문에 메모리 확보가 쉬움
• 하나의 노드에 더 많은 Key를 담을 수 있기 때문에 트리의 높이는 더 낮아짐
• 풀 스캔 시 모든 데이터가 연결되어 있기 때문에 빠름.

주의사항

• PK를 너무 큰 값으로 설정하면, B+Tree의 효율성이 떨어질 수 있음.

  Using an excessively large PRIMARY KEY can cause the B+Tree to be much less efficient, however, since primary key values must be stored in the non-leaf pages

[KKambi]

https://telling-tungsten-9c8.notion.site/23-02-05-0f137fbe9cca4af7b2353279caaa0079

[jjy0918]

8.3.5 다중 컬럼(Multi-column) 인덱스

• 일반적인 인덱스는 1개의 컬럼만 포함하고 있다.
• 다중 컬럼 인덱스는 2개이상의 컬럼을 포함하는 인덱스로 Concatenated Index라고 불린다.
• 다중 컬럼 인덱스는 인덱스 설정 순서에 따라 달라진다.
  • 앞 순위 컬럼이 같은 경우에만 뒤 순서가 실행된다.
  • 앞 순서 컬럼의 우선순위가 낮다면, 뒤 순서간 우선순위가 높더라도 뒤에 존재하게 된다.

8.3.6 B-Tree 인덱스의 정렬 및 스캔 방향

• 인덱스는 설정에 따라 오름차순, 내림차순으로 정렬된다.
• 인덱스를 읽는 것은 저장된 형태와 관련이 없다.
  • 인덱스가 오름차순으로 정렬되어 있더라도 뒤에서부터 읽으면 내림차순으로 탐색할 수 있다.
• 즉, 인덱스를 어느 방향으로 읽을지는 쿼리에 따라 옵티마이저가 시릿간으로 만들어내는 실행 계획에 따라 결정된다.
• MySQL 5.7까지는 컬럼 단위로 정렬 순서를 혼합하여 인덱스를 구성할 수 없었다.
  • 컬럼 값에 -1을 곱하는 등 우회 방법을 사용해야만 했다.
• MySQL 8.0부터는 컬럼 단위 정렬 순서를 혼합하여 사용할 수 있다.
• MySQL 옵티마이저는 인덱스의 정렬 상태에 따라 정순, 역순으로 읽어서 오름차순, 내림차순을 사용하도록 실행 계획을 만들어낸다.
  • 오름차순으로 정렬되어 있을 때, 오름차순은 정순으로 읽고 내림차순은 역순으로 읽는다.
• InnoDB의 경우 정순으로 읽는 것이 역순으로 읽는 것 보다 빠르다.
  • InnoDB는 페이지 내에서 인덱스 레코드가 단방향으로만 연결된 구조를 가지기 때문에 역순이 느리다.
• 인덱스의 정렬은 자주 사용되는 정렬 순서로 생성하는 것이 좋다.

8.3.7 B-Tree 인덱스의 가용성과 효율성

• WHERE문을 통해 다중 인덱스를 적용한 컬럼들을 탐색하는 경우 인덱스 설정이 큰 영향을 미친다.
  • 다중 컬럼 인덱스의 경우 인덱스 설정 순서에 따라 탐색을 하기 때문에 조건에 따라 탐색 속도가 달라진다.
• 작업 범위를 결정하는 조건이 많은 경우, 최종 데이터는 작을 수 있지만, 쿼리 실행이 더 느려질 수 있다.
• 인덱스는 왼쪽 값이 중요하다.
  • 다중 인덱스의 경우 왼쪽 값(인덱스 설정이 앞순서)에 따라 인덱스가 영향이 없을 수 있다.
  • 문자열 같은 경우 %TEST와 같이 왼쪽 값을 알 수 없을 경우 인덱스가 영향이 없어진다.

가용성과 효율성 판단

• B-Tree의 경우 아래 조건의 경우 범위 결정 조건으로 사용할 수 없다. 즉, 아래 조건의 경우 인덱스를 사용하지 못한다는 것이다.

• NOT-EQUAL

  • WHERE column <> 'N'
  • WHERE column NOT IN (10, 11, 12)
  • WHERE column IS NOT NULL

• LIKE '%??'(뒷 부분 일치 문자열 패턴)

• 스토어드 함수나 다른 연산자로 인덱스 컬럼이 변형된 후 비교

  • WHERE SUBSTRING(column, 1, 1) = 'X'

• NOT-DETERMINISTIC 속성의 스토어드 함수가 비교 조건에 사용된 경우

  • WHERE column = deterministic_function()

• 데이터 타입이 서로 다른 비교

• 문자열 데이터 타입의 콜레이션이 다른 경우

• 효율적으로 인덱스를 사용할 수 있는 경우

  • 동등 비교(=, IN)
  • 크다 작다 형태(>, <)
  • LIKE로 좌측 일치 패턴

8.4 R-Tree 인덱스

• MySQL은 공간 인덱스(Spatail Index)라는 것을 제공한다.
  • 공간 인덱스는 R-Tree 인덱스 알고리즘을 이용하여 2차원 데이터를 인덱싱한다.
• MySQL 공간 확장을 이용하여 세 가지 기능을 사용할 수 있다.
  • 공간 데이터를 저장할 수 있는 데이터 타입
  • 공간 데이터의 검색을 위한 공간 인덱스(R-Tree 알고리즘)
  • 공간 데이터의 연산 함수(거리 또는 포함 관계의 처리)
• MySQL은 공간 정보의 저장 및 검색을 위해 여러 가지 기하하적 도형(Geometry) 정보를 관리할 수 있는 데이터 타입을 제공한다.
  • POINT, LINE, POLYGON, GEOMETRY 등
• R-Tree는 인덱스 정렬 시 MBR(Minimum Bounding Rectangle)이라는 크기로 전체적인 포함관계를 나누고 이를 바탕으로 인덱스를 형성하여 빠르게 탐색한다.
• R-Tree를 사용하고 싶다면 ST_Contains(), ST_Within() 함수를 사용하면 된다.

8.5 전문 검색 인덱스

• 전문(Full Text) 검색에는 B-Tree과 같은 인덱스를 사용할 수 없다.
• 전문 검색에서는 문서 본문의 내용에서 사용자가 검색하게 될 키워드를 분석해 낸 후 사용할 수 있게 인덱스로 구축한다.
• 전문 검색 인덱스는 문서 키워드 인덱싱 기법에 따라 단어 어근 분석과 n-gram 분석 알고리즘으로 구분할 수 있다.

어근 분석 알고리즘

• 어근 분석 알고리즘은 불용어(Stop Word) 처리와 어근 분석(Stemming) 과정을 거쳐 색인 작업이 수행된다.
• 불용어 처리를 통해 쓸모 없는 단어를 제거한다.
• 어근 분석은 단어의 뿌리인 원형을 찾는다.

n-gram 알고리즘

• n-gram은 단순히 키워드를 검색해내기 위한 인덱싱 알고리즘이다.
• n-gram은 단순히 본문을 무조건 몇 글자씩 잘라서 인덱싱하는 기법이다.

8.6 함수 기반 인덱스

• 일반적으로 인덱스는 컬럼의 값 일부 또는 전체에 대해서만 인덱싱한다.
• 때로는 컬럼의 값을 변형해서 인덱스를 구착해야 할 떄도 있다.
  • 이때 사용하는 것이 함수 기반 인덱스다.
  • MySQL 8.0 부터는 함수 기반 인덱스를 지원한다.

8.7 멀티 밸류 인덱스

8.8 클러스터링 인덱스

• 클러스터링 인덱스란 PK에서만 적용이 되며, PK 값이 비슷한 레코드끼리 묶어서 저장하는 것이다.
• PK가 변경되면 레코드의 물리적 위치도 변경된다.
• 클러스터링 인덱스는 클러스터링 테이블과 같은 의미를 가진다.
• 클러스터링 인덱스는 PK 기반 검색이 매우 빠르지만, 키 변경이 상대적으로 느리다.
• PK가 없는 경우 유니크 인덱스 중 첫 번째 인덱스를 클러스터링 키로 선택하거나 자동으로 유니크한 값을 가지도록 증가되는 컬럼을 내부적으로 추가 후 선택한다.

8.9 유니크 인덱스

• MySQL에서는 인덱스 없이 유니크 제약만 설정할 방법이 없다.
• 유니크 인덱스에서 NULL도 저장될 수 있다.(NULL은 값이 아니기 때문에 2개 이상 저장될 수 있다.)
• 유니크 인덱스의 경우 INSERT할 때 유니크한지 체크해야하기 때문에 세컨더리 인덱스보다 느리다.
• 성능을 위해 불필요하게 유니크 인덱스를 생성하는 것은 좋은 방법이 아니다.
• 불필요하게 유니크 인덱스와 일반 세컨더리 인덱스나 PK를 중복해서 설정하는 것은 좋지 않다.

8.10 외래키

• 외래키 설정 시 연관된 테이블의 컬럼에 인덱스가 설정된다.