2011년 1월 30일 일요일

MySQL 5.1.42 SELECT 쿼리의 SharedLock 관련 버그


MySQL 5.1.42 버전에서 SELECT 쿼리가 Shared(Read) Lock을 거는 버그가 있었다.
모든 쿼리가 그런 것이 아니라, 확인된 쿼리는 아래 형태의 SubQuery로 Counting하는 쿼리이다.

간단히 재현하는 방법은 아래와 같다.

-------------------------------------------------------------
Session 1
-------------------------------------------------------------
CREATE TABLE lock_test (
  fd1 int not null,
  fd2 int not null,
  fd3 int not null,
  INDEX ix_12 (fd1, fd2),
  INDEX ix_21 (fd2, fd1)
) ENGINE=InnoDB;

INSERT INTO lock_test VALUES (1,2), (2,1);

COMMIT;

BEGIN;
SELECT (
  (SELECT COUNT(*) FROM lock_test WHERE fd1=1) +
  (SELECT COUNT(*) FROM lock_test WHERE fd2=1)
) AS cnt;


-------------------------------------------------------------
Session 2
-------------------------------------------------------------
SHOW ENGINE INNODB STATUS;


...
---TRANSACTION 1 3051889568, ACTIVE 5 sec, process no 9090, OS thread id 1261443392
19 lock struct(s), heap size 6752, 2 row lock(s)
MySQL thread id 178344, query id 17362465365 localhost root
Trx read view will not see trx with id >= 1 3051889569, sees < 1 3051889555
...

이 버그는 MySQL 5.0.68 이나 MySQL 5.1.54 버전에서는 발견되지 않았으며,
MySQL 5.1.42에서만 나타났고, MySQL 5.1.45 버전에서 Fix되었다.

그리고 관련해서 SHOW INNODB STATUS ENGINE에서 Deadlock을 보고하는 경우가 있는데,
실제로 이 내용은 Deadlock이 아니라, InnoDB가 CPU 사용률을 줄이기 위해서
ShortPath로 Deadlock으로 간주하도록 하는 부분도 있었다.

Bug #49001 (http://bugs.mysql.com/49001)

2011년 1월 13일 목요일

MySQL 5.5 GA 버전 릴리즈 : 새로운 기능(New Features) 소개

조금은 지난 일이지만, MySQL 5.5의 GA(General Available) 버전이 릴리즈 되었다.
이번 버전은 MySQL이 Oracle로 인수되고 난 이후 처음 릴리즈된 버전이다라는 것이
나름 Oracle 입장에서는 큰 의미를 주고 있는 듯 하며,
그 때문인지 이런 저런 새로운 기능들과 성능 향상을 WhitePaper에서 이야기하고 있다.

하지만, 대 부분은 InnoDB 가 플러그인 버전으로 변경되는 MySQL 5.1 수준에서
적용된 내용들이 재탕으로 이야기되고 있는 부분이 많고,
기존 버전에 비해 Linux는 370%, Windows는 1500%가 향상되었다고 이야기하고 있는데,
간단히 그래프를 보면, 개인적으로는 성능 향상이라고 하기보다는 동시성 처리가 상당히 개선되었다고
생각된다.


WhitePaper에서 이야기하고 있는 더 상세한 개선점들은 아래와 같다.


주요 개선 사항


  • InnoDB가 이제부터는 MySQL의 기본 스토리지 엔진으로 적용됨
    InnoDB as the default database storage engine 
  • 윈도우즈 서버에서 성능과 확장성 개선됨
    Improved Performance and Scalability on Windows 
  • 모든 플랫폼의 서버에서 멀티 코어 아키텍쳐를 최대한 사용 가능하도록 성능과 확장성 개선
    Improved Performance and Scalability to fully utilize the computing power of modern multi-core architectures across all platforms 
  • 가용성 개선됨
    Improved Availability 
  • 관리성과 효율성 개선됨
    Improved Manageability and Efficiency 
  • 사용성 개선됨
    Improved Usability 
  • 진단 도구들의 개선
    Improved Instrumentation and Diagnostics



[성능 및 확장성] 개선 사항

  • 쓰레드의 동시성 처리 성능 향상
    Improved Default Thread Concurrency
  • 백그라운드 I/O 쓰레드의 컨트롤 기능 도입
    Control of Background I/O Threads
  • InnoDB의 마스터(Replication의 마스터 아님) 쓰레드 의 I/O 비율 조절 기능 도입
    Control of Master Thread I/O Rate
  • 운영체제의 메모리 할당 기능을 사용하도록 조절 가능
    Control of Using Operating System Memory Allocators
  • AdaptiveHashIndex의 관리 기능 도입
    Control of Adaptive Hash Indexing
  • Insert Buffer 기능의 관리 파라미터 도입
    Control of Insert Buffering
  • Faster locking 알고리즘을 이용한 확장성 개선
    Improved scalability via Faster Locking algorithm
  • Group Commit 기능 복구(다시 사용할 수 있도록 개선됨)
    Restored Group Commit
  • 복구 (Recovery) 성능 개선
    Improved Recovery Performance 
  • InnoDB의 Buffer pool을 여러개 설정할 수 있도록 개선됨
    Multiple Buffer Pool Instances
  • Rollback 세그먼트를 여러개 설정할 수 있도록 개선됨
    Multiple Rollback Segments
  • Linux의 네이티브 비동기 I/O 적용
    Native Asynchronous I/O for Linux
  • Insert buffer의 기능을 인덱스의 INSERT뿐만 아니라 DELETE까지도 확장 적용
    Extended Change Buffering: Now with Delete and Purge Buffering
  • InnoDB buffer pool의 작업과 Flush 리스트를 관리하는 Mutext를 분리 적용
    Improved Log Sys Mutex and Separate Flush List Mutex
  • Bufer pool의 불필요한 페이지의 purge 작업의 스케쥴링 개선
    Improved Purge Scheduling
  • 트랜잭션 내에서 메타 락(오브젝트 잠금)과 다른 트랜잭션의 DDL 문장간의 락 점유 방식 개선
    Improved Metadata Locking Within Transactions





