详谈MySQL索引（为什么要有索引？为什么MySQL的索引采用B+树？）

• 为什么要有索引？（索引出现的原因）

先总说：减少访问数据的总量，相应的减少磁盘IO操作。

细说：

1. 在InnoDB存储引擎中，磁盘上的存储空间被划分成了一页页的磁盘页，数据库中每个表的数据就一行行的存储在磁盘页中。访问数据的时候磁盘就需要每次至少将一页磁盘页的数据读取到内存中，而磁盘IO的性能非常低，从而严重的影响数据库系统的性能。

2. 当数据库需要定位表的某行数据时，就需要将所有存储了该表数据的磁盘页一页页的读取到内存中，需要大量的磁盘IO操作。

3. 这时候，如果想要优化，无非就是从两个角度来思考，一个是优化存储结构，减少数据的存储空间，减少磁盘页总数。第二个就是减少无效数据的读取操作，减少无效磁盘页的磁盘IO操作。

   为什么要采用B+树的数据结构生成索引？

   • 每次进行定位操作时，都从根开始查找。
   • 每层索引只需要读出一页磁盘页。
   • 每次查找都必须要搜索到叶子节点，才能定位到数据。
   • 索引的层数称作索引树的高度(height).
   • 索引的IO性能和索引树的高度密切相关。索引树越高，磁盘IO越多。

   为什么选择多叉树作为数据库索引？

   数据库一般都选择使用树结构来存储索引，因为树结构在保证有序的同时还能保证时间复杂度为O（logN），查询速率比较快，为什么一般选择使用多叉树而不是二叉树来做为存储索引的数据结构？

   实际上这和索引的存储位置有关系，当数据量比较大的时候，索引是需要非常大的存储空间的，因此将索引保存在内存里显然不太现实，所以索引都是保存在磁盘空间里面，使用的时候再从磁盘读取到内存，这就是磁盘IO。

   当使用索引查询数据时，计算机需要从一页一页的磁盘页中将索引读取到内存中，每页磁盘页对应着树结构的一个节点，每查询一个树节点相当于经过一次磁盘IO，读取了一页磁盘页，磁盘IO每次花费时间差不多8-10ms的样子，这个时间开销是特别巨大的，所以树越矮，查询过程中需要经过的树节点越少，需要时间也就越少，所以一般选择多叉树作为数据库的索引。

   B树的高度足够低，磁盘IO次数少，对查询的性能提升更强。多叉树中某一节点内部的元素可能会多一些，但是同一节点中的数据比较都是在内存中完成的，耗时几乎可以忽略。

   比较几种数据结构作为索引的特点？

   1.二叉树与红黑树

   

   从上面我们发现，红黑树相比较于二叉树又进步了一些，但红黑树还是有些问题：那就是数据量大的话，红黑树的深度会很深，也就是说深度不可控，这样一来查找数据还是会很耗时。

   2.讨论hash的方式

   第二种是哈希表，哈希表里面存储的是key-value键值对。如果利用哈希表来存储索引的话，得先利用哈希函数把key换算成数组中对应的一个Index位置，然后把value放在这个位置，如果多个key换算后得到了相同的index，那么就在这里追加一条链表，存储哈希值相同的key-value对象。比如你现在有一个用户表，存储了用户id和用户name，用哈希表结构进行存储。

   

   从上面我们发现，相比较于红黑树，hash可以固定“深度”，且映射到磁盘存储引用，这样查找数据直接告诉磁盘数据在哪，查找数据也挺快的，但是 hash 还是有些不足：那就是不能范围查找，比如我们查找Col1>1的数据，当然如果我们查询操作很少的话，我们也可以选择hash数据结构，因为它查找数据挺快的，这也是mysql的索引方法除了B+ 树还有hash

   比较B树和B+树？

   3.B树

   

   从上面看，我们发现BTree又进步了一些，查询速度提高，存储容量也没影响到。当然有人可能会这样想，那我们为什么不把数据全部都存在一个节点，这样深度不就是1了吗?

   当然不行了！java拿取数据一般是这样的：java程序–>CPU—>内存---->硬盘，而内存与硬盘的交互是有大小限制的，是一页数据4k左右，所以不能把所有数据都放在一个节点来获取，一般来说节点会尽量预存4K容量。

   看到这里，我们知道（4K=节点；节点=小节点*小节点的容量）一个节点是4K，而节点内有几个小节点，那么也就是说，只要我们每个的小节点的data容量越小，那么可以存的节点也就可以更多。

4. B+树

   

   B+Tree通过把data不放在非叶子节点来增加度（小节点），一般会一百个以上使得深度是3~5，从而减少查询次数。并且，叶子节点之间会有指针，数据又是递增的，这使得我们范围查找可以通过指针连接查找，而不再从上面节点往下一个个找。

   结论：B+Tree 既减少查询次数又提供了很好的范围查询

   MyISAM索引实现(非聚集)

   

   MyISAM索引文件和数据文件是分离的，数据.MYD+结构.frm+索引.MYI三个文件

   那myisam的索引是什么样的？

   

   InnoDB索引实现(聚集)

   

   联合索引的底层存储结构

   

• 问：为什么采用InnoDB存储引擎必须有主键。并且推荐使用整型的自增主键：

  1、如果设置了主键，那么InnoDB会选择主键作为聚集索引、如果没有显式定义主键，则InnoDB会选择第一个不包含有NULL值的唯一索引作为主键索引、如果也没有这样的唯一索引，则InnoDB会选择内置6字节长的ROW_ID作为隐含的聚集索引(ROW_ID随着行记录的写入而主键递增)。

  2、如果表使用自增主键
  那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，主键的顺序按照数据记录的插入顺序排列，自动有序。当一页写满，就会自动开辟一个新的页

  3、如果使用非自增主键（如果身份证号或学号等）
  由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置，此时MySQL不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。

  问：为什么非主键索引结构叶子结点存储的是主键值：（一致性和存储空间）

  减少了出现行移动或者数据页分裂时二级索引的维护工作（当数据需要更新的时候，二级索引不需要修改，只需要修改聚簇索引，一个表只能有一个聚簇索引，其他的都是二级索引，这样只需要修改聚簇索引就可以了，不需要重新构建二级索引）

  【注】：聚簇索引也称为主键索引，其索引树的叶子节点中存的是整行数据，表中行的物理顺序与键值的逻辑（索引）顺序相同。一个表只能包含一个聚集索引。因为索引（目录）只能按照一种方法进行排序。

  非聚簇索引（普通索引）的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引（secondary index）。

  问：为什么不使用uuid？作为主键索引（性能会高很多）

  三个原因：

  1：uuid的占用的存储空间比int型的存储空间更大

  2：用uuid进行大小比较时，uuid的速度更慢

  3：在进行插入时，如果用整型（自增）插入数据时，永远在前一个数据进行插入，若使用uuid插入数据时，有可能会导致数据的移动（行迁移），数据会进行分页（还有可能插入的数据在第一个位置，就还需要再进行一次IO操作进行读取）

如何计算B+树的高度？
B+树中非叶子节点只有索引字段和指针。叶子节点存有整行数据。
一个page页中有16k
假设主键为bigint为8byte，指针为6byte

则一页中可以存的非叶子节点对象为：
16k/(8B+6B)=1170
假设互联网的一条数据大小为1k
则一页数据可以存储16k/1k=16
则高度为2的B+树可以存储：1170*16=18720，为2w条
则高度为3的B+树可以存储：1170*1170*16=21,902,400，约2千万条数据