首页 > 编程笔记 > MySQL笔记 阅读：15

MySQL存储引擎详解（新手必看）

通义灵码
MySQL 提供了多个不同的存储引擎，包括处理事务安全表的引擎和处理非事务安全表的引擎。在 MySQL 中，不需要在整个服务器中使用同一种存储引擎，针对具体的要求，可以对每一个表使用不同的存储引擎。

MySQL 支持的存储引擎有 InnoDB、MyISAM、Memory、NDB、Merge、Archive、Federated、CSV、BLACKHOLE 等。

可以使用 SHOW ENGINES 语句查看系统所支持的引擎类型，结果如下： 
  1. mysql> SHOW ENGINES \G
  2. *** 1. row ***
  3.         Engine: FEDERATED
  4.       Support: NO
  5.   Comment: Federated MySQL storage engine
  6. Transactions: NULL
  7.           XA: NULL
  8. Savepoints: NULL
  9.  
  10. *** 2. row ***
  11.         Engine: MRG_MYISAM
  12.       Support: YES
  13.   Comment: Collection of identical MyISAM tables
  14. Transactions: NO
  15.           XA: NO
  16. Savepoints: NO
  17.  
  18. *** 3. row ***
  19.         Engine: MyISAM
  20.       Support: YES
  21.   Comment: MyISAM storage engine
  22. Transactions: NO
  23.           XA: NO
  24. Savepoints: NO
  25.  
  26. *** 4. row ***
  27.         Engine: BLACKHOLE
  28.       Support: YES
  29.   Comment: /dev/null storage engine (anything you write to it disappears)
  30. Transactions: NO
  31.           XA: NO
  32. Savepoints: NO
  33.  
  34. *** 5. row ***
  35.         Engine: CSV
  36.       Support: YES
  37.   Comment: CSV storage engine
  38. Transactions: NO
  39.           XA: NO
  40. Savepoints: NO
  41.  
  42. *** 6. row ***
  43.         Engine: MEMORY
  44.       Support: YES
  45.   Comment: Hash based, stored in memory, useful for temporary tables
  46. Transactions: NO
  47.           XA: NO
  48. Savepoints: NO
  49.  
  50. *** 7. row ***
  51.         Engine: ARCHIVE
  52.       Support: YES
  53.   Comment: Archive storage engine
  54. Transactions: NO
  55.           XA: NO
  56. Savepoints: NO
  57.  
  58. *** 8. row ***
  59.         Engine: InnoDB
  60.       Support: DEFAULT
  61.   Comment: Supports transactions, row-level locking, and foreign keys
  62. Transactions: YES
  63.           XA: YES
  64. Savepoints: YES
  65.  
  66. *** 9. row ***
  67.         Engine: PERFORMANCE_SCHEMA
  68.       Support: YES
  69.   Comment: Performance Schema
  70. Transactions: NO
  71.           XA: NO
  72. Savepoints: NO
Support 列的值表示某种引擎是否能使用：YES 表示可以使用，NO 表示不能使用，DEFAULT 表示该引擎为当前默认的存储引擎。

查看当前默认的存储引擎，可以使用下面的命令： 
  1. mysql> show variables like '%storage_engine%';
  2. +---------------------------------+-----------+
  3. | Variable_name                   | Value     |
  4. +---------------------------------+-----------+
  5. | default_storage_engine          | InnoDB    |
  6. | default_tmp_storage_engine      | InnoDB    |
  7. | disabled_storage_engines        |           |
  8. | internal_tmp_mem_storage_engine | TempTable |
  9. +---------------------------------+-----------+

更改数据表的存储引擎

MySQL 数据库在创建表的时候，可以添加默认的存储引擎。例如下面的例子： 
  1. create table books(
  2. id int,
  3. name varchar(20) not null
  4. )engine=MyISAM default charset=utf8mb4;

更改表的存储引擎的语法格式如下： 
ALTER TABLE <表名> ENGINE=<更改后的存储引擎名>;

下面示例将数据表 books 的存储引擎修改为 InnoDB。在修改存储引擎之前，先使用SHOW create table查看表 books 当前的存储引擎，结果如下： 
  1. mysql> SHOW create table books \G
  2. *************************** 1. row ***************************
  3.      Table: books
  4. Create Table: create table `books` (
  5.   `id` int DEFAULT NULL,
  6.   `name` varchar(20) NOT NULL
  7. ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
可以看到，表 books 当前的存储引擎为 ENGINE=MyISAM，接下来修改存储引擎类型，输入如下 SQL 语句并执行： 
  • mysql> ALTER TABLE books ENGINE=InnoDB;

使用 SHOW create table 再次查看表 books 的存储引擎，发现表 books 的存储引擎变成了“InnoDB”，结果如下： 
  1. mysql> SHOW create table books \G
  2. *************************** 1. row ***************************
  3.      Table: books
  4. Create Table: create table `books` (
  5.   `id` int DEFAULT NULL,
  6.   `name` varchar(20) NOT NULL
  7. ) ENGINE= InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

选择合适的存储引擎

不同存储引擎都有各自的特点，以适应不同的需求。为了做出选择，首先需要考虑每一个存储引擎提供了哪些不同的功能。

下表分析了常用存储引擎的对比情况。

表：常用存储引擎的对比情况
特点 InnoDB MyISAM MEMORY MERGE
存储限制 64TB 有 有 没有
事务安全 支持 - - -
锁机制 行锁 表锁 表锁 表锁
B 数索引 支持 支持 支持 支持
哈希索引 - - 支持 -
全文索引 - 支持 - -
集群索引 支持 - - -
数据缓存 支持 - 支持 -
索引缓存 支持 支持 支持 支持
数据可压缩 - 支持 - -
空间使用 高 低 N/A 低
内存使用 高 低 中等 低
批量插入速度 低 高 高 高
支持外键 支持 - - -


使用哪一种引擎要根据需要灵活选择，一个数据库中多个表可以使用不同引擎，以满足各种性能和实际需求。使用合适的存储引擎，将会提高整个数据库的性能。

相关文章