[가용성] 개선 사항

  • 반 동기화 복제
    Semi-synchronous Replication
  • 복제시 마스터와 슬레이브 사이의 Heartbeat 메시지 적용
    Replication Heartbeat
  • Relay 로그 자동 복구 기능
    Automatic Relay Log Recovery
  • 특정 Master의 Replication 로그 필터링 기능
    Replication Per Server Filtering
  • 마스터와 슬레이브간의 다른 데이터 타입 허용(동일 String 또는 Numeric 타입들 내에서)
    Replication Slave Side Data Type Conversions


[관리 및 효율성] 개선 사항

  • 빠른 인덱스 생성 및 삭제
    Faster Index Creation
  • 효율적인 데이터 압축
    Efficient Data Compression
  • LOB (BLOB, TEXT) 데이터 타입이나 길이가 긴 VARCHAR 타입의 관리 효율성 향상
    Efficient Storage Options for Large Objects and Variable-Length Columns
  • INFORMATION_SCHEMA의 잠금 및 트랜잭션 그리고 압축 관련 딕셔너리 추가됨
    New INFORMATION_SCHEMA tables 
  •  


[사용성] 개선 사항

  • SIGNAL과 RESIGNAL 명령 추가 (StoredRoutine 관련)
    New SQL Syntax for SIGNAL/RESIGNAL
  • 새로운 테이블이나 인덱스 파티셔닝 기능(Non-Integer 타입으로 List나 Range 파티션 생성 가능)
    New Table/Index Partitioning Options


[진단도구] 개선 사항

  • INFORMATION_SCHEMA의 잠금 및 트랜잭션 그리고 압축 관련 딕셔너리 추가됨
    New PERFORMANCE_SCHEMA


가장 중요한 변화 : MySQL 5.5 버전부터는 Enterprise Edition의 경우 소스를 다운로드할 수 없게 되었다. 이제부터 Enterprise edition은 빌드된 MySQL만 사용할 수 있게 될 것 같다.
Community edition은 예전과 동일함








2011년 1월 11일 화요일

MySQL의 버전별 기능(Features) 변경 이력


