mysql学习笔记-存储引擎、索引数据结构

mysql存储引擎有哪些？

InnoDB(具备外键支持功能的事务存储引擎)、MyISAM （主要的非事务处理存储引擎）、 Archive(用于数据存档) 、Blackhole（丢弃写操作，读操作会返回空内容）、CSV(存储文件格式为CSV)、Memory(置于内存的表)、Federated(访问远程表)、Merge（管理多个MyISAM表构成的表集合）、 NDB(MySQL集群专用存储引擎)

InnoDB 存储引擎

1、mysql默认的存储引擎
2、支持事务、外键
3、缓存索引同时缓存真实数据

MyISAM引擎：主要的非事务存储引擎

应用场景：只读业务或以读为主的业务，访问速度快
缺点：不支持事务、行级锁、外键、安全性不高（奔溃后无法完全恢复）

InnoDB、MyISAM引擎对比

对比项	MyISAM	InnoDB
外键	不支持	支持
事务	不支持	支持
行表锁	表锁，即使操作一条记录也会锁住整个表，不适合高并发的操作	行锁，操作时只锁某一行，不对其它行有影响，适合高并发的操作
缓存	只缓存索引，不缓存真实数据	不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响
自带系统表使用	Y	Y
默认安装	N	Y

InnoDB 中的索引

InnoDB 中的索引迭代推演

这个数据结构，它的名称是 B+树

B+树有那些特征：
①规定最下边那层存放用户记录②一般不超过四层③通过二分法实现快速查找记录

聚簇索引

概念：聚簇索引也称聚集索引，是一种数据存储方式（所有真实记录都存储在叶子节点），索引即数据，数据即索引。
特点：
①数据页内的记录按主键大小排成一个单向链表
②记录页之间根据用户记录主键大小顺序排成一个双向链表
③同一层次的目录页，根据页中目录项记录的主键大小顺序排成一个 双向链表
④B+树的 叶子节点 存储的是完整的用户记录。
优点：
①数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快
②对于主键的排序查找和范围查找速度非常快
③性能优，节省大量io操作
缺点：
①插入速度严重依赖于插入顺序 ，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键
②更新主键的代价很高 ，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新
③二级索引访问需要两次索引查找 ，第一次找到主键值，第二次根据主键值找到行数据

二级索引（辅助索引、非聚簇索引）

根据这个以c2列大小排序的B+树。二级索引不记录用户完整记录，只记录当前字段(C2)+主键pk。如果想根据C2列查找完整用户记录，需要到聚簇索引再查找一遍，此过程称为回表。

联合索引

我们也可以同时以多个列的大小作为排序规则，也就是同时为多个列建立索引，比方说我们想让B+树按照 c2和c3列 的大小进行排序，这个包含两层含义：
先把各个记录和页按照c2列进行排序。
在记录的c2列相同的情况下，采用c3列进行排序
注意一点，以c2和c3列的大小为排序规则建立的B+树称为 联合索引 ，本质上也是一个二级索引。它的意思与分别为c2和c3列分别建立索引的表述是不同的，不同点如下：
建立 联合索引 只会建立如上图一样的1棵B+树。
为c2和c3列分别建立索引会分别以c2和c3列的大小为排序规则建立2棵B+树。

MyISAM中的索引方案

MyISAM 原理图

MyISAM 与 InnoDB索引对比

①InnoDB 的聚簇索引一次就能查询到想要的记录，MyISAM中都需要回表，因为存储的是数据记录地址
②InnoDB 的数据文件本身就是索引文件，而MyISAM索引文件和数据文件是 分离的 ，索引文件仅保存数据记录的地址。
③InnoDB的所有非聚簇索引都引用主键作为data域
④MyISAM的回表操作是十分快速的，InnoDB是通过获取主键之后再去聚簇索引里找记录
⑤InnoDB要求表必须有主键（ MyISAM可以没有 ）

MySQL数据结构选择的合理性

Hash结构

