MySQL存储引擎详解(新手必看)
MySQL 提供了多个不同的存储引擎,包括处理事务安全表的引擎和处理非事务安全表的引擎。在 MySQL 中,不需要在整个服务器中使用同一种存储引擎,针对具体的要求,可以对每一个表使用不同的存储引擎。
MySQL 支持的存储引擎有 InnoDB、MyISAM、Memory、NDB、Merge、Archive、Federated、CSV、BLACKHOLE 等。
可以使用 SHOW ENGINES 语句查看系统所支持的引擎类型,结果如下:
查看当前默认的存储引擎,可以使用下面的命令:
更改表的存储引擎的语法格式如下:
下面示例将数据表 books 的存储引擎修改为 InnoDB。在修改存储引擎之前,先使用SHOW create table查看表 books 当前的存储引擎,结果如下:
使用 SHOW create table 再次查看表 books 的存储引擎,发现表 books 的存储引擎变成了“InnoDB”,结果如下:
下表分析了常用存储引擎的对比情况。
使用哪一种引擎要根据需要灵活选择,一个数据库中多个表可以使用不同引擎,以满足各种性能和实际需求。使用合适的存储引擎,将会提高整个数据库的性能。
MySQL 支持的存储引擎有 InnoDB、MyISAM、Memory、NDB、Merge、Archive、Federated、CSV、BLACKHOLE 等。
可以使用 SHOW ENGINES 语句查看系统所支持的引擎类型,结果如下:
mysql> SHOW ENGINES \G *** 1. row *** Engine: FEDERATED Support: NO Comment: Federated MySQL storage engine Transactions: NULL XA: NULL Savepoints: NULL *** 2. row *** Engine: MRG_MYISAM Support: YES Comment: Collection of identical MyISAM tables Transactions: NO XA: NO Savepoints: NO *** 3. row *** Engine: MyISAM Support: YES Comment: MyISAM storage engine 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: CSV Support: YES Comment: CSV storage engine Transactions: NO XA: NO Savepoints: NO *** 6. row *** Engine: MEMORY Support: YES Comment: Hash based, stored in memory, useful for temporary tables 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: InnoDB Support: DEFAULT Comment: Supports transactions, row-level locking, and foreign keys Transactions: YES XA: YES Savepoints: YES *** 9. row *** Engine: PERFORMANCE_SCHEMA Support: YES Comment: Performance Schema Transactions: NO XA: NO Savepoints: NOSupport 列的值表示某种引擎是否能使用:YES 表示可以使用,NO 表示不能使用,DEFAULT 表示该引擎为当前默认的存储引擎。
查看当前默认的存储引擎,可以使用下面的命令:
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 | +---------------------------------+-----------+
更改数据表的存储引擎
MySQL 数据库在创建表的时候,可以添加默认的存储引擎。例如下面的例子:create table books( id int, name varchar(20) not null )engine=MyISAM default charset=utf8mb4;
更改表的存储引擎的语法格式如下:
ALTER TABLE <表名> ENGINE=<更改后的存储引擎名>;
下面示例将数据表 books 的存储引擎修改为 InnoDB。在修改存储引擎之前,先使用SHOW create table查看表 books 当前的存储引擎,结果如下:
mysql> SHOW create table books \G *************************** 1. row *************************** Table: books Create Table: create table `books` ( `id` int DEFAULT NULL, `name` varchar(20) NOT NULL ) 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”,结果如下:
mysql> SHOW create table books \G *************************** 1. row *************************** Table: books Create Table: create table `books` ( `id` int DEFAULT NULL, `name` varchar(20) NOT NULL ) ENGINE= InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
选择合适的存储引擎
不同存储引擎都有各自的特点,以适应不同的需求。为了做出选择,首先需要考虑每一个存储引擎提供了哪些不同的功能。下表分析了常用存储引擎的对比情况。
特点 | InnoDB | MyISAM | MEMORY | MERGE |
---|---|---|---|---|
存储限制 | 64TB | 有 | 有 | 没有 |
事务安全 | 支持 | - | - | - |
锁机制 | 行锁 | 表锁 | 表锁 | 表锁 |
B 数索引 | 支持 | 支持 | 支持 | 支持 |
哈希索引 | - | - | 支持 | - |
全文索引 | - | 支持 | - | - |
集群索引 | 支持 | - | - | - |
数据缓存 | 支持 | - | 支持 | - |
索引缓存 | 支持 | 支持 | 支持 | 支持 |
数据可压缩 | - | 支持 | - | - |
空间使用 | 高 | 低 | N/A | 低 |
内存使用 | 高 | 低 | 中等 | 低 |
批量插入速度 | 低 | 高 | 高 | 高 |
支持外键 | 支持 | - | - | - |
- 如果要提供提交、回滚和崩溃恢复能力的事务安全(ACID 兼容)能力,并要求实现并发控制,InnoDB 是个很好的选择;
- 如果数据表主要用来插入和查询记录,则 MyISAM 引擎能提供较高的处理效率;
- 如果只是临时存放数据,数据量不大,并且不需要较高的数据安全性,可以选择将数据保存在内存中的 Memory 引擎,MySQL 中使用该引擎作为临时表,存放查询的中间结果;
- 如果只有 INSERT 和 SELECT 操作,可以选择 Archive 引擎,Archive 存储引擎支持高并发的插入操作,但是本身并不是事务安全的;
- Archive 存储引擎非常适合存储归档数据,如记录日志信息可以使用 Archive 引擎。
使用哪一种引擎要根据需要灵活选择,一个数据库中多个表可以使用不同引擎,以满足各种性能和实际需求。使用合适的存储引擎,将会提高整个数据库的性能。