MySQL 인덱스의 구문은 무엇입니까

인덱스 정의

인덱스는 MySQL이 데이터를 효율적으로 얻는 데 도움이 되는 정렬된 데이터 구조입니다. 이는 MySQL의 인덱스에 대한 공식 정의입니다. 쿼리 효율성을 높이기 위해 인덱스는 데이터베이스 테이블의 필드에 추가되는 메커니즘입니다. 데이터 외에도 데이터베이스 시스템은 특정 검색 알고리즘을 충족하는 데이터 구조를 유지하므로 이러한 데이터 구조에서 고급 검색 알고리즘을 구현할 수 있습니다. 색인. . 아래 다이어그램과 같이

사실 간단히 말하면 인덱스는 정렬된 데이터 구조입니다.

왼쪽은 총 2개의 열과 7개의 레코드로 구성된 데이터 테이블이고 가장 왼쪽은 하나는 데이터 레코드 주소의 물리적 구조입니다(논리적으로 인접한 레코드가 디스크에서 반드시 물리적으로 인접한 것은 아닙니다). Col2 검색 속도를 높이기 위해 오른쪽과 같이 이진 검색 트리를 유지할 수 있습니다. 각 노드에는 인덱스 키 값과 해당 데이터 레코드의 물리적 주소에 대한 포인터가 포함되어 있습니다. 해당 데이터를 빠르게 얻으려면 이진 검색을 사용하십시오.

인덱스의 장점

• 검색 및 정렬 속도를 높이고, 데이터베이스의 IO 비용과 CPU 소비를 줄입니다.

• 고유한 인덱스를 생성하여 각각의 고유성을 보장할 수 있습니다. 데이터베이스 테이블의 데이터 행입니다.

인덱스의 단점

1. 인덱스는 실제로 테이블이므로 기본 키와 인덱스 필드를 저장하고 엔터티 클래스의 레코드를 가리킵니다. 그 자체로 공간을 차지해야 합니다

2. . 쿼리 효율성을 높이는 것은 추가, 삭제, 수정입니다. 테이블이 변경될 때마다 인덱스를 업데이트해야 합니다. 신규: 당연히 인덱스 트리에 새로운 노드를 추가해야 합니다. 인덱스 트리가 유효하지 않게 될 수 있습니다. 이는 이 인덱스 트리의 많은 노드가 유효하지 않음을 의미합니다. 변경: 인덱스 트리에 있는 노드의 포인터 를 변경해야 할 수도 있습니다

그러나 실제로는 사용하지 않습니다. 이진 검색 트리를 MySQL에 저장하는 이유는 무엇인가요?

이진 검색 트리에서 여기의 노드는 하나의 데이터만 저장할 수 있고 노드는 MySQL의 디스크 블록에 해당합니다. 이런 식으로 디스크 블록을 읽을 때마다 우리는 데이터만 저장할 수 있습니다. 하나의 데이터를 얻으면 효율성이 특히 낮기 때문에 B-tree 구조를 사용하여 저장하는 것을 고려해 보겠습니다.

인덱스 구조

인덱스는 서버 계층이 아닌 MySQL의 스토리지 엔진 계층에서 구현됩니다. 따라서 인덱스는 저장소 엔진마다 다를 수 있으며 모든 엔진이 모든 유형의 인덱스를 지원하는 것은 아닙니다.

• BTREE index: 가장 일반적인 인덱스 유형으로, 대부분의 인덱스는 B-트리 인덱스를 지원합니다.

• HASH Index: 메모리 엔진에서만 지원되며 사용 시나리오는 간단합니다.

• R-트리 인덱스(공간 인덱스): 공간 인덱스는 MyISAM 엔진의 특수 인덱스 유형으로 주로 지리 공간 데이터 유형에 사용되며 특별히 소개되지 않습니다.

• 전체 텍스트 인덱스(full-text index): 전체 텍스트 인덱스는 MyISAM의 특수 인덱스 유형이기도 하며 주로 전체 텍스트 인덱스에 사용됩니다. InnoDB는 Mysql5.6 버전부터 전체 텍스트 인덱스를 지원합니다.

