인덱스

진성대·2025년 3월 13일

mysql

SQL

목록 보기

8/10

RealMySQL 책을 읽고 노션에 정리한 글을 옮긴 것입니다.

인댁스는 쿼리의 성능을 향상하기 위해서 사용되는 필수 요소이다

1. 디스크 읽기 방식

랜덤 I/O, 순차 I/O 가있다.
1.1 하드 디스크 드라이브(HDD)와 솔리드 스테이트 드라이브(SDD)
- 기계식 장치 - 하드 디스크 드라이브, 전기적 장치 - 솔리드 스테이트 드라이브
- 성능 → ssd > hdd
  
  1.2 랜덤 I/O와 순차 I/O
- 랜덤 I/O 표현은 하드 디스크의 원판(플래터)를 돌려서 읽어야 할 데이터가 저장된 위치로 디스크 헤더를 이동시킨 다음 데이터를 읽는것을 의미한다. 순차 I/O 도 동일하다.
- 랜덤 I/O 는 3개의 데이터를 찾기 위해서 디스크의 헤더를 3번 이동시키고, 순차 I/O 는 3개의 데이터를 찾기위해 디스크의 헤더를 1번만 이동시킨다. 즉, 순차 I/O는 랜덤 I/O 보다 3배 더 빠른데 그 속도를 결정하는게 디스크 헤더의 움직임이다.
- 이러한 랜덤 I/O 의 속도를 보완하기 위해 InnoDB 의 버퍼 풀이 존재한다.
- SSD 는 원판이 없다고 해서 랜덤 I/O 랑 순차 I/O 차이가 안나는게 아니다. 순차 I/O 가 더 빠른다.
- 쿼리의 성능을 향상하기 위해서는 순차 I/O 를 발생시키도록 하는게 아닌 랜덤 I/O 의 횟수를 줄이도록 노력하는 것이다.

2. 인덱스란?

인덱스는 사전의 색인으로 볼 수 있고 색인옆에 있는 페이지(번호) 는 메모리 주소로 볼 수 있고 페이지를 따라 찾아간 곳에 있는 책 내용을 데이터로 볼 수 있다.
SortedList 는 인덱스와 같은 자료 구조이며, ArrayList 는 데이터 파일과 같은 자료 구조이다.
인덱스는 정렬되서 저장되기 때문에 insert 할때는 정렬해서 넣어야 하기 때문에 작업시간이 걸리지만 정렬되어 있기 때문에 찾을때는 순식간에 찾을 수 있다.
데이터 저장방식(알고리즘)으로는 대표적으로 B-Tree 인덱스와, Hash 인덱스 알고리즘이 있다.
Hash 인덱스 알고리즘은 범위 검색이나 일부 검색에서는 사용할 수 없고 주로 메모리 기반 데이터 베이스에서 자주 사용된다.

3. B-Tree 인덱스

Balanced - Tree 는 칼럼의 원래 값을 변형시키지 않고 인덱스 구조체 내에서 정렬된 상태로 있다.
전문 검색같은 특수한 요건의 검색이 아닌 경우 대부분 B-Tree 인덱스를 사용한다.
3.1 구조 및 특성
- 최상단 노드를 루트 노드, 가장 하위에 있는 노드를 리프노드, 루트노드와 리프 노드 사에이 있는 노드들을 브랜치 노드라고 한다.
- 루트 노드 페이지에 인덱스 키와 자식노드 주소가 Key, Value 형식으로 되어있으며
- 리프노드에는 Seconary Index (key) 와 프라이머리 key (value) 가 있는데 프라이머리 키를 통해서 다시 프라이머리로 구성된 b-tree(클러스터링 된 인덱스) 로 접근을 해서 리프노드에 있는 데이터를 가져오게 한다.
- MyISAM 은 리프노드 에서 인덱스 키와 레코드 주소 쌍으로 데이터 파일에 다이렉트로 접근해서 데이터를 찾을 수 있다.
- 조건 검색 값이 프라이머리 키이면 프라이머리 키로 구성된 b-tree 로 접근을 해서 리프노드에 있는 데이터를 가져오면 되지만 secondary key 를 통해서 접근하게 된다면 그 secondary key의 리프노드에 있는 프라이머리 키를 가져와서 다시 프라이머리 키로 구성된 b-tree로 접근을 해야한다는 것이다.
- 디스크에 클러스터링된 인덱스 (Primary Key B-Tree), 세컨더리 인덱스 (Secondary Key B-Tree) 두가지 경우로 저장된다.
  
  3.2 B-Tree 인덱스 키 추가 및 삭제
  
  3.2.1 인덱스 키 추가
- 데이터를 리프노드에 추가 할때 리프 노드의 용량이 꽉 차게 되면 split되서 처리된다. 리프 노드만 split 되는 것이 아닌 상위 브랜치 노드 까지 처리 범위가 넓어 지기 때문에 쓰기 작업은 상대적으로 비용이 많이 든다.
- PK , UK 중복 체크와 데이터 정렬을 위해서 B-Tree 에 즉시 추가한다.
- 세컨더리 인덱스(Secondary Index)는 체인지 버퍼에 의해서 지연 처리가 가능하다.
  - 세컨더리 인덱스의 리프 노드는 데이터 자체가 아닌 인덱스 키 값과 프라이머리 키 값만 저장하므로, 실제 데이터 삽입과 독립적으로 처리 가능하다.
  
  2. PK와 세컨더리 인덱스가 있는 테이블에서의 INSERT
  
  (1) 테이블 구조
