dBack

tb01 테이블의 user: 현 seq에 대한 전 seq 구하기 위한 쿼리

select    seq

  , lag(seq) over client_winodw as pre_seq

from tb01

where user = #{user}

window client_winodw as (partition by seq order by seq)

TO_CHAR를 사용하여 숫자를 문자로 변환하면 소수점 이상이 0일 경우 점(.)으로만 표시되어 변환이 된다. 아래와 같은 포맷(FORMAT) 옵션을 활용하여 변환된 수치 값의 형태를 결정할 수 있다.

FM9990.99의 의미

9는 해당 자리의 숫자를 의미하고 값이 없을 경우 소수점 이상은 공백으로 소수점 이하는 0으로 표시한다.

0은 해당 자리의 숫자를 의미하고 값이 없을 경우 0으로 표시하며 숫자의 길이를 고정적으로 표시 할 때 주로 사용한다.

FM은 좌우 9로 치환된 소수점 이상의 공백 및 소수점 이하의 0을 제거 한다.

[추가설명]

FM을 넣지 않을 경우 숫자소수점 이상은 공백으로 소수점 이하는 0이로 표시된다.

9는 가변적인 값이어서 0이거나 숫자가 존재하지 않을 시 값을 버린다.

0은 해당 값의 길이가 고정적으로 변환이 된다. 변환된 숫자의 길이를 일정하게 맞추고 싶으면 길이만큼 0으로 채우면 된다.

변환될 숫자의 길이보다 포맷의 길이가 작으면 오류가 발생한다.

포맷을 지정 시 변환될 숫자의 길이보다 최대 개수만큼 9 또는 0을 채워야 한다.

출처: http://gent.tistory.com/49

SELECT 

DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01')

, DATE_FORMAT(CURDATE(), '%Y-%m-%d')

FROM DUAL

// 비교

, CASE WHEN

DATE_FORMAT(B.DEL_DATE, '%Y-%m-01') <= DATE_FORMAT(CURDATE(), '%Y-%m-%d') AND DATE_FORMAT(DATE_ADD(B.DEL_DATE, INTERVAL 1 MONTH), '%Y-%m-01') >= DATE_FORMAT(CURDATE(), '%Y-%m-%d') THEN 'Y'

ELSE 'N'

MyBatis 사용 시에 param이 1개 혹은 여러개 혹은 전부 사용 시

prefix에 AND 혹은 , 항목이 필요 할 때 

trim 구문을 사용하면 유용하다

<update id="updateTable "  parameterType="map">

UPDATE TABLE

<trim prefix="SET" prefixOverrides=",">

<if test="aFlag!= null and aFlag!= ''.toString()">

, A_FLAG = #{aFlag}

</if>

<if test="bFlag!= null and bFlag!= ''.toString()">

, B_FLAG = #{bFlag}

</if>

<if test="cFlag!= null and cFlag!= ''.toString()">

, C_FLAG = #{cFlag}

</if>

</trim>

WHERE KEY = #{key}

</update>

권장사항이다. 이것을 이해하면 당신의 어플리케이션이 더 나은 성능을 발휘할 것이다.

다만 이것이 사람의 실력을 판단하는 척도로 사용되서는 안 될 것이다.

작게 생각하기

- 조만간 규모가 커질거라면 MySQL ecosystem을 봐야된다.
- 그리고 캐싱 빡시게 안 하는 메이저 웹사이트는 없다.
- develooper.com의 Hansen PT랑 Ilia 튜토리얼 볼 것
- 처음부터 확장 가능하게 아키텍처 잘 쪼개놔야된다.
- 복제랑 파티셔닝 어떻게 할지 미리 계획 세워놔라.
- 파일 기반 세션 좀 쓰지마 -_-
- 그렇다고 너무 쓸데없이 크게 생각하지도 말 것
- 특히 성능하고 확장성 구분 못 하면 난감함

EXPLAIN 안 써보기

- SELECT 앞에 EXPLAIN 이라고 붙이기만 하면 되는 것을 (..)
- 실행 계획 확인
- 타입 컬럼에 index 써있는거랑 Extra 컬럼에 index 써있는거랑 “매우 큰” 차이 있음
* 타입에 있으면 Full 인덱스 스캔 (안 좋다.)
* Extra 컬럼에 있으면 Covering 인덱스 찾았다는 의미임 (좋다!)
- 5.0 이후부터는 index_merge 최적화도 한다.

잘못된 데이터 타입 선택

- 한 메모리 블럭 단위에 인덱스 레코드가 많이 들어갈수록 쿼리가 빨리 실행될 것이다. (중요)
- 아.. 정규화 좀 해 -_-… (이거 정말 충격과 공포인 듯)
- 가장 작은 데이터 타입을 써.. (진짜 BIGINT가 필요하냐고..)
- 인덱스 걸리는 필드는 정말 최소한으로 데이터 크기를 써야된다고.
- IP는 INT UNSIGNED로 저장해!! (아주 공감)
* 이럴 때 쓰라고 INET_ATON 함수가 아예 내장되어 있음.

PHP에서 pconnect 쓰는 짓

- 아파치에서 좀비 프로세스라도 생기면 그 커넥션은 그냥 증발하는거야..
- 어차피 MySQL 접속 속도는 Oracle이나 PostgreSQL 보다 10~100배 빠르다고.

너무 과도한 DB 추상화 계층을 두는 것
- 어디 포팅 열심히 할 거 아니면 추상화 계층 쓰지마 (ADODB, MDB2, PearDB 등)
- scale out 가능한걸 쓰라고.

스토리지 엔진 이해 못 하는 것

- 단일 엔진만으로 전체 아키텍처를 결정했다면 대부분 최적이 아님
- 엔진 별 장단점을 공부할 것
- ARCHIVE : zlib으로 압축해주고 UPDATE 안 되고 로그 Bulk Insert에 유용함.
- MEMORY : 서버 재시작하면 증발. 인덱스가 HASH나 BTREE로 가능함. 임시, 요약 데이터에 사용.
* 주간 top X 테이블 같은 것.
* 하여튼 메모리에 박아넣고 싶은 데이터 있으면..

인덱스 레이아웃 이해 못 하는 것

