0102 ~ 0108

# 0102 ~ 0108

# 0103 - Preparedstatement 사용 이유

# [ 성능 측면 ]

SQL 서버 엔진이 쿼리를 수행할 때 마다 다음의 과정을 거친다.

구문 분석 및 정규화 단계 : 쿼리 문법이 제대로 작성되었는지 확인하고 해당 테이블과 컬럼이 데이터베이스에 존재하는지 확인한다.
컴파일 단계 : 쿼리를 컴파일한다.
쿼리 최적화 계획 : 쿼리를 실행할 수 있는 방법의 수와 쿼리를 실행하는 각 방법의 비용을 알아내 최적의 계획을 선택한다.
캐시 : 쿼리 최적화 계획에서 선택된 계획은 캐시에 저장되므로 동일한 쿼리가 들어올때마다 1, 2, 3 단계를 다시 실행하지 않고 다음에 동일한 쿼리가 들어오면 Cache를 찾아 실행한다.
실행 단계 : 쿼리가 실행되고 데이터가 ResultSet 객체로 사용자에게 반환된다.

Statement는 쿼리를 실행할 때마다 1~5단계를 수행한다.

PreparedStatement는 완전한 SQL 쿼리가 아니고 SQL 쿼리의 틀을 미리 생성해 놓고 물음표를 대체할 값을 나중에 지정한다. 따라서 PreparedStatement가 처음 실행될 때 위의 1~3단계를 수행 후 사전 컴파일 되어 캐시에 저장된다. 이후에 Placeholder Replacement라는 추가 단계가 있으며 런타임시에 사용자가 입력한 데이터로 set메서드를 사용해 ?를 대체한다.

?가 사용자가 입력한 데이터로 바뀐 후에는 최종 쿼리가 다시 구문 분석하거나 컴파일하지 않는다.

# [ 보안 측면 ]

PreparedStatement는 SQL Injection 공격을 방지하기 때문에 보안 측면에서도 좋다.

PreparedStatement에서는 쿼리 실행 단계에서 알 수 있듯이, 쿼리가 컴파일 되어 캐시된 이후에 Placeholder Replacement단계에서 사용자의 데이터로 대체되기 때문에 이미 컴파일된 최종 쿼리는 다시 컴파일 과정을 거치지 않는다. 따라서 사용자의 데이터는 항상 간단한 문자열이여야 하며 쿼리의 원래 논리를 수정할 수 없다. 따라서 PreparedStatement를 사용한 쿼리는 SQL 주입 공격에 대한 영향을 받지 않는다.

# 0104 - 쿼리 캐시

쿼리 캐시란, SELECT 쿼리문을 이용하여 조회한 값을 저장하고 있다가, 같은 쿼리 문을 요청하였을 때 미리 캐싱된 값을 반환하는 DBMS 기능이다.
일반적인 웹사이트와 같이 쓰기(wirte)보다는 읽는(read) 횟수가 많은 환경에서 유용하다.
하지만, 멀티 코어 시스템 및 처리량이 높은 환경에서는 확장성이 좋지 않으므로 기본적으로 사용되지 않도록 설정된다.

# [ 쿼리 캐시 기능 사용 ]

쿼리 캐시 기능을 사용할 수 있는지 확인하려면, 기본적으로 have_query_cache 설정이 되어 있어야 한다.
설정이 되어있는지 확인하고 싶다면, 아래와 같은 쿼리 문을 입력한다.

SHOW VARIABLES LIKE 'HAVE_QUERY_CACHE'

조회 결과 YES로 나온다면, 쿼리 캐시 기능을 사용할 수 있따는 뜻이다.

SHOW VARIABLES LIKE 'QUERY_CACHE_TYPE'

현재 시스템에서 쿼리 캐시 기능을 사용하고 있는지를 조회한다.

# [ 쿼리 캐시가 되지 않는 경우 ]

아래와 같은 함수를 사용하면, 기본적으로 쿼리가 캐싱되지 않는다.

BENCHMARK()
CONNECTION_ID()
CONVERT_TZ()
CURDATE()
CURRENT_DATE()
CURRENT_TIME()
CURRENT_TIMESTAMP()
CURTIME()
DATABASE()
ENCRYPT() (one parameter)
FOUND_ROWS()
GET_LOCK()
LAST_INSERT_ID()
LOAD_FILE()
MASTER_POS_WAIT()
NOW()
RAND()
RELEASE_LOCK()
SLEEP()
SYSDATE()
UNIX_TIMESTAMP()
(no parameters)
USER()
UUID()
UUID_SHORT()

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

또한 쿼리에 아래와 같은 구문이 있을 경우 쿼리가 캐싱되지 않는다.

SELECT SQL_NO_CACHE ...
SELECT ... INTO OUTFILE ...
SELECT ... INTO DUMPFILE ...
SELECT ... FOR UPDATE
SELECT * FROM ... WHERE autoincrement_column IS NULL
SELECT ... LOCK IN SHARE MODE

