1.查看存储引擎

show engines 或 show engines\G

[email protected](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)

[email protected](none) 20:59  mysql>show engines\G;
*************************** 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;

[email protected](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)

[email protected](none) 21:01  mysql>SELECT @@default_storage_engine;
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| InnoDB                   |
+--------------------------+
1 row in set (0.00 sec)

• 修改默认的存储引擎
  If the table's storage engine is not specified in the statement that creates the table,那就会默认使用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