MYSQL optimize

[초보웹마스터]

What one can and should optimize

printable version

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 클라이언트