[멘토링] SQL Performance - 성능개선 이론

OPTIMIZER

- SQL을 가장 빠르고 효율적으로 수행할 최적의 경로를 생성해주는 DBMS의 핵심 모듈

- Optimizer가 생성한 경로를 실행계획이라 함. 2가지 기준으로 실행계획을 생성함

- 테이블 및 기타 항목을 기준으로 옵티마이저는 실행계획을 생성하고 이를 실행시킨다.

 

1. 비용기반 최적화(Cost Based Optimizer, CBO)

: 쿼리를 처리하기 위한 여러가지 계획을 생성. 각 단위 작업의 비용(부하) 정보와 대상 테이블의 예측된 통계 정보를 이용해 각 실행계획별 이용을 산출 → 최소 비용이 소요되는 방식 선택

: 질의를 변환한 후, 다수의 실행계획을 생성하고 이 중에서 가장 비용이 적은 실행계획을 기반으로 실행

** 통계정보: 테이블 내 Row, Column수/ 각 열의 길이와 데이터 형/ 테이블의 크기/ Column의 PK, Not Null 등 제약 정보/ Column내 value에 대한 분산 수준

 

2. 규칙기반 최적화(Rule Based Optimizer, RBO)

: 옵티마이저에 내장된 우선순위에 따라 실행계획 수립 → 현재는 거의 사용되지 않음.

 

인덱스 개념 및 활용

• 구조
  : 테이블/컬럼을 기반으로 선택적으로 생성할 수 있는 구조
  : 설정에 대한 물리적 졔약은 없음
• 특징
  : 검색 성능 최적화를 가능하게 함
  : SELECT / UPDATE / DELETE 시에 활용 가능 → 데이터를 ‘찾는' 작업 : 적절히 설정하여 사용해야 함(성능 등에 활용)
  ex. index가 많으면 INSERT할 때 오래 걸린다.
• 활용
  : SHOW INDEX FROM [TABLE_NM];
  : ALTER TABLE [TABLE_NM] ADD INDEX [INDEX_NM] ([COLUMN1], [COLUMN2], …);
  : ALTER TABLE [TABLE_NM] DROP INDEX [INDEX_NM];
• 강제로 특정 인덱스 경로를 지정하여, 실행하도록 제어 가능
  SELECT [COLUMN] FROM [TABLE_NAME] USE INDEX [INDEX_NM];
• B트리 인덱스
  : Root, Branch, Leaf Block으로 구성되며 인덱스를 통해서 테이블 내의 위치를 찾아서 조회
• 클러스터 인덱스
  : 한 테이블 당 1개의 클러스터 인덱스만 사용 가능하며, 데이터 저장 블록의 포인트 지정

 

인덱스 사용법

PK는 첫번째 인덱스이다. 

• Unique, Not Null 조건을 만족하는 첫번째 인덱스
• PK를 최대한 활용하는 쿼리 작성 권고

복합 인덱스 설정 시, 앞부분 부터 사용되어야 인덱스가 사용된다. 

• 복합 pk 사용 시, 우선순위가 높은(=where 조건에 많이 들어가는) 컬럼을 상위에 위치
• sql 문장에서의 순서는 무관

eqaul보다는 못하지만 range로도 인덱스 사용 가능

• “[Column] = “ 뿐만 아니라 “[Column] Like” 까지도 인덱스 사용 가능

 

조인 방법

• 두 개 이상의 테이블을 하나의 집합으로 논리적으로 결성. 옵티마이저에 의한 순차적 작업으로 진행됨.
• Nested Loop(NL) - 가장 표준적인 형태의 JOIN
  : 중첩된 반복문과 유사한 방법으로 수행
  : For(For)의 구성으로 반복 수행
• SORT MERGE
  : 각 JOIN 대상을 각각 정렬 후 비교하여 연관관계를 탐색
• HASH

 

실행계획 분석방법

