MySQL 데이터베이스 최적화 방법의 요약

중간 거래 SEO 진단 Taobao 게스트 클라우드 호스트 기술 홀

데이터베이스 최적화 그것은 궁극적으로 시스템 최적화의 좋은 이해를 요구 하기 때문에 복잡 한 작업입니다. 비록 시스템 또는 응용 프로그램 시스템은 모르는 최적화 효과 대해 많은 좋은, 하지만 더 나은 효과 최적화 하려는 경우, 당신은 그것에 대해 더 알고 해야 합니다.

1, 최적화 개요

빨리 실행 하는 시스템을 만드는 가장 중요 한 요소는 데이터베이스의 기본 디자인입니다. 그리고 시스템, 일어나 고 존재 하는 병목 현상의 인식 해야 합니다.

가장 일반적인 시스템 병목 상태는 다음과 같습니다.

디스크 검색입니다. 그것은 천천히 데이터의 블록에 대 한 디스크를 검색합니다. 현대 디스크에 대 한 평균 검색 시간 이므로 기본적으로 10 밀리초, 이론적으로 초당 100 디스크 검색을 할 수 있다. 이 시간은 많이 향상 된 새 디스크 및 단일 테이블에 대 한 되지 않습니다. 검색 시간을 빠르게 하는 방법은 여러 디스크에 데이터를 분리 하는 것입니다.

디스크 읽기/쓰기입니다. 디스크가 올바른 위치에 있을 때 데이터를 읽을 해야 합니다. 현대 디스크, 디스크 처리량 적어도 10-20 m b/초입니다. 이것은 여러 미디어에서 동시에 데이터를 읽을 수 있기 때문에 디스크 검색의 최적화 보다 쉽습니다.

CPU 사이클입니다. 메인 메모리에 데이터 저장 (또는 그것은 이미 주 메모리에)는 원하는 결과 얻기 위해 데이터를 처리 해야 합니다.

메모리 대역폭입니다. CPU CPU 캐시에 더 많은 데이터를 저장 하는 때 메인 메모리 대역폭은 병목 이다.입니다. 이건 일반적인 병목 대부분의 시스템만 알고 있어야 하는 요소입니다.

1.1 MySQL 디자인의 한계

MyISAM 스토리지 엔진을 사용 하 여 MySQL 여러 읽기 및 하나 한 번에 쓸 수 있도록 빠른 데이터 테이블 잠금을 사용 합니다. 이 저장소 엔진에 대 한 가장 큰 문제는 안정적인 업데이트 작업 및 느린 쿼리는 단일 테이블에 있는 일어난 그것입니다. 이 경우 테이블에 있으면 다른 테이블 형식을 사용할 수 있습니다.

MySQL은 트랜잭션 및 비 트랜잭션 테이블에서 작업할 수 있습니다. 비-트랜잭션 테이블을 원활 하 게 사용할 수 있을 위하여 (수 구를 오류가 발생 하는 경우에 다시), 몇 가지 규칙이 있습니다:

모든 필드는 기본값

숫자 형식 필드에는 과도 한 값을 삽입 하는 등 필드에 "오류" 값을 삽입 하면 MySQL 오류가 주는 대신 "가장 가능성이 값"으로 필드 값을 넣을 것입니다. 숫자 형식의 값은 0, 작은 또는 큰 가능한 가치. 문자열 유형, 아니라 빈 문자열 필드에 저장할 수 있는 최대 길이입니다.

모든 평가 식 가치와 보고서 오류를 반환 조건, 예를 들어 1/0은 NULL을 반환 합니다.

이러한 규칙은 암시적으로 필드의 내용을 확인 하려면 MySQL을 사용할 수 없습니다 것을 의미. 대신, 그것은 체크 해야만 응용 프로그램에서 데이터베이스에 저장 되기 전에.

1.2 응용 프로그램 디자인의 이동성

다양 한 데이터베이스는 그들의 자신의 SQL 표준을 구현,이 우리가 휴대용 SQL 응용 프로그램을 사용 하려고 필요 합니다. 쿼리 및 삽입 작업을 쉽게 마이그레이션할 수 있지만 제한적인 요구 사항으로 인해 더 어렵습니다. 다양 한 데이터베이스 시스템에서 빠른 실행 응용 프로그램을 만들기 위해 더 어려워집니다.

