余生慢尝

存储引擎

MySQL中的数据用各种不同的技术存储在文件（或者内存）中。 这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。 通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。

首先我们先来看一下关于存储引擎的 SQL

相关 SQL

查看存储引擎

-- mysql 的所有存储引擎
show engines;

image-20221115152300200

-- 查看表的存储引擎
show create table 表明

查看系统默认的存储引擎


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

修改默认的存储引擎

如果在创建表的语句中没有显式指定表的存储引擎的话，那就会默认使用 InnoDB 作为表的存储引擎。 如果我们想改变表的默认存储引擎的话，可以这样写启动服务器的命令行：

-- 修改默认存储引擎
SET DEFAULT_STORAGE_ENGINE=MyISAM;

或者修改 my.cnf 文件：

default-storage-engine=MyISAM
# 重启服务
systemctl restart mysqld.service

设置表的存储引擎

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

  1. 创建表时指定存储引擎

    create table 表明(
 建表语句;
) engine = 存储引擎名称;

  2. 修改表的存储引擎

    alter table 表明 engine = 存储引擎名称;

引擎介绍

InnoDB 引擎

具备外键支持功能的事务存储引擎，InnoDB是事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键

  • MySQL从3.23.34a开始就包含InnoDB存储引擎。 大于等于5.5之后，默认采用InnoDB引擎 。
  • InnoDB是MySQL的 默认事务型引擎 ，它被设计用来处理大量的短期(short-lived)事务。可以确保事务的完整提交(Commit)和回滚(Rollback)。
  • 对比MyISAM的存储引擎， InnoDB写的处理效率差一些 ，并且会占用更多的磁盘空间以保存数据和索引。
  • MyISAM只缓存索引，不缓存真实数据；InnoDB不仅缓存索引还要缓存真实数据， 对内存要求较高 ，而且内存大小对性能有决定性的影响。

MyISAM 引擎

MyISAM拥有较高的插入、查询速度，但不支持事物。MyISAM主要特性有：

  • MyISAM提供了大量的特性，包括全文索引、压缩、空间函数(GIS)等，但MyISAM 不支持事务、行级锁、外键 ，有一个毫无疑问的缺陷就是 崩溃后无法安全恢复。
  • 5.5之前默认的存储引擎
  • 优势是访问的 速度快 ，对事务完整性没有要求或者以SELECT、INSERT为主的应用
  • 针对数据统计有额外的常数存储。故而 count(*) 的查询效率很高
  • 应用场景：只读应用或者以读为主的业务

Archive 引擎：用于数据存档

image-20221115165606323

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存储引擎时需要特别小心。

使用场景：

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

InnoDB 和 MyISAM 的区别

不同的存储引擎都有各自的特点，以适应不同的需求，如下表所示：

对比项MYISAMInnoDB
支持外键不支持支持
支持事物不支持支持
行表锁表锁，即使操作一条记录也会锁住整个表，不适合高并发的操作没有索引情况下是表锁，有索引的情况下是行锁
缓存只缓存索引不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响
支持全文索引支持支持（5.7以后支持）
支持哈希索引NoNo

如果要提供提交、回滚、崩溃恢复能力的事物安全（ACID兼容）能力，并要求实现并发控制，InnoDB是一个好的选择

如果数据表主要用来插入和查询记录，则MyISAM引擎能提供较高的处理效率

如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存中的Memory引擎，MySQL中使用该引擎作为临时表，存放查询的中间结果

如果只有INSERT和SELECT操作，可以选择Archive，Archive支持高并发的插入操作，但是本身不是事务安全的。Archive非常适合存储归档数据，如记录日志信息可以使用Archive

使用哪一种引擎需要灵活选择，一个数据库中多个表可以使用不同引擎以满足各种性能和实际需求，使用合适的存储引擎，将会提高整个数据库的性能