MySQL 如何选择合适的存储引擎 (七)

最新推荐文章于 2021-02-27 06:50:38 发布

码农新锐

最新推荐文章于 2021-02-27 06:50:38 发布

阅读量562

点赞数

CC 4.0 BY-SA版权

分类专栏： MySql 文章标签：存储引擎 InnoDB MyISAM merge mysql

本文链接：https://blog.youkuaiyun.com/qq_31984879/article/details/88927960

MySql 专栏收录该内容

18 篇文章

订阅专栏

本文介绍了MySQL中的多种存储引擎，如MyISAM、InnoDB、MEMORY和MERGE等，详细阐述了每种引擎的特点及适用场景，帮助开发者根据应用需求合理选择。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

在选择存储引擎时，应根据应用特点选择合适的存储引擎，对于复杂的应用系统可以根据实际情况选择多种存储引擎进行组合。

MyISAM

默认的 MySQL 插件式存储引擎。如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常适合的。MyISAM 是在 Web、数据仓储和其他应用环境下常使用的存储引擎之一。

InnoDB

用于事务处理应用程序，支持外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询以外，还包括很多的更新、删除操作，那么 InnoDB 存储引擎应该是比较合适的选择。InnoDB 存储引擎除了有效地降低由于删除和更新导致的锁定，还可以确保事务的完整交（Commit）和回滚（Rollback），对于类似计费系统或者财务系统等对数据准确性要求比较高的系统，InnoDB 都是合适的选择。

MEMORY

将所有数据保存在 RAM 中，在需要快速定位记录和其他类似数据的环境下，可供极快的访问。MEMORY 的缺陷是对表的大小有限制，太大的表无法 CACHE 在内存中，其次是要确保表的数据可以恢复，数据库异常终止后表中的数据是可以恢复的。 MEMORY 表通常用于更新不太频繁的小表，用以快速得到访问结果。

MERGE

用于将一系列等同的 MyISAM 表以逻辑方式组合在一起，并作为一个对象引用它们。MERGE 表的优点在于可以突破对单个 MyISAM 表大小的限制，并且通过将不同的表分布在多个磁盘上，可以有效地改善MERGE表的访问效率。这对于诸如数据仓储等VLDB 环境十分适合。

例子：

创建引擎为MyISAM：

 CREATE TABLE ai(i bigint(20) NOT NULL AUTO_INCREMENT,PRIMARY KEY (i))ENGINE=MyISAM DEFAULT CHARSET=gbk;

创建引擎为InnoDB:

CREATE TABLE country ( 
  country_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, 
  country VARCHAR(50) NOT NULL, 
  last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
  PRIMARY KEY  (country_id) 
)ENGINE=InnoDB DEFAULT CHARSET=gbk;

也可以使用 ALTER TABLE 语句，将一个已经存在的表修改成其他的存储引擎。下面的例子介绍了如何将表 ai 从MyISAM 存储引擎修改成 InnoDB存储引擎：

alter table ai engine = innodb;

查看表结构存储引擎已经变成InnoDB