- 제대로 인덱스랑 스토리지 엔진 선택하려면 공부 좀 해
- 엔진은 데이터와 인덱스 레코드를 메모리나 디스크에 레이아웃하는 걸 구현한 것
- clustered 구성은 데이터를 PK 순서에 따라 저장함.
- non-clustered 구성은 인덱스만 순서대로 저장하고 데이터는 순서 가정하지 않음.
- clustered에서는 인덱스만 타면 추가적인 조회 없이 바로 데이터 가져오는 것임.
- 그래서 clustered PK는 작은 놈으로 할 필요가 있다는거
* 다른 인덱스는 각 레코드마다 PK를 앞에 더 붙이게 되니까.
* PK 지정 안 하면 아무렇게나 해버림

쿼리 캐시 이해 못 하는 것

- 어플리케이션 read/write 비율은 알고 있어야지
- 쿼리 캐시 설계는 CPU 사용과 읽기 성능 간의 타협
- 쿼리 캐시 크기를 늘린다고 읽기 성능이 좋아지는게 아님. heavy read라도 마찬가지.
- 과도한 CPU 사용을 막기 위해 무효화 할 때는 캐시 항목들을 뭉텅이로 날려버림
- 한마디로 SELECT가 참조하는 테이블 데이터 하나라도 변경되면 그 테이블 캐시는 다 날라간다는 얘기임
- 수직 테이블 파티셔닝으로 처방
* Product와 ProductCount를 쪼갠다든지..
* 자주 변하는 것과 변하지 않는 것을 쪼개는게 중요하다 이 말임.

Stored Procedure를 쓰는 것

- 무조건 쓰면 안 된다는게 아니고..
- 컴파일 할 때 무슨 일이 일어나는지 이해 못 하고 쓰면 재앙이 된다 이 말.
- 다른 RDBMS랑 다르게 connection thread에서 실행 계획이 세워짐.
- 이게 뭔 얘기냐 하면 데이터 한 번 가져오고 연결 끊으면 그냥 CPU 낭비 (7~8% 정도)하는 꼴이라는 것.
- 웬만하면 Prepared 구문과 Dynamic SQL을 써라.. 아래 경우를 제외하고
* ETL 타입 프로시저
* 아주아주 복잡하지만 자주 실행되지는 않는 것
* 한 번 요청할 때마다 여러번 실행되는 간단한 것 (연결한 상태로 여러번 써야 된다니까)

인덱스 컬럼에 함수 쓰는 것

- 함수에 인덱스 컬럼 넣어 호출하면 당연히 인덱스 못 탄다
- 함수를 먼저 계산해서 상수로 만든 다음에 = 로 연결해야 인덱스 탈 수 있다.
* 여기 실행 계획 보면 LIKE도 range type 인덱스 타는 것 보임

인덱스 빼먹거나 쓸모없는 인덱스 만들어 놓는 것

