MySql数据库索引的简单理解

MySql数据库索引的简单理解

1.索引的定义：

1.1：索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。如果想按特定职员的姓来查找他或她，则与在表中搜索所有的行相比，索引有助于更快地获取信息。

2.索引的目的：

2.1：索引的一个主要目的就是加快检索表中数据，亦即能协助信息搜索者尽快的找到符合限制条件的记录ID的辅助数据结构。

3.索引的主要类型：

3.1索引分为聚簇索引和非聚簇索引两种，聚簇索引 是按照数据存放的物理位置为顺序的，而非聚簇索引就不一样了；聚簇索引能提高多 行检索的速度，而非聚簇索引对于单行的检索很快。

3.2根据数据库的功能，可以在数据库设计器中创建三种索引：唯一索引、主键索引和聚集索引。

3.2.1：唯一索引

唯一索引是不允许其中任何两行具有相同索引值的索引。当现有数据中存在重复的键值时，大多数数据库不允许将新创建的唯一索引与表一起保存。数据库还可能防止添加将在表中创建重复键值的新数据。例如，如果在employee表中职员的姓(lname)上创建了唯一索引，则任何两个员工都不能同姓。

3.2.2：主键索引

数据库表经常有一列或多列组合，其值唯一标识表中的每一行。该列称为表的主键。在数据库关系图中为表定义主键将自动创建主键索引，主键索引是唯一索引的特定类型。该索引要求主键中的每个值都唯一。当在查询中使用主键索引时，它还允许对数据的快速访问。

3.2.3：聚集索引

在聚集索引中，表中行的物理顺序与键值的逻辑（索引）顺序相同。一个表只能包含一个聚集索引。如果某索引不是聚集索引，则表中行的物理顺序与键值的逻辑顺序不匹配。与非聚集索引相比，聚集索引通常提供更快的数据访问速度。聚集索引和非聚集索引的区别，如字典默认按字母顺序排序，读者如知道某个字的读音可根据字母顺序快速定位。因此聚集索引和表的内容是在一起的。如读者需查询某个生僻字，则需按字典前面的索引，举例按偏旁进行定位，找到该字对应的页数，再打开对应页数找到该字。这种通过两个地方而查询到某个字的方式就如非聚集索引。

以上更多的是对所以概念的描述：

4. b+树：

例子 ：

数据库中有一张 user 表： id 是主键

4.1 查询user表中的索引 show INDEX FROM user; 可以看到，主键索引默认的结构类型是 BTREE (B树)。

4.2 查询user表数据 SELECT * FROM USER； 可以看到，是默认按照主键的大小顺序排列的。 原因是，数据库会根据主键的大小，从小到大进行排序。

4.3 想象一下，如果没有主键，若此时需要找到 ID 为7的记录，数据库需要多少次才能找到？

正常的逻辑应该是按照磁盘写入的顺序，每次读取一条记录到内存，再进行条件比较，判断ID是否符合条件，那么答案应当是4次。（也就是4次io操作）

4.4 那么如果是要找 ID 为 5 的记录呢？
那么结果就是，遍历表中所有的记录，而且找不到ID 为5的这条记录。但是实际的IO操作是 8 次，那么问题就来了，这种方式的查询效率很低。
所以回头看下设置有主键索引的查询过程：id按照顺序排列，查询到第四次会读取到ID为7的记录，那么就可以判断ID为5的记录不存在。原因是5小于 7，而表中小于7的记录中，没有ID为5的记录，且7之后的记录也一定是不符合条件的，也没有必要再进行IO读取后判断。所以，4次即可。相对于前者，效率提升明显。

4.5 但其实每次我们进行条件查询时，查询到的并不只是自己给定的条件的记录，例如 查询ID 为1 的记录，实际查询到的是一个‘页’ 的记录。 例如执行查询ID为1的记录，但实际从磁盘读取到的数据 是ID为1的记录所属的那 ‘一页’的数据（页也可理解为一个容器）。至于一页的数据有多少条记录，那么则取决于两个方面，一是，页容量的大小，二是表中每条记录的大小。

4.5.1 ： 页的大小： SHOW GLOBAL STATUS LIKE ‘INNODB_page_size’; 这里是接近16KB。（页的大小时可以调整的）

4.5.2 ：记录的大小则取决于 设计表时，给每个字段设定的类型，int,varchar…（不同的字符集，大小不同）以及存储在记录中的数据的实际大小。
简单的理解，若是页的容量是 100 ， 每条记录的大小恰好都是10 ，那么当查询ID为1的记录时，实际内存拿到的是ID为1-10的 10条记录。

4.53. 页的结构图： 如图，
页头（两个指针，分别指向上一页，和下一页，也就是链表结构）
页目录（下面单独解释）
用户数据区域（该页所对应的数据，上一条记录会指向下一条记录，单向的）

页目录： 可以想象成一本书的目录： 记录的都是某一章内容开始的最小的页数。

第一章…1； 第一章的开始页是： 1
第二章…30； 第二章的开始页是： 30
第三章…45； ~
第四章…70； ~

4.5.4 换个素材，这里解释下，一张表有5个字段，第一个字段是主键ID（其他四个字段无所谓，不说了）。这一页总的用户数据区域有四条记录。 页目录存放的是按组划分之后的ID。 两个也目录分别指向其所对应的记录。

此时若查找ID为3的记录，查询逻辑则是，首先查找页目录，判断3属于页目录中的哪一组，找到对应的组，之后再在组内进行判断，找到对应的记录。

4.5.5 随着数据量的增多，一页数据肯定无法存放所有的数据，那么与之对应的就会增加响应的页。如图：需要指出的是，页与页之间关联就是通过页头的指针进行关联的（双向链表），PS:素材里的第二页，应该是101页。

此时若查询ID为6的记录，则流程是，先从第一页开始，判断页目录是否包含或者符合条件，符合则再对记录进行筛选，知道拿到符合条件的记录。

4.5.7 其实页的功能是对数据的管理。 数据的增多会导致页数量的增多，那么页数量多了之后也面临需要管理的问题（管理起来可以提高查询效率），那么管理的解决方法也是相同的，新增一个目录页，之前管理数据的页，就称之为数据页。

目录页中记录多个数据页目录信息，信息包含数据页中ID的最小值，以及对应的数据页的地址信息。

4.6 再来看下B树的结构：（MySql的B+树结构，和实际的B+树结构略有不同4.7会解释）
依次添加7个数：1.2.3.4.5.6.7 ，其按照B+树的结构如图：

特点是： 1. 非叶子节点，会冗余在叶子节点上。
2.叶子节点上面，会保存所有的数据。
3.叶子节点有一个指针，指向下一个节点数据。

总结下: 这个B+树种，既有非叶子节点，（索引），也有全部的数据（叶子节点），且是按数值的大小进行排序。

4.7： 对 4.5.7 进行整理后的示例MySql索引结构，如图：MySql 与 单纯B+树结构的不同是，MySql的叶子节点是双向的，而B+树叶子节点是单向的。 （个人理解是一种定制化的改进吧：例如需求是查询ID小于99的记录，单向就很尴尬，没有办法回头找）

通过4.6的描述总结，可以得知MySql的InnoDB中，主键索引的相关内容是：

1. 主键索引，会在插入时，根据主键值的大小，自动排序，目的是提高查询效率。
2. 主键索引也是 聚集索引，因为索引的叶子节点会包含完整的所有的数据。（索引和数据是在一起的）

补充中…

如有错误，请大佬指正~