MySQL 存储引擎

MySQL存储引擎概述

插入式存储引擎是MySQL数据库最重要的特性之一,用户可根据应用的需要选择如何存储和索引数据、是否使用事物等。
MySQL支持的引擎包括:MyISAM、InnoDB、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED等。
InnoDB和BDB提供事物安全表,其它引擎都是非事物安全的。

默认存储引擎

默认情况下创建新表不指定表的存储引擎,则新表是默认存储引擎,设置默认存储引擎在配置文件中设置default-storage-engine

1
2
[mysqld]
default-storage-engine=INNODB

查看当前默认存储引擎

1
2
3
4
5
6
7
8
MariaDB [test]> show variables like '%storage_engine%';
+------------------------+--------+
| Variable_name | Value |
+------------------------+--------+
| default_storage_engine | InnoDB |
| storage_engine | InnoDB |
+------------------------+--------+
2 rows in set (0.00 sec)

查看支持的存储引擎

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
ariaDB [test]> show engines;
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| CSV | YES | CSV storage engine | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| InnoDB | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| FEDERATED | YES | FederatedX pluggable storage engine | YES | NO | YES |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| Aria | YES | Crash-safe tables with MyISAM heritage | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
10 rows in set (0.00 sec)

指定存储引擎

创建表使用engine关键字设置新表存储引擎
使用alter table可以修改表的存储结构

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
MariaDB [test]> create table test1(a int(10), b varchar(10));
Query OK, 0 rows affected (0.05 sec)