- 인덱스 분포도(selectivity)가 허접하면 안 쓴다.
- S = d/n
* d = 서로 다른 값의 수 (# of distinct values)
* n = 테이블의 전체 레코드 수
- 쓸모없는 인덱스는 INSERT/UPDATE/DELETE를 느리게 할 뿐..
- FK는 무조건 인덱스 걸어라. (물론 FK 제약 걸면 인덱스 자동으로 생긴다.)
- WHERE나 GROUP BY 표현식에서 쓰이는 컬럼은 인덱스 추가를 고려할 것
- covering index 사용을 고려할 것
- 인덱스 컬럼 순서에 유의할 것!

join 안 쓰는 짓

- 서브쿼리는 join으로 재작성해라
- 커서 제거해라
- 좋은 Mysql 성능을 내려면 기본
- 집합 기반으로 생각해야지 루프 돌리는거 생각하면 안 된다.

Deep Scan 고려하지 않는 것

- 검색엔진 크러울러가 쓸고 지나갈 수 있다.
- 이 경우 계속해서 전체 집합을 정렬한 다음 LIMIT로 가져와야 하니 무진장 느려진다.
- 어떻게든 집합을 작게 줄인 다음 거기서 LIMIT 걸어 가져올 것

InnoDB 테이블에서 WHERE 조건절 없이 SELECT COUNT(*) 하는 짓

- InnoDB 테이블에서는 조건절 없이 COUNT(*) 하는게 느리다.
- 각 레코드의 transaction isolation을 유지하는 MVCC 구현이 복잡해서 그렇다는..
- 트리거 걸어서 메모리 스토리지 엔진 쓰는 테이블에 통계를 별도로 유지하면 된다.

프로파일링이나 벤치마킹 안 하는 것

- 프로파일링 : 병목 찾아내기
- 벤치마킹 : 시간에 따른 성능 변화 추이 평가, 부하 견딜 수 있는지 테스트
- 프로파일링 할 때는 실제 데이터를 옮겨와서 할 것
- 어디가 병목이냐~ Memory? Disk I/O? CPU? Network I/O? OS?
- 느린 쿼리 로그로 남기기
* log_slow_queries=/path/to/log
* log_queries_not_using_indexes
- 벤치마킹 시에는 다 고정시키고 변수 하나만 바꿔가면서 해야 함. (쿼리 캐시는 끌 것.)
- 도구를 써라~~
* EXPLAIN
* SHOW PROFILE
* MyTop/innotop
* mysqlslap
* MyBench
* ApacheBench (ab)
* super-smack
* SysBench
* JMeter/Ant
* Slow Query Log

AUTO_INCREMENT 안 쓰는 것

- PK를 AUTO_INCREMENT로 쓰는건 무진장 최적화 되어 있음
* 고속 병행 INSERT 가능
* 잠금 안 걸리고 읽으면서 계속 할 수 있다는!
- 새 레코드를 근처에 놓음으로써 디스크와 페이지 단편화를 줄임
- 메모리와 디스크에 핫 스팟을 생성하고 스와핑을 줄임

ON DUPLICATE KEY UPDATE를 안 쓰는 것

- 레코드가 있으면 업데이트하고 없으면 인서트하고 이런 코드 필요없다!! 다 날려버려라!!
- 서버에 불필요하게 왔다갔다 할 필요가 없어짐
- 5-6% 정도 빠름
- 데이터 입력이 많다면 더 커질 수 있음

하지 말아야 할 것 총정리
Thinking too small
Not using EXPLAIN
Choosing the wrong data types
Using persistent connections in PHP
Using a heavy DB abstraction layer
Not understanding storage engines
Not understanding index layouts
Not understanding how the query cache works
Using stored procedures improperly
Operating on an indexed column with a function
Having missing or useless indexes
Not being a join-fu master
Not accounting for deep scans
Doing SELECT COUNT(*) without WHERE on an InnoDB table
Not profiling or benchmarking
Not using AUTO_INCREMENT
Not using ON DUPLICATE KEY UPDATE

출처: https://blog.lael.be/post/370

개요

 MySQL 쿼리 옵티마이저는 쿼리를 실행할때 최적의 계획을 세운다. 그 계획을 Database용어로 '실행계획'(Query Plan)이라고 하는데, MySQL에서는 'EXPLAIN' 키워드를 이용해 실행계획에 대한 정보를 살펴 볼 수 있다.

 이는 이슈가 발생하는 문제의 쿼리를 이해하고, 어떻게 최적화 할지에대한 insight를 제공하는 매우 강력한 도구가 될 수 있는데, 불행하게도 이를 잘 사용하는 개발자는 별로 없는것 같다. (나 포함 ㅠ)

Understanding EXPLAIN's Output

'EXPLAIN' 을 사용하면 쿼리를 실행하기전에 실행계획을 분석하여 출력한다. 아래와 같은 예를 들어보자

EXPLAIN SELECT * FROM categories

********************** 1. row **********************
           id: 1
  select_type: SIMPLE
        table: categories
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
        Extra: 
1 row in set (0.00 sec)

이제부터 해당 항목들에대해 자세히 살펴보도록 한다

• id : 쿼리 안에 있는 각 select 문에 대한 순차 식별자이다. 이순서대로 select문이 실행된다고 생각하면 된다.
• select_type : select 문의 유형을 말한다. 각 유형은 아래와 같다
• SIMPLE : 서브쿼리나 'union'이 없는 가장 단순한 select문을 말한다
• PRIMARY : 가장 바깥에 있는 select 문을 말한다
• DERIVED : from 문 안에있는 서브쿼리의 select 문이다.
• SUBQUERY : 가장 바깥의 select 문에 있는 서브쿼리이다.
• DEPENDENT SUBQUERY : 기본적으로 SUBQUERY와 같은 유형이며, 가장 바깥의 select문에 '의존성'을 가진 서브쿼리의 select문이다.
• UNCACHEABLE SUBQUERY
• UNION : union 문의 두번째 select 문을 말한다
• DEPENDENT UNION : 바깥 쿼리에 의존성을 가진 union문의 두번째 select문을 말한다
• table : 참조되는 테이블을 말한다
• type : MySQL이 어떤식으로 테이블들을 조인하는지를 나타내는 항목이다. 이는 매우 중요한데, 이유는 이 타입을 분석함으로써 어떤 인덱스가 사용되고 사용되지 않았는지를 알 수 있고, 이를통해 어떤식으로 쿼리가 튜닝되어야하는지에 대한 insight를 제공하기 때문이다. 각 유형은 아래와 같다
• system : 0개 또는 하나의 row를 가진 테이블이다.
• const : 테이블에 조건을 만족하는 레코드가 하나일 때, 상수 취급
• eq_ref : primary key나 unique not null column으로 생성된 인덱스를 사용해 조인을 하는 경우이다. const 방식 다음으로 빠른 방법이다.
• ref : 인덱스로 지정된 컬럼끼리의 '=' , '<=>' 와 같은 연산자를 통한 비교로 수행되는 조인이다
• index_merge 
• unique_subquery : 오직 하나의 결과만을 반환하는 'IN'이 포함된 서브쿼리의 경우이다.
• index_subquery : unique_subquery와 비슷하지만 여러개의 결과를 반환한다
• range : 특정한 범위의 rows들을 매칭시키는데 인덱스가 사용된 경우이다. BETWEEN이나 IN, '>', '>=' 등이 사용될 때이다.
• all : 조인시에 모든 테이블의 모든 row를 스캔하는경우이다. 물론 성능이 가장 좋지 않다.
• possible_keys : 테이블에서 row를 매핑시키기 위해 사용 가능한 (사용하지 않더라도) 키를 보여준다.  
• key : 실제적으로 쿼리 실행에 사용된 key의 목록이다. 이 항목에는 possible_keys 목록에 나타지 않은 인덱스도 포함 될 수 있다.
• ref : key column에 지정된 인덱스와 비교되는 column 또는 constants를 보여준다.
• rows : 결과 산출에 있어서 접근되는 record의 숫자이다. 조인문이나 서브쿼리 최적화에 있어서 중요한 항목이다.
• Extra : 실행계획에 있어서 부가적인 정보를 보여준다.

• http://dev.mysql.com/doc/refman/5.6/en/explain-output.html#explain-extra-information

내컴퓨터 우클릭 - 관리 - 서비스 설정

에서 mysql stop.

> mysqld --skip-grant

새 cmd 창을 열어 mysql 서버 를 Start 하지 않은 상태에서

> mysql -uroot

or

> mysql

로 진입

mysql> update user set password = password('new-password') where user = 'root';

mysql> flush privileges;

후에

net start mysql DB 다시 가동.

새 cmd 창에서 mysql -uroot -p
비밀번호 new-password 로 접속

오라클이 제공하는 힌트가 너무 강력해서 가끔 기본적인 것을 지나칠 때가 있습니다. 가끔은 힌트가 없다면 어떻게 실행 계획을 제어할 수 있을까를 고민해보면 재미있는 발견을 할 때가 있습니다.

예를 들어 아래와 같은 5개의 테이블이 있습니다.

create table t1(c1, c2)
as 
select
	 level, level
from dual
connect by level <= 5000;

create table t2(c1, c2)
as 
select
	 level, level
from dual
connect by level <= 4000;

create table t3(c1, c2)
as 
select
	 level, level
from dual
connect by level <= 3000;

create table t4(c1, c2)
as 
select
	 level, level
from dual
connect by level <= 2000;

create table t5(c1, c2)
as 
select
	 level, level
from dual
connect by level <= 1000;

create index t1_n1 on t1(c1);
create index t2_n1 on t2(c1);
create index t3_n1 on t3(c1);
create index t4_n1 on t4(c1);
create index t5_n1 on t5(c1);

exec dbms_stats.gather_table_stats(user, 't1');
exec dbms_stats.gather_table_stats(user, 't2');
exec dbms_stats.gather_table_stats(user, 't3');
exec dbms_stats.gather_table_stats(user, 't4');
exec dbms_stats.gather_table_stats(user, 't5');

테이블 T1, T2, T3, T4, T4를 조인하는 쿼리에서 조인 순서는 어떻게 될까요? 테이블의 데이터 분포를 보면 T1(c1 between 1 and 10 조건 때문에)이 드라이빙 테이블이 되고 그 다음은 T5(1000건), T4(2000건), T3(3000건), T2(4000건)이 됨을 알 수 있습니다.

explain plan for
select 
	*
from
	t1, t2, t3, t5, t4
where
	t1.c1 = t2.c1
	and t1.c1 = t3.c1
	and t1.c1 = t4.c1
	and t1.c1 = t5.c1
	and t1.c2 between 1 and 10
;

--------------------------------------
| Id  | Operation             | Name |
--------------------------------------
|   0 | SELECT STATEMENT      |      |
|*  1 |  HASH JOIN            |      |
|*  2 |   HASH JOIN           |      |
|*  3 |    HASH JOIN          |      |
|*  4 |     HASH JOIN         |      |
|*  5 |      TABLE ACCESS FULL| T1   |
|   6 |      TABLE ACCESS FULL| T5   |
|   7 |     TABLE ACCESS FULL | T4   |
|   8 |    TABLE ACCESS FULL  | T3   |
|   9 |   TABLE ACCESS FULL   | T2   |
--------------------------------------

만일 조인 순서를 T1 -> T2 -> T3 -> T4 -> T5로 만들고 싶다면? 아래와 같이 ORDERED 힌트를 사용하면 되겠죠? 또는 LEADING(t1 t2 t3 t4 t5) 힌트를 사용해도 됩니다.

explain plan for
select /*+ ordered */
	*
from
	t1, t2, t3, t5, t4
where
	t1.c1 = t2.c1
	and t1.c1 = t3.c1
	and t1.c1 = t4.c1
	and t1.c1 = t5.c1
	and t1.c2 between 1 and 10
;

--------------------------------------
| Id  | Operation             | Name |
--------------------------------------
|   0 | SELECT STATEMENT      |      |
|*  1 |  HASH JOIN            |      |
|*  2 |   HASH JOIN           |      |
|*  3 |    HASH JOIN          |      |
|*  4 |     HASH JOIN         |      |
|*  5 |      TABLE ACCESS FULL| T1   |
|   6 |      TABLE ACCESS FULL| T2   |
|   7 |     TABLE ACCESS FULL | T3   |
|   8 |    TABLE ACCESS FULL  | T4   |
|   9 |   TABLE ACCESS FULL   | T5   |
--------------------------------------

만일 힌트를 사용하지 않고(그리고 RBO가 아닌 CBO라고 하면), 조인 순서가 항상 위와 같이 나오게 하고 싶다면 어떻게 해야될까요? 힌트에 익숙해지다보면 이런 간단한 질문에 대한 답이 선뜻 나오지 않는 경우가 많습니다.

가장 전형적인 방법은 아래와 같이 t1.c1 = t3.c1 조인 조건을 t1.c1 + 0*t2.c1 = t3.c1 같은 형태로 사용하는 것입니다. t3.c1에서 조인이 이루어지려면 t2.c1 값을 알아야 하므로 반드시 T2 -> T3 순서로 조인이 됩니다. 비슷한 원리로 조건절을 만들어가면 됩니다.

explain plan for
select 
	*
from
	t1, t2, t3, t4, t5
where
	t1.c1 = t2.c1
	and t1.c1 + 0*t2.c1 = t3.c1  -- t2.c1 값을 알아야 t3.c1 조인 가능
	and t1.c1 + 0*t3.c1= t4.c1  -- t3.c1 값을 알아야 t4.c1 조인 가능
	and t1.c1 + 0*t4.c1 = t5.c1  -- t4.c1 값을 알아야 t5.c1 조인 가능
	and t1.c2 between 1 and 10
;

--------------------------------------
| Id  | Operation             | Name |
--------------------------------------
|   0 | SELECT STATEMENT      |      |
|*  1 |  HASH JOIN            |      |
|*  2 |   HASH JOIN           |      |
|*  3 |    HASH JOIN          |      |
|*  4 |     HASH JOIN         |      |
|*  5 |      TABLE ACCESS FULL| T1   |
|   6 |      TABLE ACCESS FULL| T2   |
|   7 |     TABLE ACCESS FULL | T3   |
|   8 |    TABLE ACCESS FULL  | T4   |
|   9 |   TABLE ACCESS FULL   | T5   |
--------------------------------------

위의 방법을 응용하면 조인 순서를 자유롭게 제어할 수 있습니다. 예를 들어 조인 순서를 T1 -> T2 -> T4 -> T3 -> T5(T4가 T3보다 먼저 조인되게)로 만들려면 어떻게 해야할까요?

explain plan for
select 
	*
from
	t1, t2, t3, t4, t5
where
	t1.c1 = t2.c1
	{ 여기에 어떤 조건이 들어가야 아래의 실행 계획이 나올까요? }
	and t1.c2 between 1 and 10
;

--------------------------------------
| Id  | Operation             | Name |
--------------------------------------
|   0 | SELECT STATEMENT      |      |
|*  1 |  HASH JOIN            |      |
|*  2 |   HASH JOIN           |      |
|*  3 |    HASH JOIN          |      |
|*  4 |     HASH JOIN         |      |
|*  5 |      TABLE ACCESS FULL| T1   |
|   6 |      TABLE ACCESS FULL| T2   |
|   7 |     TABLE ACCESS FULL | T4   |
|   8 |    TABLE ACCESS FULL  | T3   |
|   9 |   TABLE ACCESS FULL   | T5   |
--------------------------------------

정답은 이미 공개된 것이나 마찬가지이기 때문에 여기에 적지는 않겠습니다. 혹시 잠깐 시간내서 도전해보고 싶은 분이 있으면 댓글로 남기셔도 좋겠습니다.

그래도 역시 힌트가 편하긴 편합니다!

출처: http://ukja.tistory.com/350 [오라클 성능 문제에 대한 통찰 - 조동욱]

1. Nested Loop Join

가. 기본 메커니즘

프로그래밍을 해 본 독자라면 누구나 아래 중첩 루프문(Nested Loop)의 수행 구조를 이해할 것이고, 그렇다면 Nested Loop Join(이하 NL Join)도 어렵지 않게 이해할 수 있다.

위 중첩 루프문과 같은 수행 구조를 사용하는 NL Join이 실제 어떤 순서로 데이터를 액세스하는지 아래 PL/SQL문이 잘 설명해 준다.

위 PL/SQL문은 아래 쿼리와 100% 같은 순서로 데이터를 액세스하고, 데이터 출력순서도 같다. 내부적으로(=Recursive하게) 쿼리를 반복 수행하지 않는다는 점만 다르다.

사실 뒤에서 설명할 Sort Merge Join과 Hash Join도 각각 소트 영역(Sort Area)과 해시 영역(Hash Area)에 가공해 둔 데이터를 이용한다는 점만 다를 뿐 기본적인 조인 프로세싱은 다르지 않다.

나. NL Join 수행 과정 분석

이제 NL Join의 기본 메커니즘을 이해했으므로 아래 조인문에서 조건절 비교 순서가 어떻게 되는지 분석해 보자.

인덱스 상황은 다음과 같다.

조건절 비교 순서, 그리고 위 5개 인덱스 중 어떤 것이 사용될지도 함께 고민해 보기 바란다.

사용되는 인덱스는 dept_loc_idx와 emp_deptno_idx 인 것을 위 실행계획을 보고 알 수 있다. 그럼 조건비교 순서는? SQL 조건절에 표시한 번호로 ② → ③ → ① → ④ 순이다. 실행계획을 해석할 때, 형제(Sibling) 노드 간에는 위에서 아래로 읽는다. 부모-자식(Parent-Child) 노드 간에는 안쪽에서 바깥쪽으로, 즉 자식 노드부터 읽는다. 위 실행계획의 실행 순서를 나열하면 다음과 같다.

1. dept_loc_idx 인덱스 범위 스캔(ID = 4) 2. 인덱스 rowid로 dept 테이블 액세스(ID = 3) 3. emp_deptno_idx 인덱스 범위 스캔(ID = 6) 4. 인덱스 rowid로 emp 테이블 액세스(ID = 5) 5. sal 기준 내림차순(desc) 정렬(ID = 1)

위 실행계획을 그림으로써 표현해 보면 [그림 Ⅲ-4-26]과 같다.

[그림 Ⅲ-4-26]을 해석할 때는, 형제 노드 간에는 좌에서 우로 읽고, 부모-자식 노드 간에는 아래에서 위쪽으로, 즉 자식 노드부터 읽는다.

1. dept.loc = ‘SEOUL’ 조건을 만족하는 레코드를 찾으려고 dept_loc_idx 인덱스를 범위 스캔한다. 2. dept_loc_idx 인덱스에서 읽은 rowid를 가지고 dept 테이블을 액세스해 dept.gb = ‘2’ 필터 조건을 만족하는 레코드를 찾는다. 3. dept 테이블에서 읽은 deptno 값을 가지고 조인 조건을 만족하는 emp 쪽 레코드를 찾으려고 emp_deptno_idx 인덱스를 범위 스캔한다. 4. emp_deptno_idx 인덱스에서 읽은 rowid를 가지고 emp 테이블을 액세스해 sal >= 1500 필터 조건을 만족하는 레코드를 찾는다. 5. 1~4 과정을 통과한 레코드들을 sal 칼럼 기준 내림차순(desc)으로 정렬한 후 결과를 리턴한다.

여기서 기억할 것은, 각 단계를 완료하고 나서 다음 단계로 넘어가는 게 아니라 한 레코드씩 순차적으로 진행한다는 사실이다. 단, order by는 전체 집합을 대상으로 정렬해야 하므로 작업을 모두 완료하고서 다음 오퍼레이션을 진행한다. 아래는 SQL Server에서의 실행계획이다.

SQL Server에서 제공하는 그래픽 모드 실행계획은 [그림 Ⅲ-4-27]과 같다.

[그림 Ⅲ-4-28]을 보면 지금까지 설명한 NL Join의 수행 절차를 좀 더 명확히 이해할 수 있다.

11, 19, 31, 32는 스캔할 데이터가 더 있는지 확인하는 one-plus 스캔을 표시한 것이다. (O)는 테이블 필터 조건에 의해 레코드가 걸러지지 않은 것을 의미하고, 반대로 (X)는 테이블 필터 조건에 의해 걸러진 것을 의미한다. [그림 Ⅲ-4-28]을 보면서, dept_loc_idx 인덱스를 스캔하는 양에 따라 전체 일량이 좌우됨을 이해하기 바란다. 여기서는 단일 칼럼 인덱스를 ‘=’ 조건으로 스캔했으므로 비효율 없이 6(=5+1)건을 읽었고, 그만큼 테이블 Random 액세스가 발생했다. 우선 이 부분이 NL Join의 첫 번째 부하지점이다. 만약 dept 테이블로 많은 양의 Random 액세스가 있었는데 gb = ‘2’ 조건에 의해 필터링되는 비율이 높다면 어떻게 해야 할까? 이미 1장에서 배웠듯이 dept_loc_idx에 gb 칼럼을 추가하는 방안을 고려해야 한다. 두 번째 부하지점은 emp_deptno_idx 인덱스를 탐색하는 부분이며, Outer 테이블인 dept를 읽고 나서 조인 액세스가 얼만큼 발생하느냐에 의해 결정된다. 이것 역시 Random 액세스에 해당하며, [그림 Ⅲ-4-28]에서는 gb = ‘2’ 조건을 만족하는 건수만큼 3번의 조인시도가 있었다. 만약 emp_deptno_idx의 높이(height)가 3이면 매 건마다 그만큼의 블록 I/O가 발생하고, 리프 블록을 스캔하면서 추가적인 블록 I/O가 더해진다. 세 번째 부하지점은 emp_deptno_idx를 읽고 나서 emp 테이블을 액세스하는 부분이다. 여기서도 sal >= 1500 조건에 의해 필터링되는 비율이 높다면 emp_deptno_idx 인덱스에 sal 칼럼을 추가하는 방안을 고려해야 한다. OLTP 시스템에서 조인을 튜닝할 때는 일차적으로 NL Join부터 고려하는 것이 올바른 순서다. 우선, NL Join 메커니즘을 따라 각 단계의 수행 일량을 분석해 과도한 Random 액세스가 발생하는 지점을 파악한다. 조인 순서를 변경해 Random 액세스 발생량을 줄일 수 있는 경우가 있지만, 그렇지 못할 때는 인덱스 칼럼 구성을 변경하거나 다른 인덱스의 사용을 고려해야 한다. 여러 가지 방안을 검토한 결과 NL Join이 효과적이지 못하다고 판단될 때 Hash Join이나 Sort Merge Join을 검토한다.

다. NL Join의 특징

대부분 DBMS가 블록(또는 페이지) 단위로 I/O를 수행하는데, 하나의 레코드를 읽으려고 블록을 통째로 읽는 Random 액세스 방식은 설령 메모리 버퍼에서 빠르게 읽더라도 비효율이 존재한다. 그런데 NL Join의 첫 번째 특징이 Random 액세스 위주의 조인 방식이라는 점이다. 따라서 인덱스 구성이 아무리 완벽하더라도 대량의 데이터를 조인할 때 매우 비효율적이다. 두 번째 특징은, 조인을 한 레코드씩 순차적으로 진행한다는 점이다. 첫 번째 특징 때문에 대용량 데이터 처리 시 매우 치명적인 한계를 드러내지만, 반대로 이 두 번째 특징 때문에 아무리 대용량 집합이더라도 매우 극적인 응답 속도를 낼 수 있다. 부분범위처리가 가능한 상황에서 그렇다. 그리고 순차적으로 진행하는 특징 때문에 먼저 액세스되는 테이블의 처리 범위에 의해 전체 일량이 결정된다. 다른 조인 방식과 비교했을 때 인덱스 구성 전략이 특히 중요하다는 것도 NL Join의 중요한 특징이다. 조인 칼럼에 대한 인덱스가 있느냐 없느냐, 있다면 칼럼이 어떻게 구성됐느냐에 따라 조인 효율이 크게 달라진다. 이런 여러 가지 특징을 종합할 때, NL Join은 소량의 데이터를 주로 처리하거나 부분범위처리가 가능한 온라인 트랜잭션 환경에 적합한 조인 방식이라고 할 수 있다.

2. Sort Merge Join

NL Join은 조인 칼럼을 선두로 갖는 인덱스가 있는지가 매우 중요하다. 만약 조인 칼럼을 선두로 갖는 인덱스가 없으면 Outer 테이블에서 읽히는 건마다 Inner 테이블 전체를 스캔하기 때문이다. 그럴 때 옵티마이저는 Sort Merge Join이나 다음 절에서 설명할 Hash Join을 고려한다. Sort Merge Join은 이름이 의미하는 것처럼 두 테이블을 각각 정렬한 다음에 두 집합을 머지(Merge)하면서 조인을 수행한다. Sort Merge Join은 아래 두 단계로 진행된다.

① 소트 단계 : 양쪽 집합을 조인 칼럼 기준으로 정렬한다. ② 머지 단계 : 정렬된 양쪽 집합을 서로 머지(merge)한다.

만약 조인 칼럼에 인덱스가 있으면(Oracle의 경우 Outer 테이블에만 해당) ①번 소트 단계를 거치지 않고 곧바로 조인할 수도 있다. Oracle은 조인 연산자가 부등호이거나 아예 조인 조건이 없어도 Sort Merge Join으로 처리할 수 있지만, SQL Server는 조인 연산자가 ‘=’ 일 때만 Sort Merge Join을 수행한다는 사실에도 유념하기 바란다.

가. 기본 메커니즘

아래 SQL은 dept 테이블을 기준으로 emp 테이블과 조인할 때 Sort Merge Join 방식을 사용하라고 힌트로 지시하고 있다.

Sort Merge Join의 수행 과정을 그림으로 도식화하면 [그림 Ⅲ-4-29]와 같다.

[그림 Ⅲ-4-29]에서 주목할 점은, Inner 집합인 emp 테이블이 정렬돼 있기 때문에 조인에 실패하는 레코드를 만나는 순간 멈출 수 있다는 사실이다. 예를 들어, deptno=10인 레코드를 찾기 위해 ①번 스캔을 진행하다가 20을 만나는 순간 멈춘다. 또 한 가지는, 정렬된 emp에서 스캔 시작점을 찾으려고 매번 탐색하지 않아도 된다는 점이다. 예를 들어, deptno=20인 레코드를 찾는 ②번 스캔은 ①번에서 스캔하다가 멈춘 지점을 기억했다가 거기서부터 시작하면 된다. Outer 집합인 dept 테이블도 같은 순서로 정렬돼 있기 때문에 가능한 일이다. 아래는 Sort Merge Join이 머지하는 방식을 pseudo 코드로 작성한 것이다.

[그림 Ⅲ-4-29]와 위 pseudo 코드를 잘 살펴보면, 실제 조인 수행 과정이 NL Join과 크게 다르지 않다. outer 집합과 inner 집합을 미리 정렬해 둔다는 점만 다르다. 다시 말하지만, 양쪽 집합을 먼저 정렬해 두었기 때문에 위와 같은 처리 로직이 가능하다.

나. Sort Merge Join의 특징

Sort Merge Join은 다음과 같은 특징을 가진다.

• 조인 하기 전에 양쪽 집합을 정렬한다.

NL Join은 정렬 없이 Outer 집합을 한 건씩 차례대로 조인을 진행하지만, Sort Merge Join은 양쪽 집합을 조인 칼럼 기준으로 정렬한 후에 조인을 시작한다. 대량 집합 조인은 Random 액세스 위주의 NL Join의 경우 비효율이 있고, 이 비효율을 줄이고자 나온 조인 방식이 Sort Merge Join이다. 만약 정렬해야 할 집합이 초대용량 테이블이면 정렬 자체가 큰 비용을 수반하기 때문에 성능 개선 효과를 얻지 못할 수도 있다. 하지만, 일반 인덱스나 클러스터형 인덱스처럼 미리 정렬된 오브젝트를 이용하면 정렬작업을 하지 않고 바로 조인을 수행할 수 있어 Sort Merge Join이 좋은 대안이 될 수 있다.

• 부분적으로, 부분범위처리가 가능하다.

Sort Merge Join은 양쪽 집합을 정렬해야 함으로 부분범위처리가 불가능할 거 같지만, 부분적으로는 가능하다. Outer 집합이 조인 칼럼 순으로 미리 정렬된 상태에서 사용자가 일부 로우만 Fetch 하다가 멈춘다면 Outer 집합은 끝까지 읽지 않아도 되기 때문이다.

• 테이블별 검색 조건에 의해 전체 일량이 좌우된다.

NL Join은 Outer 집합의 매 건마다 Inner 집합을 탐색한다. Outer 집합에서 조인 대상이 되는 건수에 의해 전체 일량이 좌우되는 이유다. 그러나 Sort Merge Join은 두 집합을 각각 정렬한 후에 조인함으로 각 집합의 크기, 즉 테이블별 검색 조건에 의해 전체 일량이 좌우된다.

• 스캔(Scan) 위주의 조인 방식이다.

NL Join이 Random 액세스 위주의 조인 방식이라면 Sort Merge Join은 스캔 위주의 조인 방식이다. Inner 테이블을 반복 액세스하지 않으므로 머지 과정에서 Random 액세스가 발생하지 않는 것이다. 하지만, Random 액세스가 전혀 없는 것은 아니다. 각 테이블 검색 조건에 해당하는 대상 집합을 찾을 때 인덱스를 이용한 Random 액세스 방식으로 처리될 수 있고, 이때 발생하는 Random 액세스량이 많다면 Sort Merge Join의 이점이 사라질 수 있다.

3. Hash Join

가. 기본 메커니즘

Hash Join은 NL Join이나 Sort Merge Join이 효과적이지 못한 상황을 해결하고자 나온 조인 방식이다. 아래는 Oracle과 SQL Server 각각에서 Hash Join으로 유도했을 때의 실행계획이다.

Hash Join은 둘 중 작은 집합(Build Input)을 읽어 해시 영역(Hash Area)에 해시 테이블(= 해시 맵)을 생성하고, 반대쪽 큰 집합(Probe Input)을 읽어 해시 테이블을 탐색하면서 조인하는 방식이다.([그림 Ⅲ-4-30] 참조)

해시 함수는, 출력값을 미리 알 순 없지만, 같은 입력값에 대해 같은 출력값을 보장하는 함수다. 다른 입력값에 대한 출력값이 같을 수는 있는데, 이를 ‘해시 충돌’이라고 한다. 해시 테이블을 만들 때 해시 충돌이 발생하면, 입력값이 다른 엔트리가 한 해시 버킷에 담길 수 있다. 이런 원리를 바탕으로 Hash Join 과정을 좀 더 자세히 살펴보자.

• 1단계 : 해시 테이블 생성두 집합 중 작다고 판단되는 집합을 읽어 해시 테이블을 만든다. 해시 테이블을 만들 때 해시 함수를 사용한다. 해시 테이블은 해시 버킷으로 구성된 배열이라고 생각하면 된다. 해시 함수에서 리턴받은 해시 값이 같은 데이터를 같은 해시 버킷에 체인(연결 리스트)으로 연결한다.
• 2단계 : Probe Input을 스캔해시 테이블 생성을 위해 선택되지 않은 나머지 데이터 집합(Probe Input)을 스캔한다.
• 3단계 : 해시 테이블 탐색Probe Input에서 읽은 데이터로 해시 테이블을 탐색할 때도 해시 함수를 사용한다. 즉, 해시 함수에서 리턴받은 버킷 주소로 찾아가 해시 체인을 스캔하면서 데이터를 찾는다.

Hash Join은, NL Join처럼 조인 과정에서 발생하는 Random 액세스 부하가 없고 Sort Merge Join처럼 조인 전에 미리 양쪽 집합을 정렬하는 부담도 없다. 다만, 해시 테이블을 생성하는 비용이 수반된다. 따라서 Build Input이 작을 때라야 효과적이다. 만약 Hash Build를 위해 가용한 메모리 공간을 초과할 정도로 Build Input이 대용량 테이블이면 디스크에 썼다가 다시 읽어 들이는 과정을 거치기 때문에 성능이 많이 저하된다. Build Input으로 선택된 테이블이 작은 것도 중요하지만 해시 키 값으로 사용되는 칼럼에 중복 값이 거의 없을 때라야 효과적이다. 이유는 잠시 후 자세히 설명한다. 해시 테이블을 만드는 단계는 전체범위처리가 불가피하지만, 반대쪽 Probe Input을 스캔하는 단계는 NL Join처럼 부분범위처리가 가능하다는 사실도 기억하자.

나. Build Input이 가용 메모리 공간을 초과할 때 처리 방식

Hash Join은 Hash Build를 위한 가용한 메모리 공간에 담길 정도로 Build Input이 충분히 작아야 효과적이라고 했다. 만약 In-Memory Hash Join이 불가능할 때 DBMS는 ‘Grace Hash Join’이라고 알려진 조인 알고리즘을 사용하는데, 이는 아래 두 단계로 나누어 진행된다.

1) 파티션 단계

