MySQL 存储引擎

前言

  • 和大多数数据库不同,MySQL 中有一个存储引擎的概念,针对不同的存储需求可以选择最优的存储引擎。

1、MySQL 存储引擎概述

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

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

  • 创建新表时如果不指定存储引擎,那么系统就会使用默认存储引擎,MySQL 5.5 之前的默认存储引擎是 MyISAM,5.5 之后改为了 InnoDB。

1.1 常用存储引擎对比

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

1.2 MySQL 存储引擎设置

  • 1)查询当前数据库支持的存储引擎

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    >  show engines;

    +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
    | Engine | Support | Comment | Transactions | XA | Savepoints |
    +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
    | ARCHIVE | YES | Archive storage engine | NO | NO | NO |
    | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
    | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
    | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
    | MyISAM | YES | MyISAM storage engine | NO | NO | NO |
    | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
    | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
    | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
    | CSV | YES | CSV storage engine | NO | NO | NO |
    +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
    9 rows in set (0.00 sec)
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    > show variables like 'have%';

    +------------------------+----------+
    | Variable_name | Value |
    +------------------------+----------+
    | have_compress | YES |
    | have_dynamic_loading | YES |
    | have_geometry | YES |
    | have_openssl | YES |
    | have_profiling | YES |
    | have_query_cache | NO |
    | have_rtree_keys | YES |
    | have_ssl | YES |
    | have_statement_timeout | YES |
    | have_symlink | DISABLED |
    +------------------------+----------+
    10 rows in set (0.00 sec)
  • 2)查看当前默认存储引擎

    1
    2
    3
    4
    5
    6
    7
    8
    > show variables like 'table_type';

    +---------------+--------+
    | Variable_name | Value |
    +---------------+--------+
    | table_type | InnoDB |
    +---------------+--------+
    1 row in set (0.00 sec)
  • 3)设置新建表的存储引擎

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    # create table 表名 (
    列名称字段 数据类型(长度) 约束条件,
    列名称字段 数据类型(长度) 约束条件
    ) engine = 存储引擎名;
    > create table ai (
    i bigint(20) not null auto_increment,
    primary key(i)
    ) engine = MyISAM;

    Query OK, 0 rows affected (0.08 sec)
  • 4)修改已有表的存储引擎

    1
    2
    3
    4
    5
    6
    # 修改已有表的存储引擎
    # alter table 表名 engine = 存储引擎名;
    > alter table ai engine = InnoDB;

    Query OK, 0 rows affected (0.09 sec)
    Records: 0 Duplicates: 0 Warnings: 0
  • 5)显示表的存储引擎

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    # 显示表的存储引擎
    # show create table 表名;
    > 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=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
    +-------+-----------------------------------------------------------------------------------------+
    1 row in set (0.01 sec)

2、MyISAM 存储引擎

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

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

2.1 存储方式

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

    • .frm(存储表定义)
    • .MYD(MYData,存储数据)
    • .MYI(MYIndex,存储索引)
  • 数据文件和索引文件可以放置在不同的目录,平均分布 IO,获得更快的速度。

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

  • MyISAM 类型的表可能会损坏,原因可能是多种多样的,损坏后的表可能不能被访问,会提示需要修复或者访问后返回错误的结果。

  • MyISAM 类型的表提供修复的工具,可以用 CHECK TABLE 语句来检查 MyISAM 表的健康,并用 REPAIR TABLE 语句修复一个损坏的 MyISAM 表。

  • 表损坏可能导致数据库异常重新启动,需要尽快修复并尽可能地确认损坏的原因。

2.2 存储格式

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

    • 静态(固定长度)表
    • 动态表
    • 压缩表
  • 其中,静态表是默认的存储格式。静态表中的字段都是非变长字段,这样每个记录都是固定长度的,这种存储方式的优点是存储非常迅速,容易缓存,出现故障容易恢复;缺点是占用的空间通常比动态表多。静态表的数据在存储时会按照列的宽度定义补足空格,但是在应用访问的时候并不会得到这些空格,这些空格在返回给应用之前已经去掉。

    • 但是也有些需要特别注意的问题,如果需要保存的内容后面本来就带有空格,那么在返回结果的时候也会被去掉,开发人员在编写程序的时候需要特别注意,因为静态表是默认的存储格式,开发人员可能并没有意识到这一点,从而丢失了尾部的空格。
  • 动态表中包含变长字段,记录不是固定长度的,这样存储的优点是占用的空间相对较少,但是频繁地更新和删除记录会产生碎片,需要定期执行 OPTIMIZE TABLE 语句或 myisamchk-r 命令来改善性能,并且在出现故障时恢复相对比较困难。

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

