[튜닝] 쿼리와 성능의 기초지식!!!!!!

튜닝 - 인덱스

옵티마이저 : 비용으로 표시할 지 다른것으로 표시 할지 정해진다
비용기반 옵티마이저
예상행수와 실제행수가 차이 날 때 어떻게 할 지

 

 

--* SQL 튜닝

 

: 최소한의 자원을 사용하여 사용자가 원하는 시간에 데이터를 가져오는 것
자원 - 하드웨어(CPU/메모리/I-O)
SELECT 쿼리문 

 

-- *왜 튜닝을 해야하나

: 실제 개발 할 때에는 운영데이터가 아니고 테스트용 데이터나, 임시데이터이기 때문에,
테스트할 때 운영데이터가 아닌것으로 하면 오차가 발생함

개발서버에 운영데이터를 그대로 데이터를 삽입 후 실제 돌려보는 식...이지만 그래도 
오차는 발생 함. 하지만 제일 운영데이터와 비슷하다고 볼 수 있음


하드웨어~~~~~

-- *분석 및 설계할 때 참고할 사항
: 요구사항을 확인하고 데이터를 처리 (UPDATE/INSERT/DELETE)가 우선인지, 조회(SELECT)가 
우선인지 확인을 해야 한다.-> 이것에 맞춰서 DATABASE의 성격을 구분해줘야 한다.

SELECT에서는 SQL구조적 설계 및 성능 최적화(?)를 고민하고 설계
(예, KEY의 후보를 많이 추출하고, 역방향해서 키의 후보를 줄인다.)
데이터를 수정/삭제/조회 할 때에도 마찬가지 -> 필수 입력사항 등의 제약 사항들을 정의 
할 수 있으면 더 좋다
(예, 나이의 범위-> 특정한 범위를 둬서 찾으면 더 빠르다.)

--* 개발 구혈 할 때 참고할 사항
운영(구현)부분과 내 개발환경의 차이점 확인 
(예, 가상화, 네트워크, 디스크(I/O)

---------------------------------

개발~~~~~

-- * 쿼리문에서의 매개변수의 값과 통계정보의 정책(설정)을 운영하고 비슷하게 설정
후 테스트 한다 

(
사용자 : WWW -> WAS -> DB
개발자 : WWW,WAS,DB를 한번에
)


-- * 튜닝
: 대상 수집(튜닝 대상) : Query + 서버의 자원
문제점 확인(확인할것 -> 인덱스, 조인, WAS의 로직(응용프로그램의 업무로직)이 잘못된 경우,
사용자 조건 및 사용조건, 함수사용여부(함수를 많이 알면좋음),

docs.microsoft.com/ko-kr/sql/t-sql/functions/system-functions-transact-sql?view=sql-server-ver15

시스템 함수(Transact-SQL) - SQL Server

docs.microsoft.com/ko-kr/sql/t-sql/functions/aggregate-functions-transact-sql?view=sql-server-ver15

집계 함수(Transact-SQL) - SQL Server

 

 

SQL 구문오류(컴파일 하거나 파싱 할 때 너무 시간이 오래걸리는것, 힌트를 잘못 사용한
것(=NOLOCK 등)))

 

 

docs.microsoft.com/ko-kr/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-ver15

테이블 힌트(Transact-SQL) - SQL Server

 

-- * 기타 확인 사항

:

1) COUNT 할 때 연산 할 경우 -> 불필요한 연산이 있는지?
2) 동일 테이블에 대한 중복 접근으로 처리비용이 증가하는지 확인
-> 저장 프로시저의 사용여부를 고민해봐야 함
3) 최대/최소 할 때 테이블 전체를 검색 하는지?
-> 한개의 컬럼을 찍어서 검색하는게 훨 빠름을 참고함(특정한 범위 내를 사용 권장한다는 뜻)
4) GROUP BY 사용하여 뷰를 만들 때 주의
-> 테이블 전체를 검색하는 경우는 좋지 않다. -> SELECT 컬럼시 *이아닌 필요한 컬럼명을 입력한다
5) DISTINCT 구문사용시 :  테이블 전체 검색은 좋지 않다.(위와 동일)
6) 정렬 할 때 주의 : 테이블 전체 검색주의
7) 전체 검색 : 데이터 유무 검사
8) JOIN 구문 할 때 주의 : 테이블 결합 여부 -> JOIN순서에 따라 속도가 달라지는 경우가 있음

 

 

 

 

 

 

 

 

