[MySQL]存储引擎和索引01

引擎介绍

InnoDB引擎:具备外键支持功能的事务存储引擎

• MySQL从3.23.34a开始就包含InnoDB存储引擎。 大于等于5.5之后，默认采用InnoDB引擎 。
• InnoDB是MySQL的 默认事务型引擎 ，它被设计用来处理大量的短期(short-lived)事务。可以确保事务
  的完整提交(Commit)和回滚(Rollback)。
• 除了增加和查询外，还需要更新、删除操作，那么，应优先选择InnoDB存储引擎。
• 除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑InnoDB引擎。
• 数据文件结构：
  • 表名.frm 存储表结构（MySQL8.0时，合并在表名.ibd中）
  • 表名.ibd 存储数据和索引
• InnoDB是 为处理巨大数据量的最大性能设计 。
  • 在以前的版本中，字典数据以元数据文件、非事务表等来存储。现在这些元数据文件被删除
    了。比如： .frm ， .par ， .trn ， .isl ， .db.opt 等都在MySQL8.0中不存在了。
• 对比MyISAM的存储引擎， InnoDB写的处理效率差一些 ，并且会占用更多的磁盘空间以保存数据和
  索引。
• MyISAM只缓存索引，不缓存真实数据；InnoDB不仅缓存索引还要缓存真实数据， 对内存要求较 高 ，而且内存大小对性能有决定性的影响。

MyISAM引擎:主要的非事务处理存储引擎

• MyISAM提供了大量的特性，包括全文索引、压缩、空间函数(GIS)等，但MyISAM 不支持事务、行级 锁、外键 ，有一个毫无疑问的缺陷就是 崩溃后无法安全恢复 。
• 5.5之前默认的存储引擎.
• 优势是访问的 速度快 ，对事务完整性没有要求或者以SELECT、INSERT为主的应用
• 数据文件结构：
  • 表名.frm 存储表结构
  • 表名.MYD 存储数据 (MYData)
  • 表名.MYI 存储索引 (MYIndex)
• 应用场景：只读应用或者以读为主的业务

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存储引擎的场景

• 目标数据比较小 ，而且非常 频繁的进行访问 ，在内存中存放数据，如果太大的数据会造成 内存溢出 。可以通过参数 max_heap_table_size 控制Memory表的大小，限制Memory表的最大的大小。
• 如果 数据是临时的 ，而且必须立即可用得到，那么就可以放在内存中.
• 存储在Memory表中的数据如果突然间 丢失的话也没有太大的关系 。

还有一些表没有列出,主要重要的就是前两个,第三个表也可以了解一下.

InnoDB存储引擎和MyISAM存储引擎的区别

索引的数据结构

索引（Index）是帮助MySQL高效获取数据的数据结构。

索引的本质:索引是数据结构。你可以简单理解为“排好序的快速查找数据结构”，满足特定查找算法。这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法。

为什么需要索引?

在没有索引的情况下，不论是根据主键列或者其他列的值进行查找，由于我们并不能快速的定位到记录所在的页，所以只能从第一个页沿着双向链表一直往下找，在每一个页中根据我们上面的查找方式去查找指定的记录。因为要遍历所有的数据页，所以这种方式显然是超级耗时的。如果一个表有一亿条记录呢？此时索引应运而生。

优点

• 提高了检索的效率,降低了IO成本.
• 通过创建唯一索引，可以保证数据库表中每一行数据的唯一性。
• 在实现数据的参考完整性方面，可以加速表和表之间的连接。换句话说，对于有依赖关系的子表和父表联合查询时，可以提高查询速度。
• 在使用分组和排序子句进行数据查询时，可以显著减少查询中分组和排序的时间，降低了CPU的消耗。

缺点

• 创建索引和维护索引要耗费时间，并且随着数据量的增加，所耗费的时间也会增加。
• 索引需要占磁盘空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，存储在磁盘上，如果有大量的索引，索引文件就可能比数据文件更快达到最大文件尺寸。
• 虽然索引大大提高了查询速度，同时却会降低更新表的速度。当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度.