3、InnoDB 存储引擎

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

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

3.1 自动增长列

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

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    > create table autoincre_demo (
    i smallint not null auto_increment,
    name varchar(10), primary key(i)
    ) engine = innodb;

    Query OK, 0 rows affected (0.04 sec)

    > insert into autoincre_demo values (1, '1'), (0, '2'), (null, '3');

    Query OK, 3 rows affected (0.09 sec)
    Records: 3 Duplicates: 0 Warnings: 0

    > select * from autoincre_demo;

    +---+------+
    | i | name |
    +---+------+
    | 1 | 1 |
    | 2 | 2 |
    | 3 | 3 |
    +---+------+
    3 rows in set (0.00 sec)
  • 2)可以通过 alter table *** auto_increment = n; 语句强制设置自动增长列的初始值。

    • 默认从 1 开始。
    • 该强制的默认值是保留在内存中的,如果该值在使用之前数据库重新启动,那么这个强制的默认值就会丢失,就需要在数据库启动以后重新设置。
  • 3)可以使用 last_insert_id() 查询当前线程最后插入记录使用的值。如果一次插入了多条记录,那么返回的是第一条记录使用的自动增长值。

    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
    > insert into autoincre_demo values(4, '4');

    Query OK, 1 row affected (0.05 sec)

    > select last_insert_id();

    +------------------+
    | last_insert_id() |
    +------------------+
    | 2 |
    +------------------+
    1 row in set (0.01 sec)

    > insert into autoincre_demo (name) values ('5'), ('6'), ('7');

    Query OK, 3 rows affected (0.01 sec)
    Records: 3 Duplicates: 0 Warnings: 0

    > select last_insert_id();

    +------------------+
    | last_insert_id() |
    +------------------+
    | 5 |
    +------------------+
    1 row in set (0.00 sec)
  • 4)对于 InnoDB 表,自动增长列必须是索引。如果是组合索引,也必须是组合索引的第一列,但是对于 MyISAM 表,自动增长列可以是组合索引的其他列,这样插入记录后,自动增长列是按照组合索引的前面几列进行排序后递增的。

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

    > 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.01 sec)
    Records: 6 Duplicates: 0 Warnings: 0

    > 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)
    • 创建一个新的 MyISAM 类型的表 autoincre_demo,自动增长列 d1 作为组合索引的第二列,对该表插入一些记录后,可以发现自动增长列是按照组合索引的第一列 d2 进行排序后递增的。

