MySQL 인덱스의 역할은 무엇입니까

먼저 데이터베이스 테이블 생성:

create table single_table(
	id int not 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 uk_key2(key2), # 二级索引，而且该索引是唯一二级索引
	key idx_key3(key3),       # 二级索引
	key idx_key_part(key_part1,key_part2,key_part3) # 二级索引，也是联合索引
)Engine=InnoDB CHARSET=utf8;

1. 스캔해야 하는 레코드 수를 줄이기 위해 인덱스를 사용합니다.

가장 기본적인 쿼리 실행 계획은 테이블의 모든 레코드를 스캔하고 확인하는 것입니다. 각 검색 기록이 검색 조건과 일치하는지 여부. 일치하면 클라이언트에 보내고, 그렇지 않으면 기록을 건너뜁니다. 이 실행 방식을 전체 테이블 스캔이라고 합니다.

InnoDB 스토리지 엔진의 경우 전체 테이블 스캔은 클러스터형 인덱스의 첫 번째 리프 노드의 첫 번째 레코드부터 시작하여 레코드가 있는 단방향 연결 목록을 따라 뒤로 스캔하는 것을 의미합니다. 마지막 리프 노드의 마지막 레코드에 대해 B+ 트리를 이용하여 인덱스 컬럼 값이 특정 값과 같은 레코드를 찾을 수 있다면 스캔해야 하는 레코드 수를 줄일 수 있다. InnoDB存储引擎来说，全表扫描意味着从聚簇索引第一个叶子节点的第一条记录开始，沿着记录所在的单向链表向后扫描，直到最后一个叶子节点的最后一条记录，如果可以利用B+树查找索引列值等于某个值的记录，这样就可以减少需要扫描的记录的数量。

由于B+树叶子节点中的记录是按照索引列值有小到大的顺序排序的，所以只需要扫描某个区间或者某些区间中的记录也可以明显减少需要扫描的记录的数量。

对于查询语句：

select * from single_table where id>=2 and id<=100;

这个语句其实就是想查找id值在[2,100]区间中的所有聚簇索引记录，我们可以通过聚簇索引对应的B+树快速的找到id=2的那条聚簇索引记录，然后沿着记录所在的单向链表向后扫描，直到某条聚簇索引记录的id值不在[2,100]区间中为止，与扫描全部的聚簇索引记录相比，这种方式大大减少了需要扫描的记录数量，所以提升了查询效率。

其实，对于B+树来说，只要索引列和常数使用=、<=>、in、not in、is null、is not null、>、<、>=、<=、between、!=、或者like操作符连接起来，就可以产生扫描区间，从而提高查询效率。

2、索引用于排序

我们在编写查询语句时，经常需要使用order by子句对查询出来的记录按照某种规则进行排序。在一般情况下，我们只能把记录加载到内存中，然后再用一些排序算法在内存中对这些记录进行排序。有时查询的结果集可能太大以至于在内存中无法进行排序，此时就需要暂时借助磁盘的空间来存放中间结果，在排序操作完成后再把排序的结果返回给客户端。

在MySQL中，这种在内存中或者磁盘中进行排序的方式称为文件排序，但是如果order by子句中使用了索引列，就有可能省去在内存或磁盘中排序的步骤。

1、分析下面的查询语句：

select * form single_table order by key_part1,key_part2,key_part3 limit 10;

这个查询语句的结果集需要先按照key_part1值排序，如果记录的key_part1值相同，再按照key_part2值排序，如果key_part1值和key_part2值都相同，再按照key_part3排序。而我们建立的联合索引idx_key_part就是按照上面的规则排序的，如下为idx_key_part索引的简化示意图：

所以我们可以从第一条idx_key_part二级索引记录开始，沿着记录所在的单向链表向后扫描，取10条二级索引记录即可。由于我们的查询列表是*，也就是需要读取完整的用户记录，所以针对获取到的每一条二级索引记录都执行一次回表操作，将完整的用户记录发送给客户端。这样就省去了给10000条记录排序的时间。

这里我们在执行查询语句时加了limit语句，如果不限制需要获取的记录数量，会导致为大量二级索引记录执行回表操作，这样会影响整体的性能。

2、使用联合索引进行排序时的注意事项

