MySQL的存储引擎选择

前言

和大多数数据库不同的是MySQL有一个存储引擎的概念,针对不同的存储场景和需求可以选择最优的存储引擎。用户可以根据应用的需要选择如何存储和索引数据、是否使用事务等。

MySQL5.7支持的存储引擎包括MyISAM、InnoDB、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED等,其中InnoDB和BDB提供事务安全表,其他存储引擎都是非事务安全表。

默认引擎

在MySQL5.5之前系统默认使用的是MyISAM引擎,在MySQL5.5之后改为了InnoDB, 查看当前默认存储引擎:

1
2
3
4
5
6
7
8
9
10
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.00 sec)

可以通过下面两种方法查询当前数据库支持的存储引擎:

方法一:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
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: PERFORMANCE_SCHEMA
Support: YES
Comment: Performance Schema
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: MyISAM
Support: YES
Comment: MyISAM 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: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
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)

方法二:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> show variables like 'have%';
+------------------------+----------+
| Variable_name | Value |
+------------------------+----------+
| have_compress | YES |
| have_crypt | YES |
| have_dynamic_loading | YES |
| have_geometry | YES |
| have_openssl | DISABLED |
| have_profiling | YES |
| have_query_cache | YES |
| have_rtree_keys | YES |
| have_ssl | DISABLED |
| have_statement_timeout | YES |
| have_symlink | YES |
+------------------------+----------+
11 rows in set (0.00 sec)

上面disable表示该数据库支持该引擎,但是数据库启动的时候该引擎被禁用了。

设置引擎

我们可以更改默认的数据库引擎,在MySQL配置文件(Linux下为/etc/my.cnf,如果自己定制安装的话,以实际配置文件位置为准),在mysqld后面增加default-storage-engine=INNODB即可。(调整完毕后,需要重启mysql服务)。

Mac上MySQL默认没有配置文件,需要自己添加,可/usr/local/mysql/support-file/ 文件目录下的my-default.cnf复制一份到桌面上,可以把文件中的对应内容替换另存为my.cnf然后放到/etc目录下。

也可以在创建表的时候增加ENGINE关键字设置该表的存储引擎:

1
create table ai( i bigint(20) not null auto_increment, primary key(i)) engine=MyISAM default charset=gbk;

或者使用alter table更改表的存储引擎:

1
alter table ai engine = innodb;

引擎特性

特点MyISAMInnoDBMEMORYMERGENDB
存储限制64TB没有
事务安全支持
锁机制表锁行锁表锁表锁行锁
B树索引支持支持支持支持支持
哈希索引支持
全文索引支持
集群索引支持
数据缓存支持支持支持
索引缓存支持支持支持支持支持
数据可压缩支持
空间使用N/A
内存使用中等
批量插入的速度
支持外键支持

MyISAM

MyISAM不支持事务也不支持外键,其优势是访问速度快,对事务完整性没有要求或者以select、insert为主的应用基本上都可以使用这个引擎来创建表。

每个MyISAM在磁盘上存储成3个文件,其文件名和表名相同,扩展名分别是:

  • .frm(存储表定义)
  • .MYD(MYData, 存储数据)
  • .MYI(MYIndex,存储索引)

MyISAM的表还支持3种不同的存储格式,分别是:

  • 静态(固定长度)表
  • 动态表
  • 压缩表

其中,静态表示默认的存储格式,静态表中的字符安都是非变长字段,每个记录都是固定长度,这种存储方式的优点是存储非常迅速,容易缓存,出现故障容易恢复;缺点是占用的空间通常比动态表多。

InnoDB

InnoDB存储引擎提供了具有提交、回滚和奔溃恢复能力的事务安全。但是对比MyISAM的存储引擎,InnoDB处理效率差一些,会占用更多的磁盘空间已保留数据和索引。

InnoDB存储引擎有如下一些特点:

  1. 自动增长列,也可以手工插入,如果插入的值是空或者0则实际插入的是自动增长后的值。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> create table autoincre_demo (i smallint not null auto_increment, name varchar(10), primary key(i)) engine=innodb;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into autoincre_demo values(1, '1'), (0, '2'), (null, '3');
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from autoincre_demo;
+---+------+
| i | name |
+---+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+---+------+
3 rows in set (0.00 sec)
  1. 外键约束,MySQL中只有InnoDB支持外键索引。
  2. 存储方式,InnoDB存储表和索引有使用共享表空间存储和使用多表空间存储方式。

MEMORY

MEMORY 存储引擎使用存在于内存中的内容来创建表,每个memory表只对应一个磁盘文件.frm,memory类型表访问速度非常快,因为它的数据是存放在内存中的,并且默认使用hash索引,但是一旦服务关闭,表中的数据就会丢失掉。

总结

通过上面简单分析,想必大家也对何时使用哪种存储引擎有了自己的判断,如果我们的应用是以读写操作为主,只有很少的更新和删除操作,并且对事务的完整性、并发性要求不高,选择MyISAM最合适。如果我们需要对事务完整性有比较高的要求,例如计费系统、财务统计等,使用InnoDB最合适。如果要提供一些类似中间缓存的数据已达到快速访问的目的,使用MEMORY比较合适。