Mysql-clustered index 느린 정렬 사례 분석

선택을 많이 할 때 mysiam 엔진을 사용해야 하는 이유는 무엇인가요? 특히 인덱스가 있는 경우

이 글은 실제 적용에 의존하여 분석합니다.

1. 서문:

온라인에서 흥미로운 현상을 봤습니다. 1W의 데이터 볼륨을 갖는 테이블이 다른 orderby 조건과 쿼리 시간을 실행합니다. 실제 적용에서 실제 문제는 무엇입니까? ? 왜?

2. 분석

a) 상황설명 :

1. ); orderby 쿼리에 전자를 사용하면 속도가 느리지만, orderby 쿼리에 후자를 사용하면 매우 빠릅니다.

2. 각 행의 데이터 양이 상당히 많습니다.

3. 가 기본 인덱스이고, 선택 쿼리를 위한 필드도 ID만 있으면 인덱스가 이를 덮고 있으므로 데이터를 검색하기 위해 물리적 디스크로 이동할 필요가 없습니다. 하지만 더 빨라야 하는 쿼리가 더 느립니다. Mysql-index 적용 범위

b) 분석:

은 mysiam 엔진을 사용해서는 안 됩니다. 그렇다면 이 두 인덱스를 사용하는 쿼리 속도는 거의 같습니다. , 인덱스에 저장되는 것은 물리적인 행의 주소이고, 실제 차지하는 데이터의 양은 크지 않기 때문이다. 그러나 innodb라면 다릅니다. 행의 모든 ​​데이터는 기본 인덱스 아래에 저장됩니다.

c). 결론:

1. 주된 이유:

을 사용하는 innodb 엔진은 클러스터형 인덱스이며, 기본 키 ID 인덱스도 해당 행에 연결되어 있습니다. 다른 데이터, 따라서 ID를 따라 정렬할 때 각 ID를 쿼리하고 순회하려면 많은 작은 블록을 교차해야 합니다. (mysiam에는 데이터가 많지 않으므로 동일한 데이터 블록을 교차하고 더 많은 행을 순회하는 것이 더 빠릅니다.) 🎜>

2. 이유: 여러 분야의 데이터 양이 상대적으로 많습니다. 즉, 가족을 데리고 오는 사람들이 많고, 데이터의 양이 상대적으로 많습니다. 각 행의 데이터 양이 많고 디스크에 저장할 때 많은 블록을 차지합니다

3. 당시 mysiam 엔진에는 이 문제가 없었습니다

d). 결론:

select가 많이 실행되는 경우에는 mysiam 엔진을 사용해야 합니다.

insert 및 업데이트가 많이 실행되는 경우에는 innodb 엔진을 사용해야 합니다.

더 많은 결론은 다음을 참조하세요: Mysql-Index Summary

3. 시뮬레이션 테스트

위에서 언급한 조건을 복원하고 두 개의 테이블을 생성하고 다른 엔진을 제외하고는 그 외 조건은 동일, 기본키 ID 기본 인덱스, 조인트 인덱스(id, ver).

1. 새로운 테이블 t7 생성, mysiam 엔진

2. 데이터 10,000개를 무작위로 삽입

3. 쿼리문을 실행하여 시간을 확인합니다

시차는 그리 크지 않고 모두 같은 크기입니다.

4. 새로운 테이블 t8 생성, innodb 엔진

5. 데이터 10,000개를 무작위로 삽입

짧게 말하면 위 스크립트에 따라 명령문을 실행하면 대기 시간이 매우 길어지는 이유는 무엇입니까? 기본키 인덱스 ID를 갖는 클러스터형 인덱스이기 때문에 기본키 인덱스 생성 시 많은 양의 행 데이터 블록이 이동하게 되며, 분할 및 이동하는 시간이 발생하게 됩니다.

기본키 인덱스 ID를 먼저 삭제하고 데이터를 삽입한 후 기본키(id)를 추가한 후 기본키 인덱스 구조를 생성하는 작업입니다

6. 쿼리문 실행, 시간 확인

확실히 시간차이가 많이 나네요.

이유: 두 명령문 모두 클러스터형 인덱스를 사용하지만 기본 키가 너무 많은 블록에 걸쳐 있는 반면, 결합 인덱스는 아래에 데이터가 없고 블록 수가 적으며 탐색이 빠른 보조 인덱스입니다.

7. 종합적으로 분석하면 t8 테이블(innodb)만 기본 키 인덱스에 따라 정렬하는 데 시간이 더 걸리고 나머지는 괜찮습니다.

시간 정렬 결론: innodb.innodb.secondary index> mysiam

효율이 거의 27배나 더 나쁩니다.

1. 주요 이유는 기본 키를 따라 정렬하여 쿼리가 페이지 전체에 걸쳐 여러 블록에 걸쳐 발생하므로 시간이 늘어납니다.

2. 긴 char 필드의 경우 데이터 블록이 크지 않으며 그렇게 큰 차이가 발생하지 않습니다.

예를 들어 테이블에서 str1, str2, str3 필드를 삭제하면 쿼리 시간은 크게 줄어들고 차이가 눈에 띄지 않을 것입니다

위 내용은 Mysql-clustered index 느린 정렬 사례 분석 내용입니다. PHP 중국어 홈페이지(www.php.cn)!