행복한 블로그

디비 쿼리 최적화는 정말 끝나지 않는 싸움같다.
실전에서 사용하고 있는 쿼리도 여러번 바뀌었는데 다 기억은 못하고..

글 내용을 말하기에 앞서 테이블 구조부터 소개를 하자면,
아래와 같이 생겼다. 아주 심플한 테이블이라 할 수 있다.

CREATE TABLE `TABLE` (  
`index` int(11) NOT NULL AUTO_INCREMENT,  
`date` varchar(10) NOT NULL,  
`time` varchar(10) NOT NULL,  
`nick` varchar(30) NOT NULL,  
`data` varchar(200) NOT NULL, 
 PRIMARY KEY (`index`),  
 KEY `indx_date` (`date`),  
 KEY `indx_nick` (`nick`) )
 ENGINE=InnoDB AUTO_INCREMENT=4016741 DEFAULT CHARSET=utf8

사실 처음에 몇만건 몇십만건 됐을때는 아무렇게나 짜도 무리가 없었는데 백만단위로 들어가면서부터 검색 범위를 제한하게 됐다.
유쾌하지 않은건 사실이다. 성능 때문이라는 변명에 괜히 숨는 느낌이라고 해야하나...
그래서 항상 고민이 많다. 이 글을 작성하는 시점에는 멀쩡한데 저녁에 서비스가 몰리는 시간엔 어찌될지...

검색 쿼리는 다음 조건을 가진다.

1. 날짜 범위 지정
2. 닉네임은 매칭 / 내용은 와일드카드 검색
3. 최근 순서대로 정렬

이를 간단히 생각하면 다음과 같이 작성해볼 수 있을 것 같다.
(실제로 생활코딩에 질문을 올렸을때 어떤 누가 이런식으로 쿼리를 짜서 주었음)
select `index`, `date`,`time`, `nick`, `data` from TABLE
where `date` BETWEEN '20150709' AND '20150716'  and `data` like '%검색어%' OR `nick` = '검색어'
order by date desc
별 다른 특이사항이 보이지 않는 쿼리이다. 과연 성능은 어떨까?

Explain extended 를 사용하여 쿼리가 어떻게 수행되는지 확인을 해보자.
SIMPLE    TABLE    ALL    indx_date,indx_nick                387809    100.00    Using where; Using filesort
검색은 SIMPLE하고, possible한 key도 기재가 되지만, 실제로는 인덱스를 타지 않는다.
왜냐면 컬럼중에 인덱싱 범주에 포함되지 않는것들이 다수 있기때문이다.
또한 검색에 소요되는 Row의 수도 거의 테이블 전체를 seeking 해야하는 수준이며, 검색 조건중 반드시 배제되어야 된다는 filesort를 통해 order by를 수행한다.

따라서, 위 쿼리로 서비스를 했다간 그냥 db가 뻗어버릴것이다

그래서 두번째로 생각한것이 서브쿼리를 이용해보는것이었다.
내 착안은 이렇다.

1. 날짜 범위별로 검색범위를 제한한다.
2. 서브쿼리에서 배달된 (delivered) 쿼리중에서만 추가검색을 진행한다.

결론부터 말하자면 근접한 방법이긴한데 정답은 아니다.
쿼리를 보면서 계속하자
SELECT `index`, `date`,`time`, `nick`, `data`
FROM     (        
                 select `index`, `date`,`time`, `nick`, `data` from TABLE
                 WHERE `date` BETWEEN '20150700' AND '20150716'
              ) as origindata
WHERE (    `data` like '%검색어%' OR `nick` = '검색어' )
ORDER BY `index` DESC

서브쿼리를 이용해서 인덱싱이 되어있는 Date 컬럼을 이용해 범위를 제한하고,
나온 결과에서만 검색을 수행하겠다는 계획이고, 실제로 위 쿼리로 서비스를 오래 했다.

이 쿼리의 explain은 다음과 같다.
1    PRIMARY    <derived2>    ALL                    177838    100.00    Using where; Using filesort
2    DERIVED    TABLE    ALL    indx_date                386966    45.96    Using where
읽는 순서는 역순으로..
인덱스를 타라고 일부러 서브쿼리로 나눠놨는데 위에서 언급한것과 마찬가지로 인덱싱이 되지 않는 컬럼을 select하기때문에 결국 전체 테이블을 seeking하게 된다.

결과적으로 놓고보면, 첫번째 쿼리와 다를게 없다....
filesort도 그대로 사용하고 있고, 인덱스도 여전히 이용하지 않고 있다.

그럼 어떻게 해야할까? 우선 인덱스를 타게 해야겠다고 생각했다.
그래서 쿼리를 좀 고쳐봤다. 요렇게
select b.`index`, `date`,`time`, `nick`, `data` from TABLE as b
left join (
   select `index` from TABLE where `date` BETWEEN '20150709' AND '20150716' ) a
on a.`index` = b.`index`
where a.`index` = b.`index` and  `data` like '%검색어%' OR `nick` = '검색어'
ORDER BY b.`index` DESC

위 쿼리는 인덱싱이 되어있는 `index`컬럼과 `date`컬럼을 사용해보자고 생각을 했다.
그래서 서브쿼리 조인을 사용해서, 날짜에 해당하는 index들만 뽑아낸 후 바깥 테이블에서 해당 primary index에 해당하는 녀석들에서만 검색을 해보자고 시도를 해봤다. 결과는 실패였지만 과정은 괜찮았다.
1    PRIMARY    b    index    PRIMARY,indx_nick    PRIMARY    4        387047    100.00    Using where
1    PRIMARY    <derived2>    ref    <auto_key0>    <auto_key0>    4    lute.b.index    10    100.00    Using where; Using index
2    DERIVED    TABLE    range    indx_date    indx_date    32        179508    100.00    Using where; Using index

드디어 Extra에 using index가 등장하기 시작했다.
하지만 결국 b 테이블은 전체 테이블을 seek해서 배달되는 index랑 비교를 해야되기때문에 컬럼수가 무자비했고, 간혹 돌리다보니 using filesort뿐 아니라 using temporary까지 .....
오히려 더 안좋은 결과가 되었다. 실제 쿼리타임도 쿼리를 두번 돌려야되는 꼴이기때문에 더 느린건 두말할것도 없다

하지만 아이디어 자체가 나쁘다고 생각하지 않았기때문에 using temporary라는 키워드로 검색해보다가 이곳을 발견하게 되었다.
이제 내가 해결해야될 문제는 명확했다.

1. using index를 무조건 띄워야된다.
2. filesort 및 temporary가 뜨면 안된다
3. 최소한의 Row를 사용해야한다

링크한 블로그의 커버링 인덱스(covering index)를 참조하여 다시 작성한 쿼리는 다음과 같다.
select a.`index`, a.`date`,a.`time`, a.`nick`, a.`data`
from (     
       select `index`  from TABLE
       where  `date` BETWEEN '20150700' AND '20150716'
       order by `date` desc
       ) b
join TABLE a
on b.`index` = a.`index`
where a.`data` like '%검색어%' OR a.`nick` = '검색어'
사실 뭐가 다른지도 잘 구분이 안된다만 엄연히 다르니 잘 보자
따로 말로 설명하면 길어질것 같으니 성능부터 보자
1    PRIMARY    <derived2>    ALL                    193547    100.00   
1    PRIMARY    a    eq_ref    PRIMARY,indx_nick    PRIMARY    4    b.index    1    100.00    Using where
2    DERIVED    TABLE    range    indx_date    indx_date    32        193547    100.00    Using where; Using index
확연한 차를 보이고 있고, 제시한 모든 문제가 다 해결되었다.
최소한의 Row를 사용하고, index를 이용해 order by 까지 처리하는걸 볼 수 있다.

이번글에서는 엄청난 내용을 소개한것은 아니다.
하지만 쿼리 한개라도 아무렇게나 작성하면 안되고 충분한 테스트 및 성능 검증이 필요하다고 말하고 싶었다.
그래서 일일히 삽질한 쿼리도 공개를 해보면서 풀어나가듯이 작성해봤다.
어쨌든 디비쿼리와의 전쟁은 계속되겠고 최적화를 위해서 계속 삽질을 하자.

Reference
http://gywn.net/2012/04/mysql-covering-index/

출처 : http://b.fantazm.net/entry/Mysql-Query-Optimization-using-Covering-Index

MYSQL 매뉴얼중 Query cache에 대한 내용만을 Study 하기 위해 비공식적으로 

번역한 것을 도움이 될것 같아 올립니다. Tip&Tech 부분이 가장 적당할 것 같아

Tip&Tech에 올립니다. 저자권에 문제가 되는 경우는 바로 삭제 하겠습니다.

정리하면서 수정해서 다시 올립니다. 허접으로 번역해서 도움이 될지 모르겠습니다.

6.9 MySQL Query Cache

==============================

MYSQL 4.0.1 버전부터 Query Cache 개념이 도입되었다.

Query Cache가 사용되면 SELECT Query문과 SELECT 결과를 캐싱처리하고

나중에 동일한 Query 요청이 들어오면 Query문을 실행하지 않고

Query Cache에서 결과를 전달하는 식으로 동작 한다.

Query Cache는 동적인 내용을 많이 사용하는 웹서버 등에서 데이터 변경이 빈번하지

않는 테이블을 대상으로 동일한 Query를 중복해서 사용하는 경우 

매우 유용하다.

아래는 Query Cache의 성능에 대한 일부 자료 이다.

( OS: Linux Alpha  CPU: 2x500 MHZ  RAM: 2GB  Query Cache:64MB 환경의 

      서버에서 MySQL benchmark 에서 생성되었음)

  . 실행한 모든 쿼리문들이 단순하지만 (ex 한 레코드만 있는 테이블에서 레코드를 선택하는 쿼리)

    쿼리문이 모두 달라 캐시가 될 수 없는 경우는 Query Cache를 하기 위한 오버헤드가 13%

    증가 했다. 이 경우는 최악의 시나리오로 볼 수 있다. 일반적인 경우 현업에서 Query문은 훨씬 

    복잡하기 때문에 오버헤드는 현저히 줄어든다.

  . 레코드가 하나 있는 테이블의 레코드가 캐시된 후 검색은 238% 빨라졌다. 이는 Query가 캐시된 후

    예상할 수 있는 최소한의 성능향상에 근접한 결과라고 볼 수 있다.

  . Query Cache를 사용하지 않고자 하는 경우 Query_Cache_size=0으로 my.cnf 파일에 설정하면

    된다. Query Cache를 사용하지 않으면 눈에 띄는 오버헤드는 없다.

    ( Query Cache는 컴파일시에 --without-Query-Cache 옵션을 사용해서 제외할 수도 있다.)

6.9.1 Query Cache 동작 방식

==============================

동일한 Query문이란?

  MYSQL 서버에 Query문 처리 요청이 들어 오면 Query Cache된 Query문과 같은 Query문 인지 

  비교하는 작업이 먼저 이루어 지는데 이때 Query문의 모든 문자가 일치해야 동일 

  Query문으로 간주 된다.

  따라서 SELECT * FROM TABLE 과 Select * from table 은 다른 쿼리로 간주한다.

  Query문이 일치하더라도 각각 다른 Query문으로 간주되어 따로 캐시 처리 되는 경우도 있는데

  database,프로토콜 버젼,디폴트 character set이 다른 경우이다.

  이때는 각각 다른 Query로 간주되어 각각 캐시 처리 된다.

