sql -정리2

정리2

1.case문

1) 단순 case 문

Select
	order_id,user_id,product_id,quantity,status,
    CASE status
    	when 'pending' then '주문대기'
        when 'completed' then '결제완료'
        when 'shipped' then '배송'
        when 'cancelled' then '주문취소'
        Else '알수없음'
   End As status_korean 
   From orders;
                                

2) 검색 case

Select name, price,
	  CASE 
      	  When price >= 10000 then '고가'
          When price >= 3000 then '중가'
          Else '저가'
     End As price_label 
     From products;
     

3) Case 그룹핑

• 패턴1) count + case
  ex) count (case when status = 'complted' then 1 end)
• 패터2) sum + case
  ex) sum(case when status = 'complted' then 1 else 0 end)

---

2. view

'실제 Data'를 가지지 않는, 가상의 table'
뷰는 위의 말처럼 Data를 가지지 않아 항상 최신의 table 기준으로 쿼리가 실행
즉! 뷰 DATA는 항상 최신 정보를 가짐

• 장점 : 편리성, 보안성, 논리적 독립성

• 단점 : 성능문제, 업데이트 제약

• 생성

create view 뷰이름 AS select 쿼리문

• 수정

alter view 뷰이름 As Select 쿼리문;

• 삭제

drop view 뷰이름;

---

3. Index

왜 중요하나?
1. 인덱스가 없는 table의 select 는 기본적으로 "Full Table Scan"
-> 그렇기에 ㄱ.인덱스를 활용하거나 ㄴ. 실행 계획을 확인 해야한다.

• 인덱스는 기본적으로 지정된 컬럼과 해당 값을 가진 실제 Data의 행위치를 한쌍으로 가진다.

• 인덱스 내부 Data는 항상 정렬된 상태를 유지

• 인덱스는 Create, Show, Drop를 사용한다.

CREATE INDEX idx_orders_status
ON orders (status);

SHOW Index From orders;

Drop Inex idx_orders_status on orders;

// Select문이 index를 참조하는지 알고 싶다면
// Explain SELECT 쿼리문~ ; 

2. 인덱스와 정렬

주요 논점: '이미 정렬된 인덱스를 활용해, order by 작업 성능을 개선'
-> 별도 정렬 없이, 이미 정렬된 index를 순서대로 읽기만 하면 매우 빠르게 동작. 그리고 DB는 별도의 fileSort를 생략가능

• 1) 옵티마이저와 Index 선택
  • 옵티마이저(Optimizer) : 쿼리를 실행하기 전 여러 실행가능한 방법을 평가, 그 중 가장 효율적인 방법을 판단 후 실행
  • 만약 index 사용이 비효율적이라면 '풀테이블 스캔을 사용'
• 2) 옵티마이저가 인덱스 사용 여부를 확인하는 기준: 손익 분기점
  • 인덱스 비용 : 인덱스 탐색 비용 + 랜덤 IO(인덱스에서 찾은 주소를 접근하는 비용)
  • 풀테이블 스캔 비용 : 순차 IO(테이블을 순차적으로 읽는 비용)

일반적으로 전체 DATA의 20~25% 이상 조회하는 쿼리는 '풀테이블스캔'이 효율적이다

왜 랜덤 IO가 순차IO보다 느린가?
1. 순차 IO (Sequential I/O)

• 데이터가 연속된 위치에 있음
• 디스크 헤드가 한 번 위치 잡고 쭉 읽기/쓰기
• 👉 이동 최소화 → 빠름

2. 랜덤 IO (Random I/O)

• 데이터가 여기저기 흩어져 있음
• 매번 디스크 헤드가 다른 위치로 이동
• 👉 탐색(seek) + 회전 대기 → 엄청난 시간 낭비

3. HDD 와 SDD
   HDD는 헤드 이동 시간 (seek time) + 디스크 회전 대기 (rotational latency)

3. 커버링 인덱스

• 정의 : '쿼리에 필요한 모든 컬럼을 포함하는 인덱스'
  만약 Slect, Order by, Group by, When 에서 사용하는 컬럼을 갖고 있다면 원본 테이블에 다시 접근할 필요가 없고, 인덱스만으로 쿼리 처리가 가능.
  이는 랜덤 IO작업을 없애며, 성능을 비약적으로 높임!.
  
  
• 장단점(쓰기 읽기의 Trade Off)
  ㄱ. 장점 : Select 성능 향상, Count 성능 향상
  ㄴ. 단점 : 저장 공간을 차지, 쓰기 성능 저하

4. 복합 인덱스(다중 컬럼 인덱스)

• 정의 : '두개 이상의 컬럼을 묶어 하나의 인덱스'로

• 복합 인덱스에서 가장 중요한 규칙: 컬럼의 순서

  • 복합 인덱스는 첫 번째 컬럼을 기준으로 정렬된 상태에서 제 역할이 가능하다(왼쪽 접두어 규칙)
  • 등호 조건(=)은 앞으로, 범위 조건은(>,<)은 뒤로
  • 정렬은 인덱스 순서대로

5. 인덱스 설계 가이드 라인

• 핵심 원칙 : 카디널리티(해당 컬럼의 값에 대한 고유성)

  • 카디널리티가 높다 : 중복이 거의 없다
    ex) 주민번호, user_id, 주문번호

  • 카디널리티가 낮다 : 값 종류가 많지 않아, 중복이 높다
    ex) 성별, 상태값(Y/N), 국가코드

  • 즉. 중복이 많으면 인덱스로 걸러낼 수 있는 게 적어서 효과가 약함

1. Where 절에서 자주 사용되는 컬럼
2. Join의 연결 고리가 되는 컬럼(외래키) 
3. Order by 절에서 자주 사용되는 컬럼
 
 “많이 쓰이면서, 잘 걸러지는 컬럼에만 인덱스를 걸어라”