고성능 MySQL-고성능 인덱스 생성에 대한 자세한 설명(그림 및 텍스트)

이 문서는 색인 생성에 관한 것입니다.
(1) 인덱스 종류
(2) 인덱스의 장점
(3) 인덱스 최적화 전략

인덱싱에 대한 마인드맵은 다음과 같습니다.

인덱스는 레코드 데이터 구조를 빠르게 찾기 위해 스토리지 엔진에서 사용하는 방법입니다. . 인덱스는 쿼리 성능을 최적화하는 가장 효과적인 수단입니다. 인덱스는 쿼리 성능을 몇 배나 쉽게 향상시킬 수 있습니다. 인덱싱 우리는 일반적으로 특정 열에 인덱스를 추가합니다.

스토리지 엔진은 먼저 인덱스에서 해당 값을 찾은 다음 일치하는 인덱스 레코드의 rowid를 기반으로 해당 데이터 행을 찾습니다. 예를 들어, 다음 쿼리문을 실행합니다.

SELECT first_name from actor where actor_id=5;

actor_id 열에 인덱스가 있으면 MySQL은 해당 인덱스를 사용하여 actor_id 5에 해당하는 행을 찾습니다. MySQL은 먼저 값으로 찾기 인덱스를 검색하고 해당 값을 포함하는 모든 행을 반환합니다.

인덱스에는 하나 이상의 열 값이 포함될 수 있습니다. 인덱스에 여러 열이 포함된 경우 MySQL은 가장 왼쪽 접두사 열만 효율적으로 사용할 수 있으므로 열의 순서도 매우 중요합니다. 인덱스의. 두 개의 열이 포함된 인덱스를 만드는 것과 하나의 열이 포함된 두 개의 인덱스를 만드는 것 사이에는 큰 차이가 있습니다.

1. 인덱스 데이터 구조의 종류:

가장 일반적인 인덱스는 B-Tree 인덱스와 해시 인덱스입니다.

(1) B-Tree 트리 인덱스

일반적으로 인덱스란 B-Tree 데이터 구조를 사용하여 데이터를 저장하는 B-Tree 인덱스를 말합니다. 실제로 이는 B+Tree를 기반으로 구현됩니다. 각 리프 노드에는 다음 리프 노드에 대한 포인터가 포함됩니다.

B-Tree는 모든 값이 순서대로 저장된다는 의미입니다. 예를 들어 name 속성 의 경우 a부터 z까지 순서대로 저장됩니다. B-Tree 인덱스를 사용한 후에는 스토리지 엔진이 더 이상 필요한 데이터를 얻기 위해 전체 테이블 스캔을 수행할 필요가 없습니다. 대신 인덱스의 루트 노드에서 검색하면 해당 값이 발견되거나 최종 결과가 나옵니다. 기록이 존재하지 않습니다. 이를 통해 데이터에 더 빠르게 액세스할 수 있습니다.

B-Tree는 인덱스 컬럼을 순차적으로 정리하여 저장하기 때문에 범위 데이터 검색에 매우 적합합니다. (예를 들어 I-k로 시작하는 이름을 검색하는 것이 매우 효율적입니다.)

B-Tree 인덱스에 적합한 쿼리 유형
(1) 전체 값 일치: 및 index 모든 열이 일치합니다.

(2) 가장 왼쪽 접두사 일치: 여러 열이 포함된 인덱스의 경우 인덱스의 첫 번째 열만 사용됩니다.

(3) 열 접두어 일치: 특정 열 값의 시작 부분을 일치시킵니다. (예를 들어 이름 필드를 일치시킬 경우 J로 시작하는 이름만 일치합니다.) 여기서는 인덱스의 첫 번째 열만 사용됩니다.

(4) 일치 범위 값: 여기에는 인덱스의 첫 번째 열만 사용되는 필드가 있는 레코드와 일치합니다.

