📌 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와는 다르게 쿼리를 직접 작성해야 한다.
'SQL' 카테고리의 다른 글
| [MySQL] WITH 절 CTE (0) | 2025.05.07 |
|---|---|
| [MySQL] Routines (0) | 2025.05.06 |
| MySQL Architecture와 쿼리 실행 흐름 (0) | 2025.04.30 |