数组+链表+红黑树，跟HashMap结构一样。
只有Memory存储引擎支持Hash索引。
等值查找时，采用Hash索引是不错的选择。
InnoDB本身不支持 Hash索引，但是提供自适应 Hash 索引(Adaptive HashIndex)。某个数据经常被访问，当满足一定条件的时候，就会将这个数据页的地址存放到Hash 表中。这样下次查询的时候，就可以直接找到这个页面的所在位置。这样让 B+ 树也具备了 Hash 索引的优点。

B-Tree Balance Tree多路平衡查找树

B树的英文是 Balance Tree，也就是 多路平衡查找树。简写为B-Tree(注意横杠表示这两个单词连起来的意思，不是减号)。它的高度远小于平衡二叉树的高度。

数的阶数有多少就有，它的每一个节点就有多少个阶段，3阶树，每一个节点就有三个孩子。

B+ 树和 B树的差异

1、孩子个数不一样，B+树孩子个数=关键字个数，B树 孩子个数=关键字个数+1
2、非叶子节点的关键字也会同时存在在子节点中，并且是在子节点中所有关键字的最大(或最小)
3、非叶子节点仅用于索引，不保存数据记录，跟记录有关的信息都放在叶子节点中。而B树中，非叶子节点既保存索引，也保存数据记录。
4、所有关键字都在叶子节点出现，叶子节点构成一个有序链表，而且叶子节点本身按照关键字的大小从小到大顺序链接。

mysql为什么选择B+Tree

1. B+树的中间节点并不直接存储数据。
2. B+ 树查询效率更稳定.
3. B+ 树的查询效率更高,因为通常 B+树比B树更矮胖.
4. 不仅是对单个关键字的查询上，在查询范围上，B+树的效率也比B树高

几道思考题

1：为了减少IO，索引树会一次性加载吗？
不会，索引占用空间很大，一次性加载不大现实。逐一加载每一个磁盘页，因为磁盘页对应着索引树节点。
2：B+树的存储能力如何？为何说一般查找行记录，最多只需1~3次磁盘IO
InnoDB 存储引擎中页的大小为 16KB，一般表的主键类型为INT(占用4个字节)或 BIGINT(占用8个字节)，指针类型也一般为4或8个字节，也就是说一个页(B+Tree 中的一个节点)中大概存储16KB/(8B+8B)=1K 个键值（聚簇索引是不是不能存1000个键值？）(因为是估值，为方便计算，这里的K取值为 1000。也就是说一个深度为3的B+Tree 索引可以维护 100010001000=10 亿条记录。(这里假定一个数据也存储10条行记录数据了实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree 的高度一般都在 2-4 层。MySQL的InnoDB 存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要 1~3 次磁盘I/0 操作。
3：为什么说B+树比B-树更适合实际应用中操作系统的文件索引和数据库引？
4：Hash 索引与 B+ 树索引的区别
①、Hash索引不能进行范围查找，而B+树可以。这是因为Hash指向的数据是无序的，而B+树叶子节点是个有序的链表。
②、Hash 索引不支持联合索引的最左侧原则(即联合索引的部分索引无法使用)，而 B+ 树可以。对于联合索引来说，Hash 索引在计算 Hash 值的时候是将索引键合并后再一起计算 Hash 值，所以不会针对每个索引单独计算 Hash 值。因此如果用到联合索引的一个或者几个索引时，联合索引无法被利用。
③、Hash 索引不支持 ORDER BY 排序 ，因为 Hash 索引指向的数据是无序的，因此无法起到排序优化的作用，而 B+ 树索引数据是有序的，可以起到对该字段ORDER BY 排序优化的作用。同理，我们也无法用 Hash索引进行 模糊査询，而 B+ 树使用 LIKE 进行模糊査询的时候，LKE后面后模糊査询(比如 %结尾)的话就可以起到优化作用。
④、InnoDB 不支持哈希索引
5：Hash 索引与 B+ 树索引是在建索引的时候手动指定的吗？
不是手动指定的，若 使用InnoDB引擎 则默认使用B+树索引，且不支持Hash索引。针对InnpDB 和 MyISAM 存储引擎，都会默认采用 B+ 树索引，无法使用 Hash 索引。InnoDB 提供的自适应 Hash 是不需要手动指定的。如果是 Memory/Heap 和 NDB 存储引擎，是可以进行选择 Hash 索引。