쿼리 작성 시 주의할 부분... ING

쿼리 스터디를 진행하면서 작성하고 있습니다. 꾸준히 추가 예정

 

쿼리에는 두가지 종류가 존재한다. OLTP와 OLAP

 

OLTP 쿼리 : 간단한 select 쿼리, 이는 서비스와 트랜잭션에서 사용되는 부분이며 주된 목적은 인덱스를 반드시 사용해서 빠른 시간내에 response를 돌려주는 것이 목표이며, 랜던 I/O로 작업이 진행된다. 이들의 작업은 단일 CPU로도 충분한다.

 

OLAP 쿼리 : 분석용 쿼리, 통계, 집계, 데이터 분석용 등의 분석용 쿼리 이는 응답 속도 보다는 전체 처리 성능을 목표로 둔다. index seek + scan이 가능하며, 랜덤 I/O가 아닌 순차 진행으로 진행 병렬로 처리된다.

 

OLTP와 OLAP의 쿼리의 진행 방법이 완전히 다르기 때문에 작성할 쿼리가 둘 중 어떤 아이로 진행할지 명확히 해야한다.

 

쿼리의 성능을 결정 짓는 아이를 행수 추정 CE - cardinanlity estimization이라고 한다. 대부분의 RDB는 cost based optimizer이다. cost의 선택에 있어서 확인해야하는 부분이 행수 CE, 비용이 낮고 높음에 따라서 인덱스를 사용할지 안할지를 결정하는 부분이다.

 

인덱스를 설정 할 때 3가지 정도는 확인하고 진행하는 것을 추천한다.

1. 행수 추정 : ce의 예상 행수 추정

2. (특정열) 밀도 (denseity) : 밀도가 0에 가까울수록 유니크한 값들의 모임이고 밀도가 높으면 중복값이 많은 상태

3. 쿼리 선택도 (selectivity) : 결과 행수, 테이블 행수

 

쿼리를 작성할 때 조건절 (where, join on)에 대해서 상수화를 진행하자.

//1차 쿼리의 예상 ROW = 380
SELECT *
FROM dbo.Orders AS o
WHERE CustomerID = RTRIM((SELECT CustomerID
			FROM dbo.Customers AS c
			WHERE CompanyName = 'Romero y tomillo'));

//개선 작업 이루 예상 ROW = 5
/*
상수화 후 테스트
*/
SELECT *
FROM dbo.Orders AS o
WHERE CustomerID = 'ROMEY';

 

 

쿼리 성능을 측정하는 CE를 방해해서는 안되는데.. 이를 방해 받는 것은 SARG(search argument)를 위배하는 경우가 매우 크리티컬하다. 

---

실행 계힉에 대해서

1. 예상 실행 계획 ( 쿼리 컴파일만 진행 시 ) : 런타임 정보가 없다.

2. 실제 실행 계획 ( 예상 실행 계획 + 런타임 정보 ) : 메모리, CPU 경고 등의 실행 정보 포함, 고부하 쿼리는 부담이 크다.

3. 활성 쿼리 통계 : 실행 중인 쿼리의 실행 계획을 보고 싶을 때

 

위의 계획에서 보여주는 예상 행수와 실제 결과물 행수가 비슷할 수록 매우 좋다.

---

옵티마이저는 컬럼 값에 대해서 histogram(통계)를 만들며 이를 내부에 저장해두며 이를 통해 예상 행수를 계산한다.

 

통계

1. 인덱스 키 컬럼의 통계 : 우리가 인덱스를 진행할 때 (a,b,c)로 지정한다면 옵티마이저는 해당 인덱스를 사용할지 말지의 판단 하기 위해 a컬럼의 값으로 히스토그램을 만들어서 저장한다. 이때의 포인트는 제작시 full scan을 진행한다는 점이다.

2. 비인덱스 컬럼의 통계 : on, where절에서 검색 조인에 인덱스 컬럼이 아니고, 복합 인덱스에서 첫번째 값이 아니라면 컴파일 시점에 해당 컬럼에 대한 히스토크개람을 자동으로 생성한다. 단, full scan을 진행하지 않기에 sampling 정확도는 떨어진다.

3. 수동 생성 : 가능하지만 사용하지 말자

 

옵티마이저가 통계를 통해 예상한 행수와 결과 행수가 크게 차이 난다면 어떻게 해야하는가?

이는 통계가 잘못된 경우인데

• 통계 테이블의 생성 날짜 오류 : 통계 갱신이 안된 경우
• rows, rows sample의 열 차이 : 둘 값이 같아야 최신 통계다. sampled 행수가 작으면 작을 수록 데이터의 정확도는 떨어진다.

 

히스토그램 정보가 없다면? 

DBCC SHOW_STATICS(table, target) : target(통계 계층의 정보)

위의 명령어를 통해서 데이터를 확인하자.

통계 데이터의 치명적 단점은 당일 데이터가 없다는 점이다.

테이블에는 값이 존재하지만 통계에 존재하지 않기에 갱신을 진행해주자.

 

통계 자동 / 수동 업데이트

자동 : 데이터베이스 옵션 설정

ALTER DATABASE database SET AUTO_UPDATE_STATICS ON
ALTER DATABASE database SET AUTO_UPDATE_STATISTICS_ASYNC ON

수동 

UPDATE STATISTICS table [statistics] [WITH…]

---

기본 쿼리 튜닝

SARG, Non-SARG, 조건절 상수화

 

SARG(search-argument, 검색 인수) : predicates 검색의 대상과 범위를 제한 할 수 있는 식, 즉 테이블 전체를 검색하지 않기 위해서 추가하는 조건식, 이것을 진행해야 옵티마이저가 쿼리 최적화를 진행한다.

