MySql索引原理

目录

什么是索引

为什么要使用索引

索引原理（B树）

索引原理（B+树）

---

什么是索引

        索引的本质是一种排好序的数据结构。

        索引是为了加速对表中数据行的检索而创建的一种分散的存储结构。索引是针对表而建立的，它是由数据页面以外的索引页面组成的，每个索引页面中的行都会含有逻辑指针，以便加速检索物理数据。

        在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。

为什么要使用索引

        索引就是根据表中的一列或若干列按照一定顺序建立的列值与记录行之间的对应关系表，实质上是一张描述索引列的列值与原表中记录行之间一 一对应关系的有序表。

        索引是 MySQL 中十分重要的数据库对象，是数据库性能调优技术的基础，常用于实现数据的快速检索。
        在 MySQL 中，通常是通过全表扫描，索引查询两种方式，如果没有索引查询，如果数据量很大的时候，全部走全表扫描，可想而知，这个查询是非常耗时间的。

所以，我们在一些复杂的查询中，就必须要使用索引才能完成。

索引的优缺点

索引的优点如下：

• 通过创建唯一索引可以保证数据库表中每一行数据的唯一性。
• 可以给所有的 MySQL 列类型设置索引。
• 可以大大加快数据的查询速度，这是使用索引最主要的原因。
• 在实现数据的参考完整性方面可以加速表与表之间的连接。
• 在使用分组和排序子句进行数据查询时也可以显著减少查询中分组和排序的时间。

增加索引也有许多不利的方面，主要如下：

• 创建和维护索引组要耗费时间，并且随着数据量的增加所耗费的时间也会增加。
• 索引需要占磁盘空间，除了数据表占数据空间以外，每一个索引还要占一定的物理空间。如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸。
• 当对表中的数据进行增加、删除和修改的时候，索引也要动态维护，这样就降低了数据的维护速度。

        索引可以提高查询速度，但是会影响插入记录的速度。因为，向有索引的表中插入记录时，数据库系统会按照索引进行排序，这样就降低了插入记录的速度，插入大量记录时的速度影响会更加明显。这种情况下，最好的办法是先删除表中的索引，然后插入数据，插入完成后，再创建索引。
使用索引时，需要综合考虑索引的优点和缺点。

索引原理（B树）

        常见的索引使用的数据结构是树结构，mysql目前的索引是B+树结构，是由B树演化而来，首先我们来看下B树索引。

B树（多路平衡查找树）

B 树大概是这样子的：

从B树的结构图中可以看到每个节点中不仅包含数据的 key 值，还有 data 值。

从上图可以看到B树的节点可以不止两个子节点，这样的好处就是树可以变得又矮又胖，矮胖的树是索引的最爱，用它做索引可以降低磁盘的IO.

B树中的每个节点根据实际情况可以包含大量的键值，数据和指针，上图所示为一个3阶的B树：

一般情况下，我们都会选择将数据和索引存储在磁盘这种外围设备中。

但是和内存相比，从磁盘中读取数据的速度会慢上百倍千倍甚至万倍，所以，我们应当尽量减少从磁盘中读取数据的次数。 另外，从磁盘中读取数据时，都是按照磁盘块来读取的，并不是一条一条的读。

如果我们能把尽量多的数据放进磁盘块中，那一次磁盘读取操作就会读取更多数据，那我们查找数据的时间也会大幅度降低。

如果我们用B树这种数据结构作为索引的数据结构，那我们每查找一次数据就需要从磁盘中读取一个节点，也就是我们说的一个磁盘块，我们都知道平衡二叉树可是每个节点只存储一个键值和数据的。

那说明什么?

说明每个磁盘块仅仅存储一个键值和数据！

那如果我们要存储海量的数据呢？

 而每页的存储空间是有限的，如果 data 比较大，会导致每个节点的 key 存储的较少，当数据量较大的时候，同样会导致B树很深，从而增加了磁盘 IO 的次数，进而影响查询效率。

可以想象到二叉树的节点将会非常多，高度也会及其高，我们查找数据时也会进行很多次磁盘IO，我们查找数据的效率将会极低！

比如如B树索引图所示：

