10.6.1 Optimizing MyISAM Queries

10.6.1 Optimizing MyISAM Queries

MyISAM 테이블에서 쿼리 속도를 높이기 위한 몇 가지 일반적인 팁은 다음과 같습니다:

• MySQL이 쿼리를 더 잘 최적화하도록 돕기 위해, 데이터가 테이블에 로드된 후 해당 테이블에 대해 ANALYZE TABLE을 사용하거나 myisamchk --analyze를 실행하십시오. 이는 각 인덱스 파트에 대해 동일한 값을 가지는 행의 평균 개수를 나타내는 값을 갱신합니다. (유니크 인덱스의 경우 이 값은 항상 1입니다.) MySQL은 두 테이블을 비상수 식을 기반으로 조인할 때 어떤 인덱스를 선택할지 결정하기 위해 이 값을 사용합니다. SHOW INDEX FROM tbl_name을 사용하고 Cardinality 값을 확인하여 테이블 분석 결과를 확인할 수 있습니다. myisamchk --description --verbose는 인덱스 분포 정보를 보여줍니다.

• 인덱스 및 데이터를 특정 인덱스 기준으로 정렬하려면 myisamchk --sort-index --sort-records=1을 사용하십시오 (인덱스 1을 기준으로 정렬한다고 가정). 유니크 인덱스를 기준으로, 해당 인덱스 순서대로 모든 행을 읽어야 하는 경우 쿼리를 더 빠르게 만드는 좋은 방법입니다. 큰 테이블을 처음 이런 방식으로 정렬할 때는 매우 오랜 시간이 걸릴 수 있습니다.

• 자주 갱신되는 MyISAM 테이블에 대해, 리더와 라이터 간의 경합으로 인해 발생하는 테이블 잠금 문제를 피하기 위해 복잡한 SELECT 쿼리는 가능하면 피하십시오.

• MyISAM은 동시 삽입을 지원합니다. 만약 테이블의 데이터 파일 중간에 빈 블록이 없다면, 다른 스레드가 테이블에서 읽기를 수행하는 동시에 새로운 행을 해당 테이블에 INSERT할 수 있습니다. 이러한 동작이 중요하다면, 행 삭제를 피하는 방식으로 테이블을 사용하는 것을 고려하십시오. 또 다른 방법은 많은 행을 삭제한 후 OPTIMIZE TABLE을 실행하여 테이블을 조각 모음하는 것입니다. 이 동작은 concurrent_insert 변수 설정에 의해 변경될 수 있습니다. 삭제된 행이 있는 테이블에서도 새로운 행이 항상 파일 끝에 추가되도록 강제하여 (즉 동시 삽입을 허용하도록) 설정할 수 있습니다. Section 10.11.3, “Concurrent Inserts”를 참조하십시오.

• 자주 변경되는 MyISAM 테이블에 대해서는 가능한 모든 가변 길이 칼럼 (VARCHAR, BLOB, TEXT)을 피하도록 하십시오. 테이블에 가변 길이 칼럼이 하나라도 포함되면 동적 행 포맷을 사용하게 됩니다. Chapter 18, Alternative Storage Engines를 참조하십시오.

• 단지 행 크기가 커졌다는 이유만으로 테이블을 여러 개의 테이블로 분리하는 것은 일반적으로 유용하지 않습니다. 행에 접근할 때 가장 큰 성능 비용은 행의 첫 바이트를 찾기 위한 디스크 탐색입니다. 데이터를 찾고 나면 대부분의 최신 디스크는 대부분의 애플리케이션에 충분히 빠른 속도로 전체 행을 읽을 수 있습니다.

  테이블을 분리하는 것이 유의미한 차이를 만드는 경우는, 해당 테이블이 동적 행 포맷을 사용하는 MyISAM 테이블인데 이를 고정 행 크기로 변경할 수 있는 경우이거나, 테이블을 자주 스캔하지만 대부분의 칼럼이 필요하지 않은 경우입니다. Chapter 18, Alternative Storage Engines를 참조하십시오.

• 일반적으로 행을 expr1, expr2, ... 순서로 검색하는 경우, ALTER TABLE ... ORDER BY expr1, expr2, ...를 사용하십시오. 테이블에 대해 많은 변경 작업을 수행한 후에 이 옵션을 사용하면 더 높은 성능을 얻을 수 있습니다.

• 많은 행의 정보를 기반으로 카운트와 같은 결과를 자주 계산해야 한다면, 새로운 테이블을 도입하고 카운터를 실시간으로 갱신하는 방법이 더 바람직할 수 있습니다. 다음 형태의 갱신은 매우 빠릅니다:

1UPDATE tbl_name SET count_col=count_col+1 WHERE key_col=constant;

이 방식은 테이블 수준 잠금만 지원하는 MyISAM과 같은 MySQL 스토리지 엔진을 사용할 때 매우 중요합니다 (멀티플 리더, 싱글 라이터). 이 방식은 대부분의 데이터베이스 시스템에서도 더 나은 성능을 제공합니다. 이 경우 행 잠금 매니저가 수행해야 할 작업이 줄어들기 때문입니다.

• 동적 포맷 MyISAM 테이블에서 조각화를 피하기 위해 OPTIMIZE TABLE을 주기적으로 사용하십시오. Section 18.2.3, “MyISAM Table Storage Formats”를 참조하십시오.

• DELAY_KEY_WRITE=1 테이블 옵션으로 MyISAM 테이블을 선언하면, 테이블이 닫힐 때까지 인덱스 갱신이 디스크에 플러시되지 않으므로 인덱스 갱신이 더 빨라집니다. 단점은, 이러한 테이블이 열려 있는 상태에서 서버가 비정상 종료될 경우, myisam_recover_options 시스템 변수를 설정한 상태로 서버를 실행하거나 서버를 재시작하기 전에 myisamchk를 실행하여 테이블이 정상인지 확인해야 한다는 점입니다. (그러나 이 경우에도 DELAY_KEY_WRITE를 사용함으로써 어떤 것도 잃어버리지는 않습니다. 키 정보는 항상 데이터 행에서 생성할 수 있기 때문입니다.)

• 문자열은 MyISAM 인덱스에서 자동으로 접두사 및 끝 공백이 압축됩니다. Section 15.1.18, “CREATE INDEX Statement”를 참조하십시오.

• 애플리케이션에서 쿼리 또는 결과를 캐시한 후, 여러 개의 insert 또는 update를 한꺼번에 실행하면 성능을 높일 수 있습니다. 이 작업 동안 테이블을 잠그면 모든 갱신이 끝난 후 인덱스 캐시가 단 한 번만 플러시되도록 보장할 수 있습니다.