(MySQL) – 인덱스(INDEX) 정리(작동 방식, 생성, 삭제, 모양)

색인이란 무엇입니까?

인덱스는 데이터베이스에서 데이터를 쿼리할 때 결과를 빠르게 추출하는 데 도움이 되는 “데이터베이스 개체”입니다. 사전에서 “찾아보는 것”과 같은 역할을 한다고 상상할 수 있습니다. 결국 인덱스에 따른 결과 값의 차이는 없으며 조회 성능 향상을 위해서만 사용됩니다.

실제로 인덱스 없이 DB를 운영하는 것은 현실적으로 불가능하다. 그러나 인덱스가 항상 좋은 것은 아니며 추가 DB 공간이 필요하고 데이터 변경 시 성능 저하가 발생할 수 있습니다. 따라서 필수 컬럼에 인덱스를 생성해야 합니다.

인덱스 기능

• 장점
  • 적절한 인덱스를 만들고 사용하면 쿼리 성능을 크게 향상시킬 수 있습니다.
    (검색 속도 향상 및 시스템 성능 향상)
• 불리
  • 인덱스는 대략 테이블의 크기입니다. 10% 추가 공간 요구 사항 (색인 페이지 왜냐하면)
  • SELECT 이외의 데이터 수정 작업(INSERT, UPDATE, DELETE)이 자주 발생하면 성능에 영향을 미칩니다(페이지 정렬 왜냐하면)

인덱스 유형

인덱스 유형은 클러스터형 인덱스그리고 보조 인덱스로 나누어진다 각 지수의 특징은 다음과 같습니다.

클러스터형 인덱스

• 테이블당 1개만 가능
• 클러스터링 인덱스는 기본 키로 지정된 열에 자동으로 생성됩니다.
• 실제로 저장된 데이터 균일한 측면 구조가지다
• 클러스터링 인덱스 기반 데이터 자동 정렬BE
  • 기본 키를 변경하면 클러스터링 인덱스가 변경되므로 변경된 기본 키를 기준으로 자동 정렬합니다.

보조 인덱스/비클러스터형 인덱스

• 하나의 테이블에 여러 설정
• 고유한 고유 컬럼이 키워드로 지정되면 보조 인덱스가 자동으로 생성됩니다.
• 실제로 저장된 데이터와 다른 데이터 집합입니다. 자체 페이지 구조가지다
• 클러스터링 인덱스와 달리 데이터가 정렬되지 않습니다.
• 색인 생성 다음 명령문을 사용하여 보조 인덱스를 직접 생성할 수 있습니다.

인덱스 작동 방식

클러스터링 인덱스와 보조 인덱스는 모두 내부용입니다. 균형 트리(B-트리)균형 잡힌 트리 구조에서 데이터가 저장되는 영역을 “노드”라고 합니다. 노드가 위치 루프 매듭, 중간 매듭, 리프 노드구성됩니다. MySQL에서 이러한 노드는 ‘도서 페이지‘.

이와 같이 균형 트리(B-트리)데이터를 검색할 때 매우 잘 작동합니다. 이러한 구조로 데이터를 검색할 때 검색할 데이터가 루트 페이지에 있는 빈도를 검색하고 해당 페이지를 확인하여 데이터를 추출한다. 이 프로세스 동안 읽은 페이지 수로 효율성을 측정할 수 있습니다.

MySQL은 페이지를 분할하여 데이터를 저장합니다. 페이지는 MySQL 기반 최소 16KB추가 페이지 데이터 저장이 필요합니다. 분할 페이지 작업페이지를 생성하고 데이터를 각 페이지로 분할합니다.

공유된 페이지

인덱스를 구성하면 데이터 수정 작업(INSERT, UPDATE, DELETE)의 성능이 저하될 수 있습니다. 특히 INSERT에 큰 영향을 미치기 때문에 분할 페이지 작업그런 일이 일어나기 때문에

페이지 분할은 데이터를 페이지 단위로 저장하는 프레임워크에서 추가 페이지가 필요할 때 새로운 페이지를 준비하여 데이터를 분할하는 작업을 말합니다. 빈번한 페이지 분할은 데이터베이스 성능에 상당한 영향을 미칠 수 있습니다.

클러스터형 인덱스 페이지 구조

클러스터형 인덱스 페이지다음과 같이 뿌리 – (가운데) – 잎 쪽구성됩니다. 클러스터형 인덱스 중단 기본 키로 정렬된 현재 데이터것이 가능하다. 클러스터형 인덱스 리프 노드곧 데이터 페이지(실제 데이터가 저장되는 곳) 보지마. 클러스터형 인덱스는 루트 노드에서 탐색할 페이지를 먼저 검색한 다음 해당 페이지에서 검색할 데이터를 찾아 검색 시간을 줄입니다.

보조 인덱스 페이지의 구조