mysql> show create table ai;
+-------+-----------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                          |
+-------+-----------------------------------------------------------------------------------------------------------------------+
| ai    | CREATE TABLE `ai` (
  `i` bigint(20) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`i`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
+-------+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

存储引擎的特点

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

MyISAM

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

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

.frm（存储表定义）； .MYD（MYData，存储数据）； .MYI （MYIndex，存储索引）。

数据文件和索引文件可以放置在不同的目录，平均分布 IO，获得更快的速度。

要指定索引文件和数据文件的路径，需要在创建表的时候通过DATA DIRECTORY 和INDEX DIRECTORY 语句指定，也就是说不同 MyISAM 表的索引文件和数据文件可以放置到不同的路径下。文件路径需要是绝对路径，并且具有访问权限。

MyISAM 的表又支持 3 种不同的存储格式，分别是： 静态（固定长度）表； 动态表； 压缩表。

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

在使用时需要注意的问题，如果需要保存的内容后面本来就带有空格，那么在返回结果的时候也会被去掉，丢失了尾部的空格。

mysql> create table Myisam_char (name char(10)) engine=myisam;
Query OK, 0 rows affected (1.86 sec)

mysql> insert into Myisam_char values('abcde'),('abcde  '),('  abcde'),('  abcde  ');
Query OK, 4 rows affected (0.13 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql>  select name,length(name) from Myisam_char;
+---------+--------------+
| name    | length(name) |
+---------+--------------+
| abcde   |            5 |
| abcde   |            5 |
|   abcde |            7 |
|   abcde |            7 |
+---------+--------------+
4 rows in set (0.00 sec)

从上面的例子可以看出，插入记录后面的空格都被去掉了，前面的空格保留。

动态表中包含变长字段，记录不是固定长度的，这样存储的优点是占用的空间相对较少，但是频繁地更新删除记录会产生碎片，需要定期执行 OPTIMIZE TABLE 语句或 myisamchk -r 命令来改善性能，并且出现故障的时候恢复相对比较困难。

压缩表由 myisampack 工具创建，占据非常小的磁盘空间。因为每个记录是被单独压缩的，所以只有非常小的访问开支。

InnoDB

InnoDB 存储引擎供了具有交、回滚和崩溃恢复能力的事务安全。但是对比MyISAM 的存储引擎，InnoDB 写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。下面将重点介绍 InnoDB 存储引擎的表使用过程中不同于其他存储引擎的特点。

自动增长列

InnoDB 表的自动增长列可以手工插入，但是插入的值如果是空或者 0，则实际插入的将是自动增长后的值。

对于 InnoDB 表，自动增长列必须是索引。如果是组合索引，也必须是组合索引的第一列，但是对于MyISAM 表，自动增长列可以是组合索引的其他列，这样插入记录后，自动增长列是按照组合索引的前面几列进行排序后递增的。

例如，创建一个新的 MyISAM 类型的表 autoincre_demo，自动增长列 d1 作为组合索引的第二列，对该表插入一些记录后，可以发现自动增长列是按照组合索引的第一列 d2进行排序后递增的：

mysql>  create table autoincre_demo  (d1 smallint not null auto_increment, d2 smallint not null,  name varchar(10),
    -> index(d2,d1) )engine=myisam;
Query OK, 0 rows affected (0.41 sec)

mysql>  insert into autoincre_demo(d2,name) values(2,'2'),(3,'3'),(4,'4'),(2,'2'),(3,'3') ,
    -> (4,'4');
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from autoincre_demo;
+----+----+------+
| d1 | d2 | name |
+----+----+------+
|  1 |  2 | 2    |
|  1 |  3 | 3    |
|  1 |  4 | 4    |
|  2 |  2 | 2    |
|  2 |  3 | 3    |
|  2 |  4 | 4    |
+----+----+------+
6 rows in set (0.00 sec)

外键约束

MySQL 支持外键的存储引擎只有 InnoDB，在创建外键的时候，要求父表必须有对应的索引，子表在创建外键的时候也会自动创建对应的索引。

下面是样例数据库中的两个表，country 表是父表，country_id 为主键索引，city表是子表，country_id 字段对country 表的 country_id有外键。

mysql> CREATE TABLE country (
    ->   country_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    ->   country VARCHAR(50) NOT NULL,
    ->   last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    ->   PRIMARY KEY  (country_id)
    -> )ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.29 sec)

mysql> CREATE TABLE city (   city_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    ->   city VARCHAR(50) NOT NULL,
    ->   country_id SMALLINT UNSIGNED NOT NULL,
    ->   last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    ->   PRIMARY KEY  (city_id),
    ->   KEY idx_fk_country_id (country_id),
    ->   CONSTRAINT `fk_city_country` FOREIGN KEY (country_id) REFERENCES country (country_id) ON
    -> DELETE RESTRICT ON UPDATE CASCADE
    -> )ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.39 sec)

mysql> desc city;
+-------------+----------------------+------+-----+-------------------+-----------------------------+
| Field       | Type                 | Null | Key | Default           | Extra                       |
+-------------+----------------------+------+-----+-------------------+-----------------------------+
| city_id     | smallint(5) unsigned | NO   | PRI | NULL              | auto_increment              |
| city        | varchar(50)          | NO   |     | NULL              |                             |
| country_id  | smallint(5) unsigned | NO   | MUL | NULL              |                             |
| last_update | timestamp            | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------------+----------------------+------+-----+-------------------+-----------------------------+
4 rows in set (0.00 sec)

查询country 和city表记录


mysql> select * from country;
+------------+---------+---------------------+
| country_id | country | last_update         |
+------------+---------+---------------------+
|          1 | China   | 2019-03-31 14:42:00 |
+------------+---------+---------------------+
1 row in set (0.00 sec)

mysql> select * from city;
+---------+---------+------------+---------------------+
| city_id | city    | country_id | last_update         |
+---------+---------+------------+---------------------+
|       1 | Beijing |          1 | 2019-03-31 14:40:40 |
+---------+---------+------------+---------------------+
1 row in set (0.00 sec)

删除country表记录

mysql>  delete from country where country_id=1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`city`, CONSTRAINT `fk_city_country` FOREIGN KEY (`country_id`) REFERENCES `country` (`country_id`) ON UPDATE CASCADE)

不可以直接删除country表记录，需要删除子表关联的外键的数据，才可以删除主表的数据。

