一、操作存储引擎

1.查看提供的存储引擎

show engines;

2.设置系统默认的存储引擎

  • 查看默认的存储引擎

show variables like '%storage_engine%';
#或
SELECT @@default_storage_engine;

  • 修改默认的存储引擎：MySQL5.5之前默认的存储引擎是MyISAM，5.5之后，默认采用InnoDB引擎

    • 使用命令行：SET DEFAULT_STORAGE_ENGINE=MyISAM;

    • 修改 my.cnf 文件：设置为MyISAMdefault-storage-engine=MyISAM，重启服务systemctl restart mysqld.service

3.设置表的存储引擎

  • 创建表时指定存储引擎

CREATE TABLE 表名(
建表语句;
) ENGINE = 存储引擎名称;

  • 修改表的存储引擎

ALTER TABLE 表名 ENGINE = 存储引擎名称;

二、存储引擎的介绍

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

  • MySQL从3.23.34a已经提供了InnoDB存储引擎。 大于等于5.5之后，默认采用InnoDB引擎 。

  • InnoDB是MySQL的 默认事务型引擎 ，它被设计用来处理大量的短期(short-lived)事务。可以确保事务的完整提交(Commit)和回滚(Rollback)。

  • 除了增加和查询外，还需要更新、删除操作，那么，应优先选择InnoDB存储引擎。

  • 除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑InnoDB引擎。

  • 数据文件结构：存储在/var/lib/mysql目录下

    • 表名.frm 存储表结构（MySQL8.0时，合并在表名.ibd中）

    • 表名.ibd 存储数据和索引

  • InnoDB是 为处理巨大数据量的最大性能设计 。

知识点补充：B+Tree

先确定一条数据记录的格式

  • record_type ：记录头信息的一项属性，表示记录的类型， 0 表示普通记录、 2 表示最小记录、 3 表示最大记录、 1目录页记录。

  • next_record ：记录头信息的一项属性，表示下一条地址相对于本条记录的地址偏移量，我们用箭头来表明下一条记录是谁。

  • 各个列的值 ：这里只记录在 示例表中的三个列，分别是 c1（主键） 、 c2 和 c3 。

  • 其他信息 ：除了上述3种信息以外的所有信息，包括其他隐藏列的值以及记录的额外信息。

InnoDB存储引擎底层使用B+Tree数据结构存储数据

特点

  • 只有叶子节点存储真实的数据，非叶节点只存储键

  • 叶节点之间通过双向链表链接

  • 每条数据之间使用单单项链表结构

hash的自适应

采用自适应 Hash 索引目的是方便根据 SQL 的查询条件加速定位到叶子节点，特别是当 B+ 树比较深的时候，通过自适应 Hash 索引可以明显提高数据的检索效率。

我们可以通过 innodb_adaptive_hash_index 变量来查看是否开启了自适应 Hash，比如：

mysql> show variables like '%adaptive_hash_index';

知识点补充：B-Tree

特点

  • 每个节点最多包含 m 个子节点。

  • 如果根节点包含子节点，则至少包含 2 个子节点；除根节点外，每个非叶节点至少包含 m/2 个子节点。

  • 拥有 k 个子节点的非叶节点将包含 k - 1 条记录。

  • 所有叶节点都在同一层中。

下图以3阶B-Tree举例

说明

以磁盘块2举例：

  • 存储的值为8和12，P1代表磁盘块5的地址值，P2代表磁盘块6的地址值，P4代表磁盘块7的地址值

  • 如果查询值小于8，则通过P1的地址值，查找磁盘块5，然后判断值是3，还是5，然后找出对应的数值，没有查询到返回空值

  • 如果查询值为8，则返回8对应的值

  • 如果查询值大于8小于12，则通过P2的地址值，查找磁盘块6，然后判断值是9，还是10，没有查询到返回空值

  • 如果查询值大于12，则通过P3的地址值，查找磁盘块7，然后判断值是13，还是15，然后找出对应的数值，没有查询到返回空值

B 树的优势除了树高小，还有对访问局部性原理的利用。所谓局部性原理，是指当一个数据被使用时，其附近的数据有较大概率在短时间内被使用。B 树将键相近的数据存储在同一个节点，当访问其中某个数据时，数据库会将该整个节点读到缓存中；当它临近的数据紧接着被访问时，可以直接在缓存中读取，无需进行磁盘 IO；换句话说，B 树的缓存命中率更高。

B 树在数据库中有一些应用，如 mongodb 的索引使用了 B 树结构。但是在很多数据库应用中，使用了是 B 树的变种 B+树。