在使用联合索引时，需要注意：order by子句后面的列的顺序也必须按照索引列的顺序给出；如果给出order by key_part3,key_part2,key_part1的顺序，则无法使用B+树索引。

之所以颠倒排序列顺序就不能使用索引，原因还是联合索引中页面和记录的排序规则是规定的，即先按照key_part1值排序，如果记录的key_part1值相同，再按照key_part2值排序，如果记录的key_part1值和key_part2值都相同，再按照key_part3值排序。如果order by子句的内容是order by key_part3,key_part2,key_part1，那就要求先按照key_part3值排序，如果记录的key_part3值相同，再按照key_part2值排序，如果记录的key_part3值和key_part2值都相同，再按照key_part1

B+ 트리 리프 노드의 레코드는 작은 인덱스 열 값부터 큰 순서로 정렬되므로 특정 간격 또는 특정 간격으로 레코드를 스캔하는 것만으로도 스캔해야 하는 레코드 수를 크게 줄일 수 있습니다. 🎜🎜🎜쿼리 문의 경우: 🎜🎜

select * from single_table order by key1,,key2 limit 10;

🎜이 문은 실제로 id 값이 [2,100] 간격에 있는 모든 클러스터형 인덱스 레코드를 찾으려고 합니다. 클러스터링 클러스터 인덱스에 해당하는 B+ 트리는 id=2인 클러스터 인덱스 레코드를 빠르게 찾은 후 해당 레코드가 있는 단방향 연결 리스트를 따라 의 <code>까지 역방향으로 검색합니다. 클러스터형 인덱스 레코드 >id 값이 [2,100] 간격에 속하지 않을 때까지 클러스터형 인덱스 레코드를 모두 스캔하는 것에 비해 이 방법을 사용하면 스캔해야 하는 레코드 수가 크게 줄어듭니다. 따라서 쿼리 효율성이 향상됩니다. 🎜🎜사실 B+ 트리의 경우 인덱스 열과 상수가 =, <=>, in, not in, is null, is not null, >, <, >=을 사용하는 한 , &lt ;=, between, != 등 연산자를 연결하여 검색 간격을 생성함으로써 쿼리 효율성을 향상시킵니다. 🎜🎜2. 인덱스는 정렬에 사용됩니다🎜🎜쿼리 문을 작성할 때 특정 규칙에 따라 쿼리된 레코드를 정렬하기 위해 order by 절을 사용해야 하는 경우가 많습니다. 일반적인 상황에서는 레코드를 메모리에 로드한 다음 일부 정렬 알고리즘을 사용하여 이러한 레코드를 메모리에 정렬할 수만 있습니다. 때로는 쿼리 결과 집합이 너무 커서 메모리에 정렬할 수 없을 수도 있습니다. 이 경우 임시로 디스크 공간을 사용하여 중간 결과를 저장한 다음 정렬 작업이 완료된 후 정렬된 결과를 클라이언트에 반환해야 합니다. 🎜🎜MySQL에서는 이러한 메모리 또는 디스크 정렬 방법을 파일 정렬이라고 합니다. 그러나 인덱스 열을 order by 절에 사용하면 메모리 정렬의 필요성을 줄일 수 있습니다. 또는 디스크. 디스크 정렬 단계. 🎜

1. 다음 쿼리문을 분석하세요:

select key_part1,key_part2,key_part3,count(*) fron single_table group by key_part1,key_part2,key_part3;

🎜 이 쿼리문의 결과 집합은 기록된 key_part1<인 경우 <code>key_part1 값에 따라 정렬되어야 합니다. /code> 값 값이 동일하면 key_part2 값을 기준으로 정렬합니다. key_part1 값과 key_part2 값이 같습니다. 동일하게 설정한 다음 key_part3 값을 기준으로 정렬합니다. 우리가 설정한 조인트 인덱스 idx_key_part는 위의 규칙에 따라 정렬됩니다. 다음은 idx_key_part 인덱스를 단순화한 다이어그램입니다. 🎜🎜🎜🎜첫 번째 idx_key_part부터 시작하겠습니다. Secondary Index 녹화가 시작되면 해당 레코드가 위치한 단방향 연결리스트를 따라 역방향으로 스캔하여 10개의 Secondary Index 레코드를 얻는다. 쿼리 목록이 *이므로, 즉 전체 사용자 레코드를 읽어야 하므로 획득한 각 보조 인덱스 레코드에 대해 테이블 ​​반환 작업을 수행하고 전체 사용자 레코드를 클라이언트에 보냅니다. 이렇게 하면 10,000개의 레코드를 정렬하는 시간이 절약됩니다. 🎜🎜여기서 쿼리 문을 실행할 때 제한 문을 추가합니다. 가져올 레코드 수를 제한하지 않으면 많은 수의 보조 인덱스 레코드가 테이블에 반환되어 전체 성능에 영향을 미칩니다. . 🎜

