四、MySQL存储引擎
4.1 InnoDB引擎：具备外键支持功能的事务存储引擎4.2 MyISAM 引擎：主要的非事务处理存储引擎4.3 Memory 引擎：置于内存的表小结： 五、索引的数据结构
5.1 为什么使用索引5.2 索引及其优缺点
1.索引概述2.优点3.缺点 5.3 InnoDB中索引的推演
1.简单的索引设计方案2.InnoDB的索引方案
①迭代1次：目录项纪录的页②迭代2次：多个目录项纪录的页③ 迭代3次：目录项记录页的目录页④B+Tree 3.常见索引概念
3.1 聚簇索引
`优点：``缺点：`
3.2 非聚簇索引（二级索引、辅助索引）3.3 InnoDB的B+树索引注意事项
4.MyISAM索引方案5.InnoDB和MyISAM对比
5.4 索引的代价5.5 思考题
往期：
MySQL深入学习 — mysql逻辑架构，SQL的执行流程，数据库缓冲池 四、MySQL存储引擎
查看mysql提供什么存储引擎
show engines;4.1 InnoDB引擎：具备外键支持功能的事务存储引擎
MySQL从3.23.34a开始就包含InnoDB存储引擎。大于等于5.5之后，默认采用InnoDB引擎 。InnoDB是MySQL的 默认事务型引擎 ，可以确保事务的完整提交(Commit)和回滚(Rollback)。数据文件结构：
表名.ifm 存储表结构表面.ibd 存储数据和索引 对比MyISAM：
InnoDB支持事务，外键，行锁。MyISAM仅支持表锁，不支持外键和事务，且崩溃后无法安全恢复InnoDB写的处理效率差一些，并且会占用更多磁盘空间保存数据和索引MyISAM只缓存索引，不缓存真实数据；InnoDB不仅缓存索引还要缓存真实数据， 对内存要求较高 ，而且内存大小对性能有决定性的影响。 应用场景：
InnoDB是 为处理巨大数据量的最大性能设计 。除了增加和查询外，还需要更新、删除操作，那么，应优先选择InnoDB存储引擎除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑InnoDB引擎 4.2 MyISAM 引擎：主要的非事务处理存储引擎
MyISAM提供了大量的特性，包括全文索引、压缩、空间函数(GIS)等，但MyISAM 不支持事务、行级 锁、外键，有一个毫无疑问的缺陷就是崩溃后无法安全恢复5.5之前默认的存储引擎优势是访问的 速度快 ，对事务完整性没有要求或者以SELECT、INSERT为主的应用数据文件结构：
表名.frm 存储表结构表名.MYD 存储数据 (MYData)表名.MYI 存储索引 (MYIndex) 应用场景：
只读应用或者以读为主的业务 4.3 Memory 引擎：置于内存的表
Memory采用的逻辑介质是内存 ，响应速度很快，但是当mysqld守护进程崩溃的时候数据会丢失 。另外，要求存储的数据是数据长度不变的格式。比如，Blob和Text类型的数据不可用(长度不固定的)。Memory同时支持哈希（HASH）索引 和 B+树索引Memory表至少比MyISAM表要 快一个数量级Memory表的大小是受到限制 的。表的大小主要取决于两个参数，分别是 max_rows 和 max_heap_table_size 。其中，max_rows可以在创建表时指定；max_heap_table_size的大小默 认为16MB，可以按需要进行扩大。数据文件与索引文件分开存储。缺点：其数据易丢失，生命周期短。基于这个缺陷，选择MEMORY存储引擎时需要特别小心。应用场景：
目标数据比较小，而且非常频繁的访问如果数据是临时的 ，而且 必须立即可用 得到，那么就可以放在内存中存储在Memory表中的数据如果突然间 丢失的话也没有太大的关系 小结：
主要对比MyISAM和InnoDB
五、索引的数据结构 5.1 为什么使用索引数据存放在磁盘中是不规整的，如果不用索引，需要一行一行读取，这样大量的磁盘IO非常耗时
如果使用二叉树进行存储，就可以大大减少磁盘IO的次数
5.2 索引及其优缺点 1.索引概述MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。
本质： 索引是数据结构。可以理解为：排好序的快速查找数据结构，满足特定的查找算法，这些数据结构以某种方式指向数据，这样就可以在数据结构的基础上实现高级查找算法
2.优点降低数据库IO成本通过创建唯一索引，可以保证数据库表中每一行的数据唯一性可以加速表与表之间的连接在使用分组和排序子句进行数据查询时，可以显著减少查询中分组和排序的时间 3.缺点
创建索引和维护索引需要耗费时间索引需要占 磁盘空间虽然索引大大提高了查询速度，同时却会 降低更新表的速度 5.3 InnoDB中索引的推演
先设计一个表
mysql> CREATE TABLE index_demo(
-> c1 INT,
-> c2 INT,
-> c3 CHAr(1),
-> PRIMARY KEY(c1)
-> ) ROW_FORMAT = Compact;
如图为简化版index_demo表的行格式示意图
record_type：记录头信息的一项属性，表示记录的类型， 0表示普通记录、 2表示最小记录、 3表示最大记录、 1暂时还没用过next_record：记录头信息的一项属性，表示下一条地址相对于本条记录的地址偏移量，我们用箭头来表明下一条记录是谁各个列的值：这里只记录在 index_demo 表中的三个列，分别是 c1、 c2和 c3
把一些记录放入页里的示意图：
1.简单的索引设计方案设计索引是为了快速定位到需要查找的记录在哪一页，即可以建一个目录：
下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值给所有的页建立一个目录项（每页第一个记录），提取这个目录项出来
现在，这个目录就可以称为索引了
2.InnoDB的索引方案 ①迭代1次：目录项纪录的页我们把前边使用到的目录项放到数据页中的样子就是这样：
注意：
目录项记录 的 record_type值是1，而 普通用户记录 的 record_type值是0目录项记录只有 主键值和页的编号 两个列，而普通的用户记录的列是用户自己定义的，可能包含很多列 ，另外还有InnoDB自己添加的隐藏列。 ②迭代2次：多个目录项纪录的页
一个页默认16KB 当数据量大起来的时候，显然用一个目录页可能装不下，这时候就需要分出多个目录页
③ 迭代3次：目录项记录页的目录页当目录页数量大起来了，查询目录也需要一条一条的查询，效率也不高了，所以就抽出来一层记录目录的目录
我们可以用下边这个图来描述它：
这个结构就是B+树
④B+Tree一个B+树的节点其实可以分成好多层，规定最下边的那层，也就是 存放我们用户记录 的那层为第 0 层， 之后依次往上加。
真实环境中一个页存放的记录数量是非常大的，假设所有存放用户记录的叶子节点代表的数据页可以存放 100条用户记录 ，所有存放目录项记录的内节点代表的数据页可以存放 1000条目录项记录，那么：
如果B+树只有1层，也就是只有1个用于存放用户记录的节点，最多能存放 100条记录如果B+树有2层，最多能存放 1000×100=10,0000 条记录如果B+树有3层，最多能存放 1000×1000×100=1,0000,0000 条记录如果B+树有4层，最多能存放 1000×1000×1000×100=1000,0000,0000 条记录
一般情况下，数据量一张表不会存放1000,0000,0000 一千亿条数据，所以B+树不会超过4层
3.常见索引概念按照物理实现方式，索引可以分为两种：聚簇(聚集)索引，非聚簇(聚集)索引。非聚簇索引又称为二级索引或辅助索引
3.1 聚簇索引聚簇索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的就是整张表的行记录数据，也将聚集索引的叶子节点称为数据页。这个特性决定了索引组织表中数据也是索引的一部分，每张表只能拥有一个聚簇索引。
比如：InnoDB将数据和索引存在一起，其主键索引就是聚簇索引，MyISAM把索引和数据分开存储就是非聚簇索引。
优点：数据访问更快 ，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快聚簇索引对于主键的 排序查找 和 范围查找 速度非常快按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不用从多个数据块中提取数据，所以节省了大量的io操作 缺点：
插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。更新主键的代价很高，因为将会导致被更新的行移动。 3.2 非聚簇索引（二级索引、辅助索引）
Innodb中聚簇索引是主键索引，并且索引上的值是完整的一行数据。而非聚簇索引上记录的是主键值，所以非聚簇索引需要回到聚簇索引上查找，这个过程叫做回表
3.3 InnoDB的B+树索引注意事项- 根页面位置万年不动内节点中目录项记录的唯一性一个页面最少存储2条记录
MyISAM采用非聚簇索引
索引文件中按主键建立B+树，然后叶子结点记录数据文件的偏移量，再去数据文件中查找数据
5.InnoDB和MyISAM对比MyISAM的索引方式都是“非聚簇”的，与InnoDB包含1个聚簇索引是不同的
在InnoDB存储引擎中，我们只需要根据主键值对 聚簇索引 进行一次查找就能找到对应的记录，而在 MyISAM 中却需要进行一次 回表 操作，意味着MyISAM中建立的索引相当于全部都是 二级索引
InnoDB的数据文件本身就是索引文件，而MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址
InnoDB的非聚簇索引data域存储相应记录 主键的值 ，而MyISAM索引记录的是 地址
MyISAM的回表操作是十分 快速 的，因为是拿着地址偏移量直接到文件中取数据的，反观InnoDB是通过获取主键之后再回表去聚簇索引里找记录，虽然说也不慢，但还是比不上直接用地址去访问
InnoDB要求表 必须有主键 （ MyISAM可以没有 ）
如果没有显式指定，则MySQL系统会自动选择一个可以非空且唯一标识数据记录的列作为主键。如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整型
空间：
每建立一个索引都要为它建立一棵B+树，每一棵B+树的每一个节点都是一个数据页，一个页默认会占用 16KB 的存储空间，一棵很大的B+树由许多数据页组成，那就是很大的一片存储空间
时间：
每次增删改都需要修改B+树索引，如果使用不当出现记录移位 、页面分裂 、页面回收等操作会拖慢性能
5.5 思考题
① 为了减少IO，索引树会一次性加载吗？
不会，因为索引会占用空间，大量的索引可能会超出1g多的大小，所以不会一次性加载
② B+树的存储能力如何？为何说一般查找行记录，最多只需1~3次磁盘IO？
储存能力很强，倘若一开始的根页可以存放100条数据条目，那如果页目录可以存放1000条，那二级存放的量就1001000，三级就是10010001000,4级就是100100010001000，那为什么最多只需要加载最大3次呢，因为根页的数据在一开始已经加载了所有无需加载，那么就算最大加载4级，那也就需要加载最大3次③ 为什么说B+树比B-树更适合实际应用中操作系统的文件索引和数据库索引？
因为B+树查询更为稳定，且适合范围的快速查找④ Hash 索引与 B+ 树索引的区别
HASH索引的范围查找效率比B+树索引效率低很多，且不支持联合索引⑤ Hash 索引与 B+ 树索引是在建索引的时候手动指定的吗？
不是的，是一开始我们创建表的时候，每次插入数据，他背后都会去维护对应索引，如果又新加的二级索引才会再创建索引