보조 인덱스 페이지는 실제 데이터를 정렬하지 않습니다. 따라서 보조 인덱스를 설정해도 데이터 페이지에는 영향을 미치지 않으며 별도의 위치에 인덱스 페이지가 생성됩니다. 별도로 생성된 인덱스 페이지는 인덱스 컬럼 값을 기준으로 정렬되어 별도의 페이지가 생성됩니다.

리프 페이지는 실제 데이터가 저장되는 위치를 나타냅니다. 클러스터링 인덱스와 함께 사용하면 클러스터링 인덱스로 정렬된 데이터 페이지를 가리킵니다.

색인 사용

인덱스도 데이터베이스 개체입니다. 그러므로 만들다, 끄다 문을 사용하여 인덱스를 만들고 삭제할 수 있습니다.

• 자동으로 생성된 인덱스
• 클러스터형 인덱스
  • PC(우선순위)로 표시 / 독특함 + 0이 아님 제한
• 보조 인덱스
  • 고유한 제한

인덱스 생성

인덱스는 다음 SQL 문을 사용하여 만들 수 있습니다.

CREATE (UNIQUE) INDEX 인덱스_이름
	ON 테이블_이름 (열 이름) (ASC | DESC)

• 고유한 옵션은 중복될 수 없습니다 고유 인덱스생략하면 중복이 허용됩니다.
  • 고유한 옵션으로 색인을 생성하려면 색인을 생성하려는 열에 중복 값이 ​​없어야 합니다.
    (이후 삽입시에도 동일)
• ASC 또는 DESC를 사용하여 인덱스의 정렬 방향을 변경할 수 있습니다.
  • 나는 DESC로 거의 만들지 않습니다.

색인 제거

다음 SQL 문을 사용하여 인덱스를 제거할 수 있습니다.

DROP INDEX 인덱스_이름 ON 테이블_이름

• 드롭 인덱스 도어는 쉽게 제거할 수 있습니다.
• 기본 키, 고유 키(UNIQUE)다음을 사용하여 자동으로 생성된 인덱스 드롭 인덱스~처럼 제거할 수 없습니다.
  • 테이블 변경 기본 키나 고유 키를 문으로 제거하면 자동으로 생성된 인덱스도 제거할 수 있습니다.

인덱스를 제거할 때 보조 인덱스를 먼저 제거하는 것이 좋습니다.

인덱스가 많은 테이블이라도 사용하지 않는 인덱스는 과감하게 제거하는 것이 좋다.
(인덱스는 메모리를 사용하는 객체이기 때문에)

예

MySQL 워크벤치에 인덱스를 생성하고 결과를 확인해보자.

인덱스 생성

-- 단순 보조 인덱스 생성 
CREATE INDEX idx_member_number
	ON member (mem_number);

-- 고유 보조 인덱스 생성
CREATE UNIQUE INDEX idx_member_mem_name
	ON member (mem_name);
    
-- 테이블 구분 분석/처리를 통해 인덱스 적용
ANALYZE TABLE member;

멤버 테이블의 addr 열에서 단순 보조 인덱스그리고 mem_name 열에서 고유한 보조 인덱스설정되었습니다. 그런 다음 ANALYZE TABLE을 통해 인덱스를 적용해야 합니다.

SHOW INDEX FROM member;

SHOW INDEX 문을 이용하여 테이블에 적용된 INDEX를 확인할 수 있다.

현재 인덱스는 다음과 같이 클러스터형 인덱스 1개와 보조 인덱스 2개입니다.

색인으로 검색

SELECT mem_id, mem_name, addr
	FROM member
	WHERE mem_name="에이핑크";

위의 SQL 문은 WHERE 절에서 인덱스가 적용되는 열을 조건화합니다. 따라서 인덱스를 사용하여 데이터를 조회합니다. (행)

SELECT * FROM member
    WHERE mem_number >= 5;

위와 같이 인덱스는 데이터를 조회하기 위한 범위 검색에도 사용됩니다. (인덱스 영역 스캔)

인덱스를 사용하지 않는 경우

인덱스가 설정되어 있다고 해서 검색할 때 반드시 인덱스를 사용하는 것은 아닙니다. 몇 가지 예를 살펴보겠습니다.

누락된 인덱스 제약

SELECT * FROM member;

인덱스가 있는 컬럼을 조건으로 지정하지 않으면 테이블에 인덱스가 있어도 위와 같이 전체 테이블 스캔을 수행한다. (전체 테이블 검색)

전체 테이블 스캔이 더 빠른 경우

SELECT * FROM member
    WHERE mem_number >= 1;

멤버 테이블에 저장된 모든 숫자 값은 1보다 큽니다. 따라서 전체 데이터가 쿼리됩니다.

경우에 따라 인덱스가 있는 컬럼을 조건으로 지정한 경우에도 인덱스를 사용하여 데이터를 추출하는 것보다 전체 테이블을 검색하는 것이 더 빠를 수 있습니다. MySQL은 인덱스 또는 전체 테이블 순회를 사용할지 여부를 결정하고 이 방법을 사용하여 데이터를 조회합니다.

인덱싱된 열에 대한 작업을 수행할 때

