MySQL 인덱스의 모든 지식 포인트를 하나의 기사로 이해(수집 권장)

이 글은 mysql 데이터베이스의 인덱싱 관련 지식을 소개하며, 인덱싱에 관한 거의 모든 지식 포인트를 담고 있어 모든 분들께 도움이 되기를 바랍니다.

Mysql index

index 소개

index란 무엇입니까

• 공식 소개 Index는 MySQL데이터를 효율적으로 얻는 데 도움이 되는 데이터 구조입니다. 더 일반적으로 말하자면, 데이터베이스 색인은 책 앞부분의 목차와 같으며, 이는 데이터베이스 쿼리 속도를 높일 수 있습니다.

• 일반적으로 인덱스 자체도 매우 크고 메모리에 모두 저장하는 것이 불가능하기 때문에 인덱스는 디스크에 파일로 저장되는 경우가 많습니다(별도의 인덱스 파일에 저장되거나 함께 저장될 수도 있음) 데이터 파일에 저장된 데이터).

• 우리가 일반적으로 인덱스라고 부르는 것은 클러스터형 인덱스, 커버링 인덱스, 결합 인덱스, 접두사 인덱스, 고유 인덱스 등이 있습니다. 특별한 지시가 없으면 기본값은 B+ 트리 구조(다중 경로 검색 트리, 반드시 그럴 필요는 없음)를 사용하는 것입니다. 바이너리) 인덱스입니다.

인덱스의 장점과 단점

장점:

• 은 책의 목차와 유사하게 데이터 검색의 효율성을 향상시키고 데이터베이스의 IO 비용을 줄일 수 있습니다.

• 색인 열을 통해 데이터를 정렬하면 데이터 정렬 비용과 CPU 소비가 줄어듭니다.

  • [단일 열 인덱스], [결합 인덱스] 등 인덱스가 있는 열은 자동으로 정렬되지만, 결합 인덱스의 정렬은 더 복잡합니다.
  • 인덱스 열의 순서에 따라 정렬하면 명령문별로 정렬하는 효율성이 훨씬 향상됩니다.

단점:

• 인덱스는 디스크 공간을 차지합니다

• 인덱스는 쿼리 효율성을 향상시키지만 테이블 업데이트 효율성을 떨어뜨립니다. 예를 들어, 테이블이 추가, 삭제, 수정될 때마다 MySQL은 데이터를 저장할 뿐만 아니라 해당 인덱스 파일을 저장하거나 업데이트해야 합니다.

인덱스 유형

기본 키 인덱스

인덱스 열의 값은 고유해야 하며 null 값은 허용되지 않습니다.

일반 인덱스

MySQL의 기본 인덱스 유형은 제한이 없으며 인덱스가 정의된 열에 중복 값과 null 값을 삽입할 수 있습니다.

고유 인덱스

인덱스 열의 값은 고유해야 하지만 null 값은 허용됩니다.

전체 텍스트 인덱스

전체 텍스트 인덱스는 텍스트 유형 CHAR, VARCHAR 및 TEXT 유형 필드에서만 생성할 수 있습니다. 필드 길이가 상대적으로 큰 경우 일반 인덱스를 생성하면 퍼지 쿼리와 같은 수행 시 효율성이 떨어지게 됩니다. 이 경우 전체 텍스트 인덱스를 생성할 수 있습니다. 전체 텍스트 인덱스는 MyISAM과 InnoDB 모두에서 사용할 수 있습니다.

공간 인덱스

MySQL은 5.7 이후 버전에서 공간 인덱스를 지원하고 OpenGIS 기하학적 데이터 모델을 지원합니다. MySQL은 공간 인덱싱 측면에서 OpenGIS 기하학적 데이터 모델 규칙을 따릅니다.

접두사 인덱스

CHAR, VARCHAR, TEXT 등 텍스트 유형 열에 인덱스를 생성할 때 인덱스 열의 길이를 지정할 수 있지만 숫자 유형은 지정할 수 없습니다.

기타 (인덱스 열 수에 따라 분류)

1. 단일 열 인덱스

