MySQL 단일 열 인덱스 및 공동 인덱스 요약

이 글에서는 단일 열 인덱스 및 공동 인덱스와 관련된 문제를 주로 소개하는 mysql에 대한 관련 지식을 제공합니다. 인덱스에 추가 열을 사용하면 검색 범위가 좁아질 수 있지만 2열 인덱스를 사용하면 인덱싱이 다릅니다. 두 개의 별도 인덱스를 사용하여 모두에게 도움이 되기를 바랍니다.

추천 학습: mysql 동영상 튜토리얼

1. 소개

인덱스에 추가 열을 사용하면 검색 범위를 좁힐 수 있지만 두 개의 열이 있는 인덱스를 사용하는 것은 두 개의 별도 인덱스를 사용하는 것과 다릅니다. .

공동 색인의 구조는 전화번호부와 유사합니다. 사람의 이름은 성과 이름으로 구성됩니다. 전화번호부는 먼저 성으로 정렬된 다음, 같은 사람에 대해 이름으로 정렬됩니다. 성. 전화번호부는 성을 알면 매우 유용하고, 이름과 성을 알면 더욱 유용하지만, 이름만 알고 성을 모른다면 쓸모가 없습니다.

그러므로 공동 인덱스를 생성할 때는 열의 순서를 신중하게 고려해야 합니다. 통합 인덱스는 인덱스의 모든 열을 검색할 때 유용하거나 처음 몇 개의 열만 검색할 때 유용합니다. 후속 열을 검색할 때는 유용하지 않습니다.

2. 단일 열 인덱스

다중 조건 쿼리에 여러 개의 단일 열 인덱스가 사용되는 경우 최적화 프로그램은 하나의 인덱스만 사용할 수도 있고 여러 인덱스를 모두 사용할 수도 있습니다. 그러나 여러 개의 단일 열 인덱스를 사용하면 하단에 여러 개의 B+ 인덱스 트리가 생성되므로 더 많은 공간을 차지하고 검색 효율성이 어느 정도 낭비되므로 다중 조건 결합 쿼리만 있는 경우 결합 인덱스를 구축하는 것이 가장 좋습니다. .

3. 가장 왼쪽 접두사 원칙

이름에서 알 수 있듯이 가장 왼쪽부터 시작하는 연속 인덱스는 첫 번째 필드가 범위 쿼리인 경우 조인트 생성 시 별도의 인덱스를 생성해야 합니다. index.비즈니스 요구에 따라 where 절에서 가장 자주 사용되는 열은 맨 왼쪽에 배치됩니다. 이 경우 확장성이 더 좋습니다. 예를 들어 username은 쿼리 조건으로 자주 사용되지만 age는 자주 사용되지 않으므로 username은 조인트 인덱스의 첫 번째 위치, 즉 가장 왼쪽에 배치되어야 합니다. .

1. 복합 인덱스 생성

ALTER TABLE employee ADD INDEX idx_name_salary (name,salary)

2. 복합 인덱스의 가장 왼쪽 특성을 만족하면 일부만이라도 복합 인덱스가 적용됩니다

SELECT * FROM employee WHERE NAME='哪吒编程'

3. 나타나면 가장 왼쪽의 특성이 만족되지 않아 인덱스가 무효화됩니다

SELECT * FROM employee WHERE salary=5000

4. 이름과 급여는 모두 왼쪽 순서대로 나타나며 인덱스는 적용됩니다

SELECT * FROM employee WHERE NAME='哪吒编程' AND salary=5000

5. 가장 왼쪽의 특징은 SQL을 실행할 때 MySQL이 최적화되고 맨 아래 레이어는 거꾸로 최적화됩니다

SELECT * FROM employee WHERE salary=5000 AND NAME='哪吒编程'

6. 이유

복합 인덱스는 (k1,k2, k3)은 (k1), (k1,k2), (k1,k2,k3) 3개의 Index를 생성하는 것과 동일하며, 이것이 가장 왼쪽 일치 원칙입니다.

공동 지수는 가장 왼쪽의 원칙을 충족하지 않으며 일반적으로 지수가 실패합니다.

4. 결합 인덱스와 단일 열 인덱스가 동시에 있습니다(필드가 반복됨). 이때 MySQL을 쿼리할 때 인덱스는 어떻게 사용됩니까?

이것은 MySQL 자체의 쿼리 최적화 전략과 관련이 있습니다. 테이블에 이동할 인덱스가 여러 개 있는 경우 MySQL은 쿼리 문의 비용을 기준으로 이동할 인덱스를 선택합니다. 왼쪽에서 오른쪽으로 되므로 차폐력이 가장 강한 조건을 최대한 앞쪽에 배치해야 합니다. Baidu Online에는 이 진술이 있지만 MySQL 실행 최적화 프로그램이 이를 최적화합니다. 인덱스를 고려하지 않을 때 조건의 순서는 효율성에 영향을 미치지 않습니다. 사용된!

5. 조인트 인덱스의 본질

