MENU

MySQL 存储引擎

2021 年 01 月 17 日 • 阅读: 25 • MySQL,编码

与其他数据库不同,MySQL有一个存储引擎的概念,根据不同的需求可以选择最优的存储引擎。

MySQL存储引擎概述

插件式存储引擎是MySQL最重要的特性之一,用户可以根据应用的需要选择如何存储、索引数据、是否使用事务等。MySQL默认支持多种存储引擎,以适用于不同领域的数据库应用需要,用户可以通过选择不同的存储引擎提高应用的效率,提供灵活的存储,用户甚至可以根据自己的需求定制和使用自己的存储引擎,以实现最大程度的可定制性。

MySQL5.7支持的存储引擎包括:InnoDBMyISAMMEMORYCSVBLACKHOLEARCHIVEMERGE(MRG_MyISAM)FEDERATEDEXAMPLENDB 等,其中 InooDBNDB 提供事务安全表,其他存储引擎都是非事务安全表。

创建新表时如果不指定存储引擎,那么系统就会使用默认存储引擎,MySQL5.5 之前默认存储引擎是 MyISAM,5.5版本之后就改为了 InnoDB

修改默认存储引擎

如果要修改默认的存储引擎,可以在参数文件中设置 default_storage_engine,查看当前的默认存储引擎,可以使用如下命令:

mysql> show variables like 'default_storage_engine';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | InnoDB |
+------------------------+--------+
1 row in set, 1 warning (0.00 sec)

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

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: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary 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: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV 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: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
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)

其中 Support 不同值的含义分别是:

  • DEFAULT 支持并启用,并且为默认引擎
  • YES 支持并启用
  • NO 不支持
  • DISABLED 支持,但数据库启动时被禁用

创建表时指定存储引擎

创建新表时可以通过 ENGINE 关键字设置新建表的存储引擎,例如在下面的例子中,ai表是存储引擎是MyISAM 而 country 的存储引擎是 InnoDB:

mysql> CREATE TABLE ai (
    -> i bigint(20) not null AUTO_INCREMENT,
    -> PRIMARY KEY(i)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

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.02 sec)

修改已存在表的存储引擎

可以使用 ALTER TABLE 语句,将已经存在的表修改为其他存储引擎:

mysql> show create table ai \G;
*************************** 1. row ***************************
       Table: ai