MyISAM, InnoDB, Memory 3가지 스토리지 엔진이 다양한 인덱스 유형을 지원합니다

index	INNODB 엔진	MYISAM 엔진	메모리 엔진
BTREE index	지원됨	지원됨	지원됨
HASH 인덱스	지원되지 않음	지원되지 않음	지원됨
R-트리 index	지원되지 않음	지원되지 않음	지원되지 않음
전체 텍스트	버전 5.6 이후 지원	지원됨	지원되지 않음

우리가 일반적으로 참조하는 인덱스는 명시적으로 명시하지 않는 한 B+ 트리(다중 검색 트리, 반드시 이진일 필요는 없음) 구조를 사용하여 구성됩니다. 클러스터형 인덱스, 복합 인덱스, 접두사 인덱스, 인덱스라고 불리는 고유 인덱스는 모두 기본적으로 B+ 트리 인덱스를 사용합니다.

BTREE

다방향 균형 검색 트리, m-차수(m-fork) BTREE는 다음을 충족합니다.

• 노드당 최대 m개의 하위 하위 항목 수: ceil(m/2) ~ m 키워드 수: ceil (m/2)-1에서 m-1

ceil은 반올림을 의미합니다. ceil(2.3)=3

키워드 case

를 삽입하여 m-차수 B-의 속성을 확인하세요. 트리는 파괴되지 않습니다

레벨 3으로 인해 최대 2개의 노드만 있을 수 있으므로 처음에는 26과 30이 함께 있다가 85가 분할되기 시작하면 30이 상단 중간 위치가 되고 26이 남게 됩니다. 85는 오른쪽으로 갈 것입니다
즉: 중간 위치 위쪽 위치 , 그러면 왼쪽은 이전 노드에 머물고 오른쪽은 새 노드로 이동합니다

그림에 70이 다시 삽입되면, 70이 중간에 상위 위치에 있고 62가 남고 85가 새로운 노드로 나누어집니다

상위 레벨에 도달한 후에는 다시 분할해야 합니다

계속 위쪽으로 분할하면 됩니다. 마찬가지로

비교 장점

이진 검색 트리에 비해 높이/깊이가 낮고 자연 쿼리 효율성이 높습니다.

B+TREE

• B+ 트리에는 내부 노드(인덱스 노드라고도 함)와 리프 노드라는 두 가지 유형의 노드가 있습니다. 내부 노드는 리프가 아닌 노드입니다. 내부 노드는 데이터를 저장하지 않고 인덱스만 저장하며 리프 노드에는 데이터가 저장됩니다.

• 내부 노드의 키는 작은 것부터 큰 것의 순서로 배열됩니다. 내부 노드의 키는 왼쪽 트리의 모든 키가 그보다 작고, 오른쪽 하위 트리의 모든 키가 더 큽니다. 또는 그것과 같습니다. 리프 노드의 레코드도 키 크기에 따라 정렬됩니다.

• 각 리프 노드는 인접한 리프 노드에 대한 포인터를 저장합니다. 리프 노드 자체는 키워드의 크기에 따라 작은 것부터 큰 것 순서로 연결됩니다.

부모 노드는
• 오른쪽 자식의 첫 번째 요소에 대한 인덱스

  를 저장합니다.

장점에 비해

B+Tree의 쿼리 효율성
• 이 더 안정적입니다

  . B+Tree의 리프 노드에만 키 정보가 저장되므로 키를 쿼리하려면 루트에서 리프로 이동해야 하므로 더 안정적입니다.

전체 트리를 탐색하려면 리프 노드만 탐색하면 됩니다.
MySQL의 B+Tree

MySql 인덱스 데이터 구조는 클래식 B+Tree를 최적화합니다. 원본 B+Tree를 기반으로 인접한 리프 노드를 가리키는 연결리스트 포인터(전체 구조는 이중 연결리스트와 유사)를 추가하여 순차 포인터를 갖춘 B+Tree를 형성하여 간격 성능을 향상시킵니다. 입장.