```
CREATE TABLE employees (
    id INT PRIMARY KEY,          -- PK (즉시 처리)
    name VARCHAR(50),
    department VARCHAR(50),
    INDEX (department)           -- Secondary Index (지연 처리 가능)
);
```
  (2) INSERT 동작
```
INSERT INTO employees (id, name, department) VALUES (1, 'Alice', 'HR');
```
  과정
1. 프라이머리 키 B-Tree (즉시 처리):
  - id = 1이 프라이머리 키로, 즉시 클러스터링된 인덱스에 추가됩니다.
  - InnoDB는 id의 중복 여부를 검사한 뒤, id = 1, name = 'Alice', department = 'HR' 데이터를 클러스터링된 인덱스(B-Tree)에 저장합니다.
2. 세컨더리 인덱스 B-Tree (지연 처리 가능):
  - 세컨더리 인덱스 department는 삽입 시 바로 B-Tree에 추가되지 않을 수 있습니다.
  - InnoDB는 department = 'HR', id = 1의 값을 메모리에 유지한 뒤, 필요할 때 B-Tree에 배치(batch)로 추가합니다.
  - 배치 처리는 트랜잭션이 커밋될 때 또는 세컨더리 인덱스 플러시 작업 시 이루어질 수 있습니
  
  3.2.2 인덱스 키 삭제
- 해당 키 값이 저장된 B-Tree의 리프 노드를 찾아서 삭제 마크만 하면 된다. 마킹된 리프 노드는 방치하거나 재활용 된다. 삭제 작업도 디스크 쓰기 작업이기 때문에 I/O 가 들어가는 작업이다.
  
  3.2.3 인덱스 키 변경
- 인덱스 상의 키 값을 변경하는 것이 아닌 인덱스 값이 저장될 리프 노드를 다시 재설정해야하는 작업이다.
- B-Tree 키 값의 변경은 우선 인덱스 키 값을 삭제 후 새로운 키 값을 추가 하는 작업으로 DELETE → INSERT 작업으로 UPDATE 가 진행된다.
  
  3.2.4 인덱스 키 검색
- 100% 일치 또는 값의 앞부분(Left-most part)만 일치하는 경우에 사용할 수 있다. 부등호(”>, <”)비교 조건에서도 인덱스를 활용 할 수 잇지만, 인덱스를 구성하는 키 값의 뒷 부분만 검색하는 용도로는 인덱스를 사용할 수 없다.
- 인덱스의 키 값에 변형이 가해진 후 비교되는 경우에는 절대 B-Tree의 빠른 검색 기능을 사용할 수 없다.
- 인덱스를 제대로 설계 안하면 레코드를 읽을때 모든 레코드를 잠금하기 때문에 잘 설계 해야한다.
  
  3.3 B-Tree 인덱스 사용에 영향을 미치는 요소
  
  3.3.1 인덱스 키 값의 크기
- 자식 노드의 갯수는 데이터 페이지의 크기에 따라 달라진다. (가변적)
- 인덱스 페이지 16KB 에 Key 값 = (8 + 8) 바이트 + 자식 노드 주소 = 12 바이트
- 16 * 1024 / (16 + 12) = 585 개의 자식 노드를 가질 수 있다.
- index 키 값의 크기가 기존보다 커진다고 하면 16 * 1024 / (32 + 12) = 372 개가 저장되는데 만약 테이블의 크기가 500 개의 인덱스가 들어가는 크기라면 첫번째 경우는 디스크 한번 읽어오면 되지만 후자의 경우는 디스크를 2번 읽어오게 된다.
  
  3.3.2 B-Tree 깊이
- 인덱스의 키 값의 크기가 작으면 좋다
  
  3.3.3 선택도(기수성)
- 유니크한 값의 개수 = 기수성
1. 케이스 A : country 칼럼의 유니크한 값의 개수가 10개
2. 케이스 B : country 칼럼의 유니크한 값의 개수가 1000개
```
mysql> SELECT * FROM tb_test WHERE country = 'KOREA' AND city ='SEOUL'
```
- A 케이스의 경우에는 평균 1,000건
- B 케이스의 경우에는 평균 10건이 조회될 수 있다.
- 실제로 모든 조건을 만족하는 레코드가 1건만 있다면 케이스 A 인 경우에는 999건의 레코드를 불필요하게 읽은 것이고 케이스 B 인경우는 9건의 레코드를 불필요하게 읽은 것이다.
- 유니크한 값의 개수는 인덱스나 쿼리의 효율성에 큰 영향을 미친다.
  
  3.3.4 읽어야 하는 레코드의 건수
- 옵티마이저에서는 인덱스를 통해서 레코드를 읽는 것이 테이블에서 직접 레코드를 읽는것 보다 4 ~ 5배 정도 비용이 더든다고 판단한다.
- 인덱스를 통해 읽어야 할 레코드의 건수가 전체 테이블레코드의 20~ 25%를 넘어서면 인덱스를 이용하지 않고 테이블을 모두 직접 읽어서 필요한 레코드만 가려내는(필터링)방식으로 처리하는게 효율적이라 판단한다.
  
  3.4 B-Tree 인덱스를 통한 데이터 읽기
  
  3.4.1 인덱스 레인지 스캔
