5개의 mysql 최적화 제한 쿼리문 요약-MySQL 튜토리얼-php.cn

5개의 mysql 최적화 제한 쿼리문 요약

伊谢尔伦

풀어 주다： 2017-06-28 14:12:43

원래의

1568명이 탐색했습니다.

이 기사에서는 MySQL에서 Limitquery문을 최적화하는 5가지 방법을 주로 소개합니다. 하위 쿼리 최적화, 역 테이블 최적화, 제한 최적화 및 검색 전용 인덱스 방법이 필요한 친구들은

mysql을 참고하세요.

pagination 비교적 간단합니다. 데이터를 얻으려면 오프셋과 길이만 있으면 됩니다. 그러나 오프셋과 길이가 상대적으로 크면 mysql의 성능이 확실히 저하됩니다.

1. 먼저 첫 번째 조각을 찾습니다. 데이터이고 이 데이터보다 크거나 같은 ID가 얻어지는 데이터입니다단점: 데이터는 연속적이어야 하며 where 조건이 있을 수 없으며 where 조건이 데이터를 필터링하므로 다음과 같은 결과가 발생합니다. 데이터 연속성 손실, 특정 방법에 대한 다음 쿼리 예를 참조하십시오.

mysql> set profiling=1;
Query OK, 0 rows affected (0.00 sec)
mysql> select count(*) from Member;
+----------+
| count(*) |
+----------+
|   169566 | 
+----------+
1 row in set (0.00 sec)
mysql> pager grep !~-
PAGER set to &#39;grep !~-&#39;
mysql> select * from Member limit 10, 100;
100 rows in set (0.00 sec)
mysql> select * from Member where MemberID >= (select MemberID from Member limit 10,1) limit 100;
100 rows in set (0.00 sec)
mysql> select * from Member limit 1000, 100;
100 rows in set (0.01 sec)
mysql> select * from Member where MemberID >= (select MemberID from Member limit 1000,1) limit 100;
100 rows in set (0.00 sec)
mysql> select * from Member limit 100000, 100;
100 rows in set (0.10 sec)
mysql> select * from Member where MemberID >= (select MemberID from Member limit 100000,1) limit 100;
100 rows in set (0.02 sec)
mysql> nopager
PAGER set to stdout

mysql> show profiles\G
*************************** 1. row ***************************
Query_ID: 1
Duration: 0.00003300
   Query: select count(*) from Member
*************************** 2. row ***************************
Query_ID: 2
Duration: 0.00167000
   Query: select * from Member limit 10, 100
*************************** 3. row ***************************
Query_ID: 3
Duration: 0.00112400
   Query: select * from Member where MemberID >= (select MemberID from Member limit 10,1) limit 100
*************************** 4. row ***************************
Query_ID: 4
Duration: 0.00263200
   Query: select * from Member limit 1000, 100
*************************** 5. row ***************************
Query_ID: 5
Duration: 0.00134000
   Query: select * from Member where MemberID >= (select MemberID from Member limit 1000,1) limit 100
*************************** 6. row ***************************
Query_ID: 6
Duration: 0.09956700
   Query: select * from Member limit 100000, 100
*************************** 7. row ***************************
Query_ID: 7
Duration: 0.02447700
   Query: select * from Member where MemberID >= (select MemberID from Member limit 100000,1) limit 100

로그인 후 복사

결과에서 오프셋이 1000

이상인 경우 하위 쿼리
방법을 사용하면 성능을 효과적으로 향상시킬 수 있음을 알 수 있습니다.

2. 역테이블 최적화 방법

역테이블 방법은 인덱스를 생성하고 테이블을 사용하여 페이지 수를 유지한 후 효율적인 연결을 통해 데이터를 얻는 것과 유사합니다.단점: 상황에만 적합합니다. 데이터의 개수가 고정되어 있어 데이터를 삭제할 수 없고

페이지 테이블 유지가 어렵다

역 테이블 소개: (역 인덱스는 특히 검색 엔진의 알고리즘 초석입니다)

