第二十三章：存储引擎

最新推荐文章于 2024-09-26 14:19:34 发布

小王超会敲代码

最新推荐文章于 2024-09-26 14:19:34 发布

阅读量82

点赞数

分类专栏： MySQL 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/wcy_0522/article/details/131671770

版权

MySQL 专栏收录该内容

37 篇文章 0 订阅

订阅专栏

第二十三章：存储引擎

为了管理方便，人们把连接管理、查询缓存、语法解析、查询优化这些并不设计真实数据存储的功能划分为MySQL server的功能，把真实存取数据的功能划分为存储引擎的功能。所以在MySQL server完成了查询优化后，只需按照生成的执行计划调用底层存储引擎提供的API，获取到数据后返回给客户端就好了。

23.1：查看存储引擎

查看MySQL提供了什么存储存储引擎
```
show engines;
```
- Engine参数：表示存储引擎名称。
- Support参数：表示MySQL数据库管理系统是否支持该存储引擎。【YES表示支持，NO表示不支持】
- DEFAULT参数：表示系统默认支持的存储引擎。
- Comment参数：表示对存储引擎的评论。
- Transactions参数：表示存储引擎是否支持事物。【YES表示支持，NO表示不支持】
- XA参数：表示存储引擎所支持的分布式是否符合XA规范。【YES表示支持，NO表示不支持】代表着该存储引擎是否支持分布式事物。
- Savepoints参数：表示存储引擎是否支持事物处理的保存点。【YES表示支持，NO表示不支持】该存储引擎是否支持部分事物回滚。

23.2：设置系统默认的存储引擎

查看默认的存储引擎

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

修改默认的存储引擎

如果在创建表的语句中没有显示指定表的存储引擎的话，那就会默认使用InnoDB作为表的存储引擎。如果我们想改变表的默认存储引擎的话。
```
# 修改默认存储引擎
SET DEFAULT_STORAGE_ENGINE=存储引擎名称;

# 在my.cnf文件中修改默认存储引擎
default-storage-engine=存储引擎名称
```

23.3：设置表的存储引擎

存储引擎是负责对表中的数据进行提取和写入工作的，我们可以为不同的表设置不同的存储引擎，也就是说不同的表可以有不同的物理存储结构，不同的提取和写入方式。

创建表时指定存储引擎

之前创建表的语句都没有指定表的存储引擎，那就会使用默认的存储引擎InnoDB。如果想显示的指定一下表的存储引擎。
```
CREATE TABLE 表名(
	建表语句;
)ENGINE=存储引擎名称;
```
修改表的存储引擎

如果表已经建好了，我们也可以使用下边这个语句来修改表的存储引擎：
```
ALTER TABLE 表名 ENGINE = 存储引擎名称;
```

23.4：引擎介绍

InnoDB：具备外键支持功能的事物存储引擎
- MySQL从3.23.34a开始就包含InnoDB存储引擎。大于等于5.5之后，默认采用InnoDB引擎。
- InnoDB是MySQL的默认事物型引擎，它被设计用来处理大量的短期(short-lived)事物。可以确保事物的完整提交(Commit)和回滚(Rollback)。
- 除了增加和查询外，还需要更新、删除操作，那么，应优先选择InnoDB存储引擎。
- InnoDB是为处理巨大数据量的最大性能设计。行锁，操作时只锁某一行，不对其他有影响，适合高并发的操作。
- 对比MyISAM的存储引擎，InnoDB写的处理效率差一些，并且会占用更多的磁盘空间以保存数据和索引。
- MyISAM只缓存索引，不缓存真实数据，InnoDB不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响。
MyISAM：主要的非事物处理存储引擎
- MyISAM提供了大量的特性，包括全文索引、压缩、空间函数(GIS)等，但MyISAM不支持事物、行级锁、外键，有一个毫无疑问的缺陷就是崩溃后无法安全恢复。
- 5.5之前默认的存储引擎。
- 优势是访问的速度快，对事物完整性没有要求或者以SELECT、INSERT为主的应用。
- 针对数据统计有额外的常数存储。故而count(*)的查询效率跟高。
- 表锁，即使操作一条记录也会锁住整个表，不适合高并发的操作。

Archive：用于数据存档

archive是归档的意思，仅仅支持插入和查询两种功能(行被插入后不能再修改)。
在MySQL 5.5以后支持索引功能。
拥有很多的压缩机制，使用zlib压缩库，在记录请求的时候实时的进行压缩，经常被用来作为仓库使用。
创建ARCHIVE表示，存储引擎会创建名称以表名开头的文件。数据文件的扩展名为.ARZ。
根据英文的测试结论来看，同样数据量下，Archive表比MyISAM表要小大约75%，比支持事物处理的InnoDB表小大约83%。
ARCHIVE存储引擎采用了行级锁。该ARCHIVE引擎支持AUTO_INCREMENT列属性。AUTO_INCRMENT列可以具有唯一索引或非唯一索引。尝试在任何其他列上创建索引会导致错误。
Archive表适合日志和数据采集(档案)类应用；适合存储大量的独立的作为历史记录的数据。拥有很高的插入速度，但是对查询的支持较差。

