Mysql 공동 색인의 가장 왼쪽 매칭 원리에 대한 심층 분석

이전에 인터넷에서 MySQL 연합 색인의 맨 왼쪽 접두어 매칭에 관한 많은 문장 들을 보았는데, 나는 그 원리를 이해했다고 생각했다. 최근 면접에서 면접관과 소통하다가 뭔가 빠진 것을 발견했다. 여기서, 내가 이 방면의 내용을 직접 빗어 보겠습니다.

맨 왼쪽 접두어 일치 원칙

Mysql 에서 연방 인덱스를 작성할 때 맨 왼쪽 접두어 일치 원칙, 즉 맨 왼쪽 우선 순위를 따릅니다. 데이터를 검색할 때 연방 인덱스의 맨 왼쪽부터 일치됩니다. 예를 들면 다음과 같습니다.

Col 1, col2 행 및 col3 행에 통합 인덱스를 생성합니다.

핵심 test _ col1_ col 2 _ col3 ontest (col1,col2, col3);

통합 인덱스 test_col 1_col2_col3 은 실제로 세 개의 인덱스 (col 1), (col 1, col) 를 생성합니다

Select * from test where col1= "1"and ClO2 = "2" 및 clo4 = "4"

위의 질의문은 맨 왼쪽 접두어 일치 원칙에 따라 수행되고 검색 시 인덱스 (col 1, col2) 를 사용하여 데이터 일치를 수행합니다.

주의하다

인덱스화된 필드는 임의의 순서로 정렬할 수 있습니다. 예를 들면 다음과 같습니다.

Select * from test where col1= "1"및 ClO2 = "2"

Select * from test where col2 = "2" 및 clo1= "1"

색인 (col 1, col2) 은 두 개의 질의문에 사용됩니다. Mysql 이 통합 인덱스를 생성하는 규칙은 통합 인덱스의 맨 왼쪽 데이터 즉, 첫 번째 필드인 col 1 을 정렬한 다음 첫 번째 필드 정렬을 기준으로 두 번째 필드인 col2 를 정렬하는 것입니다. 실제로 order by col 1 col2 와 같은 정렬 규칙을 구현하는 것과 같습니다.

두 번째 질의문이 맨 왼쪽 접두사와 일치하지 않는다는 것이 이상할 수 있습니다. 첫째, 두 질의문 모두 색인의 col 1 및 col2 필드 (col 1, col2) 를 보장하지만 순서는 다릅니다 결과가 같다면 어떤 순서가 가장 좋을까요? 이 시점에서 MySQL 질의 최적기 explain 을 사용할 수 있습니다. 이 기능은 SQL 문이 가장 효율적으로 실행되어야 하는 순서를 수정하고 결국 실제 실행 계획을 생성합니다.

지출을 줄이다. 통합 인덱스 (col 1, col2, COL3) 를 구축하는 것은 실제로 세 개의 인덱스 (COL 1), (COL 1, col3) 를 구축하는 것과 같습니다 각각의 추가 인덱스는 쓰기 및 디스크 공간 비용을 증가시킵니다. 대량의 데이터가 있는 테이블의 경우 연방 인덱스를 사용하면 오버헤드를 크게 줄일 수 있습니다!

색인을 덮어씁니다. 통합 인덱스 (col 1, COL2, col3) 의 경우 SQL: Select COL 1, col2, col3 from test where col 그러면 MySQL 은 테이블을 반환하지 않고 인덱스를 통해 직접 데이터를 얻을 수 있으므로 대량의 임의 io 작업을 줄일 수 있습니다. Io 작업, 특히 임의 io 를 줄이는 것이 DBA 의 주요 최적화 전략입니다. 따라서 실제 응용 프로그램에서 겹치는 인덱스는 성능을 향상시키는 주요 최적화 방법 중 하나입니다.

효율이 높다. 인덱스 열이 많을수록 인덱스가 필터링하는 데이터가 줄어듭니다. 1000W 데이터가 있는 테이블에는 다음과 같은 SQL 이 있습니다. select from table 여기서 col 1 = 1, col 2 = 2, col3 = 이 색인은100w10% =100w 의 데이터를 필터링한 다음 일치하는 데이터가 통합 인덱스인 경우 인덱스를 통해100w 를 필터링할 수 있습니다

확장 (의미)

통합 인덱스 (col 1, col2, col3) 의 경우 쿼리 문 SELECT * FROM test 여기서 col2 = 2 는 인덱스를 트리거할 수 있습니까?

대부분의 사람들은 그렇지 않다고 말할 것입니다. 사실 그렇습니다.

이유:

SELECT * FROM test 를 해석합니다. 여기서 col2 = 2

설명 select * from test where col1=1;

위의 두 해석 결과에서 유형 필드를 관찰합니다. 질의에는 다음이 포함됩니다.

인덱스: 이 유형은 MySQL 이 전체 인덱스를 스캔한다는 것을 의미합니다. 이 유형의 색인을 사용하려는 경우 이 인덱스에 대한 특별한 요구 사항은 없습니다. Mysql 은 인덱스 또는 통합 인덱스의 일부인 한 인덱스 유형으로 스캔할 수 있습니다. 그러나 단점은 효율성이 높지 않다는 것이다. Mysql 은 판단 기준을 충족하는 색인을 찾을 때까지 색인의 첫 번째 데이터부터 시작하여 마지막 데이터를 하나씩 찾습니다. 따라서 위의 문은 색인을 트리거합니다.

Ref: 이 유형은 MySQL 이 인덱스의 각 데이터를 하나씩 스캔하는 대신 특정 알고리즘에 따라 적합한 인덱스를 빠르게 찾는 것을 의미합니다. 즉, 일반적으로 이해하는 소위 인덱스 쿼리는 데이터를 더 빨리 얻을 수 있습니다. 그러나 이런 검색을 위해서는 색인이 필요하다. 이러한 빠른 검색 알고리즘을 구현하려면 색인이 특정 데이터 구조를 충족해야 합니다. 간단히 말해서, 색인을 사용하려면 색인 필드의 데이터가 질서 정연해야 합니다.

제가 여러분께 소개한 Mysql 연합 색인의 가장 왼쪽 일치 원칙입니다. 너에게 도움이 되었으면 좋겠다. 질문이 있으시면 메시지를 남겨주세요. 제 시간에 회신 해 드리겠습니다.

두 달 후에 n offer 를 받아서 내가 어떻게 하는지 보자.

면접 요약: 20 19 가장 포괄적인 학습 수집 시험 자료 (답변 포함).

타오바오는 면접을 보고 돌아와서 프로그래머들과 이야기를 나누고 싶다.

"너무 많은 대형 공장의 면문제를 본 적이 있는데, 사실 이 세 가지 능력일 뿐이다."