MSSQL - 중급교육 #3

 

* MSSQL 실행 순서 : 쿼리문 -> 컴파일 -> 통계정보 -> 실행계획 -> Seek(OLTP) or Scan(OLAP) 실행

* WHERE 첫 번째 조건은 무조건 "=" 조건이 되도록 

  . SARG 조건

  . WHERE 구문에 맞게 Index 생성 (오름차순, 내림차순도 동일하게 적용)

  . 사용자 정의 함수 X

  . 로컬 변수, 로컬 테이블 X (SP 파라미터로 적용 : SP에서 SP 호출 방식 적용)

  . OLTP : Hash Match 나오는 경우 튜닝 해야 함

  . OLAP : Hash Match (대용량, Index 사용 못함)

7장. 고급 쿼리 튜닝

. JOIN

  - Nested Loop Join (OLTP성 조인)

  - Hash Match Join, Merge Join (OLAP성 조인)

  - 외부 입력이 먼저 실행되고 내부 입력이 다음에 실행 된다

  - 2개의 테이블 중 선택도가 좋은 지에 따라 "외부 입력" 지정 (인덱스 사용 가능, select 되는 행 수가 적은 테이블)

  - "내부 입력"은 반드시 인덱스 사용 가능 (Index Seek)

  - "외부 입력" --> "검색 테이블" (선택도 좋아야 함)

  - "내부 입력" --> "조인 테이블" (인덱스 조인)

  - 조인 시 "내부 입력" 테이블의 컬럼은 변형하지 말아야 한다 (반드시 Index Seek 해야 성능이 좋아진다)

  - 집합 연산자는 거의 대부분이 중복 제거 된다고 생각하면 된다

  - JOIN 시 한번에 2 테이블 씩 처리 (예 10개 테이블 10! = 3,628,800 가지) 

    . join 테이블이 많을 수록 성능 저하의 요인 (실행 계획 수립 과정 적정?한 선택)

  * (핵심) 초기 테이블의 외부, 내부 입력 선정

  * 조인 분리 : 임시 테이블 사용 고려

  * 비정규화 고려

. OUTER JOIN

  - 불필요한 OUTER JOIN 사용하지 말자

. Hash Match Join ("외부 입력", "내부 입력" 그대로 유지)

  - 인덱스 유무 확인 필요

. Merge Join

  - 데이터가 "정렬"되어 있어야 한다

  - 클러스터 인덱스가 있어야 한다 또는 커버링 인덱스가 있어야 한다

  - Sort 하고 Merge 처리 : Sort Merge (가끔씩 발생) 

. Adaptive Join (적응형 조인)

  - 실행 계획에 "논리적 연산 : 내부 조인" 표시

  - 실행 계획에 재사용 부작용 --> 쿼리 분리 처리 (옵티마이저에서 자동 처리)

  - OPTION(USE HINT(...))

. Semi Join 과 Anti Semi Join

  - Semi Join (교집합)

    . WHERE aaa in (selesct aaa from ...)

  - Anti Semi Join (차집합)

    . WHERE aaa not in (selesct aaa from ...)

    . 모델링에서 고려해야 함

    . 가장 어려운 튜닝 조건

8장. Hint

. Join Hint

  - INNER {...} JOIN : 왼쪽 테이블이 "외부 입력"

  - OPTION (... JOIN)

  - 반드시 필요한 경우 사용

  - 사용 시 왜 사용하는 지에 대한 주석으로 설명을 추가 한다

9장. Subquery

(예)

  - and exists (select * from ....)

  - and 중분류 = (select top 1 중분류 from ....)

. Join <--> Group by

  - Group by 먼저 해야 할 경우는 조인(집계)열의 밀도가 높은 경우

  - join (....) b on a.aa = b.bb : (....) 안에 있는 쿼리는  a.aa = b.bb 조건을 내부적으로 미리 적용된다

. ROW_NUMBER()

  - 페이징 쿼리에 많이 사용

  - ver 2012 이상 : ORDER BY ... DESC OFFSET ... ROWS FETCH NEXT .. ROWS ONLY (사용 권장)

  - 검색 조건별 인덱스 생성해서 사용하는 것이 좋음

  - 첫번째 페이징 정적 쿼리문 작성, 이후 페이지는 동적 쿼리문 작성 (예)

. 차집합 구하기 (참조)

  - nullif() 함수 : null 값 활용 : select 1 / 0 <-- nullif()

   (예)

    . ISNULL(E_PLAYER_NAME,'HELLOWORLD') ISNULL값,      /*E_PLAYER_NAME이 널이면 HELLOWORLD 출력*/

    . NULLIF(TEAM_ID,'K10') K10이면NULL,                /*TEAM_ID가 K10이면 NULL*/

    . COALESCE(E_PLAYER_NAME,NICKNAME)           /*널 아닌 최초값 출력*/

. OLTP 기분 병렬 쿼리

  - 병렬 처리 퀴리는 OLAP성 쿼리

  - 병렬 처리 퀴리 2개 이상의 CPU를 사용한다

  - OPTION (MAXDOP 0) : CPU 사용수 (0 --> 병렬 전체 사용, 1 --> 직렬 처리, 4 --> 병렬 최대 4개)

. STRING_AGG (...) : "1,2,4,7,4,5,7,,...." 문자열 만드는 함수

  - ver 2017 이상