特征	支持
B树索引	不支持
备份/时间点恢复(在服务器中实现，而不是在存储引擎中)	支持
集群数据库支持	不支持
聚集索引	不支持
压缩数据	支持
数据缓存	不支持
加密数据(加密功能在服务器中实现)	支持
外键支持	不支持
全文检索索引	不支持
地理空间数据类型支持	支持
地理空间索引支持	不支持
哈希索引	不支持
索引缓存	不支持
锁粒度	行锁
`MVCC`	不支持
存储限制	没有任何限制
交易	不支持
更新数据字典的统计信息	支持

Blackhole：丢弃写操作，读操作返回空内容
- Blackhole引擎没有实现任何存储机制，它会丢弃所有插入的数据，不做任何保存。
- 但服务器会记录Blackhole表的日志，所以可以用于复制数据到备库，或者简单地记录到日志。但这种应用方式会碰到很多问题，因此并不推荐。
CSV：存储数据时，以逗号分隔各个数据项
- CSV引擎可以将普通的CSV文件作为MySQL的表来处理，但不支持索引。
- CSV引擎可以作为一种数据交换的机制，非常有用。
- CSV存储的数据直接可以在操作系统里，用文本编辑器，或者excel读取。
- 对于数据的快速导入、导出是有明显优势的。
创建CSV表时，服务器会创建一个纯文本数据文件，其名称以表名开头并带有.csv扩展名。当你将数据存储到表中时，存储引擎将其以逗号分隔值格式保存到数据文件中。

创建CSV表还会创建相应的元文件，用于存储表的状态和表中存在的行数。此文件的名称与表的名称相同，后缀为CSM。
Memory：置于内存的表
- 概述
  
  Memory采用的逻辑介质是内存，相应速度很快，但是当mysqld守护进程崩溃的时候数据会丢失。另外，要求存储的数据是数据长度不变的格式。
- 主要特征
  1. Memory同时支持哈希(HASH)索引【默认】和**B+数索引**。
  2. Memory表至少比MyISAM表要快一个数量级。
  3. MEMORY表的大小是受限制的。表的大小主要取决于两个参数，分别是max_rows【创建表示指定】和max_heap_table_size【大小默认为16MB，可以按需要进行扩大】。
  4. 数据文件与索引文件分开存储【表结构存储在frm类型文件，数据存储在内存】。
  5. 其数据易丢失，生命周期短。基于这个缺陷，选择MEMORY存储引擎时需要特别小心。
- 使用Memory存储引擎的场景
  1. 目标数据比较小，而且非常频繁的进行访问，在内存中存放数据，如果太大的数据会造成内存溢出。可以通过参数max_heap_table_size控制Memory表的大小，限制Memory表的最大的大小。
  2. 如果数据是临时的，而且必须立即可用得到，那么就可以存放在内存中。
  3. 存储在Memory表中的数据如果突然间丢失的话也没有太大的关系。
Federated：访问远程表
- Federated引擎是访问其他MySQL服务器的一个代理，尽管该引擎看起来提供了一种很好的跨服务器的灵活性，但也经常带来问题，因此默认是禁用的。
Merge：管理多个MyISAM表构成的表集合
NDB：MySQL集群专用存储引擎
- 也叫做NDB Cluster存储引擎，主要用于**MySQL Cluster分布式集群**环境，类似于Oracle的RAC集群。

引擎对比

MySQL中同一个数据，不同的表可以选择不同的存储引擎。如下表对常用存储引擎做出了对比。

特点	`MyISAM`	`InnoDB`	`MEMORY`	`MERGE`	`NDB`
存储限制	有	`64TB`	有	没有	有
事物安全		支持
锁机制	表锁	行锁	表锁	表锁	行锁
B数索引	支持	支持	支持	支持	支持
哈希索引			支持		支持
全文索引	支持
集群索引		支持
数据缓存		支持	支持		支持
索引缓存	只缓存索引，不缓存数据	索引数据都缓存	支持	支持	支持
数据可压缩	支持
空间使用	低	高	`N/A`	低	低
内存使用	低	高	中等	低	高
批量插入的速度	高	低	高	高	高
支持外键		支持

23.5：MyISAM和InnoDB

MyISAM和InnoDB是互相结合使用的，并不是替代关系。

首先对于InnoDB存储引擎，提供了良好的事物管理、崩溃修复能力和并发控制。因为InnoDB存储引擎支持事物，所以对于要求事物完整性的场合需要选择InnoDB，缺点是其读写效率稍差，占用的数据空间相对较大。

其次对于MyISAM存储引擎，如果是小型应用，系统以读操作和插入操作为主，只有很少的更新、删除操作，并且对事物的要求没有那么高，则可以选择这个存储引擎。MyISAM存储引擎的优势在于占用空间小，处理速度快；缺点是不支持事物的完整性和并发性。

对比项	`MyISAM`	`InnoDB`
外键	不支持	支持
事物	不支持	支持
行表锁	表锁，即使操作一条记录也会锁住整个表，不适合高并发的操作	行锁，操作时只锁某一行，不对其他有影响，适合高并发的操作
缓存	只缓存索引，不缓存真实数据	不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响。
自带系统表使用	Y	N
关注点	性能：节省资源、消耗少、简单业务	事物：并发写、事物、更大资源
默认安装	Y	Y
默认使用	N	Y