휴대용 수 복잡 한 응용 프로그램에 대 한 순서, 어떤 종류의 응용 프로그램이 실행 되는 데이터베이스 시스템에서 첫 번째 모양과 다음 데이터베이스 시스템을 지 원하는 기능을 참조 하십시오. 각 데이터베이스 시스템에 몇 가지 결함이 있다. 즉, 디자인에 일부 타협 때문에 성능 차이가 발생 합니다.

MySQL 충돌을 사용할 수 있습니다-내가 선택한 데이터베이스 서버에 보고 프로그램 함수, 유형, 제한 등 사용할 수 있습니다. 충돌-나 다양 한 가능한 기능에 대 한 확인 하지 않습니다 하지만 그것은 여전히 450 테스트에 대 한 합리적인 이해. 충돌의 예-저 유형의 정보는 그것 Informix 또는 d b 2를 사용 하려면 필드 이름을 18 문자 보다 긴 만들 수 없습니다 알려줍니다.

충돌-ME 프로그램 및 MySQL 벤치 마크는 각 유사 데이터베이스에 대 한 구현 됩니다. 이러한 벤치 마크 프로그램은 작성 하는 방법을 읽고, 아마 당신은 프로그램의 다양 한 데이터베이스 아이디어 독립 할 수 있는 할 수 있습니다. 이 프로그램은 MySQL 소스 코드 ' sql-벤치 ' 디렉터리에서 찾을 수 있습니다. 그들의 대부분은 펄으로 작성 하 고 DBI 인터페이스를 사용 하 여. 다양 한 액세스 방법 데이터베이스의 독립을 제공 하기 때문에 DBI 다양 한 포팅 문제를 해결 하는 데 사용 됩니다.

데이터베이스의 독립적인 노력 하려는 경우이 다양 한 SQL 서버에 병목에 대 한 몇 가지 좋은 아이디어를 요구 한다. 예를 들어 MySQL MyISAM 유형의 검색 하 고 레코드를 업데이트할 때 테이블에 대 한 매우 빠른 이지만 동시에 문제가 읽기 속도 기록 작성. 오라클로 서 그것은 큰 문제가 방금 업데이트 된 레코드를 액세스 (결과 플러시됩니다 때까지 디스크에). 일반적으로 요약 테이블을 생성 하는 트랜잭션 데이터베이스 로그 테이블에서 이건 좋은 성능 때문에 경우에, 로그인 잠금을 거의 쓸모 없는.

응용 프로그램 데이터베이스의 진정한 독립을 위해서, 간단 하 고 확장 가능한 데이터를 조작 하기 위한 인터페이스를 정의 합니다. 때문에 C + +은 여러 시스템에서 사용할 수, 그것은 C를 사용 하 여 적절 한 + +는 데이터베이스에 대 한 기본 클래스.

일부 데이터베이스에 고유한 특정 기능을 사용 하는 경우 (예를 들어 바꾸기 문을 MySQL에만 고유 이다), 다른 데이터베이스에서이 기능을 구현 하는 대안을 작성 해야. 이러한 해결 방법 느려질 수 있습니다, 하지만 그들은 같은 기능을 달성 하기 위해 다른 데이터베이스 사용.

MySQL에서 사용할 수 있습니다 / *! 쿼리 문에서 * * mysql 관련 키워드를 추가 하는 구문입니다. 그러나 많은 다른 데이터베이스에서, / * * / 주석으로 처리 됩니다 (그리고 무시 됩니다).

높은 성능 데이터를 일부 웹 응용 프로그램의 정확성 보다 더 중요 한 시간 경우이 높은 성능을 가질 수 있는 결과 캐시 하도록 응용 프로그램 계층을 사용할 수 있습니다. 오래 된 데이터는 시간이 일정 금액 후 만료 시켜 캐시를 적절 하 게 업데이트 합니다. 이 피크 로드 시간을 처리 하는 방법, 어떤 경우 당신이 부하까지 캐시 용량 및 만료 시간을 늘릴 수 있는 정상이 된다.

