MySQL 테이블 백 쿼리와 인덱스 적용 범위의 차이점은 무엇입니까?

테이블 반환 쿼리

InnoDB 인덱스는 두 가지 범주로 나누어집니다. 하나는 클러스터형 인덱스(Clustered Index)이고 다른 하나는 비클러스터형 인덱스(Secondary Index)입니다.

클러스터형 인덱스(clustered index): 리프 노드는 전체 Row를 저장합니다. 데이터, 인덱스를 찾으면 데이터를 찾을 수 있습니다. 테이블에 있는 행의 물리적 순서는 키 값의 논리적(인덱스) 순서와 동일합니다. 클러스터형 인덱스. 색인(디렉토리)은 한 가지 방법으로만 정렬할 수 있기 때문입니다.

비클러스터형 인덱스(일반 인덱스, 비클러스터형 인덱스, 보조 인덱스): 비클러스터형 인덱스의 btree 리프 노드에는 현재 데이터 행의 PK(기본 키)가 저장됩니다. 예를 들어 MYISAM은 먼저 key_buffer를 통해 인덱스를 메모리에 캐시합니다. 데이터에 액세스해야 하는 경우(인덱스를 통해 액세스하는 데이터) 인덱스를 메모리에서 직접 검색한 다음 인덱스를 통해 디스크에 있는 해당 데이터를 찾습니다. . 인덱스가 키 버퍼에 도달하지 않는 이유는 속도가 느린 이유입니다.

기본 키가 아닌 인덱스 구조의 리프 노드는 왜 기본 키 값을 저장하나요?

행 이동이나 데이터 페이지 분할이 발생할 때 보조 인덱스의 유지 관리 작업을 줄입니다(데이터를 업데이트해야 하는 경우 보조 인덱스는 수정할 필요가 없으며 클러스터형 인덱스만 수정하면 되고 테이블은 클러스터형 인덱스는 하나만 가질 수 있고 나머지는 보조 인덱스이므로 클러스터형 인덱스만 수정하면 되며 보조 인덱스를 다시 빌드할 필요가 없습니다)

비클러스터형 인덱스를 사용할 때 특정 데이터를 얻기 위해 , 기본 키를 통해 클러스터형 인덱스로 돌아가 데이터를 쿼리해야 합니다. 그런 다음 테이블 쿼리를 다시 호출합니다. 인덱스 트리가 두 번 스캔되었습니다. 그래서 효율이 상대적으로 낮습니다.

인덱스 커버리지

인덱스 커버리지는 테이블 반환 쿼리에 대한 솔루션입니다. 이름에서 알 수 있듯이 쿼리의 모든 열은 사용된 인덱스 열에 의해 보호됩니다(단일 열 인덱스일 수도 있고, 조인트 인덱스일 수도 있으며, 일반적으로 조인트 인덱스입니다. 단일 열 인덱스는 모든 열을 포함하기 어렵습니다) 쿼리의).

인덱스에는 쿼리할 필드의 값이 이미 포함되어 있으므로 쿼리 시 인덱스에 있는 필드 값을 직접 반환하면 충분하므로 기본 키 인덱스의 2차 쿼리를 피하여 테이블에 쿼리할 필요가 없습니다. , 즉 쿼리 효율성이 향상되었습니다.

id는 클러스터형 인덱스이고 name은 비클러스터형 인덱스입니다.

select name, age from t where name = 'lcc';

테이블을 다시 쿼리해야 합니다.

인덱스 적용 범위:

SQL에서 이름 필드만 쿼리하세요. 이런 방식으로 name 인덱스는 모든 쿼리 열을 포함합니다.

select name  from t where name = 'lcc';

name의 인덱스를 공동 인덱스(이름, 나이)로 수정한 다음 name = 'lcc'인 t에서 name, age 선택을 실행합니다. 여기에는 모든 쿼리 열도 포함됩니다.
커버링 인덱스는 인덱스 열 값을 저장해야 하지만, 해시 인덱스, 공간 인덱스, 전체 텍스트 인덱스는 인덱스 열 값을 저장하지 않기 때문에 B-Tree 인덱스를 사용하는 데이터만 커버링 인덱스로 사용할 수 있습니다.