2. 결합 인덱스

   결합 인덱스 사용은 가장 왼쪽 접두사 일치 원칙(가장 왼쪽 일치 원칙)을 따라야 합니다. 일반적으로 조건이 허용되는 경우 여러 개의 단일 열 인덱스 대신 결합 인덱스가 사용됩니다.

인덱싱된 데이터 구조

해시 테이블

해시 테이블, HashMap, TreeMap은 Java에서 키-값 쌍의 형태로 데이터를 저장하는 해시 테이블 구조입니다. 테이블 데이터를 저장하기 위해 해시 테이블을 사용합니다. 키는 인덱스 열을 저장할 수 있고, 값은 행 레코드나 행 디스크 주소를 저장할 수 있습니다. 해시 테이블은 동등한 쿼리에서 매우 효율적이며 시간 복잡도는 O(1)입니다. 그러나 빠른 범위 검색을 지원하지 않으며 범위 검색은 전체 테이블을 검색해야만 수행할 수 있습니다.

분명히 이는 검색과 범위 검색이 자주 필요한 데이터베이스 인덱스로 사용하기에는 적합하지 않습니다.

이진 검색 트리

이진 트리, 누구나 마음 속에 그림이 있을 것 같아요.

이진 트리의 특징: 각 노드는 최대 2개의 포크를 가지며, 왼쪽 하위 트리와 오른쪽 하위 트리의 데이터 순서는 왼쪽이 더 작고 오른쪽이 더 큽니다.

이 기능은 각 검색을 절반으로 줄이고 IO 횟수를 줄이기 위한 것입니다. 그러나 이진 트리는 동시성 상황이 발생하기 쉽기 때문에 첫 번째 루트 노드의 값에 대한 테스트입니다. 이 기능에서는 "트리를 분기할 수 없습니다"라는 문제가 발생합니다. 이는 매우 불편하고 불안정합니다.

분명히 이 상황은 불안정하며 우리는 설계에서 이러한 상황을 반드시 피할 것입니다.

균형 이진 트리

균형 이진 트리는 이분법적 사고를 채택하는 동시에 균형 이진 탐색 트리입니다. , 가장 중요한 특징은 트리의 왼쪽 및 오른쪽 하위 트리의 수준이 최대 1만큼 다르다는 것입니다. 데이터를 삽입하고 삭제할 때 이진 트리의 균형을 유지하기 위해 좌회전/우회전 연산이 사용됩니다. 왼쪽 하위 트리는 그다지 크지 않고 오른쪽 하위 트리는 짧습니다.

균형 이진 탐색 트리를 이용한 질의의 성능은 이진 탐색 방법에 가깝고, 시간 복잡도는 O(log2n)입니다. id=6을 쿼리하려면 2개의 IO만 필요합니다.

이 기능을 보면 이것이 매우 좋고 이진 트리의 이상적인 상황을 달성할 수 있다고 생각할 수 있습니다. 그러나 여전히 몇 가지 문제가 있습니다.

1. 시간 복잡도는 트리 높이와 관련이 있습니다. 트리를 검색해야 하는 횟수는 트리의 높이에 따라 달라집니다. 각 노드를 읽는 것은 디스크 IO 작업에 해당합니다. 트리의 높이는 데이터를 쿼리할 때마다 수행되는 디스크 IO 작업 수와 같습니다. 각 디스크 탐색 시간은 10ms입니다. 테이블 데이터의 양이 많으면 쿼리 성능이 매우 저하됩니다. (1백만 개의 데이터 볼륨, log2n은 대략 20개의 디스크 IO 횟수, 시간은 20*10=0.2s)

2. 균형 이진 트리는 빠른 검색을 위해 범위 쿼리를 지원하지 않습니다. 범위 쿼리에는 루트 노드에서 여러 번의 순회가 필요합니다. 쿼리 효율성이 높지 않습니다.

B-Tree: Transform Binary Tree

