首页 > 编程笔记 > MySQL笔记 阅读:9

MySQL存储引擎详解(新手必看)

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

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: NO
Support 列的值表示某种引擎是否能使用: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
内存使用 中等
批量插入速度
支持外键 支持 - - -


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

相关文章