Mysql索引

MySql索引

一、索引定义

索引是帮助Mysql高效获取数据的一种数据结构，简而言之，索引就是一种数据结构。一种便于数据库查找数据的数据结构。

二、Mysql中的索引

在mysql中，我们可以为一张表建立一系列的索引，当查询的时候，首先查找到索引，然后根据索引快速的定位到真正的数据。

三、数据结构的选择

日常开发中，针对Mysql最主要的是为了查询。

1、暴力遍历

这个肯定是最直接的办法，将所有数据存储到线性表中，查询的时候依次遍历，这样可以获取到想要的数据，这样的话，存储比较方便，直接存储到线性表的后面就行。但是查找的话，需要一个O(n)的时间复杂度，另外对范围查询，排序等都不友好。

1.1、hash索引

在hash冲突可以忽略的情况下，通过hash的方式查找数据的时间复杂度是O(1)的，是一种理想的查询的数据结构。但是仅仅只能支持单值匹配，对于范围查询却是没有办法的。

2、二分查找

这样的话，就需要维护一个有序的数据结构，例如数组，通过二分查找可以将时间复杂度降低到O(logn)，数组的话，不利于插入和删除，因此过度到二叉搜索树。

3、二叉搜索树

二叉搜索树仍然可以在O(logn）的时间复杂度获取到数据，另外插入和删除均比数组快。然而在一些特殊的情况下，二叉搜索树会退化成链表：

这样的话，查找最终又退化成O(n)的时间复杂度了。

4、平衡二叉树

为了杜绝上面的情况，引入了AVL树，通过旋转使得左右子树的高度差不超过1。虽然这样时间复杂度基本稳定在O（logn），但是当特别大的时候，最终的执行时间也是相当长的。造成这样的原因的本质是这棵树在n特别大的时候，会变得非常高。为了降低树的高度，引入B+树。

5、B+树与磁盘

从图中可以看出，B+树非叶子结点仅存储关键字和子节点的引用，不保存数据，这样一个Node可以使得树的高度更低，同样多的数量，B+树的高度远远低于平衡二叉树，B+树的每个叶子结点，存储真实的数据，叶子结点从小到达串在一起，叶子结点中的数据也是有序的。

磁盘的结构

磁盘由大小相同且同轴的圆形盘片，磁头，磁臂组成，磁头负责读取数据，盘片可以旋转，磁头可以沿着盘片的半径方向运动，因此盘片被划分成很多同心环，每个圆环被称为一个磁道。垂直方向上众多的磁道组成一个柱面。磁道被沿半径线划分成一个个段，每个段叫做一个扇区，扇区是磁盘的最小存储单元，也是最小读取单元。扇区的大小一般在512byte到4kb之间。

从磁盘读取数据的时候，首先定位–确定柱面，然后磁臂移动到对应的磁道上，接着确定盘面，然后盘片开始旋转，将要读取的扇区移动到磁头下，磁头开始读取数据。总的而言，一次数据的读取花费的时间包含寻道时间，盘片旋转延迟的时间，数据传输时间三个部分。为了提升效率，减少磁盘IO，一般都会预读，也就是将目标数据后面的数据也一次性读进到内存中。预读的长度一般为页的整数倍，主存和磁盘以页为单位进行数据交换。当程序要读取的数据不在内存中时，将会触发一个缺页中断，然后系统向磁盘发出读信号，磁盘找对对应的位置，然后连续读取一页或者几页数据进入内存中。
按照这种性质，innodb存储引擎，默认使用一个页作为一个B+树的结点，也就是16KB，而且由于B+树结点的顺序性，因此每一次预读的过程中，可以将后续的结点也可以加载进来，也就是一次磁盘IO可以读取好几个结点。

Innodb中的索引

聚簇索引

Innodb将表的主键组织成一个B+树，并且将整个表的其它数据放在叶子结点中。即使一个表没有主键也没有唯一索引，也会使用一个rowid默认作为主键索引。聚簇索引的优点就是一次就可以快速查找到整行记录，无需二次回表。另外对于主键的范围和排序查找都很快。

非聚簇索引

叶子节点并不包含行记录的全部数据，除了索引本身以外，还包含主键，这样查询的时候，可以先通过其它索引找到主键，然后再通过主键查找到其它行记录数据。

相比这下各有优缺点，在时间和空间中进行抉择。

联合索引 / 复合索引

将表中的多个列共同组成的索引称为联合索引或者复合索引。多个列建立索引的时候，先会按照第一列排序，再第一列相同的情况下，再使用第二列进行排序依次类推，组织成一颗B+树，

也就是说除了第一列严格有序外，其它列可能并没有顺序，这也是为什么联合索引要遵循最左匹配原则，否则索引会失效的原因。

覆盖索引

因为有联合索引也就是多个列共同组成一个索引，那么当要查找的数据恰好在索引列当中时，就称为覆盖索引，覆盖索引不包含整个行记录，其大小远远小于聚簇索引。

自适应hash索引

innodb在查询过程中监控索引表，发现某些结点频繁使用，会为这些节点创建hash索引，当下次使用的时候，就可以在O(1)的时间复杂度内定位到其位置。

MyIsam中的索引

MyIsam中表的数据和索引分处于不同的文件中，数据文件插入的时候，并不划分为若干个数据页，按照行号，依次插入就完成了。同样索引节点中也和innodb不同，存储的是主键值+行号的形式，也就是先通过索引找到行号，然后通过行号再找到对应的记录。因此在myisam中索引均是非聚簇索引。

索引的创建策略

任何东西都具有两面性，索引也是如此。索引不仅占用空间，而且每次对表数据的插入和修改都需要对索引进行维护。因此对于索引不能盲目的创建，需要有一定的策略。而且要选择合适的列创建索引。

• 索引列尽可能的小：数据类型越小，占用的空间也就越少，一次IO读取进来的数据也就越多。另外如果是主键的话，因为其它非聚簇索引的叶子结点都要存储主键，因此可以更有效的节省空间。
• 离散性：索引列的选择要离散性要高，因为当一列中有大量的数据重复时，即使使用了索引，也要扫描很多数据。
• 多使用联合索引：因为联合索引可以包含单个索引使用，只需放置在首位即可。
• 索引创建的时候，要选择经常排序，归类及其出现在where语句中的列，那么什么样的列容易出现在where子句中呢？ 一般在业务代码中，前端页面上总有几个删选框，这几个便是创建索引的最佳列，就像这样。
  

如何判断是否是一个好的索引

• 索引包含了要查询的所有数据，也就是覆盖索引，或者说不用二次回表。
• 索引的顺序，和查找的数据顺序一致，也就是根据最左原则可以匹配。
• 索引最好是递增的，且占据的空间越小越好，因为这样一次IO操作，可以读取更多的数据进内存中。