Create Table: CREATE TABLE `ai` (
  `i` bigint(20) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`i`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> alter table ai engine = innodb;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table ai \G;
*************************** 1. row ***************************
       Table: ai
Create Table: CREATE TABLE `ai` (
  `i` bigint(20) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`i`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

修改后,表ai的存储引擎是InnoDB,可以使用InnoDB存储引擎的相关特性。

注意:修改表的存储引擎需要锁表并复制数据,对于线上环境的表进行这个操作非常危险,除非你非常了解可能造成的影响,否则在线上环境请考虑使用其他方式,例如:percona的 OSC工具

各存储引擎特性

重点介绍集中常用的存储引擎,并对比各个存储引擎之间的区别:

MySQL 常用存储引擎对比

特点MyISAMMemoryInnoDBArchiveNDB
B树索引支持支持支持----
备份/时间点恢复支持支持支持支持支持
支持集群--------支持
聚簇索引----支持----
数据压缩支持--支持支持--
数据缓存--N/A支持--支持
数据加密支持支持支持支持支持
支持外键----支持--支持
全文索引支持--支持----
地理坐标数据类型支持--支持----
地理坐标索引支持--支持----
哈希索引--支持----支持
索引缓存支持N/A支持--支持
锁粒度表级表级行级行级行级
MVCC多版本控制----支持----
支持复制支持有限支持支持支持支持
存储限制256TBRAM64TBNone384EB
T树索引--------支持
支持事务----支持--支持
统计信息支持支持支持支持支持

下面重点介绍四种存储引擎:MyISAM、MEMORY、InnoDB、Archive。

MyISAM 引擎

MyISAM引擎是MySQL5.5之前版本默认的存储引擎。MyISAM既不支持事务,也不支持外键,在5.5之前的版本中,MyISAM在某些场景中相对InnoDB的访问速度有明显的优势,对事务完整性没有要求或者以 SELECTINSERT 为主的应用都可以使用这个引擎来创建表,在MySQL5.6之后,MyISAM已经用的越来越少了。

每个MyISAM引擎的表在磁盘上存储成为三个文件,其文件名和表名相同,但扩展名不同:

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

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

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

MyISAM类型的表有可能会损坏,原因可能是多种多样的,损坏后的表可能不能被访问,会提示需要修复或返回错误的结果。MyISAM类型的表提供修复的工具,可以用 CHECK TABLE 来检查 MyISAM 表的健康,并用 REPAIR TABLE 语句修复一个损坏的MyISAM表。表损坏可能会导致数据库异常重新启动,需要尽快修复并尽可能地确认损坏的原因。

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

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

压缩表

其中,静态表是默认的存储格式。静态表的字段都是非变长字段,每一个记录的长度都固定的。这个存储方式的特点的优点是存储非常迅速,容易缓存,出现故障容易恢复;缺点是占用的空间通常比动态表多。静态表在存储数据时会按照列的宽度定义补足空格,但是在应用访问的时候并不会得到这些空格,这些空格在返回之前已经去掉了。需要注意的是,如果保存的内容后面本来就带有空格,那么返回结果的时候也会被去掉。

mysql> CREATE TABLE Myisam_char (name char(10)) engine=myisam;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO Myisam_char values('abcde'),('abcde  '),('  abcde'),('  abcde  ');
Query OK, 4 rows affected (0.00 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 引擎是 MySQL5.5 之后的默认存储引擎,提供了具有提交、回滚和崩溃恢复能力的事务安全保障,同时提供了更小的锁粒度和更强的并发能力,拥有自己独立的缓存和日志,在 MySQL5.6 和 5.7 版本中性能有较大的提升。

相比较 MyISAM 引擎,InnoDB 会占用更多的磁盘空间以保留数据和索引。但在多数场景下,InnoDB 都是更好选择,也是为何MySQL将默认存储引擎改成 InnoDB 的原因,在更是在 MySQL8.0 中 将所有的系统表也改为 InnoDB 存储引擎。

下面将介绍 IoonDB 存储引擎的表在使用过程中不同于其他存储引擎的特点,以及如何更好的使用 InnoDB 存储引擎。

自动增长列

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

下面定义表 autoincre_demo,其中列 i 使用自动增长列,对该表插入记录,然后查看自动增长列的处理情况:

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.03 sec)

mysql> INSERT INTO autoincre_demo VALUES(NULL,'1'),(2,'2'),(4,'3'),(NULL,'4');
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM autoincre_demo;
+---+------+
| i | name |
+---+------+
| 1 | 1    |
| 2 | 2    |
| 4 | 3    |
| 5 | 4    |
+---+------+
4 rows in set (0.00 sec)

可以通过ALTER TABLE [table_name] AUTO_INCREMENT = n; 来强制自动增长的初始值(也就是下次新增从那个数字开始,之后再次插入依然还是在上次的基础上+1):

mysql> ALTER TABLE autoincre_demo AUTO_INCREMENT=10;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> INSERT INTO autoincre_demo VALUES(NULL,'5');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM autoincre_demo;
+----+------+
| i  | name |
+----+------+
|  1 | 1    |
|  2 | 2    |
|  4 | 3    |
|  5 | 4    |
| 10 | 5    |
+----+------+
5 rows in set (0.00 sec)

注意: 在MySQL8.0 之前的版本中,对于InnoDB这个引擎,这个值是存储在内存当中的,如果数据库重启,这个值就会丢失,数据库会自动将 AUTO_INCREMENT 重置为当前存储的最大值+1,自增列记录的值和预期不一致,从而导致数据冲突。

在 MySQL8.0 中这个变量将保存到REDO LOG中,每一次计数器发生改变都会更新 REDO LOG。如果数据库发生重启,InnoDB会根据REDO LOG中的计数器信息来初始化其内存值。

可以使用 LAST_INSERT_ID() 来查询当前线程最后插入记录使用的值,如果插入多条记录,LAST_INSERT_ID() 只返回第一条数据的自动增长值,如果是手动指定了自动增长列的值,则 LAST_INSERT_ID() 的值不会更新:

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                0 |
+------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO autoincre_demo VALUES(1,'1');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                0 |
+------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO autoincre_demo(name) VALUES('2'),('3');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                2 |
+------------------+
1 row in set (0.00 sec)
  • 表中没有数据时 LAST_INSERT_ID() 的值是 0
  • 手动指定自动增长列时,LAST_INSERT_ID() 也是 0
  • 当自动插入多条数据时,LAST_INSERT_ID() 的值是第一条数据的自增值

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

例如:创建一个新的 MyISAM 表 myisam_demo ,自动增长列 d1 作为组合索引的第二列,对该表插入一些记录后,自动增长列是按照组合索引的第一列 d2 进行排序后递增的:

mysql> CREATE TABLE myisam_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.00 sec)

mysql> INSERT INTO myisam_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 myisam_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.03 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_fx_country_id (country_id),
    -> CONSTRAINT fa_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.03 sec)

