호스팅 사용법
호스팅 신청하기
도메인 연결하기
홈페이지 올리기
이메일 설정하기
서버 사용법
FTP사용법
메일사용법
텔넷/리눅스
MySQL사용법
MSSQL사용법
리눅스명령어 모음
시스템사용법
서버세팅
네트워크/보안
L4 매뉴얼
백업/미러링
호스팅용어 모음
프로그램 사용법
알리미사용법
제로보드설치방법
워드프레스설치방법
태터툴즈설치방법
그누보드설치방법
우편번호DB사용법
폼메일사용법
주문서사용법
예전 게시판/방명록
전자지불 서비스


HOME > 호스팅 > 사용안내 > MySQL사용법 > 쿼리성능
소개  기초문법  인덱스  조인  쿼리성능 
모델링  함수목록  PHP연동  DB백업  phpMyAdmin 사용법 

쿼리캐시(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가 없으면 먼저 수행되지 않는다.
  ▶ 먼저 수행되는 서브쿼리는 정렬 처리된 후 실행된다.

 
                 
 
     사이트명 : 피아호스트 | 회사명 : (주)아사달 | 대표이사 : 서창녕 I 대표전화 : 070-7510-3007 | 팩스번호 : 02-2026-2008
사업자등록번호 : 206-81-24351 I 법인등록번호 : 110111-1940504 | 통신판매업신고 : 제18-890호 I 벤처확인번호 : 051134562200563
(우편번호 : 153-803) 서울특별시 금천구 가산동 371-28 우림라이온스밸리 A,동 8층 (주)아사달
Copyright ⓒ piahost.com All rights reserved.
인터넷 익스플로어 구글 크롬 모질라 파이어폭스
애플 사파리 오페라 넷스케이프
맨위로