mysql 최적화 작업

• 가능한한 NOT NULL로 컬럼을 선언한다. 속도가 빨라지며 각 컬럼마다 1 비트를 절약할 수 있다.

• default 값을 가질 때 유리하다. 입력되는 값이 기본값과 다를 때만 확실하게 값이 입력된다. INSERT 문에서 첫번째 TIMESTAMP 컬럼이나 AUTO-INCREAMENT 컬럼의 값을 입력할 필요가 없다.[mysql_insert_id()] 참고.

• 가능한한 테이블을 작게 만드려면 더 작은 integer 타입을 사용하자. 예를 들면 MEDIUM INT 가 보통 INT 보다 좋다.

• 가변 길이 컬럼이 없다면(VARCHAR, TEXT or BLOB columns), 고정 길이 레코드 포맷이 사용된다. 이 경우 속도는 더 빠르지만 불행히도(흑흑~) 낭비되는 공간이 더 많다. [Row format] 참고.

• mysql이 질의를 효과적으로 최적화하기 위해 많은 양의 데이터를 입력한후 isamchk –analyze를 실행하자. 이렇게 하면 동일한 값을 가진 줄의 평균 숫자를 가리키는 각 인덱스의 값을 업데이트한다. (물론 unique 인덱스에서는 항상 1이다)

• 인덱스와 인덱스에 따른 데이타를 정열하려면 isamchk –sort-index –sort-records=1 을 사용하자. 인덱스에 따라 정렬된 모든 레코드를 읽기 위해 unique 인덱스를 가졌다면 이렇게 하는 것이 속도를 빠르게 하는 가장 좋은 방법이다.

• INSERT 문에서 가능한 다중 값 목록을 사용하자. 개별적인 SELECT 문보다 훨씬 빠르다. 데이타를 테이블에 입력할 때 LOAD DATA INFILE을 사용하자. 많은 INSERT 문을 사용하는 것보다 보통 20배 빠르다. [Load] 참고.

• 많은 인덱스를 가진 테이블에 데이타를 입력할때 다음의 과정을 사용하면 속도를 향상시킬 수 있다. 1. mysql이나 Perl 에서 CREATE TABLE로 테이블을 만든다. 2. mysqladmin flush-tables 실행. (** 열린 테이블을 모두 닫음 **) 3. isamchk –keys-used=0 /path/to/db/tbl_name 사용. 테이블에서 모든 인덱스 사용을 제거한다. 4. LOAD DATA INFILE 를 이용 테이블에 데이타를 입력. 5. pack_isam을 가지고 있고 테이블을 압축하기 원하면 pack_isam을 실행. 6. isamchk -r -q /path/to/db/tbl_name 를 이용 인덱스를 다시 생성. 7. mysqladmin flush-tables 실행.

• LODA DATA INFILE 과 INSERT 문에서 더 빠른 속도를 내려면 키 버퍼를 증가시킨다. mysqld나 safe_mysqld에서 -O key_buffer=# 옵션을 사용하면 된다. 예를 들어 16M는 풍부한 램을 가졌다면 훌륭한 값이다.

• 다른 프로그램을 사용하여 데이타를 텍스트 파일로 덤프할때 SELECT … INTO OUTFILE을 사용하자. [LOAD DATA INFILE] 참고.

• 연속으로 다량의 insert와 update를 할 때 LOCK TABLE을 사용하여 테이블에 락을 걸면 속도를 향상시킬 수 있다. LOAD DATA INFILE 그리고 SELECT …INTO OUTFILE는 원자적이기 때문에 LOCK TABLE을 사용하면 안된다.

테이블이 얼마나 단편화되었는지 점검하려면 ‘.ISM’ 파일에서 isamchk -evi 를 실행한다.