创建索引时,可以指定在删除、更新父表时对子表进行相应的操作,包括 RESTRICTCASCADESET NULLNO ACTION

  • 其中 RESTRICTNO ACTION 相同,作用是限制子表有关联记录时父表不能更新;
  • CASCADE 表示父表在更新和删除时,更新或删除子表对应记录。
  • SET NULL 表示父表在更新或删除的时候,子表对应的字段被 SET NULL

选择后两种方式的时候要谨慎,可能会因为错误的操作导致数据丢失。

上面创建的表,子表外键指定的是:ON DELETE RESTRICT ON UPDATE CASCADE 方式,所以在主表删除记录的时候,如果子表有记录,则不允许删除;主表在更新记录时,如果子表有对应记录,则子表对应更新:

mysql> INSERT INTO country VALUES(1,afghanitan,'2020-12-10 05:15:34');
ERROR 1054 (42S22): Unknown column 'afghanitan' in 'field list'
mysql> INSERT INTO country VALUES(1,'afghanitan','2020-12-10 05:15:34');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO city VALUES(2,'kabul',1,'2020-12-13 12:00:12');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM country WHERE country_id = 1;
+------------+------------+---------------------+
| country_id | country    | last_update         |
+------------+------------+---------------------+
|          1 | afghanitan | 2020-12-10 05:15:34 |
+------------+------------+---------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM city WHERE country_id = 1;
+---------+-------+------------+---------------------+
| city_id | city  | country_id | last_update         |
+---------+-------+------------+---------------------+
|       2 | kabul |          1 | 2020-12-13 12:00:12 |
+---------+-------+------------+---------------------+
1 row in set (0.00 sec)

-- 删除失败 因为子表有关联外键
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 `fa_city_country` FOREIGN KEY (`country_id`) REFERENCES `country` (`country_id`) ON UPDATE CASCADE)

mysql> UPDATE country SET country_id = 200 WHERE country_id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM country WHERE country = 'afghanitan';
+------------+------------+---------------------+
| country_id | country    | last_update         |
+------------+------------+---------------------+
|        200 | afghanitan | 2020-12-16 22:22:23 |
+------------+------------+---------------------+
1 row in set (0.00 sec)

-- 父表更新后 子表的关联字段自动跟着更新
mysql> SELECT * FROM city WHERE city_id = 2;
+---------+-------+------------+---------------------+
| city_id | city  | country_id | last_update         |
+---------+-------+------------+---------------------+
|       2 | kabul |        200 | 2020-12-13 12:00:12 |
+---------+-------+------------+---------------------+
1 row in set (0.00 sec)

当某个表被其他表创建了外键参照,那么该表对应的索引或主键都禁止被删除。

当导入多个表的数据时,如果需要忽略之前的导入顺序,可以暂时关闭外键的检查;同样,在执行 LOAD DATEALTER TABLE 操作时,可以通过暂时关闭外键约束来加快执行的速度,执行完成后再开启:

关闭外键约束的命令是:SET FOREIGN_KEY_CHECKS = 0;

开启外键约束的命令是:SET FOREIGN_KEY_CHECKS = 1;

对应 InnoDB 类型的表,外键的信息可以通过 SHOW CREATE TABLESHOW TABLE STATUS 命令显示:

mysql> SHOW TABLE STATUS LIKE 'city' \G
*************************** 1. row ***************************
           Name: city
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 1
 Avg_row_length: 16384
    Data_length: 16384
Max_data_length: 0
   Index_length: 16384
      Data_free: 0
 Auto_increment: 3
    Create_time: 2020-12-15 23:28:12
    Update_time: 2020-12-16 22:22:23
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE city \G
*************************** 1. row ***************************
       Table: city
