MySQL - 인덱스

인덱스를 왜 사용해야 되는지 알기 위해서는 디스크 읽기 방식에 대해 공부를 해야된다.
이 포스트는 인덱스에 관련된 내용이기 때문에 디스크 읽기 방식에 대해 간단하게 설명하고 넘어가겠다.

2개의 읽기 방식을 이해하면 좋은데 랜덤I/O와 순차I/0 방식이다.

• 랜덤I/O방식과 순차I/O 방식의 공통적인 행동이 있는데 플래터를 돌려서 읽어야 할 데이터가 저장된
• 위치로 디스크 헤더를 이동시킨 다음 데이터를 읽는다.

• 하지만 여기서 차이가 나는 부분은 순차I/O는 1번의 시스템 콜을 호출하고
• 랜덤I/O는 3번의 호출을 하였다.

이렇게 살펴보면 순차I/O를 사용하면 효율이 좋겠지만 기본적으로 랜덤I/O를 사용하고 있는 MySQL을
현실적 특성상 순차I/O로 변경하기는 어렵다.

그래서 랜덤I/O를 사용을 하면서 접근하는 ROW의 갯수를 줄이기 위하여 인덱스를 사용했다.

 

인덱스

---

인덱스는 테이블의 동작속도(조회)를 높여주는 자료구조이다. 인덱스로 데이터의 위치를 빠르게 찾아주는 역할이고 쉽게 예를 들어보면 책 뒷편에 '색인'이 인덱스의 역할과 동일하다고 볼 수 있다.

인덱스는 MySQL Index 파일에 저장되며, 인덱스가 설정되지 않았다면 Table Full Scan이 일어나 성능이 저하되거나 치명적인 장애가 발생한다.

조회속도는 빨라지지만 Write의 속도는 저하된다는 단점이 있다.
(Table의 index 색인 정보를 갱신하는 추가적인 비용 소요)
때문에 효율적인 인덱스 설계로 단점을 최대한 보완하는 방법을 생각해볼 수 있다.

 

 

🤨인덱스를 사용하면 왜 Read가 빠르지?

---

위 사진을 보면 인덱스를 걸게 되면 같은 값에 Pointer를 설정을 하게 된다. 그렇게 되면 Read의 효율이 상승한다. 왼쪽은 인덱스를 걸어 탐색을 하고 오른쪽은 인덱스가 없다고 생각하고 2022-4-17을 찾아보자 지나가던 철수가 봐도 왼쪽이 찾기 쉬운걸 알 수 있다.

 

1. 테이블의 기본 구조에 대해 설명

• 유저 테이블을 생성하여 10,000건의 더미 데이터를 삽입을 했다.

-- users 테이블 생성
CREATE TABLE users (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    age INT NOT NULL,
    PRIMARY KEY (id)
);

-- 10,000건의 더미 데이터 삽입
DELIMITER $$
DROP PROCEDURE IF EXISTS insert_users$$
CREATE PROCEDURE insert_users()
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i <= 10000 DO
        INSERT INTO users (name, age) VALUES (CONCAT('User', i), FLOOR(RAND() * 80) + 20);
        SET i = i + 1;
    END WHILE;
END$$
DELIMITER ;
CALL insert_users();

 

2. Age에 인덱스가 없는 나이가 80초과 Select쿼리를 실행을 하자

💡 실행 속도 측정하는 방법

SET profiling = 1;

-- 쿼리 실행
SELECT * FROM USERS WHERE AGE>80;

-- 실행 시간 확인
SHOW PROFILES;

 

3. Age에 인덱스를 생성 → Select 쿼리

💡 인덱스 생성 명령어

SHOW INDEX FROM 테이블;

 

4. 비교

빨간색: Age에 Index(x)

노란색:Age에 Index(O)

그런데 인덱스가 있는 부분이 시간이 더 길다.

 

5. 인덱스에 대해 더 알아보자

• 인덱스는 검색에 효율적으로 사용이 되지만 다음과 같은 경우에 더 효율적이지 않을 수 있다.

1. 테이블의 크기가 작은 경우
2. 인덱스를 사용하지 않는 쿼리
3. 인덱스를 사용하지 않는 WHERE절인 경우
4. 인덱스의 성능이 떨어지는 경우: 인덱스를 생성할 때 인덱스의 종류나 컬럼의 순서 등을 고려하지 않으면 성능이 떨어질 수 있습니다.

 

그래서 인덱스를 최대한 활용해서 WHERE절을 추가하고 order by를 이용했다.

 

인덱스를 더 효율적으로 사용하는 방식에는 3가지

---

1. 카디널리티

카디널리티 : 상대적인 수치로 특정 컬럼을 기준으로 중복도가 높으면 카디널리티가 낮고, 중복도가 낮으면 카디널리티가 높다고 표현한다.

한 컬럼이 갖고 있는 값의 중복 정도가 높을 수록 좋다.

예를 들어 설명하면 만약에 남여 , 주민번호 이렇게 두 조건이 있다고 생각을 해보자. 검색을 해야되는데 남여를 검색하면 2개를 통해 특정 값을 찾아야 하는데 이렇게 되면 1차 필터는 가능하지만 그 속에서 많은 데이터를 또 찾아야 한다.

하지만 주민번호는 값이 1개만 있기 때문에 원하는 값을 더 찾기 쉽다.

 

2. Update Frequency

• 인덱스의 업데이트 빈도라고 생각하면 된다. 만약에 update를 많이 하는 데이터에 인덱스를 추가하면 그만큼 인덱스도 수정을 해야된다. 그러면 성능에 대한 영향을 주게 된다. 그래서 인덱스를 선택을 할 경우에는 update에 대한 빈도가 적은 컬럼을 선택을 하는게 좋다. MySQL에는 인덱스 업데이트 최적화하고 성능에 대한 미치는 영향을 최소화 하기 위해서 몇가지 기능이 있는데 대표적으로 지연 인덱싱이 있다. 간단하게 설명해서 트랜잭션이 끝날 때까지 연기시켜 필요한 업데이트수를 줄여 성능을 향상을 시키는게 목적이다.

 

3.Size

인덱스의 크기는 데이터베이스의 성능에 큰 영향을 주는데 인덱스가 너무 크면 쿼리 속도가 느려지고 디스크의 용량을 많이 차지하고 너무 작으면 쿼리 속도를 효과적으로 향상시키지 못한다. 그래서 인덱스의 크기를 최적화하는 방법을 사용해야 되는데 쿼리 최적화에 필요한 열만 포함을 시키는게 대표적인 방법이다. (물론 다른 방법들도 많다)

 

B- TREE

---

 

 

B- TREE는 기본적으로 ROOT, BRANCH, LEAF 노드로 구성이 되어있다.

B-tree는 Binary search tree와 유사하지만, 한 노드 당 자식 노드가 2개 이상 가능하다.

 

B-TREE 탐색

 

B-TREE 추가(페이지 분할)

• 루트 노드는 최소 2개 이상의 주소를 가지고 깊이 A만큼의 주소를 가질 수 있다.
• 리프 노드는 깊이M의 M-1개의 주소를 가질 수 있다.
• 브랜치 노드는 차수D의 D/2보다 크고 D까지 가질 수 있습니다.

 

B-TREE 삭제(페이지 분할) → 성능