주의 깊은 학생들은 이 그림과 이진 검색 트리 다이어그램의 가장 큰 차이점이 무엇인지 알 수 있습니까?

이진 검색 트리에서 B-트리

로 전환하면서 중요한 변화는 하나의 노드가 여러 데이터를 저장할 수 있다는 것입니다. 이는 하나의 디스크 블록이 여러 데이터를 저장할 수 있는 것과 동일하므로 IO 빈도가 크게 줄어듭니다! ! MySQL의

• B+트리 인덱스 구조 다이어그램 :

Binary Search Tree Diagram :

index arperile

btree index :

initialization 소개 블루 블루는 디스크 블록으로 불립니다. 각 디스크 블록에는 여러 데이터 항목(진한 파란색으로 표시)과 포인터(노란색으로 표시)가 포함되어 있음을 알 수 있습니다. 예를 들어 디스크 블록 1에는 데이터 항목 17과 35가 포함되어 있으며 포인터 P1, P2 및 P3이 포함되어 있습니다. 17보다 작은 디스크 블록을 나타내고, P2는 17에서 35 사이의 디스크 블록을 나타내고, P3은 35보다 큰 디스크 블록을 나타냅니다.

실제 데이터는 리프 노드

즉, 3, 5, 9, 10, 13, 15, 28, 29, 36, 60, 75, 79, 90, 99에 존재합니다. `

논리프 노드는 실제 데이터를 저장하지 않으며, 17, 35 등
• 검색 방향을 안내하는 데이터 항목

  만 실제로 데이터 테이블에 존재하지 않습니다. `

검색 프로세스

데이터 항목 29를 찾으려면 먼저 디스크 블록 1이 디스크에서 메모리로 로드되고 이때 IO가 발생합니다. 메모리에서 이진 검색을 사용하여 29가 17과 35 사이인지 확인하고 디스크 블록 1의 P2 포인터를 잠급니다. 메모리 시간은 매우 짧기 때문에 무시할 수 있습니다(디스크의 IO에 비해). 디스크 블록 1의 P2 포인터 주소가 디스크에서 메모리로 로드됩니다. 두 번째 IO는 26과 30 사이에서 발생합니다. 디스크 블록 3의 P2 포인터가 잠겨 있습니다. 세 번째 IO가 발생함과 동시에 메모리가 통과합니다. 이진 검색이 29에 도달하고 쿼리가 종료되어 총 3개의 IO가 발생합니다.

실제 상황은 3계층 B+ 트리가 수백만 개의 데이터를 나타낼 수 있다는 것입니다. 수백만 개의 데이터 검색에 3개의 IO만 필요한 경우 인덱스가 없으면 모든 데이터 항목을 검색해야 합니다. . 하나의 IO에는 총 수백만 개의 IO가 필요하며 이는 분명히 매우 비쌉니다.

인덱스 분류

인덱스 구성 테이블은 기본 키 순서로 인덱스로 저장되는 테이블입니다. 이 방법은 InnoDB 엔진에 적합합니다. InnoDB는 B+ 트리 인덱스 모델을 사용하므로 데이터는 B+ 트리에 저장됩니다.

각 인덱스는 InnoDB의 B+ 트리에 해당합니다.
기본 키 열이 ID인 테이블이 있고 테이블에 필드 k가 있고 k에 대한 인덱스가 있다고 가정해 보겠습니다.
이 테이블의 테이블 생성문은 다음과 같습니다.

mysql> create table T( 
  id int primary key, 
  k int not null,  
  name varchar(16), 
  index (k))engine=InnoDB; 
复制代码

테이블의 R1~R5의 (ID,k) 값은 (100,1), (200,2), (300,3), (500,5)와 (600,6), 두 트리의 예시 다이어그램은 다음과 같습니다.