조인되는 양쪽 집합(→ 조인 이외 조건절을 만족하는 레코드) 모두 조인 칼럼에 해시 함수를 적용하고, 반환된 해시 값에 따라 동적으로 파티셔닝을 실시한다. 독립적으로 처리할 수 있는 여러 개의 작은 서브 집합으로 분할함으로써 파티션 짝(pair)을 생성하는 단계다. 파티션 단계에서 양쪽 집합을 모두 읽어 디스크 상의 Temp 공간에 일단 저장해야 하므로 In-Memory Hash Join보다 성능이 크게 떨어지게 된다.

2) 조인 단계

파티션 단계가 완료되면 각 파티션 짝(pair)에 대해 하나씩 조인을 수행한다. 이때, 각각에 대한 Build Input과 Probe Input은 독립적으로 결정된다. 즉, 파티션하기 전 어느 쪽이 작은 테이블이었는지에 상관없이 각 파티션 짝(pair)별로 작은 쪽 파티션을 Build Input으로 선택해 해시 테이블을 생성한다. 해시 테이블이 생성되고 나면 반대 쪽 파티션 로우를 하나씩 읽으면서 해시 테이블을 탐색하며, 모든 파티션 짝에 대한 처리가 완료될 때까지 이런 과정을 반복한다. Grace Hash Join은 한마디로, 분할 정복(Divide & Conquer) 방식이라고 말할 수 있다. 실제로는 DBMS 벤더마다 조금씩 변형된 형태의 하이브리드(Hybrid) 방식을 사용하지만 두 개의 큰 테이블을 Hash Join하는 기본 알고리즘은 Grace Hash Join에 바탕을 두고 있다.