-- 인덱스 사용 X
SELECT mem_name, mem_number
    FROM member
    WHERE LENGTH(addr + '1') >= 5;

-- 인덱스 사용 O
SELECT mem_name, mem_number
    FROM member
    WHERE LENGTH(addr) >= 5 - 1;

인덱스는 처리된 데이터를 저장하지 않습니다. 따라서 칼럼에 연산을 적용하면 인덱스를 이용한 데이터 검색이 불가능해진다. 따라서 컬럼에 직접적인 연산이 가해지지 않도록 사용하여야 한다.

색인 제거

다음 SQL 문을 사용하여 인덱스를 제거할 수 있습니다.

DROP INDEX idx_member_mem_name ON member;
DROP INDEX idx_member_mem_number ON member;

클러스터형 인덱스는 ALTER TABLE 문으로만 제거할 수 있습니다.

ALTER TABLE member
	DROP PRIMARY KEY;

색인 간단한 성능 비교

약 15000개의 데이터를 비교하여 인덱스에 따른 성능 차이를 확인해보자.

인덱스 X

먼저 인덱스 없이 데이터를 조회해 보겠습니다.

SELECT * FROM rental
	WHERE rental_date="2005-05-24 22:53:30";

전체 테이블 스캔을 통해 데이터를 검색했으며 검색 비용은 대략 1502.14보지마.

색인 또는

다음 SQL이 포함된 샘플 테이블에서 카디널리티높다 rental_date 열에서 idx_rental 인덱스를 설정하고 동일한 쿼리를 사용하여 데이터를 조회했습니다.

CREATE INDEX idx_rental
	ON rental (rental_date);    
analyze table rental;

SELECT * FROM rental
	WHERE rental_date="2005-05-24 22:53:30";

데이터는 단순 보조 인덱스를 사용하여 쿼리되며 검색 비용은 0.25입니다. 색인화되지 않은 검색으로 인덱스를 사용한 검색이 훨씬 저렴하다는 것을 알 수 있습니다.

물론 인덱스를 사용하더라도 여러 데이터 항목을 검색하는 경우 비용이 더 많이 들 수 있습니다.

SELECT * FROM rental
	WHERE rental_date between '2005-05-26 13:06:05' and '2005-05-30 00:00:00';

사이를 통해 특정 범위를 조건으로 설정한 경우 Index Range Scan을 사용하여 실행 계획이 변경되었음을 확인할 수 있습니다. 데이터 값을 하나만 가져오던 이전 쿼리에 비해 쿼리 비용도 증가했습니다.

임대 테이블의 모든 rental_date 값은 아래 SQL 조건보다 크거나 같습니다. 따라서 모든 데이터가 쿼리됩니다.

SELECT * FROM rental
	WHERE rental_date between '2000-05-24 00:00:00' and '2025-06-01 00:00:00';

WHERE 절은 열을 인덱스로 지정하지만 전체 테이블 스캔을 사용하여 데이터를 검색했습니다.

따라서 MySQL은 인덱스를 사용하는 것과 전체 테이블을 쿼리하는 것보다 더 나은 방법을 선택하고 실행합니다.

인덱스 사용에 대한 참고 사항

1. 카디널리티가 높은(낮은 중복성) 열에 인덱스를 배치해야 합니다.

SQL로 카디널리티용어 특정 열에 포함된 데이터 값의 고유성대표. 중복성이 높은 열에 인덱스를 배치하면 성능이 크게 향상되지 않으므로 중복성이 낮은 열에 인덱스를 배치해야 합니다.

2. WHERE 절에서 자주 사용되는 열에 대한 인덱스를 생성해야 합니다.

WHERE 절에 사용된 열에 따라 테이블이 스캔되는 방식이 변경됩니다. WHERE 절에서 거의 사용되지 않는 열에 인덱스를 지정하면 실제로 인덱스를 거의 사용하지 않는다는 의미이므로 다른 열에 인덱스를 지정하는 것이 좋습니다.

3. 자주 사용하지 않는 인덱스를 제거합니다.

인덱스는 테이블 데이터의 약 10%를 차지하는 데이터베이스 개체입니다. 또한 페이지 분할은 데이터가 수정될 때 성능에 영향을 미칠 수 있습니다. 따라서 인덱스를 잘 사용하지 않는다면 과감하게 제거하는 것이 좋습니다.

4. INSERT, UPDATE, DELETE와 같은 데이터 작업이 빈번한 테이블의 경우 인덱스를 고려해야 합니다.

데이터 수정 작업(INSERT, UPDATE, DELETE)이 자주 발생하면 페이지 분할 작업으로 인해 검색 성능을 제외한 다른 작업의 성능이 저하됩니다. 이와 같이 데이터가 자주 변경되는 테이블에서 한 가지 방법은 인덱스를 지정하지 않음으로써 조회 속도가 약간 느려지는 위험을 감수하는 것입니다.

참조

• SQL 혼자 배우기
• https://rebro.kr/167
• https://www.youtube.com/watch?v=edpYzFgHbqs