Create Table: 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_fx_country_id` (`country_id`),
  CONSTRAINT `fa_city_country` FOREIGN KEY (`country_id`) REFERENCES `country` (`country_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

注意:外键需要注意的细节较多,一旦使用不当,可能会带来性能下降或数据不一致的问题,尤其是在 OLTP类型(联机事务处理,个人理解:高可用,对数据有强一致性要求,例如电商、金融这类)的应用中,需要谨慎使用外键。

主键和索引

不同于其他存储引擎,InnoDB 的数据文件本身就是以聚簇索引的形式保存的,这个聚簇索引也被叫做主索引,并且也是 InnoDB 表的主键。InnoDB 的每行数据都是保存在主索引的叶子节点上,所以每个 InnoDB 表都必须包含主键,如果没有显式指定主键,那么 InnoDB 存储引擎会自动创建一个长度为 6 个字节的 LONG 类型的隐藏字段作为主键。

考虑到聚簇索引的特点对于查询优化的效果,所有 InnoDB 表都应该显式的指定主键,一般来说,主键都应该按照以下原则来选择:

  • 满足唯一和非空约束
  • 优先考虑使用经常被当作查询条件的字段或自增字段
  • 字段值基本不会被修改
  • 使用尽可能短的字段

在 InnoDB 表上,除了主键之外的索引都叫做辅助索引或二级索引,二级索引会指向主索引,并通过主索引获取最终数据。因此,主键设置是否合理,都会对索引的效率产生影响。

存储方式

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

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

多表空间存储,这种方式创建的表的表结构仍然保存在 .frm 文件中,但是每个表的数据和索引单独保存在 .ibd 中。如果是一个分区表,则每个分区对应的 .idb 文件是 “表名+分区名”,可以在创建分区时指定每个分区的数据文件位置,以此将此表的 IO 平均分配在多个磁盘上。

使用共享表空间时随着数据不断增长,表空间的管理维护将变得越来越困难,所以一般都建议多表空间。多表空间存储在 MySQL5.7 中默认设置为 ON ,即新建的表都是按照多表空间的方式创建。如果修改此参数为 OFF,则新创建的表都会改为共享表空间存储,但已经创建的多表空间的表仍然保存原来的访问方式。

在一些老版本的表中,很多都是共享表空间,可以通过以下命令修改为多表空间:

mysql> SET GLOBAL innodb_file_per_table=1;
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER TABLE city ENGINE=InnoDB;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

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

对于使用了多表空间特性的表,可以比较方便的进行单表操作的备份和恢复操作,但是直接复制.ibd 文件不行,因为没有共享表空间的字典信息,直接复制的 .ibd 文件和 .frm 文件恢复时是不能被正常识别的,但可以通过以下命令:

ALTER TABLE tel_name DISCARD TABLESPACE;
ALTER TABLE tel_name IMPORT TABLESPACE;

注意: 即使在多表空间的存储方式下,共享表空间仍然是必须的,InnoDB 会把内部数据词典和在线重做日志,放在这个文件中。

MEMORY 引擎

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

mysql> CREATE TABLE tab_memory ENGINE=MEMORY
    ->  SELECT phone,sheng,shi,type
    ->  FROM phone limit 600;
Query OK, 600 rows affected (0.01 sec)
Records: 600  Duplicates: 0  Warnings: 0

mysql> SELECT COUNT(*) FROM tab_memory;
+----------+
| COUNT(*) |
+----------+
|      600 |
+----------+
1 row in set (0.00 sec)

mysql> SHOW TABLE STATUS like 'tab_memory' \G;
*************************** 1. row ***************************
           Name: tab_memory
         Engine: MEMORY
        Version: 10
     Row_format: Fixed
           Rows: 600
 Avg_row_length: 189
    Data_length: 1044672
Max_data_length: 66060225
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2020-12-20 01:51:28
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

给 MEMORY 表创建索引时,可以指定使用 HASH 索引还是 BTREE 索引:

-- mysql> CREATE INDEX mem_hash USING BTREE on tab_memory (phone);
mysql> CREATE INDEX mem_hash USING HASH on tab_memory (phone);
Query OK, 600 rows affected (0.01 sec)
Records: 600  Duplicates: 0  Warnings: 0

