2.mysql索引

2.mysql索引

2.1 什么是索引？

索引其实是一种数据结构，能够帮助我们快速的检索数据库中的数

2.2 索引存储类型（B+Tree，B-Tree）

MySQL的索引是由存储引擎来实现的。由于存储引擎不同，所以具有不同的索引类型，如BTree索引，B+Tree索引，哈希索引，全文索引等。这里由于主要介绍BTree索引和B+Tree索引，我们平时使用最多的InnoDB引擎就是基于B+Tree索引的。

BTree(又称为B-Tree)是一个平衡搜索多叉树。BTree的结构如下图：

假设树的度为2d(d>1)，高度为h，那么BTree有以下性质：

• 每个叶子结点的高度一样，等于h；
• 每个非叶子结点由n-1个key和n个指针组成，key和指针相互隔离，结点两端一定是key；
• 叶子结点指针为null；

非叶子结点的key都是[key,data]二元组，其中key表示作为索引的键，data为键值所在行的其它列的数据；
在BTree中，对索引列是顺序存储的，所以很适合查找范围数据和ORDER BY操作。

B+Tree是BTree的一种变种。B+Tree和BTree的不同主要在于：

• B+Tree中的非叶子结点不存储数据，只存储键值；
• B+Tree的叶子结点没有指针，所有键值都会出现在叶子结点上，且key存储的键值对应data数据的物理地址；
• B+Tree的每个非叶子节点由n个键值key和n个指针point组成；

一般在数据库系统或文件系统中使用的B+Tree结构都在经典B+Tree的基础上进行了优化，增加了顺序访问指针，结构大致如下：

2.3 索引的优缺点

创建索引的优点，最简单的就是可以大大提高系统的性能，具体如下：

• 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
• 可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
• 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
• 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
• 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

也许会有人要问：增加索引有如此多的优点，为什么不对表中的每一个列创建一个索引呢？这种想法固然有其合理性，然而也有其片面性。虽然，索引有许多优点，但是，为表中的每一个列都增加索引，是非常不明智的。这是因为，增加索引也有许多不利的一个方面：

• 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
• 索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
• 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

2.4 聚簇索引和非聚簇索引存储结构（innodb引擎）

聚簇索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的就是整张表的行记录数据，也将聚集索引的叶子节点称为数据页。这个特性决定了索引组织表中数据也是索引的一部分，每张表只能拥有一个聚簇索引。

Innodb通过主键聚集数据，如果没有定义主键，innodb会选择非空的唯一索引代替。如果没有这样的索引，innodb会隐式的定义一个主键来作为聚簇索引。

非聚簇索引，又叫二级索引，或者辅助索引。非聚簇索引底层也是一颗B+树，只是叶子节点中保存的不是行记录数据，而是行的主键值。当通过二级索引查找行，存储引擎需要在二级索引中找到相应的叶子节点，获得行的主键值，然后使用主键去聚簇索引中查找数据行（覆盖索引），也就是我们俗称的回表（如果查询的字段在索引上的某个字段，或者查询的是主键，则不需要回表）。

聚簇索引和⾮聚簇索引的区别：

聚簇索引的叶⼦节点就是数据节点，⽽⾮聚簇索引的叶⼦节点仍然是索引节点，只不过有指向对应数据块的指针。

2.5 联合索引及最左前缀(leftmost prefix of the index)原则

联合索引又叫复合索引，是指两个或更多个列上的索引。对于复合索引:Mysql从左到右的使用索引中的字段，遵循最左前缀匹配的原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找，但不支持 b,c进行查找 .当最左侧字段是常量引用时，索引就十分有效。

下⾯举例对其进⾏说明：

1. ⼀个 2 列的索引 (name, age)，对 (name)、(name, age) 上建⽴了索引；
2. ⼀个 3 列的索引 (name, age, sex)，对 (name)、(name, age)、(name, age, sex) 上建⽴了索引。

1、 B+ 树的数据项是复合的数据结构，
⽐如：(name, age, sex) 的时候，B+ 树是按照从左到右的顺序来建⽴搜索树的，
⽐如：当(⼩明, 22, 男)这样的数据来检索的时候，B+ 树会优先⽐较 name 来确定下⼀步的所搜⽅向，如果name 相同再依次⽐较 age 和 sex，最后得到检索的数据。
但当 (22, 男) 这样没有 name 的数据来的时候，B+ 树就不知道第⼀步该查哪个节点，因为建⽴搜索树的时候 name 就是第⼀个⽐较因⼦，必须要先根据 name 来搜索才能知道下⼀步去哪⾥查询。

2、 当 (⼩明, 男) 这样的数据来检索时，B+ 树可以⽤ name 来指定搜索⽅向，但下⼀个字段 age 的缺失，所以只能把名字等于⼩明的数据都找到，然后再匹配性别是男的数据了， 这个是⾮常重要的性质，即索引的最左匹配特性。

关于最左前缀的补充：

1. 最左前缀匹配原则会⼀直向右匹配直到遇到范围查询（>、<、between、like）就停⽌匹配，⽐如：a = 1and b = 2 and c > 3 and d = 4 如果建⽴ (a, b, c, d) 顺序的索引，d 是⽤不到索引的。如果建⽴ (a, b, d, c) 的索引则都可以⽤到，a、b、d 的顺序可以任意调整。

2. = 和 in 可以乱序，⽐如：a = 1 and b = 2 and c = 3 建⽴ (a, b ,c) 索引可以任意顺序，MySQL 的优化器会优化成索引可以识别的形式。

参考链接：https://blog.csdn.net/aizhupo1314/article/details/112384410