[Section5~8] UNION, CASE 문, VIew, 인덱스1

[gusdn7142]

아래 형식으로 공유할 내용을 남겨주세요~!

새롭게 알게 된 점
어려웠거나 궁금했던 점
업무와 관련된 내용
기타 (자유 내용)

[gusdn7142]

주요내용 정리

1. UNION
   • Select 결과 집합을 수직으로 결합, 중복인 행은 자동으로 제거
   • 언제 사용해야 하는가?
     • 고유한(중복 제거된) 값 목록이 필요할때
   • 사용시 주의사항
     • 1️⃣칼럼 개수 일치
     • 2️⃣데이터 타입 호환성
     • 3️⃣최종 결과 칼럼명은 첫번째 Select문의 칼럼명을 따름
     • 4️⃣ORDER BY는 UNION 연산의 가장 마지막 한번만 사용

2. UNION ALL
   • Select 결과 집합을 중복 제거 없이 수직으로 결합
   • 언제 사용해야 하는가?
     • 로그 데이터 통합
     • 과거 데이터와 현재 데이터 통합 등
   • UNION vs UNION ALL 동작과정

     단계	UNION	UNION ALL
     1단계	첫 번째 SELECT 실행	첫 번째 SELECT 실행
     2단계	⚠️임시 테이블 1개 생성	-
     3단계	결과를 임시 테이블에 저장	결과를 즉시 출력 시작
     4단계	두 번째 SELECT 실행	두 번째 SELECT 실행
     5단계	결과를 임시 테이블에 추가	결과를 이어서 출력
     6단계	⚠️임시 테이블 전체 정렬	-
     7단계	인접 행 비교하여 중복 확인	-
     8단계	⚠️중복 행 제거	-
     9단계	최종 결과 반환	완료

3. CASE문
   • 조건에 따라 다른 값을 조회하게 만드는 기능으로, SELECT, FROM, WHERE, ORDER BY, GROUP BY 절 어디에서든 사용 가능
   • 단순 CASE문 : 하나의 칼럼이나 표현식의 값에 따라 결과를 다르게 출력

     CASE 비교할_칼럼_혹은_표현식
         WHEN 값1 THEN 결과1
         WHEN 값2 THEN 결과2
         ...
         ELSE 기본결과
     END
     

   • 검색 CASE : 각 WHEN절에 독립적인 조건식을 사용해 결과를 출력

     CASE
         WHEN 조건1 THEN 결과1
         WHEN 조건2 THEN 결과2
         ...
         ELSE 기본결과
     END
     

   • 많이 사용되는 패턴
     • 패턴1) COUNT(CASE ...)

       COUNT(CASE WHEN status = 'CANCELLED' THEN 1 END)  -- ELSE일시 NULL 반환
       

     • 패턴2) SUM(CASE ...)

       SUM(CASE WHEN status = 'CANCELLED' THEN 1 ELSE 0 END)
       

       • SUM 패턴의 의미가 더 명확, 성능비교 연구의 관점은? (ex, 데이터 ROW 수, ...)

4. View
   • 실제 데이터를 가지고 있지 않은 '가상의 테이블'로, 기본적으로는 조회(읽기)용으로만 사용하는 것이 원칙
   • 동작원리 (추가예정)
   • 사용이유
     • 1️⃣편리성 : 복잡한 쿼리의 내부 로직을 몰라도 가상 테이블 역할로 사용 가능
     • 2️⃣보안성 : 필요한 데이터(칼럼)만 안전하게 노출 가능
   • 주의사항
     • 쿼리가 단순해도 시스텝에 큰 부하 발생 가능
     • 최적 실행계획을 찾기 어려움

5. Index

   • Clustered Index : PK Index (자동생성)
     

   • Non-Clusterd Index : FK Index (자동생성), UNIQUE Constraint (자동생성), 직접 생성한 인덱스
     

5-1) Clustered Index vs Non-Clusterd Index 👇

구분	Clustered Index	Non-Clustered Index
데이터	실제 데이터 ∈ Leaf Node	인덱스 Column과 PK 포인터 ∈ Leaf Node (Mysql InnoDB 기준) (PK 포인터 -> 실제 테이블 데이터)
정렬	물리적 정렬	논리적 정렬
개수	테이블당 1개	여러 개 가능
범위 검색	매우 빠름	상대적 느림
INSERT 비용	높음 (재정렬)	낮음
공간	추가 공간 불필요	별도 공간 필요

5-2) EXPLAIN 타입에 따른 스캔 방식 비교 👇