• 연산자는 무엇을 사용하던 비교 연산자로 진행해야 한다. 이를 위반하는 연산자 LIKE %A%의 검색
• 부정조건은 피하자, 옵티마이저는 우리가 지정한 부정조건을 긍정으로 변경해서 진행한다.  not in(1,3)과 in(3,4)의 결과는 같지만 퍼포먼스는 완전히 다르다. 조건문은 가능한 긍정문을 사용하자

수식은 가능하면 상수로 변환하자

만약에 해당 식 상수화가 불가능하면 SARG 위반이다. 그럼 가능한 식은?

1.  로컬 변수
2. 사용자 정의 함수

둘은 상수화가 불가능하기에 인덱스 칼럼에 작성하면 안된다. 이 외는 옵티마이저가 잘 동작한다.

 

NON-SARG를 위반하는 경우

• 부정형 사용 주의
• 불필요한 열 값을 변형하는 경우 - 함수 적용, 함시적 형 변환
• LIKE 연산자
• 열간 비교

명심해야하는 부분은 : 복합키에서 첫번째 부터 부정 조건을 줘 버리면 자동적으로 범위 조건으로 변경되어 뒤 조건들이 다 인덱스 검색의 영향을 벗어나 버리기에 절 대 안 된 다.

 

그외 추가적으로 주의할 부분 : 컬럼은 어떤 부분도 가공 되어서는 안된다. 만약에 값 변형으로 검색이 진행 되어야 한다면... 왼쪽이 아닌 무 조 건 오른쪽에서 진행해야 한다.

/*
(Index열) 조건식 컬럼 함수 적용
*/
-- 1. Substring, Left, Right
SELECT OrderID, OrderDate, CustomerID
FROM Northwind.dbo.Orders
WHERE SUBSTRING(CustomerID, 1, 3) = 'CEN'


   --정상 구현
	SELECT OrderID, OrderDate, CustomerID
	FROM Northwind.dbo.Orders
	WHERE CustomerID LIKE 'CEN%'


-- 2. Convert
SELECT OrderID, OrderDate, CustomerID 
FROM Northwind.dbo.Orders
WHERE CONVERT(varchar(8), OrderDate, 112) = '19960704'


   --정상 구현
	SELECT OrderID, OrderDate, CustomerID 
	FROM Northwind.dbo.Orders
	WHERE OrderDate >= '19960704' AND OrderDate < '19960705'



-- 3. datediff vs. dateadd
SELECT OrderID, ShippedDate, CustomerID
FROM Northwind.dbo.Orders
WHERE DateDiff(dd, ShippedDate, '19980506') <= 1


   --정상 구현
   SELECT OrderID, ShippedDate, CustomerID 
   FROM Northwind.dbo.Orders
   WHERE ShippedDate >= DATEADD(dd, -1, '19980506')



-- 4. ISNULL
SELECT *
FROM Northwind.dbo.Orders
WHERE ISNULL(OrderDate, '19970702') = '19970702'


   --정상 구현
	SELECT *
	FROM Northwind.dbo.Orders
	WHERE (OrderDate = '19970702' OR OrderDate IS NULL)

 

 

LIKE 연산자 같은 경우에 첫번째 값은 절대 %이면 안된다. 상수로 해야함. LIKE는 반드시 문자 타입에만 숫자, DATE에 사용하면 안된다.

/*
열 간 비교, 그리고 Constant Propagation
*/
SELECT Employeeid, ShipVia
FROM Northwind.dbo.Orders
WHERE Employeeid = ShipVia (NO!)

   -- Constant Propagation 이해(명시적 방법과 차이)
	AND Employeeid = 2


// 아래와 같이 변경 가능

/*
열 간 비교, 그리고 Constant Propagation
*/
SELECT Employeeid, ShipVia
FROM Northwind.dbo.Orders
WHERE 2 = ShipVia

 

쿼리 힌트 : RECOMPILE

SELECT * 
   FROM EPlanHeap.dbo.Orders
   WHERE OrderID < @ID

   OPTION (RECOMPILE)

SELECT 문구의 제일 마지막에 존재하는 recompile은 쿼리를 다시 compile을 요청하는 행위이다.

재컴파일 동작은 compile 시점이 아닌 runtime에 발생하는 행위 이기에 정상적인 실행 계획이 나올 수 있다. 다만 recompile은 부하가 발생한다는 점을 주의하자.

 

제일 중요하게 생각해야하는 부분

• where join등 의 검색 조건은 SARG를 만족해야 한다.
  • 불필요한 열 참조 만들기
  • 불필요한 부정 조건 쓰지 말기
  • 검색 대상 열 변형하지 말기
  • 비교 대상 데이터 형식 다르게 하지 말기
  • LIKE의 첫 문자를 와일드 카드 %를 사용하지 말것
  • 모호한 검색 조건 쓰지 말기
  • 복잡한 검색 조건 사용하지 않기
• 뷰, 함수
  • 외부 쿼리에서 SARG에서 만족하도록 설계하기
• 반드시 필요한 데이터를 필요한 시점에만 요구할것
• 같은 데이터는 두번 다시 읽지 않는다
• 불필요하게 범위 조건이나 LIKE 조건을 사용하지 않는다.
  • 특히 복합 키 or 인덱스 선행 열에 대해
• 불필요한 연산(쿼리 자체 or 내부 연산자)를 줄인다
• 함수 호출을 최소화 한다.
• nolock 힌트나 잠금 세션 옵션을 적절히 사용한다.
• 실행 계획을 확인한다.

---

OLTP 쿼리는 무조건 nested loop join으로 풀려야한다.