- 인덱스 접근 방식중에 가장 대표적인 접근방식이다.
- 범위를 스캔하는것 WHERE = 보다는 >, < BETWEEN 같이 범위를 탐색할때 사용된다.
- 인덱스 레인지 스캔으로 3개의 데이터를 데이터 레코드에서 가져와야 한다면 3번의 I/O 처리가 발생한다. 인덱스를 통해서 읽어야 할 데이터의 양이 테이블의 20 ~ 25 % 가 넘으면 테이블 레코드 자체를 읽는것이 더 효율적이다.
- 인덱스 레인지 스캔이 일어나는 순서
  1. 인덱스에서 조건을 만족하는 값이 저장된 위치를 찾는다. 이 과정을 인덱스 탐색(Index seek)이라고 한다.
  2. 1번에서 탐색된 위치부터 필요한 만큼 인덱스를 차례대로 쭉 읽는다. 이 과정을 인덱스 스캔(Index scan)이라고 한다. (1번과 2번 합쳐서 인덱스 스캔으로 통칭하기도 한다.)
  3. 2번에서 읽어 들인 인덱스 키와 레코드 주소를 이용해 레코드가 저장된 페이지를 가졌고, 최종 레코드를 읽어 온다.
  
  3.4.2 인덱스 풀 스캔
- 인덱스 레인지 스캔과 달리 인덱스의 처음부터 끝까지 모두 읽는 방식 (리프노드의 페이지)
- 인덱스 리프 노드의 제일 앞 또는 제일 뒤로 이동한 후, 인덱스의 리프노드를 연결하는 링크드 리스트를 따라서 처음부터 끝까지 스캔하는 방식을 인덱스 풀 스캔이라고 한다.
- 이 방식은 인덱스 레인지 스캔보다 빠르지 않지만 테이블 풀 스캔보다는 효율적이다. 인덱스에 포함된 칼럼만으로 쿼리를 처리할 수 있는 경우
- 테이블 employees 가 다음과 같이 구성
```
```sql
  CREATE TABLE employees (
      id INT PRIMARY KEY,
      name VARCHAR(50),
      salary INT,
      department_id INT,
      INDEX idx_salary_department (salary, department_id)
  );
  
```

```sql
  SELECT salary, department_id FROM employees ORDER BY salary;
```

**왜 인덱스 풀 스캔이 효율적인가?**

1. 쿼리 조건이 인덱스에 포함된 칼럼만으로 해결 가능
    - `salary` 와 `department_id` 는 복합 인덱스 `idx_salary_department` 에 포함되어 있습니다.
    - 따라서, 인덱스 풀 스캔만으로 데이터를 처리할 수 있으며, 테이블의 데이터 페이지를 읽을 필요가 없습니다.
2. 정렬 작업이 필요 없음
    - 인덱스는 항상 정렬된 상태를 유지하므로, 추가적인 정렬 작업 없이 결과를 반환할 수 있습니다.
3. 테이블 풀 스캔보다 디스크 I/O가 적음
- 인덱스 페이지는 테이블 데이터 페이지보다 작으므로, 읽어야 할 디스크 블록 수가 줄어들어 효율적입니다.
- 일반적으로 인덱스 풀 스캔 방식으로 읽는 경우는 “인덱스를 사용하지 못한다” 또는 “인덱스를 효율적으로 사용하지 못한다”라는 표현을 사용한다.
```
  3.4.3 루스 인덱스 스캔
- 인덱스 레인지 스캔, 인덱스 풀 스캔은 타이트(Tight)인덱스 스캔으로 분류한다.
- 느슨하게 또는 듬성듬성 인덱스를 읽는 것을 의미한다.
- GROUP BY 또는 집합 함수 가운데 MAX() 또는 MIN ()함수에 대해 최적화 하는 경우 사용한다.
- 테이블 및 인덱스 정보
  - 테이블 dept_emp 에는 dept_no 와 emp_no 라는 두 컬럼이 있습니다.
  - 인덱스 (dept_no, emp_no) 는 dept_no → emp_no 순서로 정렬된 상태이다.
  - 인덱스는 다음과 같이 정렬되어 있다.
```
dept_no | emp_no
----------------
d001    | 1001
d001    | 1002
d001    | 1003
d002    | 1004
d002    | 1005
d003    | 1006
d004    | 1007
d004    | 1008
```
```
SELECT dept_no, MIN(emp_no)
FROM dept_emp
WHERE dept_no BETWEEN 'd002' AND 'd004'
GROUP BY dept_no;
```
  1. dept_no 가 d002 에서 d004 사이에 있는 데이터만 검색
  2. 각 dept_no 그룹에서 emp_no 의 최소값 MIN(emp_no) 를 가져온다.
- 루스 인덱스 스캔은 이미 정렬된 상태임을 활용하여, 각 그룹에서 필요한 첫 번째 레코드만 읽는 방식이다.
  
  동작 과정:
1. 인덱스에서 범위 조건을 만족하는 첫 번째 dept_no로 이동:
  - 시작점: d002.
