큰 데이터 페이징에 대 한 MySQL도 최적화 방법

MySQL 최적화는 매우 중요 하다. 가장 일반적이 고 가장 필요한 최적화는 제한 이다. MySQL의 페이징, 매우 편리 하지만 제한의 성능을 극적으로 감소 데이터의 양이 큰 경우.

또한 10 데이터를 받아

선택 * Yanxue8_visit에서 10000,10를 제한 하 고

선택 * Yanxue8_visit에서 0, 10 제한

양적 수준이 아니다.

또한 비록 불가능 하지만 올바른 MySQL 설명서에서 번역 된다 제한, 5 최적화 지침 많이 있다. 나도 최적화에 대해 작성 하는 기사를 발견 하는 오늘 아주 좋은.

도 직접 사용 하는 대신 우리는 먼저 오프셋의 ID를 얻을 하 고 크기 제한을 사용 하 여 데이터를 직접. 직접도 사용 하 여 보다 분명히 더 나은 자신의 데이터에 따르면. 여기 내가 테스트를 두 개의 서로 다른 상황에서 데이터 사용 합니다. (테스트 환경 WIN2033 + P4 듀얼 코어 (3 g h Z) + 4 g 메모리 Mysql 5.0.19)

1, 상대적으로 작은 시간 오프셋.

선택 * Yanxue8_visit에서 10,10 제한

0.0004 0.0005 사이 시간을 유지를 여러 번 실행 http://www.zhutiai.com

선택 * Yanxue8_visit 어디 vid에서 > (=

Vid 제한 10, 1 Yanxue8_visit 순서에서 vid를 선택

)도 10

0.0005 0.0006, 사이 시간을 주로 0.0006 유지를 여러 번 실행

결론: 제한의 직접 사용 때 더 우수한 오프셋 작습니다. 이것은 분명히 하위에 대 한 이유입니다.

2. 오프셋은 큰.

선택 * Yanxue8_visit에서 10000,10 제한

이상의 0.0187 번 실행

선택 * Yanxue8_visit 어디 vid에서 > (=

Vid 제한 10000,1 Yanxue8_visit 순서에서 vid를 선택

)도

10

여러 번 실행 시간 0.0061만 전 1/3의 주위에 남아 있었다. 후자는 큰 오프셋, 더 나은 기대할 수 있습니다.

나중에 주의를 해야 그들의 제한 문 수정, MySQL을 최적화

테이블 수집 (ID, 제목, 정보, VType)의이 4 개의 분야에 따라 어떤 타이틀을 고정 길이 텍스트, ID 정보

점차적으로, VType는 Tinyint, vtype 인덱스입니다. 기본 뉴스 시스템의 간단한 모델입니다. 이제는 데이터와 뉴스의 100000 조각을 작성.

마지막으로 100000 레코드를 수집, 데이터베이스 튜토리얼 테이블 차지 1.6 G 하드 드라이브. 좋아, 다음 SQL 문을 보면:

Id, 수집 제한 1000, 10;에서 제목 선택 매우 빨리; 기본적으로 0.01 초는 확인, 다음에 봐

Id, 수집 제한 90000, 10;에서 제목 선택 90000, 결과에서 페이징 시작?

8-9 초가, 세상에 무슨 일 이에요??? 사실,이 데이터를 대답을 찾으려고 인터넷을 최적화. 다음 문을 보십시오.

ID 제한 90000, 10; 수집 순서에서 ID를 선택 곧, 0.04 초 괜찮습니다.

왜?는 ID 기본 키 인덱스를 사용 하 여 하는 것은 확실히 빠른 때문에. 온라인 수정이입니다.

Id, 수집에서 제목 선택 어디 id > = (선택 ID 수집 순서에서에서 ID로

90000,1 제한) 제한 10;

이것은 ID와 인덱싱의 결과 하지만 문제는 너무 복잡 하 게 그것은 끝났습니다. 다음 문을 보고합니다

수집에서 ID를 선택 어디 vtype = 1 순서 ID 제한 90000, 10;