更新country表的主键会同时更新子表中的外键。

update country set country_id = 10000 where country_id = 1;

查询父表和子表的记录

mysql> select * from country;
+------------+---------+---------------------+
| country_id | country | last_update         |
+------------+---------+---------------------+
|      10000 | China   | 2019-03-31 14:44:20 |
+------------+---------+---------------------+
1 row in set (0.00 sec)

mysql> select * from city;
+---------+---------+------------+---------------------+
| city_id | city    | country_id | last_update         |
+---------+---------+------------+---------------------+
|       1 | Beijing |      10000 | 2019-03-31 14:40:40 |
+---------+---------+------------+---------------------+
1 row in set (0.00 sec)

查看表中的外键语句：

对于 InnoDB 类型的表，外键的信息通过使用 show create table 或者 show table status 命令都可以显示。

例如：


mysql> show create table city;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| city  | CREATE TABLE `city` (
  `city_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `city` varchar(50) NOT NULL,
  `country_id` smallint(5) unsigned NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`city_id`),
  KEY `idx_fk_country_id` (`country_id`),
  CONSTRAINT `fk_city_country` FOREIGN KEY (`country_id`) REFERENCES `country` (`country_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

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

使用共享表空间存储，这种方式创建的表的表结构保存在.frm 文件中，数据和索引保存在 innodb_data_home_dir 和 innodb_data_file_path 定义的表空间中，可以是多个文件。

使用多表空间存储，这种方式创建的表的表结构仍然保存在.frm 文件中，但是每个表的数据和索引单独保存在.ibd 中。如果是个分区表，则每个分区对应单独的.ibd 文件，文件名是“表名+分区名”，可以在创建分区的时候指定每个分区的数据文件的位置，以此来将表的 IO 均匀分布在多个磁盘上。要使用多表空间的存储方式，需要设置参数 innodb_file_per_table，并重新启动服务后才可以生效，对于新建的表按照多表空间的方式创建，已有的表仍然使用共享表空间存储。如果将已有的多表空间方式修改回共享表空间的方式，则新建表会在共享表空间中创建，但已有的多表空间的表仍然保存原来的访问方式。所以多表空间的参数生效后，只对新建的表生效。多表空间的数据文件没有大小限制，不需要设置初始大小，也不需要设置文件的大限制、扩展大小等参数。对于使用多表空间特性的表，可以比较方便地进行单表备份和恢复操作，但是直接复制.ibd 文件是不行的，因为没有共享表空间的数据字典信息，直接复制的.ibd 文件和.frm 文件恢复时是不能被正确识别的，但可以通过以下命令： ALTER TABLE tbl_name DISCARD TABLESPACE; ALTER TABLE tbl_name IMPORT TABLESPACE; 将备份恢复到数据库中，但是这样的单表备份，只能恢复到表原来在的数据库中，而不能恢复到其他的数据库中。如果要将单表恢复到目标数据库，则需要通过 mysqldump 和 mysqlimport 来实现。

MEMORY

MEMORY 存储引擎使用存在内存中的内容来创建表。每个 MEMORY 表只实际对应一个磁盘文件，格式是.frm。MEMORY 类型的表访问非常得快，因为它的数据是放在内存中的，并且默认使用 HASH 索引，但是一旦服务关闭，表中的数据就会丢失掉。下面例子创建了一个 MEMORY 的表，并从 city表获得记录

mysql> CREATE TABLE tab_memory ENGINE=MEMORY 
    ->     SELECT city_id,city,country_id  
    ->     FROM city GROUP BY city_id;

给 MEMORY 表创建索引的时候，可以指定使用 HASH 索引还是 BTREE 索引：

在启动 MySQL 服务的时候使用--init-file 选项，把 INSERT INTO ... SELECT 或 LOAD DATA INFILE 这样的语句放入这个文件中，就可以在服务启动时从持久稳固的数据源装载表。服务器需要足够内存来维持所有在同一时间使用的 MEMORY 表，当不再需要 MEMORY 表的内容之时，要释放被MEMORY表使用的内存，应该执行DELETE FROM或TRUNCATE TABLE，或者整个地删除表（使用 DROP TABLE 操作）。每个 MEMORY 表中可以放置的数据量的大小，受到 max_heap_table_size 系统变量的约束，这个系统变量的初始值是 16MB，可以按照需要加大。此外，在定义 MEMORY 表的时候，可以通过 MAX_ROWS子句指定表的大行数。 MEMORY 类型的存储引擎主要用在那些内容变化不频繁的代码表，或者作为统计操作的中间结果表，便于高效地对中间结果进行分析并得到终的统计结果。对 MEMORY 存储引擎的表进行更新操作要谨慎，因为数据并没有实际写入到磁盘中，所以一定要对下次重新启动服务后如何获得这些修改后的数据有所考虑。

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 刷新。下面是一个创建和使用 MERGE 表的例子。

mysql> create table payment_2006( country_id smallint,  payment_date datetime,  amount DECIMAL(15,2), KEY idx_fk_country_id (country_id) )engine=myisam;
Query OK, 0 rows affected (1.83 sec)

mysql>  create table payment_2007(  country_id smallint, payment_date datetime, amount DECIMAL(15,2),  KEY idx_fk_country_id (country_id)  )engine=myisam;
Query OK, 0 rows affected (0.46 sec)

mysql>  CREATE TABLE payment_all(  country_id smallint, payment_date datetime,  amount DECIMAL(15,2), INDEX(country_id) )engine=merge union=(payment_2006,payment_2007) INSERT_METHOD=LAST;
Query OK, 0 rows affected (0.80 sec)

测试向payment_2006和payment_2007插入数据


mysql> insert into payment_2006 values(1,'2006-05-01',100000),(2,'2006-08-15',150000);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into payment_2007 values(1,'2007-02-20',35000),(2,'2007-07-15',220000);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from payment_2006;
+------------+---------------------+-----------+
| country_id | payment_date        | amount    |
+------------+---------------------+-----------+
|          1 | 2006-05-01 00:00:00 | 100000.00 |
|          2 | 2006-08-15 00:00:00 | 150000.00 |
+------------+---------------------+-----------+
2 rows in set (0.00 sec)

mysql> select * from payment_2007;
+------------+---------------------+-----------+
| country_id | payment_date        | amount    |
+------------+---------------------+-----------+
|          1 | 2007-02-20 00:00:00 |  35000.00 |
|          2 | 2007-07-15 00:00:00 | 220000.00 |
+------------+---------------------+-----------+
2 rows in set (0.00 sec)

可以发现，payment_all 表中的数据是 payment_2006 和 payment_2007 表的记录合并后的结果集。

mysql> select * from payment_all;
+------------+---------------------+-----------+
| country_id | payment_date        | amount    |
+------------+---------------------+-----------+
|          1 | 2006-05-01 00:00:00 | 100000.00 |
|          2 | 2006-08-15 00:00:00 | 150000.00 |
|          1 | 2007-02-20 00:00:00 |  35000.00 |
|          2 | 2007-07-15 00:00:00 | 220000.00 |
+------------+---------------------+-----------+
4 rows in set (0.00 sec)

下面向 MERGE 表插入一条记录，由于 MERGE 表的定义是 INSERT_METHOD=LAST，就会向后一个表中插入记录，所以虽然这里插入的记录是 2006 年的，但仍然会写到 payment_2007 表中。

mysql>  insert into payment_all values(3,'2006-03-31',112200);
Query OK, 1 row affected (0.00 sec)

mysql> select * from payment_all;
+------------+---------------------+-----------+
| country_id | payment_date        | amount    |
+------------+---------------------+-----------+
|          1 | 2006-05-01 00:00:00 | 100000.00 |
|          2 | 2006-08-15 00:00:00 | 150000.00 |
|          1 | 2007-02-20 00:00:00 |  35000.00 |
|          2 | 2007-07-15 00:00:00 | 220000.00 |
|          3 | 2006-03-31 00:00:00 | 112200.00 |
+------------+---------------------+-----------+

查询payment_2007表果然多了一条记录。

mysql> select * from payment_2007;
+------------+---------------------+-----------+
| country_id | payment_date        | amount    |
+------------+---------------------+-----------+
|          1 | 2007-02-20 00:00:00 |  35000.00 |
|          2 | 2007-07-15 00:00:00 | 220000.00 |
|          3 | 2006-03-31 00:00:00 | 112200.00 |
+------------+---------------------+-----------+
3 rows in set (0.00 sec)

这也是 MERGE 表和分区表的区别，MERGE 表并不能智能地将记录写到对应的表中，而分区表是可以的（分区功能在 5.1 版中正式推出）。通常我们使用 MERGE 表来透明地对多个表进行查询和更新操作，而对这种按照时间记录的操作日志表则可以透明地进行插入操作。

本章重点介绍了 MySQL 供的几种主要的存储引擎及其使用、特性，以及如何根据应用的需要选择合适的存储引擎。这些供的存储引擎有各自的优势和适用的场合，正确地选择存储引擎对改善应用的效率可以起到事半功倍的效果。正确地选择了存储引擎之后，还需要正确选择表中的数据类型，下一章我们将详细介绍如何选择合适的数据类型。

本文章参考MySQL由浅入深，希望可以对大家有所帮助。