InnoDB中使用的索引

索引的结构

假设现在建立一个表如下:

CREATE TABLE index_demo(
	c1 INT,
	c2 INT,
	c3 CHAR(1),
	PRIMARY KEY(c1)
) ROW_FORMAT = Compact;

这个新建的index_demo使用了Compact行格式来实际存储记录,行格式简化的示意图如下:

这里需要说明一下的是record_type的各个取值代表的意思:

• 0:普通的用户记录
• 1:目录项记录
• 2:最小记录
• 3:最大记录
  把一些记录放到页里的示意图就是：
  
  一个页当中的空间最多只能有16KB,为了演示方便,假设1页最多只能存放3个数据,那么当一个页存储满了之后,如果还要存储新的数据,必然还要开辟新的页:
  
  注意，新分配的 数据页编号 可能并不是连续的。它们只是通过维护着上一个页和下一个页的编号而建立了链表关系。另外，页10中用户记录最大的主键值是5，而页28 中有一条记录的主键值是4，因为5>4，所以这就不符合下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值的要求，所以在插入主键值为4的记录的时候需要伴随着一次 记录移动，也就是把主键值为5的记录移动到页28中，然后再把主键值为4的记录插入到页10中.这个现象称为页分裂.

插入许多条数据之后,我们得到了许多页,那么如果想从这么多页中根据主键值快速定位某些记录所在的页,我们需要给它们做一个目录,每个目录项包括下边两个部分:

• 页的用户记录中最小的主键值,用key表示.
• 页号,用page_no表示
  
  至此，针对数据页做的简易目录就搞定了。这个目录有一个别名，称为 索引 。

上面的简易目录是存在问题的:

• InnoDB是使用页来作为管理存储空间的基本单位，最多能保证 16KB的连续存储空间，而随着表中记录数量的增多，需要非常大的连续的存储空间才能把所有的目录项都放下，这对记录数量非常多的表是不现实的。
• 我们时常会对 记录进行增删，假设我们把页28 中的记录都删除了，那意味着目录项2 也就没有存在的必要了，这就需要把目录项2后的目录项都向前移动一下，这样牵一发而动全身的操作效率很差。

所以，我们需要一种可以 灵活管理所有目录项 的方式。我们发现目录项其实长得跟我们的用户记录差不多，只不过目录项中的两个列是 主键 和 页号而已，为了和用户记录做一下区分，我们把这些用来表示目录项的记录称为目录项记录。那InnoDB怎么区分一条记录是普通的用户记录 还是目录项记录呢?使用记录头信息里的recordw_type属性,属性值代表的含义见上面.

我们把前边使用到的目录项放到数据页中的样子就是这样：

虽然说目录项记录中只存储主键值和对应的页号，比用户记录需要的存储空间小多了，但是不论怎么说一个页只有16KB大小，能存放的 目录项记录也是有限的，那如果表中的数据太多，以至于一个数据页不足以存放所有的目录项记录，如何处理呢?

这里我们假设一个存储目录项记录的页 最多只能存放4条目录项记录 ，所以如果此时我们再向上图中插入一条主键值为 320 的用户记录的话，那就需要分配一个新的存储 目录项记录 的页:
同样的还可以生成一个更高级的目录:

这个数据结构,名称是B+树

B+ Tree

从图中可以看出,我们的实际用户记录其实都存放在B+树的最底层的节点上，这些节点也被称为 叶子节点，其余用来存放 目录项 的节点称为 非叶子节点或者内节点，其中B+树最上边的那个节点也称为 根节点。

规定最下边的那层，也就是存放我们用户记录的那层为第 0 层，之前我们做了一个非常极端的假设:存放用户记录的页 最多存放3条记录 ，存放目录项记录的页 最多存放4条记录 。其实真实环境中一个页存放的记录数量是非常大的，假设所有存放用户记录的叶子节点代表的数据页可以存放 1000条用户记录 ，所有存放目录项记录的内节点代表的数据页可以存放 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条记录。