Type	스캔 방식	사용 조건	사용 예시
ref	Non-Unique Index Lookup	Non-unique 인덱스 + = 연산자	WHERE category = 'book'
ALL	Full Table Scan	인덱스 없음 또는 활용 불가	WHERE name LIKE '%kim%'
range	Index Range Scan	범위 검색 (>, <, BETWEEN, IN)	WHERE age BETWEEN 20 AND 30
const	Unique Index Lookup	PRIMARY KEY/UNIQUE + 상수 비교	WHERE id = 1
eq_ref	Unique Index Lookup (JOIN)	JOIN에서 PRIMARY KEY/UNIQUE 사용	JOIN ON a.id = b.fk_id
index	Full Index Scan	인덱스 전체 스캔 (커버링 인덱스)	SELECT id FROM users

• 참고 : https://dev.mysql.com/doc/refman/8.4/en/explain-output.html#explain_type

6. 모바일 페이지 로딩속도에 따른 페이지 이탈률 연구 (2017)

   • 로딩시간 3초 : 사용자 인내심의 첫 번째 임계점

     로딩 시간	이탈률	비고
     3초	+32%	🟡 1초일때에 비해 10명 중 3명 이탈
     5초	+90%	🟠 1초일때에 비해 이탈률 약 2배
     6초	+106%	🔴 1초일때에 비해 이탈률 2배 이상 증가
     10초	+123%	🔴 이탈률 약 2.2배 이상

   • 출처 : Google의 공식 마케팅 리서치 플랫폼

[mixxeo]

오 저도 view에 저장되어있는 쿼리를 확인하는 명령어가 궁금했는데 SHOW CREATE VIEW가 있군용!

[gonasooc]

@mixxeo 네네 서버 개발자분들이 더 잘 아시겠지만 mysql 기준에서는 저렇게 쓰더라고요!

https://dev.mysql.com/doc/refman/8.0/en/show-create-view.html

[mixxeo]

실행계획을 보고 개선을 진행하는 경우에, db에 슬로우 쿼리 기준(예를 들어 실행 시간 1초)을 설정해두고 알림을 받아 개선하는 경우도 있고 복잡한 쿼리를 작성하게 되면 개발 중에 explain으로 개선하는 경우도 있습니다!

[gonasooc]

@ZetBe 아까 수정해놔서 리프레시하고 본문 링크 다시 보시면 될 거에요~

[gonasooc]

아니 지금 보니까 현우님 글에다가 제 정리 글을 댓글을 달아놨었네요 뭐 졸면서 썼나.. 일단 그냥 두겠습니다 😂

[mixxeo]

섹션7 VIEW(뷰테이블)

추가 내용

• VIEW 관련 기본 Postgresql 명령어(MySQL과 다른 점)

  • VIEW의 정의를 변경할 때: CREATE OR REPLACE VIEW view_name AS SELECT 쿼리문
  • VIEW의 이름을 변경할 때: ALTER VIEW view_name RENAME TO new_view_name;
  • VIEW의 정의를 확인할 때: SELECT pg_get_viewdef('view_name', true)

• 일반 VIEW vs MATERIALIZED VIEW(INDEXTED VIEW)

  • 일반 VIEW: 실행시 정의된 쿼리를 실행하여 결과를 반환
  • MATERIALIZED VIEW: 쿼리를 미리 실행하여 물리적으로 데이터를 가지고 있음
    • 추가적인 싱크 필요(REFRESH) -> cron job 같은 걸로 돌림
    • 통계 대시보드 등 실시간성을 보장하지 않아도 되는 쿼리의 경우 MView를 사용하면 성능상 이점을 얻을 수 있음

섹션8 인덱스1

새로 알게된 점

• Full Text Search / Full Text Index
  • 텍스트 검색이 필요한 서비스에서는 elasticsearch를 주로 사용했어서, FTS 개념을 잘 몰랐다
  • Postgresql에는 GiST, GIN 인덱스가 있음

업무와 관련된 내용

• 다른분들께서는 인덱스를 어떤 기준으로 정하고 생성하시는지 궁금합니다!

기타

• 테이블당 최대 인덱스 수
  • MySQL은 64개, Postgresql은 no hard-coded limit이라고 나와있음

[chordpli]

저는 인덱스 설정 기준을 아래 순서처럼 정하긴 합니다.

• Where 조건(또는 Join)으로 사용되는가, 사용 빈도가 잦은가?
  • FK 제약 조건을 걸지 않은 Join용 컬럼은 테이블 생성할 때부터 걸고 시작해요. (아마 Prsima에서, 제약 조건 없이 연관관계를 맺으려고 하면 안내 문자가 뜨는걸로 알고 있어여..!)