리프 노드의 내용에 따라 인덱스 유형이 구분되는 것을 그림에서 쉽게 알 수 있습니다. 기본키 인덱스와 비기본키 인덱스로 나뉜다.

기본 키 인덱스

데이터 테이블의 기본 키 열은 기본 키 인덱스를 사용하여 기본적으로 생성되기 때문에 인덱싱을 배우기 전에 선생님께서 기본 키를 기준으로 검색하는 것이 더 빠르다고 자주 말씀하셨습니다. . 기본 키 자체가 인덱스가 구축된 것으로 나타났습니다.
기본 키 인덱스의 리프 노드는 전체 데이터 행을 저장합니다. InnoDB에서는 기본 키 인덱스를 clustered index(클러스터형 인덱스)라고도 합니다.

보조 인덱스

보조 인덱스의 리프 노드 내용은 기본 키의 값입니다. InnoDB에서는 보조 인덱스를 secondary index(보조 인덱스)라고도 합니다.

아래와 같이:

• 기본 키 인덱스는 데이터의 전체 행

• 보조 인덱스는 자신만 저장하며 id 기본 키는 테이블 반환 쿼리에 사용됩니다

위의 인덱스 구조에 따라 다음 질문에 대해 토론해 보겠습니다. 기본 키 인덱스 기반 쿼리와 보조 인덱스 기반 쿼리의 차이점은 무엇인가요?

• 문이 select * from T(ID=500), 즉 기본 키 쿼리 방법인 경우 ID의 B+ 트리만 검색하면 됩니다.

• 문이 select * from; T 여기서 k=5, 즉 일반적인 인덱스 쿼리 방법에서는 먼저 k 인덱스 트리를 검색하여 ID 값 500을 얻은 다음 ID 인덱스 트리에서 다시 검색해야 합니다. 이 프로세스를 Return to table이라고 합니다.

즉, 보조 인덱스 기반 쿼리는 인덱스 트리를 하나 더 스캔해야 합니다. 그러므로 우리는 애플리케이션에서 기본 키 쿼리를 사용하도록 노력해야 합니다.

단, 쿼리하려는 데이터가 인덱스 트리에 존재하는 경우 이를 커버링 인덱스라고 합니다. 즉, 인덱스 열에는 쿼리하려는 모든 데이터가 포함됩니다.

동시에 보조 인덱스는 다음 유형으로 나뉩니다(지금은 건너뛰고 나중에 자세히 알아보겠습니다).

• 고유 키: 고유 키도 제약 조건입니다. 고유 인덱스의 속성 열에는 중복 데이터가 나타날 수 없지만, 데이터는 NULL일 수 있습니다. 테이블에서는 여러 고유 인덱스를 생성할 수 있습니다. 대부분의 경우 고유 인덱스를 설정하는 목적은 쿼리 효율성보다는 속성 열에 있는 데이터의 고유성을 위한 것입니다.

• 일반 인덱스(Index): 일반 인덱스의 유일한 기능은 데이터를 빠르게 쿼리하는 것입니다. 테이블을 사용하면 여러 일반 인덱스를 생성할 수 있고 데이터 중복 및 NULL이 가능합니다.

• Prefix Index(Prefix): Prefix Index는 문자열 유형 데이터에만 적용 가능합니다. 접두사 인덱스는 텍스트의 처음 몇 글자에 대한 인덱스를 생성하며, 일반 인덱스에 비해 처음 몇 글자만 가져오기 때문에 생성되는 데이터가 더 작습니다.

• 전체 텍스트 색인(Full Text): 전체 텍스트 색인은 주로 대용량 텍스트 데이터에서 키워드 정보를 검색하는 데 사용됩니다. 현재 검색 엔진 데이터베이스에서 사용하는 기술입니다. Mysql5.6 이전에는 MYISAM 엔진만 전체 텍스트 인덱싱을 지원했습니다. 5.6 이후에는 InnoDB도 전체 텍스트 인덱싱을 지원합니다

확장--인덱스 푸시다운

