04、MySQL 教程 - MySQL 表类型(存储引擎)的选择

一、MySQL存储引擎简介

MySQL支持多种存储引擎,以适用于不同领域的数据库应用需要,用户可以根据需要进行选择甚至是定制自己的引擎以提高应用效率。

使用如下命令查看当前版本mysql支持的存储引擎:

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)

在使用MySQL创建新表的时候,如果没有指定存储引擎,那么系统会使用默认的存储引擎,MySQL5.5之前的默认存储引擎是MyISAM,之后改成了InnoDB。使用如下命令查看当前默认存储引擎:

mysql> show variables like '%storage_engine%';
+----------------------------------+--------+
| Variable_name                    | Value  |
+----------------------------------+--------+
| default_storage_engine           | InnoDB |
| default_tmp_storage_engine       | InnoDB |
| disabled_storage_engines         |        |
| internal_tmp_disk_storage_engine | InnoDB |
+----------------------------------+--------+
4 rows in set, 1 warning (0.00 sec)

在创建新表的时候如果不想用默认的存储引擎,可以使用关键字ENGINE显示的指定存储引擎类型,下面ai表的存储引擎定义为MyISAM,country表的引擎定义为InnoDB:

mysql> create table ai (i bigint(20) not null auto_increment,primary key (i) ) engine=MyISAM default charset=gbk;
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=gbk;
Query OK, 0 rows affected (0.02 sec)

如果对于一个已经存在的表想要更改它的存储引擎,可以使用alter语句:

