[튜닝] Mysql 서버 튜닝 요소

MySQL을 위한 하드웨어 최적화  

큰 테이블(2G가 넘는)이 필요하다면, 알파나 스팍 또는 IA64 등의 64bit 하드웨어를 고려해 보는 게 좋다. MySQL은 내부적으로 64bit 정수를 많이 사용하고 있으므로, 64bit CPU를 사용하면 좀더 나은 퍼포먼스를 기대할 수 있다.

거대한 데이터베이스를 위한 최적화는 보통 램, 빠른 디스크, CPU 순으로 진행된다.

더 많은 램은 사용되는 대부분의 key 페이지들을 램에 보관함으로써 빠른 key 갱신을 가능하게 한다.

안전한 트랜잭션을 사용하지 않거나 큰 디스크를 사용하고 파일 검사를 오랫동안 하는 일을 피하고 싶다면 UPS를 사용하여 전원 오류가 발생한 경우에도 시스템을 안전하게 종료시킬 수 있도록 하는 것도 좋은 생각이다.

하나의 전용 데이터베이스 서버를 가진 시스템이라면 1G 이더넷을 고려해 볼 필요가 있다. 네트워크 지연은 처리능력만큼 중요하다.(Latency is as important as throughput.)

디스크 최적화  

시스템, 프로그램, 임시 파일들을 위한 전용 디스크를 갖춰라. (내용이) 자주 변경되는 경우라면 갱신 기록과 트랜잭션 기록 파일을 별도의 디스크에 배치한다.

데이터베이스 디스크에 있어서는 빠른 탐색 시간(seek time)이 요건이다. 큰 테이블에서 하나의 레코드를 찾기 위해 소요되는 탐색 횟수는 다음과 같이 추정해 볼 수 있다.

log(row_count) / log(index_block_length/3*2/(key_length + data_ptr_length))+1

예를 들어, 500,000개의 레코드를 가지고 있고 medium int 형 필드로 인덱싱하고 있는 테이블의 경우라면 log(500000) / log(1024/3*2/(3+4))+1 = 4 번의 탐색이 필요하다. 여기서 인덱스는 500,000 * 7 * 3/2 = 5.2M 정도의 크기가 될 것이다. 실제로는 대부분의 블록들이 버퍼에 저장되므로 아마도 1~2번 정도의 탐색이 필요하게 된다.

쓰기의 경우 새로운 키를 넣을 위치를 찾기 위해 위에서처럼 4번의 탐색이 필요하지만, 통상적으로 인덱스를 갱신하기 위해 2번의 탐색이 더 필요하다.

매우 큰 데이터베이스에 경우, 디스크 탐색 속도에의해 성능이 좌우되는데, 탐색 수는 더 많은 데이터를 얻을 때마다 N log N 씩 증가한다.

데이터베이스들과 테이블들을 다른 디스크들에 분할해 넣어라. MySQL에서는 이를 위해 심볼릭 링크를 사용할 수 있다.

Striping disks(RAID 0와 같은)는 읽기와 쓰기 양면에서 처리능력을 증가시킨다.

미러링을 동반하는 Striping disk(RAID 0+1)는 읽기/쓰기 성능을 향상시키고 안전성을 제공한다. 쓰기는 약간 느리다.

임시파일 또는 쉽게 갱신될 수도 있는 데이터에 대해서 미러링이나 RAID(RAID 0는 예외)를 사용하지 않는다.

Linux를 사용한다면 부팅할 때 hdparm -m16 -d1 명령을 디스크에 적용하여 다중 섹터 읽기/쓰기와 DMA 사용이 가능하도록 한다. 이는 반응 시간을 5~50%까지 증가시킨다.

Linux를 사용한다면 디스크를 마운트할 때 async(기본값이다)와 noatime 옵션을 부여하여 마운트한다.

일부 특정 응용프로그램의 경우 아주 특수한 테이블을 램디스크에 저장하는 것도 한 방법이 된다. 그러나 보통은 필요 없다.

운영체제 최적화  

스왑을 제거한다. 메모리 문제가 있다면 시스템이 적은 메모리를 사용하도록 설정하기 보다는 메모리를 증설하는 것이 좋다.

데이터에 대해서 NFS 디스크를 사용하지 않는다. (NFS locking 문제에 봉착할 수 있다.)

