MySQL存储引擎

存储引擎（引擎层）

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

MySQL 提出了 存储引擎 的概念。简而言之 存储引擎就是指文件系统中存储表已经表数据的类型。其实存储引擎以前叫做 表处理器，后来改名为 存储引擎。

功能：就是接收上层传下来的指令，然后对表中数据进行提取或写入操作。

1. 查看存储引擎

  • 查看mysql提供和默认的存储引擎
show engines;

列（字段）说明：

  • Engine：表示存储引擎名称。
  • Support：表示是否支持该引擎：YES 支持，NO 不支持。DEFAULT 表示默认支持的存储引擎。
  • Comment：表示对存储引擎的描述。
  • Transactions：表示存储引擎是否支持事务：YES 支持，NO 不支持。
  • XA：表示存储引擎是否支持分布式事务：YES 支持，NO 不支持。
  • Savepoints：表示该存储引擎是否支持部分事务回滚：YES 支持，NO 不支持。

注意：InnoDB 是默认存储引擎，且支持事务，分布式事务已经部分事务回滚。

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

  • 查看系统默认的存储引擎 - （全局/会话系统变量）
SHOW VARIABLES LIKE '%storage_engine%'
#　或
SELECT @@default_storage_engine
  • 修改系统默认的存储引擎
SET default_storage_engine = INNODB
  • 或者修改 my.cnf 文件，重启服务后生效
default_storage_engine = InnoDB # 设置系统默认的存储引擎

3. 设置/修改表所使用的存储引擎

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

  • 创建表时，指定使用的存储引擎
CREATE TABLE 表名 (
	列 数据类型 约束 ,....
) ENGINE = 存储引擎...;

例如

CREATE TABLE xld_innoDB (
	ID INT PRIMARY KEY,
	NAME VARCHAR (25)
) ENGINE = INNODB;
  • 修改表，所使用的存储引擎
alter table 表名 engine = 存储引擎;

例如

ALTER TABLE xld_innodb ENGINE = MyISAM;

4. 引擎介绍

4.1 InnoDB 引擎：具备外键支持功能和事务存储引擎

  • MySQL 3.23.34 就有了 InnoDB 存储引擎，在 5.5 之后，默认采用了 InnoDB 引擎。
  • InnoDB 是 MySQL 的 默认事务型引擎，它用来处理大量的短期的事务。可以确保事务的完整提交（Commit）和回滚（Rollback）
  • InnoDB 是 行级锁（操作时只锁某一行，不对其它行有影响，适合高并发操作）
  • 数据文件结构：
    • 表名.frm 存储表结构（8.0 之后，合并在 表名.ibd 中）
    • 表名.ibd 存储数据和索引（8.0 之后，存储表结构，数据和索引）
  • InnoDB 就是 为处理巨大数据量的最大性能而设计的。
  • 相比 MyISAM 存储引擎，InnoDB 写的处理效率差一些，并且会占用更多的磁盘空间以保存数据和索引
  • InnoDB 存储引擎 不仅缓存索引还要缓存真实数据，对内存要求较高。
  • 在 InnoDB 存储引擎中：（聚簇索引）索引既数据。
  • 在开发中，除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑 InnoDB 引擎。

4.2 MyISAM 引擎：主要非事务处理存储引擎

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

4.3 Archive 引擎：用于数据存档

  • Archive 存储引擎，仅仅支持 插入 和 查询 两种功能（被插入后不能再修改）。
  • 在MySQL 5.5以后 支持索引 功能。
  • 拥有很好的压缩机制，使用 zlib压缩库，对记录的数据实时的进行压缩，经常被用来作为仓库使用。
  • 在同样数据量下，Archive表比MyISAM表大约小 75%，比支持事务的 InnoDB表 大约小 83 %。
  • 数据文件结构：
    • 表名.ARZ （存储数据的压缩包）
  • Archive 存储引擎采用了 行级锁。支持 auto_increment 列属性。auto_increment 列可以具有唯一索引或非唯一索引。其他列无法创建索引。
  • Archive 存储引擎 适合日志和数据采集（档案）类应用；适合存储大量的历史记录数据。拥有 很高的插入速度，查询效率较差。

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

  • Blackhole 引擎没有实现任何存储机制，它会 丢弃所有插入的数据，不做任何保存，但服务器会记录 Blackhole 表的日志。
  • 不推荐使用该存储引擎

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

  • CSV引擎可以将 普通的CSV文件作为MySQL的表来处理，但 不支持索引。
  • CSV引擎可以作为一种 数据交换的机制，非常有用。
  • CSV存储的数据直接可以在操作系统里，用文本编辑器，或者excel读取。
  • CSV引擎是不支持列为null，换句话就是，CSV引擎表的列都必须是非空的。
  • 对于数据的快速导入，导出是有明显优势的。
  • 数据文件结构：
    • 表名.CSM 存储表的云数据的。
    • 表名.CSV 存储数据。

4.6 Memory 引擎：置于内存的表

概述：

Memory 引擎是基于 内存的，响应速度很快，但是 当mysqld守护进程崩溃的时候 数据会丢失。另外，要求存储的数据，必须是长度不变的格式，比如，Blob和Test类型的数据不可用（长度不固定的）。

主要特征：

  • Memory同时 支持哈希（HASH）索引 和 B+树索引。

    • 哈希索引相等的比较快，但是对于范围的比较慢很多。
    • 默认使用哈希（HASH）索引，其速度要比较使用B型树（Btree）索引快。

  • Memory表至少比MyISAM表要 快一个数量级。

  • Memory 表的大小是受限制 的。表的大小主要取决于两个参数，分别是 max_rows 和 max_heap_table_size。

    • max_rows 可以在创建表时指定；
    • max_heap_table_size 默认为：16MB ；可以按需求进行扩大

  • 数据文件结构：

    • 表名.frm 只存储表结构（存储在文件系统中的）
    • 表（底层）数据都是存储在内存中

  • 缺点：内存（表）数据易丢失，生命周期短。谨慎使用。

使用Memory存储引擎的场景：

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

4.7 Federated 引擎：访问远程表

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

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

4.9 Ndb 引擎：MySQL集群专用存储引擎

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

5. InnoDB vs MyISAM

MySQL 5.5 之前的默认存储引擎是 MyISAM，5.5 之后改为了 InnoDB

5.1 InnoDB

InnoDB 提供了良好的事务管理，崩溃修复能力和并发控制。所以对于要求事务完整性的场合需要选择 InnoDB。

其缺点是：读写效率稍差，占用的数据空间相对比较大。

5.2 MyISAM

MyISAM 占用空间小，处理速度快。如果是 小型系统，系统多以 读操作和插入操作为主，只有很少的更新，删除操作比，并且对事务的要求没有那么高，则可以选择 MyISAM 存储引擎

其缺点是：不支持事务 的完整性和并发性。

5.3 InnoDB 和 MyISAM 对比表

对比项 InnoDB MyISAM
外键 支持 不支持
事务 支持 不支持
行表锁 行锁，操作时之锁某一行，不对其它行有影响，适合高并发的操作 表锁，即使操作一条记录也会锁住整个表，不适合高并发操作
缓存 不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响 只缓存索引，不缓存真实数据
自带系统表使用 N Y
关注点 事务：并发写，事务，更大资源 性能：节省资源，消耗少，简单业务
默认安装 Y Y
默认使用 Y N
posted @ 2023-11-18 14:55  小林当  阅读(20)  评论(0)  编辑  收藏  举报