#혼공학습단13기 #혼공S #SQL #MYSQL

🔖 Chapter 06 : 인덱스

<aside> 📔

인덱스 TIP

1. 인덱스는 잘 사용하면 SELECT 문의 출력 속도가 빨라지지만, 잘못 사용하면 성능이 나빠짐 (INSERT, UPDATE, DELETE를 자주 사용할 때)
2. 인덱스 검색 vs 전체 테이블 내 검색 중 무엇이 빠를지 MySQL이 알아서 판단함
3. Key_name이 Primary = 클러스터형 덱스 </aside>

<aside> 📔

인덱스를 효과적으로 사용하는 방법

1. 인덱스는 열 단위에 생성
2. WHERE 절에서 사용되는 열에 생성
3. 자주 사용하는 것에 생성
4. 데이터의 중복이 높은 열은 인덱스를 만들어도 효과 적음
5. 클러스터형 인덱스는 테이블당 하나만 생성 가능
6. 사용하지 않는 인덱스는 제거 </aside>

<aside> 📔

6장에서 사용된 SQL 문법

• CREATE INDEX - 인덱스 생성
• DROP INDEX - 인덱스 제거
• SHOW INDEX - 인덱스 확인
• SHOW TABLE STATUS - 인덱스 크기 확인
• ANALYZE TABLE - 테이블 분석, 처리 </aside>

📍 인덱스 개념을 파악하자

인덱스는 데이터를 빠르게 찾을 수 있도록 해주는 도구입니다.

1. 클러스터형 인덱스 - 자동 정렬 🙆🏻

<aside>

1. 클러스터형 인덱스(Clustered Index)란?

클러스터형 인덱스(Clustered Index)는 데이터가 실제 저장된 순서와 동일하게 정렬되는 인덱스입니다.

즉, 인덱스의 순서가 곧 데이터의 물리적 저장 순서와 같기 때문에 하나의 테이블에는 하나의 클러스터형 인덱스만 존재할 수 있습니다.

2. 특징

1. 데이터의 물리적 정렬
   • 클러스터형 인덱스를 생성하면, 해당 인덱스의 컬럼을 기준으로 실제 데이터가 정렬된 상태로 저장됩니다.
   • 예를 들어, ID 열에 클러스터형 인덱스를 설정하면, ID 값을 기준으로 정렬된 형태로 데이터가 저장됩니다.
2. 빠른 검색
   • 정렬된 상태로 저장되므로 범위 검색(BETWEEN, >, < 연산 등)에 유리합니다.
   • B-트리 구조를 사용하여 순차적인 탐색이 빠릅니다.
3. 기본키와의 관계
   • 대부분의 DBMS에서 PK를 생성하면 자동으로 클러스터형 인덱스가 설정됩니다.
   • 기본키가 아닌 다른 열에도 클러스터형 인덱스를 만들 수 있습니다. 단, 하나만 가능합니다.
4. INSERT/UPDATE/DELETE 시 성능 영향
   • INSERT: 데이터가 정렬된 상태로 저장되어야 하므로, 새로운 데이터를 삽입할 때 재정렬이 발생할 수 있습니다 → 성능 저하 가능
   • UPDATE: 클러스터형 인덱스의 컬럼이 변경되면 물리적인 데이터 위치가 변경될 수 있습니다.
   • DELETE: 삭제 시 빈 공간이 남을 수 있습니다

3. 예시

- 테이블 생성 및 클러스터형 인덱스 추가

CREATE TABLE member (
    mem_id CHAR(8) NOT NULL PRIMARY KEY,  -- 기본키가 자동으로 클러스터형 인덱스가 됨
    mem_name VARCHAR(10) NOT NULL,
    mem_number INT
);

• mem_id가 기본키이므로, 자동으로 클러스터형 인덱스가 생성됩니다.
• 데이터는 mem_id를 기준으로 정렬된 상태로 저장됩니다.

- 클러스터형 인덱스가 정렬을 유지하는 방식

INSERT INTO member (mem_id, mem_name, mem_number) VALUES ('BLK', 블랙핑크, 4);
INSERT INTO member (mem_id, mem_name, mem_number) VALUES ('TWC', 트와이스, 9);
INSERT INTO member (mem_id, mem_name, mem_number) VALUES ('OMY', 오마이걸, 7); --PK는 mem_id