2. 정렬을 위해 결합 색인을 사용할 때 주의할 사항

🎜결합 색인을 사용할 때 주의해야 할 점: order by 절 뒤의 열 순서 또한 인덱스 열을 따라야 합니다. 순서가 주어지면 order by key_part3, key_part2, key_part1 순서가 주어지면 B+ 트리 인덱스를 사용할 수 없습니다. 🎜🎜열 정렬 순서가 바뀌는 경우 인덱스를 사용할 수 없는 이유는 공동 인덱스의 페이지와 레코드에 대한 정렬 규칙이 규정되어 있기 때문입니다. 즉, 먼저 key_part1 값에 따라 정렬됩니다. . 레코드의 key_part1 값이 동일한 경우에는 key_part2 값에 따라 정렬합니다. key_part2 값이 동일하면 key_part3값 정렬에 따라 정렬합니다. order by 절의 내용이 order by key_part3,key_part2,key_part1인 경우 먼저 key_part3 값에 따라 정렬해야 합니다. .기록된 <코드>key_part3의 값이 동일한 경우, key_part2의 값에 따라 정렬됩니다. key_part2의 값이 동일하면 key_part2의 값에 따라 정렬됩니다. key_part1의 값이 정렬되는데 이는 분명히 충돌입니다. . 🎜

3、不可以使用索引进行排序的情况：

(1) ASC、DESC混用；

对于使用联合索引进行排序的场景，我们要求各个排序列的排序规则是一致的，也就是要么各个列都是按照升序规则排序，要么都是按照降序规则排序。

(2) 排序列包含非一个索引的列；

有时用来排序的多个列不是同一个索引中的，这种情况也不能使用索引进行排序，比如下面的查询语句：

select * from single_table order by key1,,key2 limit 10;

对于idx_key1的二级索引记录来说，只按照key1列的值进行排序，而且在key1列相同的情况下是不按照

key2列的值进行排序的，所以不能使用idx_key1索引执行上述查询。

(3) 排序列是某个联合索引的索引列，但是这些排序列在联合索引中并不连续；

(4) 排序列不是以单独列名的形式出现在order by子句中；

3、索引用于分组

有时为了方便统计表中的一些信息，会把表中的记录按照某些列进行分组。比如下面的分组查询语句：

select key_part1,key_part2,key_part3,count(*) fron single_table group by key_part1,key_part2,key_part3;

这个查询语句相当于执行了3次分组操作：

• 先按照key_part1值把记录进行分组，key_part1值相同的所有记录划分为一组；

• 将key_part1值相同的每个分组中的记录再按照key_part2的值进行分组，将key_part2值相同的记录放到一个小分组中，看起来像是在一个大分组中又细分了好多小分组。

• 再将上一步中产生的小分组按照key_part3的值分成更小的分组。所以整体上看起来就像是先把记录分成一个大分组，然后再把大分组分成若干个小分组，最后把若干个小分组再细分为更多的小分组。

上面这个查询语句就是统计每个小小分组包含的记录条数。

如果没有idx_key_part索引，就得建立一个用于统计的临时表，在扫描聚簇索引的记录时将统计的中间结果填入这个临时表。当扫描完记录后，再把临时表中的结果作为结果集发送给客户端。

如果有了idx_key_part索引，恰巧这个分组顺序又与idx_key_part的索引列的顺序一致，因此可以直接使用idx_key_part的二级索引进行分组，而不用建立临时表了。

与使用B+树索引进行排序差不多，分组列的顺序页需要与索引列的顺序一致，也可以值使用索引列中左边连续的列进行分组。

위 내용은 MySQL 인덱스의 역할은 무엇입니까의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!