• Recursive Hash Join(=Nested-loops Hash Join)

디스크에 기록된 파티션 짝(pair)끼리 조인을 수행하려고 ‘작은 파티션’을 메모리에 로드하는 과정에서 또다시 가용 메모리를 초과하는 경우가 발생할 수 있다. 그럴 때는 추가적인 파티셔닝 단계를 거치게 되는데, 이를 ‘Recursive Hash Join’이라고 한다.

다. Build Input 해시 키 값에 중복이 많을 때 발생하는 비효율

잘 알다시피 해시 알고리즘의 성능은 해시 충돌(collision)을 얼마나 최소화할 수 있느냐에 달렸으며, 이를 방지하려면 그만큼 많은 해시 버킷을 할당해야만 한다. [그림 Ⅲ-4-30]에는 개념적으로 설명하기 위해 하나의 버킷에 여러 키 값이 달리는 구조로 표현하였지만, DBMS는 가능하면 충분히 많은 개수의 버킷을 할당함으로써 버킷 하나당 하나의 키 값만 갖게 하려고 노력한다. 그런데 해시 버킷을 아무리 많이 할당하더라도 해시 테이블에 저장할 키 칼럼에 중복 값이 많다면 하나의 버킷에 많은 엔트리가 달릴 수 밖에 없다. 그러면 해시 버킷을 아무리 빨리 찾더라도 해시 버킷을 스캔하는 단계에서 많은 시간을 허비하기 때문에 탐색 속도가 현저히 저하된다. Build Input의 해시 키 칼럼에는 중복 값이 (거의) 없어야 Hash Join이 빠르게 수행될 수 있음을 이해할 것이다.

