Kimuksung
Kimuksung 안녕하세요. 분산처리에 관심이 많은 생각하는 주니어 Data Enginner입니다.

Index 알고 쓰자

Index 알고 쓰자

BTree 기준

Index

추가적인 저장공간(테이블 크기의 10% 정도)을 활용하여 더 빠르게 조회할 수 있는 방법을 만든다.

Clustered Index

  • 별도로 설정할 수 있는 값이 아닌 Mysql 설정
  • 효율을 위해 중복이 최대한 발생하지 않은 칼럼 지정
  • 우선 순위
    1. Auto Increments PK Column
    2. Unique Column
    3. Hidden Clustered Index Key를 만들어 사용

NonClustered Index

  • 우리가 흔히 아는 Table 생성 시 Index를 설정
  • Table 당 Index는 최대 65개의 Index를 가지고 있다. ⇒ 64개의 NonCluster Index가 설정 가능 + Cluster Index(1개)
  • Multi Column Index는 최대 16개의 칼럼이 사용
1
2
3
4
5
6
7
8

-- CREATE INDEX [인덱스명] ON [테이블명](컬럼1, 컬럼2, 컬럼3.......)
CREATE INDEX INDEX_NAME ON table(col1,col2); 

-- 컬럼 중복 X
CREATE[UNIQUE] INDEX INDEX_NAME ON table(col1,col2);

-- DROP INDEX [인덱스 명] 
DROP INDEX INDEX_NAME;
Index로 사용해야 할 칼럼 종류
  • index key 크기
  • b-tree의 level
  • selectivity = Cardinality 와 골고루 퍼져있는지
  • read raw 수
    • 100만개를 다 읽어야한다면? index가 필요 없다.
    • 전체 record 대비 20% 미만의 record를 read하는 경우 효율적
    • 전체 데이터의 5~10정도로 걸러져야 효율이 좋다고 한다.


Page
  • Mysql Index = BTree 사용
  • Page = 16KB
  • index = (key,value)로 value는 자식 노드의 주소 값이기에 고정이고, key의 크기에 따라 하나의 page에 들어갈 수 있는 index수가 결정된다. ( index key의 크기가 크다면 갯수가 줄어들어 비효율적 )

  • Tree 구조에서 최상단을 Root, 최하단을 Leaf, 중간의 Node들을 Branch Node라고 한다.

    page-1

  • 위 예시에서 아래 쿼리를 실행시킨다고 했을 때 동작하는 방식이다.
  • Btree의 Index를 탐색 후 Leaf Node가 Disk(file)를 가리켜 해당 Disk 주소를 읽는다.
1

select name from table where age = 22;
  • 그렇다면 name이 아닌 age를 조회한다면?
  • Btree Index 탐색 후 Leaf Node가 존재한다면 Disk를 갈 필요 없이 결과를 보여주면 된다.
1

select age from table where age = 22;

Insert Data
btreeinsert-1

  • Page의 최대 크기를 3개의 데이터라고 가정
  • 하나의 페이지에 3개의 데이터가 차있는 상태에서 23이라는 데이터를 넣어야 한다.
  • 이 경우에는 하나의 Page로 부족하기에 **Page Split**이 발생한다.
  • 홀수 = n/2+1 / 짝수 = n/2 번째 데이터를 상위 노드로 올린다.
  • 이와 반대로, 데이터가 삭제 시 Page를 하나로 병합하는 **Page Merge**가 발생한다.


Index Rebuild
  • Index 생성 후 insert/update/delete가 반복되다보면, 성능이 저하됩니다. Tree의 Level이 깊어지고 한쪽으로 치우치는 현상이 발생하는거죠.
  • 그렇기에, 주기적으로 index를 rebuild 해주는 작업이 필요합니다.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

-- ORACLE
SELECT 
	I.TABLESPACE_NAME,
	I.TABLE_NAME,
	I.INDEX_NAME, 
	I.BLEVEL,
  DECODE(SIGN(NVL(I.BLEVEL,99)-3),1,DECODE(NVL(I.BLEVEL,99),99,'?','Rebuild'),'Check') CNF
FROM   USER_INDEXES I
WHERE   I.BLEVEL > 4
ORDER BY I.BLEVEL DESC;

-- Mysql
SELECT
    TABLE_SCHEMA,
    TABLE_NAME,
    INDEX_NAME,
    COLUMN_NAME,
    CARDINALITY,
    SEQ_IN_INDEX,
    INDEX_TYPE,
    COMMENT
FROM
    INFORMATION_SCHEMA.STATISTICS
WHERE
    SEQ_IN_INDEX > 4;

1
2
3

-- ALTER INDEX [인덱스명] REBUILD;
ALTER INDEX INDEX_NAME REBUILD;
Index를 설정한다고 무조건 빨라질까?
  • 데이터 양이 적거나, Index가 비효율적(Index Scan이 테이블의 양고 비슷한 경우)으로 설정되어있다면 full scan이 빠르다.

  • 위 내용을 생각해보자. index를 타게 되면 무조건 Tree를 순회 후 Disk를 접근해야 한다. 데이터 양이 적다면? 바로 Disk에 접속해서 다 찾아보는게 좋다.

    data-index-1

Index가 많아지면, 조회 속도가 빨라질까?
  • 앞에서도 계속 반복 되듯이, Index가 많아지면 Tree를 N번 돌게 되는것과 같다. 그렇기에 너무 많은 Index는 오히려 성능저하


Index 사용 시 주의해야할 쿼리들
  • index range scan
    • index column을 기반으로 범위를 지정해서 쿼리를 날리는 경우

index full scan

  • multi column index에서 2번째 칼럼이 조건으로 사용된 경우
    • index = age, name
    1
2
3
4

      select 
      name
  from table
  where name = 'ㅇㅇ'

index skip scan

  • Mysql 8.0 부터 지원하는 기능
  • WHERE 조건절에 조건이 없는 인덱스의 선행 칼럼의 유니크한 값의 개수가 적어야함
  • 쿼리가 인덱스에 존재하는 칼럼만으로 처리가 가능해야함.
  • index = gender, age
  • age > 15 조건을 검색 후 gender 조건을 검색
  • https://wisdom-and-record.tistory.com/137
1
2
3
4
5
6
7

set optimizer_switch='skip_scan=off'

select 
	gender,
	age
from table
where age > 15
  • 조건절 변경
    • 연산 값
    • stored function
    • type
    1
2
3

      where date(column) = '2023-01-01'
    
  where column = function()

  • 부정형

    1
2
3
4

      not in
  <>
  not between
  is not null

  • 뒷부분 일치

    1

      like %test
  • count(*)
  • multi column에서 순서를 바꿔서 조회하는 경우 ( optimizer가 있다면 큰 상관 없다. )
    • index = age, name
    1
2
3
4

      select
      name
  from table
  where name = 'ㅇㅇ' and age = 15
  • multi column index는 순서도 중요하니 잘 설계해야한다.
  • Lock에도 영향을 줌으로 잘 설계해야한다.


참조
  • https://kyungyeon.dev/posts/66