MySQL4：索引是什么；索引类型；索引存储模型发展：1.二分查找，2.二叉查找树，3.平衡二叉树，4.多路平衡查找树，5. B+树，6.索引为什么不用红黑树？7.InnoDB的hash索引指什么？

索引是什么

数据库索引，是数据库管理系统(DBMS)中一个排序的数据结构，以协助快速查询、 更新数据库表中数据。

索引类型

在InnoDB里面，索引类型有三种，普通索引、唯一索引(主键索引是特殊的唯一索引，不允许为null)、全文索引。

• 普通索引(Normal)：也叫非唯一索引，是最普通的索引，没有任^的。

• 唯一索引 (Unique)：唯一索弓|要求键值不能重复。另外需要注意的是，主键索引是一 种特殊的唯一索弘它还多了一个条件，要求键值不能为空。主键索引用primay key 创建。

• 全文索引(Fulltext)：针对比较大的数据，比如我们存放的是消息内容、一篇文章，有几KB的数据的这种情况，如果要解决like査询在全文匹配的时候效率低的问题，可以创建全文索引。只有文本类型的字段才可以创建全文索引，比如char、varchar、text。

  CREATE TABLE `fulltext_index_test` (
  	`id` INT(11) NULL DEFAULT NULL,
  	`content` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
  	FULLTEXT INDEX `idx_fulltext_content` (`content`)
  )
  COLLATE='utf8mb4_unicode_ci'
  ENGINE=InnoDB
  ;
  

  增加两条记录

  查询语法：

  select * from fulltext_index_test where match(content) against('小李广' IN NATURAL LANGUAGE MODE);
  

MylSAM和InnoDB支持全文索引。

索引存储模型发展

网址：

1.二分查找

我们都玩过给出范围猜数字的游戏，比如1~10，猜猜我想的是几？
正常人都会从5开始猜。其实这个就是二分查找的一种思想，也叫折半查找，每次都把候选数据缩小了一半。在数据是有序的情况下，这种方式效率比较高。

所以第一个，我们可以考虑用有序数组作为索引的数据结构。

有序数组的等值查询和比较查询效率非常高，但是更新数据的时候会出现一个问题，可能要挪动大量的数据(改变index)，所以只适合存储静态的数据。

为了支持频繁的修改，比如插入数据，我们需要采用链表。链表的话，如果是单链 表，它的查找效率还是不够高。

所以，有没有可以使用二分査找的链表呢？ 
为了解决这个问题，BST(Binary Search Tree)也就是我们所说的二叉査找树诞生了。

2.二叉查找树(BST Binary Search Tree)

BST(Binary Search Tree)，二叉査找树。左子树所有的节点都小于父节点，右子树所有的节点都大于父节点。投影到平面以后，就是一个有序的线性表。

什么情况是最坏的情况呢？
还是刚才的这一批数字，如果我们插入的数据刚好是有序的，1，3，5，7，9。

它会变成链表(我们把这种树叫做'斜树')，这种情况下不能达到加快检索速度的目的，和顺序查找效率是没有区别的。

造成它倾斜的原因是什么呢？
因为左右子树深度差太大，这棵树的左子树根本没有节点——也就是它不够平衡。

所以，我们有没有左右子树深度相差不是那么大，更加平衡的树呢？
这个就是平衡二叉树，叫做Balanced binary search trees，或者AVL树(AVL是
发明这个数据结构的两位作者的名字简写：G. M. Adelson-Velsky和E. M. Landis)。

3.平衡二叉查找树(AVL Tree)(左旋、右旋)

AVL Trees(Balanced binary search trees)，平衡二叉树的定义：左右子树深度差绝对值不能超过1。

是什么意思呢？
比如左子树的深度是2，右子树的深度最多只能是1或者3。
这个时候我们再按顺序插入1、2、3、4、5、6，—定是这样，不会变成一棵'斜树'。

那应该怎么办呢？

所以为了保持平衡，AVL树在插入和更新数据的时候执行了一系列的计算和调整的 操作。

AVL Tree数据存储

• 索引的键值：比如我们在id上面创建了一个索引，我在用where id =1的 条件查询的时候就会找到索引里面的id的这个键值。
• 数据的磁盘地址：因为索引的作用就是去查找数据的存放的地址。
• 左子节点和右子节点：因为是二叉树，它必须还要有左子节点和右子节点的引用，这样我们才能找到下一个节点。比如大于22的时候，走右边，到下一个树的节点，继续判断。

如果是这样存储数据的话会有什么问题？

AVL Tree用于存储索引数据

首先，索引的数据除了Memory例外，是存放在内存上的，无论是innoDB，还是MylSAM，数据是放在硬盘上的。查看数据和索引的大小：