소위 푸시다운은 이름에서 알 수 있듯이 실제로 테이블 반환 작업을 연기합니다. MySQL은 매우 낭비적이기 때문에 쉽게 테이블 반환을 허용하지 않습니다. 그게 무슨 뜻이에요? 다음 예를 고려하십시오.

그림과 유사하게 이 필드에 따라 저장되는 복합 인덱스(이름, 상태, 주소)를 설정했습니다.

복합 인덱스 트리(테이블 반환을 위한 인덱스 열과 기본 키만 저장)

我们执行这样一条语句：

SELECT name FROM tb_seller WHERE name like '小米%' and status ='1' ;
复制代码

1. 首先我们在复合索引树上，找到了第一个以小米开头的name -- 小米1

2. 此时我们不着急回表(回到主键索引树搜索的过程，我们称为回表)，而是先在复合索引树判断status是否=1，此时status=0，我们直接就不回表了，直接继续找下一个以小米开头的name

1. 找到第二个-- 小米2，判断status=1，则根据id=2去主键索引树上找，得到所有的数据

这种先在自身索引树上判断是否满足其他的where条件，不满足则直接pass掉，不进行回表的操作，就叫做索引下推。

最左前缀原则

所谓最左前缀，可以想象成一个爬楼梯的过程，假设我们有一个复合索引：name，status，address，那这个楼梯由低到高依次顺序是：name，status，address，最左前缀，要求我们不能出现跳跃楼梯的情况，否则会导致我们的索引失效：

1. 按楼梯从低到高，无出现跳跃的情况--此时符合最左前缀原则，索引不会失效

   

2. 出现跳跃的情况

• 直接第一层name都不走，当然都失效

  

• 走了第一层，但是后续直接第三层，只有出现跳跃情况前的不会失效（此处就只有name成功）

  

• 同时，这个顺序并不是由我们where中的排列顺序决定，比如： where name='小米科技' and status='1' and address='北京市' where status='1' and name='小米科技' and address='北京市'

这两个尽管where中字段的顺序不一样，第二个看起来越级了，但实际上效果是一样的

其实是因为我们MySQL有一个Optimizer（查询优化器），查询优化器会将SQL进行优化，选择最优的查询计划来执行。

• 关于这个查询优化器，后续文章我们也会谈谈MySQL的逻辑架构与存储引擎

索引设计原则

针对表

1. 查询频次高，且数据量多的表

针对字段

1. 最好从where子句的条件中提取，如果where子句中的组合比较多，那么应当挑选最常用、过滤效果最好的列的组合。

其他原则

1. 最好用唯一索引，区分度越高，使用索引的效率越高

2. 不是越多越好，维护也需要时间和空间代价，建议单张表索引不超过 5 个

因为 MySQL 优化器在选择如何优化查询时，会根据统一信息，对每一个可以用到的索引来进行评估，以生成出一个最好的执行计划，如果同时有很多个索引都可以用于查询，就会增加 MySQL 优化器生成执行计划的时间，同样会降低查询性能。

比如：

我们创建了三个单列索引，name，status，address

当我们where中根据status和address两个字段来查询时，数据库只会选择最优的一个索引，不会所有单列索引都使用。

最优的索引：具体是指所查询表中，辨识度最高(所占比例最少)的索引列，比如此处address中有一个辨识度很高的 '西安市'数据；

1. 使用短索引,索引创建之后也是使用硬盘来存储的，因此提升索引访问的I/O效率，也可以提升总体的访问效率。假如构成索引的字段总长度比较短，那么在给定大小的存储块内可以存储更多的索引值，相应的可以有效的提升MySQL访问索引的I/O效率。

2. 利用最左前缀，比如有N个字段，我们不一定需要创建N个索引，可以用复合索引

也就是说，我们尽量创建复合索引，而不是单列索引

创建复合索引:
	CREATE INDEX idx_name_email_status ON tb_seller(name,email,status);

就相当于
	对name 创建索引 ;
	对name , email 创建了索引 ;
	对name , email, status 创建了索引 ;