적용~~~~~

-- * 옵티마이저 
: (SQL 서버들은 대부분 옵티마이저가 있다.) 고급 프로그래밍 언어에서 사용되는 프로그램에 대하여
사용할 때 자원들의 영역의 실행시간의 단축이나 축소를 할 수 있도록하는 최적화 프로그램 
-> 최적화 프로그램

-- * 쿼리 옵티마이저
: 쿼리문을 실행하면 DBMS(엔진 관리자)가 처리하기 좋은 형태로 유도또는 처리하는 프로그램
=> 문법검사 => 파씽 => 최적화(DB엔진) => 실행


SELECT 컬럼명 FROM 테이블 실행
(컬럼명/ 테이블 이름은 확인하지 않는다)
구문이 맞는지 틀린지만 검사

컬럼과 테이블 이름을 확인

기록이 있는지 확인 - 엔진에 따라 다름 : 다시 해당기록을 호출(SP)
Soft Parsing
실행 기록이 없으면 : SQL엔진이 옵티마이저한테 문의하고, 어떤 방식으로 처리하면
좋을지 실행 계획을 문의. 실행계획에 맞추어서 실행 : Hard Parsing
=> 할때마다 계획을 세운다고 봄 따라서 자주쓰는건 저장프로시져를 쓰고, 복잡하면
뷰를 쓰되, 인덱스된 뷰를 쓸 수 있으면 인덱스덴 뷰를 쓰고, 너무 많으면 파티션 된 뷰를
쓰면 좋다 왜냐면 MSSQL은 소프트파씽보다 하드파씽을 많이하기 때문이다.



-------------------------------------------------------------------

옵티마이저(최적화) ~~~~~

-- * 쿼리 옵티마이저의 종류
: 1) 규칙 기반 옵티마이저(Rule Based Optimizer : RBO)
초창기시절 많이 함. 최적화. DBMS에서 문법에 대한 검증을 우선 할 경우 사용.
(오라클은 유닉스에 최적화)
초창기시절에는 구문을 확인하고 이를 우선순위를 두어서 처리하는 방식을 많이 씀

ROW(행)에 대한 값을 확인(1개 행만 우선 확인) 후 인덱스를 확인하는식으로 하나하나씩 확인

- 장점 : 우선순위가 정해져있기 때문에 SQL실행 방식에 대한 예측이 가능했다.
따라서, 예측에 따라 실행 계획을 세우기 편했다.-> 절차 분석으로 예측하기 편했다.

소프트웨어에 대한 비용 즉 자원(하드웨어의 자원)을 확인하지 않고, 정해진 규칙으로만 한다. 
인덱스를 적절히 사용 및 종류에 맞추어서 최단/최적으로 가져올 수 있는지 확인하기 어렵다.
(하드웨어 자원을 사용해서)

-> 많은 경험이 필요하다


2) 비용 기반 옵티마이저(Cost Based Optimizer : CBO)
: 자원을 얼만큼 소비하면서 이것을 처리할까를 따짐. 

쿼리를 전달받음 - 쿼리확인 : DBMS 툴에서 규칙기반 최적화 기능을 진행을 먼저한다.
규칙기반 최적화를 대신 할 수 있다.
이때, 엔진에 따라 몇백개부터 몇천개의 실행 계획을 생성한다.
그 다음에, 생성한 계획에서 가장 자원을 적게 사용하는 것을 선택한 후 제시한다.

-> 핵심 : 예상실행 계획대로 쭉 가서 내가 원하는대로 시간과 비용이 나와야 한다.
예상과 실행이 다르면 성능이 떨어진다: 따라서 사람이 잡아줘야함.