**(a, b, c) 조인트 인덱스를 생성하는 것은 (a) 단일 컬럼 인덱스, (a, b) 조인트 인덱스 및 (a, b)를 생성하는 것과 같습니다. , c) 결합 인덱스 인덱스, 인덱스를 적용하려면 물론 세 가지 조합만 사용할 수 있습니다. 위에서 테스트한 것처럼 a와 c의 조합도 사용할 수 있지만 실제로는 a의 인덱스만 사용할 수 있습니다. 사용되며 c는 사용되지 않습니다.

6. 인덱스 실패

1. 앞에 %를 넣습니다.

2. null이 아닌 경우에는 or 문 앞뒤에 인덱스가 사용되지 않습니다. or의 왼쪽 및 오른쪽 쿼리 필드 중 하나만 인덱스인 경우 인덱스는 유효하지 않습니다. or의 왼쪽 및 오른쪽 쿼리 필드가 모두 인덱스인 경우에만 적용됩니다. 전후 인덱스이므로 SQL 최적화에서는 쓰기나 명령문을 피해야 합니다.

4. 암시적 데이터 유형 변환이 있습니다. varchar를 작은따옴표로 묶지 않으면 자동으로 int 유형으로 변환되어 인덱스가 무효화되고 전체 테이블 스캔이 발생할 수 있습니다.

7. 기타 지식 포인트

1. 인덱싱이 필요한 필드는 반드시 where 조건에 있어야 합니다

2. 데이터 양이 적은 필드는 인덱싱할 필요가 없습니다. index 데이터의 양이 적으면 필요하지 않습니다.

3. 조인트 인덱스는 각 열에 인덱스를 구축하는 것보다 더 많은 장점이 있습니다. 인덱스를 많이 생성할수록 디스크 공간을 더 많이 차지하며, 다중 열 인덱스를 구축할 때 데이터 업데이트 속도도 느려지기 때문입니다. , 순서에도 주의를 기울여야 합니다. 먼저 엄격한 인덱싱을 적용하여 심사를 더욱 강력하고 효율적으로 수행해야 합니다.

八、MySQL存储引擎简介

1、InnoDB

支持事务处理，支持外键，支持崩溃修复能力和并发控制。如果需要对事务的完整性要求比较高（比如银行），要求实现并发控制（比如售票），那选择InnoDB有很大的优势。如果需要频繁的更新、删除操作的数据库，也可以选择InnoDB，因为支持事务的提交和回滚。

2、MyISAM

插入速度快，空间和内存使用比较低。如果表主要是用于插入新纪录和读取记录，那么选择MyISAM能实现处理高效率。如果应用的完整性、并发要求比较低，也可以使用。

注意，同一个数据库也可以使用多种存储引擎的表。如果一个表要求比较高的事务处理，可以选择InnoDB。这个数据库中可以将查询要求比较高的表选择MyISAM存储。如果该数据库需要一个用于查询的临时表，可以选择MEMORY存储引擎。

九、索引结构（方法、算法）

在mysql中常用两种索引结构(算法)BTree和Hash，两种算法检索方式不一样，对查询的作用也不一样。

1、Hash

Hash索引的底层实现是由Hash表来实现的，非常适合以 key-value 的形式查询，也就是单个key 查询，或者说是等值查询。

Hash 索引可以比较方便的提供等值查询的场景,由于是一次定位数据，不像BTree索引需 要从根节点到枝节点，最后才能访问到页节点这样多次IO访问，所以检索效率远高于BTree索引。但是对于范围查询的话，就需要进行全表扫描了。

但为什么我们使用BTree比使用Hash多呢？主要Hash本身由于其特殊性，也带来了很多限制和弊端：

• Hash索引仅仅能满足“=”,“IN”,“”查询，不能使用范围查询。

• 联合索引中，Hash索引不能利用部分索引键查询。 对于联合索引中的多个列，Hash是要么全部使用，要么全部不使用，并不支持BTree支持的联合索引的最优前缀，也就是联合索引的前面一个或几个索引键进行查询时，Hash索引无法被利用。

• Hash索引无法避免数据的排序操作 由于Hash索引中存放的是经过Hash计算之后的Hash值，而且Hash值的大小关系并不一定和Hash运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算。

• Hash索引任何时候都不能避免表扫描 Hash索引是将索引键通过Hash运算之后，将Hash运算结果的Hash值和所对应的行指针信息存放于一个Hash表中，由于不同索引键存在相同Hash值，所以即使满足某个Hash键值的数据的记录条数，也无法从Hash索引中直接完成查询，还是要通过访问表中的实际数据进行比较，并得到相应的结果。

• Hash索引遇到大量Hash值相等的情况后性能并不一定会比BTree高 对于选择性比较低的索引键，如果创建Hash索引，那么将会存在大量记录指针信息存于同一个Hash值相关联。这样要定位某一条记录时就会非常麻烦，会浪费多次表数据访问，而造成整体性能底下。

2、B+ Tree