select
CONCAT(ROUND(SUM(DATA_LENGTH/1024/1024),2),'MB') AS datajen, 
CONCAT(ROUND(SUM(INDEX_LENGTH/1024/1024),2),'MB') as index_len
from information_schema.TABLES
where table_schema='数据库名' and table_name='表名';

当我们用树的结构来存储索引的时候，访问一个节点就要跟磁盘之间发生一次I/O操作。InnoDB每次操作磁盘的大小是固定的，最小的单位是一页(或者叫一个磁盘块)，大小是16K(16384 字节)。那么，一个树的节点必须设计成16K的大小，不然就会出现读不完或者读不够的情况。如果我们一个节点只存一个键值+数据+引用，例如整形的字段，可能只用了十几个、或者几十个字节，它远远达不到16K的容量。这样就浪费了很多存储空间。

我们基于索引査找数据的时候，肯定是希望一次从磁盘加载很多的这样的数据单元到内存中进行比较，这样就可以尽快拿到完整的数据。如果一个节点只存1个这样的单元，就需要读更多的节点，发生更多的I/O操作。如果是机械硬盘时代，每次从磁盘读取数据需要10ms左右的寻址时间，交互次数越多，消耗的时间就越多。

结合上面的图，我们一张表里面有6条数据，当我们查询id = 23的时候，要查询两个子节点，就需要跟磁盘交互3次，如果我们有几百万的数据呢？所谓二叉就是每个节点分叉最多为2，数据量很多的时候也就导致了树的高度会很高，那么这个查询时间更加难以估计。我们怎么解决这个问题？