知识点补充：B-Tree（B树）与B+Tree的对比（B+树）

  • B 树中每个节点（包括叶节点和非叶节点）都存储真实的数据，B+树中只有叶子节点存储真实的数据，非叶节点只存储键。在 MySQL 中，这里所说的真实数据，可能是行的全部数据（如 Innodb 的聚簇索引），也可能只是行的主键（如 Innodb 的辅助索引），或者是行所在的地址（如 MyIsam 的非聚簇索引）。

  • B 树中一条记录只会出现一次，不会重复出现，而 B+树的键则可能重复重现（一定会在叶节点出现，也可能在非叶节点重复出现）

  • B+树的叶节点之间通过双向链表链接。

  • B 树中的非叶节点，记录数比子节点个数少 1；而 B+树中记录数与子节点个数相同。

由此，B+树与 B 树相比，有以下优势：

  • 更少的 IO 次数：B+树的非叶节点只包含键，而不包含真实数据，因此每个节点存储的记录个数比 B 数多很多（即阶 m 更大），因此 B+树的高度更低，访问时所需要的 IO 次数更少。此外，由于每个节点存储的记录数更多，所以对访问局部性原理的利用更好，缓存命中率更高。

  • 更适于范围查询：在 B 树中进行范围查询时，首先找到要查找的下限，然后对 B 树进行中序遍历，直到找到查找的上限；而 B+树的范围查询，只需要对链表进行遍历即可。

  • 更稳定的查询效率：B 树的查询时间复杂度在 1 到树高之间(分别对应记录在根节点和叶节点)，而 B+树的查询复杂度则稳定为树高，因为所有数据都在叶节点。

    B+树也存在劣势：由于键会重复出现，因此会占用更多的空间。但是与带来的性能优势相比，空间劣势往往可以接受，因此 B+树的在数据库中的使用比 B 树更加广泛。

知识点补充：思考题: B+树的存储能力如何？为何说一般查找行记录最多只需1-3次磁盘？

2.MyISAM引擎：主要的非事务处理存储引擎

  • MyISAM提供了大量的特性，包括全文索引、压缩、空间函数(GIS)等，但MyISAM 不支持：事务、行级锁、外键 ，有一个毫无疑问的缺陷就是崩溃后无法安全恢复 。

  • 5.5之前默认的存储引擎

  • 优势是访问的 速度快 ，对事务完整性没有要求或者以SELECT、INSERT为主的应用

  • 针对数据统计有额外的常数存储。故而 count(*) 的查询效率很高

  • 数据文件结构：存储在/var/lib/mysql目录下

    • 表名.frm 存储表结构

    • 表名.MYD 存储数据 (MYData)

    • 表名.MYI 存储索引 (MYIndex)

  • 应用场景：只读应用或者以读为主的业务

3.InnoDB引擎和MyISAM引擎对比

对比项

MyISAM

InnoDB

外键

不支持

支持

事务

不支持

支持

行表锁

表锁，即使操作一条记录也会锁住

整个表，不适合高并发的操作

行锁，操作时只锁某一行，不对其它行有影响，

适合高并发的操作

缓存

只缓存索引，不缓存真实数据

不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响

自带系统表使用

是

否

关注点

性能：节省资源、消耗少、简单业务

事务：并发写、事务、更大资源

4. Archive 引擎：用于数据存档

5. Blackhole 引擎：丢弃写操作，读操作会返回空内容

6. CSV 引擎：存储数据时，以逗号分隔各个数据项

7. 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表的最大的大小。

  • 如果 数据是临时的 ，而且 必须立即可用 得到，那么就可以放在内存中。

知识点补充：Hash结构

Hash结构由于存在hash碰撞的情况（计算出来的的hash值相同），需要把hash转换成链表结构，随着链表的逐渐变长，会将数据结构变为红黑树存储，红黑树结构，层数较高，与磁盘会产生较多次IO，如果数据存储较多的情况，速度较慢，hash结构适合于小数据量的情况下。

8. Federated 引擎：访问远程表

Federated引擎是访问其他MySQL服务器的一个 代理 ，尽管该引擎看起来提供了一种很好的 跨服务器的灵活性 ，但也经常带来问题，因此 默认是禁用的 。

9. Merge引擎：管理多个MyISAM表构成的表集合

10. NDB引擎：MySQL集群专用存储引擎

也叫做 NDB Cluster 存储引擎，主要用于 MySQL Cluster 分布式集群 环境，类似于 Oracle 的 RAC 集群。