. 함수

  - 실제 실행 계획에서 "QueryTimeStats" 속성 아래 "UdfCpuTime", "UdfElapsedTime" 속성 참조

  - ver 2019 이상 함수 쿼리가 자동으로 튜닝 처리 해준다. (Scalar UDF InLine 옵션 적용 시)

  - "인라인 스칼라 Tsql Udf 포험 : True"

  - STRING_SPLIT() : ver 2016 이상

10장. 저장 프로시저 튜닝

. 권장 세션 옵션

  - SET LOCK_TIMEOUT

  - SET NOCOUNT ON

  - SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

. sp_ 접두사는 금기

11장. 성능 문제 및 튜닝 방안

. OPTION (RECOMPILE)

. OPTION (OPTIMIZE FOR (@aaa {'값'})) 

  

MSSQL - 중급교육 #2

 

4장. 열 통계(STATISTICS)

- 인덱스 생성 시 통계 정보 자동 생성 (FullScan)

  . (a,b,c,..) "a" 첫번째 필드 정보에 대해서만 통계 생성

- 비 인덱스 경우 쿼리 실행 시 통계 정보 자동 생성 (Sampling 일부 데이터로)

SELECT * 

FROM sys.stats st 

WHERE st.object_id = OBJECT_ID('myOrders')

-- 통계 내용 확인

DBCC SHOW_STATISTICS(myOrders, NC_myOrders_OrderID)

- 통계 보기

  . Steps (200) : 막대기 수, 최대 200개, null 추가 시 201 (수가 적을 경우 예상 행 수 추정에 문제)

  . All density : 열 밀도 (0에 가까울 수록 유니크한 값이 많다. 1에 가까울 수록 중복 데이터가 많다)

  . EQ_ROWS : RANGE_HI_KEY 본인 값에 해당 되는 레코드 수

  . DISTINCT_RANGE_ROWS : 앞에 있는 값에서 해당 값까지 생략된 레코드 수 (중복 제거)

  . RANGE_ROWS : 앞에 있는 값에서 해당 값까지 생략된 레코드 수

  . AVG_RANGE_RIWS = RANGE_ROWS / DISTINCT_RANGE_ROWS

  . 가정법 : 열 통계 정보에 없는 데이터는 어떻게 추정하느냐? (마지막 데이터 < ?)

    - ver 2014 이상 : 예상 행 수가 많은 것으로 처리 된다. (seek --> scan)

  . 행수 추정 방해 요소 : 로컬변수 DECLARE @....  : col <= @...  ,  col = @...

  . 평균 선택도 = Rows * All density  (col = @...)

  . 오늘 기준 데이터는 통계 정보에 없기 때문에 예상 행 수 N건으로 처리

  . 통계 정보 업데이트 재 생성(계산)해야 한다

  . 통계 정보 업데이트 생성을 매일 1~2번은 재 계산 할 수 있도록 한다. 

  . update statistics 통계명

  . alter database {db명} set auto_update_statistics {on|off}

  . BTS 예매 시 대용량 데이터 발생 시 최종 티켓 판매 수 값을 예상 행 수 추정이 어렵지만 ver 2014 이상 일 경우 N건 처리

  

5장. 잠금 (Lock) 및 차단 (Blocking)

  . update

    - select -> update 진행

    - 테이블 및 인덱스도 잠금 

. READ UNCOMMITTED ( = NOLOCK) : Dirty Read (수정 중인 데이터 읽기)

. 행 단위 잠금

. COM+ : 기본적으로 Serializable 격리 수준 부하 (대량 DeadLock 발생)

. new TransactionScope() : 분산 처리, 무겁다, 기본 Serializable 격리 수준 (변경해서 사용 할 것)

. NOLOCK : Dirty Read + Index Allocation Scan 발생 시 쿼리 결과 정합성 이슈 주의

. 정합성 이슈 : NOLOCK 사용 시 ORDER BY 생략하고 대량의 데이터 변경 시 데이터를 인식 못하거나 동일한 데이터가 나타나는 현상 발생

. Lock Escalation : 행 단위 lock이 많을 경우 테이블 lock 처리

6장. 기본 쿼리 튜닝

. SARG (Search ARGument, 검색인수)

. 정적 데이터는 케쉬 사용 권장

*** 프로시저문 (인덱스 적용이 가능하도록) 

WHERE (OrderID = @OrderID OR @OrderID IS NULL)

WHERE OrderID = COALESCE(@OrderID, OrderID)

OPTION (RECOMPILE) -- PEO

select *

from qqqq ....

INNER JOIN uf_aaa('1,2,3,4,3,5,2') -- distinct 안됨

WHERE aaa in (select val from uf_aaa('1,2,3,4,3,5,2')) -- 자동 distinct 됨