MySQL 데이터는 디스크 파일에 저장됩니다. 데이터를 쿼리하고 처리할 때 먼저 디스크에서 메모리로 데이터를 로드해야 합니다. 디스크 IO 작업은 매우 시간이 많이 걸립니다. 그래서 최적화했습니다. 요점은 디스크 IO 작업을 최소화하는 것입니다. 이진 트리의 각 노드에 액세스하면 IO가 발생합니다. 디스크 IO 작업을 줄이려면 트리 높이를 최대한 줄여야 합니다. 그렇다면 트리의 높이를 줄이는 방법은 무엇입니까?

키가 bigint=8바이트인 경우 각 노드에는 2개의 포인터가 있고 각 포인터는 4바이트이며 하나의 노드는 16바이트의 공간(8+4*2=16)을 차지합니다.

MySQL의 InnoDB 스토리지 엔진은 한 IO에서 한 페이지(기본 페이지는 16K)의 데이터 양을 읽는 반면, 바이너리 트리의 한 IO에 있는 데이터의 유효 양은 16바이트에 불과하고 공간 활용도가 높기 때문입니다. 매우 낮습니다. 하나의 IO 공간의 활용을 극대화하기 위해서는 각 노드에 여러 요소를 저장하고 각 노드에 최대한 많은 데이터를 저장하는 것이 간단한 아이디어입니다. 각 노드는 1000개의 인덱스(16k/16=1000)를 저장할 수 있으므로 이진 트리를 다중 포크 트리로 변환합니다. 트리의 포크 트리를 늘리면 트리가 크고 얇은 트리로 변경됩니다. 100만 개의 데이터를 구성하려면 트리 높이에 2개 수준(1000*1000=100만)만 필요합니다. 즉, 데이터를 쿼리하는 데 2개의 디스크 IO만 필요하다는 의미입니다. 디스크 IO 수가 줄어들고 데이터 쿼리 효율성이 향상됩니다.

이 데이터 구조를 B-트리라고 합니다. B-트리는 아래와 같이 다중 포크 균형 검색 트리입니다.

1. B-트리의 노드는 여러 요소를 저장합니다. , 각 내부 노드에는 여러 요소가 있습니다.

2. 노드의 요소에는 키 값과 데이터가 포함됩니다. 노드의 키 값은 큰 것부터 작은 것 순으로 배열됩니다. 즉, 데이터는 모든 노드에 저장됩니다.

3. 상위 노드의 요소는 하위 노드에 표시되지 않습니다.

4. 모든 리프 노드는 동일한 레이어에 위치하고 리프 노드의 깊이는 동일하며 리프 노드 사이에는 포인터 연결이 없습니다.

예를 들어 b-트리의 데이터를 쿼리할 때:

값이 10인 데이터를 쿼리한다고 가정합니다. 쿼리 경로 디스크 블록 1->디스크 블록 2->디스크 블록 5.

첫 번째 디스크 IO: 디스크 블록 1을 메모리에 로드하고, 메모리에서 처음부터 10

두 번째 디스크 IO: 디스크 블록 2를 메모리에 로드하고 메모리에서 처음부터 7

세 번째 디스크 IO: 디스크 블록 5를 메모리에 로드하고 메모리에서 처음부터 순회 비교하여 10=10을 찾아 10을 찾아 데이터를 꺼내고, 데이터에 저장된 행 레코드를 빼면, 쿼리가 종료됩니다. 디스크 주소가 저장되어 있으면 디스크 주소에 따라 디스크에서 데이터를 가져와야 하며 쿼리가 종료됩니다.

이진 균형 검색 트리와 비교하면 전체 검색 과정에서 데이터 비교 횟수는 크게 줄어들지 않지만 디스크 IO 횟수는 크게 줄어듭니다. 동시에 비교가 메모리에서 수행되므로 비교 시간은 무시할 수 있습니다. B-트리의 높이는 일반적으로 대부분의 애플리케이션 시나리오를 충족할 수 있는 2~3개 계층이므로 B-트리를 사용하여 인덱스를 구축하면 쿼리 효율성을 크게 향상시킬 수 있습니다.

과정은 사진과 같습니다.