1
2
3
4
5
6

# [ 쿼리 캐시 크기 제한 ]

SHOW VARIABLES LIKE 'query_cache_size';

쿼리 캐시 크기 설정 확인

SET GLOBAL query_cache_size = 800000;

query_cache_size값 설정
쿼리의 빈도수가 적고, 데이터가 많이 조회되는 쿼리가 있을 경우 캐싱을 할 필요가 없으므로 query_cache_limit 옵션으로 크기를 설정해서 특정 결과값의 용량이 설정해놓은 값이 넘으면 캐싱하지 않도록 설정을 할 수 있다.

# [ 캐시 설정 및 의미 ]

SHOW STATUS LIKE 'Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1158     |
| Qcache_free_memory      | 3760784  |
| Qcache_hits             | 31943398 |
| Qcache_inserts          | 42998029 |
| Qcache_lowmem_prunes    | 34695322 |
| Qcache_not_cached       | 652482   |
| Qcache_queries_in_cache | 4628     |
| Qcache_total_blocks     | 11123    |
+-------------------------+----------+

1
2
3
4
5
6
7
8
9
10
11
12
13

Qcache_inserts : 현재, 캐싱된 쿼리의 값을 의미
Qcache_hits : 쿼리를 캐싱하여, 캐싱된 값을 반환한 값을 의미
Qcache_lowmem_prunes : 메모리 값이 부족하여, 캐시에서 이전에 있떤 값을 제거한 값
- 해당 값을 줄이려면 query_cache_limit 값을 적절하게 설정한다.

# [ 결론 ]

그 밖에도, query_cache_wlock_invalidate 옵션을 끄면, WRITE 락이 걸리더라도, 캐싱된 값을 반환하게 하여, 경합 상태에서도 기다리지 않고 값을 읽을 수 있다.
캐리된 쿼리를 잘 이용하면, 성능을 높일 수 있다.
실제 SQL은 대소문자를 구분하지 않지만, 캐싱된 쿼리 값을 반환받기 위해서는 대소문자까지 같아야 한다.
쿼리 캐시 값의 크기를 크게 늘리면, 읽기 속도는 빨라지지만 락 경합 때문에 쓰기 속도는 느려질 수 있다.

# 0108 - 성능 관점에서의 서브쿼리(Subquery)

# [ 서브쿼리란? ]

서브쿼리는 SQL 내부에서 작성되는 일시적인 테이블이다. 여기서 '일시적인 테이블'이라는 부분이 성능적인 차이를 불러온다. SQL 구문 작성 시 활용하는 테이블(Table), 뷰(View), 서브쿼리(Subquery)에 대해서 설명을 해보면 아래와 같이 정리할 수 있다.

테이블(Table) : DB에 물리적으로 저장된 데이터(영속적)

select * from recipts

DB에 저장되어 있는 테이블을 가져오기 때문에 빠르고 큰 비용없이 접근할 수 있다.

뷰(View) : 가상의 테이블, 접근할 때 마다 SELECT 구문이 실행됨(영속적, 물리적 저장 X)

create view vw_receipts 
as 
select * 
from receipts
where cust_id = 'B';

select * from vw_receipts;

1
2
3
4
5
6
7

장점 : 테이블 중 필요한 데이터만 가상 테이블로 저장하여 처리함으로써 관리가 편하고 SQL문이 간단해진다.
단점 : 독립적인 인덱스(index)를 가질 수 없다. 접근할 때마다 SELECT 문에 구문이 실행되어 성능상의 문제를 유발시킬 수 있다.

서브쿼리(Subquery) : 가상의 테이블, SQL 구문 실행 중에만 존재(영속적, 물리적 저장 X)

select *
from (select * from receipts where cust_id = 'B') a;

1
2

장점 : SQL 구문 안에서 유연하게 또 다른 SQL문을 만들어 활용할 수 있다. 이로인해 코딩 시 편리함을 준다.
단점 : 연산 비용이 추가된다, 최적화를 받을 수 없다, 쿼리가 복잡해진다.

# [ 요약 ]

서브쿼리는 SQL 구문 작성 시 다양하게 활용할 수 있는 편리한 도구.
간편하고 유연하다는 장점이 있지만 성능적인 문제를 발생시킬 수 있다. 이는 서브쿼리가 '일시적인 테이블'이라는 특성을 가지기 때문이다.
성능적인 문제를 해결하기 위해 크게 두 가지를 고려하자.
- 불필요한 Join 연산을 수행하지 않는지
- Join 시 불필요한 테이블 접근을 하지 않는지
위 두가지 고려사항과 함께 한 가지 방법을 활용하면 더욱 좋다.
- Join 시 서브쿼리를 활용하여 결합 레코드 수를 줄여 성능적 효율성을 높힌다.

0109 ~ 0115