深入学习MySQL之篇二（索引）

一.抛出问题：为什么需要索引？

假设我们创建这样一张表

CREATE TABLE `person` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

person 表中有两个字段：id和name.给表中加数据

我们知道数据库的数据最终它都会存储在文件中。那么我们查找id为1的数据时，我们会这么写：

            SELECT * FROM person WHERE ID = 1；

如果没有索引，我们怎么查询呢，我想我们只能读取文件，逐行扫描，找到符合的结果。

如果 我们再来查询：SELECT * FROM person WHERE ID = 3；,那么就需要读取文件，逐行扫描，这样子效率会特别差。这时候就需要有一种东西可以帮助我们实现高效的获取数据。索引就这么出现了。

在讨论索引之前，还要讨论一下底层文件系统是怎么样进行读取，写入数据的：

如图一，在文件系统中，table表是这么存储的，有一个文件名，文件名对应着一个三元组（柱面，磁道，扇区），当我们要去读机械硬盘时，我们需要通过我们的柱面，磁道定位到扇区，然后找到table.idb存储在图右磁盘的红色区域内，读取数据时（假设顺时钟），将会从A依次读到B,这就是原始存储和读取方式。所以当我们每次查找的时候都是需要如图2 中从A到B的扫描读取，效率很低。

索引出现，解决效率低问题

这时候设计索引（假设表是以ID为索引），那么就可以通过这个索引快速的定位到磁盘中的某一个位置，如上图。

二，索引采取什么样的结构

我们知道几种适合快速查找的数据结构，比如：hash(哈希)、二叉树，红黑树，B+树。

而mysql的索引数据结构采取的是B+树。那么为什么不采用别的呢，以下依据索引优略的标准（IO渐进复杂度）做个比较。

1.hash(哈希)

  如果使用哈希作为索引，对ID做索引的话，可以对ID使用hash(ID)，存储到合适的位置，这样在使用sql查找，如：

select * from person where id = 1,就可以对id做hash,快速的找到数据。这样是没错，可是使用hash的话，有一个显著的弊端，无法进行范围的查找。比如：select * from person where id > 1;这样使用哈希完全没有办法找到数据在哪里。

2.对比 二叉树，红黑树，B+树

二叉查找树（BST）：

1、二叉排序树或者是一棵空树，或者是具有下列性质的二叉树：

（1）若左子树不空，则左子树上所有结点的值均小于它的根结点的值；

（2）若右子树不空，则右子树上所有结点的值均大于它的根结点的值；

（3）左、右子树也分别为二叉排序树；

（4）没有键值相等的节点（因此，插入的时候一定是叶子节点）。

插入有序节点，退化成单支树

1、查找效率最好O(logn)，最坏O(n)

2、插入效率和查找效率相同（只插入叶子节点）

3、删除效率最好O(logn)+O(1)->只有左子树或者右子树

      最差O(logn)+O(logn)->左子树和右子树同时存在

这里插入10个数据，不同索引算法图示如下：

红黑树

红黑树原理讲解：http://www.360doc.com/content/18/0904/19/25944647_783893127.shtml

B+树：

深入理解红黑树与B+树应用场景：https://blog.csdn.net/yu876876/article/details/82892112

我们说一个索引的是否优秀判断的依据是 IO渐进复杂度，那么我们讨论一下这三种结构，很明显二叉树的树高要远远大于 红黑树 和 B+树的树高，而且一个节点只能放一个数据。假如一个数据节点就需要一个IO，有千万个数据节点的话，那么二叉树就需要千万次IO，那么这个性能就不行了。如果用红黑树来做索引的话，比二叉树相对来说会好一些。它会有一个因子会控制树高，但是一旦数据很多，这个树高就是不可控了。而相对于B+树，它的树高是恒定（可以控制在3到5层），也就是说它的渐进复杂度是恒定的。(具体比较各种树，请自行找资料，我对树的数据结构也不是很了解)

三.mysql中的索引

这里讨论两种mysql的存储引擎：MyISAM和InnoDB。

在数据库存储文件中 MyISAM存储引擎的mysql对表会存储三个文件，后缀名 MYI(索引文件)，后缀名MYD（数据文件），后缀名frm（表字段结构文件）。而对于InnoDB存储引擎的表会存储两个文件，后缀名frm（表字段结构文件）,后缀名ibd(数据文件)。可见InnoDB存储引擎下的表没有索引文件，为什么呢？它又是怎么存储的呢？

在了解两种引擎如何设计索引之前，先来了解一下，BTree和B+Tree的区别。

由图1 和 图2 可以知道，BTree每个节点都可以存储数据，而B+Tree只有叶子节点才存储数据，第二，B+Tree在mysql的实现时还做了定制，可以看到相邻的叶子节点间加了链式的关联的。

这样的优势是在进行范围查找时会很快，比如：执行select * from person where id > 1 . 这样就可以找到1的叶子节点，然后顺着链式关联就可以很快速的找下去。

接着看 MyISAM 是如何设计索引的。我们知道MyISAM存储表时会有三个文件（索引文件，数据文件，字段表结构文件）。那么查询时怎么利用索引文件和数据文件来查询数据的呢。用以下的一张图可以表现出来。

可以看见左边是MYI文件，右边是MYD文件（上图是建立以ID为索引的）

当我们查询 select * from  table where id = 1的时候，它会判断id是否建立索引，如果建立索引，就会到MYISAM文件中定位到1的位置，找到对应的逻辑地址,然后去MYD文件中去找地址上存储的数据。如下图

上列是基于ID建立索引，再来看看基于name建立索引。

从上图可以看出以name为索引的MYI文件，它的二元组也存储的是逻辑地址。当我们执行select * from table where name = 'james'时，它也会判断有没有对于name建立索引，进而从MYI表中找到james为key对应的逻辑地址。再从MYD文件中找到数据。

以上这种索引叫非聚集索引

接下来看看  InnoDB存储引擎下的 索引（聚集索引 -- 以索引来组织数据）

在InnoDB存储引擎下只有两个文件，没有MYI文件，那么它怎么实现索引的呢？看下图，InnoDB是以主键为索引来组织数据的。

可以看到InnoDB组织的数据就是一颗B+Tree（也可以理解为索引文件和数据文件在一起），而不是像MyISAM中MYD文件的存储方式，所以它不需要类似MYI的索引文件。

比如上图就是以ID为主键建立索引组织的数据。如果是以name为主键建立索引的呢？既然需要以主键建立索引，那么InnoDB是否允许创建表时不设置主键呢？其实InnoDB底层是这样选择的，如果创建表时没有指定主键，InnoDB会为你指定一列不重复数据的列作为主键，如果找不到这样的列，那么InnoDB会给你生成一列作为主键（相当于Mongodb 中的 objectId策略）。

如果我们执行 select * from table where id = 1时，它会根据ID主键为索引找到如上图1节点的位置，然后直接返回数据了。

如果我们还要把name也建立一个索引，那么在执行  select * from table where name = 'james' 时会怎么样呢，如下图，如果以name建立一个副索引，那么在IDB文件里在存储一个如下图的结构，叶子节点存储的是主键。

那么我们在执行 select * from table where name = 'james'时，会先判断name是否建立索引，那么会在索引树种找到james，拿到存储的主键值，在通过主键去那颗以主键为索引组织的数据的树种去找到主键的位置，从而拿到数据。过程图下图：

另一篇很好的文章：https://www.cnblogs.com/fengqiang626/archive/2019/09/04/11459434.html