mysql> alter table ai engine=innodb;
Query OK, 0 rows affected (0.03 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=gbk
1 row in set (0.00 sec)

定义好表的存储引擎后就可以使用该引擎的相关特性。

二、各存储引擎的特性

这里主要介绍最常用的几种引擎:

 

2.1 MyISAM

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

每个MyISAM类型的表在磁盘上都会存储为3个文件,文件名都和表名一样,扩展名分别为:

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

数据文件和索引文件可以放置在不同的目录,平均分布IO,获得更快的速度;如果需要指定具体的目录,在创建表的时候可以通过 DATA DIRECTORY 和 INDEX DIRECTORY 语句指定,但是需要注意,路径必须是绝对路径且具有访问权限。

MyISAM类型的表可能会损坏,造成的原因很多,损坏后会造成以下情况:表可能无法访问;提示你需要修复;访问后返回错误的结果;数据库异常重新启动等等。这时候我们就需要对表进行修复,CHECK TABLE 语句来检测MyISAM表的健康状况,REPAIR TABLE 语句来修复。(具体修复过程及查找损坏原因后面再介绍)

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

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

关于这些表的具体介绍就不多说明,这跟数据结构里面的一些存储规则很类似,这里仅仅说一下需要注意的一些点:

1、 在静态表中查询数据会在返回之前将后面的空格删除:;

mysql> create table myisam_char(name char(10)) engine=myisam;
Query OK, 0 rows affected (0.01 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)

从上面的例子可以看出MyISAM类型的静态表在存储的时候会按照指定的列的宽度来向后补足空格,但是在应用访问的时候会返回去掉后面空格的数据,因此在存储的数据前后有空格时需要注意,数据后面的空格会被一并抹除掉,前面的空格则会保留。

2、 频繁的更新和删除动态表的记录会产生碎片,因此需要定期执行OPTIMIZETABLE语句或myisamchr-r命令来改善性能;

3、 压缩表由myisampack工具创建;

2.2 InnoDB

InnoDB是MySQL里面默认的存储引擎,也是使用的最多的存储引擎;它提供了具有提交、回滚和崩溃恢复能力的事务安全性能,但相对于MyISAM存储引擎,InnoDB写的处理效率差一些,而且会占用更多的磁盘空间以保留数据和索引。

1. 自增长列

InnoDB表的自增长列可以手动插入,如果忽略或是插入0或空值 ,则实际上该列还是会自动增长,看下面的例子:

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

mysql> insert into autoincre_demo values(1,'1'),(0,'2'),(null,'3');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

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

自增长列的起始值默认从1开始,如果想要从别的值开始可以用语句 ALTER TABLE *** AUTO_INCREMENT = n 来指定,但是需要注意,该语句强制指定的初始值是保存在内存中的,一旦数据库重新启动就需要重新设置

可以使用**LAST_INSERT_ID()**来查询当前线程最后插入记录使用的值:

mysql> insert into autoincre_demo values(4,'4');
Query OK, 1 row affected (0.00 sec)

mysql> select LAST_INSERT_ID();   这里需要解释以下为什么这里查出来的记录是2,因为这个查询只会查到上一次的insert或update语句更改的最新记录,但是它需要是自动增长的才算,手动指定的不算,比如这里手动指定的记录4就不算,而上面手动指定的0或null被自动增长代替,所以算作自增,因此此处结果为2.
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                2 |
+------------------+
1 row in set (0.00 sec)

mysql> insert into autoincre_demo(name) values('5'),('6'),('7');  这里没有指定自增长列,它是自己增长的,因而查询结果为当前多条数据的第一条自增长列对应的值,也就是5。
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                5 |
+------------------+
1 row in set (0.00 sec)

下面几个例子就很好的证明了上面的结论
mysql> insert into autoincre_demo values(8,'8');
Query OK, 1 row affected (0.01 sec)

mysql> select LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                5 |
+------------------+
1 row in set (0.00 sec)

mysql> insert into autoincre_demo values(9,'9');
Query OK, 1 row affected (0.00 sec)

mysql> select LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                5 |
+------------------+
1 row in set (0.00 sec)

mysql> insert into autoincre_demo(name) values('10');
Query OK, 1 row affected (0.01 sec)

mysql> select LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|               10 |
+------------------+
1 row in set (0.00 sec)

这里必须提一点,对于InnoDB表,自增长列必须是索引,且排在第一列,如果是作为组合索引,它也必须组合索引的第一列。但是对于MyISAM表就不必,它的自增长列可以是组合索引的其他列,此时的自增就会先按照第一索引进行排序后再自增,如下面的例子:

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

mysql> insert into autoincre_demo2(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_demo2;
+----+----+------+
| 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)

2. 外键约束

MySQL中只有InnoDB支持外键,在创建外键时要求父表必须要有对应的索引。下面创建一个country表作为父表,city表作为子表,country_id作为外键:

由于country表之前创建过,这里就显示一下它的结构
mysql> show create table country \G;
*************************** 1. row ***************************
       Table: country
Create Table: CREATE TABLE country (
  country_id smallint(5) 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
1 row in set (0.00 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),
    -> 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)

关键字RESTRICT和NO ACTION相同,作用是拒绝父表的的更新删除操作(如果子表有关联记录的情况下);CASCADE表示父表在更新或删除时,同时对子表对应的记录进行同样操作;SET NULL则表示父表在更新或删除操作时,子表的对应字段被置为空。后两种要谨慎使用,因为会导致数据丢失。

因为外键的存在导致表之间联系比较紧密,在导入表数据或者修改表结构时往往由于这种联系导致速度较慢,此时我们可以暂时关闭外键的检查来加快处理速度,关闭的命令为:SET FOREIGN_KEY_CHECKS = 0;执行完之后通过命令:SET CHECK_FOREIGN_KEY = 1 ;改回原来的状态。

对InnoDB类型的表,通过使用show table status命令来显示包含外键的表的状态信息:

mysql> show table status like 'city' \G;
*************************** 1. row ***************************
           Name: city
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 16384
      Data_free: 0
 Auto_increment: 1
    Create_time: 2018-12-17 16:07:17
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

3. 存储方式

InnoDB存储表和索引主要有两种方式:

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

一般都使用的共享表存储,如果要使用多表空间存储需要设置参数 innodb_file_per_table,并且需要重启服务后才可以对新建的表生效,但原来的表仍使用共享表空间存储;

多表空间的数据文件没有大小限制,不需要设置初始大小等一些其它限制参数;

使用多表空间特性的表,可以方便的进行单表备份和恢复操作,但不能直接复制.ibd文件,因为还有一些东西在共享表空间里,可以通过以下命令“ ALTER TABLE tb1_name DISCARD TABLESPACE; ALTER TABLE tb1_name IMPORT TABLESPACE;" 进行备份和恢复,但是只能恢复到原来所在的数据库中,如果想要恢复到别的数据库需要通过mysqldump和mysqlimport来实现。

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

2.3 MEMORY

MEMORY存储引擎使用内存中的内容来创建表,每个MEMORY表只实际对应一个磁盘文件,格式是.frm。它的访问速度很快,默认采用hash索引,但一旦服务关闭,表中的数据就会丢失。

下面使用city表的记录来创建memory的表,并且指定索引为hash索引还是btree索引:

mysql> create table tab_memory engine=memory select city_id,city,country_id from city group by city_id;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create index men_hash using hash on tab_memory (city_id);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from tab_memory \G;
*************************** 1. row ***************************
        Table: tab_memory
   Non_unique: 1
     Key_name: men_hash
 Seq_in_index: 1
  Column_name: city_id
    Collation: NULL
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: HASH
      Comment:
Index_comment:
1 row in set (0.00 sec)

如果想把hash索引更换为btree索引,需这样做:

mysql> drop index men_hash on tab_memory;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create index men_hash using btree on tab_memory(city_id);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

如何将memory类型表的数据保存下来?我们在启动MySQL时需要使用--init-file选项,把把各种表单数据操作语句放入到文件中,这样就可以在在服务启动时从持久稳固的数据源装载表。

memory表存储数据的大小受到max_heap_table_size系统变量的约束,初始值为16MB,当然,我们可以根据需要加大,并且在创建memory表时可以通过MAX_ROWS字句指定表的最大行数来限制表的大小。

当memory表不再用的时候需要释放它的内存,应当执行DELETE FROM 或TRUNCATE TABLE 或 DROP TABLE操作。

memory类型的表主要用于那些内容变化不频繁的代码表,或者作为统计的中间结果表;对这种表进行更新操作需要谨慎,应考虑到数据的持久性问题。

2.4 MERGE

MERGE存储引擎是一组MyISAM表的组合,只不过要求MyISAM表的结构必须完全相同,MERGE表本身在磁盘上只有两个文件,一个.frm文件存储表结构,另一个.MRG文件包含组合表的信息。

对MERGE表一些数据上从操作实际上是对内部的MyISAM表进行的,比如插入操作:用INSERT_METHOD子句,它有三个取值,FIRST或LAST表明是插入内部第一个还是最后一个表,NO或者不定义这个子句表示不能执行插入操作。比如DROP操作:只删除MERGE表的定义,对内部的表没有任何影响。

如果通过.MRG文件来修改MERGE表,那需要 FLUSH TABLES 来刷新后才可生效。

下面举个例子:用两个myisam表创建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 (0.01 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.01 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.01 sec)

向myisam表中插入数据:

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-05-01',350000),(2,'2007-08-15',220000);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

分别查看merge表和myisam表中数据:

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-05-01 00:00:00 | 350000.00 |
|          2 | 2007-08-15 00:00:00 | 220000.00 |
+------------+---------------------+-----------+
2 rows in set (0.00 sec)

mysql> select * from payment_all;        可以看出merge表是myisam表的集合。
+------------+---------------------+-----------+
| 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-05-01 00:00:00 | 350000.00 |
|          2 | 2007-08-15 00:00:00 | 220000.00 |
+------------+---------------------+-----------+
4 rows in set (0.00 sec)

下面向merge表插入一条数据:

mysql> insert into payment_all values(3,'2006-3-21',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-05-01 00:00:00 | 350000.00 |
|          2 | 2007-08-15 00:00:00 | 220000.00 |
|          3 | 2006-03-21 00:00:00 | 112200.00 |
+------------+---------------------+-----------+
5 rows in set (0.00 sec)

mysql> select * from payment_2007;
+------------+---------------------+-----------+
| country_id | payment_date        | amount    |
+------------+---------------------+-----------+
|          1 | 2007-05-01 00:00:00 | 350000.00 |
|          2 | 2007-08-15 00:00:00 | 220000.00 |
|          3 | 2006-03-21 00:00:00 | 112200.00 |
+------------+---------------------+-----------+
3 rows in set (0.00 sec)

可以看到数据插在了后一个表中,这是由于之前定义的插入方式为last导致的。

2.5 TokuDB(第三方存储引擎)

TokuDB是一个高性能、支持事务处理的MySQL和MariaDB的存储引擎,具有高扩展性、高压缩率、高效的写入性能,支持大多数在线DDL操作,其它相关内容可以在其官网(http://www.tokutek.com)进行查看。

根据其性能,我们发现它特别适合以下几种场景:

  • 日志数据,因为日志数据通常插入频繁且存储量大;
  • 历史数据,通常不会有写操作,可用其高压缩性进行存储;
  • 在线DDL较频繁的场景,该引擎增加系统的可用性。

三、如何选择合适的存储引擎

根据合适的情况选择相应的引擎,有时候还可以选用多种引擎进行组合。

  • MyISAM:如果应用是以读操作和插入操作为主,只有很少的更新和删除操作,并且对事务的完整性、并发性要求不高,那么该引擎很合适。常用在web、数据仓库等环境下;
  • InnoDB:如果事务的完整性要求较高,在并发条件下要求数据的一致性,数据操作增删改查都很多,那么该引擎较合适。常用在计费系统、财务系统等对数据准确性要求比较高的系统中;
  • MEMORY:数据保存在RAM中,访问速度快,有大小限制,通常用于更新不太频繁的小表或作为中间表快速得到访问结果;
  • MERGE:用于将一系列等同的MyISAM表以逻辑方式组合在一起,并作为一个对象引用他们。可以突破对单个MyISAM表的大小限制,并且通过将不同的表分布在多个磁盘上可以有效改善MERGE表的访问效率。对诸如数据仓库等VLDB环境很适合。