第05章_存储引擎

1. 查看存储引擎

show engines;

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

show variables like '%storage_engine%';

修改默认的存储引擎

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

SET DEFAULT_STORAGE_ENGINE=MyISAM;

或者修改 my.cnf 文件：

default-storage-engine=MyISAM
# 重启服务

systemctl restart mysqld.service

3. 设置表的存储引擎

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

3.1 创建表时指定存储引擎

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

3.2 查看表的存储引擎

 SHOW CREATE TABLE 表名

mysql> show create table jobs;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                  |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| jobs  | CREATE TABLE `jobs` (
  `job_id` varchar(10) NOT NULL DEFAULT '',
  `job_title` varchar(35) NOT NULL,
  `min_salary` int DEFAULT NULL,
  `max_salary` int DEFAULT NULL,
  PRIMARY KEY (`job_id`),
  UNIQUE KEY `job_id_pk` (`job_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

3.2 修改表的存储引擎

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

4.引擎介绍

4.1InnoDB引擎

• MySQL从3.23.34a开始就包含InnoDB存储引擎。 大于等于5.5之后，默认采用InnoDB引擎；
• InnoDB支持事务；
• InnoDB支持外键；
• 除了增加和查询外，还需要更新、删除操作，就优先选择InnoDB存储引擎；
• InnoDB支持行级锁，适合数据量大、高并发的数据库；
• .frm存储表结构，.ibd存储数据和索引；
• 缺点：
  • 对比MyISAM的存储引擎， InnoDB写的处理效率差一些；
  • InnoDB不仅缓存索引还要缓存真实数据， 对内存要求较
    高，而且内存大小对性能有决定性的影响。

4.2 MyISAM 引擎

• MyISAM 不支持事务、行级锁、外键 ，有一个毫无疑问的缺陷就是 崩溃后无法安全恢复 。
• 5.5版本之前的默认引擎是MyISAM；
• 访问的 速度快 ，适合对事务完整性没有要求或者以SELECT、INSERT为主或者数据量小的数据库；
• 对数据统计有额外的常数存储。故而 count(*) 的查询效率很高,时间复杂度是O(1)；
• 数据和索引是存储的
  • 表名.frm 存储表结构
  • 表名.myd 存储数据 (MYData)
  • 表名.myi 存储索引 (MYIndex)

4.3 Archive 引擎

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

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

Blackhole引擎没有实现任何存储机制，它会丢弃所有插入的数据，不做任何保存。

4.5 CSV 引擎

• 不支持索引；
• 如果以CSV作为存储引擎，那么所有的字段都要设置为非空；
• 如果以CSV作为存储引擎，那么表就是一个普通的 .CSV文件，特点就是可以使用文本编辑器、excel读取；
• 如果以CSV作为存储引擎，那么表就可以用来做数据交换；

mysql> CREATE TABLE csv_demo (i INT NOT NULL, c CHAR(10) NOT NULL) ENGINE = CSV;
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO csv_demo VALUES(1,'atguigu01'),(2,'atguigu02');
Query OK, 2 rows affected (0.05 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM csv_demo;
+---+------------+
| i | c |
+---+------------+
| 1 | atguigu01 |
| 2 | atguigu02 |
+---+------------+
2 rows in set (0.00 sec)

创建CSV表还会创建相应的 元文件 ，用于 存储表的状态 和 表中存在的行数 。此文件的名称与表的名称相同，后缀为 CSM 。我们使用ll命令 查看文件信息：

- rw-r----. 1 mysql mysql    30 8月19 21:08 csv_demo.CSV
- rw-r----. 1 mysql mysql    35 8月19 21:08 csv_demo.CSM

同时我们可以使用文本文档或者excel打开，效果如下

4.6 Memory 引擎：置于内存的表

主要特征：

• 如果以Memory作为存储引擎，那么表是存储在内存中的(memory是内存的意思)，响应速度很快，比MyISAM表要 快一个数量级 。
• Memory同时 支持哈希（HASH）索引 和B+树索引 。默认是哈希索引；
• MEMORY 表的大小是受到限制的。表的大小主要取决于两个参数，分别是 max_rows 和max_heap_table_size 。其中，max_rows可以在创建表时指定；max_heap_table_size的大小默认为16MB，可以按需要进行扩大。
• 缺点：数据容易丢失。

使用Memory存储引擎的场景：

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

4.7 Federated 引擎：访问远程表

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

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