인덱스 커버리지 쿼리 수행 시 explain(실행 계획)의 Extra 컬럼에서 [Using Index] 정보를 확인할 수 있습니다.

인덱스 처리의 장점

• 인덱스 항목은 일반적으로 데이터 행의 크기보다 훨씬 작습니다. 왜냐하면 인덱스를 처리하려면 인덱스를 읽기만 하면 되므로 데이터 액세스 양이 크게 줄어들기 때문입니다.

• 인덱스는 열 값 순서로 저장되며 IO 집약적인 범위 검색은 디스크에서 데이터의 각 행을 무작위로 읽는 IO보다 훨씬 작습니다.

• MyISAM과 같은 일부 스토리지 엔진은 메모리의 인덱스만 캐시하며 데이터는 운영 체제에 의존하여 캐시합니다. 따라서 데이터에 액세스하려면 커버링 인덱스를 사용하여 이를 방지해야 합니다.

• InnoDB 엔진의 데이터베이스 테이블의 경우 InnoDB의 클러스터형 인덱스 덕분에 인덱스를 포함하는 것이 매우 실용적입니다. InnoDB의 보조 인덱스는 리프 노드에 있는 행의 기본 키 값을 저장하기 때문에 보조 인덱스가 쿼리를 다룰 수 있으면 기본 키 인덱스의 보조 쿼리를 방지합니다.

인덱스 범위를 사용하여 SQL을 최적화하는 데 적합한 시나리오는 무엇입니까?

• 전체 레코드 행을 쿼리할 필요가 없는 경우

• 전체 테이블 수 쿼리 최적화;

• 테이블에 레코드가 너무 적으면 인덱스를 생성하지 마세요

자주 추가, 삭제 또는 수정되는 테이블이나 필드(예: 사용자 잔액)

• 인덱스를 생성하지 마세요. Where 조건에서 사용되지 않는 필드에 대한 필터링 성적 지향이 좋지 않은 사람은 인덱스 구축에 적합하지 않습니다(예: 성별)

• 인덱스 푸시다운
  인덱스 푸시다운 최적화는 MySQL 5.6에서 도입되었습니다. , 인덱스에 포함된 필드를 먼저 판단하고 조건에 맞지 않는 레코드를 삭제하고 테이블 반환 횟수를 줄일 수 있습니다.
• 공동 인덱스 생성:

  KEY `username` (`name`,`age`) )

  실행:

select * from user2 where name like 'j%' and age=99;

• 위 쿼리 SQL은 준수합니다.
  위 5.5의 SQL 실행 흐름은 다음과 같습니다.
먼저, MySQL 서버 계층은 스토리지 엔진을 호출하여 첫 ​​번째 사용자 이름을 얻습니다. j로 시작합니다.
스토리지 엔진이 username=‘j’의 첫 번째 레코드를 찾은 후 B+Tree의 리프 노드에 기본 키 ID가 저장됩니다. 이때 테이블 반환 작업을 통해 해당 레코드를 찾습니다. 기본 키 인덱스를 완성하고 서버 계층으로 돌아갑니다.

• 서버 계층은 데이터를 가져온 후 레코드의 연령이 99인지 확인합니다. age=99인 경우 레코드를 클라이언트에 반환합니다!=99인 경우 레코드를 삭제합니다.

5.6에서 위 SQL의 실행 흐름은 다음과 같습니다.

• MySQL의 서버 계층은 먼저 스토리지 엔진을 호출하여 j로 시작하는 첫 번째 사용자 이름을 찾습니다.

• 기록을 찾은 후 스토리지 엔진은 서둘러 테이블로 돌아가지 않고 계속해서 이 레코드의 age가 99인지 확인합니다. age=99이면 테이블로 돌아갑니다. 나이가 99와 같지 않으면 테이블이 반환되지 않으며 다음 레코드를 직접 읽습니다.

위 내용은 MySQL 테이블 백 쿼리와 인덱스 적용 범위의 차이점은 무엇입니까?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!