MySQL 서버의 주요 버그

MySQL 서버에서 발생되는 버그는 모두 http://bugs.mysql.com 을 통해서 보고되며,
버그인지 아니면 사용자 실수인지 그리고 버그인 경우, 패치 진행 현황까지 모두 관리되고 있다.
하지만, 한번이라도 그 내용을 찾아본 사용자는 그 어려움을 절감 하고 있을 것이다.  
MySQL 의 보고되었고, 현재 해결되지 않은 버그가 300개 가량이다. 어떤 버그가 있는지 어느 버전에 해결되었는지 알아낸다는 것은 적지 않은 시간을 요하는 작업임에는 틀림이 없다.


하지만, 주요 버그들에 대해서는 알아 두는 것이 MySQL의 버전을 선택할 때 또는 개발을 할 때 많은 도움이 될 것이라 생각한다.


l JOIN UPDATE와 ALTER TABLE 의 충돌로 인한 MySQL server crash (http://bugs.mysql.com/bug.php?id=38691)
   - 버그 내용
      JOIN UPDATE 쿼리 문과 ALTER TABLE 문이 동일 InnoDB 테이블에 대해서 동시에 실행될 경우, 
      JOIN UPDATE가 대기하게 되고 ALTER TABLE이 종료되면서 JOIN UPDATE 쿼리 문이 Lock을 
      점유하려는 순간 Server crash (Segmentation fault)가 발생됨
   - 대상 버전
      MySQL 5.0.72 버전, MySQL 5.1.30, MySQL 6.0.8-alpha 버전부터 패치 되었으며, 
      각 Major 버전 별로 그 이전의 버전 대에서는 발생 가능함
   - 회피 방법
      JOIN UPDATE 쿼리의 사용의 자제 (포기하기에는 너무 절실한 기능임 ㅠㅠ) 또는 
      ALTER TABLE 작업을 사용자의 요청이 없는 특정 점검 시간에 진행


l Query cache purge 오류로 인한 MySQL server crash (http://bugs.mysql.com/saved/all_query_cache_bugs)
   - 버그 내용
      Slave 장비에서 Relay log를 실행하는 Slave SQL thread가 INSERT, UPDATE, DELETE 문장을 실행하면서 
      QueryCache의 내용을 제거하는 도중 MySQL server crash 발생. View 에 INSERT, UPDATE, DELETE 등이
      실행되면서, QueryCache에서 해당 View의 내용을 제거하는 도중에서 MySQL server가 Crash되는 버그도
      있음 
   - 대상 버전
      MySQL 5.1.0 ~ MySQL 5.1.54 버전, 
      그리고 이 버전들에서는 이 이외에도 수많은 형태의 Query cache 관련된 버그가 있는 것으로 보고됨)
   - 회피 방법
      MySQL 5.1.54 이후 버전으로의 업그레이드가 당장 어려운 경우, 
      MySQL server의 Query cache를 비활성화(Disable) 후 서비스 투입
      query_cache_size = 0
      query_cache_type = 0


l SELECT ... FROM tab WHERE idx_fd IN (SELECT ... FROM tab1 WHERE ...) 쿼리의 인덱스 사용 불가
   - 버그 내용
      MySQL 버그 시스템에 등록되어 있는지 모르겠지만, 현재 대 부분의 MySQL 버전 대에서 위의 쿼리에서 
      tab 테이블의 idx_fd 컬럼에 인덱스가 준비되어 있어도 그 인덱스를 이용하지 못하고 Full Scan을 
      하게 되는 버그가 있음. (왜 아직 고쳐지지 않는지 잘 모르겠음) MySQL 서버의 구조상의 문제라고 
      이야기 되기도 하는데...
   - 대상 버전
      현재 Release된 거의 모든 버전
   - 회피 방법
      현재로써는 MySQL의 버전 업그레이드로 해결될 수 없기 때문에 
      만약, tab 테이블의 레코드 건수가 적다면 그대로 유지하고, 
      그렇지 않다면, 서브 쿼리 부분을 별도의 쿼리로 실행해서 그 결과를 IN (...) 조건에 직접 추가
      (Application을 통해서 처리)






앞으로도 일반적으로 겪을 가능성이 있는 주요 버그들에 대해서 보완하도록 하겠다.

CHAR vs VARCHAR ?

CHAR를 사용할지 VARCHAR를 사용할 지의 문제는

문자열 데이터의 타입을 선정할 때, 항상 고민하는 부분이 아닐까 생각된다.

CHAR와 VARCHAR의 장단점을 확인해보고, 그 장단점에 맞게 선택하는 것이 최적일 것이라 생각한다.

1. CHAR

  1.1 특징

    1.1.1 Disk에 저장 시, 고정 길이로 저장된다.

    1.1.2 고정 길이이기 때문에, 별도의 유효(실질) 데이터 길이를 관리하지 않는다.

  1.2 장점

    1.2.1 고정 길이이기 때문에, 이 컬럼의 변경으로 인해서 Record의 위치 이동(Row migration)이 필요한 경우는 없다.

    1.2.2 별도의 유효(실질) 데이터 길이를 관리하지 않기 때문에 VARCHAR에 비해서 최소 1~2 바이트는 절약된다.

  1.3 단점

    1.3.1 실제 데이터의 길이와 관계 없이 테이블 생성 시(CREATE TABLE...)에 정의된 사이즈만큼 Disk를 사용한다.

2. VARCHAR

  2.1 특징

    2.1.1 Disk에 저장 시, 가변 길이로 저장된다.

    2.1.2 가변 길이이기 때문에, 별도로 실제 데이터의 길이를 관리해야 한다. (데이터의 길이에 따라서 1~2바이트가 사용됨)

  2.2 장점

    2.2.1 실제 데이터의 길이 변화가 심해도 Disk 공간을 절약할 수 있다.

  2.3 단점

    2.3.1 이 컬럼의 값이 예전보다 긴 값으로 변경될 경우, Record의 위치 이동(Row migration)이 발생할 수 있다.

    2.3.2 실제 데이터의 길이 값을 관리해야 하기 때문에 1~2바이트가 더 필요하다.

여기서 참고로

Disk의 공간을 더 차지한다는 것은, 데이터 파일의 각 페이지 낭비가 커서 데이터 파일의 사이즈가 커지는 결과를 가져오고, 이는 큰 Disk가 필요하다는 것보다는 MySQL이 쿼리를 처리하기 위해서 읽어야 하는 Disk의 페이지 수가 많아진다는 것이 더 큰 영향이라고 볼 수 있다. (즉, 레코드 100건을 읽기 위해서 데이터 파일 사이즈가 작을 때에는 1번 Disk 읽기로 완료되었던 작업이 파일 사이즈가 커져서 Disk 읽기 2번이 필요 해질 수 있다는 것을 의미한다.)

그럼, 이제 위의 내용을 기초로 어떤 데이터를 CHAR로 또는 VARCHAR 선택해야 할지 생각 해보자.

A. 컬럼의 모든 값의 길이가 동일한 경우

B. 1~2 글자의 Alpha-numeric 값

C. 거의 대부분이 15자리 Alpha-numeric 값인데 가끔 10~14 자리 값이 들어올 수 있는 경우

조금은 억지가 있는 질문들이지만, 

A와 B의 케이스 모두 일반적으로 CHAR로 설정하는 것이 좋지만,

B의 케이스는 UTF8의 CHAR(1) 은 3바이트, EUCKR의 CHAR(1)은 2바이트를 차지하게 되는데, 

저장되는 데이터가 Alpha-numeric이라면 조금씩의 낭비는 발생하게 되며, 

이를 피하기 위해서는 해당 컬럼만 CharacterSet을 별도로 지정 해주는 방법도 있지만 

상당한 혼란을 초래할 수도 있다. 일반적으로 코드 형태의 컬럼이 B케이스가 아닐까 생각된다.

C 케이스의 경우에도, 개인적으로는 CHAR 타입이 좋지 않을까 생각한다. 만약 이 컬럼의 값의 길이가 

자주 변경(10~15자리 사이에서)이 된다면 더더욱 CHAR로 선정함이 더 좋다. 

하지만 값의 길이가 가변 폭이 크다면 VARCHAR로 선택하는 것이 좋다.

조인시 서로 비교되는 컬럼을 동일 타입으로 유지하는 것 이외에 특별히 "이건 꼭 CHAR로 해야돼" 라고 

생각할 필요는 별로 없어 보인다.

하지만, VARCHAR로 해야 될 것을 CHAR로 적용해서는 무리가 있다.

개인적으로 CHAR와 VARCHAR를 선정하는 기본적인 절차는 (상당히 주관적이고 개인적인 판정 절차임)

1. 모든 값이 고정 길이 값인가 ?    -> CHAR

2. 모든 값은 아니어도 대 부분(대략 90% 이상)의 값이 고정 길이이고 자주 변경이 발생하는가 ? -> CHAR

3. 5 바이트 미만의 코드 값인가 ?   -> CHAR

4. 그 이외에는 모두 VARCHAR 로 선정

Tritonn을 이용한 MySQL 서버의 전문 검색 엔진 구현

MySQL의 전문 검색 엔진 (Fulltext search engine)은 테이블의 레코드 건수나 Fulltext 인덱스 건수에 따라서 성능 저하 현상이 심한 편이다. 

또한, 전문 인덱스의 인덱싱 방식이 Stopword 기반이기 때문에 LIKE와 비슷한 패턴의 검색(Partial search)이 불가능하며, 검색의 목적에 맞게 전문 인덱스를 여러 개 만들어야 할 경우도 있다.

MySQL 5.1에서 Fulltext engine Parser를 플러그 인으로 만들어서 끼워 넣을 수 있지만, 현재로써는 적당한 오픈 소스 Parser가 없으며, 직접 만든다는 것은 더더욱 어려운 것이 사실이다.

이러한 이유들로 인해서, 일본에서 MySQL 전문 검색의 단점을 보완하고, 아시아권 언어를 위한 Tritonn이라는 

전문 검색 엔진을 오픈 소스로 개발했으며, MySQL builtin 전문 검색 엔진에서는 제공되지 않는 여러 가지 

기능들을 더 제공하고 있다.

가장 큰 장점은 Stopword 뿐만 아니라 구분자와 n-Gram 방식의 전문 분석 및 인덱싱이 가능하다는 것이다.

MySQL Builtin 전문 검색 엔진과 Tritonn 전문 검색 엔진의 기능 비교는 아래 URL을 참조하기 바란다.

http://intomysql.blogspot.com/2010/12/mysql-builtin-full-text-search-engine.html

n-Gram 방식의 인덱싱이란 Document의 전체 내용을 구분자나 Stopword에 관계 없이 n-문자씩 잘라서 

전문 인덱싱을 구성하는 방식을 이야기하며, 자르는 문자 수에 따라서 2-Gram 또는 4-Gram 등등으로 

구분할 수 있지만, 일반적으로 2-Gram이 일반적이다. 

이러한 n-Gram 방식의 인덱싱은 구분자나 Stopword 기준으로 검색하지 않아도 검색이 가능하다는 것이 장점이다.

예를 들어서 "Diablo Entertainment"라는 데이터와 "BlizzardDiabloEntertainment"라는 두 개의 데이터가 있다고 

가정할 때, "Diablo"로 검색할 경우, 구분자나 Stopword 방식으로는 첫 번째 데이터만 검색할 수 있다. 

하지만, n-Gram방식의 인덱싱에서는 둘 다 검색을 할 수 있게 되는 것이다.

현재 Tritonn은 MySQL 5.0 버전 대에서는 Source-patch 방식으로 적용할 수 있으며,

MySQL 5.1 버전 부터는 Plugin 형태로 적용할 수 있게 되었다. (즉, MySQL 소스 코드를 다시 빌드하지 않고 

적용할 수 있다.)

Tritonn (MySQL 5.0)의 URL은 http://qwik.jp/tritonn/ 이며,

Groonga (Tritonn의 MySQL 5.1 용 플러그인 버전의 이름)의 정보는 http://mroonga.github.com/ 를 참조하면 된다.

Groonga는 현재 개발 버전이며 Release 버전은 아니므로, 오늘 글에서는 MySQL 5.0.87 버전의 Tritonn 패치를 가지고 설치 및 인덱스 생성 사용하는 방법을 살펴보도록 하겠다.

우선 Tritonn이 전문 인덱싱을 위해서 사용하는 Senna라는 라이브러리를 받아서 설치해야 한다.

http://sourceforge.jp/projects/senna/releases/ 사이트를 방문하여 senna-1.1.4.tar.gz(더 최신 버전이 있지만, Tritonn이 이 버전을 타겟으로 개발했기 때문에) 파일을 다운로드 한다.

그리고, http://sourceforge.jp/projects/tritonn/releases/ 사이트에서 Tritonn의 소스 코드가 패치된 MySQL 5.0.87 

소스 파일을(tritonn-1.0.12-mysql-5.0.87.tar.gz)을 다운로드한다.

Senna를 Build하기 위해서는 최소 Redhat Enterprise linux 5.x(CentOS 5.x) 이상이 필요함

Senna 설치

tar zxvf senna-1.1.4.tar.gz

cd senna-1.1.4

./configure --prefix=/usr --without-mecab

make

make install

"ls -al /usr/bin/senna" 명령으로 senna library가 설치되었는지 확인한다.

그리고, MeCab은 일본어를 위한 언어 분석기이기 때문에 오늘 설치 절차에서는 제외하기로 한다.

그 다음으로 Tritonn 코드가 패치된 MySQL 5.0.87을 압축 해제하여 빌드하고 설치한다.

tar zxvf tritonn-1.0.12-mysql-5.0.87.tar.gz

cd tritonn-1.0.12-mysql-5.0.87

./configure \

    '--prefix=/mysql/MySQL-5.0.87'\

    '--localstatedir=/mysql/MySQL-5.0.87/data'\

    '--libexecdir=/mysql/MySQL-5.0.87/bin'\

    '--with-comment=MySQL 5.0.87 tritonn 1.0.12 64bit'\

    '--with-server-suffix=-tritonn_64'\

    '--enable-thread-safe-client'\

    '--enable-local-infile'\

    '--enable-assembler'\

    '--with-pic'\

    '--with-fast-mutexes'\

    '--with-client-ldflags=-static'\

    '--with-mysqld-ldflags=-static'\

    '--with-zlib-dir=bundled'\

    '--with-big-tables'\

    '--with-readline'\

    '--with-innodb'\

    '--with-federated-storage-engine'\

    '--with-extra-charsets=complex'\

    '--enable-shared'\

    '--with-senna'\

    '--without-mecab'\

    'CC=gcc'\

    'CXX=gcc'

make

make install

cd /mysql/MySQL-5.0.87

MySQL 의 Configure 과정은 필요한 다른 것들을 추가하거나 변경해도 무방하지만, '--with-senna' 과 '--without-mecab' 옵션은 반드시 추가해주어야 한다.

이렇게 해서 빌드된 MySQL 프로그램을 /mysql/MySQL-5.0.87 디렉토리에 복사하고 나면, 

일반적인 MySQL의 설치 과정과 같이 기본 데이터베이스를 생성해주고 MySQL을 기동하면 된다.

cp /mysql/MySQL-5.0.87/share/mysql/mysql.server /etc/init.d/

cd /mysql/MySQL-5.0.87

.bin/mysql_install_db  --defaults-file=./etc/my.cnf

/etc/init.d/mysql.server start

MySQL 이 정상적으로 기동되었다면, MySQL에 로그인해보자.

[root@toto:/mysql/MySQL-5.0.87]$ ./bin/mysql -uroot -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 1

Server version: 5.0.87-tritonn_64 MySQL 5.0.87 tritonn 1.0.12 64bit

정상적으로 버전 레이블이 출력되었다면, 이제 Tritonn을 이용해서 n-Gram 전문 인덱스를 생성 해보자.

CREATE TABLE tritonn_test (

  doc_id INT NOT NULL AUTO_INCREMENT,

  title VARCHAR(1000) NOT NULL,

  document TEXT,

  PRIMARY KEY (doc_id),

  FULLTEXT INDEX fx_document USING NGRAM, NO NORMALIZE (document)

) ENGINE=MyISAM DEFAULT CHARSET utf8;

insert into tritonn_test values (NULL, 'Tritonn full text search engine', '트리튼 전문 검색 엔진');

root@localhost:test>select * from tritonn_test;

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

| doc_id | title                           | document                       |

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

|      1 | Tritonn full text search engine | 트리튼 전문 검색 엔진          |

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

Tritonn 인덱스를 이용하는 테이블이 만들어지면, "SHOW SENNA STATUS;" 라는 명령을 이용하여 각 전문 인덱스들의 특성들을 확인할 수 있다.

(여기서는 크게 중요하지 않으므로, 그냥 무시)

SENNA를 이용한 인덱스가 만들어지면, MySQL의 데이터 디렉토리에는 아래와 같은 파일들이 생성된다.

-rw-rw---- 1 mysql mysql    8612 Dec 30 17:20 category.frm

-rw-rw---- 1 mysql mysql   98304 Dec 30 17:20 category.ibd

-rw-rw---- 1 mysql mysql 8462336 Dec 30 17:23 tritonn_test.001.SEN

-rw-rw---- 1 mysql mysql  430080 Dec 30 17:23 tritonn_test.001.SEN.i

-rw-rw---- 1 mysql mysql  135168 Dec 30 17:23 tritonn_test.001.SEN.i.c

-rw-rw---- 1 mysql mysql 8462336 Dec 30 17:23 tritonn_test.001.SEN.l

-rw-rw---- 1 mysql mysql    8634 Dec 30 17:23 tritonn_test.frm

-rw-rw---- 1 mysql mysql      76 Dec 30 17:23 tritonn_test.MYD

-rw-rw---- 1 mysql mysql    2048 Dec 30 17:23 tritonn_test.MYI

.SEN 파일은 MyISAM 테이블과 SENNA의 내부 문서 ID의 매핑 파일로써, 레코드가 증가할수록 파일 크기가 증가함

.SEN.i 파일은 인덱스 버퍼 정도로 생각하면 되고, INDEX 생성시에 사이즈를 지정할 수 있음

.SEN.l 파일은 어휘와 어휘 ID의 매핑을 관리하는 파일로써, 등록된 어휘의 수가 늘어날수록 파일 크기가 증가함

.SEN.i.c 파일은 인덱스를 저장하는 파일

이제 다시 MySQL로 돌아와서

이제 Tritonn을 이용한 전문 검색을 실행 해보자. 

Tritonn의 전문 검색 사용 방법은 MySQL의 Fulltext와 기본적으로 동일하다.

-- // 공백이나 구분자 또는 Stopword로 구분된 단위가 아니어도 검색이 가능하다.

root@localhost:test>SELECT * FROM tritonn_test WHERE MATCH (document) AGAINST ('리튼' IN BOOLEAN MODE);

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

| doc_id | title                           | document                       |

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

|      1 | Tritonn full text search engine | 트리튼 전문 검색 엔진          |

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

-- // 중간에 공백이 있는 단어라 하더라도 검색이 가능하다.

root@localhost:test>SELECT * FROM tritonn_test WHERE MATCH (document) AGAINST ('색 엔' IN BOOLEAN MODE);

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

| doc_id | title                           | document                       |

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

|      1 | Tritonn full text search engine | 트리튼 전문 검색 엔진          |

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

-- // Tritonn의 전문 검색 엔진 결과와 다른 MyISAM 또는 InnoDB 테이블과 조인이 가능하다.

root@localhost:test>SELECT c.category_name, d.doc_id, d.title, d.document

    -> FROM tritonn_test d, category c

    -> WHERE MATCH (document) AGAINST ('리튼' IN BOOLEAN MODE)

    ->   AND c.doc_id=d.doc_id;

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

| category_name | doc_id | title                           | document                       |

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

| MySQL         |      1 | Tritonn full text search engine | 트리튼 전문 검색 엔진          |

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

이제, 

두 개의 컬럼을 이용한 전문 검색 인덱스를 생성해서, 인덱스의 전체 컬럼 또는 일부 컬럼만 검색하는 예제를 살펴보자.

CREATE TABLE tritonn_test (

  doc_id INT NOT NULL AUTO_INCREMENT,

  title VARCHAR(1000) NOT NULL,

  document TEXT,

  PRIMARY KEY (doc_id),

  FULLTEXT INDEX fx_title_document USING NGRAM, NO NORMALIZE, SECTIONALIZE (title, document)

) ENGINE=MyISAM DEFAULT CHARSET utf8;

insert into tritonn_test values (NULL, 'Tritonn full text search engine', '트리튼 전문 검색 엔진');

-- // 전문 인덱스가 두개의 컬럼으로 만들어졌기 때문에 MATCH 절에는 항상 2개의 컬럼 모두 명시되어야 한다.

-- // AGAINST 절에 따른 패턴이 적용되지 않으면, 기본적으로 모든 컬럼에 대해서 검색을 실행한다.

root@localhost:test>SELECT * FROM tritonn_test WHERE MATCH (title, document) AGAINST ('리튼' IN BOOLEAN MODE);

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

| doc_id | title                           | document                       |

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

|      1 | Tritonn full text search engine | 트리튼 전문 검색 엔진          |

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

-- // AGAINST 절에 검색어 앞에 "*Wn" 가 명시되면, 인덱스의 n 번째 컬럼에 대해서만 전문 검색을 실행하게 된다.

-- // 아래 예제는 "*W1" 이기 때문에 첫번째 컬럼인 title 컬럼에 대한 검색만 실행한다. 그래서, 결과가 없음.

root@localhost:test>SELECT * FROM tritonn_test WHERE MATCH (title, document) AGAINST ('*W1 리튼' IN BOOLEAN MODE);

Empty set (0.00 sec)

-- // 이번에는 "*W2" 이기 때문에 두 번째 컬럼인 document 컬럼에 대한 검색만 실행한다.

root@localhost:test>SELECT * FROM tritonn_test WHERE MATCH (title, document) AGAINST ('*W2 리튼' IN BOOLEAN MODE);

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

| doc_id | title                           | document                       |

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

|      1 | Tritonn full text search engine | 트리튼 전문 검색 엔진          |

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

Tritonn 전문 검색 엔진에 대해서는 여기까지 간단히 설명을 끝내고,

Tritonn과 비슷한 Sphinx라는 전문 검색 엔진도 있다. 이 또한 n-Gram 방식의 검색을 지원하고 있다.

Sphinx에 대해서는 다시 한번 더 알아볼 수 있는 기회를 갖도록 하겠다.

참고로, "SHOW CREATE TABLE tritonn_test;"의 결과에서 보이는

512라는 숫자는 INITIAL_N_SEGMENTS (인덱스 버퍼 공간의 기본값) 값이며 512가 Default임.

문자열 (CHAR, VARCHAR) 타입의 비교 및 정렬 방식

DBMS 종류별로 CHAR 타입의 데이터를 읽어 오고 비교하는 방식에서 조금씩의 차이가 있다.

가끔 오라클과 같은 타 DBMS에 익숙한 사용자는 자주 이런 부분을 혼동하는 경우가 많다.

우선 CHAR 타입이 고정 길이로 관리된다는 것은 타 DBMS와 동일하다.

하지만, CHAR 타입의 필드를JDBC나 PHP 또는 C api를 이용하는 Application에서 읽어 왔을 때, 오라클과는 달리 지정된 길이만큼 공백으로 채워져 있지 않다는 것이다.

즉, CHAR(10)이라는 컬럼에 "ABC"라는 값을 저장해 두었다고 가정했을 때, Application에서 그 필드를 Fetch해 보면, 오라클은 "ABC       "와 같이 필드 값을 리턴하지만, MySQL 은 "ABC" 만 리턴하게 된다. 또한 MySQL은 사용자가 입력한 내용의 마지막에 공백이 있는 경우, 그 공백마저도 제거해 버리게 된다. 공백이 아닌 문자 뒤에 붙어 있는 공백만 그렇게 처리된다.

아래와 같이 저장된 값들은 Application에서 읽어 보면 실제 우측에 표기된 것과 같은 값을 얻게 된다.

l  " ABC"  -> " ABC"

l  "AB C"  -> "AB C"

l  "A BC " -> "A BC"

CHAR 타입의 경우, MySQL은 마지막에 붙어 있는 공백 문자가 사용자의 입력인지 MySQL이 내부적으로 자리 수를 채우기 위해서 붙여 넣은 공백인지 구분하지 않기 때문이다.

그리고, 당연히 "ABC"와 "ABC  "는 동일한 값으로 간주되기 때문에 UNIQUE 인덱스가 있는 경우에는 DUPLICATE-KEY 오류를 발생시키게 된다.

만약, 오라클과 같은 형태로 CHAR 타입의 자리 수만큼 공백이 추가된 상태로 값을 리턴 받기를 원한다면 my.cnf (또는 my.ini) 설정 파일에 sql_mode 라는 변수를 정의하고, 그 값에 "PAD_CHAR_TO_FULL_LENGTH" 설정을 추가 해주면 오라클과 동일한 형태의 결과를 Application에서 전달 받을 수 있게 된다.

VARCHAR 타입의 경우, 내부적인 실제 데이터의 길이를 보관하기 때문에 마지막에 붙어 있는 공백 문자를 같이 데이터로 간주하고 저장 및 읽어 올 때 같이 리턴하게 된다.

그리고, 또 한가지 MySQL에서 주의해야 할 사항은, CHAR 타입과 VARCHAR타입의 비교에서는 뒤에 붙은 공백 문자를 모두 제거하고 비교를 하게 된다.

MySQL의 문자열 타입이 가지게 되는 Collation이라는 속성이 있는데, 이 Collation이라는 것은 비교 및 정렬의 기준을 제공하는 속성이다.

사실은 이러한 공백을 제거한 비교 작업은 이 Collation이라는 속성의 특징으로 발생하는 결과이며, 즉 MySQL의 문자열의 비교 및 정렬은 모두 마지막 공백을 제거(Right Space Trim)한 후, 비교 및 정렬 작업을 수행하게 된다.

그래서 최종적으로 아래와 같은 결과를 발생시키게 되는 것이다.

CREATE TABLE test (

  fd1 CHAR(10) DEFAULT NULL,

  fd2 VARCHAR(10) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

insert into test values ('ABC ','ABC  ');

insert into test values ('ABC ','ABC');

insert into test values ('ABC ','ABC      ');

insert into test values ('ABC ','ABC    ');

SELECT

  CONCAT('''',fd1,'''') as converted_fd1,

  CONCAT('''',fd2,'''') as converted_fd2,

  (fd1=fd2) as is_equal

FROM test

ORDER BY fd2 DESC;

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

| converted_fd1 | converted_fd2 | is_equal |

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

| 'ABC'         | 'ABC  '       |        1 |

| 'ABC'         | 'ABC'         |        1 |

| 'ABC'         | 'ABC      '   |        1 |

| 'ABC'         | 'ABC    '     |        1 |

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

이 결과로 test 테이블 모든 레코드의 fd1 값과 fd2값은 모두 동일한 값이라는 것을 확인할 수 있으며,

뒤에 붙은 공백 문자는 정렬에 아무런 영향을 미치지 않는다는 것도 확인할 수 있다.

Stored function의 NOT DETERMINISTIC 옵션은 무엇이고 쿼리에 어떤 영향을 미칠까?

Procedure나 Function의 생성시에 사용되는 키워드 중에서 DETERMINISTIC 또는 NOT DETERMINISTIC이라는 키워드를 본 적이 있을 것이다.
여기서 DETERMINISTIC이 의미하는 것이 무엇일까 ?. 그리고 이 옵션으로 인해서 어떤 차이가 생기는 것일까 ?.


이 글에서는 DETERMINITIC하고 그러지 않은 함수의 차이를 알아보고자 한다.
우선, 아래와 같은 예제 함수를 하나 만들었다고 가정해보자.


CREATE FUNCTION 
  getKeyValue() RETURNS BIGINT
  NOT DETERMINISTIC
BEGIN
  return 99999999;
END;;


이 함수는 NOT DETERMINISTIC 으로 정의가 되었다는 것을 기억하고, 아래 쿼리를 한번 보자.


SELECT COUNT(*) FROM tb_test WHERE fdpk > getKeyValue();


tb_test 테이블에는 대략 1억건 정도의 레코드가 저장되어 있고, fdpk는 tb_test 테이블의 Primary key 컬럼이며,
tb_test 의 fdpk 값은 1~1억까지 값을 가지고 있다고 가정해보자.


이 쿼리는 최종적으로 값 1을 리턴하는 쿼리인데, 이 쿼리가 실행되는데, 시간이 얼마나 걸릴까 ?
직접 한번 테스트해보길 바라며, 정답은 테스트를 해보진 않아서 모르겠지만, 아마 기대 했던 1초 미만은 아닐 것이다.


왜 이런 결과가 나온 것일까 ?
이 질문의 정답은 이 게시물의 제목에서 말하듯이 "NOT DETERMINISTIC" 옵션 때문이다.
MySQL의 Stored procedure나 Function이 NOT DETERMINISTIC으로 정의되면, 
MySQL은 이 Stored routine의 결과값이 시시각각 달라진다고 가정하고, 
비교가 실행되는 레코드마다 이 Stored routine을 매번 새로 호출해서 비교를 실행하게 된다.
즉, 함수 호출의 결과값이 Cache되지 않고, 비교되는 레코드 건수만큼 함수 호출이 발생하는 것이다.


그래서 위 예제 쿼리의 경우, 이 쿼리문이 완료되기 위해서는 getKeyValue() 함수가 1억번 호출이 되어야 되며,
그와 동시에 fdpk 컬럼에 생성되어 있는 인덱스까지도 무용지물로 만드는 것이다.


만약, getKeyValue() 함수가 DETERMINISTIC으로 정의되었다면 우리가 기대하는 시간안에 처리를 완료할 것이다.
이 때에는 MySQL이 이 함수가 DETERMINISTIC 옵션으로 입력값이 동일하면 출력값은 항상 동일하다는 것을 인지하고
단 1번만 이 함수를 호출해서 결과값으로 Primary key를 검색하게 될 것이기 때문이다.


별것 아닌것으로 보이는 이 옵션으로 엄청난 성능 차이를 낼 수 있는 것이므로,
함수를 이와 같은 용도로 사용할 경우에는 이 옵션에 주의하자.

MySQL에서 해시(Hash) 인덱스 구현

MySQL에는 기본적으로 해시 인덱스가 제공되지 않는다.
물론, InnoDB의 Adaptive Hash 인덱스라는 기능이 있지만, 이 기능은 사용자가 조작 가능한 형태가 아니라 InnoDB 엔진에서 자주 접근되는 인덱스 데이터들에 대해서 자동적으로 Hash 인덱스를 생성하는 기능이므로 사용자 테이블에 명시적으로 Hash 인덱스를 적용할 수 는 없다.


Hash 인덱스의 장점은 인덱싱할 필드 값들을 Hash 함수를 통해서 계산된 Hash 값으로 인덱스를 만들기 때문에 검색 대상이 되는 필드 값의 길이에 관계 없이 빠른 검색 기능을 제공할 수 있다.
물론, 단점도 있는데 Hash 인덱스는 이미 한번 가공된 값으로 인데싱을 구현하기 때문에 정확히 일치하는 값만 검색이 가능하다. (즉, 범위 검색이나 부분 일치 검색은 Hash 인덱스를 이용할 수 없다.)


일반적으로, 최근의 많은 웹페이지의 기능들 또는 분석 기능들 중에서는 URL을 가공해야 처리해야 하는 요건이 상당히 많이 발생하는데, URL의 길이는 제한이 없거나 상당히 길다는 것도 B-Tree 인덱스를 사용하기에는 부담스러운 부분이다.


오늘은 간단히 URL 필드 값에 대해서 Hash 인덱스와 비슷한 기능을 MySQL의 B-Tree 인덱스로 구현하는 예제를 살펴보고자 한다.


요건은 간단히 Apache 웹 서버의 Access log를 저장하고 가공하기 위한 테이블을 설계한다고 가정해보자.
우선 아래와 같은 테이블이 필요할 것이다.


CREATE TABLE access_log(
  log_id bigint unsigned not null auto_increment,
  access_dttm datetime not null,
  agent_name varchar(50) not null,
  access_url varchar(2000) not null,
  referrer_url varchar(2000),
  ...
  PRIMARY KEY (log_id),
  INDEX ix_accessdttm (access_dttm)
);


대략 요건을 충족할 수 있는 테이블이 위와 같이 준비하고,
만약, 주로 검색이 발생하는 URL 값이 referrer_url 이라면, referrer_url 컬럼값의 Hash 추출 값을 저장할 컬럼을 하나 더 추가한다.
이때, 추가할 컬럼의 타입은 사용할 해시 함수의 특성과 해시값의 데이터 타입에 따라서 결정해야 한다.
(만약, Referrer의 도메인별 처리가 필요하다면, 테이블의 처음 설계부터 referrer_domain컬럼 추가를 고려하는 것이 좋다.)


CRC32 함수를 사용할 경우
  CRC32 함수는 문자열을 입력으로 받아서, 32Bit unsigned값을 리턴해주는 함수이므로 
  추출값을 저장할 컬럼의 타입을 INT UNSIGNED 로 지정하면 충분하다.
  CRC32는 입력값이 다르다 하더라도 출력되는 값이 동일할 가능성이 상당히 높은 편이다.


MD5 함수를 사용할 경우
  MD5는 Message Digest 함수로써, 긴 문자열이나 데이터를 입력으로 받아서 128 Bit 체크섬을 리턴하는데, 
  MD5() 함수의 결과값은 32 글자의 Hex 문자열을 리턴한다.
  그래서 MD5를 사용할 경우에는 CHAR(32)로 설정하는 것이 일반적인데,
  (MD5 함수의 결과값은 항상 32글자 이므로 VARCHAR(32)보다는 CHAR(32)로 하는 것이 
   더 스토리지 공간을 절약할 수 있다.)
  만약, 데이터의 사이즈를 줄이고자 하는 경우에는 BINARY(16) 으로 타입을 생성하고 리턴값을 
  BINARY로 변환해서 저장하면 된다. 또한 MD5는 비밀번호 암호화 용도로도 주로 사용되는데,
  입력값이 다른 경우, 생성되는 체크섬의 중복 가능성이 상당히 희박하기 때문이다.


기타 해시 함수 사용
  그 해시 함수가 리턴해주는 값의 타입에 맞게 적절히 타입 결정해야 하는데,
  일반적으로 이러한 해시 함수는 MySQL의 SQL로 생성 가능한 것이 작업하기 편하기 때문에 
  CRC32 또는 MD5를 사용하는 것이 일반적이며, 이 함수들이 만족스럽지 못한 경우에는
  UDF를 직접 만들어서 추가해도 무방하다.
  (만약 이 경우를 선택하기로 했다면, FNV (Fowler-Voll-No) 해시 함수를 한번 참조해보는 
   것도 도움이 될듯 하다. - 상당히 빠르며 중복도 작게 발생하는 것으로 많이 알려져 있으며,
   이미 MySQL UDF로 구현된 것도 다운로드 할 수 있을 것이다.)
  
이 글에서는 MD5를 사용하는 것으로 예제를 살펴보겠다.
위에서 준비된 테이블에 Hash값을 저장할 추출 컬럼과 그 컬럼에 인덱스를 추가하자.


CREATE TABLE access_log(
  log_id bigint unsigned not null auto_increment,
  access_dttm datetime not null,
  agent_name varchar(50) not null,
  access_url varchar(2000) not null,
  referrer_url varchar(2000),
  referrer_hash CHAR(32) NOT NULL,
  ...
  PRIMARY KEY (log_id),
  INDEX ix_accessdttm (access_dttm),
  INDEX ix_referrerhash (referrer_hash)
);


이제 간단히 데이터를 저장하는 INSERT 문장과, SELECT 하는 문장을 예제로 살펴보자.


데이터 저장
INSERT INTO access_log (log_id, access_dttm, ..., referrer_url, referrer_hash)
  VALUES (NULL, '2010-11-11 10:22:12', ..., 'http://intomysql.blogspot.com',
          MD5('http://intomysql.blogspot.com'));


데이터 조회 (단순)
SELECT * 
FROM access_log
WHERE referrer_hash=MD5('http://intomysql.blogspot.com');


데이터 조회 (GROUP BY)
SELECT referrer_url, COUNT(*) 
FROM access_log
GROUP BY referrer_hash;


여기에서 한 가지 주의해야 할 사항은,
CRC32 해시 함수의 경우 상당히 중복 가능성이 높다. 그래서 CRC32 함수를 사용하는 경우에는 반드시 실제 데이터의 확인까지 필요하다.


SELECT * 
FROM access_log
WHERE referrer_hash=MD5('http://intomysql.blogspot.com')
  AND referrer_url='http://intomysql.blogspot.com';
  
CRC32가 MD5 보다는 빠르고 저장 공간도 적게 차지하지만, 이러한 부분들을 용인할 수 있다면,
MD5 함수를 해시 함수로 사용할 것을 권장한다. 
물론 MD5 함수도 중복의 가능성은 있지만, 거의 무시할 수 있을 정도이며, 만약 이 부분이 걱정된다면 위 예제처럼 실질값 비교를 추가해주어도 성능상 손실은 거의 없을 것으로 보인다.






그리고, 추가로
만약 MD5 해시 값을 저장하는 컬럼을 CHAR(32)가 아닌 BINARY(16)으로 선택한 경우에는 아래와 같이 INSERT, SELECT하면 된다.
데이터 저장
INSERT INTO access_log (log_id, access_dttm, ..., referrer_url, referrer_hash)
  VALUES (NULL, '2010-11-11 10:22:12', ..., 'http://intomysql.blogspot.com', 
  UNHEX(MD5('http://intomysql.blogspot.com')));


데이터 조회 (단순)
SELECT log_id, access_dttm, HEX(referrer_hash) as referrer_hash
FROM access_log
WHERE referrer_hash=UNHEX(MD5('http://intomysql.blogspot.com'));