이것만 보면 B-tree가 이상적이라고 생각하시겠지만, 선배님들께서는 아직 최적화할 수 있는 곳이 있다고 말씀하실 겁니다.

1. B-tree는 범위 질의 검색 속도를 지원하지 않습니다. 이 상황을 생각해보세요. 10에서 35 사이의 데이터를 검색하려면 15를 찾은 후 다시 루트 노드로 돌아가 검색을 거쳐야 합니다. 루트 노드에서 여러 번 순회하려면 쿼리 효율성을 향상해야 합니다.

2. 데이터에 행 레코드가 저장되면 열 개수가 늘어날수록 행 크기도 늘어나고, 차지하는 공간도 늘어납니다. 이때 페이지에 저장할 수 있는 데이터의 양은 줄어들고, 트리의 키도 커지며, 디스크 IO 횟수도 늘어나게 됩니다.

B+ 트리: B 트리 변환

B+ 트리는 B 트리를 기반으로 한 업그레이드 버전으로 MySQL은 B 트리를 기반으로 계속 변환하고 B+ 트리를 사용하여 인덱스를 구축합니다. B+ 트리와 B-트리의 주요 차이점은 비리프 노드가 데이터를 저장하는지

• B-트리인지 여부에 대한 질문입니다. 비리프 노드와 리프 노드 모두 데이터를 저장합니다.
• B+ 트리: 리프 노드만 데이터를 저장하고 리프가 아닌 노드는 키 값을 저장합니다. 리프 노드는 양방향 포인터를 사용하여 연결되며, 가장 낮은 리프 노드는 양방향 순서 연결 목록을 형성합니다.

B+ 트리의 가장 낮은 리프 노드에는 모든 인덱스 항목이 포함됩니다. 그림에서 볼 수 있듯이 B+ 트리가 데이터를 검색할 때 데이터는 가장 낮은 리프 노드에 저장되어 있으므로 각 검색에서 데이터를 쿼리하려면 리프 노드를 검색해야 합니다. 따라서 데이터를 쿼리해야 할 경우 각 디스크의 IO는 트리의 높이와 직접적인 관련이 있지만, 반면에 데이터는 리프 노드에 배치되므로 디스크 블록 잠금에 저장된 인덱스의 개수가 달라집니다. 이에 따라 인덱스가 증가하므로 B 트리에 비해 B+ 트리의 높이는 이론적으로 B 트리보다 짧습니다. 인덱스가 쿼리를 포괄하는 경우도 있습니다. 이 경우 인덱스의 데이터가 현재 쿼리 문에서 요구하는 모든 데이터를 충족하면 가장 낮은 리프 노드를 검색하지 않고 즉시 반환할 수 있습니다.

예:

• 동등한 쿼리:

값이 9인 데이터를 쿼리한다고 가정합니다. 쿼리 경로 디스크 블록 1->디스크 블록 2->디스크 블록 6.

1. 첫 번째 디스크 IO: 디스크 블록 1을 메모리에 로드하고 메모리에서 처음부터 순회 및 비교하고 9

2. 두 번째 디스크 IO: 디스크 블록 2를 메모리에 로드하고 메모리에서 처음부터 7

3. 세 번째 디스크 IO: 디스크 블록 6을 메모리에 로드하고 메모리를 처음부터 순회 비교하여 세 번째 인덱스에서 9를 찾아 데이터를 꺼내고, 데이터에 저장된 행 레코드를 빼면, 쿼리가 종료됩니다. 디스크 주소가 저장되어 있으면 디스크 주소에 따라 디스크에서 데이터를 가져와야 하며 쿼리가 종료됩니다. (여기서 구별해야 할 점은 InnoDB의 Data는 행 데이터를 저장하고 MyIsam은 디스크 주소를 저장한다는 점입니다.) 프로세스는 그림과 같습니다.

범위 쿼리:

• 9에서 26 사이의 데이터를 찾고 싶습니다. 검색 경로는 디스크 블록 1 -> 디스크 블록 2 -> 디스크 블록 6 -> 디스크 블록 7입니다.