2. 해당 dept_no의 첫 번째 레코드만 읽음:
  - d002 | 1004 → emp_no = 1004를 선택.
  - 나머지 d002 그룹은 스킵하고 다음 그룹으로 이동.
3. 다음 dept_no의 첫 번째 레코드로 이동:
  - d003 | 1006 → emp_no = 1006을 선택.
  - 나머지 d003 그룹은 스킵하고 다음 그룹으로 이동.
4. 마지막 dept_no의 첫 번째 레코드로 이동:
  - d004 | 1007 → emp_no = 1007을 선택.
  - 나머지 d004 그룹은 스킵.
  
  3.4.4 인덱스 스킵 스캔
```
mysql> ALTER TABLE employees ADD INDEX ix_gender_birthdate(gender, birth_date);
```
- 이 인덱스를 사용하려면 WHERE 조건절에 gender 칼럼에 대한 비교 조건이 필수다.
```
mysql> SELECT * FROM employees WHERE gender='M' AND birth_date >= '1965-02-01'
```
- gender 칼럼과 birth_date 칼럼의 조건을 모두 가진 두 번째 쿼리는 인덱스를 효율적으로 사용할 수 있다.
- MySQL 8.0 버전 부터는 복합키에 gender 가 빠지더라도 옵티마이저가 gender 컬럼을 건너뛰어서 birth_date 칼럼만으로 검색이 가능하게 해주는 인덱스 스킵 스캔이 가능하도록 되었다.
- 이전 버전에서는 gender 가 없으면 인덱스 풀 스캔으로 데이터를 검색한다.
- 복합 인덱스 (gender, birth_date)에서 gender 조건 없이 Full Index Scan이 가능하다는 게 무슨 의미인가?
  - MySQL은 복합 인덱스의 리프 노드에 있는 데이터를 처음부터 끝까지 순차적으로 읽으며, 조건을 확인한다..
  - 복합 인덱스를 효율적으로 탐색하지는 않지만, 단순히 정렬된 데이터 구조로 활용한다.
  - 이는 gender가 없는 상태에서도 Full Index Scan으로 전체 데이터를 스캔하는 것을 의미한다.
- SET optimizer_switch=’skip_scan=on’ 일 경우
```
mysql> SELECT gender, birth_date FROM employees WHERE gender='M' AND 
birth_date >= '1965-02-01';
mysql> SELECT gender ,birth_date FROM employees WHERE gender ='F' AND 
birth_date >= '19965-02-01';
```
- 이런 형식으로 인덱스 스킨 스캔이 작동한다.
- 인덱스 스킵 스캔의 단점
  - WHERE 조건절에 조건이 없는 인덱스의 선행 칼럼의 유니크한 값의 개수가 적어야 한다.
```
A    | B
------------
M    | 1
M    | 2
F    | 3
F    | 4
F    | 5
```
```
SELECT * FROM table WHERE B = 3;
```
    이 쿼리에서 선행 컬럼 A 에 조건이 없더라도, MySQL 은 인덱스 스킵 스캔을 사용하여 다음 단계를 수행한다.
  1. A 의 유니크한 값 (M, F)를 하나씩 확인
  2. 각 A 값에 대해, B = 3 조건을 검색
  - 선행 컬럼의 유니크 값이 적을 경우
  1. 유니크 값 개수와 스캔 횟수의 관계
    - 선행 컬럼 A 의 유니크 값이 적으면, 옵티마이저는 모든 유니크 값을 순차적으로 탐색해야한다.
    - 예를 들어, A 의 유니크 값이 100 개라면, 각 값에 대한 후행 컬럼 B 를 검색하는 작업이 100번 반복된다.
  2. 유니크 값이 적으면 전체 탐색에 가까워짐
    - 선행 컬럼의 유니크 값이 너무 적으면, 사실상 테이블 전체를 스캔하는 것과 비슷한 수준으로 많은 스캔이 필요하다.
    - 예를 들어, A의 유니크 값이 2개(M, F ) 뿐이라면, 두 번의 범위 탐색만으로 전체 인덱스를 스캔해야 할 가능성이 높다.
- 쿼리가 인덱스에 존재하는 칼럼만으로 처리 가능해야함(커버링 인덱스)
  
  3.5 다중 칼럼(Multi-column) 인덱스
- 복합키로 보면됨
- 선행 키 → 후행 키 순으로 정렬된다.
- 다중 칼럼 인덱스에서 인덱스 내에서 각 칼럼의 위치(순서)가 상당이 중요하다.
  
  3.6 B-Tree 인덱스의 정렬 및 스캔 방향
- 인덱스 정렬은 오름차순, 내림차순
```
### 3.6.1 인덱스의 정렬

1. 인덱스 스캔 방향

```sql
  mysql> SELECT * FROM employees ORDER BY first_name DESC LIMIT 1;