실제 저장되는 데이터 순서: BLK → OMY → TWC

• PK인 mem_id를 기준으로 자동 정렬되어 저장됩니다.

4. 주의 사항

1. 자주 변경되는 컬럼에는 부적절
   • 클러스터형 인덱스의 컬럼 값이 바뀌면 데이터 정렬을 다시 해야 하므로 성능 저하가 발생합니다.
   • 이름(Name), 주소(Address)와 같이 자주 바뀌는 컬럼에는 사용하지 않는 것이 좋습니다.
2. INSERT 성능 저하 가능
   • 새 데이터를 삽입할 때, 중간에 삽입될 경우 정렬이 필요하므로 성능이 저하될 수 있습니다.
   • 특히, AUTO_INCREMENT를 사용하면 삽입이 항상 마지막에 이루어지므로 성능 저하를 줄일 수 있습니다.
3. 디스크 단편화 발생 가능
   • 삭제 연산이 많으면 빈 공간(Fragmentation)이 발생하여 성능이 저하될 수 있습니다.
   • 주기적으로 REORGANIZE 또는 REBUILD 작업을 수행하는 것이 좋습니다.

5. 요약

• 클러스터형 인덱스는 데이터 저장 순서를 결정하며, 범위 검색 성능이 뛰어납니다.
• 기본키(Primary Key)는 자동으로 클러스터형 인덱스로 설정됩니다.
• 삽입, 삭제, 업데이트가 빈번한 컬럼에는 사용을 신중히 고려해야 합니다.
• 테이블당 하나만 설정 가능하므로, 조회 성능이 중요한 컬럼에 적용하는 것이 좋습니다. </aside>

2. 보조 인덱스 - 자동 정렬 🙅🏻‍♀️

<aside>

1. 보조 인덱스(Secondary Index)란?

보조 인덱스(Secondary Index)는 테이블의 기본 데이터 정렬과 관계없이 별도로 생성되는 인덱스입니다.

즉, 클러스터형 인덱스 외에도 특정 컬럼에 대한 검색 속도를 높이기 위해 추가로 생성하는 인덱스입니다.

클러스터형 인덱스가 데이터의 물리적 정렬을 결정하는 반면, 보조 인덱스는 별도로 저장되며 물리적 데이터 정렬과 무관합니다.

2. 특징

1. 테이블당 여러 개 생성 가능
2. 빠른 검색(특히 특정 값 조회 시 유리)
3. 인덱스의 구조
4. INSERT/UPDATE/DELETE 시 성능 영향

3. 예시

테이블 생성 및 보조 인덱스 추가

CREATE TABLE member (
    mem_id CHAR(8)
    mem_name VARCHAR(10) NOT NULL,
    mem_number INT
); --고유키 없음

ALTER TABLE member
  ADD CONSTRAINT
  UNIQUE (mem_id)

• 코드 해석: 고유키가 없던 테이블에서 mem_id를 고유키로 설정합니다.
• 결과: 보조 인덱스를 생성해도, 데이터의 순서는 변경되지 않고 별도의 인덱스만 생성됩니다.
• 따라서 SELECT문을 활용해도, 출력 순서는 입력 순서 그대로 나옵니다.

4. 주의 사항

1. 보조 인덱스가 많을수록 성능 저하 가능
2. 범위 검색 성능 고려
3. 중복 값이 많을 경우 인덱스 효과 감소

5. 요약

• 보조 인덱스는 클러스터형 인덱스 외에도 검색 속도를 향상시키기 위해 추가로 생성하는 인덱스입니다. </aside>

🌳 인덱스의 내부 작동 - 균형 트리

<aside>

</aside>

📍 인덱스의 실제 사용 - 인덱스 관련 SQL 문법 정리

<aside>

</aside>

<aside>

</aside>

📝: p. 310 인덱스 생성하고 key_name이 PRIMARY로 출력된 결과 화면 캡처하기

SHOW INDEX FROM member;
SHOW TABLE STATUS LIKE 'member';
CREATE INDEX idx_member_addr
   ON member (addr);
SHOW INDEX FROM member;
ANALYZE TABLE member;
SHOW TABLE STATUS LIKE 'member';