VersionFeatures
추가변경삭제
5.5
  • MyISAM 대신 InnoDB가 MySQL의 기본 스토리지 엔진으로 채택
  • 5.4.2
  • Plugin버전의 InnoDB가 Builtin 버전으로 다시 적용
  • 5.1.38
  • InnoDB Plugin
  • 5.1.24
    (Enterprise version)

  • "SHOW PROFILE"
  • 5.1.12
  • "general_log" 파라미터

  • General query log를 동적으로 변경 가능
  • 5.1.8
  • "Mixed" 복제 모드
  • 5.1.6
  • Partition pruning 기능
  • 5.1.5
  • EXPLAIN PARTITIONS(파티션 테이블의 실행 계획) 지원
  • "RBR"(Row Based Replication) 복제 모드
  • 5.1
  • Plugin API 도입
  • Plugin버전의 InnoDB 릴리즈 (InnoDB 의 많은 성능 개선과 변화가 있음)

  • BDB 스토리지 엔진
  • 5.0.32
    (Community version)

  • "SHOW PROFILES"
  • 5.0.7
  • LIMIT의 파라미터도 PreparedStatement에서 변수화 가능
  • 5.0.5
  • BIT 데이터 타입이 MEMORY, InnoDB, BDB, NDBCLUSTER 스토리지 엔진에 구현됨
  • 5.0.3
  • FEDERATED 스토리지 엔진
  • 신규 함수 추가
    STDDEV_POP()
    STDDEV_SAMP()
    VAR_POP()
    VAR_SAMP()

  • BIT 데이터 타입이 TINYINT와 호환성 없어짐


  • NUMERIC와 DECIMAL 타입의 저장 방식이 String에서 Binary로 변경
  • 5.0.2
  • TRIGGER 도입

  • HAVING 조건에 SELECT컬럼, GROUP-BY컬럼, OUTER-서브쿼리의 값 사용 가능(ANSI 표준)
  • 5.0.1
  • VIEW 도입

  • HAVING 조건에 SELECT컬럼, GROUP-BY컬럼, OUTER-서브쿼리의 값 사용 가능(ANSI 표준)
  • 5.0
  • StoredRoutine (Procedure,Function) 도입
  • CURSOR 도입
  • Archive 스토리지 엔진
  • INFORMATION_SCHEMA 딕셔너리 데이터베이스 도입 (ANSI 표준)

  • ISAM 스토리지 엔진 제거
  • 4.1.11
  • Blackhole 스토리지 엔진
  • 4.1.4
  • CVS 스토리지 엔진
  • 4.1
  • SubQuery 도입
  • WHOW WARNINGS
  • CREATE TABLE ... LIKE ...
  • GROUP_CONCAT() 구현
  • 유니코드(UTF8, UCS2) 지원
  • GIS 관련 기능(Spatial extension) 지원
  • ALTER DATABASE 명령 지원
  • DUAL 테이블 내부 지원(타 DBMS와의 호환성 유지)
    "SELECT 1" 명령과 "SELECT 1 FROM DUAL" 명령은 동일
  • Memory 스토리지 엔진에서 B-Tree 허용
  • EXPLAIN EXTENDED 구현

  • Column 코멘트 구현(CREATE TABLE...)
  • PASSWORD() 함수의 알고리즘 업그레이드
    기존 알고리즘은 OLD_PASSWORD()로 변경됨
  • CHAR, VARCHAR 타입의 길이가 바이트수에서 문자수로 변경됨
  • 파생 테이블(Derived tables) 내에서 UNION 사용 가능
  • 4.0.18
  • "TYPE" 키워드가 "ENGINE" 키워드로 변경(CREATE TABLE...)
  • 4.0.14
  • InnoDB의 BLOB와 TEXT 타입에 대한 인덱스 지원
  • 4.0.4
  • JOIN DELETE (Multiple Delete) 도입
  • JOIN UPDATE (Multiple Update) 도입
  • 4.0.2
  • Memory 스토리지 엔진에서 NULLABLE 컬럼의 인덱스 지원

  • VARCHAR 컬럼의 길이가 1~255에서 0~255로 변경됨
  • 4.0.1
  • Query Cache 도입
  • 4.0
  • UNION 집합 연산 도입
  • SQL_CALC_FOUND_ROWS 힌트와 FOUND_ROWS() 함수 구현

  • UPDATE와 DELETE 구문에 ORDER BY 사용 허용
  • 3.23
  • EXPLAIN(쿼리 실행계획) 구현
  • 전문 검색(Fulltext search) 도입
  • JOIN(SELECT만) 도입
  • NULL-SAFE 연산자(<=>) 도입

  • 길이가 0인 CHAR 컬럼 허용
  • 2011년 1월 3일 월요일

    JOIN DELETE (Multiple-table Delete)

    두개의 테이블을 조인하여 UPDATE를 실행하는 것(JOIN UPDATE)과 같이,
    두개의 테이블을 조인하여 그 결과 레코드를 삭제하는 것도 가능하다.

    이를 JOIN DELETE 또는 Multiple-Table DELETE라고 하는데,
    JOIN DELETE는 아래 두 가지 문법으로 작성할 수 있다.

    • DELETE와 FROM 절 사이에 삭제할 테이블 명시

      DELETE
      t1, t2
      FROM test1 t1 INNER JOIN test2 t2 INNER JOIN test3 t3
      WHERE t1.id=t2.id
        AND t2.id=t3.id;

    • FROM과 USING 절 사이에 삭제할 테이블 명시
      DELETE
      FROM t1, t2
        USING test1 t1 INNER JOIN test2 t2 INNER JOIN test3 t3
      WHERE t1.id=t2.id
        AND t2.id=t3.id;

    JOIN DELETE에서도 주의해야 할 사항이 있는데,
    • 조인 결과 레코드가 삭제되는 테이블이 어느 테이블인가 ?.
      • 첫번째 문법에서는 DELETE 키워드와 FROM 절 사이에 명시된 테이블의 레코드만 삭제한다.
      • 두번째 문법에서는 FROM 절과 USING 절 사이에 명시된 테이블의 레코드만 삭제한다.
      • 위의 두 예제에서는 t1(test1) 테이블과 t2(test2) 테이블의 레코드만 삭제하게 된다. t3(test3)의 레코드는 조인에만 참여하고 삭제되지는 않는다.
    • 테이블의 별명(Alias)는 어디에서 정의해야 하는가 ?.
      • 첫번째 문법에서는 FROM 절 이하에서 테이블의 별명(Alias)를 지정하며, 이 이외의 위치에서는 Alias를 지정할 수 없고, FROM 절에서 정의된 Alias를 사용만 해야 한다.
      • 두번째 문법에서는 USING 절 이하에서만 테이블의 별명(Alias)를 정의해야 한다.

    JOIN UPDATE (Multiple-table Update)

    MySQL에서도 두개 이상의 테이블을 조인하여,
    어떤 테이블의 필드 값을 또 다른 테이블의 컬럼에 업데이트하는 것이 가능하다.
    물론, 이때 2개 이상의 테이블의 레코드를 상호 업데이트하는 것도 가능하다.


    이러한 형태의 작업을 JOIN UPDATE 또는 Multiple table Update라고 표현한다.
    아래의 쿼리를 한번 살펴보자.


    UPDATE test1 t1, test2 t2
    SET t1.target_fd=t2.source_fd
    WHERE t1.fdpk=t2.fdpk;


    이 쿼리는 test1 테이블과 test2 테이블을 fdpk 컬럼으로 조인한 뒤,
    test2 테이블의 source_fd 컬럼의 값을 test1 테이블의 target_fd 컬럼에 업데이트를 실행하게 된다.
    이 예제는 하나의 테이블만 업데이트하고 있지만, 아래와 같이 상호 업데이트 하는 형태도 가능하다.


    UPDATE test1 t1, test2 t2 
    SET t1.target_fd=t2.source_fd,
      t2.target_fd=t1.source_fd
    WHERE t1.fdpk=t2.fdpk;

    물론 3개 이상의 테이블을 조인하여
    n개의 테이블로부터 필드 값을 모아서 하나의 테이블로 업데이트하는 것도 가능하다.



    단, 이런 형태의 업데이트에서 주의해야 할것이 있다.
    1. UPDATE 쿼리에 대해서는 실행 계획을 확인할 수 없으므로, JOIN의 순서를 확인할 수 없다.
       조인의 순서에 따라서 상당한 처리 성능 차이를 보일 수 있으므로, 가능한 조인의 순서를 고정시켜서 실행한다.
    2. JOIN에 참여하는 테이블 Tab1과 Tab2가 1:M 관계로 조인될 경우
       Tab1 테이블의 값을 Tab2로 업데이트하는 것은 문제없지만, Tab2를 Tab1으로 업데이트하는 경우
       M개의 값 중에서 어떤 값이 업데이트될지 예측할 수 없다.
       M:M 관계의 조인이라면 어느 쪽으로 업데이트되든지 업데이트 되는 값을 예측할 수 없게 된다.
       

    유용한 VI 명령어 모음

    저장 및 파일 읽기 명령
    vi -r <파일명> : VI가 비정상 종료되었을 때, 작성중이던 파일을 복구한다. (Vi가 아닌 Shell명령)
    :1,4w <파일명> : 1부터 4줄까지를 지정된 파일명으로 저장한다.
    :r <파일명>    : 현재 편집중인 내용에 <파일명> 파일의 내용을 읽어서 덧붙인다.

    라인 삭제
    d1G : 문서의 첫번째 줄부터 현재 이전 줄까지 삭제
    dG  : 문서의 현재 줄부터 끝까지 삭제

    문서의 영역 선택 및 위치 마킹
    mx  : 현재 라인을 "x" 라는 이름으로 마킹 ('m' 다음 문자의 이름으로 현재 라인을 마킹)
    v   : Visual 영역 선택

    명령 반복
    .   : 마지막 실행했던 명령 재실행

    대소문자 전환
    'v' 명령으로 Visual 영역을 선택한 후, '~' 키를 입력하면 대문자를 소문자로 소문자를 대문자로 전환
    :%s/.*/L&/ : 문서 전체를 소문자로 변환
    :%s/.*/U&/ : 문서 전체를 대문자로 변환

    들여쓰기
    >>  : 명령을 입력하면 현재 줄만 들여쓰기 실행
    n>> : 명령을 입력하면 현재 줄부터 n번째 줄까지 들여쓰기 실행
    >%  : 괄호가 시작되는 부분에 커서를 옮기고 실행해야 하며, 괄호의 짝이 쌍으로 끝나는 부분까지 들여쓰기 실행 (소괄호, 중괄호, 대괄호 모두 적용되며, 괄호가 포함된 라인까지 들여쓰기 함)
    >}  : 한 문단을 들여쓰기 실행

    내어쓰기
    <<  : 명령을 입력하면 현재 줄만 내어쓰기 실행
    n<< : 명령을 입력하면 현재 줄부터 n번째 줄까지 내어쓰기 실행
    <%  : 괄호가 시작되는 부분에 커서를 옮기고 실행해야 하며, 괄호의 짝이 쌍으로 끝나는 부분까지 내어쓰기 실행 (소괄호, 중괄호, 대괄호 모두 적용되며, 괄호가 포함된 라인까지 내어쓰기 함)
    <}  : 한 문단을 내어쓰기 실행

    문서 검색
    /검색어 : '검색어'를 문서의 뒷부분으로 검색 (커서 기준)
    ?검색어 : '검색어'를 문서의 앞부분으로 검색 (커서 기준)
    n        : 검색을 순방향으로 진행
    N        : 검색을 역방향으로 진행

    문자열 변환
    :s/변환전/변환후/    : 현재 줄에서 처음 검색된 "변환전" 문자열을 "변환후"로 대체
    :s/변환전/변환후/g   : 현재 줄의 모든 "변환전" 문자열을 "변환후" 로 대체
    :%s/변환전/변환후/g  : 문서의 모든 "변환전" 문자열을 "변환후" 로 대체
    :%s/변환전/변환후/gc : 문서의 모든 "변환전" 문자열을 "변환후" 로 대체 (대체시 확인 메시지 출력)

    VI 모드 변경
    :set paste    : 붙혀넣기 모드 활성화 (자동 들여쓰기 방지) <-> :set nopaste
    :set ic       : 검색시 대소문자 구분 무시                 <-> :set noic
    :se ai        : 자동 들여쓰기                             <-> :se noai
    :se list      : 탭과 새줄을 특수문자로 표시               <-> :se nolist
    :se nu        : 줄 번호를 출력                            <-> :set nonu
    :se sm        : 괄호 입력시 대응하는 괄호 표시            <-> se nosm
    :se sw=2      : 들여쓰기를 공백 2개로 설정한다.
    :se tabstop=4 : 탭 크기를 공백 4개로 설정한다.

    커서 이동
    G  : 파일의 제일 마지막 줄로 이동
    nG : 파일의 n 번째 줄로 이동
    1G : 파일의 제일 첫번째 줄로 이동
    %  : 괄호 짝을 찾아서 이동
    )  : 다음 문장으로(구두점으로 구분) 이동
    }  : 다음 문단으로(빈 줄로 구분) 이동
    ]  : 다음 섹션으로 이동
    'x : 'x'로 마킹된 줄로 이동

    VI에서 외부 명령 실행
    !!                 : 현재 줄을 외부 프로그램의 입력으로 제공하고, 외부 프로그램을 실행한 결과로 대체한다.
    !!date             : 현재 줄을 날짜로 대체
    3!!sort            : 현재 줄 부터 3줄을 sort의 입력으로 제공하고, 그 실행 결과로 대체한다.
    !}sort             : 현재 줄 부터 문단 끝(첫번째 빈 줄 나오는 부분)을 정렬한다.
    :'x,. !sed '/^$/d' : 마크 'x' 줄부터 현재 줄까지 빈 줄을 삭제한다.
    :'x,. !awk '{print $3 " " $2 " " $1}' : 마크 'x' 부터 현재 줄까지 3 컬럼을 역순으로 배열한다.

    DISTINCT 와 GROUP BY의 차이

    DISTINCT는 주로 UNIQUE한 컬럼이나 튜플(레코드)을 조회하는 경우 사용되며,
    GROUP BY는 데이터를 그룹핑해서 그 결과를 가져오는 경우 사용되는 쿼리 형태이다.


    하지만 두 작업은 조금만 생각해보면 동일한 형태의 작업이라는 것을 쉽게 알 수 있으며,
    일부 작업의 경우 DISTINCT로 동시에 GROUP BY로도 처리될 수 있는 쿼리들이 있다.
    그래서 DISTINCT를 사용해야 할지, GROUP BY를 사용해서 데이터를 조회하는 것이 
    좋을지 고민되는 경우들이 가끔 있다.


    간단하게 아래 예를 살펴 보자
    1. SELECT DISTINCT fd1 FROM tab;
    2. SELECT DISTINCT fd1, fd2 FROM tab;


    위의 두개 쿼리는 간단히 GROUP BY로 바꿔서 실행할 수 있다.
    1. SELECT fd1 FROM tab GROUP BY fd1;
    2. SELECT fd1, fd2 FROM tab GROUP BY fd1, fd2;


    그렇다면 이 예제의 쿼리에서 DISTINCT와 GROUP BY 는 어떤 부분이 다를까 ?
    사실 이런 형태의 DISTINCT는 내부적으로 GROUP BY와 동일한 코드를 사용한다.
    즉, 동일한 처리를 하게 된다는 것이다. 


    하지만 더 중요한 차이가 있다.
    DISTINCT의 결과를 정렬된 결과가 아니지만, GROUP BY는 정렬된 결과를 보내준다.
    GROUP BY의 작업을 크게 "그룹핑" + "정렬"로 나누어서 본다면, DISTINCT는 "그룹핑" 작업만
    수행하고 "정렬" 작업은 수행하지 않는 것이다.
    그런데, 여기서 "정렬"은 "그룹핑" 과정의 산물이 아닌 부가적인 작업이다.


    최종적으로, 이 예제의 DISTINCT와 GROUP BY는 일부 작업은 동일하지만 GROUP BY는
    "정렬"을 하기 위한 부가적인 작업을 더 하게 된다.


    만약 "정렬"이 필요하지 않다면 DISTINCT를 사용하는 것이 성능상 더 빠르다고 볼 수 있다.
    하지만, GROUP BY를 사용하는 경우에는 정렬을 하지 않도록 유도할 수 있다.
    (자세한 내용은 "GROUP BY의 Filesort 작업 제거"를 참조)




    참고로
    GROUP BY와 DISTINCT는 각자 고유의 기능이 있다.


    DISTINCT로만 가능한 기능
    1. SELECT COUNT(DISTINCT fd1) FROM tab;
        -- // 이런 형태의 쿼리는 서브 쿼리를 사용하지 않으면 GROUP BY로는 작성하기 어렵다.


    GROUP BY로만 가능한 기능
    1. SELECT fd1, MIN(fd2), MAX(fd2) FROM tab GROUP BY fd1;
        -- // 이렇게 집합함수(Aggregation)가 필요한 경우에는 GROUP BY를 사용해야 한다.




    <<주의사항>>
    가끔 어떤 사용자는 DISTINCT가 마치 함수인 것처럼 (괄호를 사용하여) 아래와 같이 사용을 하는데
    만약 fd1 컬럼은 unique 값, fd2는 전체 값을 원한다면 절대 그 결과를 얻을 수 없다.


    SELECT DISTINCT(fd1), fd2 FROM tab;

    SELECT 문장에 DISTINCT라는 키워드가 있으면, MySQL은 SELECT되는 모든 컬럼(튜플)들에 대해서 DISTINCT를 적용해서 결과를 보내주게 된다.
    위와 같은 요건을 처리하기 위해서도 아래와 같이 GROUP BY로만 해결할 수 있다.

    SELECT fd1, fd2 FROM tab GROUP BY fd1;

    2011년 1월 2일 일요일

    PASSWORD()와 OLD_PASSWORD() 함수 그리고 old_passwords 설정


    MySQL에서 사용자의 로그인 보안 수준을 높이기 위해서,
    MySQL 4.0.x 이하 버전과 MySQL 4.1.x 이상 버전의 PASSWORD() 함수의 구현 알고리즘이 달라졌다.
    사실 아주 오래전 이야기이지만, 아직도 MySQL 4.0.x를 사용하는 사이트가 많고,
    최근 들어서 MySQL 5.1이나 5.5 버전으로 업그레이드를 준비하면서 이런 내용이
    업그레이드에 걸림돌이 되는 경우가 많은 것으로 보인다.



    MySQL 4.0.x 이하 버전
      - PASSWORD()
      - OLD_PASSWORD() 함수는 없음


      mysql> SELECT PASSWORD('mypass');
      +--------------------+
      | PASSWORD('mypass') |
      +--------------------+
      | 6f8c114b58f2ce9e   |
      +--------------------+


    MySQL 4.1.x 이상 버전
      - PASSWORD()         ==> 버전업된 암호화 함수
      - OLD_PASSWORD()     ==> 기존 암호화 (MySQL 4.0.x의 PASSWORD()와 동일)


      mysql>select PASSWORD('mypass');
      +-------------------------------------------+
      | password('mypass')                        |
      +-------------------------------------------+
      | *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 |
      +-------------------------------------------+
      
      mysql>select OLD_PASSWORD('mypass');
      +------------------------+
      | old_password('mypass') |
      +------------------------+
      | 6f8c114b58f2ce9e       |
      +------------------------+


    위 결과를 보면, 새로운 암호화 알고리즘에 의해서 암호화된 내용은 암호 제일 앞에 "*" 마크가 붙게 되므로, 새로운 암호인지 예전 암호인지 쉽게 구분할 수 있다.


    그런데, old_passwords=1로 설정하게 되면, MySQL 4.1.x 이상 버전에서도 아래와 같이 MySQL 4.0.x와 같은 결과를 보여주게 된다.
    MySQL 4.1.x 이상 버전에서도, 이전 버전과 동일하게 짧은 암호화 문장으로 변환됨
      root@localhost:(none)>select PASSWORD('mypass');
      +--------------------+
      | PASSWORD('mypass') |
      +--------------------+
      | 6f8c114b58f2ce9e   |
      +--------------------+
      
      root@localhost:(none)>select OLD_PASSWORD('mypass');
      +------------------------+
      | OLD_PASSWORD('mypass') |
      +------------------------+
      | 6f8c114b58f2ce9e       |
      +------------------------+


    MySQL 4.1.x 이상 버전에서 old_passwords=0 (기본 설정값)인 경우에는, mysql.user 테이블의 비밀번호가 
    예전 버전에서 생성된 짧은 비밀번호라면 로그인할 수 없게 된다.


    하지만, MySQL 4.1.x 이상 버전에서도 mysql.user 테이블에 사용자의 비밀번호가 예전 버전인 경우 로그인할 수 있도록 
    해주기 위해서 old_passwords 라는 옵션을 1로 설정할 수 있도록 해둔 것이다.


    근본적인 원인은 MySQL이 업그레이드되면서 기존 비밀번호의 암호화 수준을 보완하여 더 보안 수준을 높이면서 
    이런 문제가 야기되었다는 것이며, 가능하다면 업그레이드된 긴 암호를 사용하는 것이 좋아 보인다.


    더 중요한 것은
    MySQL의 PASSWORD() 함수는 MySQL의 사용자 자체의 계정 및 비밀번호를 관리하기 위한 함수이지
    일반 서비스용 계정및 암호를 관리하는 용도로는 적합하지 않다는 것이다.


    예를 들어서 MySQL 4.0.x 버전의 회원정보가 예전 방식의 PASSWORD() 함수로 암호화되어서 저장되어져 있다면,
    MySQL 4.1.x 이상의 PASSWORD() 함수로는 로그인이 되지 않게 될 것이다. (이 경우에는 OLD_PASSWORD()를 사용해야만 로그인을 할수 있게 된다.)
    하지만, 이 경우 예전 버전의 PASSWORD()함수의 암호화 내용을 새로운 버전의 PASSWORD() 함수로 대체할 수 없다.
    (한번 암호화된 문장은 다시 풀어낼 수 없는 형태 - 비대칭형 암호화 - 이기 때문)
    서비스용 계정이 MySQL의 사용자 계정 암호화 방식에 의존하게 되면 이런 문제가 야기될 수 있으므로 
    필요시에는 MD5와 같은 알고리즘을 사용할 것을 추천한다.


    또한, 이렇게 두개 버전의 암호화 방식이 존재하는 상태에서
    서비스용 MySQL의 버전 업그레이드와 연관되어서 명확히 해결하지 않고 업그레이드를 진행하게 되면,
    일부 사용자의 암호는 예전 버전이고, 또 일부 사용자는 새로운 암호화 버전을 사용하도록 되어버리면
    걷잡을 수 없는 혼란에 빠져들고, 이를 처리하기 위해서 또 한번 쿼리와 IF ~ ELSE가 필요해질 수도 있다는 것이다.

    MySQL에서 MINUS와 INTERSECT 집합 연산

    INTERSECT 집합 연산 사용
    INTERSECT 는 두개 집합에서 SELECT되는 튜플들을 모두 INNER JOIN의 조인 조건으로 포함시켜서 실행하면 쉽게 동일한 결과를 얻을 수 있다.


    예제 쿼리)
    SELECT member_id as uid, member_name as uname FROM member
    INTERSECT
    SELECT emp_id as uid, emp_name as uname FROM emp;
    (이 형태의 쿼리는 MySQL에서는 지원되지 않음)


    위의 쿼리에서 SELECT되는 튜플들이 uid와 uname이므로 
    이 두개의 컬럼을 INNER JOIN의 조건으로 포함시켜서 아래와 같이 작성해주면 된다.


    SELECT member_id as uid, member_name as uname
    FROM member m 
      INNER JOIN emp e ON e.emp_id=m.member_id 
        AND e.emp_name=m.member_name;








    MINUS 집합 연산 사용

    MINUS 연산은 첫번째 집합에는 있지만, 두번째 집합에는 없는 것들을 조회하는 것이므로,
    아래와 같이 3가지 방법으로 해결할 수 있다.



    예제 쿼리)
    SELECT member_id as uid, member_name as uname FROM member
    MINUS
    SELECT emp_id as uid, emp_name as uname FROM emp;
    (이 형태의 쿼리는 MySQL에서는 지원되지 않음)


    단, MINUS 집합 연산은 항상 DISTINCT하게 중복 레코드를 제거하고 리턴하기 때문에
    SELECT의 최종 결과에 DISTINCT를 붙혀 줘야 다른 DBMS의 MINUS와 동일한 결과를
    얻을 수 있다. (만약, 필요치 않거나 중복 가능성이 없는 결과인 경우 DISTINCT 없어도 됨)

    • NOT IN을 사용하는 방법
      SELECT DISTINCT m.member_id as uid, m.member_name as uname
      FROM member m
      WHERE (m.member_id, m.member_name) NOT IN
        (SELECT e.emp_id, e.emp_name FROM emp e);
    • NOT EXISTS를 사용하는 방법
      SELECT DISTINCT m.member_id as uid, m.member_name as uname
      FROM member m
      WHERE NOT EXISTS (
        SELECT 1
        FROM emp e
        WHERE e.emp_id=m.member_id
          AND e.emp_name=m.member_name
      );
    • LEFT OUTER JOIN을 이용하는 방법
      SELECT DISTINCT m.member_id as uid, m.member_name as uname
      FROM member m
        LEFT JOIN emp e ON emp e ON e.emp_id=m.member_id
          AND e.emp_name=m.member_name
      WHERE e.emp_id IS NULL;

    조회되는 데이터의 성격에 따라서 성능의 차이는 있겠지만,
    일반적인 데이터에서는 밑으로 내려갈수록 빠른 성능(세번째 > 두번째 > 첫번째)을 보인다.
    가능하면 두번째 아니면 세번째 방법을 사용할 것을 권장하며, 
    대상 레코드가 아주 많다면, 세번째 방법을 사용할 것을 권장한다.

    2011년 1월 1일 토요일

    UNION과 UNION ALL 의 차이 및 주의 사항

    ANSI SQL에서 제안하는 집합 연산 "UNION", "INTERSECT", "MINUS" 중에서
    MySQL에서는 UNION 집합 연산만 제공하고 있다.
    (하지만 MySQL에서 INTERSECT나 MINUS를 다른 형태의 쿼리로 풀어서 사용할 수 있다.)


    이 글에서는 UNION 에 대해서 좀 더 자세히 알아 보고자 한다.
    UNION 집합 연산은 다시 아래와 같이 두가지 종류로 나누어진다.
      - UNION ALL
      - UNION DISTINCT


    우리가 일반적으로 사용하는 방식인 아무런 추가 키워드 없이 UNION 만 사용하는 것은
    UNION DISTINCT 를 줄여서 사용하고 있는 것이다
    .

    UNION ALL과 UNION DISTINCT를 레코드가 많은 결과에 대해서 적용해본 사람은
    아마도 둘의 처리 방식에 대해서 의구심을 가져본 적이 있을 것이다.

    레코드 건수가 많아지면 많아질수록 그 성능 차이는 엄청난 차이를 보여줄 것이다.

    우선, 아래와 같이 2개씩 동일한 레코드 데이터를 가지고 있는 tab1과 tab2라는 테이블이 있다.

    mysql>SELECT fdpk, fddata FROM tab1;
    +------+--------+
    | fdpk | fddata |
    +------+--------+
    |    1 | data1  |
    |    2 | data2  |
    +------+--------+
    2 rows in set (0.00 sec)


    mysql>SELECT fdpk, fddata FROM tab2;
    +------+--------+
    | fdpk | fddata |
    +------+--------+
    |    1 | data1  |
    |    2 | data2  |
    +------+--------+
    2 rows in set (0.01 sec)


    그러면, 이 두개 테이블에 대해서 각각 UNION과 UNION ALL을 사용하는 쿼리를 실행해보자.

    mysql>SELECT fdpk, fddata
        -> FROM (
        ->   SELECT fdpk, fddata FROM tab1
        ->   UNION ALL
        ->   SELECT fdpk, fddata FROM tab2
        -> ) x;
    +------+--------+
    | fdpk | fddata |
    +------+--------+
    |    1 | data1  |
    |    2 | data2  |
    |    1 | data1  |
    |    2 | data2  |
    +------+--------+
    4 rows in set (0.00 sec)


    mysql>SELECT fdpk, fddata
        -> FROM (
        ->   SELECT fdpk, fddata FROM tab1
        ->   UNION
        ->   SELECT fdpk, fddata FROM tab2
        -> ) x;
    +------+--------+
    | fdpk | fddata |
    +------+--------+
    |    1 | data1  |
    |    2 | data2  |
    +------+--------+
    2 rows in set (0.00 sec)


    두개의 퀴리 실행 결과 UNION은 레코드가 반으로 줄었다.
    이미 다들 알고 있다시피 UNION은 UNION DISTINCT와 동일한 작업을 하기 때문에 중복되는 레코드를 제거했음을 알 수 있다.
    하지만, UNION ALL의 경우에는 별도의 중복 제거 과정을 거치지 않고 그냥 결과를 내려준다.
    아주 중요한 내용이지만, 사실 이 내용을 다들 별로 신경쓰지 않고 모두들 UNION을 즐겨 사용한다.


    안타깝게도, MySQL의 실행계획에서는 둘의 차이를 전혀 느낄 수 없다.
    +----+--------------+------------+------+..+------+..+------+------+-------+
    | id | select_type  | table      | type |..| key  |..| ref  | rows | Extra |
    +----+--------------+------------+------+..+------+..+------+------+-------+
    |  1 | PRIMARY      | <derived2> | ALL  |..| NULL |..| NULL |    4 |       |
    |  2 | DERIVED      | tab1       | ALL  |..| NULL |..| NULL |    2 |       |
    |  3 | UNION        | tab2       | ALL  |..| NULL |..| NULL |    2 |       |
    |NULL| UNION RESULT | <union2,3> | ALL  |..| NULL |..| NULL | NULL |       |
    +----+--------------+------------+------+..+------+..+------+------+-------+


    하지만 중복 제거는 그냥 얻을 수 있는 결과가 아니다.그러면, MySQL이 내부적으로 어떻게 중복을 제거하는 것일까 ?

    내부적인 처리를 알아보기 전에, 레코드의 중복이라는 표현을 했는데 이 중복의 기준이 무었일까 ?
        1. 각 테이블의 Primary key ?
        2. 전체 테이블의 모든 필드 ?
        3. 각 서브 쿼리에서 SELECT된 튜플(레코드)의 모든 필드 ?


    그렇다. 이미 SELECT된 결과를 가지고 UNION하기 때문에 SELECT되기 전의 테이블이나 레코드에 대한 정보는 알 수 없다.
    그래서, 중복 여부의 판단은 SELECT된 튜플들에 속해있는 모든 컬럼의 값들 자체가 중복 체크의 기준이 되는 것이다.


    자~, 그러면 이제 MySQL이 내부적으로 UNION ALL과 UNION을 처리하는 과정을 알아보자.
    1. 최종 UNION [ALL | DISTINCT] 결과에 적합한 임시 테이블(Temporary table)을 메모리 테이블로 생성
    2. UNION 또는 UNION DISTINCT 의 경우, Temporary 테이블의 모든 컬럼으로 Unique Hash 인덱스 생성3. 서브쿼리1 실행 후 결과를 Temporary 테이블에 복사
    4. 서브쿼리2 실행 후 결과를 Temporary 테이블에 복사
    5. 만약 3,4번 과정에서 Temporary 테이블이 특정 사이즈 이상으로 커지면

        Temporary 테이블을 Disk Temporary 테이블로 변경
        (이때 Unique Hash 인덱스는 Unique B-Tree 인덱스로 변경됨)
    6. Temporary 테이블을 읽어서 Client에 결과 전송
    7. Temporary 테이블 삭제


    UNION 두 가지의 차이는 2번 과정 딱 하나이다. 중복 제거를 위해서 Temporary 테이블에 인덱스를 생성하느냐 ?. 그렇지 않느냐 ?.별로 중요하지 않은 것 같지만, 이 인덱스로 인해서 3,4번 과정의 작업이 작지 않은 성능 차이가 만들어 내게 된다.
    실제 UNION을 실행하는 데이터의 건수에 따라서 다르겠지만, 1.5 ~ 4배 가량의 성능 차이로 UNION ALL이 빠르게 처리된다.
    만약 처리중 데이터의 량이 작아서 5번 과정을 거치지 않는다면 메모리 Temporary 테이블에 Hash 인덱스를 사용하기 때문에

    속도 차이가 아주 미세할 것이다.
    하지만 데이터량이 커져서 5번 과정을 거치게 되면 Disk Temporary 테이블에 B-Tree 인덱스를 사용하기 때문에 큰 성능 차이를 보이게 될 것이다.
    이 성능 차이는 UNION 하는 두 집합에 중복되는 레코드가 있든 없든 관계 없이 발생할 것이다.


    위에서 잠깐 알아보았던, "중복의 기준"을 생각하면, UNION 하는 컬럼들의 수가 많아지고 레코드의 사이즈가 커질수록 두 작업 모두에게 불리하겠지만, UNION ALL보다는 UNION에 더 악영향이 클 것이다.

    결론은,
    0. UNION 이든지 UNION ALL이든지 사실 그리 좋은 SQL 작성은 아니다.

        UNION이 필요하다는 것은 사실 두 엔터티(테이블)가 하나의 엔터티(테이블)로 통합이 되었어야
        할 엔터티들이었는데, 알 수 없는 이유로 분리 운영되는 경우가 상당히 많다.
        즉 모델링 차원에서 엔터티를 적절히 통합하여 UNION의 요건을 모두 제거하자.
    1. 두 집합에 절대 중복된 튜플(레코드)가 발생할 수 없다는 보장이 있다면 UNION ALL을 꼭 사용하자.

        두 집합에서 모두 각각의 PK를 조회하는데, 그 두 집합의 PK가 절대 중복되지 않는 형태
    2. 중복이 있다 하더라도 그리 문제되지 않는다면 UNION 보다는 UNION ALL을 사용하자.
    3. 만약 UNION이나 UNION ALL을 사용해야 한다면, 최소 필요 컬럼만 SELECT 하자.