데이터베이스 해시 연결에 대한 자세한 설명(MySQL의 새로운 기능)-MySQL 튜토리얼-php.cn

개요

오랫동안 MySQL이 조인을 수행하는 데 사용하는 유일한 알고리즘은 중첩 루프 알고리즘의 변형이었지만 중첩 루프 알고리즘은 일부 시나리오에서 매우 비효율적이었습니다. MySQL이 비판을 받아온 이유입니다.

MySQL 8.0.18이 출시되면서 MySQL 서버에서 해시 조인을 사용할 수 있게 되었습니다. 이 글에서는 해시 조인을 구현하는 방법과 이것이 MySQL에서 어떻게 작동하는지, 언제 사용하는지에 대해 간략하게 설명합니다.

추천 학습: MySQL 자습서

해시 조인 소개

해시 조인이란 무엇입니까?

해시 조인은 관계형 데이터베이스에 사용되는 조인 알고리즘이며 등호 조인 조건(a.b = c.b)이 있는 조인에만 사용할 수 있습니다. 이는 일반적으로 중첩 루프 알고리즘보다 더 효율적입니다(프로브 끝이 매우 작은 경우 제외). 특히 인덱스에 도달하지 않은 경우 더욱 그렇습니다.

간단히 말하면, 해시 조인 알고리즘은 먼저 작은 테이블을 메모리 해시 테이블에 로드한 다음 큰 테이블의 데이터를 순회하고 해시 테이블의 정규화된 데이터를 행별로 일치시켜 클라이언트에 반환하는 것입니다.

데이터베이스 해시 연결에 대한 자세한 설명(MySQL의 새로운 기능)

(해시 테이블은 예시일 뿐입니다. 이해하시면 실제 해시의 키는 연결의 값이고 값은 데이터 행 목록입니다.)

해시 연결은 일반적으로 두 단계로 나누어지며, 빌드 단계와 프로브 단계. 구축 단계에서는 먼저 "빌드 입력"으로 적절한 테이블을 선택하고 해시 테이블을 구축한 다음 다른 "탐지 입력" 테이블의 레코드를 순회하여 해시 테이블을 탐지하여 연결 조건을 충족하는 레코드를 찾습니다.