쿼리는 인덱스만 잡아서 되는게 아니고 통계정보를 활용



- 구성요소
1) 쿼리 Transpormer : 해당되는 SQL구문에 대하여 Passing Tree(구조적으로 변경)
구조를 분석하기 위해서, 결과값에 영향을 주지않는 범위에서 실행계획을 세우고, 
계획대로 되는지 판단하는 작업을 한다.

2) ESTIMATOR
전체 비용을 계산
시스템 통계 정보(현재 서버의 자원 정보를 통계롤 가지고 있음)
SQL개체(DB안의 정보)를 통계로 가지고 있다.
-> 두개의 통계정보를 사전으로 등록해서 수집정보와 총 비용을 도출한다.

3) PlanGenerator: 총 비용(Estimator)에서 통하여 나온 계산값을 기준으로 실행계획 후보들을
생성하고, Plan Generator를 통하여 최종 선발된 행의 소스 값을 생성하여 코드로 변경한다.




따라서, 비용 방식에 따른 종류가 있다.
1) FIRST_ROWS : 옵티마이저가 처리 결과중에 첫번째 행을 출력하는데 까지의 시간을
확인한다. 그래서 최소 시간중심으로 실행계획을 장성하는 모드다.
단점 - 첫번째 행을 가져와서 계산, 전체건수가 많으면 (보통 테이블의 데이터가 10%이상
가져올 경우 결과가 좋지 않다)

2) ROW_n(단순SELECT에서 쓰면됨, JOIN 제외....)
: ROWS 단점을 보완한 방식 : SQL 쿼리문의 실행결과를 출력하는데 걸리는 응답속도 최적화에 중점.

3) ALL_ROWS(요즘 대부분 이걸로 씀- 기본값)
: 결과 전체에 대한 실행계획을 세운다
마지막까지 출력 될 행에대하여 최소한의 자원을 사용하여 최대한 빨리 실행되는 계획을 세움


------------------------------------------------------------


-- * 통계정보
: SQL 튜닝 할 때 가장 중요한 요소이다.
비용기반 옵티마이저가 실행 계획을 수립하기 위하여 참고하는 정보.

통계가 가지고 있는 통계정보의 종류~
1) 테이블
테이블의 전체 행의 수.
테이블이 차지하고있는 전체 블록의 수
테이블의 행들이 갖는 평균 길이
기타 테이블 정보

2) 컬럼
컬럼 값의 타입(형식)
컬럼 내 NULL 값의 정보
컬럼 값의 평균길이
컬럼 안의 데이터 분포와 추정치
기타 컬럼 정보

3) 인덱스
해당되는 LEAF BLOCK수 : 테이블의 블록수(인덱스가 어디까지 가지를 쳤는지-Tree식)
인덱스 트리 LEVEL 정보
사용하고자 하는 데이터가 모이있는 위치(밀집도)

4) 시스템
입출력 성능 및 사용률
CPU성능 및 사용률


위의 정보를 가지고 예상실행 작성을 실행하게 됨.
비용기반 옵티마이저는 우리가 실행 할때마다 이런것들을 만들어 냄

비용기반 최적화를 할 때에는 통계정보를 일회용이 아니라 지속적으로 활용 할 수 있도록
정보를 저장한다.
주의할 점은 INSERT, UPDATE, DALETE가 일어나서 테이블 정보를 변경 할 때,
해당 통계정보를 업데이트를 해야한다는 것이다. 언제....?! 자주? 가끔?
개발자들이 언제업데이트를 해야할까?

- 데이터베이스 속성-> 옵션 -> 자동부분 설정확인
- 통계 자동 업데이트/작성 작성된는시간동안 느릴 수 있음. 그래서 하루 한번 수동으로 
하는 경우가 있음.
- 증분통계자동작성과 비동기적 자동업데이트을 TRUE로 바꾸고 속도가 얼마나 빠른지 체크해볼것.


-------------------------------------------------------------------------