라. Hash Join 사용기준

Hash Join 성능을 좌우하는 두 가지 키 포인트는 다음과 같다.

• 한 쪽 테이블이 가용 메모리에 담길 정도로 충분히 작아야 함
• Build Input 해시 키 칼럼에 중복 값이 거의 없어야 함

위 두 가지 조건을 만족할 때라야 Hash Join이 가장 극적인 성능 효과를 낼 수 있음을 앞에서 살펴보았다. 그러면 Hash Join을 언제 사용하는 것이 효과적인지 그 선택 기준을 살펴보자.

• 조인 칼럼에 적당한 인덱스가 없어 NL Join이 비효율적일 때
• 조인 칼럼에 인덱스가 있더라도 NL Join 드라이빙 집합에서 Inner 쪽 집합으로의 조인 액세스량이 많아 Random 액세스 부하가 심할 때
• Sort Merge Join 하기에는 두 테이블이 너무 커 소트 부하가 심할 때
• 수행빈도가 낮고 조인할 때

앞쪽 세 가지 사항은 앞에서 이미 설명한 내용이므로 생략하기로 하고, 마지막 항목을 강조하면서 Hash Join에 대한 설명을 마치려고 한다. Hash Join이 등장하면서 Sort Merge Join의 인기가 많이 떨어졌다고 했는데, 그만큼 Hash Join이 빠르기 때문이다. Hash Join이 워낙 빠르다 보니 모든 조인을 Hash Join으로 처리하려는 유혹에 빠지기 쉬운데, 이는 매우 위험한 생각이 아닐 수 없다. 수행시간이 짧으면서 수행빈도가 매우 높은 쿼리(→ OLTP성 쿼리의 특징이기도 함)를 Hash Join으로 처리한다면 어떤 일이 발생할까? NL Join에 사용되는 인덱스는 (Drop하지 않는 한) 영구적으로 유지되면서 다양한 쿼리를 위해 공유 및 재사용되는 자료구조다. 반면, 해시 테이블은 단 하나의 쿼리를 위해 생성하고 조인이 끝나면 곧바로 소멸하는 자료구조다. 따라서 수행빈도가 높은 쿼리에 Hash Join을 사용하면 CPU와 메모리 사용률을 크게 증가시킴은 물론, 메모리 자원을 확보하기 위한 각종 래치 경합이 발생해 시스템 동시성을 떨어뜨릴 수 있다. 따라서 Hash Join은 ①수행 빈도가 낮고 ②쿼리 수행 시간이 오래 걸리는 ③대용량 테이블을 조인할 때(→ 배치 프로그램, DW, OLAP성 쿼리의 특징이기도 함) 주로 사용해야 한다. OLTP 환경이라고 Hash Join을 쓰지 못할 이유는 없지만 이 세 가지 기준(①~③)을 만족하는지 체크해 봐야 한다