역 테이블은 역 테이블을 의미합니다. 반전된 인덱스를 추가할 수 있는 메모리에 저장된 레코드입니다. 반전 목록은 미니 반전 인덱스입니다.

임시 역파일이란 파일 형태로 저장되며 역기록에 추가할 수 없는 역색인을 말합니다. 임시 반전 파일은 중간 크기의 반전 인덱스입니다.

최종 반전 파일이란 디스크에 파일 형태로 저장된 임시 반전 파일을 병합하여 얻은 반전 인덱스를 말합니다. 최종 반전 파일은 더 큰 반전 인덱스입니다.

역 인덱스는 추상적인 개념이며, 역 목록, 임시 역 파일, 최종 역 파일은 역 인덱스의 세 가지 다른 표현입니다.

3. 역방향 검색 최적화 방법

오프셋이 레코드 수의 절반을 초과하면 먼저 정렬을 사용하므로 오프셋이 반전됩니다.

단점: 순서 최적화가 더 번거롭고 인덱스를 추가되고 인덱스의 영향 데이터 수정 효율성 및 총 레코드 수를 알아야 하며 오프셋이 데이터의 절반보다 큽니다

제한 오프셋 알고리즘: 정방향 검색: (현재 페이지 - 1) * 페이지 길이

역검색: 전체 기록 - 현재 페이지* 페이지 길이

성능이 어떤지 실험하고 확인하세요

총 기록 수: 1,628,775

페이지당 기록 수: 40
총 페이지 수: 1,628,775 / 40 = 40720
중간 페이지 수: 40720 / 2 = 20360

21000페이지

정방향 조회 SQL:

SELECT * FROM `abc` WHERE `BatchID` = 123 LIMIT 839960, 40

로그인 후 복사

시간: 1.8696초

역방향 조회 SQL:

SELECT * FROM `abc` WHERE `BatchID` = 123 ORDER BY InputDate DESC LIMIT 788775, 40

로그인 후 복사

시간: 1.8336초

페이지 30000

앞으로 조회 SQL:

SELECT * FROM `abc` WHERE `BatchID` = 123 LIMIT 1199960, 40

로그인 후 복사

Time: 2.6493초

역방향 조회 sql:

SELECT * FROM `abc` WHERE `BatchID` = 123 ORDER BY InputDate DESC LIMIT 428775, 40

로그인 후 복사

Time: 1.0035초

역방향 조회 결과는 내림차순으로 표시되며, InputDate는 삽입 시간입니다. 기본 키 조인트 인덱스를 사용할 수도 있지만 불편합니다.

4. 제한 최적화 방법

제한 오프셋을 특정 숫자 이하로 제한합니다. . 이 숫자를 초과하면 데이터가 없다는 것을 의미합니다. Alibaba의 DBA가 이렇게 했습니다.

5. 인덱스 전용 방법

MySQL 제한의 작동 원리는 n개의 레코드를 먼저 읽은 다음 첫 번째 n개의 레코드를 삭제하는 것입니다. m개의 원하는 항목을 읽으므로 n이 클수록 성능이 저하됩니다.

최적화 전 SQL:

SELECT * FROM member ORDER BY last_active LIMIT 50,5

로그인 후 복사

최적화 후 SQL:

SELECT * FROM member INNER JOIN (SELECT member_id FROM member ORDER BY last_active LIMIT 50, 5) USING (member_id)

로그인 후 복사

차이점은 최적화 전 SQL에서는 인덱스를 먼저 읽고 그 다음 데이터를 읽은 다음 불필요한 행을 읽기 때문에 더 많은 I/O 낭비가 필요하다는 점입니다. 폐기되었습니다. 최적화된 SQL(서브쿼리) 읽기전용 인덱스(Cover index)로 충분하며, member_id를 통해 필요한 컬럼을 읽어온다.

위 내용은 5개의 mysql 최적화 제한 쿼리문 요약의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!