```

- 쿼리의 인덱스에 ASC, DESC 를 통해서 읽기 방향을 인덱스의 오름차순, 내림차순으로 읽을 수 있다.
1. 내림차순 인덱스
- 정순 스캔보다 역순 스캔이 느린 이유 두가지
    1. 페이지 잠금이 인덱스 정순 스캔 (Forward index scan)에 적합한 구조
    2. 페이지 내에서 인덱스 레코드가 단방향으로만 연결된 구조
    - **단방향 연결된 구조에서 역순접근이 가능한 이유**
        
        InnoDB 의 B-Tree 구조는 리프 노드에서 단방향 링크드 리스트로 연결되어 있다.
        
        구조적 특징
        
        - 리프 노드의 레코드들은 다음 레코드로 이동할 수 있다로고 단방향으로 연결되어 있다.
            - 예 : 레코드 1 → 2 → 3- > … → N 처럼 오른쪽으로만 이동할 수 있는 구조
        - 역순 스캔의 경우, 부모 노드를 통해 다시 이전 리프 노드로 이동해야 한다.
            - 역순 스캔에서는 InnoDB가 현재 페이지에서 끝까지 도달한 후, 부모 노드로 돌아가 이전 페이지를 다시 검색하는 과정을 반복해야 한다.
        
        단방향 구조와 역순 스캔
        
        - 단방향 연결은 순차(정순) 스캔에 최적화되어 있으며, 역순 스캔은 효율성이 떨어질 수 밖에 없다.
        - 역순 접근은 추가적인 페이지 이동과 복잡한 탐색 과정이 필요하므로 정순 스캔보다 느리게 동작한다.
    - **페이지 잠금이 정순 스캔에 적합한 이유**
        
        정순 스캔과 페이지 잠금의 관계
        
        - InnoDB 에서 정순 스캔은 리프 노드의 왼쪽에서 오른쪽으로 순차적으로 진행된다.
        - 이 과정에서 InnoDB는 다음 페이지를 읽고 잠금하는 과정이 매우 효율적이다.
            - 한 페이지를 스캔한 뒤, 연속적인 페이지로 이동하기 때문에 I/O 및 잠금 관리가 최적화 된다.
        
        역순 스캔의 잠금 비효율성
        
        - 역순 스캔은 이전 페이지로 이동해야 하므로, 한 번 풀었던 잠금을 다시 설정하거나 다음 페이지를 거슬러 올라가는 과정을 반복해야 한다.
        - 이러한 방식은 페이지 잠금 및 해제 과정에서 오버헤드가 발생하며, 성능 저하로 이어진다.
```
  3.7 B-Tree 인덱스의 가용성과 효율성
  
  3.7.1 비교 조건의 종류와 효율성
- 인덱스에서 칼럼의 순서와, 칼럼에 사용된 동등 비교(”=”) 아니면 (”<”) or (”>”) 같은 범위 조건인지에 따라 인덱스 칼럼의 활용 조건이 달라진다.
```
mysql> SELECT * FROM dept_emp WHERE dept_no = 'd002' AND emp_no >= 10114;
```
- 위의 쿼리에서 두 가지 인덱스 생성되어있다고 했을때 차이점
- 케이스 A : INDEX (dept_no, emp_no)
  - “deptno=’d002’ AND emp_no = 10144” 인 레코드를 찾고, 그 이후에는 dept가 ‘d002’가 아닐때 까지 인덱스를 쭉 읽기만 하면된다.
  - 조건을 만족하는 레코드가 5건이면 5번의 비교작업만 하면 된다.
- 케이스 B : INDEX (emp_no, dept_no)
  - emp_no ≥ 10144 AND dept_no =’d002’ 레코드를 찾고 그 이후에 dept_no 가 ‘d002’인지 비교하는 과정을 거친다.
  - 레코드가 나머지 조건에 맞는지 비교하면서 취사 선택하는 작업을 필터링이라고 한다.
  - 검색 결과 5번의 레코드를 찾기 위해 7번의 비교 과정을 거쳣다.
- 케이스 A 인덱스에서 2번째 칼럼인 emp_no 는 비교작업을 좁히는데 도움을 주지만, 케이스 B 인덱스에서 2번째 칼럼인 dept_no는 비교 작업의 범위를 좁히는데 아무런 도움을 주지 못하고, 단지 쿼리 조건에 맞는지 검사 용도로만 사용 됐다.
  
  3.7.2 인덱스의 가용성
- B-Tree 인덱스는 왼쪽 값에 기준해서(Left-most) 오른쪽 값이 정렬돼 있다.
- 케이스 A : INDEX (first_name)
```
mysql> SELECT * FROM employees WHERE first_name LIKE '%mer';
```
  - 이 쿼리는 인덱스 레인지 스캔 방식으로 인덱스를 사용할 수 없다.
  - first_name 칼럼에 저장된 값의 왼쪽부터 한 글자씩 비교해 가면서 일치하는 레코드를 찾아야 하는데, 조건절에 주어진 상숫값 (’%mer’)에는 왼쪽 부분이 고정이 되어 있지 않기 때문이다.
- 케이스 B : INDEX (dept_no, emp_no)
```
```sql
  mysql> SELECT * FROM dept_emp WHERE emp_no >= 10144;
```

- 인덱스가 (dept_no, emp_no) 칼럼 순서대로 생성돼 있다면 인덱스의 선행 칼럼인 dept_no 조건 없이 emp_no 값으로만 검색하면 인덱스를 효율적으로 사용할 수 없다.
- dept_no 칼럼에 대해 먼저 정렬한 후, 다시 emp_no 칼럼값으로 정렬돼 있기 때문이다.
```
  3.7.3 가용성과 효율성 판단