이 경우에, 테이블 정보 캐시 및 일반적으로 데이터 시트를 새로 고침 하는 능력을 포함 해야 합니다. 응용 프로그램 계층 캐시를 구현 하기 위한 하나의 옵션 MySQL 쿼리 캐시를 사용 하는 것입니다. 쿼리 캐시를 사용 하면 데이터베이스는 결과 몇 가지 세부 사항에 따라 다시 사용할 수 있습니다 결정 합니다. 그것은 크게는 응용 프로그램을 단순화합니다.

1.3 우리가 mysql 무엇을 해야 합니까?

MySQL은 원래 주요 고객에 대 한 MySQL의 첫 번째 개발, 그리고 그들은 화물 저장 데이터 관리를 위한 스웨덴에 2 가장 큰 소매 업체.

우리가 결과 얻을 누적 거래 수익의 모든 매장에서 매주, 저장소 소유자에 게 분석 하 고 만약 그들이 그들의 고객에 영향을 광고에 더 나은 그들을 돕기 위해 유용한 정보를 제공.

데이터의 양이 매우 큰 (한달에 약 7 백만의 누적 결과), 그리고 그것은 또한 4-10 년에 대 한 데이터를 표시. 우리 고객의 요구 매주 고 그들은 '즉시' 데이터에 관한 최신 보고서를 부탁 드립니다.

우리는 문제를 해결 하기 위해 압축된 '거래' 테이블에서 각 달에 대 한 모든 정보를 저장 합니다. 우리는 매크로 필드 그룹 (제품 그룹, 고객 Id, 상점, 및 등)에 따라 저장된 '거래' 테이블에서 결과 얻으려면 다른 기준에 따라 수 있는 몇 가지 간단한 설정. 작은 펄 스크립트를 사용 하 여 웹 페이지의 폼에서 보고서를 동적으로 생성. 이 스크립트는 웹 페이지를 구문 분석, SQL 문을 실행 하 고 결과 삽입 합니다. 우리 또한 일, PHP 또는 Mod_perl를 사용할 수 있습니다 하지만 우리는 이러한 2 도구를가지고 하지 않습니다.

그래픽 데이터를 얻으려면, 우리는 또한 SQL 쿼리를 실행 하 고 결과 GIF 이미지로 만들기 위한 간단한 C 언어 도구를 썼다. 이 도구는 또한 동적으로 Perl 스크립트는 웹 페이지를 구문 분석 한 후 실행 됩니다.

많은 경우에, 단순히 SQL 쿼리 문을 수정할 기존 스크립트를 복사 하 여 새 보고서를 생성할 수 있습니다. 때로는 기존 누적 테이블에 더 많은 필드를 추가 하거나 새로운 하나를 만들 해야 합니다. 이 때문에 우리는 모두 우리의 거래 테이블의 디스크 (약 50 G 거래의 총) 및 다른 고객 정보 20 G에는 매우 간단한 작업입니다.

또한 고급 사용자가 그 데이터를 사용 하 여 그들의 자신의 실험을 할 수 있도록 고객 ODBC를 통해 누적 테이블에 직접 액세스를 허용. 시스템이 잘 작동 하 고 겸손 한 썬의 울트라 스팍 워크스테이션 (2x200mhz)에 문제 처리 데이터가 없습니다. 결국 시스템은 리눅스로 포팅 했다.

1.4 MySQL 벤치 마크 스위트

벤치 마크 스위트 어떤 SQL 쿼리를 더 잘 수행 하는 사용자 또는 악화 하는 방법입니다. 참고가이 데이텀 이므로 단일 스레드 작업이 수행 되는 최소 시간을 측정 합니다. 우리는 나중에 다중 스레드 테스트 벤치 마크 스위트를 추가할 것입니다.

벤치 마크 스위트를 사용 하려면 다음 조건이 필요:

벤치 마크 스크립트 펄, 펄의 DBI 모듈을 사용 하 여 데이터베이스에 연결 하는, 그래서 DBI 모듈을 설치 해야 합니다에 기록 됩니다. 당신은 또한 테스트 하려는 각 서버에 대 한 특정 BDB 드라이버가 필요 합니다. 예를 들어 테스트 하려면 MySQL, PostgreSQL, DB2, Dbd::mysql, DBD::P G, DBD::D B2 모듈을 설치 해야 합니다. "2.7 Perl 설치 안내를" 자세한 내용은 참조 하십시오.