mysql> SHOW INDEX FROM tab_memory \G;
*************************** 1. row ***************************
        Table: tab_memory
   Non_unique: 1
     Key_name: mem_hash
 Seq_in_index: 1
  Column_name: phone
    Collation: NULL
  Cardinality: 300
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: HASH
      Comment:
Index_comment:
1 row in set (0.00 sec)

在启动MySQL 服务的时候使用 --init-file 选项,把 INSERT INTO ... SELECT 或 LOAD DATA INFILE 这样的语句放入这个文件中,就可以在服务启动时从持久稳固的数据源装载表。

服务器需要足够的内存来维持所有在同一时间使用的 MEMORY 表,当不再需要 MEMORY 表的内容之时,要释放被MEMORY 表使用的内存,应当执行 DELETE FROMTRUNCATE TABLE,或整个删除表(使用 DROP TABLE 操作)。

每个 MEMORY 表都可以方式的数据量大小,受到 max_heap_table_size 系统变量的约束,这个系统变量的初始值是16MB,可以根据需求加大。在定义 MEMORY 表的时候,可以通过 MAX_ROWS 子句来指定表的最大行数。

MEMORY 类型的存储引擎主要用于那些内容变化不频繁的代码表,或作为统计操作的中间结果表,便于高效地对中间结果进行分析并且得到最终的计算结果。对存储引擎为 MEMORY 的表更新操作要谨慎,因为数据并没有实际写入磁盘中,所以一定要对下次重启服务如何获取这些修改后的数据有所考虑。

临时表数据量受MySQL设置影响,超过设置临时表数据,无法写入数据:

mysql> INSERT INTO tab_memory SELECT * FROM tab_memory;
ERROR 1114 (HY000): The table 'tab_memory' is full

设置的是临时表内存占用64MB,目前这张表数据量刚好达到64MB,继续写入就会提示这个

MERGE 引擎

MERGE 存储引擎也被称之为 MRG_MyISAM,是一组 MyISAM 表的组合。这些 MyISAM 表必须结构完全相同,MERGE 本身并不存储数据,对 MERGE 类型的表进行删、改、查操作,本质上是对内部的 MyISAM 表进行的。对于 MERGE 类型的表进行插入操作是通过 INSERT_METHOD 子句定义插入的表。可以有三个不同的值,FIRST 或 LAST 值使得插入操作被相应的作用在第一个表或最后一个表上,不定义这个语句或定义为 NO 表示不能对这个 MERGE 表执行插入操作。

可以对 MERGE 表进行 DROP 操作,这个操作只是删除 MERGE 表的定义,对内部的表没有任何影响。

MERGE 在磁盘存储上只保留两个文件,文件名以表名开始,一个 .frm 文件存储表定义,另一个 .MRG 存储组合表信息,包括 MERGE 表是由那些表组成的,插入新数据时的依据。可以通过修改 .MRG 文件来修改 MERGE表,但修改后需要通过 FLUSH TABELES 刷新。

下面是创建和使用 MERGE 表的示例:

  1. 创建三个表 payment_2006payment_2007payment_all,其中 payment_all 是前面两张表的MERGE表:
mysql> CREATE TABLE payment_2006(
    -> country_id SMALLINT,
    -> payment_data datetime,
    -> amount DECIMAL(15,2),
    -> KEY idx_fk_country_id (country_id)
    -> )ENGINE=MyISAM;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE payment_2007(
    -> country_id SMALLINT,
    -> payment_data datetime,
    -> amount DECIMAL(15,2),
    -> KEY idx_fk_country_id (country_id)
    -> )ENGINE=MyISAM;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE payment_all(
    -> country_id SMALLINT,
    -> payment_data datetime,
    -> amount DECIMAL(15,2),
    -> INDEX(country_id)
    -> )ENGINE=MERGE UNION=(payment_2006,payment_2007) INSERT_METHOD=LAST;
Query OK, 0 rows affected (0.00 sec)

payment_2006payment_2007表中插入数据,并且查看三张表的数据:

mysql> INSERT INTO payment_2006 VALUES(1,'2006-05-01',100000),(2,'2006-08-15',15000);
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_data        | amount    |
+------------+---------------------+-----------+
|          1 | 2006-05-01 00:00:00 | 100000.00 |
|          2 | 2006-08-15 00:00:00 |  15000.00 |
+------------+---------------------+-----------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM payment_2007;
+------------+---------------------+-----------+
| country_id | payment_data        | 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)

