7.2.5. Other Optimization Tips

• 연결 오버헤드를 피하기 위해 데이터베이스에 영구적인 접속을 사용하십시오. 만약 영구적인 접속을 사용하지 않고, 데이터베이스에 새로운 연결이 생성되는 경우라면, thread_cache_size 변수의 값을 조정할 수 있습니다. 7.11.2 "서버 파라미터 튜닝" 섹션을 참고하십시오.
• 이미 만들어진 테이블에서 모든 쿼리에 대해서 항상 정말로 인덱스를 사용하는지 여부를 검토해야합니다. MySQL에서는, EXPLAIN 구문을 사용하여 이것을 검토 할 수 있습니다. 7.8.1 "EXPLAIN을 활용한 쿼리 최적화" 섹션을 참고하십시오.
• 데이터를 읽고 쓰는 작업을 할 때 발생하는 충돌을 발생시키는 테이블 락킹 문제를 피하기 위해서, 자주 업데이트 되는 MyISAM 테이블에는 복잡한 SELECT 쿼리를 피해야 합니다.
• MyISAM엔진은 동시 INSERT를 지원합니다:
  만일 어떤 테이블이 데이터 파일의 중간에 빈 블록을 가지고 있지 않다면, 다른 쓰레드가 테이블에서 데이터를 읽고 있는 동안에도 INSERT를 사용해서 테이블 중간에 새로운 열을 삽입시킬 수가 있습니다. 만약 이 기능을 사용 가능하게 하는 것이 중요하다면, 테이블에서 레코드를 삭제하지 않는 방법을 사용하는 것을 고려할 수 있습니다. 또 다른 방법은 테이블에서 많은 행을 삭제한 이후에, 테이블 조각 모음 실행하는 OPTIMIZE TABLE 명령을 실행하는 것입니다. 이 문제는 concurrent_insert 변수를 변경하여 설정합니다. 심지어 삭제된 레코드가 있는 테이블에 새로운 레코드(rows)를 추가하는 것(그리고 동시 삽입을 허용하는 것)을 강제로 할 수 있습니다. 7.10.3, "동시 Inserts" 섹션을 참고하십시오.
• ARCHIVE 테이블 발생했을 수 있는 압축 문제를 해결하기 위해서는 OPTIMIZE TABLE을 사용할 수 있습니다. 13.6, "ARCHIVE 스토리지 엔진" 섹션을 참고하십시오.
• 일반적으로 expr1, expr2, ... 정렬하여 순서대로 검색하는 경우라면, ALTER TABLE ... ORDER BY expr1, expr2, ... 를 사용할 수 있습니다. 이 옵션을 사용하여, 테이블에 광범위한 변화를 주면, 높은 성능을 얻을 수 있습니다.
• 어떤 경우에는, 다른 컴럼의 정보를 해시 기반으로 컬럼의 정보를 저장합니다. 만약에 이 컬럼이 짧고, 합리적인 고유값이고, 색인 되었다면, 여러 컬럼에 "다양한" 인덱스보다 훨씬 빠릅니다. MySQL에서 확장 컬럼을 사용하는 것은 매우 쉬운일 입니다:

      SELECT * FROM tbl_name
      WHERE hash_col=MD5(CONCAT(col1,col2))
      AND col1='constant' AND col2='constant';
  

• 빈번하게 변경되는 MyISAM 테이블에 대해서 모든 가변 길이의 컬럼(VARCHAR, BLOB 및 TEXT)을 피하기 위해서 노력해야 합니다. 심지어 단 하나의 가변 데이터 컬럼이 포함된 경우 데이블은 동적 레코드(rows) 형식을 사용하십시오. Chapter 13, 스토리지 엔진을 참고하십시오.
• 일반적으로 다른 테이블에 테이블을 분할하는 것은 단지 레코드(rows)가 커지기만 하지 때문에 유용하지 않습니다. 레코드(rows)를 엑서스하는 것에서 가장 큰 성능을 좌우 하는 것은 레코드(rows)의 첫번째 바이트를 찾는 디스크의 검색 능력입니다. 데이터를 찾는 이후, 대부분의 최신 디스크는 충분히 빠른 속도로 대부분의 응용 프로그램에서 필요한 행 전체를 읽을 수 있습니다. 테이블을 만들어 분할하는 것에 대해서 주목할만한 점은, 고정 행 크기를 변경할 수 있는 동정 행 형식을 사용하는 MyISAM 테이블이 있거나, 또는 매우 자주 테이블을 스캔해야 하지만, 대부분의 컬럼이 필요하지 않은 경우가 유일합니다. Chapter 13, 스토리지 엔진을 참고하십시오.
• 많은 행의 정보에 기초하여 집계하는 것과 같은 결과를 자주 계산해야 하는 경우라면, 새로운 테이블에 실시간으로 집계결과(count)를 업데이트 하는 것이 바람직 할 수 있습니다. 다음 형식의 업데이트가 매우 빠릅니다:

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

  MyISAM과 같은 MySQL 스토리지 엔진을 사용할 때, 오직 테이블 수준의 락킹(단일 작성과 다중 조회)만 가지고 있다는 것은 매우 중요한 일입니다. 레코드(row) 락킹 메니저는 이러한 경우에 처리량을 줄일 수 있기 때문에, 대부분의 데이터베이스 시스템에서 더 나은 성능을 제공합니다.