(5) 특정 열과 범위가 정확히 일치하고 다른 열과 일치합니다. 예를 들어 인덱스에 여러 필드가 포함된 경우 첫 번째 열을 정확하게 일치시키고 두 번째 열의 범위와 일치합니다.

(6) 인덱스에만 접근하는 쿼리: 레코드에 있는 다른 필드의 데이터 행에 접근하지 않고 인덱스 행에만 접근합니다.

위의 범위 일치는 주로 인덱스가 인덱스 열을 순서대로 저장하기 때문에 범위 일치의 효율성이 높습니다.

B-Tree 인덱스에도 몇 가지 제한 사항이 있습니다.
(1) 인덱스는 가장 왼쪽 열에서만 검색할 수 있습니다.

(2) 특정 범위 검색이 있는 경우 열이 있는 경우 오른쪽에 있는 모든 열은 인덱스 최적화를 사용할 수 없습니다.

위의 두 가지 제한 사항을 보면 인덱스에 여러 열이 포함된 경우 인덱스 열의 순서가 매우 중요하다는 것을 이해할 수 있습니다.

(2) 해시 해시 인덱스

해시 인덱스는 해시 테이블을 기반으로 구현되며 인덱스의 모든 열과 정확히 일치하는 쿼리만 유효합니다. 데이터의 각 행에 대해 데이터 저장 엔진은 모든 인덱스 열에 대한 해시 코드를 계산합니다. 해시 코드는 더 작은 값이며, 서로 다른 키 값을 가진 행에 대해 계산된 해시 코드도 다릅니다.

1) 해시 인덱스는 해시 값과 행 포인터만 저장하고 특정 필드 값을 저장하지 않으므로 행을 읽는 과정이 있어야 합니다.

2) 해시 인덱스는 인덱스 값 순서대로 저장되지 않으므로 정렬용으로 사용할 수 없습니다.

3) 해시 인덱스는 동등 비교 쿼리만 지원하고 범위 비교 쿼리는 지원하지 않습니다. 이는 해시 테이블의 특성과 관련이 있습니다.

4) 해시 인덱스는 해시 충돌 문제가 있으며, 해시 충돌 데이터의 경우 연결 목록의 모든 행 포인터를 순회해야 합니다.

위의 제한으로 인해 해시 인덱스는 특정 경우에만 적합하지만 일단 해시 인덱스에 적합하면 성능이 특히 높아집니다.

해시 인덱스를 사용할 때 일반적으로 다음과 같이 쿼리 조건에서 해시 앞에 값을 추가해야 합니다.

mysql>select * from words where crc=crc32(‘gnu’) and word=’gnu’;

这里crc字段就是word字段哈希之后的值，因为hash之后可能存在冲突，带上原本的值做上二次比较，就可以精确定位。

2.索引的优点：

索引可以让服务器快速定位到表的指定位置。但是这不是唯一的作用，比如：
（1）对于B-Tree索引，由于B-Tree是按照顺序存储数据的，所以用来做order by 操作或则是 group by操作的效率很高。

（2）因为索引中存储了实际的列值，所以某些查询只需要索引就可以完成全部查询。

总结来说就是3点：
（1）索引大大减少服务器需要扫描的数据量；

（2）索引可以帮助服务器避免排序和临时表；

（3）索引可以将随机IO变为排序IO。

3.高性能的索引策略

先概括一下索引的策略：
 1）单列索引
 2）多列索引
 3）前缀索引
 4）聚簇索引
 5）覆盖索引

单列索引

所谓单列索引是指：使用数据表字段中的某一列作为索引。但是索引列不能是表达式的一部分，也不能是函数的参数。

比如对于下面的一个例子：

select actor_id from actor where actor_id+1=5;

对于这样的一个SQL，where语句后面 是一个表达式，其实很明显是actor_id=4的条件，但是MySQL却无法解析，索引无法正却使用索引。

还有一种是函数参数：也是无法正常的使用索引的