Query Cache의 제외 대상 

   특정 SELECT문은 Query Cache 대상이 되지 않는 경우도 있는데 

      SELECT CALC_ROWS ... 유형의 쿼리와  

      SELECT FOUND_ROWS () ... 유형의 쿼리는 

   Query Cache의 대상이 되지 않는다.

   아래의 함수를 포함하고 있는 SELECT Query문은 캐시 되지 않는다.

   Function 

   -----------------------

   User-Defined Functions        

   CONNECTION_ID       

   FOUND_ROWS

   GET_LOCK  

   RELEASE_LOCK  

   LOAD_FILE  

   MASTER_POS_WAIT  

   NOW  

   SYSDATE  

   CURRENT_TIMESTAMP  

   CURDATE  

   CURRENT_DATE  

   CURTIME  

   CURRENT_TIME  

   DATABASE  

   ENCRYPT (with one parameter)  

   LAST_INSERT_ID  

   RAND  

   UNIX_TIMESTAMP (without parameters)  

   USER  

   BENCHMARK  

   Query문에 사용자 변수가 있는 경우와

   SELECT ... IN SHARE MODE 유형의 쿼리

   SELECT * FROM AUTOINCREMENT_FIELD IS NULL(마지막 INSERT된 ID를 가져오기 위해)

   유형의 쿼리도 캐시되지 않는다.

   참고로 이전의 Query문의 결과가 캐시에서 가져온 경우라도 FOUND_ROWS()는 정확한 값을 리턴한다.

   또 SELECT Query가 테이블을 대상으로 하지 않는 경우, 

   임시 테이블을 사용하는 경우,

   접속 클라이언트가 Query문 대상의 테이블들중 한 테이블에 대해 컬럼 권한(Column Privilege)을 

   가지고 있는 경우에도 쿼리가 캐시되지 않는다.

Query Cache에 저장된 캐시의 삭제

    테이블이 변경되는 경우(INSERT, UPDATE, DELETE, TRUNCATE, ALTER, DROP TABLE|DATABASE)는 

    해당 테이블(MRG_MyISAM table를 통한 경우도)을 참조한 모든 캐시 데이터가 삭제 되고,

    InnoDb 테이블에서  트랜잭션이 이루어지는 단계에서는 테이블이 변경되는 경우는 

    COMMIT이 실행될때 캐시 데이터가 삭제 된다.

Query Cache 적용시

    Query Cache를 적용시에는 접속클라이언트가 적용할 캐시에 관계된 모든 데이터베이스들과

    테이블들에 대해 SELECT 권한이 있을때만 적용 된다.

6.9.2 Query Cache 환경설정

==============================

Query Cache를 사용하기 위해서는 환경파일의 mysqld 부분이나 mysqld를 커맨트라인에서 실행하는 경우

모두 추가해야 할 사항이 있다.

   . query_qache_limit  결과가 이 값보다 큰 경우 결과를 캐시하지 않는다. ( default 1M)

   . query_qache_size   캐시 하기 위해 메모리상에 확보해야할 메모리 크기

                        이 값이 0이면 Query Cache가 사용되지 않는다.   ( default 0)

   . query_qache_type   숫자형식이며 다음값중 하나가 사용됨

                        0 :  (OFF, 캐시하지 않으며 캐시에서 결과를 가져오지도 않음)

                        1 :  (ON, SELECT SQL_NO_Cache ... 를 제외한 모든 Query 

                                  결과를 캐시함)

                        2 :  (DEMAND, SELECT SQL_Cache ... 쿼리들만 캐시 한다.)

런타임에 thread(connection)에서 동적으로 Query Cache의 기본 동작 방식을 변경할 수도 있는데

문법은 다음과 같다.

Query_Cache_TYPE = OFF | ON | DEMAND 

Query_Cache_TYPE = 0 | 1 | 2 

      (   0 or OFF  : 캐시하지 않으며 캐시에서 결과를 가져오지도 않음

          1 or ON   : SELECT SQL_NO_Cache ... 를 제외한 모든 Query 결과를 캐시함

          2 or DEMAND : SELECT SQL_Cache ... 쿼리들만 캐시 한다.)

6.9.3 SELECT 문에서 Query Cache 옵션들

========================================

SELECT문에 Query Cache와 관련되서 두가지 옵션이 첨가 될 수 있다.

SQL_CACHE : Query_Cache_TYPE이 DEMAND인 경우는 Query를 캐시 한다.

            Query_Cache_TYPE이 ON인 경우 default

            Query_Cache_TYPE이 OFF인 경우 의미 없음

 ex> SELECT SQL_CACHE .....

SQL_NO_CACHE : Query문이 캐시에 저장되지 못하도록 함.

 ex> SELECT SQL_NO_CACHE ...

6.9.4 Query Cache 상태와 유지관리

========================================

FLUSH QUERY CACHE

  FLUSH QUERY CACHE는 Query Cache의 메모리를 조각모음해서 메모리 사용 효율을

  높일때 사용 한다.

FLUSH TABLES

  FLUSH TABLES는 Query Cache를 비운다.

RESET QUERY CACHE

  RESET QUERY CACHE는 Query Cache의 캐시된 모든 쿼리결과를 제거 한다.

Query Cache 지원 여부 확인

  현재 사용하고 있는 MYSQL Version에서 Query Cache가 있는지 여부를 다음과 같이 

  확인 할 수 있다.

  mysql> SHOW VARIABLES LIKE 'have_Query_Cache';

  +------------------+-------+

  | Variable_name    | Value |

  +------------------+-------+

  | have_Query_Cache | YES   |

  +------------------+-------+

  1 row in set (0.00 sec)

Query Cache 성능 모니터

 SHOW STATUS 문을 통해 Query Cache의 성능을 모니터 할 수 있다.

 변수                        설명

 ------------------------    ------------------------------------

 QCache_queries_in_Cache    캐시에 등록된 쿼리 갯수. 

 QCache_inserts             캐시에 추가된 쿼리 갯수. 

 QCache_hits                캐시 Hit(적용된) 갯수. 

 QCache_lowmem_prunes       메모리 부족으로 캐시에서 삭제된 쿼리 갯수. 

 QCache_not_Cached          캐시 불가능한 쿼리 갯수(캐시 불가능 하거나 Query_Cache_TYPE에 의해). 

 QCache_free_memory         Query Cache의 비할당 메모리양. 

 QCache_free_blocks         Query Cache의 비할당 메모리 블럭의 갯수. 

 QCache_total_blocks        Query Cache의 총 메모리 블럭 갯수. 

 총 쿼리 수 = QCache_inserts + QCache_hits + QCache_not_Cached

 Query Cache는 동적인 크기의 블럭을 사용하기 때문에  QCache_total_blocks 과

 QCache_free_blocks은 Query Cache의 메모리의 조각화 현상을 보여 준다.

 FLUSH Query Cache 후에는 QCache_free_blocks의 값이 1이 된다.

 NOTE: 각 Query는 최소한 두 블럭(Query문과 Query결과값)을 사용한다. 

       또, Query에 사용된 각 테이블은 한 블럭을 사용하지만, 여러 Query에서 

       같은 테이블이 사용되는 경우에는 한 블럭만 할당 된다.

 QCache_lowmem_prunes 값을 참조해서 Query Cache의 크기를 튜닝할 수 있다.

 이 값은 새로운 쿼리를 캐시하기 위해 캐시에서 삭제된 쿼리 갯수인데 Query Cache는

 least recently used(LRU : 최근에 가장 사용빈도가 적음) 전략을 통해 Cache에서

 제거할 쿼리를 결정 한다.

# root암호설정 - root로 로그인하여 해야함

% mysqladmin -u root password '변경암호'

% mysqladmin -u root -p기존암호 password '변경암호'

root암호변경설정

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !

This is done with:

/usr/bin/mysqladmin -u root -p password 'new-password'

/usr/bin/mysqladmin -u root -h ns.moyiza.net -p password 'new-password'

DB작업

DB생성: mysql> create database DB명 ( or % mysqladmin -u root -p create DB명 )

DB삭제: mysql> drop database DB명

DB사용: mysql> use DB명 (엄밀히 말하자면, 사용할 'default database'를 선택하는 것이다.)

DB변경: mysql> alter database db명 DEFAULT CHARACTER SET charset (4.1이상에서만 available)

MySQL 연결

mysql -u 사용자 -p DB명 ( or % mysqladmin -u root -p drop DB명 )

데이터파일 실행(sql*loader기능)

mysql>load data infile "데이터파일" into table 테이블명 ;

데이터파일에서 컬럼구분은 탭문자, Null값은 /n로 입력

데이터파일의 위치는 /home/kang/load.txt 와 같이 절대경로로 지정할것.

질의 파일 실행

쉘프롬프트상에서

mysql -u 사용자 -p DB명 < 질의파일

or

mysql프롬프트상에서

mysql> source 질의파일

쉘프롬프트상에서 질의 실행

moyiza@nero board]$ mysql mysql -u root -pxxxx -e 

>        "INSERT INTO db VALUES(

>        'localhost', 'aaa', 'aaa',

>        'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y')"

사용자 생성 & 사용자에게 DB할당

shell> mysql --user=root -p mysql

mysql> INSERT INTO user VALUES('localhost','사용자',PASSWORD('비밀번호'),'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');

mysql> INSERT INTO user VALUES('%','사용자',PASSWORD('비밀번호'),'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');