-- * 튜닝을 할 때 중요한것(사용할것)

1) 저장프로시저 사용여부
컴파일해서 빌드 한 다음 캐시에 저장
자주 사용되는 쿼리문은 저장 프로시저를 사용한다.

-순서
구문분석(파씽) -> 표준화 (Standardizarion)-> 최적화(Optimiztion)-> 컴파일(Compilation)-> 실행(Execute)
자주사용되므로 메모리에 캐시를 사용함

SELECT * FROM sys.syscacheobjects;
Drop freeproccache

최적화에서 변경이 되는게 있다면 저장프로시저도 내렸다가 수정후 다시 올려야 한다.
(통계에 대한 내용이 부족하면 저장프로시져도 효과가 없을 수 있다-> 기존에 저장프로시저는 잘 변경되지 않는 데이터를 사용할 것)


2) 뷰
복잡한 쿼리를 쉽게 사용 할 수있다.
인덱스된 뷰/ 파티션 된 뷰
인덱스된 뷰 : 뷰 + 인덱스를 적용 가능하다.(오직 오라클,MS만 사용 가능)
파티션된 뷰 : 뷰 + 특정 그룹처럼 나누어서 만든 뷰 

드랍 

3) 인덱스
- Clusctered Index/ Non-Clustered Index : 구조적인 기준으로 봤을 때로 나뉨

Clusctered Index : 테이블에 1개만 존재, 목차/페이지 용도로 INDEX를 줄때, A~B까지 빠르게 찾을 때 씀
Non-Clustered Index : 콕찝어 A만 찾을 때 씀, 250개까지 사용 가능하다.

컬럼구성
Single Column Index :  1개 컬럼으로 인덱스
Compositive Index : 2개 이상의 컬럼으로 인덱스

- 유일한값 : Unique Index / Non-Unique Index

-- Clustered Index (클러스터된 인덱스) : 데이터를 순서대로, 저장소(하드디스크)에 물리적으로 저장(목차/차례) 5%
테이블당 한개.
클러스터 되지 않는 인덱스보다 빠르고, unique index로 사용됨.
Insert 할 때 느리다.
범위에 최적화 되어 있는 쿼리 사용하면 좋다.
순차적으로 디스크에 저장되는데 중간에 값이 비워지면 어떻게 될까?
1 ~ 999 에서 600 삭제되면 600을 채우냐? X 1000 으로 저장 < - 디스크에 물리적으로 진짜 
저장? 저장하면 Disk IO에 부담가중 ( 물리랑 논리랑 조금 다를 수 있는데 디스크 조각 모음을 통해
물리적 저장소를 논리적 저장소로 맞춘다)
8page 저장 다음 페이지 넘어가면 Clustered Index 순차적으로 저장되는 공간을 연결

-- Non-Clustered Index 10~15%
물리적인 순서에 따라 저장되지 않는다.
SELECT에서 사용 할 때에는 Clustered Index보다 느리게 응답한다
하지만, INSERT/UPDATE/DELETE용도로 사용 할 때에는 빠르다.
249개를 사용 가능하고 + 1개 Clustered Index를 사용 가능하다
특정한 범위(짧은 범위)에서 데이터를 반환하는데 빠름
숫자가 아닌, CHAR 타입으로도 가능하다(문자말고 숫자를 권장, 문장일 때에는 알파벳을 권장)

목차, 페이지 중에 페이지가 Non-Clustered Index다.


Clustered Index Non-Clustered Index
개수 1 249 합계=250개
인덱스크기 테이블의 1~5%만 크기지정가능 10~20%
select 빠름 느림
update 등 느림 빠름
사용범위 지정시 빠름 하나의 값을 지정할때 빠름


-- 최적의 JOIN 성능
먼저 Clustered Index끼리 쭉 연결 해 놓고 Non-Clustered Index로 연결하는 애들을 가져온다.


- 한개의 컬럼으로 인덱스를 만드는지, 두개이상의 컬럼으로 인덱스를 만드는지 파악할것
- 단일컬럼 인덱스 : 한개의 컬럼으로 생성한 인덱.
- 복합컬럼 인덱스 : 2개이상의 컬럼으로 생성한 인덱스.