模拟查找关键字29的过程：

根据根节点找到磁盘块1，读入内存。【磁盘I/O操作第1次】

比较关键字29在区间（17,35），找到磁盘块1的指针P2。

根据P2指针找到磁盘块3，读入内存。【磁盘I/O操作第2次】

比较关键字29在区间（26,30），找到磁盘块3的指针P2。

根据P2指针找到磁盘块8，读入内存。【磁盘I/O操作第3次】

在磁盘块8中的关键字列表中找到关键字29。

分析上面过程，发现需要3次磁盘I/O操作，和3次内存查找操作。由于内存中的键值是一个有序表结构，可以利用二分法查找提高效率。而3次磁盘I/O操作是影响整个B树查找效率的决定因素。

所以后面就出现了B+树索引：

索引原理（B+树）

想想还有没有可能进一步优化，在B树中每个节点的内容由三部分组成：键值，指针，数据，而磁盘块的容量是有限的，并不是每次读取磁盘块都会取出里面的数据，只是在最后一次读取的时候才会取出里面的数据，能不能将数据只存储在叶子节点里面，非叶子节点只存储键值和指针呢？这样就能最大化的利用磁盘块空间，一个磁盘块也就能存更多的东西了，没错，B+树就是这么干的。

假设在非叶子节点不存数据以后每个节点可以存储4个键值和指针，就变成了上图的B+树

B+树相对于B树有几点不同：

1. 非叶子节点只存储键值和指针。
2. 所有叶子节点之间都有一个链指针。
3. 数据记录都存放在叶子节点中。

在B+树中因为叶子节点的键值是按顺序排列的所以进行键值的范围查找效率非常高。

 在B+树中由于一个节点存储了更多的键值和指针，所以同样多的内容可以降低树的高度，减少磁盘io次数，从而提高效率。

数据库的索引分为聚集索引和非聚集索引，innoDb存储引擎中的聚集索引表中的数据按主键的顺序存放，它实际上就是按主键构建的一个B+树，叶子节点存放的是数据行记录。所以数据库中的数据实际上是索引的一部分。由于实际的数据页只能按照一个顺序存放，所以每张表聚集索引只能有一个。

非聚集索引的叶子节点中存放的是键值和主键值，所以通过非聚集索引需要先查找到主键值然后通过聚集索引查询到具体的数据，因此非聚集索引的效率要低于聚集索引。非聚集索引并不会影响到数据的存储顺序，所以非聚集索引可以存在多个。

聚簇索引和辅助索引

B+Tree的叶子节点存放主键索引值和行记录就属于聚簇索引；

如果索引值和行记录分开存放就属于非聚簇索引。

主键索引和辅助索引：B+Tree的叶子节点存放的是主键字段值就属于主键索引；如果存放的是非主键值就属于辅助索引（二级索引）。

在InnoDB引擎中，主键索引采用的就是聚簇索引结构存储。

聚簇索引（聚集索引）

聚簇索引是一种数据存储方式，InnoDB的聚簇索引就是按照主键顺序构建 B+Tree结构。B+Tree的叶子节点就是行记录，行记录和主键值紧凑地存储在一起。 这也意味着 InnoDB 的主键索引就是数据表本身，它按主键顺序存放了整张表的数据，占用的空间就是整个表数据量的大小。通常说的主键索引就是聚集索引。 InnoDB的表要求必须要有聚簇索引：

• 如果表定义了主键，则主键索引就是聚簇索引

• 如果表没有定义主键，则第一个非空unique列作为聚簇索引

• 否则InnoDB会从建一个隐藏的row-id作为聚簇索引

辅助索引

InnoDB辅助索引，也叫作二级索引，是根据索引列构建 B+Tree结构。但在 B+Tree 的叶子节点中只存了索引列和主键的信息。二级索引占用的空间会比聚簇索引小很多， 通常创建辅助索引就是为了提升查询效率。一个表InnoDB只能创建一个聚簇索引，但可以创建多个辅助索引。

查找原理：

1、主键索引直接从索引树就可以返回结果集。

2、辅助索引首先找到主键索引，在通过主键索引找到值。