3.2 外键约束

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

    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
    > 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, 1 warning (0.05 sec)

    > 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 约束名称 foreign key(外键名称) references 源表(主键名称)
    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, 1 warning (0.07 sec)
    • country 表是父表,country_id 为主键索引,city 表是子表,country_id 字段为外键,对应于 country 表的主键 country_id。
  • 2)在创建索引时,可以指定在删除、更新父表时,对子表进行的相应操作,包括 restrict、cascade、set null 和 no action。选择后两种方式的时候要谨慎,可能会因为错误的操作导致数据的丢失。

    • restrict 和 no action 相同,是指限制在子表有关联记录的情况下父表不能更新;
    • cascade 表示父表在更新或者删除时,更新或者删除子表对应记录;
    • set null 则表示父表在更新或者删除的时候,子表的对应字段被 set nullL。

      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
      > select * from country where country_id = 1;

      +------------+-------------+---------------------+
      | country_id | country | last_update |
      +------------+-------------+---------------------+
      | 1 | Afghanistan | 2006-02-15 04:44:00 |
      +------------+-------------+---------------------+
      1 row in set (0.00 sec)

      > select * from city where country_id = 1;

      +---------+-------+------------+---------------------+
      | city_id | city | country_id | last_update |
      +---------+-------+------------+---------------------+
      | 251 | Kabul | 1 | 2006-02-15 04:45:25 |
      +---------+-------+------------+---------------------+
      1 row in set (0.00 sec)

      > delete from country where country_id=1;

      ERROR 1451 (23000): Cannot delete or update a parent row:
      a foreign key constraint fails ('sakila/city', CONSTRAINT 'fk_city_country'
      FOREIGN KEY ('country_id') REFERENCES 'country' ('country_id') ON UPDATE CASCADE)

      > update country set country_id = 10000 where country_id = 1;

      Query OK, 1 row affected (0.04 sec)
      Rows matched: 1 Changed: 1 Warnings: 0

      > select * from country where country = 'Afghanistan';

      +------------+-------------+---------------------+
      | country_id | country | last_update |
      +------------+-------------+---------------------+
      | 10000 | Afghanistan | 2007-07-17 09:45:23 |
      +------------+-------------+---------------------+
      1 row in set (0.00 sec)

      > select * from city where city_id = 251;

      +---------+-------+------------+---------------------+
      | city_id | city | country_id | last_update |
      +---------+-------+------------+---------------------+
      | 251 | Kabul | 10000 | 2006-02-15 04:45:25 |
      +---------+-------+------------+---------------------+
      1 row in set (0.00 sec)
    • 当某个表被其他表创建了外键参照,那么该表的对应索引或者主键禁止被删除。

  • 3)在导入多个表的数据时,如果需要忽略表之前的导入顺序,可以暂时关闭外键的检查;同样,在执行 load data 和 alter table 操作的时候,可以通过暂时关闭外键约束来加快处理的速度,关闭的命令是 set foreign_key_checks = 0;,执行完成之后,通过执行 set foreign_key_checks = 1; 语句改回原状态。

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

    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
    # show create table 表名;
    > 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 DEFAULT CHARSET=utf8 |
    +-------+--------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)

    # show table status like 表名;
    > show table status like 'city';

    +------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+
    | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment |
    +------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+
    | city | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 16384 | 0 | 1 |
    +------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+

    ---------------------+-------------+------------+-----------------+----------+----------------+---------+
    Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
    ---------------------+-------------+------------+-----------------+----------+----------------+---------+
    2018-06-21 18:58:55 | NULL | NULL | utf8_general_ci | NULL | | |
    ---------------------+-------------+------------+-----------------+----------+----------------+---------+

    1 row in set (0.06 sec)

3.3 存储方式

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

    • 使用共享表空间存储,这种方式创建的表的表结构保存在 .frm 文件中,数据和索引保存在 innodb_data_home_dirinnodb_data_file_path 定义的表空间中,可以是多个文件。
    • 使用多表空间存储,这种方式创建的表的表结构仍然保存在 .frm 文件中,但是每个表的数据和索引单独保存在 .ibd 中。如果是个分区表,则每个分区对应单独的 .ibd 文件,文件名是 “表名+分区名”,可以在创建分区的时候指定每个分区的数据文件的位置,以此来将表的 IO 均匀分布在多个磁盘上。
  • 要使用多表空间的存储方式,需要设置参数 innodb_file_per_table,并且重新启动服务后才可以生效,对于新建的表按照多表空间的方式创建,已有的表仍然使用共享表空间存储。如果将已有的多表空间方式修改回共享表空间的方式,则新建表会在共享表空间中创建,但已有的多表空间的表仍然保存原来的访问方式。所以多表空间的参数生效后,只对新建的表生效。

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

  • 对于使用多表空间特性的表,可以比较方便地进行单表备份和恢复操作,但是直接复制 .ibd 文件是不行的,因为没有共享表空间的数据字典信息,直接复制的 .ibd 文件和 .frm 文件恢复时是不能被正确识别的,但可以通过以下命令将备份恢复到数据库中,但是这样的单表备份,只能恢复到表原来所在的数据库中,而不能恢复到其他的数据库中。如果要将单表恢复到目标数据库,则需要通过 mysqldump 和 mysqlimport 来实现。

    1
    2
    alter table tbl_name discard tablespace;
    alter table tbl_name import tablespace;
  • 即便在多表空间的存储方式下,共享表空间仍然是必须的,InnoDB 把内部数据词典和在线重做日志放在这个文件中。

文章目录
  1. 1. 前言
  2. 2. 1、MySQL 存储引擎概述
    1. 2.1. 1.1 常用存储引擎对比
    2. 2.2. 1.2 MySQL 存储引擎设置
  3. 3. 2、MyISAM 存储引擎
    1. 3.1. 2.1 存储方式
    2. 3.2. 2.2 存储格式
  4. 4. 3、InnoDB 存储引擎
    1. 4.1. 3.1 自动增长列
    2. 4.2. 3.2 外键约束
    3. 4.3. 3.3 存储方式
隐藏目录