常见的索引概念

索引按照物理实现方式，索引可以分为2种：聚簇（聚集）和非聚簇（非聚集）索引。我们也把非聚集索引称为二级索引或者辅助索引。

1.聚簇索引

所有的用户记录都存在了叶子节点，数据即索引，索引即数据
特点:

• 使用记录主键值的大小进行记录和页的排序，这包括三个方面的含义：
  • 页内的记录是按照主键的大小顺序排成一个单向链表.
  • 各个存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表。存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个双向链表.
  • B+树的叶子节点存储的是完整的用户记录.
    优点:
• 数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快.
• 聚簇索引对于主键的排序查找和范围查找速度非常快,按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不用从多个数据块中提取数据，所以节省了大量的io操作.
  缺点:
• 插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键.
• 更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新.
• 二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据.

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

上边介绍的聚簇索引只能在搜索条件是 主键值 时才能发挥作用，因为B+树中的数据都是按照主键进行排序的。那如果我们想以别的列作为搜索条件该怎么办呢?肯定不能是从头到尾沿着链表依次遍历记录一遍。

我们可以多建几棵B+树,这时它们都是二级索引.假设此时建立一个c2列的索引,那么就相当于又建立了一棵B+ Tree:

回表:我们根据这个以c2列大小排序的B+树只能确定我们要查找记录的主键值，所以如果我们想根据c2列的值查找到完整的用户记录的话，仍然需要到聚簇索引中再查一遍，这个过程称为 回表 。也就是根据c2列的值查询一条完整的用户记录需要使用到 2 棵B+树！

问题：为什么我们还需要一次 回表 操作呢？直接把完整的用户记录放到叶子节点不OK吗？

回答:可以是可以,但是太占用空间!

3.联合索引

我们也可以同时以多个列的大小作为排序规则，也就是同时为多个列建立索引，比方说我们想让B+树按照 c2和c3列 的大小进行排序，这个包含两层含义：

• 先把各个记录和页按照c2列进行排序
• 在记录的c2列相同的情况下，采用c3列进行排序

InnoDB的B+ Tree索引的注意事项

1.根页面永远不动

抛去刚才索引的构建过程,我们写出实际上B+ Tree的形成过程:

• 每当为某个表创建一个B+树索引(聚簇索引不是人为创建的，默认就有)的时候，都会为这个索引创建一个根节点 页面。最开始表中没有数据的时候，每个B+树索引对应的 根节点中既没有用户记录，也没有目录项记录。
• 随后向表中插入用户记录时，先把用户记录存储到这个根节点 中。
• 当根节点中的可用 空间用完时 继续插入记录，此时会将根节点中的所有记录复制到一个新分配的页，比如 页a中，然后对这个新页进行页分裂的操作，得到另一个新页，比如页b。这时新插入的记录根据键值(也就是聚簇索引中的主键值，二级索引中对应的索引列的值)的大小就会被分配到 页a 或者 页b 中，而 根节点 便升级为存储目录项记录的页。

2.内节点中目录项记录的唯一性

对于c2列建立的索引,假设现在所有的c2数据都是1,那么此时此刻怎么保证目录当中记录的唯一性?难道还是用最小值吗?事实上,真实的目录如下图所示:

由此可见,目录项记录是由c2列+主键+页号的值构成的!

3.一个页面最少存储2条记录

一个B+树只需要很少的层级就可以轻松存储数亿条记录，查询速度相当不错！这是因为B+树本质上就是一个大的多层级目录，每经过一个目录时都会过滤掉许多无效的子目录，直到最后访问到存储真实数据的目录.那如果一个大的目录中只存放一个子目录是个啥效果呢？那就是目录层级非常非常非常多，而且最后的那个存放真实数据的目录中只能存放一条记录.费了半天劲只能存放一条真实的用户记录？所以InnoDB的一个数据页至少可以存放两条记录.