• 카디널리티가 높은가?
• 읽기보다 쓰기, 수정이 잦은가?
• 동등 비교인가? 범위 검색인가?
• 자료형 비교 등.. (최하위 고려사항)

복합 인덱스라면 인덱스 선언 순서에 따라 인덱스 적용이 달라지기 때문에, 공통적으로 사용되는 인덱스 컬럼인지도 고려하는 것 같습니다!

비슷하게 구성하시는지, 다른 기준이 더 있으신지 궁금합니다.

[chordpli]

새롭게 알게 된 점

CASE

조건부 집계를 통해 피봇 테이블을 만들 수 있다.

집계 함수: COUNT(), SUM(), AVG() 등 데이터를 요약하는 함수
조건문: CASE WHEN ~ THEN ~ END

CASE 문에 집계 함수를 넣어서 생성.

INDEX

Full-Text Search 에 대해 처음 들어봤습니다. 문자열 검색은 Elastic search을 사용한다라고만 알았거든요.

풀텍스트 인덱스는 긴 문장 전체를 대상으로 인덱싱을 하며, InnoDB와 MyISAM 테이블만 지원하며, char, varchar, text타입 문자만 인덱싱이 가능하다.

CREATE TABLE 테이블이름(
  …,
  열이름 데이터형식,
  …,
  FULLTEXT 인덱스이름 (열이름)
);
CREATE FULLTEXT INDEX 인덱스이름 ON 테이블이름 (열이름);
ALTER TABLE 테이블이름 ADD FULLTEXT (열이름);

3가지 방식으로 풀텍스트 인덱스를 생성할 수 있다.

텍스트 검색시, SELECT문의 WHERE절에 MATCH(), AGAINST()와 같은 메서드를 사용해야 전체 텍스트 인덱스가 적용되어 검색이 된다고 합니다.

출처: Inpa Dev

[ZetBe]

view, case, union

view를 통해 조회용 쿼리 생성

case: group by와 비슷해 보이지만, group by는 같은 값을 가진 행끼리 묶는 것이고, case는 분기를 나눠서 묶거나 컬럼에 명시함

union은 두 테이블 합치기 + 중복제거
union all은 두 테이블 합치기 중복포함

[witch-factory]

새로 알게 된 점

인덱스의 경우 DB 수업에서 들은 내용이 많아서 EXPLAIN 명령 이외에는 크게 신기한 게 없었습니다.
union 문법을 이번에 처음 알았고(혹은 배웠는데 까먹었거나) 존재 정도만 알고 있었던 case문의 조건부 집계, 뷰가 더 신선했습니다.
그리고 이전에 어떤 분이 자주 사용하는 쿼리들을 공유하냐고 물어보신 게 있었는데 view가 있다는 걸 알아서 신기했습니다.

프로젝트에서 postgre를 쓰면서 알게 된 건데 postgre에는 show tables;가 없고 SELECT datname FROM pg_database; 같은 걸 씁니다..

궁금한 점

인덱스 거는 기준
뷰가 실제로 많이 사용되는지?

[SANGWOO-JOO]

UNION, CASE, VIEW 내용 요약

기능	핵심 역할	주요 특징
UNION	여러 결과 집합을 수직으로 합침	중복 제거 필요 시 UNION, 아니면 UNION ALL (성능 우수)
CASE 문	조건부 로직으로 데이터 가공	분류, 그룹핑 기준 생성, 조건부 집계(피벗 테이블)
VIEW	복잡한 쿼리를 가상 테이블로 저장	편리성, 보안성, 유지보수성 향상 / 기본적으로 조회용

1. UNION 활용

   • 활성 라이선스와 만료된 라이선스 테이블을 하나로 통합

   -- 활성 라이선스 (active_licenses 테이블)
   SELECT 
       license_key,
       customer_name,
       issue_date,
       'ACTIVE' as status
   FROM active_licenses
   
   UNION
   
   -- 만료된 라이선스 (expired_licenses 테이블)
   SELECT 
       license_key,
       customer_name,
       issue_date,
       'EXPIRED' as status
   FROM expired_licenses
   
   ORDER BY issue_date DESC;