mysql> INSERT INTO db(Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES ('localhost','DB명','사용자','Y','Y','Y','Y','Y','Y');

mysql> INSERT INTO db(Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES('%','DB명','사용자','Y','Y','Y','Y','Y','Y');

mysql> FLUSH PRIVILEGES; (or shell prompt: mysqladmin -u root -pxxxx reload)

CASE 2: GRANT명령을 이용한 사용자 생성(이 방법이 권장된다)

kang이라는 DB를 만들고, 이 DB를 아래에서 나열된 권한을 가진 kang이라는 사용자를 생성

create database kang;

grant SELECT,INSERT,UPDATE,DELETE,CREATE,DROP on kang.* to kang@localhost identified by 'kang';

grant SELECT,INSERT,UPDATE,DELETE,CREATE,DROP on kang.* to kang@'%' identified by 'kang';

mysql> create database kang;

Query OK, 1 row affected (0.00 sec)

mysql> grant SELECT,INSERT,UPDATE,DELETE,CREATE,DROP on kang.* to kang@localhost identified by 'kang';

Query OK, 0 rows affected (0.00 sec)

mysql> grant SELECT,INSERT,UPDATE,DELETE,CREATE,DROP on kang.* to kang@'%' identified by 'kang';

Query OK, 0 rows affected (0.01 sec)

mysql>

여러가지 명령정리

mysql> show variables;                               서버의 variables(설정사항)출력

mysql> show variables like 'have_inno%'                   조건에 맞는 variables만 출력

mysql> show databases;                               database목록

mysql> show tables;                                 현재DB의 테이블목록(temporary table은 출력하지 않음)

mysql> show tables from db명;                          지정된 db명이 소유한 테이블목록

mysql> show tables like 'mem%';                         조건에 맞는 테이블목록만 출력

mysql> show index from 테이블명;                        인덱스 보기

mysql> show columns from 테이블명;                       테이블구조(describe 테이블명, explain 테이블명)

mysql> show table status;                             현재 DB의 테이블들의 상태(row수,table type,row길이,..)

mysql> show table status from db명;                      지정된 DB의 테이블들의 상태(row수,table type,row길이,..)

mysql> show create table 테이블명;                       해당 테이블 생성 SQL문 출력

mysql> rename table 테이블1 to 테이블2;                   테이블명 변경(ALTER TABLE 테이블1 RENAME TO 테이블2)

mysql> rename table 테이블1 to 테이블2, 테이블3 to 테이블4;      rename multiple tables

mysql> rename table db1명.테이블명 to db2명.테이블명;          테이블을 다른 DB로 이동

mysql> alter table 테이블명 add 컬럼명 데이터타입;            컬럼추가

mysql> alter table 테이블명 del 컬럼명;                   컬럼제거

mysql> alter table 테이블명 modify 컬럼명 컬럼타입;           컬럼명에 지정된 컬럼타입의 변경

mysql> alter table 테이블명 change old컬럼명 new컬럼명 컬럼타입   컬럼명 변경

mysql> alter table 테이블명 type=innodb;                   테이블type변경

mysql> create table 테이블명(..) type=heap min_rows=10000;       10000row를 수용할 수 있을 만큼 메모리할당(heap type이므로)

mysql> select version();                             MySQL서버버전 출력

mysql> create table 테이블2 as select * from 테이블1;          테이블1과 동일한 테이블 생성(with 데이터, as는 생략가능)

mysql> create table 테이블2 as select * from 테이블1 where 1=2;   테이블1과 동일한 구조의 테이블 생성(without 데이터, 1=2는 0으로 할수도 있다.)

mysql> insert into 테이블2 select * from 테이블1;             테이블1의 데이터를 테이블2에 insert

테이블이 존재여부 파악

DROP TABLE IF EXISTS 테이블명;

CREATE TABLE 테이블명 (...);

프로그래밍 언어에서 COUNT(*)를 사용하여 질의가 성공하면 테이블이 존재함을 파악할 수 있다.

ISAM, MyISAM의 경우 COUNT(*)가 최적화되어 상관없으나, BDB, InnoDB의 경우 full scan이 발생하므로 사용하지 마라.

대신 select * from 테이블명 where 0; 을 사용하라. 질의가 성공하면 테이블이 존재하는 것이고, 아니면 존재하지 않는 것이다.

접속

mysql {-h 접속호스트} -u 사용자 -p 사용DB

-h로 다른 서버에 존재하는 MySQL접속시 다음과 같이 MySQL DB에 설정해줘야 한다.

mysql> INSERT INTO user VALUES('접근을 허용할 호스트ip','사용자',PASSWORD('비밀번호'),'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');

mysql> INSERT INTO db(Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES('접근을 허용할 호스트ip','사용DB','사용자','Y','Y','Y','Y','Y','Y');

mysql> FLUSH PRIVILEGES; or 쉴프롬프트상에서 % mysqladmin -u root -p flush-privileges

검색조건(where)

regular expression을 지원하다니 신기하군..

mysql> select * from work where 열명 regexp "정규표현식";

백업 & 복구

mysqldump {-h 호스트} -u 사용자 -p DB명 > 백업파일

mysql {-h 호스트} -u 사용자 -p DB명 < 백업파일

mysqldump -u root -p --opt db_moyiza > moyiza.sql

mysqldump -u root -p --opt db_board | mysql ---host=remote-host -C database (상이한 머쉰)

mysql -u moyiza -p db_moyiza < moyiza.sql

mysqldump -u root -p --opt db_moyiza | mysql ---host=ns.moyiza.net -C db_moyiza

테이블 생성구문만을 화면에서 보려면 다음과 같이 --no-data를 사용한다. 테이블명을 생략하면 모든 테이블 출력

mysqldump -u 유저명 -p --no-data db명 테이블명

테이블 검사

isamchk

오라클 sysdate와 동일

insert into test values('12', now());

유닉스 time()함수 리턴값 사용

FROM_UNIXTIME(954788684)

UNIX_TIMESTAMP("2001-04-04 :04:04:04")

MySQL 디폴트 DB&로그파일 위치

/var/lib/mysql

/var/lib디렉토리는 여러 프로세스들이 사용하는 데이터를 저장하는 일종의 파일시스템상의 데이터베이스라고 볼 수 있다.

replace

해당 레코드 존재하면 update하고, 존재하지 않는다면 insert한다.(insert문법과 동일)

replace into test values('maddog','kang myung gyu')'

explain

explain 질의문: 지정한 질의문이 어떻게 실행될 건지를 보여줌

mysql> explain select u.uid, u.name, a.name from sm_user u, sm_addr a where u.uid=a.uid;

+-------+------+-----------------+-----------------+---------+-------+------+-------+

| table | type | possible_keys  | key         | key_len | ref  | rows | Extra |

+-------+------+-----------------+-----------------+---------+-------+------+-------+

| u   | ALL | PRIMARY      | NULL        |   NULL | NULL | 370 |     |

| a   | ref | sm_addr_uid_idx | sm_addr_uid_idx |    11 | u.uid |  11 |     |

+-------+------+-----------------+-----------------+---------+-------+------+-------+

2 rows in set (0.01 sec)

temporary table

크기가 큰 테이블에 있는 subset에 대한 질의라면 subset을 temporary table에 저장한 후 질의하는 것이 더 빠를 경우가 있다.

temporary table는 세션내에서만 유효하고(현재 사용자만이 볼수 있다는 뜻), 세션종료시 자동적으로 drop된다.

create temporary table (...);

create temporary table (...) type=heap;     디스크가 아닌 메모리에 테이블 생성

존재하는 permanent table의 테이블명과 동일하게 생성할 수 있으며,

temporary table은 permanent table보다 우선시되어 처리된다.

4.0.7의 감마버전에서 테스트하면 결과는 약간 달라진다. 버그인건지..

mysql> create table test (id varchar(10));

Query OK, 0 rows affected (0.01 sec)

mysql> insert into test values('moyiza');

Query OK, 1 row affected (0.00 sec)

mysql> create temporary table test(id varchar(10));

Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;

Empty set (0.00 sec)

mysql> drop table test;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;

+----------+

| id     |

+----------+

| moyiza |

+----------+

1 row in set (0.00 sec)

Table Type에 다른 Files on Disk

ISAM  .frm (definition) .ISD (data) .ISM (indexes)

MyISAM .frm (definition) .MYD (data) .MYI (indexes)

MERGE .frm (definition) .MRG (list of constituent MyISAM table names)

HEAP  .frm (definition)

BDB   .frm (definition) .db (data and indexes)

InnoDB .frm (definition)

보통 mysqldump를 사용하여 백업을 수행하여 다른 DB서버에 데이터를 restore하면 된다.

MySQL은 별다른 작업없이 데이터파일을 단순히 복사(copy)하는 것만으로도 다른 서버에

DB을 이동시킬 수 있다. 하지만, 이런 방식이 지원되지 않는 table type도 있다.

ISAM: machine-dependent format하기때문에..

BDB : .db파일에 이미 테이블위치가 encode되어 있기때문에..

MyISAM, InnoDB, MERGE :가능(machine-independent format)

별다른 지정을 하지 않았다면 디폴트 TABLE type이 MyISAM이므로, 무난히 migration할 수 있다.

floating-point컬럼(FLOAT,DOUBLE)이 있다면 이러한 방식이 실패할 수 도 있다.

쉘에서는 mysql이 되는데 PHP에서 mysql.sock error를 내면서 MySQL이 안되는 경우

mysql.sock은 /tmp 아니면 /var/lib/mysql에 생기게 된다.

나의 경우, /var/lib/mysql에 mysql.sock파일이 있는데 PHP에서는 /tmp에서 찾으려하면서 에러를 발생했다.

/usr/bin/safe_mysqld파일에서 다음과 같이 수정한다.

주석(#)이 달린 것이 원래것이고 그 밑에 있는것이 수정한 것이다.

# MYSQL_UNIX_PORT=${MYSQL_UNIX_PORT:-/var/lib/mysql/mysql.sock}

MYSQL_UNIX_PORT=${MYSQL_UNIX_PORT:-/tmp/mysql.sock}

위와 같이 하니 /usr/bin/mysql이 /var/lib/mysql/mysql.sock에서 소켓파일을 찾으려 했다.

socket file을 지정하는 --socket이라는 옵션으로 다음과 같이 지정하면 된다.

mysql --socket=/tmp/mysql.sock -u moyiza -p db_test

하지만 mysql실행시마다 이렇게 써줘야한다는 것이 상당히 귀찮다. 옵션이 바로 적용되게 설정하자.

mysql은 설정사항을 다음 3가지 파일에서 검색한다.

/etc/my.cnf        global options(MySQL 전체적으로 사용되는 옵션 정의)

mysql-data-dir/my.cnf 특정 DB에 적용되는 option (/var/lib/mysql/my.cnf)

~/.my.cnf         사용자 각각의 설정('~'문자는 사용자의 홈디렉토리는 의미)

/usr/share/mysql디렉토리에 예제가 있으므로 참고한다.

소켓파일의 지정은 다음줄을 넣어주면 된다.

socket       = /tmp/mysql.sock

== /etc/my.cnf예 ==

# The following options will be passed to all MySQL clients

[client]

#password     = your_password

port        = 3306

socket       = /tmp/mysql.sock

# Here follows entries for some specific programs

# The MySQL server

[mysqld]

port        = 3306

socket       = /tmp/mysql.sock

MySQL에서 통계처리시

orderby, groupby 는 sort_buffer를 늘여준다.(show variables)

live table(smslog)에서 모든 질의를 처리하지 말고 summary table에 질의결과를 저장해 재질의 처리한다.

summary table이 heap-type table가 가능한지 확인할 것.

INSERT INTO tblTemp2 (fldID) SELECT tblTemp1.fldOrder_ID FROM tblTemp1 WHERE

   tblTemp1.fldOrder_ID > 100;

join이 subselect보다 빠르다.

join시 사용되는 컬럼은 동일한 column type과 길이를 가져야만 최적의 속도를 보장한다.

즉, 동일 column type이지만 길이가 다르다면(char(11), char(10)), 동일한 컬럼도메인으로 변경해주는 것이 좋다.

where의 in은 optimize되어 있으므로 빠르다

insert,select는 동시에 수행가능하다.(어떻게?)

explain으로 질의과정 점검

varchar to/from char

conversion varchar를 char로 변경할 경우 모든 컬럼타입을 동시에 변경해야 한다.

반대의 경우, 하나만 char->charchar변경시 다른 모든 컬럼도 varchar로 변경됨

참.. 특이하구만..

mysql> CREATE TABLE chartbl (name VARCHAR(40), address VARCHAR(80));

Query OK, 0 rows affected (0.05 sec)

mysql> desc chartbl;

+---------+-------------+------+-----+---------+-------+

| Field  | Type     | Null | Key | Default | Extra |

+---------+-------------+------+-----+---------+-------+

| name   | varchar(40) | YES |   | NULL   |     |

| address | varchar(80) | YES |   | NULL   |     |

+---------+-------------+------+-----+---------+-------+

2 rows in set (0.03 sec)

mysql> alter table chartbl modify name char(40);

Query OK, 0 rows affected (0.02 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc chartbl;

+---------+-------------+------+-----+---------+-------+

| Field  | Type     | Null | Key | Default | Extra |

+---------+-------------+------+-----+---------+-------+

| name   | varchar(40) | YES |   | NULL   |     |

| address | varchar(80) | YES |   | NULL   |     |

+---------+-------------+------+-----+---------+-------+

2 rows in set (0.00 sec)

mysql> alter table chartbl modify name char(40), modify address char(80);

Query OK, 0 rows affected (0.01 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc chartbl;

+---------+----------+------+-----+---------+-------+

| Field  | Type   | Null | Key | Default | Extra |

+---------+----------+------+-----+---------+-------+

| name   | char(40) | YES |   | NULL   |     |

| address | char(80) | YES |   | NULL   |     |

+---------+----------+------+-----+---------+-------+

2 rows in set (0.00 sec)

mysql>

"For each article, find the dealer(s) with the most expensive price."

표준안

   SELECT article, dealer, price

   FROM  shop s1

   WHERE price=(SELECT MAX(s2.price)

             FROM shop s2

             WHERE s1.article = s2.article);

수정안(최적화)

   CREATE TEMPORARY TABLE tmp (

         article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,

         price  DOUBLE(16,2)         DEFAULT '0.00' NOT NULL);

   LOCK TABLES shop read;

   INSERT INTO tmp SELECT article, MAX(price) FROM shop GROUP BY article;

   SELECT shop.article, dealer, shop.price FROM shop, tmp

   WHERE shop.article=tmp.article AND shop.price=tmp.price;

   UNLOCK TABLES;

   DROP TABLE tmp;

========================================================

MySQL 특성정리

========================================================

primary key, foreign key지원

index 지원(15개컬럼, 256byte까지)

MySQL에서의 Stored Script개념 => SQL server language

commit-rollback개념 => lock tables(lock table test write -> 트랜잭션.. -> unlock tables)

컬럼명길이: 64자까지, 컬럼 Alias: 256자까지

not case-sensitive: keywords, functions, column, index명

case-sensitive: database, table, alias명

키워드,함수명은 대소문자구별이 없지만, db명과 table명은 Unix계열이라면 case-sensitive하다.

(이는 오브젝트명이 OS의 fs에 따라 저장되기 때문이다. 서버의 lower_case_table_names 변수를

1로 설정하면 오브젝트명은 모두 소문자로 저장되므로 유닉스-윈도간 호환성을 높일 수 있다.

지원되지 않는 부분: 

Stored Procedure(5.0이상부터 지원된다고 함)

View(5.0이상부터 지원된다고 함)

Trigger(5.0이상부터 지원된다고 함)

subquery(4.1이상부터 지원된다고 함)

union, union all(4.0이상부터 지원됨)

[테이블 type에 따른 인덱스 특성]

Index Characteristic        ISAM   MyISAM         HEAP     BDB           InnoDB

NULL values allowed         No    Yes           As of 4.0.2 Yes           Yes

Columns per index          16    16            16       16            16

Indexes per table          16    32            32       31            32

Maximum index row size (bytes) 256   500           500      500/1024        500/1024

Index column prefixes allowed  Yes   Yes           Yes      Yes           No

BLOB/TEXT indexes allowed     No    Yes(255 bytes max) No       Yes (255 bytes max) No

인덱스 생성

- alter table을 이용한 인덱스 생성이 더 flexible함

- 인덱스명은 생략가능

ALTER TABLE 테이블명 ADD INDEX 인덱스명 (인덱스컬럼);

ALTER TABLE 테이블명 ADD UNIQUE 인덱스명 (인덱스컬럼);

ALTER TABLE 테이블명 ADD PRIMARY KEY (인덱스컬럼);

ALTER TABLE 테이블명 ADD FULLTEXT (인덱스컬럼);

CREATE INDEX 인덱스명 ON 테이블명 (인덱스컬럼);

CREATE UNIQUE INDEX 인덱스명 ON 테이블명 (인덱스컬럼);

CREATE FULLTEXT INDEX 인덱스명 ON 테이블명 (인덱스컬럼);

unique인덱스와 primary key인덱스와의 차이

unique은 null허용하지만, primary key는 null허용 안함

unique은 하나의 테이블에 여러개 올 수 있지만, primary key는 하나만 존재

테이블생성시 지정

CREATE TABLE 테이블명

(

... column declarations ...

INDEX 인덱스명 (인덱스컬럼),

UNIQUE 인덱스명 (인덱스컬럼),

PRIMARY KEY (인덱스컬럼),

FULLTEXT 인덱스명 (인덱스컬럼),

...

);

index prefix 생성

- 컬럼의 전체길이중 일부만 인덱스로 사용

- supported for ISAM, MyISAM, HEAP, and BDB tables, but not for InnoDB tables

- 지정되는 길이는 byte단위가 아닌 charater단위이므로, multi-byte character일 경우 주의

- blob, text 컬럼타입일 경우, index prefix 가 유용(255 길이까지 가능)

CREATE TABLE 테이블명

(

name CHAR(30) NOT NULL,

address CHAR(60) NOT NULL,

INDEX (name(10),address(10))

);

인덱스 삭제

DROP INDEX 인덱스명 ON 테이블명;

ALTER TABLE 테이블명 DROP INDEX 인덱스명;

ALTER TABLE 테이블명 DROP PRIMARY KEY;

outer join 

[MySQL] 

left outer joing : SELECT t1.*, t2.* FROM t1 LEFT OUTER JOIN t2 ON t1.i1 = t2.i2;

right outer joing: SELECT t1.*, t2.* FROM t1 RIGHT OUTER JOIN t2 ON t1.i1 = t2.i2;

[Oracle]

left outer joing : SELECT t1.*, t2.* FROM t1, t2 where t1.i1 = t2.i2(+);

right outer joing: SELECT t1.*, t2.* FROM t1, t2 where t1.i1(+) = t2.i2;

SELECT

student.name, student.student_id,

event.date, event.event_id, event.type

FROM

student, event

LEFT JOIN score ON student.student_id = score.student_id

       AND event.event_id = score.event_id

WHERE

score.score IS NULL

ORDER BY

student.student_id, event.event_id;

:= 문장을 이용한 변수의 설정

현재 moyiza의 데이터베이스강좌게시판에 등록된 총 게시물은 43개이다. 43개의 강좌를 읽은 수(hit수)는 각각 다르다.

평균 hit수를 구해 보자.

mysql> select @total_hit := sum(hit), @total_record := count(*) from zetyx_board_database;

+------------------------+---------------------------+

| @total_hit := sum(hit) | @total_record := count(*) |

+------------------------+---------------------------+

|             3705 |                43 |

+------------------------+---------------------------+

1 row in set (0.00 sec)

mysql> select @total_hit/@total_record as 평균HIT;

+-----------------+

| 평균HIT      |

+-----------------+

| 86.162790697674 |

+-----------------+

1 row in set (0.00 sec)

select substring(subject from 9) from zetyx_board_database where substring(subject, 1, 8) = '[ORACLE]';

보통 상용DBMS들이 row-level locking을 지원한다. 쉽게 말해 레코드단위로 락킹한다는 말이다.

반면, MySQL의 MyISAM 테이블타입은 table-level locking을 사용한다. 

쉽게 말하면, insert, update, delete작업은 전체 테이블에 락을 걸고 처리된다는 것이다.

row-level락보다 비효율적이지만,.. MySQL은 빠르기 때문에 이 단점이 상쇄된다.

Compressed MyISAM(packed MyISAM)

정적인 테이블데이터는 압축하여 20-60%정도의 공간을 절약할 수 있다.

Production데이터를 CD로 받아서 차후 디스크에 풀지 않고 CD자체로 바로 사용할 수도 있다.

gzip등으로 백업받으면 이를 푸는 과정이 필요할 것이다.

% myisampack moyiza.myi

데이터베이스 게시판의 Merge Table에 좀 더 자세한 내용을 적어 두었다.

RAID Table

1개의 테이블은 OS상에 3개의 파일로 구성된다.

스키마파일(.frm), data파일(.myd), index파일(.myi)

MySQL의 RAID테이블은 데이터파일(.myd)을 여러개의 파일들로 구성하는 것이다.

create table raid_test (...)

type=myisam raid_type=striped raid_chunks=4 raid_chunsize=8

테이블을 4개의 데이터파일로 나누고, 8kb단위로(8kb stripe) 라운드로빈 방식으로 write가 이루어진다. 

This article comes from moyiza.net (Leave this line as is) 

출처 : http://yaku.tistory.com/184

. T-SQL 순위를 구하는 문제의 해결책

가장 쉬우면서도 가장 평범한 문제를 다루고자 한다. 간단하게 생각하면 이 순위 패턴은 오라클 에서는 필요하지 않다라고 생각 할 수 있다. 왜냐면 오라클에서는 ROWNUM을 제공하므로 데이터를 ORDER BY해주고 ROWNUM만 적어 주면, 순위를 매겨 번호를 붙여 줄 수 있다. 하지만, 순위가 동률인 데이터에 대해서 어떻게 처리할 것인가? 이런 문제에 대해서 순위 패턴을 적용할 수 있을 것이다. SQL Server의 T-SQL은 어떠한가? ROWNUM같은 기능의 함수는 존재하지 않는다. 그러므로 이 순위 패턴을 절대적으로 피할 수 없을 것이다.(순위에 대한 문제를 클라이언트에서 처리하지 않는다면)

예전에 T-SQL을 공부하기 시작했을 때는 왜 ROWNUM 같은 것이 없을까 하고, 찾아 헤맨 적이 있었다. 하지만, SQL에 대해 알면 알아 갈수록 ROWNUM이 없어도 모든 처리가 가능하다는 것을 알 수 있었다.

SQL을 하면서 항상 느끼는 것은 연구하고 생각할수록 더 좋은 문장을 만들 수 있다는 것이다. 이 데이터베이스의SQL은 기능상으로 어떤 함수가 제공되지 않는다고 못한다는 것은 연구하고 생각하고자 하는 의지가 부족한 것이다. 그런 개발자들은, 생각부터 고쳐야 할 것이다.

가장 간단한 데이터를 가지고 생각을 해보도록 하자.

우리가 만들 테이블은 점수 테이블이다. 간단하게 학번과 점수로만 구성되어 있다.

이렇게 간단한 데이터를 가지고 시작하는 것은 우리가 알아야 할 순위를 구하는 원리에 대해 집중하기 위한 것이다.

학번과 점수로만 구성이 된 간단한 테이블이므로 ERD는 생략하도록 하겠다.

<SQL 1-1>

테이블을 생성했으면 샘플 데이터를 입력하도록 하자.

<SQL 1-2>

샘플 데이터 입력 후에 점수(Score) 순으로 데이터를 관찰 해보도록 하자. “갑자기 무슨 관찰인가” 란 생각이 들 수 있겠지만 데이터를 관찰 하는 것은 매우 중요하다. 데이터를 관찰하고 자신이 원하는 결과가 무엇인지 상상하다 보면 풀 수 있는 해법을 찾을 수 있기 때문이다.

<SQL 1-3>

위의 SQL을 통해서 다음과 같은 결과를 얻을 수 있다.

이 결과에서 점선으로 표시된 Ranking 부분은 아직 얻어내지 않은 결과이다. 우리가 원하는 것은 위의 결과처럼 점선의 Ranking까지 같이 표시하는 결과이다.

이 결과를 얻기 위해 먼저 정해야 하는 것은 같은 점수를 가진 학생에 대해서 어떻게 등수를 부여할 것이냐의 문제이다. 여기서는 일단, 동률 점수를 가진 학생에 대해 같은 낮은 순위의 등수를 부여하기로 하겠다. 그러므로StudentID 3번과 2번은 같은 4등을 가지게 할 것이다.

이 순위 문제를 해결하기 위해서 SQL문을 만들기 전에, 종이와 펜을 들고, 또는 머릿속으로 생각해 보도록 하자.생각으로 SQL문을 만들어 보자는 것이 아니라, 왜, StudentID 5번은 1등이고, 1번은 2등이 되는지 생각해 보자는 것이다. 5번은 점수가 가장 높으니까, 1등이고, 1번은 2번째로 점수가 좋으니 2등이다. 그렇다면 이 당연한 것을StudentID와 Score만 가지고 어떻게 만들어 낼 수 있는가를 깊이 생각해 보자.

이것은 당연하고 쉬운 문제이다. “5번 학생의 점수 95점보다 높은 점수를 가진 학생이 몇 명일까?”란 질문으로 이 해답은 풀어지게 된다. 5번 학생 점수보다 높은 학생은 아무도 없다. 그러므로 1등이다. 1번 학생의 점수보다 높은 학생은 몇 명인가? 5번 학생 한 명이다. 그러므로 1번 학생은 2등이다. 4번 학생보다 점수가 좋은 학생은 5번과 1번 학생 두 명이다. 그러므로 4번은 3등이다. 이렇게, 자기 자신의 점수보다 점수가 좋은 학생의 수를 센다면, 쉽게 순위를 구할 수 있다.

자, 그럼, 생각한 대로 SQL문을 만들어 보도록 하자. 여기서는 이해가 쉽도록 SELECT절의 서브쿼리를 사용할 것이다. 간혹, 개발자들의 특성에 따라, SELECT절에 서브쿼리가 오는 것은 최악이라고 생각하는 경우가 있다. 하지만,필자는 꼭 그렇다라고 생각하지는 않는다. SQL문의 가독성 적인 측면을 생각한다면, SELECT절의 서브쿼리가 훨씬 유용할 때가 많기 때문이다.

우리가 생각한 논리는 다음과 같이 SQL문으로 표현 할 수 있다.

<SQL 1-4>

위의 SQL문에서 실제 순위를 구하는 곳은 SELECT절의 서브쿼리란 것을 쉽게 알 수 있을 것이다. FROM절의 SCORE테이블(T1)의 Score보다 큰 Score를 가진 학생들의 COUNT를 구해서 Ranking으로 표현하는 것이다.

실행 결과, 원하지 않는 결과가 나온 것을 알 수 있다.

실제 1등으로 표현되어야 할 StudentID 5번은 Rangking이 0으로 표시되고, 실제, 2등인 1번의 Ranking이 1로 표시된 것을 알 수 있다. 이것을 해결할 수 있는 방법은 무엇인가? 아주 쉬울 것이다. 결과에 1씩만 더해 주면 될 것이다. 다음과 같은 SQL이 될 것이다.

<SQL 1-5>

원하는 결과가 나온 것을 알 수 있다.

이런 순위의 문제에 대해서 항상, 고려해야 하는 것은 동률을 이룬 데이터에 대한 처리이다.

동률의 데이터에 대해 동일하게 낮은 등수를 적용하는 경우도 있을 것이다. 3번과 2번 StudentID에 대해 같은 4등을 적용하는 것이 아닌, 같은 5등을 적용하는 것이다. 이 경우는 어떻게 하면 되겠는가?

0을 1로 바꾸기 위해서 서브쿼리의 결과에 + 1 을 한 것과 같이 + 2를 해주면 되는 것인가? 아니다. + 2를 해주면, 1등은 2등이 되고, 2등은 3등이 될 것이다. 단지, StudentID, 3번과 2번만이 우리가 원하는 5등이 될 것이다.같은 낮은 등수를 적요하기 위해서는 서브쿼리에 + 1을 하는 것이 아닌, 서브쿼리의 T2.Score > T1.Score 부분을>= 조건으로 변경하면 된다. 자신보다 높은 점수를 가진 사람을 세는 것이 아니라, 자신보다 높거나 같은 점수를 가진 사람들을 세게 된다면, 동률 데이터에 대해서는 저절로 동일한 낮은 순위가 매겨지게 될 것이다.

<SQL 1-6>

만족스러운 결과를 얻을 수 있을 것이다.

우리는 비록 원하는 결과를 일차적으로 얻었지만, 또 다른 방법이 없는지 연구 해봐야 한다. 해당 시스템에서 가장 최적이고 좋은 방법을 생각해 내야 하는 것이다.

이런 순위를 구하는 가장 간단한 방법은 클라이언트에서 순위를 비교해서 뿌려 주는 것이다. SQL로는 ORDER BY만 해주고, 데이터를 화면에 출력할 때, 기존에 출력했던 데이터보다, 점수가 높으면 1증가시키고, 아닌 경우는 순위를 이전과 동일하게 유지하면서 출력해주는 방법이다. 가장 쉬운 방법이면서도, 가장 좋은 성능을 낼 것이라 생각 된다. 문제는, 다양하게 변하는 사용자의 요구사항을 적용하기 위해서 클라이언트 프로그램을 변경하는 일은 만만한 작업이 아니라는 것이다. 이미 운영되고 있는 시스템이라면, 배포에 대한 문제도 있을 수 있으면, 어떤 코딩을 건드렸을 때, 다른 코딩에 영향을 주지 않는지를 알아내기란 쉽지 않은 문제이다.

모든 것에는 장 단점이 있는 것이므로 적절하게 사용을 하면 될 것이다. 하지만, 우리는 좀더 SQL쪽에 접근해서 문제를 푸는 방법을 생각하도록 하자.

순위를 구할 수 있는 또 다른 SQL문에는 어떤 것이 있을까? 서브쿼리를 사용한 것은 가장 쉬운 방법이라고 필자는 생각한다. 서브쿼리를 사용하지 않고 해결하는 방법에는 셀프 조인(Self Join)이 있다.

자기 자신과 테이블을 조인하는 방법이다. 사실, 순위를 구하기 위해 사용한 서브쿼리 역시 셀프 조인이다. 자기 자신의 테이블을 관련되어서 순위를 찾아내니까 말이다. 하지만, 문법적으로 셀프 조인을 작성 할 때는 FROM절에 두 개의 같은 테이블이 모두 있어야 한다.

순위를 구하기 위한 셀프조인 SQL문은 다음과 같다.

<SQL 1-7>

확실히 셀프 조인은 서브쿼리보다 읽기가 쉽지는 않을 것이다. 하지만, 이 어려워 보이는 문장도 하나씩 각개격파를 해보면 별 것 아니다. 그리고, 이 문장을 이해하는 것은 SQL의 JOIN이 얼마나 강력한지 이해하게 되는 것이다.위의 결과에 대해 결과는 다음과 같다.

실행 결과를 보면, StudentID 3번과 2번이 4등이 아닌, 5등으로 나타난 것을 알 수 있다. 이것을 4등으로 표현하기 위한 문제는 좀 더 후에 생각하도록 하고, 지금은, 이 결과가 어떻게 해서 나왔는지 생각을 해보도록 하자. 약간씩 복잡한 SQL문을 만들거나, 복잡한 SQL을 파악할 때는 하나씩 작은 단위로 나누어서 SQL을 파악하는 것이다.이 정도 SQL가지고 복잡하다고 말 할 수 없음을 많은 개발자들은 알고 있을 것이다. 하지만, 이 짧은 SQL을 파악하는 과정을 통해 더 복잡한 문장도 파악할 수 있는 능력이 생길 것이다.

먼저 순수하게 조인만 수행하고 결과를 살펴보도록 하자.

<SQL 1-8>

결과를 보면 다음과 같다.

이 결과에서 눈여겨 볼 것은 같은 T1.StudentID를 가진 데이터가 몇 개씩 있느냐 이다.

거듭 강조하지만, T2로 나온 결과는 신경쓰지 말도록 하자. T1.StudentID가 5번인 데이터는 한 건이다. 1번인 데이터는 2건, 4번인 데이터는 3건, 3번과 2번은 각각 5건이다. 이 T1.StudentID별로 데이터 건수가 등수로 연결되는 것은 너무도 쉽게 알 수 있을 것이다. 그럼, 왜 이렇게 데이터가 나올 수 있을까를 고민해 보도록 하자. 이것에 대한 답은 조인을 통해 변하는 데이터의 건수이다. SQL문을 보면 조인 조건이 T1.Score = T2.Score가 아닌, T1.Score <= T2.Score인 것을 알 수 있다. 이 것이 어떻게 조인이 이루어 지는가를 알기 위해서 간단한 테이블이 있다고 생각하고 하나씩 짚어 보도록 하자.

no란 컬럼을 가진 A테이블과 B테이블이 있다고 생각해 보자.

두 테이블에서 같은 no를 가진 데이터만 연결(JOIN)해서 조인 결과(A.no = B.no)를 만들어 보자.

조인의 결과는 총 3 건이 된다. 그림을 보고 A테이블부터 첫 번째 데이터부터 시작을 해보도록 하자.

A테이블의 첫 번째 데이터는 no가 1이다. 이 1이란 숫자를 가지고 B테이블로 가져가 보도록 하자.

B테이블에서 지금 가져온 1이란 값과 일치되는 데이터는 B테이블의 가장 첫 데이터이다. 그러므로 이것은 1건의 조인 결과를 만들어 내게 된다.

이번에는 A테이블의 두 번째 데이터인 2번을 가지고 B테이블에서 찾아보도록 하자. B테이블은 2번을 두 건 가지고 있다. 그러므로 결과는 조인 결과는 2건이다.

마지막으로 A테이블의 4번 데이터를 B테이블에서 찾아보자. 알다시피 한 건도 없으므로 조인 결과에는 참여하지 않게 된다. 이것이, 같다 조건을 사용한 조인이 된다. 여기서 정확히 알고 있어야 하는 것은, A테이블과 B테이블의 연결 순서에 상관없이 결과는 동일하다는 것이다. 위와는 반대로 B테이블로 시작해서 A테이블을 거쳐서 조인을 해보도록 하자.

B테이블의 처음 데이터는 no가 1이다. 이 데이터를 가지고 A테이블을 뒤져 보면, no가 1인 데이터는 1건이다. 그러므로 결과에는 1건이 나오게 된다. B테이블의 두 번째 데이터는 no가 2이다. 이 데이터와 동일한, no를 가진 데이터를 A테이블에서 찾아보면 1건이다. 그러므로 B.no가 2이고, A.no가 2인 데이터가 결과에 한 건 만들어 진다. B테이블의 세 번째 데이터의 no도 역시 B테이블의 두 번째 데이터와 마찬가지로 2이다. 이 데이터를 가지고 다시A테이블에서 찾아보면, 이전에 B테이블의 두 번째 데이터와 짝을 맞춘 no가 2인 데이터가 한 건 있다. 이 데이터와 조인이 되어서 역시 결과로 한 건이 보내 진다. 마지막으로 B테이블의 no가 3인 데이터는 A테이블에서 찾아볼 수 없으므로 결과에 참여하지 못한다.

이와 같이, 조인의 순서는 결과 내용에는 영향을 미치지 않는다. 하지만, 이러한 조인의 순서는 때로는 성능에 영향을 미치게 되며, 내부 조인(일반적인 조인)이 아닌, 외부조인을 사용할 경우에는 조인의 순서가 조인결과에 영향을 미치기도 한다. 하지만, 내부 조인에서는 절대적으로 조인의 순서에 따라 결과가 틀리게 나오는 경우는 없다.

그러면 이번에는 같다 조건이 아닌 크기 비교 조건으로 조인을 수행해 보도록 하자.

대부분의 개발자들은 같다 조건 이외의 조건으로 조인을 해본 적이 거의 없을 것이다. 실제로, 이런 조건을 잘못 사용하게 되면, SQL의 실행 성능에 악영향을 끼치기도 하며, 실제로 사용되는 일이 드물기 때문이다. 하지만, 원리를 알고 정확히 사용한다면, 아주 유용하게 사용할 수 있을 것이다.

A테이블과 B테이블을 A.no >= B.no 의조건으로 조인을 해보도록 하자.

총 몇 건의 결과가 나올 거 같은가? 아마, 머리 좋은 개발자들은 이 정도의 데이터 건수라면, 쉽게 암산 할 수도 있을 것이다. 하지만, 데이터가 많아 지면 이를 암산하기는 쉽지 않다. 그림을 통해서 알아보도록 하자.

같다 조건으로 결합한 경우보다 좀 더 보기 복잡해 졌을 것이다. 그래도 인내를 갖고 하나씩 생각하면서 보도록 하자. 결합(조인) 조건은 A의 no가 B의 no보다 크거나 같은 경우이다.

A의 첫 번째 데이터는 no가 1이다. 그럼 B테이블에서 1보다 작거나 같은 no를 가진 데이터는 첫 번째 데이터 한 건이다. 그러므로 먼저 한 건이 결과로 만들어 진다.

A의 두 번째 데이터는 no가 2이다. B테이블에서 2보다 작거나 같은 no를 가진 데이터는 무엇이 있는지 찾아보자. B 테이블의 첫 번째 데이터, 두 번째, 세 번째 데이터가 각각, 1, 2, 2의 값을 가지고 있으므로 A테이블의 두 번째 데이터와 결합 할 수 있다. 그러므로 3건이 결과 집합에 만들어 지게 된다.

마지막으로 A의 세 번째 데이터는 4에 대해서 살펴보도록 하자. B테이블에서 4보다 작거나 같은 no를 가진 데이터는 B테이블의 전부이다. 총 4건이므로 총 4개의 결과가 추가 된다. 그 결과, 총 8건의 결과가 만들어 진 것을 볼 수 있다. 반대로 B테이블을 시작점으로 해서 조인을 해보아도 = 조건의 조인과 결과는 동일하다.

우리는 이 A.no >= B.no 같은 조건을 순위를 구하는 SQL문에 응용했던 것이다.

그럼, 실제 순위를 구했던 SQL을 통해 크기비교 조건으로 조인이 어떻게 되는지 살펴보자.

아마도, 관계형 DB를 할 때는 집합개념이 중요하다는 말을 많이 들었을 것이다. 필자는 수학이고 산수고 모두 자신 없다.(실제로 구구단을 외자 게임을 하게 된다면, 두, 세번 답변 하다가 지고 말 것이다.) 그러므로 집합에 대한 얘기는 하지 않겠다. 집합을 몰라도 관찰력과 상상력을 가지고 있다면 SQL을 능숙히 만들어 낼 수 있을 것이다. 이런, 관찰력이 우리에게는 필요하다는 것을 상기하고 SQL문과 결과를 관찰해 보도록 하자.

먼저 SQL문장에서는 셀프 조인(SELF JOIN)을 사용했다. 셀프 조인이란 것은 특별히 어려운 것이 없다. FROM절에 같은 테이블이 두 개 이상 온다면 셀프 조인인 것이다. 우리는 이전의 크기비교 조인을 살펴보기 위해서 간단한 A테이블과 B테이블을 사용했지만, 실제 순위를 구하는 SQL은 크기비교 조인을 자기 자신과 수행하게 된다. <SQL 1-8>을 수행해서 결과를 살펴보자. 어떤 특징이 있는가? 위에서도 설명했듯이, 이 결과에서는, T1.StudentID가 5번인 데이터는 한 건, 1번인 데이터는 2건, 4번인 데이터는 3건, 3번과 2번은 각각 5건라는 것이다. 이러한 사항을 좀 더 쉽게 관찰 할 수 있는 방법은 무엇일 까? 그것은 바로 하나씩 실행을 해보는 것이다. 실제 개발 시에도 하나의 데이터에 대해 관찰해서 얻은 결론으로 전체 데이터에 적요하는 SQL문을 만들면 생각보다 쉽게 SQL을 만들 수 있다.

다음과 같은 SQL을 실행해 보자.

<SQL 1-9>

T1테이블의 StudentID가 1번인 학생 보다 높거나 같은 점수를 가진 데이터를 T2에서 찾는 것이다. 결과는 총 두 건이 나온 것을 알 수 있다. 그러므로 이 1번 학생은 2등이다. WHERE조건의 StudentID를 하나씩 바꿔가면서 실행해 보도록 하자. 해당 번호의 학생이 몇 등인지를 쉽게 알 수 있을 것이다.

<SQL 1-8>의 결과를 T1.StudentID별로 집계를 해서, 카운트를 센다면, 등수가 나올 것이다. 그리고, Score는T1.StudentID에 대해 종속적(T1.Score는 T1.StudentID에 대해 결정되어 진다.)이므로 MAX나, MIN을 사용해서Score를 표시하도록 하자. 그래서 나온 결과가 <SQL 1-7>이다. GROUP BY, COUNT, MAX, MIN등에 대해서는 설명하지 않아도 모두 알고 있으리라 생각하고 생략하도록 하겠다.

이번에는, 셀프 조인을 사용해서 동률인 데이터에 대해서 낮은 등수를 출력할 수 있도록 구성해 보자. 기존의<SQL 1-7>의 결과는 다음과 같았다.

이 결과에서 3번과, 2번 StudentID가 5등이 아닌, 4등이 되어야 하는 것이다.

스스로 한번, 시도를 해보기 바란다. 아마도, 다양한 방법이 있을 것이다. 하지만, 결코 만만한 문제는 아닐 것이다.

먼저, 단순하게, Ranking에 -1을 하게 되면 어떻게 되겠는가? 이것은, 3번과, 2번 학생에 대해서는 우리가 원하는4를 돌려주겠지만, 나머지, 데이터들의 등수가 모두 내려가게 될 것이다.

이것 역시 관찰과 상상력을 통해서 결과를 얻을 수 있다.

먼저, 3등과, 2등이, 4등이 되려면 어떻게 되어야 하는가? 기존의 서브쿼리로 순위를 구했던 <SQL 1-5>를 참고 할 수 있다. <SQL 1-5>의 서브 쿼리에서 조건은 크거나 같은 데이터가 아닌, 외부 테이블보다 큰 점수를 가진 데이터만 찾았다. 그러므로 다음과 같은 조건의 셀프 조인이 될 수 있다.

<SQL 1-10>

결과는 다음과 같다.

제대로 된 결과 같은가? 자세히 보도록 하자. 데이터가 한 건 사라진 것을 알 수 있다.

이것이 바로 내부 조인의 핵심이다. 조인 조건에 참인 데이터만 내보낸다는 것이다.

거듭 강조하는 것은 조인은 같은 데이터를 연결 하는 것이 아닌, 조인 조건을 만족하는 데이터를 연결하는 것이다. 그러므로 한 건의 데이터가 사라진 것이다. 어떤 데이터가 사라졌는가? T1.StudentID가 5번인 1등 데이터가 사라진 것이다. 그럼, 왜 사라졌는가? 조인 조건을 만족하는 데이터는 연결이 되어서 결과에 나타나지만, 조인 조건을 만족하지 못하는 데이터는 연결되지 못하기 때문이다. 5번인 데이터의 Score는 95이다. 우리는 T1.Score < T2.Score조건으로 조인을 했다. 그러므로 T2에서, 95점 보다 큰 데이터는 없으므로 사라진 것이다. 같다 조인을 사용하든, 크기 비교 조인을 사용하든, 이것은 조심해야 할 사항이다. 기존의 <= 조건에서 5번 데이터는 사라지지 않았다. 왜냐면, T2에는 같은 95점을 가지고 있는 T2.StudentID가 5번인 데이터가 있기 때문이다.

그렇다면 어떻게 해야 하는가? 여기서 조건을 반대로 준다거나, 크거나 같다. 조건 등으로 바꿔 보는 것은 아주 좋은 시도다. 조건을 바꿔서 결과가 어떻게 나왔는지 살펴보고 왜 그렇게 되었는가를 고민해 본다면, 정답을 찾든 안 찾든, 큰 발전이 있을 것이다. 스스로 많은 방법을 연구 해보고 다음을 보도록 하자.

필자가 생각하는 방법은 외부조인을 사용하는 것이다. 외부조인의 특징이 무엇인지 확실히 알고 있어야 한다. 외부 조인은, 어느 한쪽의 기준 테이블의 모든 데이터를 조인 조건이 참이 아니더라도 결과에 내보내는 것이다. 단, WHERE조건을 통해 걸러 진 데이터는 역시 결과에 참여할 수 없는 것이다.

LEFT OUTER JOIN을 했다면, JOIN문장의 왼편에 있는 테이블이 기준 테이블이 되어서 내부적으로 먼저 접근 되어지게 되며, 왼편에 있는 데이터는 모두 결과에 나오게 된다. 왼편에 테이블과 결합되는 데이터가 오른편의 테이블에 존재하지 않는다면, 오른쪽의 데이터는 모두 NULL이 되어서 결합되어 진다.

이전의 A테이블과 B테이블에 대해서 A LEFT OUTER JOIN B ON A.no = B.no 에 대해서 수행해 보도록 하자.

이전의 내부 조인처럼 조인 조건을 만족하는 데이터를 결과에 내보내 준다. 하지만, A테이블의 마지막 데이터만은 다른 데이터와 틀리다. A.no가 4인 데이터는 B테이블에서 전혀 찾을 수 없다. 그러므로, A.no가 4인 데이터는 B테이블에 해당하는 값들 대신에 NULL값을 가지고, 결과로 내보내 진다. 그러므로 결과에는 A테이블의 모든 데이터들이 포함되어 있다.

B테이블을 기준으로 LEFT OUTER JOIN을 한다면 어떻게 될 것인가? 조인 조건은 B LEFT OUTER JOIN A ON B.no = A.no이다. 결과는 B테이블에 있는 모든 데이터가 나오게 된다. 하지만, A테이블의 A.no가 4인 데이터는 결과에 나오지 않는다. 이처럼 외부 조인에서는 조인의 순서가 있게 된다. 그러므로 어떤 테이블의 데이터가 기준이 되어서 모두 나와야 하는지를 유심히 생각을 해서 정해야 하며, 이런 순서는 성능에 영향을 미치게 되므로 특히 조심을 해야 한다. 계속해서 강조하게 되는 것은 조인은 같다란 조건을 만족하는 데이터를 결과로 내보내는 것이 아니라,조인 조건이 참인 데이터를 내보내게 된다. 그러므로 우리는 외부조인이라 해도 크기 비교의 조건을 사용할 수 있는 것이다.

우리는 순위를 구하기 위해 어느 한쪽의 테이블의 데이터가 모두 나와야 하는지 결정해야 한다.

조건은 T1.Score < T2.Score 이다. T2테이블은 순위를 카운트하기 위해 참여하는 테이블이다. 그러므로 T1테이블의 데이터들이 모두 나와야 한다.

그러므로 다음처럼 외부 조인을 구사해야 할 것이다.

<SQL 1-11>

결과를 살펴보도록 하자. 결과를 보면, StudentID가 1번인 데이터와 5번인 데이터가 동일하게 1등을 가지고 있는 것을 볼 수 있다. 무엇이 잘 못되었는지 알겠는가? 그것은 바로 COUNT이다.

COUNT의 특성을 제대로 알고 있다면, 쉽게 고칠 수 있을 것이다. COUNT(*)를 하는 경우에는 해당 레코드(모든 컬럼들이 포함된한 줄의 데이터)의 모든 컬럼 값이 NULL을 가지고 있어도 카운트에 추가시킨다.

하지만, COUNT(컬럼)을 사용해서 특정 컬럼에 대해 카운트를 하게 되면, NULL값은 카운트에서 제외시키게 된다.다음의 간단한 두개의 SQL을 통해 정확히 알 수 있을 것이다.

<SQL 1-12>

두 개의 결과가 틀리다는 것을 알 수 있다. 첫 번째 SQL문은 1을 두 번째 SQL은 0을 결과값으로 보여준다.

두 번째 SQL문은 특정 컬럼에 대해 카운트를 세었기 때문에 NULL값은 제외되었기 때문이다.

그러므로 우리는 제대로 된 등수를 구하기 위해서 COUNT를 변경해야 한다. <SQL 1-11>에서, T1.StudentID가 5인 데이터와 결합될 T2의 데이터는 없다. 그리고, T1.StudentID가 1인 데이터는 T2의 StudentID가 5인 데이터와 결합된다.(1번은 90점, 5번은 95점이므로) 그러므로, COUNT(T2.StudentID)를 한 후에 COUNT에 +1만 해주게 되면 원하는 결과가 정확히 나오게 된다.

<SQL 1-13>

여기까지 원하는 순위를 구하기 위한 SQL문을 모두 살펴 보았다.

우리는 서브쿼리와 셀프조인을 사용해서 순위를 각각 구해 보았다. 개발자들이 SQL문을 작성할 때 가장 중요하게 고민해야 하는것은 바로 성능이다. 우리가 작업했던 SQL문들에 대한 성능을 측정해 보도록 하는 시간을 가져 보도록 하자. 본격적으로 성능을살펴보기 전에 알아야 할 것은, 각 SQL Server의 버전마다. 테스트 환경의 PC성능에 따라, SQL의 환경 설정에 따라 다른 실행 계획을 나타날 것이다. 하지만, 우리는 여기서 기본적으로 어떤 요소들이 성능에 영향을 주는지는 알 수 있을 것이다.

먼저, 서브쿼리와, 셀프 조인을 했을 경우 어떻게 성능의 차이가 있는지 알아보도록 하자.

먼저 <SQL 1-5>와 <SQL 1-13>을 비교해 보도록 하겠다. 두 SQL문은 정확히 같은 결과를 돌려주도록 만들 어진 문장이다. 이 두 문장을 수행하기 전에 쿼리 분석기에서 Ctrl + K(또는 메뉴의 쿼리의 실행계획 표시를 선택한다.)를 눌러서 그림으로 실행계획을 표시하도록 설정해 놓고 수행해 보도록 하자. 우리가 수행할 두 문장이 같이 있는SQL은 <SQL 1-14>이다.

<SQL 1-14>

수행한 후 쿼리분석기의 왼쪽아래의 탭에서 실행계획을 클릭해서 실행계획을 확인 할 수 있다. 필자의 PC에서는 두 개의 SQL에 대해 거의 동일한 비용이 소모된 것으로 나타났다. 서브쿼리를 사용한 SQL은 49.99%이고, 셀프조인을 사용한 SQL은 50.01%이다. 이 것은 두 개의 비용이 별 반 차이 없음을 나타낸다. 그것은 곧, 둘 중에 어느 문장을 사용해도 좋다는 것을 나타낸다. 이 비용에 대한 %가 SQL 성능을 나타내는 절대적인 수치가 될 수 없음을 알도록 하자. 그럼에도 불구하고 실행계획의 그림에 표시되는 수치들은 큰 도움을 준다. 여기서는 두 문장이 수치적으로 어떤 차이가 있는지 알게 해준 것과 더불어, 각 SQL에서 어떤 부분에서 가장 많은 비용이 소모되었는지 알 수 있는 것이다. 두 SQL의 실행계획의 그림을 보고 어떤 부분이 가장 비용이 많이소모되었는지 알아보자. 먼저,첫 번째 서브쿼리를 사용한 SQL의 경우에는 SCORE테이블의 PK인덱스를 스캔하는 부분이다. 이 인덱스 스캔이 두 번 일어난 것을 알 수 있다. 하나는 FROM절의 테이블에 대한 스캔이고, 또 하는, 서브쿼리에 있는 스캔이다. 두 번째 SQL 역시, 두 개의 SCORE 테이블의 PK 인덱스가 가장 많은 비용을 차지하고 있다. 그럼 우리가 튜닝할 것의 목표가 정해진 것이다. 이 SCAN에 대한 비용을 줄이는 것이다.

이 중에는 줄일 수 있는 것과 없는 것이 있다. 어떤 것일까? 바로, 한 테이블에 대한 PK SCAN이다. 테이블의 모든 데이터에 대해 등수를 구하는 것이기 때문에, 어느 한 테이블에 대한 SCAN은 피할 수 없는 운명인 것이다.

설명을 좀더 편하게 하기 위해서, 다시 한번 Ctrl + K 를 눌러서 실행계획 그림표시를 제거하고 다음을 수행한다.

<SQL 1-15>

<SQL 1-15>를 수행 한 다음, <SQL 1-14>를 다시 수행해 보도록 하자. 우리는 실행 결과와 더불어 다음과 같은 실행 계획들을 얻을 수 있다. 먼저 <PLAN 1-1>은 서브쿼리에 대한 실행 계획이다.

<PLAN 1-1>

실행 계획을 보는 순서는, 처음 문장을 쫓아서 내려가면서, 분기점(두 개의 노드를 가지고 있는 계획)을 만나면 위의 노드부터 다시 쫓아 내려간다. 더 이상 내려갈 경우가 없을 때 해당 노드의 연산이 수행되고, 수행된 연산의 부모 노드의 연산이 시작된다. 기존의 분기되었던, 부모 노드일 경우, 다시 아래쪽 노드를 쫓아 내려가야 한다.이렇게 설명할 수 있는데, 말로는 설명이 만만하지가 않다.

일단, 필자가 실행계획에 번호(No)를 붙여 났기 때문에, 이 번호를 통해 순서를 파악해 보도록 하자.

먼저, 1번 실행 계획은 자식 노드 2번이 있기 때문에, 2번보다 1번이 먼저 수행된다. 2번도 자식 노드 3번을 가지고 있으므로 2번보다 3번이 먼저 수행되고, 3번도 자식 노드 4번이 있으므로 3번보다 4번이 먼저 수행된다. 그리고, 4번 노드는 두 개의 자식 노드 5번과 6번을 가지고 있다. 자식 노드중에 위에께 먼저 수행되므로, 5번이 6번보다 먼저 수행된다. 그런데, 5번은 자식 노드가 없다. 그러므로 5번 노드가 가장 먼저 수행된다. 다시, 6번 노드부터 쫓아가면, 6번 이후로 9번까지 하나 만의 자식 노드를 가지고 연결되어 있으므로, 이 부분에서는 9-8-7-6순으로 실행계획의 연산이 수행될 것이다. 종합해 보면, 실행계획이 실행 되는 순서는 이렇다. 5-9-8-7-6-4-3-2-1이 되는 것이다. 제일 처음 수행되는 5번 연산부터 알아보자. 5번 연산은 T1테이블에 대해 클러스터드 인덱스 스캔을 하고 있다. 이것은, T1테이블에 있는, 모든 데이터에 대해 순위를 구해야 하는 것이므로 피할 수 없는 운명이다. 그렇다면, 5번 연산은 무시하고 넘어가도록 하자. 5번 다음의 연산인 9번 연산을 살펴 보도록 하자. 9번 연산에서는 T2.Score가 T1.Score보다 큰 데이터를 찾고 있다. 실제적으로 순위를 구하기 위해 대상 데이터를 찾는 과정이다. 그런데, 9번 연산도, 클러스터드 인덱스 스캔을 하고 있다. 이것은, T1테이블의 각각의 데이터에 대해 스캔을 계속해서 수행한다는 것을 나타낸다. 즉, SCORE테이블의 데이터 건수만큼, SCORE테이블을 스캔하는 것이다. SCORE에는 현재 총 6건의 데이터가 있다. 그러나, 9번 문장의 수행 횟수(Execute)는 6이 아닌 5이다. 이것은,영리한 SQL Server가 캐쉬를 사용하기 때문이다. 서브쿼리에서 사용 되어진 내용을 메모리 캐쉬에 올려 났다가,똑 같은 조건의 매개변수 값(T1.Score가 된다.)이 들어오면 메모리 캐쉬에 있는 부분을 돌려주므로 9번 연산의 수행 횟수가 6이 아닌 5이다. SCORE테이블에는 85점의 점수를 가진 사람이 2명이 존재한다. 그러므로 첫 번째 85점에 대해서는 실제 테이블에 접근해서 서브쿼리의 결과를 찾았겠지만, 두 번째 85점에 대해서는 기존에 캐쉬에 저장된 결과를 참조하게 되는 것이다. 이것은 메모리에 접근해서 연산하는 수행 횟수를 줄여 주므로 성능의 이득을 주게 된다.

* 메모리 캐쉬에 대한 내용은 Windows Magazine 2004년 9월호 page67의 정재우님의 멀티 캐시의 효과를 통해서 더 자세히 알 수 있다.

9번 과정을 거쳐 8번과 7번을 수행하게 된다. 8번과 7번 역시 실행 횟수가 5번 이라는 것은, 9->8->7의 과정의 결과가 메모리 캐쉬에 저장되었다는 것을 의미한다. 8번 과정은 서브쿼리의 결과를 COUNT하는 과정이다. 이 과정을 통해 등수를 구할 수 있다는 것은 잘 알 것이다. 7번 과정은 스칼라 계산을 하는 과정이다. 정확히 7번 과정이 무슨 연산을 수행하는지는 필자도 모르겠다. 아무튼 7번 과정 자체가 성능에 영향을 미치는 요소는 아니므로 무시하고 넘어가도록 하겠다. 그 다음에는 6번 과정을 거치게 된다. 6번 과정은 해시 매치라는 과정인데, 이 과정은 해시 테이블을 만드는 과정이다. 해시 매치는 해시 함수를 사용해서 입력되는 값을 계산해서 테이블을 만들어 낸다.이 테이블은 조인을 위해 사용 된다. 이 해시 테이블은 T1.Score의 값에 순위를 저장하게 된다. 그러므로T1.Score가 95가 들어 왔을 때는 1이란 숫자를 돌려주고,  90이란 숫자가 들어왔을 때는 2라는 숫자를 돌려주게 된다. 이 해시 테이블은 무엇을 통해서 만들어 졌는가? 바로 9, 8, 7 과정을 통해서 만들어 진 것이다. 이 해시 매치는 9, 8, 7과는 다르게 수행횟수가 6번 인 것을 알 수 있다. 그것은, FROM절의 SCORE테이블에서 같은 등수의 데이터가 두 번째 들어온 경우라도 이 연산은 수행하게 된다는 것이다.

4번 과정은 실제적으로 조인을 수행하는 연산이다. 5번의 내용과 6번의 내용을 실제 INNER JOIN을 하는 과정이다. 6번의 내용은 해시 테이블이고 5번의 연산 결과는 실제 SCORE테이블이다. 6번의 해시 테이블에는 이미 각 점수 별 등수가 있으므로 이 두 데이터를 연결하는 과정을 수행하는 것이다.

지금까지의 과정을 종합해 보면, 5번의 과정에서 데이터를 입력 받아서, 9->8->7과정을 수행하게 된다.

이 결과를 메모리 캐쉬(해시 테이블)에 쌓아 놓게 되고, 6번과 4번 과정을 통해 해시 테이블과, 5번의 실제 테이블 사이에 데이터 조인을 수행하게 한다. 5번 연산의 모든 데이터에 대해서 동일한 과정을 반복 수행한다. 여기서 만약 5번 테이블의 데이터에 대해 조인될 결과 값이 이미 해시 테이블에 있는 경우는 9, 8, 7 과정을 생략하고 바로 6과 4를 거쳐 조인을 하게 된다.

마지막으로, 3번과 2번 과정은 설명을 하지 않아도 모두 알 수 있으리라 생각한다.

지금까지 서브쿼리의 실행계획을 살펴보았다. 무엇이 성능을 향상시킬 수 있는 요소라고 생각 되는가? SQL문에 대해서 여러 가지 해법이 있는 것처럼 성능 향상의 방법에도 여러 가지 해법이 있다.

필자가 생각하는 부분은 9번 과정이라고 생각된다. 실제로 그래픽으로 실행 계획을 보게 되면 9번 과정이 43%로 큰 부분을 차지하고 있다. 그러므로 9번 과정을 향상시킬 방법이 필요하다. 무엇이 있을까? 아마도 대부분의 성능 향상의 답은 인덱스 설정이다. 실제로 시스템의 성능을 향상시키는 것은 제대로 설정된 인덱스와 제대로 작성된SQL문이라고 생각 할 수 있다. 이 외에도 여러 가지 요소가 많이 있겠지만, 개발자가 할 수 있는 요소는 이 두 가지 일것이다. 사실, 인덱스도 개발자 스스로 만드는 것에는 무리가 있다. 하지만, 개발자가 적절한 인덱스가 무엇인지 알고, DBA나, 팀장을 통해 인덱스를 생성하도록 하는 것은 꼭 필요한 일이다.

9번 과정의 성능 향상을 위해서는 9번 과정의 WHERE절의 컬럼에 대해 성능을 향상시켜 주면 된다.

현재 SCORE테이블에는 StudentID에만 클러스터드 인덱스가 설정되어 있다.

*클러스터드 인덱스와 넌클러스터드의 인덱스의 차이점은 매우 중요하다. 이에 대해서는 다른 문서를 통해 알고 있기 바란다.

여러 가지 인덱스 전략이 있을 수 있다. StudentID + Score의 결합된 클러스터드 인덱스, 또는 Score로 만든, 넌클러스터드 인덱스. 이것에 대해 어떤 것이 최적일까라는 것을 알아내기란 쉽지 않다. 하지만, 어느 정도의 추측은 가능하다. 그 추측은 경험이 더해 질수록 더욱 정확해 질 것이다.

먼저, SCORE테이블에 제약사항이 무엇인가를 살펴보자. 그것은 바로 PK인 StudentID이다. 데이터베이스는 PK를 유지하기 위해서 UNIQUE 인덱스를 사용해야 한다. SQL Server는 PK에 대해서 기본적으로 클러스터드 인덱스를 만들어 버린다. 그리고, 클러스터드 인덱스 스캔과 테이블 스캔은 동일한 것이다. 클러스터드 인덱스는 성능 향상을 위한 아주 중요한 요소이다. 이 클러스터드 인덱스를 무조건 PK에 사용할 수는 없다. 필자의 생각은, StudentID에는 UNIQUE 넌클러스터드 인덱스를 만들어서 PK를 유지시켜 주고, Score에 대해서는 클러스터드 인덱스를 만들어 주는 것이다. 아마도, 이 SQL에 대해서는 최적을 발휘 할 수 있지 않을까 싶다.

먼저, 기존의 SCORE테이블을 보호하기 위해서, SCORE2라는 테이블을 만들고, 인덱스를 설정 해 주는 과정을 거치도록 하자.

<SQL 1-16>

자 그럼 <SQL 1-5>의 서브쿼리를 다음처럼 SCORE와 SCORE2에 대해 실행해서 실행 비용을 비교해 보도록 하자. 우리는 실행 비용을 쉽게 판단 할 수 있도록 그래픽으로 실행계획을 볼 것이다. 그래픽으로 실행 계획을 보기 위해서 먼저 SET STATISTICS PROFILE OFF 를 실행해서 기존의 문자열로 출력되던 실행 프로필을 OFF 시켜야 한다.

<SQL 1-17>

문자열 실행 프로필을 OFF시킨 후에는 다시 Ctrl + K 를 눌러서 그래픽 실행 계획을 활성화한다.

그 다음, 다음의 두 개의 SQL을 동시에 실행한다.

<SQL 1-18>

첫 번째 SQL은 기존의 SCORE테이블이고, 두 번째 SQL은 새롭게 인덱스를 설정해 준 SCORE2 테이블이다.

그래픽 실행계획을 보면, 첫 번째 SQL이 60%의 비용을, 두 번째 SQL이 40%의 비용을 사용하는 것을 알 수 있다.이것은 기존의 <PLAN 1-1>에서 9번 과정이 인덱스 스캔에서 인덱스 씨크(Seek)로 변경되었기 때문이다. 새로 만들어진 Score에 대한 클러스터드 인덱스는 9번 과정을 인덱스 씨크로 변경하는데 도움을 준 것이다.

이 외에 다른 방법들을 실제, 만들어 보고 테스트해 보기 바란다. 이런, 고민과 테스트는 자신의 발전에 큰 도움을 줄 것이다.

계속해서 셀프조인으로 해결했던 SQL의 실행 계획을 보도록 하자.

<PLAN 1-2>

셀프조인으로 사용한 순위 SQL은 기존의 서브쿼리로 해결한 방법의 실행 계획과는 틀리다. 제일 눈에 뛰는 것은 조인이 Left Outer Join이며 해시매치를 사용하지 않는다는 것이다.

먼저, 이 실행계획이 실행되는 순서를 찾아보자. 한 번 해보았으므로 쉽게 알 수 있을 것이다. 가장 먼저 수행되는 것은 7번이다. 그 다음 8번, 6번부터 2번 까지 역순으로 수행된다. 7->8->6->5->4->3->2->1 순으로 수행되는 것이다. 이번 실행 계획은 이전의 서브쿼리 실행계획보다 확실히 보기 쉬운 것 같다.

먼저 7번 연산은 클러스터드 인덱스 스캔을 하고 있다. 8번 연산도 클러스터드 인덱스 스캔을 하고 있으며 실행 횟수가 7번 연산은 1번인데 비해 8번 연산은 6번 이다. 이것은 7번 연산의 데이터를 스캔 하면서 각 데이터에 대해 8번 연산을 한 번씩 수행한 다는 것이다. 이것이 실제적으로 6번 과정을 위한 과정이다. 6번 과정은 외부 조인을 수행하고 있다. T1.Score < T2.Score의 조건으로 조인을 수행한다. 6번 과정을 통해 총 15건의 데이터가 만들어 진다. 그 후 5번 과정을 거쳐서 GROUP BY를 수행하게 된다. GROUP BY와 MIN과 COUNT를 통해 해당 StudentID별로 점수와 등수를 구해낸다. 4번 연산은 스칼라 연산이고, 3번 연산도 스칼라 연산이다. 4번 연산은 결과를 보여주기 위해 형 변환을 수행하는 과정이고,(아마도, 등수를 센 것을 숫자형으로 변환시크는 과정일 것이다.) 3번 연산은 등수에 + 1을 해줌으로서 우리가 원하는 등수를 정확히 표현해 주도록 하는 과정이다. 마지막으로 2번은 결과를 보기 좋도록 정렬을 하는 과정이다.

이 실행 계획에서 성능을 향상 시킬 수 있는 부분은 무엇일까? 이것 역시, 서브쿼리와 마찬가지의 인덱스를 설정해 주면 좀 더 나은 성능을 낼 것 같다. 그러므로 SCORE2를 사용해서 똑 같은 셀프조인 쿼리를 만들어 비교를 해보도록 하자. 그래픽 실행 계획을 보는 방법에 대해서는 더 이상 설명하지 않겠다. 아울러, 실행 프로필을 설정하고 보는 방법에 대해서도 더 이상 설명하지는 않겠다.

다음과 같은 SQL을 동시에 날려보도록 하자.

<SQL 1-19>

순위를 구하기 위한 서브쿼리 방법으로 SCORE와 SCORE2를 실행해서 비교했던 것과 동일하게 SCORE를 사용한 첫 번째 셀프조인 SQL은 60%의 비용을 사용하고, SCORE2를 사용한 두 번째 SQL은 40%의 비용이 소모되는 것을 알 수 있었다.

자, 이제, 순위 구하는 SQL에서 좋은 성능을 내기 위한 방법은 무엇인가? 조인 조건이 되는 컬럼 부분에 인덱스를 만들어 주는 것이다. 그리고 넌클러스터드 인덱스보다 클러스터드 인덱스가 좋은 성능을 내는 것은 당연한 일일 것이다. 우리가 사용했던 예제 SQL만을 볼 때는 이와 같은 결론을 내릴 수 있다.

하지만, 실제업무에서는 WHERE절의 조건을 충분히 줄여 주는 대상에 인덱스가 있는 것이 아주 중요하다. 6건의 데이터를 가지고 성능을 얘기한다는 건 우스운 얘기이다. 6건의 데이터를 가지고, 아무리 복잡한 연산을 수행하는SQL을 만든다 해도, 데이터베이스는 빛의 속도로 이것을 해결할 것이기 때문이다. 하지만 6건의 데이터를 가지고 성능을 테스트한 우리는 훨씬 더 많은 수의 데이터가 들어 있는 테이블에 대한 SQL도 테스트 할 수 있는 능력을 배운 것이다.

지금 우리가 했던 SQL에 대해 최적의 인덱스가 무엇인지가 중요하지 않다. 제일 중요한 건, 어느 연산을 변경해서 성능을 향상시킬 수 있는 것을 찾아내는 부분이다. 필자 역시 계속해서 노력해야 하는 부분이고, 여러 개발자들도 끊임없이 관찰하고 연구해야 하는 부분이다.

순위 구하는 문제에 대해서 마무리 한다면, 순위를 구하기 위해 어떤 방법을 사용했는가 보다는 데이터를 어떻게 관찰하고 연구해서 결과를 만들어 냈는지의 과정이 더 중요하며, 그 과정에 대해 많은 개발자들이 끊임없이 노력하기를 바란다는 말로 마무리를 하겠다.

출처 : http://yaku.tistory.com/440

Explain 정보 보는법

1. 인덱스가 적절히 사용되고 있는지 검토

2. 나열된 순서는 MYSQL이 쿼리처리에 사용하는 순서대로 출력

Explain의 각 행 설명

1. id : SELECT 번호, 쿼리내의 SELECT 의 구분번호, 실행순서

2. select_type : SELECT 의 타입

- SIMPLE: 단순 SELECT (UNION 이나 서브쿼리를 사용하지 않음)

- PRIMARY: 가장 외곽의 SELECT

- UNION: UNION 에서의 두번째 혹은 나중에 따라오는 SELECT

- DEPENDENT UNION: UNION 에서의 두번째 혹은 나중에 따라오는 SELECT, 외곽쿼리에 의존적

- UNION RESULT: UNION 의 결과물

- SUBQUERY: 서브쿼리의 첫번째 SELECT

- DEPENDENT SUBQUERY: 서브쿼리의 첫번째 SELECT, 외곽쿼리에 의존적

- DRIVED: SELECT 로 추출된 테이블 (FROM 절 내부의 서브쿼리)

3. table : table명

4. type : 조인타입, 우수한 순서대로… 뒤로갈수록 나쁜 조인형태

1) system : 테이블에 단 하나의 행만 존재(시스템 테이블). const join 의 특수한 경우 (거의 메모리에 상주)

2) const : 많아야 하나의 매치되는 행만 존재할 때, PRIMARY KEY 나 UNIQUE index 를 상수와 비교할 때, 각 컬럼값은 나머지 연산에서 상수로 간주, 처음 한번만 읽어들이면 되므로 매우 빠름

3) eq_ref : 조인수행을 위해 각 테이블에서 하나씩의 행만이 읽히는 경우, 조인연산에  PRIMARY KEY 나 UNIQUE index 인덱스가 사용되는 경우, 인덱스된 컬럼이 = 연산에 사용되는 경우

4) ref : 이전 테이블과의 조인에 사용될 매치되는 인덱스의 모든행이 이 테이블에서 읽혀질 때, leftmost prefix 키만을 사용하거나 사용된 키가 PRIMARY KEY 나 UNIQUE 가 아닐때

(즉 키값으로 단일행을 추출할수 없을때)

사용된 키가 적은수의 행과 매치되면 이것은 적절한 조인 타입.

ref 는 인덱스된 컬럼과 = 연산에서 사용됨

5) ref_or_null : ref 와 같지만 NULL 값을 포함하는 행에대한 검색이 수반될 때. 서브쿼리 처리에서 대개 사용됨

6) index_merge : 인덱스 병합 최적화가 적용되는 조인 타입. 이 경우, key 컬럼은 사용된 인덱스의 리스트를 나타내며 key_len 컬럼은 사용된 인덱스중 가장 긴 key 명을 나타냄

7) unique_subquery : 몇몇 IN 서브쿼리 처리에서 ref 타입대신 사용됨. unique_subquery 는 성능향상을 위해 서브쿼리를 단순 index 검색 함수로 대체함.

