[TIL] Index

📌 Index Scan VS Table Scan(Full Table Search)

Index Scan	인덱스를 이용하여 필요한 데이터만 조회 ➡️ 인덱스 ➡️ 데이터
Table Scan (Full Table Scan)	테이블의 모든 데이터를 처음부터 끝까지 훑음 인덱스 없이 전체 검색

 

➡️ Index Scan이 일반적으로 더 빠르다, 하지만 인덱스가 없거나 조건에 맞는 데이터가 많으면 Full Table Scan이 발생할 수 있다.

---

📌 Clustered Indext VS Non -Clustered(Secondary) Index

	Clustered Index	Non-Clustered Index
데이터 저장 방식	데이터 자체가 인덱스의 순서에 맞게 물리적으로 저장	인덱스 별도의 구조에 저장 데이터 위치를 참조
인덱스와 데이터 관계	인덱스 = 데이터	인덱스 ≠ 데이터 (별도의 pointer 통해 접근)
기본 키	주로 Primary Key → 자동 생성	별도로 생성 가능 (Unique Index 등)
I/O 성능	Insert, Update 시 페이지 분할, 재배치 → I/O 부담	데이터 재배치 영향 ❌
검색 속도	범위 검색에 빠름	랜덤 검색 추가 Index Lookup 필요

Clustered Index 특징

• PK or Unique inex에서 자동 생성된다. 
• 데이터 파일 자체가 인덱스다. ➡️ 디스크 저장 순서에 맞게 정렬됨
• MySql InnoDB엔진은 테이블마다 하나의 클러스터 인덱스만 존재한다. 
• B-Tree 구조를 사용
• 읽기 성능은 향상한다. 
  하지만, 쓰기는 I/O가 증가하기 때문에 성능이 저하된다. 
  ‼️ 데이터 삽입 시에 정렬된 구조를 유지해야하기 때문에, 페이지 분할과 데이터 이동이 발생하게 되면서
        디스크의 I/O가 증가하게 된다.
• 카디널리티가 높을 수록 인덱스의 효율성이 커진다. 아래의 명령어를 통해 인덱스 관한 정보를 확인할 수 있다.

SHOW INDEX FROM <테이블명>

 

위에 말했듯이 Insert와 Update 시 데이터 이동과 페이지 분할이 많이 발생하게 되면, 쓰기 성능이 저하된다. 
그렇기 때문에 잦은 쓰기일 경우 인덱스가 많아질 수록 느려질 수 있다. 

 

Read Lock : 업데이트 중 다른 트랜잭션이 동일 데이터 읽기 불가

 

참고

• MySQL 에서는 Clustered Index를 직접 생성이 불가능하다.
  ➡️ Primary Key 생성 시 자동 적용 된다. (오라클은 가능)
• ORM에서는 PK 기반 조회일 때 성능이 좋다.
  하지만 ORM 사용 시 JOIN 최적화가 어려워 복잡한 쿼리를 작성해야한다. 
• PK가 없는 경우도 있지만, 그럴경우 Full Table Scan이 발생하게 되어 성능이 저하된다. 

정리

• PK → Clustered Index 자동 적용 → 가능하면 PK 꼭 설정
• 카디널리티 높은 컬럼 인덱스 → 효율 ↑
• 조인, WHERE 절 자주 쓰는 컬럼 인덱스 고려
• 클러스터(Clustered) 인덱스는 데이터 파일과 직접 연관.
• 데이터 크기가 너무 크면 페이지 분할이 빈번하여 쓰기 성능 절하.
• 인덱스는 꼭 필요한 것만.  인덱스는 읽기 성능은 높이지만, 쓰기/업데이트 성능은 떨어뜨릴 수 있음
• 다중 컬럼 인덱스는 순서를 고려해서.
• 인덱스 추가 시 옵티마이저, 실행계획 확인 필수
• ORM은 단순 조회 성능엔 유리, 복잡한 조인/쿼리 최적화는 SQL 튜닝 필요
• 전체 테이블의 10~15% 이상을 읽을 경우 보조 인덱스 사용 안함!

---

📌 B-Tree 

 

• Node = Page (1개의 노드가 1개의 페이지에 저장됨)
• 기본 Page Size = 16KB (MySQL InnoDB 기준)
• Top-Down Search, Bottom-Up Writing
  ➡️ 루트 ➡️ 리프 노드까지 검색은 위에서 아래로 ➡️ 삽입/삭제 시 리프 노드부터 시작해 올라가며 수정

Page Split

• 한 페이지(노드) 50% 이상 차면 분할 발생
• 16KB → 8KB씩 쪼개어 새 페이지 생성
• Insert/Update 시 Split 많아지면 성능 저하 (I/O 증가)

---

 

📌 Sargable(Search ARGument ABLE) Query

Sargable 하다 -> 빠르다

 

범위를 좁게, 컬럼이 나오고, 인덱스가 걸려있을 경우 베스트임

in 과 exists를 통해 범위를 좁혀서 쓰는 것이 좋다.

=> 그냥 뭘 하든 범위를 줄이는 것이 좋다. 

- 서브 쿼리보단, 조인-> 조인 자체를 통해 외래키를 사용할 수 있기에 더 좋다. 
서브쿼리를 최대한 쓰지 않는 것이 좋다. (서브 쿼리는 느린 쿼리라 함 )

• where, order by, group by 등에는 가능한 index가 걸린 컬럼 사용.
• where 절에 함수, 연산, Like(시작 부분 %)문은 사거블하지 않다!
• between, like, 대소비교(>, < 등)는 범위가 크면 사거블하지 않다.
• or 연산자는 필터링의 반대 개념(로우수를 늘려가는)이므로 사거블이 아니다.
• offset이 길어지면 사거블하지 않는다.
• 범위 보다는 in 절을 사용하는 게 좋고, in 보다는 exists가 더 좋다.
• 꼭 필요한 경우가 아니라면 서브 쿼리보다는 조인(Join)을 사용하자.
• nullable은 카디널리티가 낮아지기 때문에 별로 안좋음

---

ORM(Object Relational Mapping):객체 지향 프로그래밍 언어와 관계형 데이터베이스의 호환되지 않는 유형 시스템 간에 데이터를 변환하는데 사용되는 프로그래밍 기술이다. (객체지향과 RDB 사이에서 변환해주는 기술)

 

MyBatis : JAVA 기반 ORM 프레임워크의 일종, JPA와는 다르게 쿼리를 직접 작성해야 한다.