인덱스
인덱스를 사용하면? -> SELECT출력 속도가 빨라진다, but 인덱스도 공간을 차지한다
클러스터형 인덱스:
- 기본키로 지정시 자동 생성
- 테이블에 1개만 가능(유사 영어사전 - 내용이 이미 정리되어 있으므로 별도의 찾아보기 자료 없음)
- 자동으로 생성된 인덱스는 key_name이 PRIMARY
- 인덱스가 따로 저장되는 건 아니고, 데이터가 정렬됨
보조 인덱스:
- 데이터에 대한 포인터를 가지는 인덱스 저장 공간 생김
- 데이터베이스의 공간을 차지하게 되므로, 꼭 필요한 열에만 사용함
- 데이터 순서 변경되지 않음
- key_name이 열 이름 (Unique로 지정시 자동으로 지정)
- 고유 키로 지정하면 자동 생성, 여러 개 가능(유사 뒷페이지 찾아보기)
보조 인덱스는 일반적인 책처럼 뒤에 찾아보기가 별도로 있는 것과 비슷하다.
SELECT가 아닌 데이터의 변경 작업이 자주 일어나면, 성능이 나빠질 수도 있다.
인덱스는 컬럼 단위에 생성된다. 하나의 열에는 하나의 인덱스를 생성할 수 있다.
하나의 열에 여러 인덱스를 생성하는 것도 가능하고, 여러 개의 열을 묶어서 하나의 인덱스를 생성할 수도 있다.하지만 일반적으로는 하나의 열에 하나의 인덱스를 만든다.
postgresql 콘솔에서 \d를 입력해보면, 인덱스를 확인할 수 있다.
내가 지정해준 적이 없음에도 pk를 지정했기 떄문에 자동으로 인덱스가 생겼음을 알 수 있다.
인덱스 검색 유형은 Btree로 되어 있다(이진검색트리)
mysql의 경우에는 show index from 테이블명;해서 조회할 수 있다.
고유 인덱스
고유 인덱스란 인덱스의 값이 중복되지 않는다는 의미이다. pk혹은 고유 키(unique)로 지정하면 값이 중복되지 않으므로 고유 인덱스가 생성된다. (고유 키도 인덱스가 자동으로 생성)
그 외에는 단순 인덱스가 된다.
클러스터형 인덱스
어떤 열을 기본 키로 지정하면 자동으로 알파벳 순서로 정렬된다.
클러스터형 인덱스는 데이터가 정렬되지만, 보조 인덱스는 정렬되지 않는다.
인덱스의 내부 작동
인덱스는 클러스터, 보조 모두 내부적으로 균형 트리 (Btree)로 만들어진다
루트 노드: 최상단 노드
리프 노드: 최하단 노드
중간 노드: 루트-리프 사이 노드
각 노드는 최소한의 저장 단위를 의미할 수 있음 (Mysql의 페이지, 16KByte)
데이터 조회시 몇 개의 페이지를 읽었느냐에 따라 효율성이 갈린다
인덱스는 SELECT의 속도를 향상시키지만, INSERT/DELETE/UPDATE작업시 성능이 나빠진다
=> '페이지 분할'때문에
데이터 변경시 새로운 페이지를 준비해서 데이터를 나눠야 하므로, 데이터 변경 작업이 더 오래 걸림
만약 abc, fhij, lmo가 들어간 각각의 페이지가 있을 때 g를 삽입한다고 하자.
알파벳 순서상 두 번째 페이지에 들어가야 하는데, 공간이 없으므로 새 페이지를 만든다.
abc, fgh, ij_, lmo와 같이 ij만 들어간 새 페이지가 생긴다.
이 때 루트 페이지에는 총 네 개의 리프 페이지를 넣을 수 있는데, 만약 또 페이지가 생긴다면 루트 페이지까지 하나 더 필요하다. 루트 페이지가 두 개가 되면, 이들은 중간 페이지가 되고
또 이 둘을 담는 하나의 루트 페이지가 생긴다.
이렇게 INSERT시 엄청나게 많은 작업이 일어날 수 있다.
클러스터형 인덱스 구조
- 각 리프 페이지는 데이터 페이지이므로 바로 접근 가능하다.
- 루트 페이지는 각 페이지의 인덱스로 지정된 열의 첫 번째 값과, 참조를 가지고(포인터) 만들어진다.
보조 인덱스 구조
보조 인덱스가 추가돼도 데이터의 순서는 변경되지 않는다.
다만 별도의 공간에 찾아보기와 같은 보조 인덱스가 만들어진다.
- 리프 페이지 != 데이터 페이지
- 리프 페이지는 데이터의 주소값이므로, 리프 페이지에서 찾은 주소로 접근한다.
- 데이터 페이지는 정렬되지 않는데, 리프 페이지에 있는 데이터의 인덱스가 설정된 열은 정렬되어 있다. 이 인덱스를 이진탐색으로 찾아가면서 주소를 찾고 접근하여 조회 시간을 단축하는 것
인덱스의 실제 사용
pk로 설정하면 클러스터형 인덱스가 생성되고, unique로 설정하면 보조 인덱스가 자동으로 생성된다. 이 외에도 직접 지정하여 인덱스 생성이 가능하다.
생성: CREATE INDEX문
제거: DROP INDEX문 (단, 자동 생성 인덱스는 DROP 불가)
한 테이블 내에 보조 인덱스와 클러스터형 인덱스가 둘 다 있을 때, 보조 인덱스부터 제거하는게 좋다. 클러스터형 인덱스를 제거하면 내부적으로 데이터가 재구성되기 때문이다.
index_length는 보조 인덱스의 길이
member는 보조 인덱스가 없다는 것이고, buy는 보조 인덱스가 1페이지 할당
(16KB = 16384Byte)
이제 저기서 member에 보조 인덱스를 만들어보자.
mysql> CREATE INDEX idx_member_addr ON member (addr);
pk값때문에 생긴 클러스터형 인덱스 말고도 보조 인덱스가 추가된것을 확인할 수 있다.
cardinality?
chatGPT에게 cardinality에 대해서 물어봤다.
cardinality란 인덱스에 저장된 고유한 값의 수를 나타내는 개념입니다. 즉, 해당 인덱스를 구성하는 열(Column)에서 중복되지 않은 값의 개수를 의미합니다.
cardinality 값은 인덱스의 효율성을 평가하는 데 중요한 지표 중 하나입니다.
cardinality 값이 높을수록 인덱스를 사용하여 데이터를 검색할 때 성능이 좋아집니다.
이는 인덱스가 데이터를 빠르게 검색할 수 있기 때문입니다.
반대로, cardinality 값이 낮을수록 인덱스를 사용한 검색 성능이 저하됩니다.
이는 중복되는 값이 많아 인덱스를 사용하여 데이터를 검색할 때 검색 범위가 크기 때문입니다.그럼 만약 not unique인 열에 데이터가 5개 있고, 이중 3개가 같은 값일 때
여기에 보조 인덱스를 걸어서 확인해보면
cardinality는 3인걸까?
궁금하니까 확인해보자.
우선 member에 생성한 보조 인덱스의 non_unique가 1이므로, 고유 보조 인덱스가 아니다. 즉 중복값을 허용한다.
그런데 cardinality가 1이다!?
이론적으로 생각해보면 00, 01, 공백까지 3이어야 하는데 왜 1일까?
-> 업데이트되지 않았던 것!
analyze table member; 해서 인덱스 정보를 업데이트하고 다시 확인했다.
예상대로 cardinality가 3이 된 것을 확인할 수 있다.
index_length도 확인해보면 16384(16KByte)가 되어 있다.
어떤 열에 중복 값을 삽입하려면 열 자체도 UNIQUE가 되어 있으면 안되고, 인덱스도 고유 인덱스가 생성되어 있으면 안 된다.
절대로 중복될 일이 없는 이메일, 학번, 주민번호 등의 열에만 UNIQUE옵션을 사용해서 인덱스를 생성하자.
중복된 데이터가 많은 열에 인덱스를 생성하는 것은 의미도 없고 성능에 악영향을 미친다!
쿼리 앞에 explain을 붙이면 쿼리 실행 계획을 확인할 수 있다.
인덱스가 붙지 않은 열은 full scan(type = ALL)방식으로 조회된다
그럼 인덱스가 붙은 addr를 가지고 조회해보자
addr키에 생성해둔 키를 가지고 조회되었다.
type이 ref라는건, 인덱스를 사용하여 데이터를 찾고, 그 다음 나머지 칼럼을 조회하는 방식으로 데이터를 걸렀다는 뜻
이번엔 where로 pk열을 필터링했다.
possible_key에 PRIMARY가 있음에도 불구하고, 인덱스를 사용하지 않았다.
type이 ALL이며, key와 key_length가 NULL이다.
왜 그런걸까?
왜 인덱스를 사용하지 않을까?
테이블에 저장된 레코드 수가 작기 때문일까?
10개라서 그런건가?
type: const - 쿼리에서 사용되는 인덱스의 유형이 const. PRIMARY KEY나 UNIQUE INDEX와 같은 유니크 인덱스를 검색할 때 사용한다.
key_len: 길이가 4byte인 int타입이라 4
ref: const
ref 필드는 인덱스의 사용 방식을 설명하는 type 필드와 함께 사용됩니다. type이 const일 때 ref는 const가 됩니다.
ref 필드가 const인 경우는 검색 조건에 사용된 값이 상수(constant)인 경우입니다.
이 경우 검색 대상이 되는 레코드가 하나뿐이기 때문에,
인덱스의 리프 노드에서 바로 해당 레코드를 찾아서 반환할 수 있습니다.
즉, ref가 const이라는 것은 인덱스를 사용하지 않고
검색 조건에 사용된 값으로 바로 레코드를 찾아낼 수 있다는 것을 의미합니다.
이 경우 검색 속도가 매우 빠르며,
일반적으로 레코드가 매우 적은 경우 발생할 수 있습니다.데이터를 100개 넣어보니 인덱스를 사용한다
검색 대상 레코드가 하나인 경우에는 인덱스를 사용하던지, 직접 데이터를 읽어오던지 성능 차이가 크지 않기에 인덱스를 쓸 때도 있고 아닐 때도 있다고 한다.
- 클러스터형 인덱스가 생성된 경우 데이터가 정렬되어 있으므로, 인덱스 블록을 절반씩 나누어 가면서 검색 대상 레코드를 찾아낸다.
- 클러스터형 인덱스가 없는 경우, 데이터가 정렬되어 있지 않기 때문에 검색 대상 레코드를 찾으려면 데이터를 순차적으로 읽어가면서 검색 대상 레코드를 찾아야 한다. (full scan)
인덱스를 사용하지 않는 경우
인덱스를 쓰지 않는 데에는 인덱스가 손상되었거나, 레코드가 적거나 등의 여러 이유가 있는데
내가 pk를 가지고 조회했을때 인덱스를 사용하지 않은 이유는 후자인듯하다.
복합 키로 인덱스 만들기
pk+일반 열로 만든 복합키와, 일반 열 두개로 만든 복합키와 공간 사용량이 같을까?
ALTER TABLE TEST ADD CONSTRAINT PK_TEST PRIMARY KEY (PKDATA, PKDATE);pk를 포함하지 않은 두 개의 열로 복합키를 만들었다.
이 경우 보조 인덱스가 자동으로 생성된다.
각 열에 대한 인덱스가 따로 생성되었음을 확인했다.
이 때 인덱스 블록에는 두 인덱스에 대한 정보가 함께 저장된다고 함
여러 개의 열을 가지고 복합키를 만드는데, 이 때