4. Scalar Subquery

쿼리에 내장된 또다른 쿼리 블록을 서브쿼리라고 하는데, 그 중에서 함수처럼 한 레코드당 정확히 하나의 값만을 리턴하는 서브쿼리를 ‘Scalar Subquery’라고 한다. Scalar Subquery는 주로 select-list에서 사용되지만 몇 가지 예외사항을 뺀다면 칼럼이 올 수 있는 대부분 위치에서 사용 가능하다.

Scalar Subquery를 사용한 위 쿼리 문장은 아래 Outer 조인문과 100% 같은 결과를 낸다. 즉, dept와 조인에 실패하는 emp 레코드가 있다면 dname으로 null 값이 출력된다.

위에서 예시한 쿼리는 결과만 같은 것이 아니라 조인을 수행하는 처리 경로도 동일한데, NL 방식으로 수행되도록 힌트를 사용했기 때문이다. 다만 Scalar Subquery에는 내부적으로 캐싱 기법이 작용된다는 점이 다르고, 이를 이용한 튜닝이 자주 행해진다.

가. Scalar Subquery의 캐싱 효과

아래 쿼리는 위치가 ‘CHICAGO’인 부서(dept)만 대상으로 급여 수준을 집계하려는 것인데, 사원(emp) 테이블 전체를 다 읽어야 하는 비효율이 있다.

아래와 같이 바꿀 수 있으면 좋겠지만 스칼라 서브쿼리는 한 레코드당 하나의 값만 리턴한다는 특징 때문에 그럴 수가 없다.

그렇다고 아래와 같이 쿼리한다면 emp에서 같은 범위를 반복적으로 액세스하는 비효율이 생긴다.

이럴 때, 아래 처럼 구하고자 하는 값들을 모두 결합하고서 바깥쪽 액세스 쿼리에서 substr 함수로 분리하는 방법이 유용하게 쓰인다.