먼저 값이 9인 데이터를 찾고, 값이 9인 데이터를 결과 세트에 캐시합니다. 이 단계는 이전의 동등한 쿼리 프로세스와 동일하며 3개의 디스크 IO가 발생합니다.

1. 15를 찾은 후 기본 리프 노드는 정렬된 목록입니다. 디스크 블록 6과 키 값 9에서 시작하여 필터링 조건을 충족하는 모든 데이터를 필터링하기 위해 뒤로 이동합니다.

2. 네 번째 디스크 IO: 디스크 6의 후속 포인터에 따라 디스크 블록 7의 주소를 지정하고 찾습니다. 디스크 7을 메모리에 로드하고 처음부터 메모리에서 순회 및 비교합니다. 9

3. 기본 키는 고유하며(나중에

MySQL의 인덱스는 B+ 트리 데이터 구조를 사용하여 동일한 값 및 범위 쿼리에 대해 빠른 검색을 보장할 수 있음을 알 수 있습니다.

Mysql 인덱스 구현인덱스 데이터 구조를 도입한 후 실제 사용 시나리오를 보려면 MySQL로 가져와야 하므로 다음은 Mysql의 두 가지 스토리지 엔진인

MyISAM 인덱스

및

InnoDB의 인덱스 구현에 대한 분석입니다. Index

MyIsam Index간단한 사용자 테이블을 예로 들어보겠습니다. 사용자 테이블에는 두 개의 인덱스가 있는데, id 열은 기본 키 인덱스이고 age 열은 일반 인덱스입니다.

CREATE TABLE `user`(
  `id`       int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) DEFAULT NULL,
  `age`      int(11)     DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_age` (`age`) USING BTREE) ENGINE = MyISAM  AUTO_INCREMENT = 1
  DEFAULT CHARSET = utf8;

MyISAM의 데이터 파일과 인덱스 파일은 별도로 저장됩니다. MyISAM이 B+ 트리를 사용하여 인덱스 트리를 구축할 때 리프 노드에 저장된 키 값은 인덱스 열의 값이고, 데이터는 인덱스가 위치한 행의 디스크 주소입니다.

기본 키 인덱스

테이블 사용자의 인덱스는 인덱스 파일

에 저장됩니다.

쿼리 중 디스크 IO 상황에 대한 간단한 분석:

user.MYI中，数据文件存储在数据文件 user.MYD

기본 키 등가물을 기반으로 한 데이터 쿼리:

select * from user where id = 28;

1. 先在主键树中从根节点开始检索，将根节点加载到内存，比较28
2. 将左子树节点加载到内存中，比较16
3. 检索到叶节点，将节点加载到内存中遍历，比较16
4. 从索引项中获取磁盘地址，然后到数据文件user.MYD中获取对应整行记录。（1次磁盘IO）
5. 将记录返给客户端。

磁盘IO次数：3次索引检索+记录数据检索。

根据主键范围查询数据：

select * from user where id between 28 and 47;

1. 先在主键树中从根节点开始检索，将根节点加载到内存，比较28

2. 将左子树节点加载到内存中，比较16

3. 检索到叶节点，将节点加载到内存中遍历比较16

   根据磁盘地址从数据文件中获取行记录缓存到结果集中。（1次磁盘IO）

   我们的查询语句时范围查找，需要向后遍历底层叶子链表，直至到达最后一个不满足筛选条件。

4. 向后遍历底层叶子链表，将下一个节点加载到内存中，遍历比较，28

5. 最后得到两条符合筛选条件，将查询结果集返给客户端。

磁盘IO次数：4次索引检索+记录数据检索。

**备注：**以上分析仅供参考，MyISAM在查询时，会将索引节点缓存在MySQL缓存中，而数据缓存依赖于操作系统自身的缓存，所以并不是每次都是走的磁盘，这里只是为了分析索引的使用过程。

辅助索引

在 MyISAM 中,辅助索引和主键索引的结构是一样的，没有任何区别，叶子节点的数据存储的都是行记录的磁盘地址。只是主键索引的键值是唯一的，而辅助索引的键值可以重复。

查询数据时，由于辅助索引的键值不唯一，可能存在多个拥有相同的记录，所以即使是等值查询，也需要按照范围查询的方式在辅助索引树中检索数据。

InnoDB索引

主键索引（聚簇索引）

每个InnoDB表都有一个聚簇索引 ，聚簇索引使用B+树构建，叶子节点存储的数据是整行记录。一般情况下，聚簇索引等同于主键索引，当一个表没有创建主键索引时，InnoDB会自动创建一个ROWID字段来构建聚簇索引。InnoDB创建索引的具体规则如下：

1. 在表上定义主键PRIMARY KEY，InnoDB将主键索引用作聚簇索引。
2. 如果表没有定义主键，InnoDB会选择第一个不为NULL的唯一索引列用作聚簇索引。
3. 如果以上两个都没有，InnoDB 会使用一个6 字节长整型的隐式字段 ROWID字段构建聚簇索引。该ROWID字段会在插入新行时自动递增。

除聚簇索引之外的所有索引都称为辅助索引。在中InnoDB，辅助索引中的叶子节点存储的数据是该行的主键值都。 在检索时，InnoDB使用此主键值在聚簇索引中搜索行记录。

这里以user_innodb为例，user_innodb的id列为主键，age列为普通索引。

CREATE TABLE `user_innodb`(
  `id`       int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) DEFAULT NULL,
  `age`      int(11)     DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_age` (`age`) USING BTREE) ENGINE = InnoDB;