MariaDB [test]> show create table test1\G
*************************** 1. row ***************************
Table: test1
Create Table: CREATE TABLE `test1` (
`a` int(10) DEFAULT NULL,
`b` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

MariaDB [test]> drop table test1;
Query OK, 0 rows affected (0.02 sec)

MariaDB [test]> create table test1(a int(10), b varchar(10))engine=MyISAM;
Query OK, 0 rows affected (0.02 sec)

MariaDB [test]> show create table test1\G
*************************** 1. row ***************************
Table: test1
Create Table: CREATE TABLE `test1` (
`a` int(10) DEFAULT NULL,
`b` varchar(10) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

MariaDB [test]> alter table test1 engine=innodb;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0

MariaDB [test]> show create table test1\G
*************************** 1. row ***************************
Table: test1
Create Table: CREATE TABLE `test1` (
`a` int(10) DEFAULT NULL,
`b` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

各种存储引擎特性

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

MyISAM

不支持事物、不支持外键,访问速度快,对事物完整性没有要求或者以SELECT、INSERT为主的应用基本上都可以使用MyISAM引擎。
每个MyISAM在磁盘上存储为3个文件,文件名与表名相同,扩展名不同:

  • .frm(存储表定义)
  • .MYD(MyData,存储数据)
  • .MYI(MYIndex,存储索引)
    1
    2
    3
    4
    5
    6
    [root@VM_187_252_centos test]# cat /etc/my.cnf | grep datadir
    datadir=/var/lib/mysql
    [root@VM_187_252_centos test]# ls /var/lib/mysql/test/ | grep test_myisam
    test_myisam.frm
    test_myisam.MYD
    test_myisam.MYI

数据文件和索引文件可以放置在不同目录,平均分布IO,获得更快速度。创建表时可通过DATA DIRECTORY和INDEX DIRECTORY指定数据文件路径和索引文件路径,文件路径使用绝对路径,且有访问权限。
MyISAM类型的表可能会损坏,损坏后的表可能不能访问,会提示修复或返回错误数据。
使用CHECK TABLE语句可以检查MyISAM表的健康,使用REPAIR TABLE可以修复损坏的MyISAM表。
MyISAM表支持三种不同存储格式:

  • 静态(固定长度)表 默认存储格式。静态表字段非变长字段,每个记录固定长度,存储迅速,容易缓存,出现故障容易恢复,占用空间比动态表多。静态表数据存储时按列宽补总空格,但应用访问时并不返回空格,会自动去掉。
  • 动态表 包含变长字段,记录不是固定长度。占用空间少,频繁更新删除字段会产生碎片,需要定期执行OPTIMIZE TABLE或myisamchk -r命令改善性能,出现故障恢复比较困难。
  • 压缩表 压缩表用myisampack工具创建,占用非常小的磁盘空间。每个记录都被单独压缩,只有非常小的访问开支。
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    MariaDB [test]> create table test_myisam(a char(64), b varchar(64)) engine=myisam;
    Query OK, 0 rows affected (0.01 sec)

    MariaDB [test]> insert into test_myisam values(" abc", " abc"), ("abc ", "abc ");
    Query OK, 2 rows affected (0.00 sec)
    Records: 2 Duplicates: 0 Warnings: 0

    MariaDB [test]> select concat(a, "+"), concat(b, "+") from test_myisam;
    +----------------+----------------+
    | concat(a, "+") | concat(b, "+") |
    +----------------+----------------+
    | abc+ | abc+ |
    | abc+ | abc + |
    +----------------+----------------+
    2 rows in set (0.00 sec)

InnoDB

InnoDB存储引擎提供具有提交、回滚、崩溃恢复能力的事物安全。对比MyISAM,InnoDB写的处理效率差一点,并且会占用更多的磁盘控件保存数据和索引

自动增长列

  1. 自动增长列不论插入0或NULL实际插入的都是自动增长的值。
  2. 可以使用ALTER TABLE ** AUTO_INCREMENT=n语句强制设置自动增长列的初始值(该强制值保存在内存中,若使用前数据库重启,该强制值会丢失,需重新设置)。
  3. 可使用LAST_INSERT_ID()查看当前线程最后插入记录使用的值,一次插入多条,返回第一条记录使用的自动增长列。
  4. InnoDB自动增长列必须是索引。如果是组合索引,必须是组合索引第一列,对MyISAM,自动增长列可以是组合索引的其它列。

外键约束

MySQL只有InnoDB支持外键约束,在创建外键时,要求父表必须有对应的索引,子表在创建外键时也会自动创建对应的索引。
在创建索引的时候,可以指定在删除、更新父表时,对子表进行的相应操作,包括 RESTRICT、 CASCADE、SET NULL 和 NO ACTION。
RESTRICT 和 NO ACTION 相同,是指限制在子表有关联记录的情况下父表不能更新;
CASCADE 表示父表在更新或者删除时,更新或者删除子表对应记录;
SET NULL 则表示父表在更新或者删除的时候,子表的对应字段被 SET NULL。
选择CASCADE和SET NULL两种方式的时候要谨慎,可能会因为错误的操作导致数据的丢失。
当某个表被其他表创建了外键参照,那么该表的对应索引或者主键禁止被删除。
在导入多个表的数据时,如果需要忽略表之前的导入顺序,可以暂时关闭外键的检查; 同样,在执行 LOAD DATA 和 ALTER TABLE 操作的时候,可以通过暂时关闭外键约束来加快处理的速度,关闭的命令是“SET FOREIGN_KEY_CHECKS = 0;”,执行完成之后,通过执行“SET FOREIGN_KEY_CHECKS = 1;”语句改回原状态。
对于 InnoDB 类型的表,外键的信息通过使用 show create table 或者 show table status 命令都可以显示。

存储方式

InnoDB 存储表和索引有以下两种方式。

  • 使用共享表空间存储,这种方式创建的表的表结构保存在.frm 文件中,数据和索引保存在 innodb_data_home_dir 和 innodb_data_file_path 定义的表空间中,可以是多个文件。
  • 使用多表空间存储,这种方式创建的表的表结构仍然保存在.frm 文件中,但是每个表的数据和索引单独保存在.ibd 中。如果是个分区表,则每个分区对应单独的.ibd文件,文件名是“表名+分区名”,可以在创建分区的时候指定每个分区的数据文件的位置,以此来将表的 IO 均匀分布在多个磁盘上。

多表空间的数据文件没有大小限制,不需要设置初始大小,也不需要设置文件的最大限制、扩展大小等参数。

MEMORY

MEMORY 存储引擎使用存在内存中的内容来创建表。每个 MEMORY 表只实际对应一个磁盘文件,格式是.frm。MEMORY 类型的表访问非常得快,因为它的数据是放在内存中的,并且默认使用 HASH 索引,但是一旦服务关闭,表中的数据就会丢失掉。
给 MEMORY 表创建索引的时候,可以指定使用 HASH 索引还是 BTREE 索引。

MERGE

MERGE 存储引擎是一组 MyISAM 表的组合,这些 MyISAM 表必须结构完全相同,MERGE 表本身并没有数据,对 MERGE 类型的表可以进行查询、更新、删除的操作,这些操作实际上是对内部的实际的 MyISAM 表进行的。对于 MERGE 类型表的插入操作,是通过 INSERT_METHOD 子句定义插入的表,可以有 3 个不同的值,使用 FIRST 或 LAST 值使得插入操作被相应地作用在第一或最后一个表上,不定义这个子句或者定义为 NO,表示不能对这个 MERGE 表执行插入操作。
可以对 MERGE 表进行 DROP 操作,这个操作只是删除 MERGE 的定义,对内部的表没有任何的影响。
MERGE 表在磁盘上保留两个文件,文件名以表的名字开始,一个.frm 文件存储表定义,另一个.MRG 文件包含组合表的信息,包括 MERGE 表由哪些表组成、插入新的数据时的依据。
可以通过修改.MRG 文件来修改 MERGE 表,但是修改后要通过 FLUSH TABLES 刷新。

合适存储引擎的选择

  • MyISAM:如果应用是以读操作和插入操作为主,只有很少的更新和删除操作,并且对事务的完整性、并发性要求不是很高,那么选择这个存储引擎是非常适合的。MyISAM 是在 Web、数据仓储和其他应用环境下最常使用的存储引擎
    之一。
  • InnoDB:用于事务处理应用程序,支持外键。如果应用对事务的完整性有比较高的要求,在并发条件下要求数据的一致性,数据操作除了插入和查询以外,还包括很多的更新、删除操作,那么 InnoDB 存储引擎应该是比较合适的选择。InnoDB 存储引擎除了有效地降低由于删除和更新导致的锁定,还可以确保事务的完整提交(Commit)和回滚(Rollback),对于类似计费系统或者财务系统等对数据准确性要求比较高的系统,InnoDB 都是合适的选择。
  • MEMORY:将所有数据保存在 RAM 中,在需要快速定位记录和其他类似数据的环境下,可提供极快的访问。MEMORY 的缺陷是对表的大小有限制,太大的表无法 CACHE 在内存中,其次是要确保表的数据可以恢复,数据库异常终止后表中的数据是可以恢复的。MEMORY 表通常用于更新不太频繁的小表,用以快速得到访问结果。
  • MERGE:用于将一系列等同的 MyISAM 表以逻辑方式组合在一起,并作为一个对象引用它们。MERGE 表的优点在于可以突破对单个 MyISAM 表大小的限制,并且通过将不同的表分布在多个磁盘上,可以有效地改善MERGE表的访问效率。这对于诸如数据仓储等VLDB环境十分适合。