• 第一个：就是让每个节点存储更多的数据。
• 第二个：节点上的关键字的数量越多，那么存储的指针也越多，也就是意味着可以有更多的分叉(我们把它叫做"路数”)。

因为分叉数越多，树的深度就会减少(根节点是0)，这样，树就从原来的高瘦高瘦的样子，变成了矮胖矮胖的样子，这个时候，我们的树就不再是二叉了，而是多叉，或者叫做多路，树的深度降低了，意味着I/O的次数会大大减少。

4.多路平衡查找树(B-Tree、Balanced Tree、B树)(、合并)

作为索引分叉数的多少是由一个节点能存多少个关键字来决定的，比如16K，16384字节可以存储的整型键值类型可以存1000个，那么分叉数就是1001，如果存字符类型，只能存100个，那么分叉数就是101。

比如我们要在这张表里面查找5，因为5小于22，走左边，因为5小于9，走左边。
在磁盘块5里面就找到了5，只用了3次I/O。

B Trees又是怎么实现一个节点存储多个关键字，还保持平衡的呢？跟AVL Tree有什么区别？

比如Max Degree(路数)是3的时候，我们插入数据1、2、3,在插入3的时候，本来应该在第一个磁盘块，但是如果一个节点有三个关键字的时候，意味着有4个指针，子节点会变成4路，所以这个时候必须进行。把中间的数据2提上去，把1和3变成2的子节点。如果删除节点，会有相反的合并的操作。注意这里是和合并，跟AVL树的左旋和右旋是不一样的。这个时候继续插入4和5，B Tree又会出现和合并的操作。

可以看到顶端由原来0002变成了0002，0004，0002，0004这里所展示的节点的，其实代表的就是InnoDB中的页的和合并，因此在更新索引的时候会有大量的索引的结构的调整，所以解释了为什么不要在频繁更新的列上建索引，或者为什么不要更新主键。

InnoDB中的页的和合并

如果索引键值有序，数据会顺序写入，写满一页接着开辟一个新的页。
如果索引键值无序，产生值的顺序不确定，因此写入的顺序无法确定(索引本身必须是有序的，只是你写入时候的顺序不确定；但是存储的顺序还是确定的，按字符编码顺序)，可能一个页没写满，就创建了新的页，导致存储过程产生大量磁盘碎片，并且带来频繁的page和合并。

5. B+树(B+Tree，加强版多路平衡查找树)

B Tree的效率已经很高了，为什么MySQL还要对B Tree进行改良，最终使用了
B+Tree呢？
总体上来说，这个B树的改良版本解决的问题比B Tree更全面。看一下InnoDB里面的B+树的存储结构：

MySQL中的B+Tree特点

• 它的关键字的数量是跟路数相等的；
• B+Tree的根节点和枝节点中都不会存储数据，只有叶子节点才存储数据。目前我们的认知：这里存放的数据是完整记录的地址。
  • 搜索到关键字不会直接返回，会到最后一层的叶子节点。比如我们搜索id=22, 虽然在第一层直接命中了，但是全部的数据在叶子节点上面，所以我还要继续往下搜索，一直到叶子节点。
• B+Tree的每个叶子节点增加了一个指向相邻叶子节点的指针，它的最后一个数据会指向下一个叶子节点的第一个数据，形成了一个有序链表的结构。

B+Tree的数据搜寻过程

• 单值查询：比如我们要查找22，在根节点就找到了键值，但是因为它不是页子节点，所以会继续往下搜寻，22是［22，40）的左闭右开的区间的临界值，所以会走左边的子节点，然后继续搜索，它又是［18，36）的左闭右开的区间的中间值，所以会走右边的子节点，最后在叶子节点上找到了需要的数据。
• 范围查询：比如要查询从22到60的数据，当找到22之后，只 需要顺着节点和指针顺序遍历就可以一次性访问到所有的数据节点，这样就极大地提高了区间查询效率(不需要返回上层父节点重复遍历查找)。

InnoDB中的B+Tree特性带来的优势

• B Tree能解决的问题，它都能解决：它是B Tree的变种，B Tree能解决的问题(B Tree解决的两大问题 是什么？每个节点存储更多关键字；路数更多)，它都能解决
• 扫库、扫表能力更强：如果我们要对表进行全表扫描，只需要遍历叶子节点就可以 了，顺序的跟着指针区区进行访问，不需要遍历整棵B+Tree拿到所有的数据
• 磁盘读写能力更强：根节点和枝节点不保存数据区，所以一个节点可以保存更多的关键字，存储更多的指针，形成更多的分叉，它的深度会更低，因此一次磁盘加载的关键字更多，换句话说，就是磁盘的I/O的次数会更少
• 排序能力更强：排序操作就是指'＞'、'＜'、'between and'等这种操作，基于叶子节点上的双向指针来实现的，因为叶子节点上有下一个数据区的指针，数据形成了链表，这样不需要再次回到根节点去查询
• 效率更加稳定：B+Tree永远是在叶子节点拿到数据，所以在一张表中查询数据，它的I/O次数是稳定的；不会像B Tree那样存在根节点拿到数据直接返回，这种随机的快，而大部分情况下都需要再次向下进行遍历，这种耗时差异大

InnoDB中的B+Tree记录数计算

索引字段+指针大小为16字节，一条记录是16bytes。非叶子节点可以存储多少个指针？在深度为2的B+Tree中可以存多少条记录？
InnoDB中读取按页读取，一页大小固定16KB。

• 索引字段+指针大小为16字节，非叶子节点(一页)可以存储1000个这样的单元(键值+指针)，代表有1000个指针；
• 一条记录是16bytes，一个叶子节点(一页)可以存储10条记录。

当树深度为2的时候，有1000²个叶子节点，可以存储的数据为1000*1000*10=10000000 ，千万级别。
在査找数据时一次页的査找代表一次I/O，也就是说，一张千万级别的表，査询数据最多需要访问3次磁盘。

树的深度是怎么来的？
根据你的键值类型和数据量计算出来的。字段值越大、数据量越大，深度越大。所以在InnoDB中，B+Tree深度一般为1-3层，它就能满足千万级的数据存储。

6.索引为什么不用红黑树？

红黑树也是BST树(二叉查找树)，但是不是严格平衡的，通过变色和旋转来保持平衡。
必须满足5个约束：

• 1、节点分为红色或者黑色。
• 2、根节点必须是黑色的。
• 3、叶子节点都是黑色的NULL节点。
• 4、红色节点的两个子节点都是黑色(不允许两个相邻的红色节点)。
• 5、从任意节点出发，至惧每个叶子节点的路径中包含相同数量的黑色节点。

基于以上规则，可以推导出：
从根节点到叶子节点的最长路径(红黑相间的路径)不大于最短路径(全部是黑色节点)的2倍。

MySQL为什么不用红黑树？
1、只有两路；2、不够平衡，最长路径不大于最短路径的2倍，也就是最长8，最短4，这样差4个，而AVL Tree都能够做到差值为1。所以，红黑树一般只放在内存里面用。例如Java的TreeMap，它可以用来实现一致性哈希。

7.索引方式：InnoDB真的是用的B+Tree吗，有没有hash索引？InnoDB的hash索引指什么？

经常会问，InnoDB里面的hash索引是干嘛的？
先说一下hash索引的特点：

• 査询速度比较快，时间复杂度是O(1)。
• 因为哈希索引里面的数据不是按顺序存储的，所以不能用于排序。
• 我们在查询数据的时候要根据键值计算哈希码，所以它只能支持等值查询('='、'IN')，不支持范围查询('>'、'<'、'>='、'<='、'between and')。
• 如果字段重复值很多的时候，会出现大量的哈希冲突(采用拉链法解决)，效率会降低。

因为B Tree和B+Tree的特性，它们广泛地用在文件系统和数据库中，例如Windows的HPFS文件系统；Oracle、MySQL、SQLServer 数据库。

MySQL合集