- B-Tree 인덱스의 작업 범위 결정 조건으로 사용할 수 없는 경우 (체크 조건으로는 사용할 수 있다.)
  - NOT-EQUAL 로 비교된 경우(”<>”, “NOT IN”, “NOT BETWEEN”, “IS NOT NULL”)
  - LIKE ‘%??’ (앞부분이 아닌 뒷부분 일치) 형태로 문자열 패턴이 비교된 경우
  - 스토어드 함수나 다른 연산자로 인덱스 칼럼이 변형된 후 비교된 경우
  - NOT-DETERMINISTIC 속성의 스토어드 함수가 비교 조건에 사용된 경우
  - 데이터 타입이 서로 다른 비교(인덱스 칼럼의 타입을 변화해야 비교가 가능한 경우)
  - 문자열 데이터 타입의 콜레이션이 다른 경우
- 다중 칼럼으로 만들어진 인덱스의 사용 조건 여부
```
INDEX ix_test ( column_1, column_2, column_3, .., column_n)
```
  - 작업 범위 결정 조건으로 인덱스를 사용하지 못하는 경우
    - column_1 칼럼에 대한 조건이 없는 경우
    - column_1 칼럼의 비교 조건이 위의 인덱스 사용 불가 조건 중 하나인 경우
  - 작업 범위 결정 조건으로 인덱스를 사용하는 경우(i는 2보다 크고 n보다 작은 임의의 값을 의미)
    - column1 ~ column(i - 1) 칼럼까지 동등 비교 형태(”=” 또는 “IN”)로 비교
    - column_i 칼럼에 대해 다음 연산자 중 하나로 비교
      - 동등 비교(”=” 또는 “IN”)
      - 크다 작다 형태(”>” 또는 “<”)
      - LIKE 로 좌측 일치 패턴(LIKE ‘승환 %’)
  - column_1 이 있어야 인덱스 사용 가능한 이유는?
    - column_1 이 특정 값으로 제한되었기 때문에, column_3도 그 값이 있는 그룹 안에서만 검색하면 되기 때문이다.

4. R-Tree 인덱스

위치 기반 인덱스(2차원 공간 개념 값)
GPS나 지도 서비스를 내장하는 스마트 폰이 대중화 되면서 SNS 서비스나 GIS와 GPS 기번 서비스가 공간 확장(Spatial Extension)을 이용해서이러한 기능들을 구현한다.
4.1 구조 및 특성
- MySQL 데이터 타입은 POINT, LINE, POLYGON, GEOMETRY 으로 구성되어 있다.
- GEOMETRY(기하학적 도형) 타입은 슈퍼 타입으로, POINT 와 LINE, POLYGON 객체를 모두 저장할 수 있다.
- 최상위 레벨 : R1, R2
- 차상위 레벨 : R3, R4, R5, R6
- 최하위 레벨 : R7 ~ R14

4.2 R-Tree 인덱스의 용도

R-Tree는 MBR 정보를 이용해 B-Tree 형태로 인덱스를 구축하므로 Rectangle 의 ‘R’ 과 B-Tree의 ‘Tree’를 섞어서 R-Tree라는 이름이 붙여졌으며, 공간(Spatial) 인덱스라고도 한다.
일반적으로 WGS84(GPS) 기준의 위도, 경도 좌표 저장에 주로 사용한다.
R-Tree는 각 도형의 포함 관계를 이용해 만들어진 인덱스기 때문에 ST_Contains() 또는 ST_Within() 등과 같은 포함 관계를 비교하는 함수로 검색을 수행하는 경우에만 인덱스를 이용할 수 있다.
- 대표적으로 ‘현재 사용자의 위치로부터 반경 5km 이내의 음식점 검색’ 등과 같은 검색에 사용할 수 있다.

5. 전문 검색(Full Text search) 인덱스

문서 전체에 대한 분석과 검색을 위한 인덱식 알고리즘

5.1 인덱스 알고리즘

사용자가 자주 사용할 만한 키워드로 인덱스를 구축한다.

### 5.1.1 어근 분석 알고리즘

- 불용어(Stop Word)처리
    - 검색에서 별 가치가 없는 단어를 모두 필터링해서 제거하는 작업
- 어근 분석(Stemming)
    - 검색어로 선정된 단어의 뿌리인 원형을 찾는 작업

### 5.1.2 n-gram 알고리즘

- 몇 글자씩 잘라서 인덱싱하는 방법

### 5.1.3 불용어 변경 및 삭제

- MySQL 서버에 내장되어 있는 불용어 대신 사용자가 직접 불용어를 등록하는 방법을 권장한다.
- **전문 검색 인덱스의 불용어 처리 무시하는 방법**
    - MySQL 서버의 모든 전문 검색 인덱스에 대해 불용어를 완전히 제거하는 것
        - ft_stopword_file=’’공백
    - InnoDB 스토리지 엔진을 사용하는 테이블의 전문 검색 인덱스에 대해서만 불용어 처리 무시
        - innodb_ft_enable_stopword 시스템 변수 OFF