그것은 천천히, 8-9 초!

여기 많은 사람들이 나 처럼 될 것입니다, 붕괴의 감각을 믿고! vtype 지 아 나요? 어떻게 느린 될 수? VType 않았다 인덱스 확인에서 ID를 선택 하면 직접

어디를 수집 vtype = 1도 1000, 10;

매우 빠르고, 기본적으로 인상 90 시간, 하지만 0.05 초 속도 두 번째는 0.05 * 90 = 4.5 90000에서 시작 이다. 그리고 테스트 결과 크기 순서에 8-9 초. 이 시점에서 어떤 사람들 넣어 앞으로 discuz의 아이디어

포럼은 같은 생각입니다. 아이디어는 다음과 같습니다.

인덱스 테이블을 구축: T (id, 제목, vtype)로 설정 하 고 고정 길이, 하 고 다음 페이지 매김, 결과 페이징 그리고 내부 수집 하는 정보를 찾을 수.

실현 가능한가?

T (Id, 제목, vtype), 약 20 M의 데이터 시트 크기를 100000 레코드. 와 함께

T에서 ID를 선택 어디 vtype = 1 순서 ID 제한 90000, 10;

조만간. 기본적으로 0.1-0.2 초 밖으로 실행할 수 있습니다. 이것은 왜? 난 그것 때문에 너무 많은 데이터를 수집, 그래서 페이징 갈 길이. 제한

완전히 데이터 시트의 크기와 관련이 있습니다. 사실, 이것은 전체 테이블 스캔, 데이터 볼륨이 작습니다, 있기 때문에 그냥 빨리만 100000. 좋아, 미친 실험, 1 백만, 테스트 성능에 추가.

즉시 t 200 m 이상의 표 고는 고정 길이 데이터를 10 번 배를 추가. 아니면 그냥 쿼리 문, 시간 0.1-0.2 초 완료! 하위 테이블 성능 문제? 우리의 한계는 여전히 90000, 때문에 너무 빨리. 큰 한 게 900000 시작

T에서 ID를 선택 어디 vtype = 1 순서 ID 제한 900000, 10; 보고 결과, 시간은 1-2 초!

왜? 미터 시간은 아직도 너무 오래, 아주 우울! 누군가가 동의 긴 제한의 성능을 향상 시킬 것입니다, 나 또한 생각, 레코드 길이 고정 하기 때문에 MySQL 자습서

수 900, 000의 위치 파악은 바로 아? 하지만 우리는 MySQL의 지혜를과 대 평가, 그는 비즈니스 데이터베이스, 고정 길이 및 제한에 영향을 작은 사실? 당연히 누군가가 말했다

1 백만 레코드 Discuz는 매우 느린, 난이 사실이 생각 될 것입니다, 이것은 관련 데이터베이스 디자인!

Mysql 1 백만 깰 수 없습니다??? 페이지의 1 백만에서 한계에 정말은???

대답: 아니오!

왜 휴식 1 백만 때문에 MySQL은 되도록 설계 되지 않았습니다. 여기 미친 테스트를 수행 하는 방법의 목록이 있다! 1 백만 레코드와 10 G 테이블

데이터베이스, 페이지 매김 신속 하 게 하는 방법!

글쎄, 우리의 테스트 수집 테이블에 돌아갔다 고 결론을 테스트 하기 시작 하는:

300000 데이터 테이블 메서드를 사용 하 여 가능, 300000 이상 그의 속도 느린 선 참을 수 없습니다!, 테이블 + 나이 메서드를 사용 하는 경우에, 그것은 절대적으로 완벽 한. 하지만 사용 후 테이블 없이 나의 방법 또한 완벽 한 해결책이 될 수 있습니다!

대답은: 종합 지! 일단 MySQL 인덱스 디자인 실수로 인덱스 이름을 취할 수 있습니다, 필드 선택할 수 있습니다 몇 가지에, 그것의 사용은 무엇?에서 선택 ID 시작

ID 제한 90000, 10; 순서를 수집 너무 빨리 where 인덱스를 이동 하지 것입니다 하지만 추가 하면 인덱스 때문입니다. 와 아이디어.