mysql> SELECT * FROM payment_all;
+------------+---------------------+-----------+
| country_id | payment_data        | amount    |
+------------+---------------------+-----------+
|          1 | 2006-05-01 00:00:00 | 100000.00 |
|          2 | 2006-08-15 00:00:00 |  15000.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)

payment_all 表的数据是 payment_2006 和 payment_2007 两张表合并后的结果集。

再向 payment_all 表插入数据,由于 MERGE 表定义的是 INSERT_METHOD=LAST ,就会向最后一张表写入数据:

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_data        | amount    |
+------------+---------------------+-----------+
|          1 | 2006-05-01 00:00:00 | 100000.00 |
|          2 | 2006-08-15 00:00:00 |  15000.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 |
+------------+---------------------+-----------+
5 rows in set (0.00 sec)

mysql> SELECT * FROM payment_2007;
+------------+---------------------+-----------+
| country_id | payment_data        | 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 表并不能智能的将记录写入对应的表中,而分区表是可以的(分区功能于 MySQL5.1 版本推出,经过多个版本的更新,目前已比较完善 )。

通常我们使用的 MERGE 表来透明地对多个表进行查询和更新操作,而这种按照时间记录的操作日志表则可以透明的进行插入。

TokuDB引擎

MySQL 除了自带的存储引擎之外,还支持一些常见的第三方存储引擎,在某些特定应用中也有广泛的使用,比如列式存储引擎 Infobright 以及 高写行能,高压缩的 TokuDB 就是其中两个非常有代表性的存储引擎,这里简单介绍 TokuDB 。

ToKuDB 是一个高性能、支持事务处理的存储引擎, 在 MySQL5.6 之前,可以安装在 MySQL、MariaDB 中,被 Percona 公司收购后,目前最新版本可在 Percona Server for MySQL (类似于 MariaDB 一样是 MySQL 的分支,完全兼容 MySQL,主要做了服务端的优化增强,客户端无修改) 中使用。 TokuDB 存储引擎具有高扩展性、高压缩率、高效的写入性能,支持大多数在线的 DDL 操作。且是开源的,可以从 Percona 官网下载。

对比 MySQL5.6 InnoDB 存储引擎, TokuDB 有以下特性:

  • 使用 Fractal 树索引保证高效的插入性能 (查了一下 好像是快20 ~ 80 倍)
  • 优秀的压缩特性,比 InnoDB 高近十倍
  • Hot Schema Changes 特性支持 在线创建索引和添加、删除属性列等DDL操作
  • 使用 Bulk Loader 达到快速加载大量数据
  • 提供了主从延迟消除技术
  • 支持 ACID 和 MVCC

通过上面的介绍可以发现 TokuDB 适合以下场景:

  • 日志数据,日志通常插入频繁,且存储量大
  • 历史数据,通常写入后不再操作数据,可以利用 TokuDB 的高压缩特性进行存储
  • 在线 DDL 较频繁的场景,可以使用TokuDB 大大增加系统可用性

如何选择合适的存储引擎

在选择存储引擎时,应根据应用特点选择合适的存储引擎。对于复杂的应用系统,还可以使用多种存储引擎进行组合。

MyISAM 适合的场景

  • 在 MySQL5.5之前的版本是默认的存储引擎,
  • 应用是以读和插入为主,只有极少更新和删除操作。
  • 并且对事务的完整性没有要求,没有并发写操作,

OLTP 环境一般建议不要使用 MyISAM。

InnoDB 适合场景

  • MySQL 5.5 之后的默认存储引擎
  • 用于事务处理应用程序
  • 支持外键
  • 对事务完整性有较高要求
  • 在并发条件下要求数据的一致性
  • 数据除了插入和查询外,还有很多更新、删除操作,InnoDB 存储引擎有效的降低由于删除和更新导致的锁定

在大多数的应用系统,InnoDB 都是合适的选择。

MEMORY 适用场景

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

MERGE 适用场景

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

以上存储引擎选择仅作为建议,实际应用场景千差万别。

CC版权: 本篇博文采用《CC 协议》,转载必须注明作者和本文链接
最后编辑于: 2021 年 04 月 14 日