What one can and should optimize


번역: 최영봉
편집: 황미영


자료제공: DATABASE.SARANG.NET
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 클라이언트