InnoDB的数据和索引存储在一个文件t_user_innodb.ibd中。InnoDB的数据组织方式，是聚簇索引。

主键索引的叶子节点会存储数据行，辅助索引只会存储主键值。

等值查询数据：

select * from user_innodb where id = 28;

1. 先在主键树中从根节点开始检索，将根节点加载到内存，比较28

2. 将左子树节点加载到内存中，比较16

3. 检索到叶节点，将节点加载到内存中遍历，比较16

   磁盘IO数量：3次。

辅助索引

除聚簇索引之外的所有索引都称为辅助索引，InnoDB的辅助索引只会存储主键值而非磁盘地址。

以表user_innodb的age列为例，age索引的索引结果如下图。

底层叶子节点的按照（age，id）的顺序排序，先按照age列从小到大排序，age列相同时按照id列从小到大排序。

使用辅助索引需要检索两遍索引：首先检索辅助索引获得主键，然后使用主键到主索引中检索获得记录。

画图分析等值查询的情况：

select * from t_user_innodb where age=19;

根据在辅助索引树中获取的主键id，到主键索引树检索数据的过程称为回表查询。

磁盘IO数：辅助索引3次+获取记录回表3次

组合索引

还是以自己创建的一个表为例：表 abc_innodb，id为主键索引，创建了一个联合索引idx_abc(a,b,c)。

CREATE TABLE `abc_innodb`(
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a`  int(11)     DEFAULT NULL,
  `b`  int(11)     DEFAULT NULL,
  `c`  varchar(10) DEFAULT NULL,
  `d`  varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_abc` (`a`, `b`, `c`)) ENGINE = InnoDB;

select * from abc_innodb order by a, b, c, id;

组合索引的数据结构：

组合索引的查询过程：

select * from abc_innodb where a = 13 and b = 16 and c = 4;

最左匹配原则：

最左前缀匹配原则和联合索引的索引存储结构和检索方式是有关系的。

在组合索引树中，最底层的叶子节点按照第一列a列从左到右递增排列，但是b列和c列是无序的，b列只有在a列值相等的情况下小范围内递增有序，而c列只能在a，b两列相等的情况下小范围内递增有序。

就像上面的查询，B+树会先比较a列来确定下一步应该搜索的方向，往左还是往右。如果a列相同再比较b列。但是如果查询条件没有a列，B+树就不知道第一步应该从哪个节点查起。

可以说创建的idx_abc(a,b,c)索引，相当于创建了(a)、（a,b）（a,b,c）三个索引。、