복합컬럼 인덱스에서 쿼리가 이루어질 때 하나 이상의 컬럼에 대하여 자주 쿼리
(여러컬럼 연결/비교 할 때->WHERE에서(10개미만으로 생성하길 권장). Non-Clustered Index도 20개미만으로 생성 권장.)
가 발생하면 속도를 증가 시킬 수 있다.

Non-Clustered Index를 이용하면(타고다닌다고 표현) 검색을 빨리 해오기 때문에 인덱스로 보통 만드려고 한다.
Clustered Index는 크기가 너무 크면 효율이 떨어진다.
따라서 많이 만드는게 아니라 복합 INDEX에서 10개미만으로 생성을 한느데 Clustered Index는 5개, Non-Clustered Index는 10개
미만으로 생성하기를 권장한다 -> 크기때문에......(사실 제한은 없음.....)

- 복합 컬럼인덱스를 만들때에는 컬럼 순서가 중요하다.
컬럼 안에있는 녀석의 인덱스 안에 1개의 컬럼을 우리는 WHERE에서 사용함.-> 정렬을 사용함-> INDEX SEEK가 일어날 때
복합컬럼 인덱스는 첫번째는 INDEX SEEK가 일어나(빨리 가져 올 수 있음)고 복합컬럼을 생성한 2번째 컬럼부터는 
빨리 가져오지는 못함. 평소와 비슷
(따라서 복합 컬럼인덱스를 굳이 쓸 필요는 없을수도 있다.......)


INDEX SEEK..............!!!!!!!!!?????


유니크 인덱스 : 테이블안에 고유한 하나의 값
중복된 유니크 인덱스 : 값이 중복
검색 : 유니크 -> Clustered Index가 기본 유니크 인덱스

-------------------------------------------------------------------------------------


*인덱스 종류

-- INDEX SEEK -> 훨씬 빠름!!!!
: 해당되는 녀석을 A~B까지 범위(작은 범위)를 콕 찝어서 INDEX를 찾는다.BETWEEB, IN, OR

- INDEX SCAN
: 범위가 정해지지 않을 때(큰 범위).LIKE구문

* INDEX만 잘하면 Order By를 줄일 수 있다.
쿼리문 -> 해당테이블 스캔 -> Sort(정렬) OR Index(Sort) -> Parallelism 
: SQL 엔진에 비용 최적화에서 많은 비용을 소비한다.



-----------------------------------------------------------------------------------


-- * 성능 비교

1. WHERE구문에서는 대부분 INDEX를 사용한다. -> 사실 모두(Clustered/NonClustered)

2. INDEX를 이용해서 WHERE를 할 경우 가급적으로 ' = '를 사용하는것이 좋다.
(LIKE/ IS NULL, IS NOT NULL, NOT IN)은 효율이 떨어진다

3. 인덱스의 원형을 유지
  크기를 통계로 가지고 있기 때문에
  int 선언한 것을 char 변경 가능함 이런 컬럼을 index 하지 않는다
  또는 형을 변경하지 아낳는다. : 통계

4. OR 보다는 AND가 성능이 더 좋다.
  OR = UNION ALL 하는 것과 같다. 그래서 AND를 해야 성능이 더 좋다.

5. GROUP BY ~ HAVING 보다는 WHERE 필터링을 한다.

6. DISTINCT 는 최대한 줄인다.
  : 정렬 작업하기 때문에 필요한 경우 아니라면 하지 않는다.

7. IN, NOT IN 최대한 사용하지 않고, EXISTS, NOT EXISTS 으로 변경하여 사용

8. UNION을 사용하지 말고, 필요하면 차라리 UNION ALL이 더 낫다. 
-> SET연산자 사용시 UNION 말고 UNION ALL을 사용한다.
해당되는 쿼리에서 동일한 행의 값을 제거하고 한개의 행의 값을 반환한다.