select ... where TO_DAYS(CURRENT_DATE) - TO_DAYS(date_col)<=10;

多列索引以及选择合适的索引顺序

注意这里要区分：为每个列创建独立的索引和为多个列创建一个索引的区别。

比如下面这种情况：

CREATE TABLE t{
c1 int,
c2 int,
c3 int,key(c1),key(c2),key(c3)
}

这一种就是为表中的3个列都创建了索引。

但是多个列创建索引就是：创建了一个索引，包含customer_id,和staff_id

alter table payment add KEY(customer_id, staff_id);

上面这个索引其实是包含了两个索引，一个是customer_id这个索引，还有一个是(customer_id，staff_id)。注意staff_id并不能作为单独的索引使用。

---

对于多列索引，最重要的就是怎么选择索引列的顺序，其实这一点与实际的查询需求有关。主要是为了满足排序和分组。

先从数据结构层次来分析，我们知道索引是以B-Tree的形式存储的，在一个多列索引列中，索引的顺序意味着索引首先按照最左列进行排序，其次是第二列。所以对于一个多列索引，如果以第二列或则第三列直接作为索引，基本是没有用到索引。由于索引的有序性很好的满足了order by、group by和distinct等子句的查询需求。

从上面的分析我们就能认识到多列索引中列的顺序是多么重要。关于多列索引中有一点经验法则：
（1）在不需要考虑排序和分组时，通常情况下将选择性最高的列放在索引最前列。(这时候索引只需要优化where查询条件，能够很快过滤出需要的行)

索引列的选择性定义：不重复的索引值和数据表的记录总数的比值。索引的选择性越高也就是查询效率越高。比如对于人员信息表，phone这一字段的选择性是很高的，几乎为1，但是对于sex性别这一字段的选择性是非常低的，因为只有两个选择男或则是女，几乎为0。

（2）实际情况下也与数据的分布有很大关系。
以下面的查询为例：

SELECT * FROM item WHERE staff_id=2 AND customer_id=584;

这时候应该创建（staff_id, customer_id）的索引还是应该创建（customer_id，staff_id）的索引呢？这时候就应该确认一下那个字段的选择性更高，先查询一下staff_id和customer_id的总数，哪个小就将哪个放在前面。

前缀索引

前缀索引：有时候需要索引的列可能会很长，这时候会导致索引大而且很慢，我们可以只索引列开始的部分（也就是只索引某一列的前面几个字符），这样可以大大节省索引空间也能加快索引的速度，但是也会降低索引的选择性（也就是索引查出来的结果会变多）。

使用的技巧在于：选择足够长的前缀保证较高的选择性，同时又不能太长，避免占用太多的存储空间。

클러스터형 인덱스

클러스터형 인덱스는 별도의 인덱스 형태가 아닌 데이터를 저장하는 방식입니다. 여기서는 클러스터형 인덱스를 설명하기 위해 주로 InnoDB를 예로 사용합니다.

InnoDB의 클러스터형 인덱스는 실제로 B-트리 인덱스와 데이터 행을 동일한 구조에 저장합니다. 테이블에 클러스터형 인덱스가 있는 경우 해당 데이터 행은 실제로 인덱스의 리프 페이지에 저장됩니다. 클러스터링의 의미는 실제로 인접한 B-Tree의 데이터 행과 키 값이 함께 콤팩트하게 저장된다는 것입니다. 데이터 행은 한 곳에만 저장할 수 있으므로 클러스터형 인덱스는 하나만 있을 수 있습니다.

다음은 설명하기 위한 예제 다이어그램입니다. 인덱스 열은 정수 값이고 리프 페이지에는 행의 모든 ​​데이터가 포함되지만 노드 페이지에는 인덱스 열(그림의 정수 값)만 포함됩니다. 아래에).

