MySql 인덱스 항목

오늘은 MySql 인덱스에 대해 이야기하겠습니다. 이번 글에서는 주로 InnoDB에서 인덱스의 데이터 구조, 인덱스 작동 방식, 인덱스를 더 잘 활용하여 효율성을 높이는 방법에 대해 이야기하겠습니다.

1. 인덱스란 무엇입니까?

데이터베이스 인덱스는 데이터베이스 테이블의 데이터를 빠르게 쿼리하고 업데이트하는 데 도움이 되는 데이터베이스 관리 시스템의 정렬된 데이터 구조입니다. 이전에 사용했던 신화사전의 목차와 마찬가지로 특정 단어를 빠르게 검색하는 데 도움이 됩니다.

2. 인덱스 분류

분류 각도	인덱스 이름
데이터 구조	B+ 트리, 해시 인덱스, R-Tree 등
저장 수준	클러스터 에드 인덱스, 비 -클러스터드 인덱스 클러스터드 인덱스
논리레벨	기본키 인덱스, 일반 인덱스, 복합 인덱스, 고유 인덱스, 공간 인덱스 등

3. 인덱스 예시 분석(InnoDB를 예로 들어)

3.1 InnoDB의 인덱스 구조

InnoDB에서는 기본 키 순서에 따라 테이블이 인덱스 형태로 저장된다. 클러스터링 인덱스라고도 불리는 "클러스터링"은 데이터 행과 인접한 키 값이 함께 콤팩트하게 저장된다는 의미입니다. 즉, 데이터 행이 실제로 인덱스의 리프 페이지에 저장됩니다. InnoDB의 인덱스 구조를 실제로 설명하기 위해 테이블을 생성합니다. 테이블 생성 문은 다음과 같습니다.

create table person(id int primary key, 
age int not nullindex (age)engine=InnoDB;

그런 다음 (1,15), (2,17), (6,20), (10,18),(19,21)에서 인덱스의 트리 구조는 다음과 같습니다.

위 그림은 내용의 두 부분을 보여줍니다. 첫 번째 그림은 클러스터형 인덱스(기본 키)의 내용을 보여줍니다. index) 데이터가 ID 크기에 따라 정렬되어 해당 인덱스에 해당 인덱스의 전체 데이터 행이 포함되는 것을 볼 수 있습니다.

두 번째 사진은 age를 인덱스로 한 인덱스 구조도인데, 이는 논클러스터형 인덱스(비기본키 인덱스)인데, 기본키 인덱스와는 달리 인덱스가 age별로 정렬되어 있는 것을 볼 수 있습니다. age 인덱스는 Id에 해당하므로 비기본키 인덱스 레코드의 내용이 기본키 인덱스의 값임을 알 수 있습니다.

여기서 질문하는 학생들이 있을 수 있습니다. 테이블을 생성할 때 기본 키를 지정하지 않으면 인덱스 구조가 어떻게 되나요? 실제로 InnoDB에서는 기본 키가 정의되지 않은 경우 대신 비어 있지 않은 고유한 인덱스를 선택합니다. 그러한 인덱스가 없으면 기본 키를 클러스터형 인덱스로 암시적으로 정의합니다. 따라서 기본 키 설정 여부에 관계없이 InnoDB는 위에 표시된 형식으로 데이터를 색인화하는 데 계속 도움을 줍니다. 다음으로 인덱스 쿼리 프로세스를 분석합니다.

3.2 인덱스 쿼리 분석

ID = 6인 select * from person 쿼리문을 실행한다고 가정합니다. 기본 키 ID 쿼리가 직접 사용되므로 기본 키 인덱스가 직접 연결되므로 기본 키 인덱스가 사용됩니다. 전체 행의 모든 ​​데이터이므로 엔진은 결과를 쿼리하기 위해 한 번만 실행하면 됩니다.

실행된 sql 문이 기본키가 아닌 인덱스인 경우

select * from person where age = 18

일반 인덱스에서 기본키 인덱스를 찾아 그 데이터를 쿼리하는 과정을 테이블 리턴이라고 합니다. 테이블을 반환하려면 쿼리가 하나 더 필요하므로 기본 키 인덱스가 일반 인덱스보다 빠르므로 최대한 기본 키 쿼리를 사용하도록 노력해야 합니다. 위 명령문은 age의 일반 인덱스를 사용합니다. 인덱스는 먼저 age를 기준으로 18인 인덱스 레코드를 검색하고 ID = 10인 레코드를 찾은 다음 기본 키 인덱스에서 한 번 검색한 다음 해당 레코드를 꺼냅니다. 쿼리해야 하는 데이터입니다.

3.3 Covering Index

우리는 일반적으로 쿼리의 where 조건을 기반으로 인덱스를 생성하지만 이는 단지 일반적인 관행일 뿐입니다. 위의 분석을 통해 쿼리 효율성을 높이려면 먼저 다음을 사용하는 것을 알 수 있습니다. 기본 키 인덱스, 둘째, 테이블 반환을 피하십시오. 즉, 인덱스에서 원하는 데이터를 최대한 얻으십시오. 인덱스에 쿼리해야 하는 필드가 포함되어 있는 경우 이를 "커버링 인덱스"라고 합니다.

