 |
|
|
 |
|
|
 쿼리캐시(Query Cache)
|
쿼리캐시는 DBMS에 질의된 쿼리를 보관하고 있고 다시 동일 쿼리가 질의될 경우 기존 결과값을 리턴하여 응답속도를 빠르게
한다.
① 쿼리캐시는 대소문자를 구분한다. 아래의 예는 각각 별개의 다른 쿼리로 인식한다.
|
| mysql-> SELECT * FROM mytable; |
| mysql-> select * from mytable; |
| mysql-> select * |
| -> from mysql; |
|
|
따라서 SQL은 한 파일로 모두 정의하여 관리가 용이하도록 하며 동일 결과의 쿼리가 다른 쿼리로 인식되어 동일 결과가 캐시버퍼(Cache buffer)에 모두 점유하는 현상을 방지한다.
② my.cnf 파일의 query_cache_type = 2 로 설정할 경우 쿼리캐시는 ondemand mode로 동작하여기본적으로 쿼리캐시를 하지 않고 SQL_CACHE 힌트 사용 시 캐시를 한다.
쿼리캐시 버퍼는 메모리상의 한정된 자원으로 불필요한 결과가 적재되어 자원을 낭비하지 않도록 주의한다.
|
| select /*! sql_cache */ * from mytable; |
| select /*! sql_no_cache */ * from mytable; |
|
|
|
| |
|
|
| 실행계획(explain plan) |
SQL을 생성하면 반드시 실행계획을 확인하고 효율적인 Index 사용여부를 확인해야 한다.
|
| mysql> explain select * from ASADAL_BOARD where board_no = 397824 \G |
| *************************** 1. row *************************** |
| id: 1 |
| select_type: SIMPLE |
| table: ASADAL_BOARD |
| type: const |
| possible_keys: PRIMARY |
| key: PRIMARY |
| key_len: 4 |
| ref: const |
| rows: 1 |
| Extra: |
| 1 row in set (0.00 sec) |
|
|
id: 쿼리 내의 테이블 아이디
select_type: 쿼리 내에서 본 테이블의 역할을 의미함. simple, primary, union, dependent union, subselect, derived 등이 있음
table: 테이블명
type: 어떤 형태의 조인을 수행하는가를 나타냄 const, system, eq_ref, ref, range, index, all 등이 있음
possible_keys: DBMS가 사용할 수 있는 키들을 나열함
key: 쿼리에서 사용한 키
key_len: 키값의 사이즈(bytes)
ref: 키에 대응되는 컬럼명이나 값
rows: 예상되는 row수, 실제 추출된 결과와 예상되는 row수가 다른 경우가 많으며 이 경우 analyze table 명령어를 사용해 통계정보를 갱신한다.
Extra: 기타 정보
- Using where : where에 사용됨
- Using Index : Index 정보를 사용함
- Using File sort : Sorting buffer의 사용하지 못하고 파일로 정열함
|
|
| |
|
|
| 테이블 조인(Join)
|
| mysql> select * |
| -> from a, b, c |
| -> where a.id = b.id |
| -> and b.id = c.id |
| -> and c.name = 'asadal'; |
|
|
DBMS에서 조인은 기본적인 쿼리 기법으로 항상 빈번하게 발생한다. 위 쿼리에서 a, b, c 순으로 쿼리를 작성하였다고 하여 a, b, c 순서로 조인이 이루어지는 것이 아니며 상수화가 이루어지는 테이블이 구동 테이블이 된다.
|
| mysql> drop index idx_city on mytable; |
| mysql> create index idx_city_date on mytable(city, date); |
| mysql> select * from mytable where city = 'seoul' order by date desc; |
|
|
일부 DBMS는 최적의 처리경로를 선택하기 위해 주어진 조건으로 가능한 모든 경로조합을 추정하고 그 중 가장 우수한 경로를 선택하여 결과를 가져온다. 10개 이상의 테이블이 조인되는 쿼리에서 29초간 경로탐색을 하고 실제 쿼리 실행은 1초만 하는 경우도 있다. 따라서 쿼리 작성 시 최적의 처리경로를 알고 있으면 using index 및 상수화 조건의 첨가 등을 통해 힌트를 주는 것이 좋다.
|
|
| |
|
|
| Index 기반 정열(Index-based Ordering)
|
15,000 row를 추출하는 것보다 15,000 row를 정렬하는 것이 더 많은 부하 및 시간을 요구한다. 따라서 DBMS에서의 정렬은 부하를 줄이고 응답속도를 빠르게 하는 열쇠가 된다. 하나의 쿼리 내에서 테이블당 사용할 수 있는 Index는 오직 하나이다.
|
| mysql> create index idx_city on mytable(city); |
| mysql> select * from mytable where city = 'seoul' order by date desc; |
|
|
위 쿼리는 idx_city를 기준으로 쿼리를 실행하고 the_date 컬럼을 기준으로 역정렬을 한다.
|
| mysql> drop index idx_city on mytable; |
| mysql> create index idx_city_date on mytable(city, date); |
| mysql> select * from mytable where city = 'seoul' order by date desc; |
|
|
위 쿼리처럼 city와 date를 사용하여 결합Index를 구성할 경우 date의 정렬은 Index를 사용하여 빠른 속도로 정렬을 수행할 수 있다.
|
|
| |
|
|
| 힌트(hints) |
힌트는 Optimizer가 선택한 최적의 처리경로가 개발자의 기대에 못 미칠 때 수동으로 직접 최적의 처리경로를 잡아줄 때 사용한다.
|
| ① mysql> select sql_cache from mytable; |
| ② mysql> select /*! sql_cache */ from mytable; |
| ③ mysql> select * from mytable /*! use index(time, name) */ .... ; |
| ④ mysql> create /*!32302 temporary */ table t (a int); |
|
|
▶ ①번의 예제처럼 sql_cache라는 힌트를 사용할 수 있지만 이 경우 DBMS가 변경될 경우 SQL를 수정해야 하는 문제가 발생한다.
따라서 타 DBMS에서도 현재 작성한 SQL이 동작하게 하려면 ②과 같이 /*! .... */ 형태로 사용한다.
▶ 특정 버전 이상에서만 동작하게 하려면 ④와 같이 버전번호를 기록할 수 있다.
▶ 따라서 힌트를 사용할 경우 반드시 향상 /*! .... */ 형태로 사용한다.
|
|
| |
|
|
| 서브쿼리(Sub query)
|
일반적으로 서브쿼리는 메인쿼리보다 먼저 실행된다고 생각하는데 항상 그렇지는 않으며 오히려 잘못된 SQL문으로 인해 메인쿼리가 먼저 실행되어 심각한 성능하락을 가져오는 경우가 많다. 메인쿼리가 먼저 실행되고 서브쿼리가 나중에 실행되는 경우에는 메인쿼리의 검색 행 수만큼 서브쿼리가 실행되기 때문에 성능저하를 유발한다. 따라서 되도록 서브쿼리가 먼저 실행되도록 SQL문을 작성하여 불필요한 액세스를 피하도록 한다. 아래는 서브쿼리 사용 시 유의할 점을 기술한 것이다.
|
| ▶ 서브쿼리 내에 메인쿼리의 컬럼이 없으면 먼저 수행될 수 있다. |
| ▶ 서브쿼리의 수행결과에 비교되는 메인쿼리 컬럼에 Index가 없으면 먼저 수행되지 않는다. |
| ▶ 먼저 수행되는 서브쿼리는 정렬 처리된 후 실행된다. |
|
|
|
|
|
|