시스템과 SQL 서버를 위해 open file 한계 수치를 증가시킨다. (safe_mysql 스크립트에 ulimit -n #을 추가한다.)

프로세스와 쓰레드의 개수 제한을 늘려준다.

상대적으로 큰 테이블을 사용할 일이 드물다면, 파일시스템이 파일을 여러 실린더에 분산시켜 저장하지 않도록 설정한다.(솔라리스)

큰 파일을 지원하는 파일시스템을 사용한다.(솔라리스)

어떤 파일시스템을 사용하는 것이 좋을지 선택한다. 리눅스의 Reiserfs 는 파일 열기, 읽기, 쓰기에 있어서 (ext2보다) 빠르다. 파일 검사도 단지 수 초 밖에 안 걸린다.

API 선택  

PERL

OS 와 데이터베이스들간의 이식성 우수하다.

빠른 프로토타이핑에 적합하다.

DBI/DBD 인터페이스를 사용하는 것도 한 방법이다.

PHP

PERL 보다 익히기 쉬운 언어다.

PERL 보다 자원을 적게 사용. 때문에 웹서버에 내장시키기에 좋다.

PHP4로 업그레이드하여 더 나은 속도를 얻는 것도 한 방편이다.

C

MySQL 본래의 인터페이스이다.

더 빠르고 더 많은 제어가 가능하다.

저 수준. 때문에 (프로그래머가) 더 많은 일을 해야 한다.

C++

고 수준. 코딩에 더 많은 시간이 필요하다.

(MySQL C++ API는) 여전히 개발 단계에 있다.

ODBC

윈도우즈와 유닉스에서 동작한다.

거의 대부분의 다른 SQL 서버로 이식 가능하다.

느리다. MyODBC는 단순한 pass-through 드라이버이지만 본연의 인터페이스에 비해 19% 정도 느리다.

같은 일을 수행하는 많은 다른 도구들이 있다. 작업을 어렵게 하는 한 가지는 많은 ODBC 드라이버들이 제각기 다른 부분에서 상이한 버그들을 가지고 있다는 점이다.

문제 발생 소지가 많다. 마이크로소프트는 정기적으로 인터페이스를 변경한다.

미래가 불확실하다.(마이크로소프트는 ODBC보다 OLE 쪽에 더 많은 비중을 두고 있다.)

JDBC

이론적으로 OS, 데이터베이스 간의 이식성이 우수하다.

(브라우저와 같은)웹 클라이언트 상에서 동작할 수 있다.

Python + others

좋을 것이다. 그러나 우리는 사용하지 않는다.

응용프로그램 최적화  

우선은 문제 해결에 집중하는 것이 필요하다.

응용프로그램을 제작할 때 다음 중 무엇이 가장 중요한지를 결정하는 것이 필요하다:

속도

OS 간의 이식성

SQL 서버들 간의 이식성

persistent connection을 사용한다.

응용프로그램 측의 캐싱은 SQL 서버의 부하를 감소시킨다.

응용프로그램 상에서 쓰이지 않는 컬럼은 쿼리하지 않는다.

SELECT * FROM table_name… 과 같은 쿼리를 사용하지 않는다.

응용프로그램의 모든 부분에 대하여 벤치마킹을 시도한다. 그러나 대부분의 노력을 부하의 가장 유력한 요인일 것 같은 부분의 응용프로그램들에 집중하는 것이 좋다. 이를 모듈 단위로 수행하면 발견한 병목구간을 빠른 ‘더미 모듈’로 대체하고 나서 다음 병목구간을 찾는 일로 넘어가는 식으로 일을 진행할 수 있다.

일련 작업 중에 많은 변경이 이루어진다면 LOCK TABLES을 이용한다. 예를 들면, 여러 개의 UPDATE 또는 DELETES 문장을 집합적으로 수행하는 경우 등.

이식성이 중요한 응용프로그램이라면  

Perl DBI/DBD

ODBC

JDBC

Python (또는 범용 SQL 인터페이스를 가진 다른 언어들) 등을 사용한다.

모든 대상 SQL 서버들이 갖추고 있는, 또는 쉽게 다른 구문으로 모사할 수 있는 SQL 구문만 사용한다. www.mysql.com 의 crash-me 페이지를 보면 도움이 될 것이다.

다른 OS나 SQL서버들에 없는 기능들을 제공하기 위해 wrapper 프로그램을 제작하여 사용한다.

보다 빠른 속도가 요구된다면  

병목구간(bottleneck)을 (CPU, 디스크, 메모리, SQL 서버, OS, API, 또는 응용프로그램에서) 찾아내서 제거하는 일에 집중한다.

더 빠른 속도와 유연성을 제공하는 MySQL의 확장기능을 사용한다.

SQL 서버에 관한 지식을 더 많이 습득하여 문제를 해결하기 위한 가장 빠른 SQL 구문을 사용하고 병목요소를 사전에 제거한다.

테이블 레이아웃과 쿼리들을 최적화한다.

select 속도를 증가시키기 위해 replication을 사용한다.

데이터베이스가 느린 네트워크로 연결되어 있다면, 압축된 클라이언트/서버 프로토콜을 사용한다.

응용프로그램의 초기 버전이 이식성에 있어서 부실하더라도 걱정할 필요 없다. 문제를 먼저 해결하고 나서 나중에 언제든지 최적화할 수 있다.(Don’t be afraid to make the first version of your application not perfectly portable; when you have solved your problem, you can always optimize it later.)

MySQL 최적화  

컴파일러와 컴파일 옵션을 충분히 고려하여 선택한다.

가장 훌륭한 MySQL 시작 옵션을 찾는다.

MySQL 매뉴얼을 찾아보고 Paul DuBois 의 MySQL 서적을 읽는다.

EXPLAIN SELECT, SHOW VARIABLES, SHOW STATUS, SHOW PROCESSLIST 명령을 사용한다.

쿼리 옵티마이저가 동작하는 방식을 공부해 둔다.

테이블을 관리한다.(myisamchk, CHECK TABLE, OPTIMIZE TABLE)

MySQL 확장기능을 사용하여 속도를 증진시킨다.

특정 함수가 많은 곳에서 자주 사용될 것이라면 MySQL 사용자 정의 함수(UDF)로 직접 제작한다.

정말 필요한 경우가 아니라면, 테이블 수준 또는 컬럼 수준에서 GRANT 를 사용하지 않는다.

MySQL 고객 지원 서비스에 비용을 지불하고 문제 해결을 위한 도움을 받는다 🙂

MySQL의 컴파일 및 설치  

자신의 시스템에서 사용 가능한 최상의 컴파일러를 선택함으로 보통 10~30% 정도 성능 향상을 기대할 수 있다.

Intel 기반의 리눅스 시스템이라면 MySQL을 pgcc(펜티엄급에 최적화된 버전의 gcc)로 컴파일 한다. 그러나, (컴파일된) 바이너리는 인텔 펜티엄 CPU에서만 동장할 것이다.

MySQL 매뉴얼에서 권하는 플랫폼 별 최적화 옵션을 사용한다.

통상적으로 특정 CPU를 위한 본연의 컴파일러(Sparc을 위한 Sun Workshop과 같은)를 사용하면 gcc 보다 더 나은 성능을 기대할 수 있다. 그러나, 항상 그런 것은 아니다.

MySQL을 사용하려는 한 가지 문자셋만 지정하여 컴파일한다.

mysqld 실행파일을 정적으로 컴파일(–with-mysqld-ldflags=-all-static)하고 strip sql/mysqld 명령으로 최종 실행파일에서 디버그 코드를 제거한다.

MySQL이 C++ 예외처리를 하지 않으면, 즉 예외처리 지원 옵션을 빼고 컴파일하면 성능이 크게 향상된다.

운영체제가 네이티브 쓰레드(native thread)를 지원한다면 mit-pthreads 라이브러리 대신 네이티브 쓰레드를 사용하도록 한다.

생성된 실행파일을 MySQL 벤치마크 테스트로 테스트해 본다.

유지 보수  

가능하면 정기적으로 OPTIMIZE table 을 실행한다. 이는 특히 자주 갱신되는 가변크기 레코드들에 대해 중요하다.

정기적으로 myisamchk -a 명령을 사용하여 테이블들의 key 분산 상태를 갱신한다. 이 작업을 수행하기 전에 반드시 MySQL을 셧다운해야 한다는 점을 잊지 않는다.

파일들이 조각난 상태라면 다른 디스크로 모두 복사하고 기존의 디스크를 깨끗이 한 후 다시 파일을 옮기는 일도 시도해 볼만한 가치가 있다.

문제가 발생한다면, 테이블을 myisamchk나 CHECK table 명령으로 검사한다.

MySQL의 상태를 mysqladmin -i10 processlist extended-status 명령으로 모니터한다.

MySQL GUI 클라이언트를 사용하면 프로세스 목록과 상태를 다른 윈도우에서 모니터할 수 있다.

mysqladmin debug 명령을 사용해서 잠금(lock)과 성능에 관한 정보를 얻는다.

SQL 최적화  

사용하는 것이 좋은 것에만 SQL을 사용하고 그렇지 않은 곳에서는 다른 것을 사용한다. SQL는 다음과 같은 곳에 사용한다.

WHERE 절에 의존하여 행을 찾는 경우

테이블들을 JOIN 할 경우

GROUP BY

ORDER BY

DISTINCT

다음과 같은 일에는 사용하지 않는다.

데이터(date 와 같은)의 유효성을 검증하는 경우

계산기로 사용

Tips

key를 폭 넓게 사용한다.

key는 검색에는 좋지만, key 컬럼에 대해 insert 나 update를 수행하는 데는 좋지 않다.

데이터를 제3의 보통 데이터베이스 형식(in the 3rd normal database form)으로 유지하되, 속도를 중시한다면 정보의 중복이나 요약 테이블(summary tables)을 생성하는 일을 기피할 필요는 없다.

큰 테이블에 대해서 GROUP BY를 남용하는 대신 그 테이블에 대한 요약 테이블을 생성하고 이 테이블에 대해 쿼리하는 것이 낫다.

UPDATE table set count=count+1 where key_column=constant 와 같은 문장은 매우 빠르다!

기록 테이블(log tables)에 관한 한, 정기적으로 요약 테이블을 만드는 것이 요약테이블을 그대로 두는 것 보다 나을 것이다.

INSERT에서 디폴트 값(default values)의 잇점을 십분 활용한다.

SQL 서버들 간의 속도 차이 (단위:초)  

key 이용 2,000,000 행 읽기  NT  Linux  

mysql 367 249

mysql_odbc 464  

db2_odbc 1,206  

imformix_odbc 121,126  

ms-sql_odbc 1,634  

oracle_odbc 20,800  

solid_odbc 877  

sybase_odbc 17,614  

350,768 행 삽입 NT Linux

mysql 381 206

mysql_odbc 619  

db2_odbc 3,460  

informix_odbc 2,692  

ms-sql_odbc 4,012  

oracle_odbc 11,291  

solid_odbc 1,801  

sybase_odbc 4,802  

위의 테스트는 MySQL의 경우 8M 캐시를 사용하도록 설정한 것이고 다른 데이터베이스들은 설치 기본값을 이용하였다.

중요한 MySQL 기동 옵션들  

back_log 접속 수가 많다면 변경한다.

thread_cache_size 접속 수가 많다면 변경한다.

key_buffer_size 인덱스 페이지를 위한 풀(pool) 크기. 큰 수치를 지정하는 것도 가능하다.

bdb_cache_size BDB 테이블들에 의해 사용되는 레코드와 키 캐시 크기.

table_cache 많은 테이블을 가지고 있거나 동시 접속 수가 많다면 변경한다.

delay_key_write 모든 키 쓰기 동작을 버퍼링할 필요가 있다면 지정한다.

log_slow_queries 시간이 많이 걸리는 쿼리를 찾을 때 사용한다.

max_heap_table_size GROUP BY 절에서 사용된다.

sort_buffer ORDER BY 와 GROUP BY 절에서 사용된다.

myisam_sort_buffer_size REPAIR TABLE 문에서 사용된다.

join_buffer_size 키 없이 join 할 때 사용된다.

테이블 최적화  

MySQL은 풍부한 상이한 컬럼 유형(type)들의 집합을 가지고 있다. 각 컬럼에 대해 가장 효과적인 유형을 선택하여 사용하는 것이 필요하다.

ANALYSE 프로시저는 테이블을 위한 최적의 컬럼 유형을 찾는데 도움이 될 것이다. SELECT * FROM table_name PROCEDURE ANALYSE()

널 값을 저장하지 않을 컬럼은 NOT NULL 로 지정한다. 이는 특별히 인덱스 컬럼의 경우 중요하다.

ISAM 테이블들을 MyISAM 으로 변경한다.

가능하다면, 테이블을 고정된 테이블 형식으로 만드는 것이 좋다.

사용하지 않을 인덱스는 아예 만들지 않는다.

MySQL이 인덱스의 접두부(prefix)에 대해서 검색을 수행할 수 있다는 점을 활용한다. INDEX (a, b) 로 되어 있다면, (a)에 대해 인덱싱할 필요는 없다.

길이가 긴 CHAR 형이나 VARCHAR 형이라면 해당 컬럼에 대해 인덱스를 생성하지 않고 그 컬럼의 접두부에 대해서만 인덱스를 생성하면 공간이 절약된다.

CREATE TABLE table_name (hostname CHAR(255) not null, index(hostname(10)))

각 테이블을 위한 가장 효과적인 테이블 유형을 사용한다.

서로 다른 테이블들 중 동일한 정보를 가지는 컬럼들은 같은 유형, 같은 이름을 가지도록 정의한다.

MySQL이 데이터를 저장하는 방식  

데이터베이스는 디렉토리로 저장된다.

테이블은 파일로 저장된다.

컬럼은 가변 길이나 고정 길이 유형으로 파일 안에 저장된다. BDB 테이블에서 데이터는 페이지에 저장된다.

메모리 기반의 테이블도 지원된다.

데이터베이스와 테이블들은 다른 디스크로부터 심볼릭 링크될 수 있다.

Windows용 MySQL은 .sym 파일을 이용하여 데이터베이스에 대한 내부적인 심볼릭 링크를 지원한다.

MySQL 테이블 유형들  

HEAP 테이블: 고정된 수의 레코드만 가지는 테이블로서 오직 메모리에만 저장되며 HASH 인덱스로 인덱스된다.

ISAM 테이블: MySQL 3.22에서 사용된 구식 B-tree 테이블 유형이다.

MyISAM 테이블: ISAM 테이블의 새 버전으로 많은 확장 기능들을 가지고 있다.

바이너리 호환성

NULL 컬럼에 대한 인덱싱

가변 크기 테이블의 파편화(fragmentation)가 ISAM 테이블 보다 적음

거대 파일 지원

인덱스 압축 향상

키 통계 향상

더 향상되고 빠른 auto_increment 지원

Sleepycat의 Berkeley DB(BDB) 테이블: 안전한 트랜잭션 지원(BEGIN WORK / COMMIT | ROLLBACK)

MySQL 레코드 유형(ISAM/MyISAM 테이블과 관련하여서만)  

MySQL은 모든 컬럼들이 고정 크기 유형이라면 (VARCHAR, BLOB, TEXT 컬럼이 없다면) 테이블을 고정 크기 테이블로 생성한다. 그렇지 않다면, 가변 크기 유형의 테이블로 만든다.

고정 크기 유형은 동적 크기 유형에 비해 속도가 빠르며 안전하다.

동적 크기 레코드 유형은 대개 보다 적은 공간을 사용하지만 테이블의 갱신이 자주 발생한다면 파편화가 가중되기 마련이다.

어떤 경우에는 주 테이블의 속도를 향상시키기 위해 모든 VARCHAR, BLOB, TEXT 컬럼들을 다른 테이블로 옮기는 것도 유용할 때가 있다.

myisampack(ISAM 테이블에서는 pack_isam)을 사용하면 읽기 전용, 압축된 테이블을 만들 수 있다. 느린 디스크를 사용할 때는 디스크 사용량을 최소화하는 것이 좋은 한 방법이 된다. 압축된 테이블은 더 이상 갱신되지 않는 로그 테이블 등에 사용하면 최상이다.

MySQL 캐시들 (한 번 적재되어 모든 쓰레드가 공유)  

키 캐시: key_buffer_size, 기본값은 8M

테이블 캐시: table_cache, 기본값은 64

쓰레드 캐시: thread_cache_size, 기본값은 0

호스트명 캐시: 컴파일할 때 변경 가능, 기본값은 128

메모리에 맵핑된 테이블(Memory mapped tables): 현재는 압축된 테이블을 위해서만 사용된다.

MySQL은 행(raw) 캐시를 가지고 있지 않다. 그러나, 운영체제에게 이 일을 시킬 수 있다!

MySQL 버퍼 변수들 (공유되지 않으며 실행 중 적재됨)  

sort_buffer: ORDER BY / GROUP BY 절에서

record_buffer: 테이블을 스캔할 때

join_buffer_size: 키 없이 join을 수행할 때

myisam_sort_buffer_size: REPAIR TABLE에서

net_buffer_length: SQL 문장을 읽을 때와 결과 값을 버퍼링할 때

tmp_table_size: 임시 결과값을 위한 HEAP-table-size

MySQL 테이블 캐시가 동작하는 방식  

MyISAM 테이블의 열려 있는 각각의 인스턴스는 인덱스 파일과 데이터 파일을 사용한다. 만일 어떤 테이블이 두 개의 쓰레드에 의해 사용되거나 같은 쿼리에서 두 번 사용되면, MyISAM이 인덱스 파일은 공유하지만 데이터 파일은 또 하나의 인스턴스를 위해 추가로 열게 된다.

캐시 안의 모든 테이블이 사용 중이라면 그 캐시는 일시적으로 테이블 캐시 크기보다 커진다. 이러한 상황이 발생하면, 그 다음 방면된 테이블이 닫히게 된다.

mysqld 변수 Opend_tables를 검사해 보면 테이블 캐시가 너무 작은지 아닌지를 알 수 있다. 이 값이 높으면 테이블 캐시를 늘려줘야 한다!

MySQL 확장들 / 속도 증진 최적화  

최적화된 테이블 유형을 사용한다.(HEAP, MyISAM, BDB 테이블)

데이터를 위한 최적의 컬럼을 사용한다.

가능한 한 고정 크기 레코드를 사용한다.

다른 잠금 유형(lock types)를 사용한다.(SELECT HIGH_PRIORITY, INSERT LOW_PRIORITY)

Auto_increment

REPLACE (REPLACE INTO table_name VALUES (…))

INSERT DELAYED

LOAD DATA INFILE / LOAD_FILE()

한번에 많은 레코드를 추가하기 위해서는 다중 레코드 INSERT를 사용한다.

SELECT INTO OUTFILE

LEFT JOIN, STRAIGHT JOIN

IS NULL 과 접목된 LEFT JOIN 사용

일부 경우, ORDER BY 는 키를 사용할 수 있다.

하나의 인덱스에 있는 컬럼들만 쿼리할 경우에는 쿼리를 수행하기 위해 그 인덱스 트리만 사용하게 된다.

조인은 보통 subselect 보다 빠르다. (대부분의 SQL 서버들에서 그러하다.)

LIMIT

SELECT * from table1 WHERE a > 10 LIMIT 10, 20

DELETE * from table1 WHERE a > 10 LIMIT 10

foo IN (상수 목록) 구문은 매우 최적화되어 있다.

GET_LOCK() / RELEASE_LOCK()

LOCK TABLES

INSERT 와 SELECT 는 동시에 실행 될 수 있다.

작동하고 있는 서버로 읽어 들일 수 있는 UDF 함수들

압축된 읽기 전용 테이블들

CREATE TEMPORARY TABLE

CREATE TABLE .. SELECT

MyISAM 테이블을 RAID와 사용하면 하나의 파일을 여러개의 파일들로 나누어 일부 파일시스템의 2G 제한을 넘어서는 것이 가능하다.

Delayed_keys

리플리케이션(replication)

MySQL이 인덱스를 사용할 경우  

>, >=, =, <, <=, 키에 대해 IF NULL 과 BETWEEN을 사용할 때

SELECT * FROM table_name WHERE key_part1=1 and key_part2 > 5;

SELECT * FROM table_name WHERE key_part1 IS NULL;

와일드카드 문자로 시작하지 않는 LIKE 절을 사용할 때

SELECT * FROM table_name WHERE key_part1 LIKE ‘jani%’

조인을 수행하면서 다른 테이블들로부터 레코드를 가져올 때

SELECT * from t1, t2 where t1.col=t2.key_part;

특정 인덱스에 대해서 MAX() 나 MIN() 값을 구할 때

SELECT MIN(key_part2), MAX(key_part2) FROM table_name where key_part1=10;

키의 접두부에 대해 ORDER BY 나 GROUP BY 절을 수행할 때

SELECT * FROM foo ORDER BY key_part1, key_part2, key_part3;

쿼리에 사용되는 모든 컬럼이 한 개의 키의 부분(part)일 경우

SELECT key_part3 FROM table_name WHERE key_part1=1;

MySQL이 인덱스를 사용하지 않을 경우  

MySQL은 테이블 전체를 스캔하는 더 빠를 것이라고 판단되면 인덱스를 사용하지 않는다. 예를 들어, key_part1이 1과 100사이의 값을 고르게 가지고 있다면, 다음과 같은 쿼리에서 인덱스를 사용하는 것은 좋지 않다.

SELECT * FROM table_name where key_part1 > 1 and key_part1 < 90;

HEAP 테이블을 사용하고 있으며, 모든 키 부분들에 대해서 = 로 검색하지 않을 경우

HEAP 테이블에 대해 ORDER BY 절로 쿼리할 경우

맨 처음의 키 부분을 사용하지 않을 경우

SELECT * FROM table_name WHERE key_part2 = 1;

와일드카드 문자로 시작하는 LIKE 를 사용할 경우

SELECT * FROM table_name WHERE key_part1 LIKE ‘%jani%’

하나의 인덱스에 대해서 검색하면서 다른 인덱스에 대해서는 ORDER BY 를 적용할 때

SELECT * FROM table_name WHERE key_part1 = # ORDER BY key2;

EXPLAIN 사용법 익히기  

지나치게 느리다고 생각되는 모든 쿼리 문장에 대해 EXPLAIN 을 사용한다.

mysql> explain select t3.DateOfAction, t1.TransactionID

-> from t1 join t2 join t3

-> where t2.ID = t1.TransactionID and t3.ID = t2.GroupID

-> order by t3.DateOfAction, t1.TransactionID;

+——-+——–+—————+———+———+——————+——+———————————+

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

+——-+——–+—————+———+———+——————+——+———————————+

| t1    | ALL    | NULL          | NULL    | NULL    | NULL             | 11   | Using temporary; Using filesort |

| t2    | ref    | ID            | ID      | 4       | t1.TransactionID | 13   |                                 |

| t3    | eq_ref | PRIMARY       | PRIMARY | 4       | t2.GroupID       | 1    |                                 |

+——-+——–+—————+———+———+——————+——+———————————+

유형 ALL과 범위는 잠재적인 문제점을 알리고 있다.

SHOW PROCESSLIST 사용법 익히기  

현재 진행 상황을 파악하기 위해서는 SHOW processlist 를 사용한다.

+—-+——-+———–+—-+———+——+————–+————————————-+

| Id | User  | Host      | db | Command | Time | State        | Info                                |

+—-+——-+———–+—-+———+——+————–+————————————-+

| 6  | monty | localhost | bp | Query   | 15   | Sending data | select * from station,station as s1 |

| 8  | monty | localhost |    | Query   | 0    |              | show processlist                    |

+—-+——-+———–+—-+———+——+————–+————————————-+

mysql에서 KILL을 사용하거나 명령행에서 mysqladmin을 사용하여 불필요한(runaway) 쓰레드들을 없앨 수 있다.

MySQL이 쿼리를 해결하는 방법을 찾는 방법  

다음 명령들을 실행해서 결과를 이해하도록 노력한다.

SHOW VARIABLES;

SHOW COLUMNS FROM … \\G

EXPLAIN SELECT … \\G

FLUSH STATUS;

SELECT …;

SHOW STATUS;

MySQL은 이런 경우 극히 우수하다  

로그 기록시

많은 연결이 이루어 질 때, 연결 속도가 매우 빠르다.

SELECT 와 INSERT 를 동시에 사용하는 곳에서.

update를 시간이 오래 걸리는 select 문과 결합하지 않을 때

대부분의 select/update 문이 고유한 키들을 사용할 때

많은 테이블을 장시간 잠금(lock) 충돌 없이 사용할 때

크기가 큰 테이블을 가지고 있을 때 (MySQL 은 매우 컴팩트한 테이블 포맷을 사용한다.)

MySQL 사용에 있어서 피해야 할 것들  

테이블을 UPDATE 또는 삭제된 행을 테이블에 대해 INSERT하면서 시간이 오래 걸리는 SELECT 절들과 결합시키는 일

WHERE 절에 올 수 있는 것들에 대한 HAVING

키를 사용하지 않은, 또는 충분히 유니크하지 않은 키를 사용한 JOIN

컬럼 유형이 서로 다른 컬럼들에 대해 JOIN 수행

온전한 키 전체가 아닌 키의 부분에 대해서만 ‘=’ 로 비교연산할 때 HEAP 테이블을 사용

MySQL monitor 에서 UPDATE 나 DELETE를 사용하면서 WHERE 절을 생략하는 일. 만약 자신이 이런 경향이 있다면, mysql 클라이언트 프로그램을 실행할 때 –i-am-a-dummy 옵션을 추가하기 바란다.

MySQL의 독특한 잠금들(locks)  

내장된 테이블 잠금

LOCK TABLES (모든 테이블 유형에 대해 동작함)

GET_LOCK() / RELEASE_LOCK()

Page locks (BDB 테이블에 대해서)

ALTER TABLE 역시 BDB 테이블에 대해 테이블 잠금을 수행함

LOCK TABLES 는 다중 읽기 작업 또는 한개의 쓰기 작업을 허용한다.

보통 WRITE 잠금은 READ 잠금 보다 우선 순위가 높다. 쓰기 작업이 무한정 대기 상태에 놓이게 되는 경우를 피하기 위해서다(to avoid starving the writers). 그리 중요하지 않은 쓰기 작업은 LOW_PRIORITY 키워드를 사용하여 lock handler가 읽기 작업에 먼저 허가를 내어 주도록 하는 것도 한 방법이다.

UPDATE LOW_PRIORITY SET value=10 WHERE id=10;

문제를 쉽게 해결하기 위해 MySQL로부터 더 많은 정보를 얻는 기법들  

MySQL 만의 기능들을 항상 주석처리함으로써 쿼리의 이식성 높일 수 있다.

SELECT /*! SQL_BUFFER_RESULTS */ …

SELECT SQL_BUFFER_RESULTS …

MySQL이 임시 결과 세트를 만들도록 강제한다. 임시 세트가 만들어지면, 그 테이블들에 대한 모든 잠금이 해제된다. 이는 테이블 잠금으로 인해 문제가 발생했을 때나 쿼리 결과를 클라이언트로 전송하는데 오랜 시간이 소요되는 경우에 도움이 된다.

SELECT SQL_SMAIL_RESULT … GROUP BY …

결과 세트가 적은 수의 레코드만 가지게끔 하도록 옵티마이저에게 지시한다.

SELECT SQL_BIG_RESULT … GROUP BY …

결과 세트가 많은 수의 레코드를 가지도록 옵티마이저에게 지시한다.

SELECT STRAIGHT_JOIN …

옵티마이저가 FROM 절에 나타난 순서대로 테이블을 join 하도록 강제한다.

SELECT … FROM table_name [USE INDEX (index_list) | IGNORE INDEX (index_list)] table_name2

MySQL이 특정 인덱스들을 사용하거나 무시하도록 강제한다.

트랜잭션 사용예  

MyISAM 테이블에 대해 트랜잭션을 수행하는 방법:

mysql> LOCK TABLES trans READ, customer WRITE;

mysql> select sum(value) from trans where customer_id=some_id;

mysql> update customer set total_value=sum_from_previous_statement where customer_id=some_id;

mysql> UNLOCK TABLES;

Berkeley DB 테이블에 대해 트랜잭션을 수행하는 방법:

mysql> BEGIN WORK;

mysql> select sum(value) from trans where customer_id=some_id;

mysql> update customer set total_value=sum_from_previous_statement where customer_id=some_id;

mysql> COMMIT;

다음과 같이 함으로써 여러 트랜잭션들 간의 간섭을 방지할 수 있다는 점에 주목할 필요가 있다:

UPDATE customer SET value=value+new_value WHERE customer_id=some_id;

REPLACE 사용예  

REPLACE는 테이블의 이전 레코드가 새 레코드와 같은 고유 인덱스 값을 가지고 있다면 예전 레코드가 먼저 삭제되고 새 레코드가 추가된다는 점만 제외하면 INSERT와 똑같이 작동한다.

다음과 같이 하는 대신,

SELECT 1 FROM t1 WHERE key=#

IF found-row

LOCK TABLES t1

DELETE FROM t1 WHERE key1=#

INSERT INTO t1 VALUES (…)

UNLOCK TABLES t1;

ENDIF

다음과 같이 한다.

REPLACE INTO t1 VALUES (…)

일반적인 팁  

프라이머리 키는 짧은 것을 사용한다. 테이블 조인할 때는 문자열형 보다는 숫치형을 사용한다.

여러 부분으로 구성된 키를 사용할 때는 첫 번째 부분이 가장 많이 사용되는 키이어야 한다.

의심스러울 때는, 앞부분이 더 많이 중복된 컬럼을 사용해서 보다 나은 키 압축 효과를 얻는다.

클라이언트를 실행 중이고 MySQL 서버가 같은 머신에 있다면, TCP/IP 대신에 유닉스 소켓을 사용하여 서버에 연결하는 것이 좋다.(이렇게 하면 7.5% 정도까지 효율이 증진된다.) MySQL 서버에 접속할 때 호스트이름이나 localhost를 지정하지 않으면 유닉스 소켓으로 접속하게 된다.

가능하다면 –skip-locking(일부 운영체제에서는 이 것이 기본값이다)을 사용한다. 이는 외부적인 잠금을 사용하지 않게 되고 퍼포먼스가 향상된다.

긴 키를 사용하기 보다는 응용프로그램 수준에서 해시된 값을 사용한다.

SELECT * FROM table_name WHERE hash=MD5(concat(col1,col2)) AND col_1=’constant’ AND col_2=’constant’;

Store BLOB’s that you need to access as files in files. 데이터베이스에는 파일이름만 저장한다.

레코드들의 큰 부분을 지우는 것 보다 레코드 전체를 지우는 것이 더 빠르다.

SQL이 충분히 빠르지 않다면, 데이터에 접근하는 더 낮은 수준의 인터페이스를 점검해 본다.

MySQL 3.23을 사용할 때 얻을 수 있는 이점  

MyISAM; 이식이 수월한 거대 테이블 유형

HEAP; 메모리 상의 테이블

Berkeley DB; Sleepycat에서 제공하는 트랙잰션이 가능한 테이블

대폭 확대된(풀린) 제한들

동적 문자셋

더 많이 제공되는 STATUS 변수들

CHECK table, REPAIR table

더 빠른 GROUP BY 절과 DISTINCT 절

최적화된 LEFT JOIN … IF NULL

CREATE TABLE … SELECT

CREATE TEMPORARY table_name (…)

임시적인 HEAP에서 MyISAM 테이블로의 자동 변환

리플리케이션

mysqlhotcopy 스크립트

실제 작업에서 중요한 기능들  

진보된 트랜잭션

오류로부터 안전한 리플리케이션

텍스트 검색

많은 테이블의 삭제 (이 작업 후에 많은 테이블의 갱신이 이루어진다.)

너 나은 키 캐시

원자화된 RENAME (RENAME TABLE foo as foo_old, foo_new as foo)

쿼리 캐시

MERGE TABLES

향상된 GUI 클라이언트