8) index_subquery : unique_subquery 와 마찬가지로 IN 서브쿼리를 대체. 단, 서브쿼리에서 non-unique 인덱스가 사용될때 동작 함

9) range : 인덱스를 사용하여 주어진 범위 내의 행들만 추출

key 컬럼: 사용된 인덱스 / key_len: 사용된 가장 긴 key 부분 / ref 컬럼: 이 타입의 조인에서 NULL

키 컬럼이 상수와 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN 또는 IN 연산에 사용될때 적용됨

10) index : 인덱스가 스캔된다는걸 제외하면 ALL 과 같음. 일반적으로 인덱스 파일이 데이타파일보다 작기 때문에 ALL 보다는 빠름. MySQL 은 쿼리에서 단일 인덱스의 일부분인 컬럼을 사용할때 이 조인타입을 적용함

11) ALL

이전 테이블과의 조인을 위해 풀스캔. (조인에 쓰인) 첫번째 테이블이 고정이 아니라면 비효율적. 대부분의 경우에 아주 느린 성능

정리:

system - const - eq_ref - ref - ref_or_null - index_mergy - unique_subquery -  ndex_subquery - range - index - ALL

5. possible_keys : MySQL 이 해당 테이블의 검색에 사용할수 있는 인덱스들. possible_keys 에 나타난 인덱스들이 결과에 나타난 테이블 순서에서 실제 사용할 수 없을수도 있음