- **사용자 정의 불용어 사용**
    - 불용어 목록을 파일로 저장하고, MySQL 서버 설정 파일에서 파일의 경로를 다음과 같이 ft_stopword_file로 설정
        - ft_stopword_file=’/data/my_custom_stopword.txt’
    - InnoDB 스토리지 엔진을 사용하는 테이블 전문 검색 엔진에서만 사용할 수 있는데, 불용어의 목록을 테이블로 저장하는 방식이다.

5.2 전문 검색 인덱스의 가용성

쿼리 문장이 전문 검색을 위한 문법(MATCH … AGAINST …)을 사용

테이블이 전문 검색 대상 칼럼에 대해서 전문 인덱스 보유

mysql > CREATE TABLE tb_test (
	doc_id INT,
	doc_body TEXT,
	PRIMARY KEY (doc_id),
	FULLTEXT KEY fx_docbody (doc_body) WITH PARSER ngram
	) ENGINE=InnoDB;

mysql> SELECT * FROM tb_test WHERE MATCH (doc_body) AGAINST ('애플' IN BOOLEAN MODE);

6. 함수 기반 인덱스

인덱스는 칼럼의 값 일부(칼럼의 값 앞부분) 또는 전체에 대해서만 인덱스 생성이 허용되지만 칼럼의 값을 번형해서 만들어진 값에 대해서 인덱스를 해야할 때가 있는데 이때 함수를 사용해서 인덱스를 만든다.
인덱싱할 값을 계산하는 과정의 차이만 있을뿐, 실제로는 B-Tree 인덱스 구조와 동일하다.
6.1 가상 칼럼을 이용한 인덱스
```
mysql> CREATE TABLE user (
			user_id BIGINT,
			first_name VARCHAR (10),
			last_name VARCHAR(10),
			PRIMARY KEY (user_id)
			);
```
- fisrt_name 과 last_name 을 합쳐서 인덱싱 하기 위해서는 이전에는 full_name 칼럼을 만들고 업데이트를 했어야 했다면
- SQL 8.0 버전에서 부터는 가상 칼럼을 추가하는 방식으로 진행한다.
```
mysql> ALTER TABLE user ADD full_name VARCHAR(30) AS 
(CONCAT(first_name, ' ', last_name)) VIRTUAL, ADD INDEX ix_fullname (fullname);
```
- 가상 칼럼은 테이블에 새로운 칼럼을 추가하는 것과 같은 효과를 내기 때문에 실제 테이블의 구조가 변경된다는 단점이 있다.
  
  6.2 함수를 이용한 인덱스
```
mysql > CREATE TABLE user (
				user_id BIGINT,
				first_name VARCHAR(10),
				last_name VARCHAR(10),
				PRIMARY KEY (user_id),
				INDEX ix_fullname ((CONCAT(first_name, ' ', last_name)))
				);
```
- 함수를 직접 사용하는 인덱스는 테이블의 구조를 변경하지 않고 인덱스를 생성해 준다.
- 함수 기반 인덱스를 제대로 활용하려면 반드시 조건절에 함수 기반 인덱스에 명시된 표현식을 그대로 사용해야 한다.
```
mysql> SELECT * FROM user WHERE CONCAT(first_name, ' ', last_name)='Matt Lee';
```

7. 멀티 밸류(Multi-Value)인덱스

하나의 데이터 레코드가 여러 개의 키 값을 가질 수 있는 형태의 인덱스
JSON 배열의 개별 요소를 인덱싱 할 수 있도록 설계된 새로운 형태의 인덱스이다

멀티밸류 인덱스 사용법

테이블 생성 및 인덱스 추가

CREATE TABLE user (
    user_id BIGINT PRIMARY KEY,
    credit_scores JSON,
    INDEX idx_credit_scores ((CAST(credit_scores->'$.scores' AS UNSIGNED ARRAY)))
);

데이터 삽입

INSERT INTO user (user_id, credit_scores)
VALUES (1, '{"scores": [700, 750, 800]}'),
       (2, '{"scores": [600, 680]}'),
       (3, '{"scores": [720, 810]}');

기존 B-Tree 인덱스라면 JSON 배열에서 첫 번째 값만 인덱싱되었겠지만,

멀티밸류 인덱스에서는 [700, 750, 800] 각각이 인덱스에 개별적으로 저장됨.

8. 클러스터링 인덱스

테이블의 레코드를 프라이머리 키를 기준으로 비슷한 것들 끼리 묶어서 저장하는 형태이다.

InnoDB 스토리지 엔진에서만 지원하고 나머지 스토리지 엔진에서는 지원하지 않는다.

8.1 클러스터링 인덱스

프라이머리 키 값이 비슷한 레코드 끼리 묶어서 저장하는 것을 클러스터링 인덱스라고 한다.
프라이머리 키 값에 의해 레코드의 저장위치가 결정된다. 즉 프라이머리 키값이 변경되면 레코드 저장위치도 변경된다.
클러스터링 인덱스 = 클러스터링 테이블, 프라이머리 키 = 클러스터링 키
키 검색이 빠르지만, 저장이나 프라이머리 키 변경은 상대적으로 느리다.
클러스터링 테이블의 리프노드에 레코드 정보가 다 들어가져 있다.
프라이머리 키가 없는 테이블의 경우 프라이머리키의 설정은 어떻게 해야하나?
1. 프라이머리 키가 있으면 기본적으로 프라이머리 키를 클러스터링 키로 선택
2. NOT NULL 옵션의 유니크 인덱스 (UNIQUE INDEX) 중에서 첫 번째 인덱스를 클러스터링 키로 선택
3. 자동으로 유니크한 값을 가지도록 증가되는 칼럼을 내부적으로 추가한 후, 클러스터링 키로 선택
내부적으로 증가된 클러스터링 키는 실제 쿼리에서 사용할 수 없다.