도시에 해당하는 지방을 쿼리하려면 위의 사진을 예로 들어보세요. 우리는 도시가 건설 입력이라고 가정합니다. 건설 단계에서 서버는 도시 해시 테이블을 구축하고 도시 테이블을 순회하며 해시 테이블에 행을 순서대로 넣습니다. 키는 hash(province_id)이고 값은입니다. 해당 도시 행. `

프로브 단계 동안 서버는 프로브 입력(지방)에서 라인 읽기를 시작합니다. 각 행에 대해 해시 테이블은 조회 키로 hash(province.province_id) 값을 사용하여 일치하는 행을 검색합니다.

즉, 모든 구성 입력을 메모리에 로드할 수 있을 때 각 감지 선은 한 번만 스캔되고 상수 시간 검색을 사용하여 두 입력 사이에 일치하는 선을 찾을 수 있습니다.

메모리에 넣을 수 없는 데이터가 너무 많으면 어떻게 해야 하나요?

모든 빌드 입력을 메모리에 로드하는 것이 의심할 여지 없이 가장 효율적이지만, 어떤 경우에는 테이블 전체를 메모리에 로드하기에는 메모리가 부족하므로 일괄 처리해야 합니다.

두 가지 일반적인 방법이 있습니다.

일괄 처리를 위해 메모리에 로드

1. 최대 메모리가 수용할 수 있는 레코드를 읽고, 해시 테이블을 만들고, 입력을 구성하고, 해시 테이블을 생성합니다.

2. 트래버스 감지 해시 테이블의 이 부분에 대한 전체 탐색을 입력합니다.

3. 해시 테이블을 정리하고 모든 처리가 완료될 때까지 프로세스를 반복합니다.

이 방법을 사용하면 전체 감지 입력 테이블이 여러 번 스캔됩니다.

파일 처리에 쓰기

1. 해시 테이블 구축 단계에서 메모리가 부족해지면 서버는 계산 후 남은 빌드 입력을 디스크의 여러 작은 파일에 씁니다. 메모리로 읽고 해시 테이블을 생성합니다(메모리에 로드하기에는 너무 커서 다시 분리해야 하는 파일 블록을 방지하기 위해).

2. 감지 단계에서 감지 라인이 빌드 라인과 일치할 수 있습니다. 입력이 디스크에 기록되므로 감지 입력도 디스크에 기록되어야 합니다.

3. 감지 단계가 완료된 후 블록 파일을 디스크에서 읽어 메모리 해시 테이블에 로드한 다음 응답 블록을 로드합니다. 감지 입력에서 파일을 읽고 일치하는 항목이 감지됩니다.

4. 처리 후 모든 처리가 완료될 때까지 다음 블록 파일 쌍으로 이동합니다.

MySQL의 해시 조인 구현

MySQL은 두 입력 중 더 작은 입력을 빌드 입력(바이트 단위로 계산)으로 선택합니다. 메모리가 충분하면 빌드 입력이 처리를 위해 메모리에 로드됩니다. 이 경우에는 파일에 쓰는 방법을 사용하십시오.

join_buffer_size 시스템 변수를 사용하여 해시 연결에 사용되는 메모리는 이 양을 초과할 수 없습니다. MySQL은 처리를 위해 파일을 사용합니다.

메모리가 Join_buffer_size를 초과하고 파일이 open_files_limit를 초과하면 실행이 실패할 수 있습니다.

다음 두 가지 솔루션을 사용할 수 있습니다.

● 해시 연결이 디스크로 오버플로되는 것을 방지하기 위해 Join_buffer_size를 늘립니다.

● open_files_limit를 늘립니다.

MySQL은 어떤 상황에서 해시 연결을 사용합니까?

MySQL 버전 8.0.18에서는 하나 이상의 동등 조인 조건을 사용하여 테이블을 조인하고 조인 조건에 사용할 수 있는 인덱스가 없는 경우 해시 조인이 사용됩니다. MySQL은 인덱스가 사용 가능한 경우 중첩 루프를 지원하기 위해 인덱스 조회를 사용하는 것을 선호합니다.

기본적으로 MySQL은 가능할 때마다 해시 조인을 사용하며 다음 두 가지 방법으로 활성화하거나 비활성화할 수 있습니다.

● 전역 또는 세션 변수 설정(hash_join = on 또는 hash_join = off)

SET optimizer_switch="hash_join=off";

로그인 후 복사

● 힌트 사용(HASH_JOIN) 또는 NO_HASH_JOIN).

다음 쿼리를 예로 사용합니다.

EXPLAIN FORMAT = tree
SELECT
  city.name AS city_name,
  province.name AS province_name
FROM
  city
  JOIN province
    ON city.province_id = province.province_id;

로그인 후 복사

출력은 다음과 같습니다.

| -> Inner hash join (city.province_id = province.province_id)  (cost=1333.82 rows=1329)
    -> Table scan on city  (cost=0.14 rows=391)
    -> Hash
        -> Table scan on province  (cost=3.65 rows=34)

로그인 후 복사

해시 조인은 여러 조인 쿼리에도 사용할 수 있습니다.

예를 들어 다음 쿼리는

EXPLAIN FORMAT= TREE
SELECT
  city.name AS city_name,
  province.name AS province_name,
  country.name AS country_name
FROM
  city
  JOIN province
    ON city.province_id = province.province_id
    AND city.id < 50
  JOIN country
    ON province.province_id = country.id

로그인 후 복사

출력은 다음과 같습니다.

| -> Inner hash join (city.province_id = country.id)  (cost=23.27 rows=2)
    -> Filter: (city.id < 50)  (cost=5.32 rows=5)
        -> Index range scan on city using PRIMARY  (cost=5.32 rows=49)
    -> Hash
        -> Inner hash join (province.province_id = country.id)  (cost=4.00 rows=3)
            -> Table scan on province  (cost=0.59 rows=34)
            -> Hash
                -> Table scan on country  (cost=0.35 rows=1)

로그인 후 복사

해시 연결은 "데카르트 곱"에도 적용 가능합니다. 즉, 다음과 같이 쿼리 조건이 지정되지 않습니다.

EXPLAIN FORMAT= TREE
SELECT
  *
FROM
  city
  JOIN province;

로그인 후 복사

출력은 다음과 같습니다.

| -> Inner hash join  (cost=1333.82 rows=13294)
    -> Table scan on city  (cost=1.17 rows=391)
    -> Hash
        -> Table scan on province  (cost=3.65 rows=34)

로그인 후 복사

MySQL 어떤 상황에서 해시 조인을 사용하는 방법을 모르시나요?

1. 현재 MySQL 해시 조인은 내부 조인만 지원하며, 반 조인 및 외부 조인은 여전히 블록 중첩 루프를 사용하여 실행됩니다.

2. 인덱스를 사용할 수 있는 경우 MySQL은 중첩 루프를 지원하기 위해 인덱스 조회를 선호합니다.

3 동일한 쿼리가 없으면 중첩 루프가 사용됩니다.

는 다음과 같습니다.

EXPLAIN FORMAT=TREE
SELECT
  *
FROM
  city
  JOIN province
    ON city.province_id < province.province_id;

로그인 후 복사

출력은 다음과 같습니다.

| <not executable by iterator executor>

로그인 후 복사

문 실행이 해시 연결을 사용하는지 확인하는 방법은 무엇입니까?

EXPLAIN FORMAT= TREE는 MySQL 8.0.16 이상 버전에서 사용할 수 있습니다. TREE는 트리와 같은 출력을 제공하며 기존 형식보다 쿼리 처리를 더 정확하게 설명합니다. 해시 조인의 사용법을 보여주는 유일한 형식입니다.

또한 EXPLAIN ANALYZE를 사용하여 해시 연결 정보를 볼 수도 있습니다.

위 내용은 MySQL 커뮤니티 서버 8.0.18을 기준으로 작성되었습니다.

위 내용은 데이터베이스 해시 연결에 대한 자세한 설명(MySQL의 새로운 기능)의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!