2. CASE 문

   • 대시보드에서 시스템 상태를 직관적으로 표시

   SELECT
       server_name,
       cpu_usage,
       memory_usage,
       CASE
           WHEN cpu_usage >= 90 THEN '위험'
           WHEN cpu_usage >= 70 THEN '경고'
           WHEN cpu_usage >= 50 THEN '주의'
           ELSE '정상'
       END AS cpu_status,
       CASE
           WHEN memory_usage >= 85 THEN '위험'
           WHEN memory_usage >= 70 THEN '경고'
           ELSE '정상'
       END AS memory_status
   FROM server_metrics
   	WHERE timestamp >= NOW() - INTERVAL 1 HOUR;

3. VIEW

   -- 카테고리별, 상태별 주문 현황 (50줄 이상의 복잡한 쿼리)
   SELECT
       p.category,
       COUNT(*) AS total_orders,
       SUM(CASE WHEN o.status = 'COMPLETED' THEN 1 ELSE 0 END) AS completed_count,
       SUM(CASE WHEN o.status = 'SHIPPED' THEN 1 ELSE 0 END) AS shipped_count,
       SUM(CASE WHEN o.status = 'PENDING' THEN 1 ELSE 0 END) AS pending_count
   FROM orders o
   JOIN products p ON o.product_id = p.product_id
   GROUP BY p.category;
   
   -- 1. 한 번만 뷰 생성
   CREATE VIEW v_order_status AS
   SELECT /* 위의 복잡한 쿼리 */;
   
   -- 2. 이후부터는 간단하게 사용
   SELECT * FROM v_order_status;

   • 복잡한 쿼리를 한줄로 단순화

인덱스는 이럴 때 사용!!

• WHERE 절에 자주 사용되는 컬럼
  • 조회 조건으로 빈번하게 검색하는 컬럼 (예: 사용자 ID, 주문번호, 이메일)
• JOIN 조건에 사용되는 컬럼
  • 외래키(FK) 컬럼에는 인덱스 필수
• ORDER BY, GROUP BY에 사용되는 컬럼
  • 정렬/집계 성능 향상
• 이메일, 사용자명 등 유니크 제약이 필요한 컬럼
  • UNIQUE INDEX로 중복 방지 + 조회 성능 향상

이럴 때는 사용하면 안 됨

• INSERT/UPDATE/DELETE가 빈번한 테이블
  • 인덱스 유지 비용 > 조회 성능 향상
• 카디널리티가 낮은 컬럼
  • 예) 성별(M/F), 상태 코드(true/false), 등급(1~5)
  • 선택도가 낮아 인덱스 효과 미미
• 작은 테이블 (수천 건 이하)
  • Full Table Scan이 더 빠를 수 있음

특수한 경우

• LIKE 검색
  • LIKE '%검색어%' → Full Text Search 사용
  • LIKE '검색어%' → 일반 인덱스로도 가능 (prefix 검색)
• 여러 컬럼을 함께 조회
  • 복합 인덱스 고려 (예: INDEX(user_id, created_at))
  • 순서 중요: 카디널리티 높은 컬럼을 앞에

그외 팁

• EXPLAIN 명령으로 인덱스 사용 여부 확인
• 슬로우 쿼리 로그 모니터링 후 인덱스 추가

[gonasooc]

오 역시 슬로우 쿼리 체크하는 방식이군요 팁 감사합니다!

[sylee6529]

새롭게 알게 된 점

• Explain 을 하면 어떻게 나오고, 이것을 어떻게 해석하는지 강의에서 떠먹어주셔서 좋았습니다.

궁금한점

• Material view 얼핏 들어만 봤는데 논리적인 개념인지 공식 지원인지 궁금해서 찾아봤습니다.
  그냥 테이블에 넣고 비동기적으로 크론잡 돌리는 것보다는 공식 지원하는게 더 빠른가? 라는 궁금점이 생겼습니다

  

기타

인덱스는 조회 외에는 성능이 좋을까? 에 대해...

인덱스는 데이터를 빠르게 찾기 위한 정렬된 구조이므로 조회 성능에 빠르게 하지만
그렇기때문에 데이터가 바뀔 때마다 인덱스도 같이 수정해야 한다는 점이 있어 쓰기 연산시 부하가 증가한다
특히 업데이트 시에는 (DB에 따라 다르겠지만) 삭제를 하고 insert 하는 경우도 있다 라고 알고 있습니다. 아마 다음 강의에 나오지 않을까 싶네요

[mixxeo]

오 refresh materialized view와 select-update(new table)의 차이점은 생각을 못해봤는데 감사합니다 ㅎㅎ

[gonasooc]

오 삭제를 하고 insert.. TMI 감사합니다ㅏㅏ