复制代码

举个栗子

假设我们有这么一个表，id为主键，没有创建索引：

CREATE TABLE `tuser` (
  `id` int(11) NOT NULL,
  `name` varchar(32) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB
复制代码

如果要在此处建立复合索引，我们要遵循什么原则呢？

通过调整顺序，可以少维护一个索引

• 比如我们的业务需求里边，有如下两种查询方式： 根据name查询 根据name和age查询

如果我们建立索引（age,name），由于最左前缀原则，我们这个索引能实现的是根据age，根据age和name查询，并不能单纯根据name查询（因为跳跃了），为了实现我们的需求，我们还得再建立一个name索引；

而如果我们通过调整顺序，改成（name，age），就能实现我们的需求了，无需再维护一个name索引，这就是通过调整顺序，可以少维护一个索引。

考虑空间->短索引

• 比如我们的业务需求里边，有以下两种查询方式： 根据name查询 根据age查询 根据name和age查询

我们有两种方案：

1. 建立联合索引(name,age)，建立单列索引：age索引。

2. 建立联合索引(age,name)，建立单列索引：name索引。

这两种方案都能实现我们的需求，这个时候我们就要考虑空间了，name字段是比age字段大的，显然方案1所耗费的空间是更小的，所以我们更倾向于方案1。

何时建立索引

1. where中的查询字段

2. 查询中与其他表关联的字段，比如外键

3. 排序的字段

4. 统计或分组的字段

何时达咩索引

1. 表中数据量很少

2. 经常改动的表

3. 频繁更新的字段

4. 数据重复且分布均匀的表字段（比如包含了很多重复数据，那此时多叉树的二分查找，其实用处不大，可以理解为O(logn)退化了）

索引相关语法

创建索引

默认会为主键创建索引--primary

CREATE 	[UNIQUE|FULLTEXT|SPATIAL]  INDEX index_name 
[USING  index_type]
ON tbl_name(index_col_name,...)

index_col_name : column_name[(length)][ASC | DESC]
复制代码

查找索引

结尾加上\G，可以变成竖屏显示

select index from tbl_name\G;
复制代码

删除索引

drop INDEX index_name on tbl_name ;
复制代码

变更索引

1). alter  table  tb_name  add  primary  key(column_list); 
	该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL	
	
2). alter  table  tb_name  add  unique index_name(column_list);
	这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）
	
3). alter  table  tb_name  add  index index_name(column_list);
	添加普通索引， 索引值可以出现多次。
	
4). alter  table  tb_name  add  fulltext  index_name(column_list);
	该语句指定了索引为FULLTEXT， 用于全文索引
复制代码

查看索引使用情况

show status like 'Handler_read%';	 -- 查看当前会话索引使用情况

show global status like 'Handler_read%';	-- 查看全局索引使用情况
复制代码

Handler_read_first：索引中第一条被读的次数。如果较高，表示服务器正执行大量全索引扫描（这个值越低越好）。

Handler_read_key：如果索引正在工作，这个值代表一个行被索引值读的次数，如果值越低，表示索引得到的性能改善不高，因为索引不经常使用（这个值越高越好）。

Handler_read_next ：按照键顺序读下一行的请求数。如果你用范围约束或如果执行索引扫描来查询索引列，该值增加。

Handler_read_prev：按照键顺序读前一行的请求数。该读方法主要用于优化ORDER BY ... DESC。

Handler_read_rnd ：根据固定位置读一行的请求数。如果你正执行大量查询并需要对结果进行排序该值较高。你可能使用了大量需要MySQL扫描整个表的查询或你的连接没有正确使用键。这个值较高，意味着运行效率低，应该建立索引来补救。

Handler_read_rnd_next：在数据文件中读下一行的请求数。如果你正进行大量的表扫描，该值较高。通常说明你的表索引不正确或写入的查询没有利用索引。

name	status	address	id(기본 키)
Xiaomi 1	0	1	1
샤오미 2	1	1	2

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