현재 버전의 MySQL에서 InnoDB의 클러스터형 인덱스는 기본 키를 사용하여 데이터를 클러스터링하는 것만 지원합니다. 기본 키가 정의되지 않은 경우 InnoDB는 대신 비어 있지 않은 고유한 인덱스를 선택합니다.

클러스터 데이터의 장점:
(1) 관련 데이터를 함께 저장할 수 있습니다. 예를 들어, 이메일 주소를 조회할 때 사용자 ID를 기본 키로 사용하고 사용자 ID별로 데이터를 클러스터링하는 방식으로 디스크에서 소수의 데이터 페이지만 읽어도 사용자의 모든 이메일을 얻을 수 있습니다.

(2) 데이터 액세스가 더 빨라졌습니다. 클러스터형 인덱스는 인덱스와 데이터를 B-트리에 저장하므로 일반적으로 클러스터형 인덱스에서 데이터를 검색하는 것이 동일한 인덱스를 조회하는 것보다 빠릅니다. (물론 검색열이 인덱스열인 경우도 있습니다)

(3) 커버링 인덱스 스캔을 이용한 쿼리는 페이지 노드의 기본키를 직접 사용할 수 있습니다.

위의 장점은 테이블 쿼리 및 설계 시 성능을 크게 향상시킬 수 있지만 몇 가지 단점도 있습니다.
(1) 클러스터링된 데이터는 IO 집약적인 애플리케이션의 성능을 크게 향상시키지만, 모든 데이터를 메모리에서는 액세스 순서가 중요하지 않으며 클러스터형 인덱스는 이점을 잃습니다.

(2) 삽입 속도는 삽입 순서에 따라 크게 달라집니다.

(3) 클러스터형 인덱스 열을 업데이트하는 데는 비용이 많이 들고 업데이트된 InnoDB의 각 행을 새 위치로 이동해야 합니다.

커버링 인덱스

인덱스가 쿼리해야 하는 모든 필드의 값을 포함(또는 커버)하는 경우 이를 커버링이라고 부릅니다. 색인.

인덱스의 경우 테이블을 다시 쿼리할 필요 없이 인덱스를 스캔하여 인덱스의 리프 노드에 있는 모든 데이터를 가져오기만 하면 됩니다. 이는 성능을 크게 향상시킬 수 있습니다. 또한 많은 이점이 있습니다.

(1) 인덱스 항목은 일반적으로 데이터 행의 크기보다 훨씬 작습니다. 인덱스를 읽기만 하면 되는 경우 MySQL은 데이터 액세스 양을 크게 줄여줍니다. 캐시에 과도한 부하를 가하는 것이 매우 중요합니다.

(2) 인덱스는 열 값 순서로 저장되므로 IO 집약적인 범위 검색은 디스크에서 데이터의 각 행을 무작위로 읽는 것보다 훨씬 적은 Io를 필요로 합니다.

정렬을 위한 인덱스 스캔

MySQL에는 정렬된 결과를 생성하는 두 가지 방법이 있습니다.
(1) 정렬 작업을 통해

( 2) 인덱스 순서로 스캔; >설명된 유형 값이 index인 경우 MySQL은 정렬을 위해 인덱스 스캐닝을 사용한다는 의미입니다.

인덱스 스캔 자체는 매우 빠릅니다. 그러나 인덱스 열이 모든 쿼리 필드를 포함할 수 없는 경우 인덱스 레코드를 스캔할 때마다 테이블로 돌아가서 쿼리하면 직접 순차 전체 테이블 스캔만큼 성능이 좋지 않습니다.

최대한 정렬과 검색을 모두 만족할 수 있도록 동일한 색인을 디자인하세요.

4. 인덱스의 단점

(1) 삽입, 업데이트, 삭제 작업의 경우 인덱스를 동기적으로 업데이트해야 하므로 속도가 느려진다.

(2) 인덱스는 많은 저장 공간을 차지합니다.

위 내용은 고성능 MySQL-고성능 인덱스 생성에 대한 자세한 설명(그림 및 텍스트)의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!