Mysql数据库B+树索引，聚集索引和非聚集索引

背景：

数据在磁盘上随机写入，查询数据时如果在磁盘上挨个读取速度会非常慢，这时就需要想办法把查询控制在一个范围内

索引：

索引是 帮助mysql高效获取数据的排好序的数据结构

索引数据结构：

• 二叉树：单边数据增长的情况数据查询会很慢
• 红黑树（平衡二叉树）：单边数据增长会做平衡处理，有自动平衡功能，左子树和右子树相差绝对值<=1，缺点：随着数据量增加树高越来越高，查询速度也越来越慢
• B树：基于平衡二叉树每个节点放多个元素，高度即变矮
• hash索引：对索引的key进行一个hash运算就可以定位到数据的存储位置，大多数时比B+树索引更快，仅能满足“=” “in”，不支持范围查找，hash冲突问题
• B+树：非叶子节点放索引（冗余索引，存放每一个磁盘页的第一个元素，为了构建B+树），叶子节点放索引和数据（所有的索引和数据），叶子节点从左至右以此递增（单向链表），叶子节点之间用指针连接，提高区间访问性能

数据查找过程：

比如要查找一个col=30的数据，从B+树的根节点开始查找，首先把最上层级的磁盘页数据读取到内存，找到位置后再把下一个磁盘页数据读取到内存，找到位置继续读取下一个磁盘页的数据到内存........ B+树树高3层，一个层级的磁盘页大小16K，16K/(8Bit+6Bit)=1170条索引key，一条数据大约1k大小容量，则一个B+树能放：1170*1170*16=2190W条数据，即在一个千万级表里走索引找一个数据只需要磁盘io三次，mysql的根节点常驻内存

B树和B+树区别

• B树的所有节点存放索引和数据，而B+树非叶子节点存放索引，叶子节点存放索引和数据，
• B+树的非叶子节点都只存索引，这样B+树存的数据就多，树高就矮，查询就越快。而B树每个节点放索引和数据就要站用1k空间，一个页可以放16条数据，2190w条数据树高要很高远>3，查询性能会很慢，超过2190w的数据一般分库分表，也可以增加树高为4
• 范围查找：B树没有指针没办法往后找，只能回到根节点往下找

存储引擎：

Myisam（非聚集索引）：

• 索引和数据各放在一个文件（MYI文件和MYD文件分离开），
• 在索引文件找到数据地址/指针（叶子节点放的是索引和数据地址），然后在数据文件里找data
• myisam的主键索引和非主键索引都是这样存储

innodb（聚集索引）：

• 叶子节点包含完整的数据记录（索引和数据），innodb只有一个聚集索引，innodb表用聚集索引组织数据，如果没有建立，mysql会自己建立一个rowid来组织数据
• 索引和数据放在一个文件(IBD文件，按照B+树结构把整个数据组织起来)
• 为什么非主键索引结构叶子节点存储的是主键（有主键id用主键id，没有则用rowid）值？一致性(数据保持一致)和节省存储空间(表建立了多个索引放一个索引，只放一个主键索引来标识唯一数据即可)
• innodb里的二级索引也是非聚集索引，先找到主键索引再回表找到数据。回表：二级索引找到主键，再去聚集索引里查数据

应用场景：

        myisam：查询多，数仓等

        innodb：插入，更新多，事务支持，用户并发操作，业务支持

主要区别：

•  外键支持
• 事务支持
• 行表锁支持
• 缓存

为什么innodb表推荐使用整型自增id作为主键？

innodb表数据文件是用B+树结构组织起来的一个索引结构数 据，如何组织？用整型自增id，如果不建立主键整型id，mysql会自己建立一个rowid来组织数据，浪费了些系统资源，所以尽量自己来建立整型（字符串比大小要逐位转换asc码比较，1<2快？还是uuid一串字符串挨个比较快？且整型占用空间小）自增id。

为什么用自增？

B+树对范围查找支持比其他结构好的多，排好序的索引还要分裂重新插入，树的分裂消耗性能，使用自增用于向后插入分离概率小的多，如果不是有序增长，插入一条id小于存在id，排序时后面的数据就都需要移动，移动会消耗io性能，后面要移动的数据越多，io性能消耗越大

复合/联合索引底层的数据结构？

也是B+树结构，联合索引的三个字段a,b,c， 先排序a，a都相等的情况下排序b，拍完b之后再排序c