MySQL에서 조인 문을 최적화하는 방법

간단한 중첩 루프 조인

조인 작업을 수행할 때 mysql이 어떻게 작동하는지 살펴보겠습니다. 일반적인 조인 방법은 무엇입니까?

그림과 같이 조인 연산을 수행하면 왼쪽 테이블이 드라이버 테이블, 오른쪽 테이블이 구동 테이블

Simple Nested-Loop Join This 조인 작업은 드라이버 테이블에서 수행됩니다. 그런 다음 레코드가 구동 테이블의 레코드와 하나씩 일치되고 조건이 일치하면 결과가 반환됩니다. 그런 다음 드라이버 테이블의 모든 데이터가 일치할 때까지 드라이버 테이블의 다음 레코드를 계속 일치시킵니다.

매번 드라이버 테이블에서 데이터를 가져오는 데 시간이 많이 걸리기 때문에 MySQL은 이 알고리즘을 사용하여 수행하지 않습니다. 연결 작업

Block Nested-Loop Join

매번 드라이버 테이블에서 데이터를 가져오는 시간 소모적인 프로세스를 피하기 위해 드라이버 테이블에서 일괄 데이터를 한 번에 꺼내고 메모리에서 일치 작업을 수행합니다. 이 데이터 배치가 일치되면 드라이버 테이블에서 데이터 배치를 가져와서 드라이버 테이블의 모든 데이터가 일치할 때까지 메모리에 저장합니다. 일괄 데이터 가져오기는 많은 IO 작업을 줄일 수 있으므로 실행 효율성이 향상됩니다. 이 연결 작업은 MySQL에서도 채택되었습니다

그런데 이 메모리는 MySQ에서 조인 버퍼라는 고유한 이름을 가지고 있습니다. 다음 명령문을 실행하여 조인 버퍼의 크기를 확인할 수 있습니다

show variables like '%join_buffer%'

이전에 사용했던 Single_table 테이블을 꺼내고, Single_table 테이블을 기반으로 2개의 테이블을 생성하고, 각 테이블에 1w개의 무작위 레코드를 삽입합니다

CREATE TABLE single_table (
    id INT NOT NULL AUTO_INCREMENT,
    key1 VARCHAR(100),
    key2 INT,
    key3 VARCHAR(100),
    key_part1 VARCHAR(100),
    key_part2 VARCHAR(100),
    key_part3 VARCHAR(100),
    common_field VARCHAR(100),
    PRIMARY KEY (id),
    KEY idx_key1 (key1),
    UNIQUE KEY idx_key2 (key2),
    KEY idx_key3 (key3),
    KEY idx_key_part(key_part1, key_part2, key_part3)
) Engine=InnoDB CHARSET=utf8;

create table t1 like single_table;
create table t2 like single_table;

Join 문을 직접 사용하면 MySQL 옵티마이저가 테이블 t1 또는 t2를 구동 테이블로 선택할 수 있습니다. , 이는 sql 문을 분석하는 과정에 영향을 미칩니다. 그래서 우리는 Straight_join을 사용하여 mysql이 고정된 연결 방식을 사용하여 쿼리를 실행하도록 합니다

select * from t1 straight_join t2 on (t1.common_field = t2.common_field)

실행 시간은 0.035s

실행 계획은 다음과 같습니다

Extra 열에 조인 버퍼 사용이 표시되어 조인 연산이

Block Nested-Loop Join

알고리즘Index Nested-Loop Join

Block Nested-Loop Join

알고리즘을 이해한 후, 구동 테이블의 각 레코드가 구동 테이블의 모든 레코드와 일치하는 것을 볼 수 있습니다. 이는 매우 시간이 많이 걸립니다. 구동 테이블 매칭의 효율성을 향상시킬 수 있습니까? 그림과 같이 Driven 테이블 연결의 컬럼에 인덱스를 추가하여 매칭 과정을 매우 빠르게 하는 이 알고리즘도 생각해 보셨을 텐데요

살펴보겠습니다. 인덱스 컬럼을 기준으로 연결 실행 시 쿼리 속도는 얼마나 되나요?

select * from t1 straight_join t2 on (t1.id = t2.id)

실행 시간은 0.001초로 일반 컬럼 기준으로 조인하는 것보다 한 단계 이상 빠르다는 것을 알 수 있습니다.

실행 계획은 다음과 같습니다

드라이버 테이블 레코드는 조인 버퍼에 배치되며 쿼리 목록의 열과 필터 조건의 열만 조인 버퍼에 저장되므로 *를 쿼리 목록으로 사용하지 않고 쿼리 목록에서 관심 있는 열을 추가하여 조인 버퍼에 더 많은 레코드를 배치할 수 있습니다.

드라이버 테이블을 선택하는 방법은 무엇입니까?

이제 조인의 구체적인 구현을 알았으니, 일반적인 질문, 즉 드라이버 테이블을 선택하는 방법에 대해 이야기해 보겠습니다.

Block Nested-Loop Join 알고리즘인 경우:

조인 버퍼가 충분히 크면 누가 구동 테이블을 만드는지는 중요하지 않습니다.
조인 버퍼가 충분히 크지 않으면 작은 테이블을 구동 테이블로 선택해야 함(작은 테이블 데이터의 양이 적고, 조인 버퍼에 들어가는 횟수가 적으며, 테이블 스캔 횟수가 줄어듬)

그렇다면 는 Index Nested-Loop Join 알고리즘입니다

driving 테이블의 행 개수가 M이라고 가정하면, driving 테이블 M을 스캔해야 합니다. Row

drive 테이블에서 한 행의 데이터를 얻을 때마다 먼저 인덱스 a를 조회한 다음 기본 키 인덱스를 조회해야 합니다. 구동 테이블의 행 수는 N입니다. 매번 트리를 검색하는 대략적인 복잡도는 2N의 로그이므로 구동 테이블에서 행을 검색하는 시간 복잡도는 2∗ log 2 N 2*log2^N 2&log2N

구동 테이블의 각 행입니다. 전체 실행 과정의 대략적인 복잡도는 M + M ≥ log 2 N M + M*2*log2^N M+M

입니다. 스캔된 행 수에 더 큰 영향을 미치므로 작은 테이블을 드라이버 테이블로 사용해야 합니다. 물론 이 결론의 전제는 피동 테이블의 인덱스를 사용할 수 있다는 것이다

간단히 말하면, 작은 테이블을 구동 테이블로 만들 수 있습니다

조인 문이 느리게 실행될 때 다음 방법을 통해 최적화할 수 있습니다.

• 조인 작업을 수행할 때 구동 테이블은 인덱스를 사용하세요

• 작은 테이블을 드라이버 테이블로 사용

• 조인 버퍼의 크기를 늘리세요

• *를 쿼리 목록으로 사용하지 말고 필요한 열만 반환하세요

위 내용은 MySQL에서 조인 문을 최적화하는 방법의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!