MySQL 배포에 대 한 소스 코드를 얻을 후 ' Sql-벤치 ' 디렉토리에 벤치 마크 스위트를 볼 수 있습니다. 이러한 벤치 마크, 첫 번째 설정 서비스를 실행 하려면 다음 ' sql-벤치 ' 디렉토리로 이동 하 고 실행-모든-테스트 스크립트 실행:

셸 > CD Sql 벤치

셸 > Perl 실행-모든-테스트-server = server_name

server_name 사용 가능한 서비스 중 하나를 수 있습니다. 사용 가능한 모든 옵션 및 지원된 서비스를 나열 하려면 단순히 다음 명령을 호출:

셸 > Perl 실행-모든-테스트-도움말

충돌-나 스크립트 또한 ' Sql-벤치 ' 디렉터리에 배치 됩니다. 충돌-데이터베이스를 지 원하는 어떤 기능, 성능, 그리고 한계를 결정 하려고 하는 실제 쿼리를 실행 하 여 날. 예, 그것은 재판 될 수 있다:

• 어떤 필드 유형이 지원 됩니다

• 얼마나 많은 인덱스는 지원

• 어떤 기능 지원

• 얼마나 큰 쿼리 지원 될 수 있습니다

· 얼마나 큰 VARCHAR 필드 형식을 지원할 수 있습니다.

1.5 직접 벤치 마크를 사용

데이터베이스 또는 응용 프로그램 병목 현상을 발견을 벤치 마크 해야 합니다. 후이 병목을 해결 (또는 대신 가짜 모듈을 사용 하 여), 그것은 쉽게 다음 병목을 찾을 수. 응용 프로그램의 현재 성능을 허용 하더라도, 적어도 각 병목을 찾을 계획 이며 언젠가 응용 프로그램 성능이 향상을 원할 거 야.

MySQL의 벤치 마크 스위트에서 이식 될 수 있는 휴대용 벤치 마크 프로그램을 찾을 수 있습니다. 보십시오 "7.1.4 MySQL 벤치 마크 스위트" 자세한 내용은. 벤치 마크 제품군에 어떤 프로그램에서 필요에 맞게 적절 한 수정을 할 수 있습니다. 모든 방법을 통해 다양 한 문제를 해결 하기 위해 가장 빠른 어떤 프로그램 인지 다른 방법으로 할 수 있습니다.

문제는 일반적으로 시스템 오버 로드 되 면 발생 합니다. 우리는 많은 고객의 연락처 우리 말 (테스트) 생산 시스템을가지고 그들은 또한 고통이 부하 문제. 대부분의 경우에서 성능 문제는 데이터베이스 (예를 들어 높은 부하에서 데이터 테이블을 스캔의 가난한 성능), 운영 체제 또는 프로그램 라이브러리의 기본 설계. 종종, 이러한 문제는 비교적 쉽게 해결 하기 전에 그들은 공식적으로 생산을 위해 사용 됩니다.

2. SELECT 문 및 다른 쿼리 최적화

첫째, 하나의 요소에 영향을 미치는 모든 문의 더 복잡 한 사용 권한 설정, 큰 오버 헤드. 단순한 GRANT 문을 사용 하 여 MySQL을 권한 클라이언트 문을 실행할 때 검사의 오버 헤드를 줄이기 위해 수 있습니다. 예를 들어 테이블 수준 또는 필드 수준 사용 권한을 설정 하지 서버 Tables_priv 및 Columns_priv 테이블의 레코드를 확인 필요 하지 않습니다. 마찬가지로, 리소스 제한이 계정에 설정 된 경우 다음 서버는 없습니다 리소스 사용 통계를 할. 쿼리 수가 많은 경우는 간단한 인증 메커니즘 서버 권한 확인의 오버 헤드를 줄이기 위해 계획 시간을 보내고 보람 있다.

초과 하 벤치 마크 () 함수를 사용할 수 있습니다 일부 MySQL 전용 식 또는 기능에 문제가 있는 경우 MySQL 클라이언트 프로그램을 사용 하 여 테스트. 그 구문은: 벤치 마크 (loop_count, 식). 예를 들어:

모든 MySQL 함수를 최적화 해야 합니다, 하지만 여전히 몇 가지 예외는 함수에. (벤치 마크)는 쿼리 문에서 문제에 대 한 검사를 위한 아주 좋은 도구입니다.