그럼 커버링 인덱스는 어떻게 생성하나요? 대답은 공동 인덱스를 통해 이를 달성하는 것입니다. 쿼리할 필드는 인덱스 적용 범위의 효과를 달성하기 위해 공동 인덱스의 필드에 포함됩니다.

我们把上面的建表语句改造下，来分析下如何实现覆盖索引。

 CREATE TABLE `person` (  `id` int(11) NOT NULL,  `age` int(11) DEFAULT NULL,  `name` varchar(20) DEFAULT NULL,  `sex` varchar(1) DEFAULT NULL,

上面我创建了一个name和age的联合索引，索引结构图表示如下：

我们根据图可以知道，联合索引是和创建索引字段顺序有关的，上面这个例子就是先以name排序，然后name相同再以age为标准排序。那么我们建表后该如何达到覆盖索引的效果呢？相信有些同学已经知道了怎么写sql可以达到覆盖索引效果，sql如下：

select name,age from person where name = "Barry"

因为我们需要查询的字段name和age，都在索引中可以直接查询到了，所以不需要查找到主键ID，然后再回表了。

看到这里，肯定有同学会说，既然这样的话，我把所有需要查询的字段组合都建上联合索引不就行了吗？答案是：不行。因为索引也是需要消耗空间的，而且维护索引也是需要成本的，这一点我会在后面的优缺点中提到。那么有没有别的方式可以尽可能的实现不回表的效果呢？这里我们就要引入MySql的最左前缀原则了。

什么叫最左前缀原则呢？就是在索引的匹配中，可以以索引的最左N个字段,也可以是字符串索引的最左N个字符。比如在上图中，要查询以A开头的名字，查询语句就是

<span style="font-family: "Microsoft Yahei", "Hiragino Sans GB", Helvetica, "Helvetica Neue", 微软雅黑, Tahoma, Arial, sans-serif; white-space: normal;">select name from person where name like &#39;A%&#39;</span><br/>

这个时候就可以满足最左前缀规则来使用索引查询了，这里就会依赖索引查询到第一个首字母是A的名字，然后向后遍历，直到不满足条件为止。

那么最左N个字段是什么意思呢？意思就是索引（name,age),可以直接利用 name来当做单独索引使用，可以只使用联合索引的部分字段，但是必须是顺序一致，比如索引（a,b,c)，如果要想使用最左前缀规则，可以使用索引a,ab。

我们也可以利用该规则来少维护一个或多个索引，比如我们需要 a,ab,abc的查询，那就只需要(a,b,c)联合索引就满足要求了。

3.4 索引下推

在MySql 5.6版本中引入了一个新特性，叫做“索引条件推送（index condition pushdown)”，这也称为索引下推。那么索引下推是这个什么东东呢？其实从“索引条件推送”这个名字就可以表明，这个特性是可以在索引中的字段进行条件判断，然后过滤不满足条件的记录，减少回表的次数。

比如以上图中的数据为准，sql如下：

<span style="font-family: "Microsoft Yahei", "Hiragino Sans GB", Helvetica, "Helvetica Neue", 微软雅黑, Tahoma, Arial, sans-serif; white-space: normal;">select * from person where name like &#39;A%&#39; and age =19;</span><br/>

那么如果没有索引下推的情况下，首先会根据索引查询出名字以A开头的所有记录，然后查询出ID，然后回表去查询对应的ID记录，最后再判断age=19，返回满足条件的语句。因为满足A开头的记录有2条，所以这种情况下，会回表2次。

在索引下推情况下，InnoDB会在索引内部直接判断age=19是否满足条件，过滤掉不满足条件的记录，所以只返回了一条，也就是只需要回表一次。从而提高了性能。

3.5 索引的优点与缺点

说了这么多关于索引的内容，我们来谈谈索引的优缺点。

优点：

减少服务器需要扫描的数据量索引可以帮助服务器避免排序和临时表索引可以将随机IO变为顺序IO

缺点

索引会占用额外的存储空间索引的维护需要一定的成本，插入数据后需要保证原来的索引有序，所以也会影响一定的数据库性能。

五、总结

이번 블로그 포스팅에서는 주로 지수의 정의, 지수의 분류, 다양한 관점에 따른 지수의 일반적인 유형에 대해 이야기했습니다. 그런 다음 InnoDB에 인덱스된 인덱스의 데이터 구조에 중점을 두었습니다. 기본 키 인덱스와 비기본 키 인덱스의 차이점은 기본 키 인덱스를 쿼리하면 데이터를 직접 반환할 수 있다는 점입니다. 비기본 키 인덱스는 먼저 기본 키 ID를 쿼리한 다음 데이터를 쿼리해야 합니다. . 인덱스를 커버함으로써 테이블 반환 횟수를 줄여 성능을 향상시킬 수 있습니다. mysql5.6 이후에는 InnoDB에서 인덱스 푸시다운을 지원할 수 있다. 조인트 인덱스를 사용할 때 인덱스에서 조건을 판단할 수 있으면 조건에 맞지 않는 행을 인덱스에서 필터링하여 테이블 반환 횟수를 줄인다.

6. 참고

"고성능 MySql" 3판

"MySql45 강의" 칼럼

[추천 과정: MySQL 동영상 튜토리얼]

위 내용은 MySql 인덱스 항목의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!