组合索引的最左前缀匹配原则：使用组合索引查询时，mysql会一直向右匹配直至遇到范围查询(>、

覆盖索引

覆盖索引并不是说是索引结构，覆盖索引是一种很常用的优化手段。因为在使用辅助索引的时候，我们只可以拿到主键值，相当于获取数据还需要再根据主键查询主键索引再获取到数据。但是试想下这么一种情况，在上面abc_innodb表中的组合索引查询时，如果我只需要abc字段的，那是不是意味着我们查询到组合索引的叶子节点就可以直接返回了，而不需要回表。这种情况就是覆盖索引。

可以看一下执行计划：

覆盖索引的情况：

未使用到覆盖索引：

总结

看到这里，你是不是对于自己的sql语句里面的索引的有了更多优化想法呢。比如：

避免回表

在InnoDB的存储引擎中，使用辅助索引查询的时候，因为辅助索引叶子节点保存的数据不是当前记录的数据而是当前记录的主键索引，索引如果需要获取当前记录完整数据就必然需要根据主键值从主键索引继续查询。这个过程我们成位回表。想想回表必然是会消耗性能影响性能。那如何避免呢？

使用索引覆盖，举个例子：现有User表（id(PK),name(key),sex,address,hobby…）

이러한 구문은 비즈니스에서 자주 사용되며 사용자 테이블의 다른 필드는 사용 빈도가 훨씬 낮습니다. 이 경우 단일 인덱스를 사용하는 대신 이름 필드의 인덱스를 구축하면 됩니다. 하지만, 이 쿼리 문을 다시 실행하면 보조 인덱스 쿼리 결과를 바탕으로 현재 문의 전체 데이터를 얻을 수 있습니다. 이렇게 하면 성별 데이터를 얻기 위해 테이블로 돌아가는 것을 효과적으로 피할 수 있습니다. select id,name,sex from user where name ='zhangsan';

다음은 Covering Index를 사용하여 테이블 백을 줄이는 일반적인 최적화 전략입니다.

조인트 인덱스의 활용

조인트 인덱스인덱스를 구축할 때, 여러 개의 단일 컬럼 인덱스에 조인트 인덱스를 사용할 수 있는지 판단해 보세요. 조인트 인덱스를 사용하면 공간이 절약될 뿐만 아니라 인덱스 적용 범위를 더 쉽게 사용할 수 있습니다. 인덱싱된 필드가 많을수록 쿼리에서 반환된 데이터를 만족시키는 것이 더 쉬워진다고 상상해 보십시오. 예를 들어, 조인트 인덱스(a_b_c)는 a, a_b, a_b_c라는 세 개의 인덱스를 갖는 것과 동일합니다. 이렇게 하면 공간이 절약됩니까? 물론 절약된 공간은 세 개의 인덱스(a, a_b, a_b_c)의 3배가 아닙니다. 인덱스 트리의 데이터는 변경되지 않았지만 인덱스 데이터 필드의 데이터는 실제로 저장되어 있기 때문입니다.

결합 인덱스 생성 원칙. 자주 사용하는 열과 판별률이 높은 열을 앞에 배치해야 하며, 판별률이 높다는 것은 필터링 세분성이 크다는 의미입니다. 인덱스 생성 시 고려해야 할 최적화 시나리오, 조인트 인덱스에 쿼리로 자주 반환해야 하는 필드를 추가할 수도 있고, 커버링 인덱스를 사용하는 경우에는 이런 상황을 권장합니다. .아래의 조인트 인덱스를 사용하세요.

조인 인덱스 사용

병합 가능한 단일 열 인덱스가 이미 여러 개 있는지 고려해보세요. 그렇다면 현재 여러 단일 열 인덱스에서 조인트 인덱스를 생성하세요.
2. 현재 인덱스에는 반환 필드로 자주 사용되는 열이 포함되어 있습니다. 이때 쿼리 문이 포함 인덱스를 사용할 수 있도록 현재 열을 기존 인덱스에 추가할 수 있는지 여부를 고려할 수 있습니다.

【추천:

mysql 비디오 튜토리얼】

위 내용은 MySQL 인덱스의 모든 지식 포인트를 하나의 기사로 이해(수집 권장)의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!