• 대용량 로그 테이블에서 통계를 수집해야하는 경우, 전체 로그 테이블을 스캔하는 대신 요약 테이블을 사용하십시오. 요약을 유지 관리하는 것은 "실시간 운영데이터(live)"를 통계를 계싼하는 것보다 더 빠르게 해야 합니다. 로그에서 새로운 요약 테이블로 다시 생성하는 중에 어떤 것을들 변경되는 것(비스니스 결정에 따른)은 실행중인 응용프로그램을 변경하는 것보다 더 빠르게 변경할 수 있습니다.
• 가능하다면 "통계 데이터" 또는 "실시간 운영데이터(live)"를 분류하고, 요약 테이블은 실시간 운영데이터(live)에서 주기적으로 생성하며, 통계보고서에 필요한 데이터는 요약 테이블에서 생성합니다.
• 컬럼에 기본값이 설정되어 있다는 사실은 유용합니다. 기본값과 명확하게 다른 값이 입력되었을때에만 입력합니다. MySQL이 해야하는 구문 분석을 줄이고 입력 속도를 향상시킵니다.
• 어떠한 경우에는, BLOB 컬럼 안에 데이터를 포장(pack)하고 저장하는 것이 편리합니다. 이러한 경우에는, 응용프로그램에서 정보를 포장(pack)하고 해제하는 코드를 제공합니다. 하지만, 어떤 단계에서는 수 많은 엑서스를 동반할 수 있습니다. 레코드와 컬럼으로 구성된 테이블 구조에는 잘 맞지 않는 데이터가 있을 때, 실용적입니다.
• 일반적으로, 모든 데이터를 이중화가 아닌 상태(non redundant: 데이터베이스 이론에 규정된 제 3정규형을 관찰)를 유지하려고 합니다. 그러나, 정보를 복제하거나, 속도를 얻기 위해 요약 테이블을 만드는 것이 유리한 상황이 될 수 있습니다.
• 저장된 루틴 이나 UDFs(사용자 정의 함수)는 몇 가지 작업의 성능을 얻을 수있는 좋은 방법이 될 수 있습니다. 자세한 내용은 17.2 "저장된 루틴 (프로시저와 함수)", 그리고 21.3 "MySQL에 새로운 함수 추가"를 참고하십시오.
• 응용프로그램에서 쿼리 또는 답변을 캐싱하고 함께 많은 데이터를 삽입 또는 업데이트를 실행하여 성능을 향상시킬 수 있습니다. 만약 (MySQL과 같은) 데이터베이스 시스템에서 테이블 락을 지원하는 경우라면, 인덱스 캐시는 모든 업데이트가 실행된 이후에 단 한번만에 flush 되는 것을 보장하기 위해 도움이 됩니다. 또한, MySQL의 쿼리 캐시를 활용하여 유사한 결과를 얻을 수 있습니다. 7.9.3, "MySQL 쿼리 캐시"를 참고하십시오.
• 데이터가 기록되었는지를 알 필요가 없을때는 INSERT DELAYED를 사용하십시오. 하나의 디스크에 많은 행을 작성할 수 있기 때문에 전체적인 입력에 미치는 영향이 감소합니다.
• INSERT구문 보다 SELECT 구문의 높은 우선순위 가지고 싶을때, INSERT LOW_PRIORITY를 사용하십시오. 높은 우선순위의 SELECT를 사용하여, 검색 결과를 가지고 오는 것은 대기열(Qyeue)를 뛰어 넘는다. 심지어 write를 하기 위해서 대기하고 있는 클라이언트가 있더라도, SELECT가 실행됩니다. 낮은 우선순위(LOW_PRIORITY)와 높은 우선순위(HIGH_PRIORITY)는 오직 테이블 레벨 락킹을 사용하는 스토리지 엔진(MyISAM, MEMORY, MERGE와 같은)에만 적용됩니다.
• 하나의 SQL 문을 많은 행을 저장하는 여러 행 INSERT 문을 사용하십시오. MySQL을 포함하여, 대부분의 SQL 서버는 이것을 지원합니다.
• 대용량의 데이터를 로드할 수 LOAD DATA INFILE을 사용하십시오. 이것은 INSERT 구문을 사용하는 것보다 빠릅니다.
• 테이블의 각 행을 하나의 고유 값에 의해 구별 수 있도록 AUTO_INCREMENT 컬럼을 사용하십시오.
• MyISAM 테이블이 동적 형식으로 조각나는 것을 피하기 위해서, 가끔씩 OPTIMIZE TABLE을 실행 해야 합니다. 13.3.3, MyISAM 테이블 스토리지 형식을 참고하십시오.
• 더 빠른 속도를 가능하게 하는 MEMORY 테이블을 사용합니다. 13.4, MEMORY 스토리지 엔진을 참고하십시오. MEMORY 테이블은 웹 브라우저에서 쿠키가 활성화되지 않은 사용자를 위해서, 마지막으로 표시되는 배너에 대한 정보와 같이 자주 액서스되는 중요하지 않은 데이터에 유용하게 사용될 수 있습니다. 많은 웹 응용프로그램 환경에서 휘발성 상태 데이터를 처리하기 위한 사용자 세션이 사용가능한 또 다른 대안입니다.
• 웹서버에서 이미지나 다른 종류의 바이너리 컨텐츠는 일반적인 파일로 저장해야 햡니다. 즉, 데이터 베이스에 파일 자체를 저장하기 보다는 파일에 대한 참조만 저장하십시오. 대부분의 웹서버는 데이터베이스의 내용보다 캐싱되어진 파일을 사용하기 때문에 일반적으로 더 빠릅니다.
• 다른 테이블에 동일한 정보를 가진 컬럼은 해당 컬럼이 조인을 기반으로 빠르게 될 수 있도록 동일한 데이터 형식을 선언해야합니다.
• 컬럼 명을 간단하게 지정보십시오. 예를 들면, 이름이 'customer'인 테이블에서, 'customer_name' 라는 컬럼 명 대신 'name'을 사용하십시오. 다른 SQL 서버에 컬럼명을 이식하기 위해서, 명칭을 18자 보다 짧게 유지 하는 것이 좋습니다.
• 만약 정말 빠른 속도가 필요한 경우라면, 다른 SQL 서버도 지원하는 데이터 스토리지를 위한 낮은 수준의 공용 영역부터 살편보십시오. 예를 들면, MySQL MyISAM 스토리지 엔진에 직접 엑서스한다면, SQL 인터페이스를 사용하는 것보다 2~5배의 속도 향상을 얻을 수 있습니다. 이 작업을 수행할 수 있을려면, 데이터는 응용프로그램과 같은 서버에 있어야 하고, (외부 락은 정말 느리기 때문에) 보통 그것은 하나의 프로세스에 의해서 액서스해야 합니다. MySQL 서버에 있는 낮은 수준의 MyISAM 명령을 도입하여, (더 많은 성능이 필요한 경우 이것은 한가지 쉬운 방법이 될 수 있습니다.) 이러한 문제를 제거할 수 있습니다. 신중하게 데이터베이스 인터페이스를 설계하면, 이러한 종류의 최적화 지원을 매우 쉽게합니다.
• 숫자형 데이터를 사용하는 경우, 데이터베이스에서 텍스트 파일을 엑서스하는 것보다 대부분의 경우 숫자형 데이터를 사용하여 데이터에 접급하는 것이 더 빠르다. 데이터베이스안에서 데이터는 텍스트 파일에 비해서 조금더 컴팩트한 형식으로 저장 될 가능성이 높아서, 저 적은 디스크 접근을 포함하여 사용합니다. 라인이나 컬럼의 경계를 찾기 위해 텍스트 파일의 구문분석을 할 필요가 있기 때문에 또한 응용프로그램에서 코드를 저장합니다.
• 리플리케이션은 일부 작업에 대한 성능 이점을 제공할 수 있습니다. 부하를 분할하기 위해서 리플리케이션 서버 중에서 검색 클라이언트를 배포할 수 있습니다. 백업을 만드는 동안 마스터(서버)가 느려지는 것을 필할려면, 슬레이브 서버를 사용하여, 백업을 만들 수 있습니다. 15. 리플리케이션 섹션을 참고하십시오..
• DELAY_KEY_WRITE=1 테이블 옵션과 함께 MyISAM 테이블 선언하는 것은 테이블이 닫힐 때 까지 인덱스가 디스크로 플러시되지 않기 때문에, 인덱스 업데이트를 신속하게 합니다. 무엇인가 같은 테이블이 열려있는 동안 (강제로) 죽이는 경우라면, --myisam-recover-options 옵션을 사용하거나, 또는 서버를 다시 시작하기 전에 myisamchk를 실행하여 테이블이 괜찮은지 확인해야 합니다. (그러나 이런 경우에서 조차, DELAY_KEY_WRITE 사용에 의해서, 키 정보는 항상 데이터 레코드에서 생성할 수 있기 때문에, 아무런 데이터도 유실 되지 않습니다.)