6. key :  MySQL 이 실제 사용한 key(index)

7. key_len : MySQL 이 사용한 인덱스의 길이, key 컬럼값이 NULL 이면 이값도 NULL

key_len 값으로 MySQL 이 실제 복수컬럼 키중 얼마나 많은 부분을 사용할 것인지 알 수 있음

8. ref : 행을 추출하는데 키와 함께 사용된 컬럼이나 상수값

9. rows : 쿼리 수행에서 MySQL 이 예상하는 검색해야할 행수

10. Extra : MySQL 이 쿼리를 해석한 추가적인 정보를 나타냄

1) Distinct: MySQL 이 매치되는 첫행을 찾는 즉시 검색을 중단한다는 의미

2) Not exists: MySQL 이 LEFT JOIN 을 수행함에 매치되는 한 행을 찾으면 더이상 매치되는 행을 검색x

3) range checked for each record (index map: #): MySQL 이 사용할 좋은 인덱스가 없음 의미

4) Using filesort: MySQL 이 정렬을 위해 추가적인 과정을 필요로 함

5) Using index: 컬럼정보가 실제 테이블이 아닌 인덱스트리에서 추출, 쿼리에서 단일 인덱스된 컬럼들만을 사용하는 경우

6) Using temporary: MySQL 이 결과의 재사용을 위해 임시테이블을 사용, 쿼리 내에 GROUP BY 와 ORDER BY 절이 각기 다른 컬럼을 사용할때 발생

7) Using where: WHERE 절이 다음 조인에 사용될 행이나 클라이언트에게 돌려질 행을 제한하는 경우 테이블의 모든 행을 검사할 의도가 아니면 ALL 이나 index 라면 쿼리사용이 잘못된 것임

Using sort_union(…) , Using union(…) , Using intersect(…)

Using index for group-by: Using index 와 접근방식이 같으며, 추가적인 디스크 접근 없이 GROUP BY 나 DICTINCT 쿼리에 사용된 모든 컬럼에 대한 인덱스를 찾았음을 의미