색인 검색 (Vtype, id) 등. 다음 테스트

수집에서 ID를 선택 어디 vtype = 1 제한 90000, 10; 매우 빠른! 0.04 초!

다시 테스트: ID, 수집에서 제목 선택 어디 vtype = 1 제한 90000, 10;

죄송 합니다, 8-9 초, 가지 않았다 검색 인덱스 매우!

다시 테스트: 검색 (Id, vtype), 또는 선택 ID이 성명, 또한 매우 유감, 0.5 초.

요약에서: 경우에 where 조건, 고 지가 원하는 제한, 인덱스를 디자인 해야 합니다 어디

먼저, 사용 기본 키 2, 넣고만 기본 키를 선택할 수 있습니다!

페이징 문제를 완벽 하 게 해결. 신속 하 게 반환할 수 있다 ID 제한, 최적화를 희망 같은 논리, 수백만 따라 제한 0.0 x 초를 완료할 수 있어야. MySQL 처럼 보이는

문 최적화 및 인덱싱 하는 것은 매우 중요!

음, 다시 원래의 질문에 신속 하 게 위의 연구 개발에 적용 하는 방법? 내 간단한 프레임 워크는 쓸모가 복합 쿼리를 사용 하는 경우. 페이지 매김 문자열, 직접 쓸 필요가 얼마나 많은 문제가? 여기에 또 다른 예를 들어, 아이디어 밖으로 나오는:

선택 * 수집에서 (9000,12,50,7000);에 있는 ID 0 초 보세요!

Mygod, MySQL 인덱스는 또한에 문! 인터넷에서 말을 사용할 수 없습니다 보인다 인덱스는 잘못!

이 결론은 경량 프레임 워크에 적용할 쉽다:

코드는 다음과 같습니다.

코드는 다음과 같습니다.

$db = dblink ();

$db-> pagesize = 20;

$sql = "수집에서 선택 ID 어디 vtype $vtype =";

$db-> 실행 ($sql);

$strpage = $db-> strpage ();

페이징 문자열 쉽게 출력을 임시 변수에 저장

동안 ($rs = $db-> fetch_array ()) 없음

$strid. $rs = [' id '].

}

$strid = substr ($strid, 0, strlen ($strid)-1);

ID 문자열을 생성

$db-> pagesize = 0;

그것은 중요 한, 로그 오프 클래스, 하지 않고 페이징 것입니다 비운, 하나의 데이터베이스 연결이 필요 하 고, 하 고 아니 더 여는 것은 필요한;

$db-> 실행 ("선택

Id, 제목, url, 에러, gtime, vtype, 수집에서 태그 ($strid)에 있는 ID);

Fetch_array ()):? >

target = "_blank" >

$rs [' 태그 '];? >

? >

에코 $strpage;

? >

간단한 변환을 통해는 아이디어는 간단 하다: 1 인덱스를 최적화 하 여 ID를 찾아서 "123,90000,12000" 같은 문자열을 철자. 2 2 쿼리 결과를.

작은 인덱스 + 약간의 변화는 MySQL 수백만 또는 심지어 수만 효율적인 지원할 수 페이징!

이 예제에서 나는 포인트에 반영: 대형 시스템에 대 한 PHP는 프레임 워크를 사용 하지 않아야 합니다, 특히 하나 SQL 문을 프레임 워크를 볼 수 없습니다! 그것은 거의 내 간단한 프레임 워크에 추락 하기 때문에! 작은 응용 프로그램의 빠른 개발을 위한 Erp, oa, 대형 웹 사이트에만 논리적 계층을 포함 하 여 데이터 계층에 프레임 수 있습니다. 프로젝트의 위험 기 하 급수적으로 증가 합니다 프로그래머는 SQL 문의 제어를 잃으면! MySQL와 특히.

MySQL 전문 DBA의 최상의 성능을 수행 하 여야 합니다. 인덱스에 의해 발생 하는 성능 차이 천 수천 수 있습니다!