GROUP BY  (1) DIFF... (2) YEAR(... and MONTH(... (3) LEFT(...  : 성능이 좋은 순서

Hash Match : Index 사용 못하는 경우, 대용량 인 경우 (OLAP성 쿼리)

Hash Match는 OLTP성 쿼리에서 나오면 안됨 (인덱스 확인해야 함, 예상 행 수 문제,...)

UNION <--> OR <--> IN

... AND (CASE aa THEN 'C' THEN b.bbb ELSE c.ccc END) = 'aaaaa'

--> ... AND aa = b.bbb ... UNION ... AND aa = c.ccc ...

MSSQL - 중급교육 #1

 

. 인프런 온라인 교육 : https://www.inflearn.com

. 행 수 추정 (Cardinality Estimation)

  - 집합의 원수 (데이터 레코드)

  - 처리 예상 행 수 추정 (잘 할 수 있도록 하는 것이 쿼리 튜닝이다)

  - 밀도 : 열의 유일 값 

    . 1 / 열의 유일 값 개수 = ? (값이 1에 가까울 수록 중복 데이터가 많은 것)

  - 선택도 : 

    . 결과 행 수 / 테이블 행 수 = ? (값이 작을 수록 선택도가 좋다)

. OLTP (트랜젹션용 작업용 쿼리) : 빠른 응답 속도 목표, 단일 CPU 사용 충분, Index Seek

. OLAP (통계 및 대용량 작업 쿼리) : 전체 처리 성능 목표, 병렬 처리 필요, 하드웨어 성능, Index Scan

. 2014 버전 이상 사용 권장 (미만 버전 성능 버그가 많음)

. 내부 함수 사용 시 행 수 추정이 좋지 않은 경우가 많음

. 쿼리 튜닝 : 인덱스 튜닝, 쿼리 자체 튜닝, 리소스 대기 튜닝 (CUP, RAM, 네트워크, ...)

. SET STATISTICS PROFILE ON

. SET STATISTICS XML ON

. SET SHOWPLAN_XML ON

. 물리적(논리적) 연산자 : 논리적 연산자 --> (내부 조인), (Aggregate), ...

. 논리적 연산자는 최종 적용된 연산자 정보 표시

. "예상" 비용 : 좋은 쿼리 나쁜 쿼리라는 판단하는 것은 문제가 있다. 성능과는 무관하다.

. 예상 행 수가 다르면 예상 비용도 다르게 된다.

*** 실행 계획 (튜닝) ***

. 실제 행 수 <--> 예상 행 수 : 차이가 적어야 한다 (OLTP or OLAP 선택 조건)

  - 복합 인덱스 튜닝 부족

  - 열 통계 관련 문제

  - SARG 위반 등의 SQL 쿼리 자체 문제

  - 기타

. (예) IO 용량 = 논리적 일기 수 6 * 8K

. 쿼리 옵티마이저가 사용하는 내부 임시 테이블 : Worktable, Workfile (사용되지 않도록 해야 함)

. RID Lookup + Join (** 중요)

. 실제 읽기 --> 논리적 읽기로 생각하면 됨

. 인덱스 쓰는 손익분기 점 기준은 1% 내외 (예) 1.325% 이하 (고정 아님) 

  (Table Scan (Index Scan) --> Index Seek (Lookup))

. 클러스터 인덱스 컬럼의 필드 업데이트 할 경우 insert, delete 함께 발생

. 클러스터 인덱스 선택 기준 - 트랜잭션 성능을 위한 후보 열 (OLTP성 테이블의 열)

  - 등록, 수정, 삭제 등의 작업을 빠르게 하는 작업

. 주문일자

. 주문일자 + 순번

. Covering Index (Key Lookup이 발생하지 않는 경우)

. 넌클러스터 인덱스 생성 할 경우 (넌클러스터 인덱스 , 클러스터 인덱스 로 생성된다)

  (UnitPrice --> UnitPrice, OrderID, ProductID)

. Key Lookup + Join (넌클러스터 인덱스의 성능을 좌우한다)

. Index 또한 테이블이다 (일반 테이블의 종속 데이블이 된다)

. Index에 필요한 필드가 포함되면 Key Lookup이 발생하지 않는다.

. key columns : 정렬 대상

. non-key columns : 정렬 대상 아님, 필더링 대상 아님 : INCLUDE (non-key column1, ...)

  - 단순 Select만 하는 필드

. WHERE : 첫번째 = 조건이 오도록

. WHERE 사업장 LIKE @a  --> 사업장 = @a   or 사업장 in (@a)

. 조건자

. Seek 조건자 : 필드명이 보여야 한다

. select + update : 인덱스 적용된다.

. select + delete : 인덱스 적용된다.

MSSQL - 실행 계획

 

SET SHOWPLAN_XML ON

이 문을 사용하면 SQL Server에서 Transact-SQL 문이 실행되지 않습니다. 대신 Microsoft SQL Server는 올바른 형식의 XML 문서에서 문을 실행하는 방법에 대한 실행 계획 정보를 반환합니다. 자세한 내용은 SET SHOWPLAN_XML(Transact-SQL)을 참조하십시오.

SET SHOWPLAN_TEXT ON

이 SET 문이 실행된 후에 SQL Server는 각 쿼리에 대한 실행 계획 정보를 텍스트로 반환합니다. Transact-SQL 문 또는 일괄 처리는 실행되지 않습니다. 자세한 내용은 SET SHOWPLAN_TEXT(Transact-SQL)를 참조하십시오.

SET SHOWPLAN_ALL ON

이 문은 출력 형식이 SHOWPLAN_TEXT의 형식보다 더 자세하다는 점을 제외하면 SET SHOWPLAN_TEXT와 비슷합니다. 자세한 내용은 SET SHOWPLAN_ALL(Transact-SQL)을 참조하십시오.

SET STATISTICS XML ON

문이 반환하는 일반 결과 집합과 함께 문이 실행된 후에 각 문에 대한 실행 정보를 반환합니다. 출력은 일련의 올바른 형식의 XML 문서입니다. SET STATISTICS XML ON은 실행할 각 문에 대한 XML 출력 문서를 만듭니다. SET SHOWPLAN_XML ON 및 SET STATISTICS XML ON의 차이점은 두 번째 SET 옵션이 Transact-SQL 문 또는 일괄 처리를 실행한다는 것입니다. SET STATISTICS XML ON 출력에는 다양한 연산자에서 처리하는 실제 행 수 및 연산자의 실제 실행 횟수에 대한 정보도 포함됩니다. 자세한 내용은 SET STATISTICS XML(Transact-SQL)을 참조하십시오.

SET STATISTICS PROFILE ON

문이 반환하는 일반 결과 집합과 함께 문이 실행된 후에 각 문에 대한 실행 정보를 반환합니다. 두 SET 문 옵션 모두 출력을 텍스트로 제공합니다. SET SHOWPLAN_ALL ON 및 SET STATISTICS PROFILE ON의 차이점은 두 번째 SET 옵션이 Transact-SQL 문 또는 일괄 처리를 실행한다는 것입니다. SET STATISTICS PROFILE ON 출력에는 다양한 연산자에서 처리하는 실제 행 수 및 연산자의 실제 실행 횟수에 대한 정보도 포함됩니다. 자세한 내용은 SET STATISTICS PROFILE(Transact-SQL)을 참조하십시오.

SET STATISTICS IO ON

Transact-SQL 문이 실행된 후에 해당 문에서 만들어진 디스크 동작 양에 대한 정보를 표시합니다. 이 SET 옵션은 텍스트 출력을 생성합니다. 자세한 내용은 SET STATISTICS IO(Transact-SQL)를 참조하십시오.

SET STATISTICS TIME ON

문이 실행된 후에 각 Transact-SQL 문을 구문 분석, 컴파일 및 실행하는 데 필요한 시간을 밀리초 단위로 표시합니다. 이 SET 옵션은 텍스트 출력을 생성합니다. 자세한 내용은 SET STATISTICS TIME(Transact-SQL)을 참조하십시오.

StmtText

PLAN_ROW 형식이 아닌 행에 대해 이 열에는 Transact-SQL 문의 텍스트가 포함됩니다. PLAN_ROW 유형의 행에 대해서는 이 열에 작업의 설명이 포함됩니다. 이 열에는 물리적 연산자가 포함되며 논리 연산자가 포함될 경우도 있습니다. 이 열 다음에 물리적 연산자가 결정한 설명이 나올 경우도 있습니다. 자세한 내용은 논리 및 물리 연산자 참조를 참조하십시오.

SQL - 초급 교육

 

. (주)씨퀄로 : http://www.sqlroad.com

. SW개발 : http://www.sqlroad.com/080819-2/ (SQLBigEyesHammer V3 Freeware)

. 은행쿼리 : 22GB -> 0.1MB (218,000배 향상) : where : 함수 X

1. 실행계획 (인덱스 사용 여부)

2. 세션옵션 (IO) (테이블 단위 페이지 IO량 확인)

. 실제 실행계획 : 검색, seek (OK)

. 논리적 읽기, lob 논리적 읽기

. lob (Large Object) : text, image, ....

. ANSI 표준 사용하기

. WHERE 절 작성 순서는 성능과 연관?

   - 옵티마이저가 변경하기 때문에 순서는 성능과 무관하다.

   - inner join 일 경우에 해당

. 쿼리문 작성 시 테이블 이름 선언할 때 전체 이름으로 (예 db.schema.object)

   - schema 생략 시 옵티마이저가 ID 찾아 사용

. 날짜 그 날 마지막 시간 20211010 23:59:59.997

. between '20211010' and '20211010 23:59:59.99?'

. >= '20211010' and < '20211011'

. 문자열 비교 시 오른쪽 공백은 고려하지 않는다 (rtrim 안해도 된다)

  - @varchar, @char

. MSSQL 다른 DB와 다른 것은 잠금(Lock) 부분이 다르게 적용됨

. 의미 오류 (Semantic Error) : 불필요한 조건

  - WHERE PK_Col IS NULL

  - WHERE ShipVia > 4 OR ShipVia > 2  (false)

  - DISTINCT PK_Col, ....

  - LIKE 'aaa'

  - EXISTS (SELECT DISTINCT.... FORM ...   무조건 1개만 있으면 작업 끝난다)  

  - ORDER BY PK_Col 

  - LIKE '%'

  - HAVING (WHERE에 있어야 할 조건 경우)

  - select 50 orderid from orders (별칭 붙이는 습관이 있어야 함)

  

2. SARG (검색인수)

. WHERE, FROM, HAVING : TRUE, FALSE, UNKNOWN

. 검색 대상 및 범위를 제한할 수 있는 식

. 인덱스 사용 및 쿼리 최적화를 위한 필요 조건

. 반대적인 조건 사항 : Non-SARG (비 검색인수)

  - 불필요한 데이터 (열,행) 요청

  - 열 간 비교 (컬럼 대 컬럼)

  - 부정문은 긍정문으로 자동 변환 (범위 검색이 된다)

  - ID IN (2,4,6) --> ID = 2 or ID = 4 or ID = 6

  - NOT IN (가능한 사용하지 말자)

  - 복합 Index 선행 열 ( "=" 조건이 제일 먼저 나오게 해야 한다)

     ex. PK = (사업장 + 주문일자 + 주문번호)

          사업장 LIKE '%' and 주문일자 = '20211011'     

(예)

PK (사업자, 일자, 번호)

WHERE 일자 = ?

         사업자 LIKE ?

         번호 = ?

사업장 만 Seek, 나머지는 스캔 (인덱스 사용 못 함)

*** 쿼리 스토어 기능 권장 (성능관리) ver 2017 이상

 

. WHERE 함수 (가급적 사용 X)

. WHERE SUBSTRING(CustomerID, 1, 3) = 'CEN' --> WHERE CustomerID LIKE 'CEN%'

. WHERE CONVERT(varchar(8), OrderDate, 112) = '19960704' --> WHERE OrderDate >= '19960704' AND OrderDate < '19960705'

. WHERE DateDiff(dd, ShippedDate, '19980506') <= 1 --> WHERE ShippedDate >= DATEADD(dd, -1, '19980506')

. MSSQL은 NULL 값 인덱스 값으로 저장된다. 

. WHERE ISNULL(OrderDate, '19970702') = '19970702' --> WHERE (OrderDate = '19970702' OR OrderDate IS NULL)

*** ORM 파라미터 : WHERE col = @id 대부분 유니코드('N')로 인식 함.  (예) NVARCHAR 

. LIKE : 문자열에만 사용, %???% (앞에 % 있으면 안된다)

. 열 간 비교

   - select emlid, ship from orders where emlid = ship and ship = 2

   - where emlid = case @ship is null then emlid else @ship end

. OR 인덱스 안 걸리면  --> UNION 으로 변경

3. 조건절 상수화 이슈

. WHERE OrderID > @ID 

  - 상수화 할 수 있어야 옵티마이저가 실행계획을 예측 가능하게 된다.

  - 쿼리 컴파일 시점에 @ID 값을 알아야 하기 때문

  - 문제되는 조건

     . 로컬변수 declare @id int .... where OrderId > @ID

     . 사용자 정의 함수 where OrderId > dbo.uf_OrderNo()

     . 테이블 변수 성능 문제 (@table --> #Table 로 사용)

  - MSSQL 2019 이상 EE "Scalar UDF InLine" 자동 튜닝 지원 (아직 시작 단계. 안정화 진행 중)

  

  - 집합연상 WHERE aaa in (select distinct.....) distinct 할 필요 없다. 중복 자동 제거 됨.

  - where a.ordrid = dbo.uf_split('1,2,3,4,..') -->  join order as a on a.ordrid = dbo.uf_split('1,2,3,4,..')

  - NOLOCK 사용 시 ORDER BY 생략 시 문제 발생할 수 있음 (데이터 정합성 고려)

  - ORDER BY 작성 시 유니크한 값이 나오도록 조건을 만들어야 한다.

     . select top 5 .... ORDER BY aaa, bbb, ccc....

  - count(*) vs count(열) --> null 값이 허용된 경우 count(열)

  - count(*) vs exists --> 데이터 존재 여부 경우 exists

  - 집계 시 대량의 null 이 있을 경우 is not null 처리

  - union --> 자동으로 distinct 처리하는 결과로 처리 (옵티마이저 판단)

  - union all --> 전체 데이터 처리

  - 채번을 원 쿼리로 : update 채번 set @일련번호 = 일련번호 = 일련번호 + 1 where ....

  - OUTPUT inserted.*, deleted.*

4. JOIN

(1) 네스티드 루프 조인

(2) 해쉬 조인

(3) 머지 조인

- 네스티드 루프 조인

  . 첫번째 검색 테이블 데이터가 적어야 함. (inner join 인 경우)

  . 데이터가 많은 FK에 값 convert 할 경우 --> 첫번째 PK 값을 convert 하는 것이 좋다.

  . outer join 시 옵티마이지 처리에 어려울 수 있다. 테이블 순서는 상관없다.

  . 나름 기준을 잡아서 테이블 순서를 정하자. (필수 아님)

5. Subquery 이해

. 파생 테이블 : 외곽 테이블의 key 값이 자동으로 안쪽 파생 테이블 필터링 조건에 적용 된다.

. case select... (X) --> select case (O) 

. null 값이 없도록 : where customerID not in (select customerID from orders where customerID is not null)

. CTE

  - 성능 튜닝 어렵다

. 쿼리 힌트

. 잠금 튜닝

  - 조회 전용 쿼리 시 잠금 차단 회피

     . table WITH (NOLOCK) : 수정 중인 데이터 값을 가지고 온다. (권장 안함 X. 수정후 데이터를 넘긴다)

     . Read Committed Snapshot 사용 권장 O (내부적으로 데이터를 스냅샷 하여 그 데이터를 넘겨준다)

         - TempDB 사용할 시 때문에 해당 TempDB 관리 필요.

6. Cursor : 되도록 운영을 위한 사용 하시고 서비스용은 최대한 사용하지 말 것

   - 사용한다면

     . LOCAL

     . FAST_FORWARD or STATIC READONLY FORWARD_ONLY

7. 함수

  - InLine Table-Values Function (권장)

C# - SameSiteMode

 

쿠키의 SameSite 특성에 대한 값을 나타내는 상수를 지정합니다.

public enum SameSiteMode

상속

Object

 -> ValueType

 -> Enum

 -> SameSiteMode

필드

Lax	1	쿠키는 "동일 사이트" 요청 및 "교차 사이트" 최상위 탐색과 함께 전송됩니다.
None	0	쿠키가 모든 요청과 함께 전송됩니다(설명 참조).
Strict	2	값이 Strict이면 쿠키는 "동일 사이트" 요청과 함께 전송됩니다.

설명

의 동작은 None kb 문서 4531182 및 kb 문서 4524421에 설명 된 업데이트에 의해 수정 되었습니다.

이러한 업데이트를 사용 하지 않으면 None 값이 쿠키 헤더를 내보내지 않습니다. SameSite

이는 준수 https://tools.ietf.org/html/draft-west-first-party-cookies-07#section-4.1 합니다.

이러한 업데이트를 적용 한 후에는 None 값이 SameSite=None 쿠키 헤더를 내보냅니다.

이 새 동작은를 준수 https://tools.ietf.org/html/draft-west-cookie-incrementalism-00 합니다. 

이러한 변경의 일환으로 양식 인증 및 SessionState 쿠키는 이전 기본값 대신 SameSite=를 사용 하여 발급 됩니다. Lax None

단, 이러한 값은 web.config에서 재 정의 할 수 있습니다.

이러한 업데이트가 적용 된 시스템에서는 를로 설정하여 이전 동작을 지정할 수 있습니다. SameSiteMode(SameSiteMode)(-1) 

web.config에서 문자열을 사용하여 이 동작을 지정할 수 있습니다 "Unspecified" .

[추가]

Unspecified  ( -1 )

Lax (느슨한)	1	Indicates the client should send the cookie with "same-site" requests, and with "cross-site" top-level navigations. 클라이언트가 "동일한 사이트" 요청 및 "교차 사이트" 최상위 탐색과 함께 쿠키를 보내야 함을 나타냅니다.
None (없음)	0	Indicates the client should disable same-site restrictions. 클라이언트가 동일 사이트 제한을 비활성화해야 함을 나타냅니다.
Strict (엄격한)	2	Indicates the client should only send the cookie with "same-site" requests. 클라이언트가 "같은 사이트" 요청이 있는 쿠키만 보내야 함을 나타냅니다.
Unspecified (지정되지 않음)	-1	No SameSite field will be set, the client should follow its default cookie policy. SameSite 필드가 설정되지 않으며 클라이언트는 기본 쿠키 정책을 따라야 합니다.

WEB - 브라우저 쿠키와 SameSite 속성

 

브라우저 쿠키와 SameSite 속성

쿠키(Cookie)는 대부분의 웹 서비스에서 사용하는 기술이라 웹 개발자라면 여러 번 마주해보셨을 거에요. 저 또한 웹 서비스를 개발하면서 쿠키를 다뤘던 경험이 종종 있었습니다. 그렇지만 쿠키라는 기술 자체는 별로 흥미로운 기술이 아니다보니 그냥 구현만 마친 후 대충 넘겼던 경험이 많은 것 같아요.

쿠키는 아주 예전부터 쓰였던 기술이지만, 요즘에는 보안이나 개인정보보호 문제 때문에 쿠키에 SameSite 같은 속성이 추가되기도하고 브라우저가 쿠키를 다루는 방식도 점차 바뀌어가고 있습니다.

이번 글에서는 쿠키에 대한 기본적인 이해를 바탕으로 SameSite 속성은 왜 나온 것인지, 브라우저들은 어떻게 동작하고 있는지 알아보겠습니다.

쿠키란 무엇인가요? 왜 사용하나요?

쿠키는 브라우저에 데이터를 저장하기 위한 수단 중 하나입니다. 브라우저에서 서버로 요청을 전송할 때 그 요청에 대한 응답에 Set-Cookie 헤더가 포함되어있는 경우, 브라우저는 Set-Cookie 에 있는 데이터를 저장하고, 이 저장된 데이터를 쿠키라고 부릅니다.

서버에서 전송하는 응답에 포함된 "Set-Cookie" 헤더

위처럼 서버의 응답에 Set-Cookie 헤더가 포함된 경우, normal 이라는 이름의 쿠키에 yes라는 값이 저장됩니다.

그리고 이렇게 저장된 쿠키는 다음에 다시 그 브라우저에서 서버로 요청을 보낼때, Cookie라는 헤더에 같이 전송됩니다. 서버에서는 이 헤더를 읽어서 유저를 식별하는 등 필요한 구현을 할 수 있죠.

브라우저에서 보내는 요청에 포함된 "Cookie" 헤더

쿠키는 이렇게 동작하기 때문에 주로 서버에서 사용자를 식별하기 위한 수단으로 사용되어 왔습니다. 애초에 쿠키가 만들어진 목적 자체가 이런 일을 하는 것이기도 하고요. Set-Cookie 헤더로 세션 ID를 넣어둔 뒤에, 이 후 요청부터 전송될 Cookie 헤더의 세션 ID를 읽어 어떤 사용자가 보낸 요청인지 판단하는 식으로요. 수 많은 웹 사이트의 로그인 구현이 제가 말씀드린 것과 거의 같은 방식으로 구성되어 있습니다.

쿠키에 대한 Domain 설정

쿠키가 유효한 사이트를 명시하기 위해 쿠키에 도메인을 설정할 수 있습니다.

"Domain"이 명시된 "Set-Cookie" 헤더

이렇게 도메인이 설정된 쿠키는 해당 도메인에서만 유효한 쿠키가 됩니다. 위에서 normal 쿠키는 localhost를 대상으로 쿠키가 설정되었기 때문에, localhost를 대상으로 한 요청에만 normal 쿠키가 전송됩니다.

쿠키에 별도로 명시된 도메인이 없다면 기본값으로 쿠키를 보낸 서버의 도메인으로 설정됩니다.

퍼스트 파티 쿠키와 서드 파티 쿠키

그리고 이렇게 설정된 도메인을 기준으로 퍼스트 파티 쿠키(First-party cookies)와 서드 파티 쿠키(Third-party cookies)가 나뉘어 집니다.

이 부분은 예제를 들어서 설명하는게 좋을 것 같네요.

여러분은 seob.dev에 접속한 상태입니다. 만약 seob.dev에서 example.com이 제공하는 이미지인 example.com/image.png를 사용하고 있다고 가정해볼까요? 이 경우 사용자는 seob.dev에 접속해 있지만 브라우저에서는 example.com/image.png로 요청을 보낼 것 입니다. 아래와 같은 HTML 코드로 나타낼 수 있을 것 같네요.

<html>  <head>    <title>seob.dev</title>    <meta property="og:url" content="https://seob.dev/" />  </head>  <body>    <img src="https://example.com/image.png" />  </body></html>

이 때 사용자가 example.com에 대한 쿠키를 가지고 있다면, 해당 쿠키가 example.com을 운영하는 서버로 같이 전송됩니다. 이 때 전송되는 쿠키를 서드 파티 쿠키라고 부릅니다. 그러니까, 서드 파티 쿠키는 사용자가 접속한 페이지와 다른 도메인으로 전송하는 쿠키를 말합니다. Referer 헤더와 쿠키에 설정된 도메인이 다른 쿠키라고도 말할 수 있겠네요. 그렇기 때문에 사용자가 seob.dev에 걸려있는 example.com 링크를 클릭한 경우에 전송되는 쿠키도 서드 파티 쿠키로 취급됩니다. 이 때 Referer는 seob.dev이니까요.

<html>  <head>    <title>seob.dev</title>    <meta property="og:url" content="https://seob.dev/" />  </head>  <body>    <!-- 아래 링크를 클릭한 경우에 전송되는 쿠키들은 서드 파티 쿠키로 취급됩니다. -->    <a href="https://example.com/">링크</a>  </body></html>

퍼스트 파티 쿠키는 반대로 이해하면 간단합니다. 퍼스트 파티 쿠키는 사용자가 접속한 페이지와 같은 도메인으로 전송되는 쿠키를 말합니다.

같은 쿠키라도 사용자가 접속한 페이지에 따라 퍼스트 파티 쿠키로도 부를 수 있고, 서드 파티 쿠키로도 부를 수 있습니다. 앞서 말씀드린 예제에서 example.com에 설정된 쿠키는 사용자가 seob.dev에 접속해 있을 때는 서드 파티 쿠키였지만, example.com에 접속해 있을때는 퍼스트 파티 쿠키입니다.

쿠키와 CSRF 문제

쿠키에 별도로 설정을 가하지 않는다면, 크롬을 제외한 브라우저들은 모든 HTTP 요청에 대해서 쿠키를 전송하게 됩니다. 그 요청에는 HTML 문서 요청, HTML 문서에 포함된 이미지 요청, XHR 혹은 Form을 이용한 HTTP 요청등 모든 요청이 포함됩니다.

CSRF(Cross Site Request Forgery)는 이 문제를 노린 공격입니다. 간단히 소개해보자면 아래와 같은 방식입니다.

1. 공격대상 사이트는 쿠키로 사용자 인증을 수행함.
2. 피해자는 공격 대상 사이트에 이미 로그인 되어있어서 브라우저에 쿠키가 있는 상태.
3. 공격자는 피해자에게 그럴듯한 사이트 링크를 전송하고 누르게 함. (공격대상 사이트와 다른 도메인)
4. 링크를 누르면 HTML 문서가 열리는데, 이 문서는 공격 대상 사이트에 HTTP 요청을 보냄.
5. 이 요청에는 쿠키가 포함(서드 파티 쿠키)되어 있으므로 공격자가 유도한 동작을 실행할 수 있음.

CSRF는 워낙 널리 알려진 문제고 조금만 검색해봐도 잘 설명하고 있는 자료가 많으니 자세히 알고 싶으신 분은 찾아보시는 것을 추천합니다.

바로 뒤에서 말씀드릴 SameSite는 이 문제를 해결하기 위해 탄생한 기술입니다.

SameSite 쿠키

SameSite 쿠키는 앞서 언급한 서드 파티 쿠키의 보안적 문제를 해결하기 위해 만들어진 기술입니다. 크로스 사이트(Cross-site)로 전송하는 요청의 경우 쿠키의 전송에 제한을 두도록 합니다.

SameSite 쿠키의 정책으로 None, Lax, Strict 세 가지 종류를 선택할 수 있고, 각각 동작하는 방식이 다릅니다.

• None: SameSite 가 탄생하기 전 쿠키와 동작하는 방식이 같습니다. None으로 설정된 쿠키의 경우 크로스 사이트 요청의 경우에도 항상 전송됩니다. 즉, 서드 파티 쿠키도 전송됩니다. 따라서, 보안적으로도 SameSite 적용을 하지 않은 쿠키와 마찬가지로 문제가 있는 방식입니다.
• Strict: 가장 보수적인 정책입니다. Strict로 설정된 쿠키는 크로스 사이트 요청에는 항상 전송되지 않습니다. 즉, 서드 파티 쿠키는 전송되지 않고, 퍼스트 파티 쿠키만 전송됩니다.
• Lax: Strict에 비해 상대적으로 느슨한 정책입니다. Lax로 설정된 경우, 대체로 서드 파티 쿠키는 전송되지 않지만, 몇 가지 예외적인 요청에는 전송됩니다.

Lax 쿠키가 전송되는 경우

The Chromium Projects 의 SameSite 속성을 소개한 게시물을 보면 다음과 같이 Lax 정책을 설명합니다.

A cookie with "SameSite=Lax" will be sent with a same-site request, or a cross-site top-level navigation with a "safe" HTTP method.

그러니까 같은 웹 사이트일 때는 (당연히) 전송된다는 것이고, 이 외에는 Top Level Navigation(웹 페이지 이동)과, "안전한" HTTP 메서드 요청의 경우 전송된다는 것입니다.

Top Level Navigation에는 유저가 링크(<a>)를 클릭하거나, window.location.replace 등으로 인해 자동으로 이뤄지는 이동, 302 리다이렉트를 이용한 이동이 포함됩니다. 하지만 <iframe>이나 <img>를 문서에 삽입함으로서 발생하는 HTTP 요청은 "Navigation"이라고 할 수 없으니 Lax 쿠키가 전송되지 않고, <iframe> 안에서 페이지를 이동하는 경우는 "Top Level"이라고 할 수 없으므로 Lax 쿠키는 전송되지 않습니다.

또한 "안전하지 않은" POST나 DELETE 같은 요청의 경우, Lax 쿠키는 전송되지 않습니다. 하지만 GET처럼 서버의 서버의 상태를 바꾸지 않을 거라고 기대되는 요청에는 Lax 쿠키가 전송됩니다.

이 모든 내용은 서드 파티 쿠키에 한하는 것이고, 퍼스트 파티 쿠키는 Lax나 Strict여도 전송됩니다.

브라우저의 SameSite 구현

아마 적극적으로 SameSite 속성을 사용하고 있는 개발자는 많지 않을 겁니다. 우리가 SameSite에 주의를 기울여야 하는 이유는 브라우저들의 동작이 변경되고 있기 때문입니다.

Lax by default

크롬은 SameSite를 가장 적극적으로 적용하고 있는 브라우저입니다. 원래 SameSite를 명시하지 않은 쿠키는 SameSite가 None으로 동작했지만, 2020년 2월 4일 크롬 80 버전이 배포되면서 SameSite의 기본값이 Lax로 변경되었고, 이 변경사항은 운영되고 있는 웹 서비스들에게 많은 영향을 미쳤습니다. 특히 온라인 결제나 OAuth처럼 구현에 크로스 사이트 간의 페이지 전환이 필요한 경우 이러한 변경사항 때문에 원래 제공하던 기능이 제대로 동작하지 않은 경우도 있었습니다. 물론 시간이 꽤 지났기 때문에 현재 운영되는 서비스들은 대부분 대응되어 있을 거에요.

2021년 5월 현재는, 크롬만이 Lax를 기본으로 적용하고 있지만 파이어폭스도 곧 변경될 예정입니다. 사파리는.. 언제 바뀔지 모르겠네요.

Secure 필수 정책

SameSite 속성으로 None을 사용하려면 반드시 해당 쿠키는 Secure 쿠키여야 합니다. Secure 쿠키는 HTTPS가 적용된(그러니까 암호화된) 요청에만 전송되는 쿠키입니다. 이 정책을 구현하는 브라우저도 현재로서는 크롬밖에 없습니다. 그래서 크롬에서는 SameSite=None으로 Set-Cookie를 사용하면 다음과 같이 쿠키 자체가 제대로 설정되지 않습니다.

"Secure"가 설정되지 않은 "SameSite=None" 쿠키

쿠키의 미래

크로미엄 블로그의 Building a more private web: A path towards making third party cookies obsolete라는 글에는 다음과 같은 내용이 있습니다.

... and we have developed the tools to mitigate workarounds, we plan to phase out support for third-party cookies in Chrome.

크롬에서는 장기적으로 서드 파티 쿠키에 대한 지원을 단계적으로 제거할 예정이라는 말이죠. 결국 미래에는 모든 쿠키가 SameSite=Strict로 설정된 것처럼 동작하게 된다는 의미인데요.

현재로서는 퍼스트 파티 쿠키가 서드 파티 쿠키의 역할을 모두 대체할 수 없는 상태입니다. 가령 어떤 서비스가 seob.dev와 seob.io 두 가지 도메인을 모두 사용해서 운영된다고 생각해보세요. 당연히 브라우저는 이 두 도메인을 다른 도메인으로 인식할 것이고, 모든 서드 파티 쿠키가 전송되지 않는다면 이 두 도메인 사이를 왔다갔다 할 때마다 전송되지 않는 쿠키로 인해 문제가 생길 거에요.

구글은 이 문제를 해결하기 위해서 First-Party Sets라는 표준을 제안했습니다. First-Party Sets는 여러개의 도메인을 동일한 사이트로 다룰 수 있도록 만드는 기술입니다. seob.dev에서 "seob.io도 같은 서비스를 제공하고 있어!" 라고 브라우저한테 알려주면 브라우저는 이후에는 그 도메인을 같은 사이트로 관리하는 것이죠. 하지만 아직 표준으로 합의되지 않았고 반대도 많은 만큼 어떻게 될 지는 모르겠네요.

확실한 건 앞으로는 점점 더 쿠키를 사용하기 까다로워 질 거라는 사실입니다. 지금부터 서드 파티 쿠키를 사용하지 못한다는 전제하에 서비스를 개발하는 게 좋을 것 같네요.

출처 : https://seob.dev/posts/%EB%B8%8C%EB%9D%BC%EC%9A%B0%EC%A0%80-%EC%BF%A0%ED%82%A4%EC%99%80-SameSite-%EC%86%8D%EC%84%B1/