MYSQL05高级_查看修改存储引擎、InnoDB和MyISAM对比、其他存储引擎介绍

文章目录

①. 查看、修改存储引擎
②. InnoDB和MyISAM对比
③. Archive引擎 - 归档
④. Blackhole引擎丢数据
⑤. CSV - 引擎
⑥. Memory引擎 - 内存表
⑦. Federated引擎 - 访问远程表
⑧. Merge引擎 - 管理多个MyISAM
⑨. NDB引擎 - 集群专用

①. 查看、修改存储引擎

①. 查看mysql提供什么存储引擎

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

②. 查看默认的存储引擎

mysql> show variables like '%storage_engine%';
+---------------------------------+-----------+
| Variable_name                   | Value     |
+---------------------------------+-----------+
| default_storage_engine          | InnoDB    |
| default_tmp_storage_engine      | InnoDB    |
| disabled_storage_engines        |           |
| internal_tmp_mem_storage_engine | TempTable |
+---------------------------------+-----------+
4 rows in set (0.01 sec)

mysql> SELECT @@default_storage_engine;
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| InnoDB                   |
+--------------------------+
1 row in set (0.00 sec)

③. 修改默认的存储引擎
如果在创建表的语句中没有显式指定表的存储引擎的话,那就会默认使用 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和MyISAM对比

①. InnoDB引擎:具备外键支持功能的事务存储引擎,行锁

MySQL从3.23.34开始就包含InnoDB存储引擎。大于等于5.5之后,默认采用InnoDB引擎
InnoDB是MySQL的默认事务型引擎,它被设计用来处理大量的短期(short-lived)事务。可以确保事务的完整提交(Commit)和回滚(Rollback)
除了增加和查询外,还需要更新、删除操作,那么,应优先选择InnoDB存储引擎除非有非常特别的原因需要使用其他的存储引擎,否则应该优先考虑InnoDB引擎
数据文件结构:
表名.frm存储表结构（MySQL8.0时,合并在表名.ibd中）
表名.ibd存储数据和索引
InnoDB是为处理巨大数据量的最大性能设计
对比MyISAM的存储引擎,InnoDB写的处理效率差一些,并且会占用更多的磁盘空间以保存数据和索引
MyISAM只缓存索引,不缓存真实数据。InnoDB不仅缓存索引还要缓存真实数据,对内存要求较高,而且内存大小对性能有决定性的影响

②. MyISAM引擎:主要的非事务处理存储引擎

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

③. MyISAM和InnoDB对比

③. Archive引擎 - 归档

①. archive是归档的意思,仅仅支持插入和查询两个功能(行被插入后不能修改)
②. 在MYSQL5.5以后支持索引功能
③. 拥有很好的压缩机制,使用zlib压缩库,在记录请求的时候实时进行压缩,经常被用来作为仓库使用
④. acchive适合日志和数据采集(归档)类应用,适合存储大量的独立的作为历史记录的数据,拥有很高的插入速度,但是对查询的支持较差

④. Blackhole引擎丢数据

①. Blackhole引擎没有实现任何存储机制,它会丢弃所有插入的数据,不做任何保存
②. 服务器会记录Blackhole表的日志,所以可以用来复制数据到备库,或者简单地记录到日志。但这种应用会碰到很多问题,因此并不推荐

⑤. CSV - 引擎

①. 使用案例如下

mysql> SELECT @@default_storage_engine;
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| InnoDB                   |
+--------------------------+
1 row in set (0.00 sec)

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

mysql> INSERT INTO test VALUES(1,'record one'),(2,'record two');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM test;
+---+------------+
| i | c          |
+---+------------+
| 1 | record one |
| 2 | record two |
+---+------------+
2 rows in set (0.00 sec)

②. 创建CSV表还会创建相应的元文件 ,用于存储表的状态和表中存在的行数。此文件的名称与表的名称相同,后缀为CSM
③. 如果检查test.CSV 通过执行上述语句创建的数据库目录中的文件,其内容使用Notepad++打开如下:

"1","record one"
"2","record two"

④. 这种格式可以被 Microsoft Excel 等电子表格应用程序读取,甚至写入。使用Microsoft Excel打开如图所示

⑥. 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表的最大的大小
如果数据是临时的 ,而且必须立即可用得到,那么就可以放在内存中
存储在Memory表中的数据如果突然间丢失的话也没有太大的关系