• EXPLAIN 키워드
  : MySQL 에게 쿼리문의 실행 계획을 물어보는 키워드이다.
  : Explain 구문을 이용해서 SQL Query를 수행하기 전에 데이터를 어떻게 가져올 건지에 대한 시스템의 실행계획을 받아볼 수 있다. 주로 쿼리 퍼포먼스 측정을 위해 Explain 을 많이 사용하지만 매 쿼리를 코드에 삽입할 때 테스트해보는 습관을 들이는 것이 좋다.
  : 사용법) SELECT 구문에서 explain 을 사용하는 방법은 단순히 키워드 앞에 붙여주기만 하면 된다. 단 SELECT 구문이 아닐 경우에는 INSERT, UPDATE, DELETE 등의 구문을 SELECT로 재구성시켜줘야 한다.

 

SQL 작성시 TIP

인덱스 활용

• WHERE 구문에, 컬럼에는 함수를 씌우지 않음
• 가급적 PK 위주 조회조건 사용

모호한 부분 최대한 제거

• ALIAS 사용 시에도 최대한 활용
  ex. SELECT HEIGHT, LOCATION FROM SCHOOL C, STUDENT S … (X)
  SELECT S.HEIGHT, C.LOCATION FROM SCHOOL C, STUDENT S … (O)

많이 사용되는 컬럼은 인덱스 생성

• 과다한 인덱스는 지양하되, 적절한 인덱스는 필요
• 몇 십만 이상의 데이터도 인덱스 없이도 일정 수준 이상의 성능 확보

 

FUNCTION 예시

DELIMITER //

CREATE FUNCTION FN_GEN_RANDNUM(IN_TOT_NUM INT)
RETURNS INTEGER(11)
BEGIN

DECLARE V_TGT_NUM INT(11);

	 SELECT MOD(ROUND(RAND()*10000,0),IN_TOT_NUM) + 1 
	   INTO V_TGT_NUM
	  LIMIT 1;
	  
	 RETURN V_TGT_NUM;
        
END //

DELIMITER ;

FN_GEN_RANDNUM(N) : 1~N까지의 숫자 중에서 랜덤으로 가지고 옴

FN_GET_RANDCODE(’카테고리 타입’) : 카테고리 타입에 해당하는 것들을 랜덤으로 가지고 옴
ex. FN_GET_RANDCODE(’ALPHABET')

FN_GEN_RANDNUM('시작날짜', '끝나는 날짜', 'D') : D는 Date, S는 시간까지 바뀜

 

PROCEDURE 예시

고유독립의 table에 해당하는 것들을 많이 만들 수 있음

키워드 CALL

procedure 만든 후에 ex. CALL PC_GEN_DATA_TABLE(1,100); 등으로 확인해 볼 수 있음

 

---

멘토링 후기

1. 현재 프로젝트는 DB JOIN을 사용하지 않고 대부분 json형태로 저장하고 있어 PROCEDURE를 만들어 보지는 않을 것 같다.
2. 실제 사용자가 많지 않다면, (근데 서비스 테스트를 진행해 보고 싶다면) FUNCTION을 만들어서 자동으로 테스트 케이스를 생성하는 건 해 보아야 겠다.
3. 현재 findByName()등 이름으로 검색하는 부분이 많은데 PK를 제대로 활용하지 못한 부분이란 생각이 들었다. 
-> 이 부분 고려해서 코드 내에서는 Id 값을 활용할 수 있다면 최대한 활용하게 코드 수정해야겠다.
-> 다른 강의를 들으면서 왜 Long id등을 return값으로 사용하는 건가 생각했는데 이런 영향도 있을 거라는 생각이 들었다.
4. 조인방법 3가지를 멘토링 때는 이해했지만, 다음에 블로그에 소개하는 글로 써봐야겠다. (한 번 더 정리도 할겸~)
5. OPTIMIZER를 네비게이션에 빗대어 설명해주셨는데 이해가 완전 쏙쏙 갔다. 다음에 나도 설명하게 된다면 인용하기~
6. 인덱스 개념...확실히 알아갈 수 있었다. 단일 컬럼의 인덱스도 가능하고, 멀티 컬럼의 인덱스도 가능하고, 인덱스 여러개도 가능하다. 다만 고려해봐야 하는 건 그만큼 INSERT할 때 소요되는 시간이 길어진다는 점!

 

https://www.notion.so/19-00-SQL-Performance-62498f16457e45ba8ff9435810664b02