MySQL --- 存储引擎1.查看存储引擎2.设置系统默认的存储引擎3.设置表的存储引擎4.引擎介绍5. MyISAM和InnoDB

87 阅读 0 评论 58 点赞

我是靠谱客的博主开朗大象，最近开发中收集的这篇文章主要介绍MySQL --- 存储引擎1.查看存储引擎2.设置系统默认的存储引擎3.设置表的存储引擎4.引擎介绍5. MyISAM和InnoDB，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

文章目录

1.查看存储引擎
2.设置系统默认的存储引擎
3.设置表的存储引擎
- 3.1创建表时指定存储引擎
- 3.2修改表的存储引擎
4.引擎介绍
- InnoDB 引擎：具备外键支持功能的事务存储引擎
- MyISAM 引擎：主要的非事务处理存储引擎
5. MyISAM和InnoDB

1.查看存储引擎

show engines 或 show enginesG

root@(none) 20:58
mysql>show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine
| Support | Comment
| Transactions | XA
| Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB
| DEFAULT | Supports transactions, row-level locking, and foreign keys
| YES
| YES
| YES
|
| MRG_MYISAM
| YES
| Collection of identical MyISAM tables
| NO
| NO
| NO
|
| MEMORY
| YES
| Hash based, stored in memory, useful for temporary tables
| NO
| NO
| NO
|
| BLACKHOLE
| YES
| /dev/null storage engine (anything you write to it disappears) | NO
| NO
| NO
|
| MyISAM
| YES
| MyISAM storage engine
| NO
| NO
| NO
|
| CSV
| YES
| CSV storage engine
| NO
| NO
| NO
|
| ARCHIVE
| YES
| Archive storage engine
| NO
| NO
| NO
|
| PERFORMANCE_SCHEMA | YES
| Performance Schema
| NO
| NO
| NO
|
| FEDERATED
| NO
| Federated MySQL storage engine
| NULL
| NULL | NULL
|
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
root@(none) 20:59
mysql>show enginesG;
*************************** 1. row ***************************
Engine: InnoDB
Support: DEFAULT
Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
XA: YES
Savepoints: YES
*************************** 2. row ***************************
Engine: MRG_MYISAM
Support: YES
Comment: Collection of identical MyISAM tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 3. row ***************************
Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 4. row ***************************
Engine: BLACKHOLE
Support: YES
Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
XA: NO
Savepoints: NO
*************************** 5. row ***************************
Engine: MyISAM
Support: YES
Comment: MyISAM storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 6. row ***************************
Engine: CSV
Support: YES
Comment: CSV storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 7. row ***************************
Engine: ARCHIVE
Support: YES
Comment: Archive storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 8. row ***************************
Engine: PERFORMANCE_SCHEMA
Support: YES
Comment: Performance Schema
Transactions: NO
XA: NO
Savepoints: NO
*************************** 9. row ***************************
Engine: FEDERATED
Support: NO
Comment: Federated MySQL storage engine
Transactions: NULL
XA: NULL
Savepoints: NULL
9 rows in set (0.00 sec)
ERROR:
No query specified

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

查看默认的存储引擎：
show variables like ‘%storage_engine%’;
SELECT @@default_storage_engine;

root@(none) 20:59
mysql>show variables like '%storage_engine%';
+----------------------------------+--------+
| Variable_name
| Value
|
+----------------------------------+--------+
| default_storage_engine
| InnoDB |
| default_tmp_storage_engine
| InnoDB |
| disabled_storage_engines
|
|
| internal_tmp_disk_storage_engine | InnoDB |
+----------------------------------+--------+
4 rows in set (0.01 sec)
root@(none) 21:01
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

3.设置表的存储引擎

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

3.1创建表时指定存储引擎

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

3.2修改表的存储引擎

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

4.引擎介绍

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

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

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

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

Archive引擎：用于数据存档

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

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

Memory引擎：置于内存的表

Federated引擎：访问远程表

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

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

5. MyISAM和InnoDB

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