B+Tree索引是最常用的mysql数据库索引算法，因为它不仅可以被用在=,>,>=,

例如：

select * from user where name like 'jack%'; select * from user where name like 'jac%k%';

如果一通配符开头，或者没有使用常量，则不会使用索引，

例如：

select * from user where name like '%jack'; select * from user where name like simply_name;

3、 B+/-Tree原理

在数据库中，数据量相对较大，多路查找树显然更加适合数据库的应用场景，接下来我们就介绍这两类多路查找树，毕竟作为程序员，心里没点B树怎么能行呢？

B树：B树就是B-树，他有着如下的特性：

• B树不同于二叉树，他们的一个节点可以存储多个关键字和多个子树指针，这就是B+树的特点；

• 一个m阶的B树要求除了根节点以外，所有的非叶子子节点必须要有[m/2,m]个子树；

• 根节点必须只能有两个子树，当然，如果只有根节点一个节点的情况存在；

• B树是一个查找二叉树，这点和二叉查找树很像，他都是越靠前的子树越小，并且，同一个节点内，关键字按照大小排序；

• B树的一个节点要求子树的个数等于关键字的个数+1；

B+树就是B树的plus版

• B+树将所有的查找结果放在叶子节点中，这也就意味着查找B+树，就必须到叶子节点才能返回结果；

• B+ 트리의 각 노드에 있는 키워드 수는 하위 트리 포인터의 수와 같습니다.

• B+ 트리의 리프가 아닌 노드의 각 키워드는 포인터에 해당하며 키워드는 하위 트리의 최대값 또는 최소값

이전 섹션에서 B-Tree를 최적화합니다. B+Tree의 리프가 아닌 노드는 키 값 정보만 저장하므로 각 디스크 블록은 4개의 키 값을 저장할 수 있다고 가정합니다. B+Tree의 구조는 아래와 같습니다:

일반적으로 B+Tree에는 두 개의 헤드 포인터가 있는데, 하나는 루트 노드를 가리키고 다른 하나는 가장 작은 리프 노드를 가리킵니다. 키워드와 모든 리프 노드(즉, 데이터 노드) 사이에 체인 링 구조가 있습니다. 따라서 B+Tree에서는 두 가지 검색 작업을 수행할 수 있습니다. 하나는 기본 키에 대한 범위 검색 및 페이징 검색이고, 다른 하나는 루트 노드에서 시작하는 무작위 검색입니다.

위 예에는 데이터 레코드가 22개만 있을 수 있으며 B+Tree의 장점을 볼 수 없습니다. 계산은 다음과 같습니다.

InnoDB 스토리지 엔진의 페이지 크기는 16KB이고 기본 키 유형은 일반 테이블은 INT(4워드 점유) 섹션) 또는 BIGINT(8바이트 점유)이며 포인터 유형은 일반적으로 4 또는 8바이트이므로 한 페이지(B+Tree의 노드)는 아마도 16KB/( 8B+8B )=1K 키 값 ​​(추정이므로 계산의 편의를 위해 여기서 K의 값은 〖10〗^3입니다). 16KB/(8B+8B)=1K个键值（因为是估值，为方便计算，这里的K取值为〖10〗^3）。
也就是说一个深度为3的B+Tree索引可以维护10^3 * 10^3 * 10^3 = 10亿 즉, 깊이가 3인 B+Tree 인덱스는 10^3 * 10^3 * 10^3 = 10억개의 레코드를 유지할 수 있습니다.

실제 상황에서는 각 노드가 완전히 채워지지 않을 수 있으므로 데이터베이스에서 B+Tree의 높이는 일반적으로 2~4레이어입니다. MySQL의 InnoDB 스토리지 엔진은 루트 노드가 메모리에 상주하도록 설계되었습니다. 즉, 특정 키 값의 행 레코드를 찾는 데 1~3회의 디스크 I/O 작업만 필요하다는 의미입니다.

데이터베이스의 B+Tree 인덱스는 클러스터형 인덱스와 보조 인덱스로 나눌 수 있습니다. 위의 B+Tree 예시 다이어그램은 클러스터형 인덱스로 데이터베이스에 구현되어 있으며, 클러스터형 인덱스의 B+Tree에 있는 리프 노드에는 테이블 전체의 행 레코드 데이터가 저장됩니다. 보조 인덱스와 클러스터형 인덱스의 차이점은 보조 인덱스의 리프 노드에는 행 레코드의 모든 데이터가 포함되어 있는 것이 아니라 해당 행 데이터를 저장하는 클러스터형 인덱스 키, 즉 기본 키가 포함된다는 점입니다. 보조 인덱스를 통해 데이터를 쿼리할 때 InnoDB 스토리지 엔진은 보조 인덱스를 순회하여 기본 키를 찾은 다음 기본 키를 통해 클러스터형 인덱스에서 전체 행 레코드 데이터를 찾습니다.

추천 학습: mysql 비디오 튜토리얼

위 내용은 MySQL 단일 열 인덱스 및 공동 인덱스 요약의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!