성능 최적화:

MySQL5.0에도 고성능을 바탕으로, 저는 데이터 페이징에 대 한 새로운 이해.

1입니다.

선택 * Cyclopedia 어디 id에서 > (=

(에서 최대 (ID)을 선택

ID 제한 90001 Cyclopedia 순서에서 ID를 선택

) TMP로

) 제한 100;

2입니다.

선택 * Cyclopedia 어디 id에서 > (=

(에서 최대 (ID)을 선택

ID 제한 90000,1 Cyclopedia 순서에서 ID를 선택

) TMP로

) 제한 100;

또한 100 레코드, 1 문장 빠른 또는 2 문장 빠른 후 90000 걸릴?

1 문장 처음 90,001 레코드 시작 ID로 가장 큰 ID 값 중 하나를 수행 하 고 다음 신속 하 게 다음 레코드를 찾을 그것을 사용 하 여 하는 것입니다.

2 옵션은 하만 90000 레코드 1, 후 다음 다음 레코드를 찾을 시작 표시로 ID 값을가지고

1 문장 실행 결과 행 집합 (0.23) 초

2 문장 실행 결과 행 집합 (0.19) 초

그것은 분명 그 2 문장 승. 보이는 아니 정확 하 게 내가 생각 하는 것 처럼. 전체 테이블 스캔 반환 제한 오프셋 + 길이 레코드는 Ms sql의 최고 성능에 비해 제한 개선 것.

사실, 2 문장으로 단순화 될 수 있습니다.

선택 * Cyclopedia 어디 id에서 > (=

Cyclopedia 제한 90000,1에서 ID를 선택

) 제한 100;

최대 작업 없이 직접 번호 90000 레코드의 ID를 사용 하 여, 이것은 이론적으로 더 효율적인, 하지만 실제로 그것 거의 보이지 않습니다, 1 레코드를 반환 하는 위치 ID 그리고 최대 결과 얻으려면 일 필요는 없습니다 하지만 이것은 명확 하 고 명확 하 게, 뱀 그림의 발을 제거 하기 때문에.

그러나, MySQL도 직접 제어 레코드의 위치를 수 있기 때문에, 왜 단순히 사용 하지 선택 * Cyclopedia에서 90000,1 제한?

이것은 잘못 알고 하려고, 결과: 1 행 집합 (8.88) 초, 얼마나 무서운 생각나 4.1에서 어제 보다 이것은 "높은 점수." 선택 * 하지를 사용 하 여, 무엇을, 정신의 최고 원리를 선택, 더 많은 필드, 필드 데이터의 큰 금액, 느린 속도 선택 합니다. 위의 2 종류 페이징 방법의 쿼리 수보다 더 많은 처럼 보이지만 사실, 효율적인 성능에 대 한 대가로 작은 가격에 매우 보람은 비록 단 하나 보다는 더 많은이 1 문장 쓰기 이다.

1 시나리오 ms sql도 사용할 수 있습니다 그리고 수 있습니다 최고. 때문에 기본 키 ID는 항상 시작 찾을 가장 빠른 세그먼트.

상단 선택 * Cyclopedia 어디 id에서 > (=

(에서 최고 90001 최대 (ID)을 선택

Cyclopedia id 순서에서에서 ID를 선택

) TMP로

)

하지만 구현 저장소 프로세스 또는 직접 코드 인지 병목은 항상 ms sql 상단 항상 반환 합니다 첫 번째 N 레코드는 데이터의 양과 깊은 수행에 많은 이점이 있다 수백만, 효율성 있을 것입니다 확실히 낮은. MySQL의 제한 달리, 경우:

Cyclopedia 제한 90000에서 ID를 선택

Cyclopedia 제한 90000,1에서 ID를 선택

결과:

90000 행 집합 (0.36) 초에

1 행 집합 (0.06) 초

Ms sql만 사용 선택 최고 90000 ID Cyclopedia 실행 시간에서 390ms 수 있습니다, 그리고 같은 수행 작업 시간 또한 MySQL 360ms 보다는 더 적은.