0%

MySQL存储引擎

MySQL存储引擎

MySQL有很多的存储引擎,每个存储引擎都有自己的特点,且存储引擎是基于表的,所以可以根据不同的应用来建立不同存储引擎表,虽然有很多存储引擎,但是使用最多的还是Innodb、MyISAM和Memory这三种存储引擎

在mysql的my.cnf文件中使用default-storage-engine=INNODB来指定默认存储引擎

所以先看一下这三种搜索引擎的区别

对比 MyISAM Innodb Memory
外键 不支持 支持 不支持
事务 不支持 支持 不支持
行表锁 表锁,不适合高并发 行锁,适合高并发 表锁
缓存 只缓存索引,不缓存数据 既缓存索引,又缓存数据,对内存要求较高
表空间
关注点 性能 事务
内存使用
插入速度

可以使用SHOW ENGINES来查看数据库支持的存储引擎

使用alter table <表名> engine = <存储引擎> 来修改表的存储引擎

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
show engines \G
*************************** 1. row ***************************
Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 2. row ***************************
Engine: CSV
Support: YES
Comment: CSV storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 3. row ***************************
Engine: MRG_MYISAM
Support: YES
Comment: Collection of identical MyISAM 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: InnoDB
Support: DEFAULT
Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
XA: YES
Savepoints: YES
*************************** 6. row ***************************
Engine: PERFORMANCE_SCHEMA
Support: YES
Comment: Performance Schema
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: MyISAM
Support: YES
Comment: MyISAM storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 9. row ***************************
Engine: FEDERATED
Support: NO
Comment: Federated MySQL storage engine
Transactions: NULL
XA: NULL
Savepoints: NULL

Innodb存储引擎

InnoDB给MySQL的表提供了事务处理回滚崩溃修复能力多版本并发控制的事务安全。

InnoDB存储引擎总支持AUTO_INCREMENT。自动增长列的值不能为空,并且值必须唯一。MySQL中规定自增列必须为主键。在插入值的时候,如果自动增长列不输入值,则插入的值为自动增长后的值;如果输入的值为0或空(NULL),则插入的值也是自动增长后的值;如果插入某个确定的值,且该值在前面没有出现过,就可以直接插入

InnoDB还支持外键(FOREIGN KEY)。外键所在的表叫做子表,外键所依赖(REFERENCES)的表叫做父表。父表中被子表外键关联的字段必须为主键。当删除、更新父表中的某条信息时,子表也必须有相应的改变,这是数据库的参照完整性规则

InnoDB中,创建的表的表结构存储在.frm文件中,数据和索引存储在innodb_data_home_dir和innodb_data_file_path定义的表空间中

默认情况下,InnoDB的后台线程有7个,包括4个IO 线程, 1个master 线程, 1个lock monitor 线程, 一个error monitor 线程

内存

InnoDB的内存主要有以下几个部分组成:缓冲池 (buffer pool)、重做日志缓冲池(redo log buffer)以及额外的内存池(additional memory pool)

其中缓冲池占最大块内存,用来缓存各自数据,数据文件按页(每页16K)读取到缓冲池,按最近最少使用算法(LRU)保留缓存数据。

缓冲池缓冲的数据类型有:数据页、索引页、插入缓冲、自适应哈希索引、锁信息、数据字典信息等,其中数据页和索引页占了绝大部分内存。

MyISAM存储引擎

MyISAM的表存储成3个文件。文件的名字与表名相同。拓展名为frm、MYD、MYI。其中,frm文件存储表的结构;MYD文件存储数据,是MYData的缩写;MYI文件存储索引,是MYIndex的缩写

基于MyISAM存储引擎的表支持3种不同的存储格式。包括静态型、动态型和压缩型。其中,静态型是MyISAM的默认存储格式,它的字段是固定长度的;动态型包含变长字段,记录的长度不是固定的;压缩型需要用到myisampack工具,占用的磁盘空间较小

MEMORY存储引擎

MEMORY是将数据存储在内存中,每个基于MEMORY存储引擎的表实际上对应了一个磁盘文件,文件名称与表名相同,类型为frm,文件中只存储表结构,数据文件都存储在内存中,其默认使用的是hash索引,但是如果数据库重启或者崩溃,数据将消失,一般内存数据库也不会使用MySQL,而是使用redis居多

NDB存储引擎

NDB存储引擎是一种集群存储引擎,其结构是share nothing的集群结构,数据全部放在内存中(可以将非索引数据放在磁盘上),使用主键查找的速度极快,并且通过添加NDB数据存储节点可以线性的提高数据库性能

Archive存储引擎

Archive存储引擎只支持insert和select语句,使用zlib算法将数据行进行压缩后存储,存储比一般可达1:10,适合存储归档数据,如日志信息

Federated存储引擎

Federated存储引擎不存放数据,只是指向一台远程MySQL数据库服务器上的表