|
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 |
|
|
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이 데이터를 저장하는 방식 |
- 데이터베이스는 디렉토리로 저장된다.
- 테이블은 파일로 저장된다.
- 컬럼은 가변 길이나 고정 길이 유형으로 파일 안에 저장된다. 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) |
|
|