8.2 세컨더리 인덱스에 미치는 영향
InnoDB 는 세컨더리 인덱스의 리프 노드에는 클러스터링 인덱스를 가지고 있다.

만약 리프노드에 클러스터링 인덱스가 아닌 레코드의 주소가 저장된다면, 레코드 주소가 변경될때마다. 세컨더리 인덱스의 리프노드의 값을 변경해야하는 불편함이 생긴다.

mysql> CREATE TABLE employees (
	emp_no INT NOT NULL,
	first_name VARCHAR (20) NOT NULL,
	PRIMARY KEY (emp_no),
	INDEX ix_firstname (first_name)
	);
	
mysql> SELECT * FROM employees WHERE first_name='Aamer';

MyISAM : ix_firstName 인덱스를 검색해서 레코드의 주소를 확인한 후, 레코드의 주소를 이용해 최종 레코드를 가져옴

InnoDB: ix_firstname 인덱스를 검색해 레코드의 프라이머리 키 값을 확인 후, 프라이머리 키 인덱스를 검색해서 최종 레코드를 가져옴

8.3 클러스터링 인덱스의 장점과 단점

장점	프라이머리 키(클러스터링 키)로 검색할 때 처리 성능이 매우 빠름 (특히, 프라이머리 키를 범위 검색하는 경우 매우 빠름)
	테이블의 모든 세컨더리 인덱스가 프라이머리 키를 가지고 있기 때문에 인덱스만으로 처리될 수 있는 경우가 많음(이를 커버링 인덱스라고 한다.)
단점	테이블의 모든 세컨더리 인덱스가 클러스터링 키를 갖기 떄문에 클러스터링 키 값의 크기가 클 경우 전체적으로 인덱스의 크기가 커진다.
	세컨더리 인덱스를 통해 검색할 때 프라이머리 키로 다시 한번 검색해야 하므로 처리 성능이 느리다.
	INSERT 할 때 프라이머리 키에 의해 레코드의 저장 위치가 결정되기 때문에 처리 성능이 느리다.
	프라이머리 키를 변경할 때 레코드를 DELETE 하고 INSERT 하는 작업이 필요하기 때문에 처리 성능이 느리다.

8.4 클러스터링 테이블 사용 시 주의사항

8.4.1 클러스터링 인덱스 키의 크기

8.4.2 프라이머리 키는 AUTO-INCREMENT 보다는 업무적인 칼럼으로 생성(가능한 경우)

8.4.3 프라이머리 키는 반드시 명시할 것

8.4.4 AUTO-INCREMENT 칼럼을 인조 식별자로 사용할 경우

9. 유니크 인덱스

9.1 유니크 인덱스와 일반 세컨더리 인덱스의 비교

9.1.1 인덱스 읽기

9.1.2 인덱스 쓰기

9.2 유니크 인덱스 사용시 주의사항

10. 외래키

10.1 자식 테이블의 변경이 대기하는 경우

자식 테이블의 외래 키 갈럼의 변경(INSERT, UPDATE)은 부모 테이블의 확인이 필요한데, 이 상태에서 부모 테이블의 해당 레코드가 쓰기 잠금이 걸려 있으면 해당 쓰기 잠금이 해제될 때까지 기다리게 된다.
자식 테이블의 외래키가 아닌 칼럼의 변경은 외래키로 인한 잠금 확장이 발생하지 않는다.

10.2 부모 테이블의 변경 작업이 대기하는 경우

진성대

주니어 개발자

이전 포스트

트랜잭션과 잠금

다음 포스트

인덱스

SQL

1. 디스크 읽기 방식

1.1 하드 디스크 드라이브(HDD)와 솔리드 스테이트 드라이브(SDD)

1.2 랜덤 I/O와 순차 I/O

2. 인덱스란?

3. B-Tree 인덱스

3.1 구조 및 특성

3.2 B-Tree 인덱스 키 추가 및 삭제

3.2.1 인덱스 키 추가

3.2.2 인덱스 키 삭제

3.2.3 인덱스 키 변경

3.2.4 인덱스 키 검색

3.3 B-Tree 인덱스 사용에 영향을 미치는 요소

3.3.1 인덱스 키 값의 크기

3.3.2 B-Tree 깊이

3.3.3 선택도(기수성)

3.3.4 읽어야 하는 레코드의 건수

3.4 B-Tree 인덱스를 통한 데이터 읽기

3.4.1 인덱스 레인지 스캔

3.4.2 인덱스 풀 스캔

3.4.3 루스 인덱스 스캔

3.4.4 인덱스 스킵 스캔

3.5 다중 칼럼(Multi-column) 인덱스

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

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

3.7.1 비교 조건의 종류와 효율성

3.7.2 인덱스의 가용성

3.7.3 가용성과 효율성 판단