MySQL 存储引擎概述

一、 MySQL 存储引擎概述##查看当前的默认存储引擎，可以使用以下命令：mysql> show variables like 'table_type';  ##MySQL 5.1版本mysql> show variables like '%storage%';   ##MySQL 5.5版本##可以通过下面两种方法查询当前数据库支持的存储引擎：mysql> SHOW ENGINES \G*************************** 1. row ***************************      Engine: MEMORY     Support: YES     Comment: Hash based, stored in memory, useful for temporary tablesTransactions: NO          XA: NO  Savepoints: NO*************************** 2. row ***************************      Engine: CSV     Support: YES     Comment: CSV storage engineTransactions: NO          XA: NO  Savepoints: NO*************************** 3. row ***************************      Engine: MRG_MYISAM     Support: YES     Comment: Collection of identical MyISAM tablesTransactions: 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 engineTransactions: NO          XA: NO  Savepoints: NO*************************** 6. row ***************************      Engine: FEDERATED     Support: NO     Comment: Federated MySQL storage engineTransactions: NULL          XA: NULL  Savepoints: NULL*************************** 7. row ***************************      Engine: ARCHIVE     Support: YES     Comment: Archive storage engineTransactions: NO          XA: NO  Savepoints: NO*************************** 8. row ***************************      Engine: InnoDB                                                          ##存储引擎名称     Support: DEFAULT                                                         ##当前是否支持（DEFAULT表示默认存储引擎）     Comment: Supports transactions, row-level locking, and foreign keys      ##注释信息Transactions: YES                                                             ##是否支持事务（InnoDB存储引擎是唯一支持事务的存储引擎）          XA: YES                                                             ##是否支持分布式事务  Savepoints: YES                                                             ##是否支持保存点*************************** 9. row ***************************      Engine: PERFORMANCE_SCHEMA     Support: YES     Comment: Performance SchemaTransactions: NO          XA: NO  Savepoints: NO9 rows in set (0.00 sec)mysql> SHOW VARIABLES LIKE 'have%';##其中Value 显示为“DISABLED”的记录表示支持该存储引擎，但是数据库启动的时候被禁用。##在创建新表的时候，可以通过增加ENGINE 关键字设置新建表的存储引擎CREATE TABLE ai ( i bigint(20) NOT NULL AUTO_INCREMENT, PRIMARY KEY (i)) ENGINE=MyISAM DEFAULT CHARSET=gbk;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 语句，将一个已经存在的表修改成其他的存储引擎。mysql> alter table ai engine = innodb;二、 各种存储引擎的特性2.1 MyISAMMyISAM 不支持事务、也不支持外键，其优势是访问的速度快，对事务完整性没有要求或者以SELECT、INSERT 为主的应用基本上都可以使用这个引擎来创建表。每个MyISAM 在磁盘上存储成3 个文件，其文件名都和表名相同，但扩展名分别是： *.frm（存储表定义）； *.MYD（MYData，存储数据）； *.MYI （MYIndex，存储索引）。数据文件和索引文件可以放置在不同的目录，平均分布IO，获得更快的速度。要指定索引文件和数据文件的路径，需要在创建表的时候通过DATA DIRECTORY 和INDEX DIRECTORY 语句指定，也就是说不同MyISAM 表的索引文件和数据文件可以放置到不同的路径下。文件路径需要是绝对路径，并且具有访问权限。MyISAM 类型的表可能会损坏，原因可能是多种多样的，损坏后的表可能不能访问，会提示需要修复或者访问后返回错误的结果。MyISAM 类型的表提供修复的工具，可以用CHECK TABLE 语句来检查MyISAM 表的健康，并用REPAIR TABLE 语句修复一个损坏的MyISAM 表。MyISAM 的表又支持3 种不同的存储格式，分别是： 静态（固定长度）表；静态表是默认的存储格式。静态表中的字段都是非变长字段，这样每个记录都是固定长度的，这种存储方式的优点是存储非常迅速，容易缓存，出现故障容易恢复；缺点是占用的空间通常比动态表多。静态表的数据在存储的时候会按照列的宽度定义补足空格，但是在应用访问的时候并不会得到这些空格，这些空格在返回给应用之前已经去掉。但是也有些需要特别注意的问题，如果需要保存的内容后面本来就带有空格，那么在返回结果的时候也会被去掉，开发人员在编写程序的时候需要特别注意，因为静态表是默认的存储格式，开发人员可能并没有意识到这一点，从而丢失了尾部的空格。 动态表；动态表是指表中包含有变长字符类型的列（如varchar、varbinary、blob、text），或者创建时明确指定了row_format=dynamic选项，那么该表就会被创建成动态存储格式。动态表中包含变长字段，记录不是固定长度的，这样存储的优点是占用的空间相对较少，但是频繁地更新删除记录会产生碎片，需要定期执行OPTIMIZE TABLE 语句或myisamchk -r 命令来改善性能，并且出现故障的时候恢复相对比较困难。从性能上来讲，静态表的处理性能会高于动态表，但是占用的存储空间也会高于动态表。 压缩表：压缩表由myisampack 工具创建，占据非常小的磁盘空间。因为每个记录是被单独压缩的，所以只有非常小的访问开支。压缩包是只读的，不支持添加或修改记录。mysql> create table t_mis1 (id int,name char(10)) engine=myisam;Query OK, 0 rows affected (0.02 sec)mysql> create table t_mis2 (id int,name varchar(10)) engine=myisam;Query OK, 0 rows affected (0.00 sec)mysql> show table status like 't_mis%' \G*************************** 1. row ***************************           Name: t_mis1         Engine: MyISAM        Version: 10     Row_format: Fixed   ##静态           Rows: 0 Avg_row_length: 0    Data_length: 0Max_data_length: 9851624184872959   Index_length: 1024      Data_free: 0 Auto_increment: NULL    Create_time: 2015-12-10 00:15:26    Update_time: 2015-12-10 00:15:26     Check_time: NULL      Collation: utf8_general_ci       Checksum: NULL Create_options:         Comment: *************************** 2. row ***************************           Name: t_mis2         Engine: MyISAM        Version: 10     Row_format: Dynamic  ##动态           Rows: 0 Avg_row_length: 0    Data_length: 0Max_data_length: 281474976710655   Index_length: 1024      Data_free: 0 Auto_increment: NULL    Create_time: 2015-12-10 00:15:32    Update_time: 2015-12-10 00:15:32     Check_time: NULL      Collation: utf8_general_ci       Checksum: NULL Create_options:         Comment: 2 rows in set (0.00 sec)##压缩表：mysql> create table t2(id int,name varchar(10)) engine=myisam;Query OK, 0 rows affected (0.00 sec)mysql> insert into t2 values(1,'a'),(2,'b'),(3,'c'),(4,'d'),(5,'e');Query OK, 5 rows affected (0.00 sec)Records: 5  Duplicates: 0  Warnings: 0mysql> insert into t2 select * from t2;       ##重复很多次Query OK, 5 rows affected (0.00 sec)Records: 5  Duplicates: 0  Warnings: 0......[root@db test]# ll t2.*-rw-rw---- 1 mysql mysql      8586 Dec 14 06:13 t2.frm-rw-rw---- 1 mysql mysql 209715200 Dec 14 06:15 t2.MYD-rw-rw---- 1 mysql mysql      1024 Dec 14 06:15 t2.MYI[root@db test]# myisampack t2Compressing t2.MYD: (10485760 records)- Calculating statistics- Compressing file90%                                           ##可以看见压缩了90%[root@db test]# ll t2.*-rw-rw---- 1 mysql mysql     8586 Dec 14 06:13 t2.frm-rw-rw---- 1 mysql mysql 20971589 Dec 14 06:15 t2.MYD-rw-rw---- 1 mysql mysql     1024 Dec 14 06:17 t2.MYImysql> flush tables;                          ##flush tables之后，表就变为只读的了。Query OK, 0 rows affected (0.00 sec)mysql> insert into t2 values(1,'a'),(2,'b'),(3,'c'),(4,'d'),(5,'e');ERROR 1036 (HY000): Table 't2' is read onlyMyISAM存储引擎使用key buffer缓存索引块，以加速MyISAM索引的读写速度。对于MyISAM表的数据块，mysql没有特别的缓存机制，完全依赖于操作系统的IO 缓存。2.2 InnoDBInnoDB 存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比MyISAM的存储引擎，InnoDB 写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。InnoDB用一块内存区做IO缓冲池，该缓存池不仅用来缓存InnoDB的索引块，而且也用来缓存InnoDB的数据块，这一点与MyISAM不同。对于InnoDB 表，自动增长列必须是索引。如果是组合索引，也必须是组合索引的第一列，但是对于MyISAM 表，自动增长列可以是组合索引的其他列，这样插入记录后，自动增长列是按照组合索引的前面几列进行排序后递增的。MySQL 支持外键的存储引擎只有InnoDB，在创建外键的时候，要求父表必须有对应的索引，子表在创建外键的时候也会自动创建对应的索引。在创建索引的时候，可以指定在删除、更新父表时，对子表进行的相应操作，包RESTRICT、CASCADE、SET NULL 和NO ACTION。其中RESTRICT 和NO ACTION 相同，是指限制在子表有关联记录的情况下父表不能更新；CASCADE 表示父表在更新或者删除时，更新或者删除子表对应记录；SET NULL 则表示父表在更新或者删除的时候，子表的对应字段被SET NULL。选择后两种方式的时候要谨慎，可能会因为错误的操作导致数据的丢失。在导入多个表的数据时，如果需要忽略表之前的导入顺序，可以暂时关闭外键的检查；同样，在执行LOAD DATA 和ALTER TABLE 操作的时候，可以通过暂时关闭外键约束来加快处理的速度，关闭的命令是“SET FOREIGN_KEY_CHECKS = 0;”，执行完成之后，通过执行“SETFOREIGN_KEY_CHECKS = 1;”语句改回原状态。对于InnoDB 类型的表，外键的信息通过使用show create table 或者show table status 命令都可以显示。mysql> show table status like 'city' \GInnoDB 存储表和索引有以下两种方式。 使用共享表空间存储，这种方式创建的表的表结构保存在.frm 文件中，数据和索引保存在innodb_data_home_dir 和innodb_data_file_path 定义的表空间中，可以是多个文件。 使用多表空间存储，这种方式创建的表的表结构仍然保存在.frm 文件中，但是每个表的数据和索引单独保存在.ibd 中。如果是个分区表，则每个分区对应单独的.ibd文件，文件名是“表名+分区名”，可以在创建分区的时候指定每个分区的数据文件的位置，以此来将表的IO 均匀分布在多个磁盘上。要使用多表空间的存储方式，需要设置参数innodb_file_per_table，并重新启动服务后才可以生效，对于新建的表按照多表空间的方式创建，已有的表仍然使用共享表空间存储。如果将已有的多表空间方式修改回共享表空间的方式，则新建表会在共享表空间中创建，但已有的多表空间的表仍然保存原来的访问方式。所以多表空间的参数生效后，只对新建的表生效。mysql> show variables like 'innodb_data%';+-----------------------+------------------------+| Variable_name         | Value                  |+-----------------------+------------------------+| innodb_data_file_path | ibdata1:12M:autoextend |    ##innodb系统表空间数据文件配置（系统表空间一旦占用就无法释放）| innodb_data_home_dir  |                        |    ##innodb系统表空间数据文件存储目录（默认在data目录）+-----------------------+------------------------+2 rows in set (0.00 sec)mysql> show variables like '%innodb_file_per_table%';+-----------------------+-------+| Variable_name         | Value |+-----------------------+-------+| innodb_file_per_table | ON    |                     ##启用多重表空间的存储方式+-----------------------+-------+1 row in set (0.00 sec)多表空间的数据文件没有大小限制，不需要设置初始大小，也不需要设置文件的最大限制、扩展大小等参数。注意：即便在多表空间的存储方式下，共享表空间仍然是必须的，InnoDB 把内部数据词典和未作日志放在这个文件中。##innodb日志文件mysql> show variables like '%innodb_log%';+-----------------------------+----------+| Variable_name               | Value    |+-----------------------------+----------+| innodb_log_buffer_size      | 8388608  || innodb_log_compressed_pages | ON       || innodb_log_file_size        | 50331648 |     ##innodb日志文件大小| innodb_log_files_in_group   | 2        |     ##innodb日志文件组数| innodb_log_group_home_dir   | ./       |     ##innodb日志文件目录+-----------------------------+----------+5 rows in set (0.01 sec)[root@CentOS 3306]# ll ib*-rw-rw----. 1 mysql mysql 12582912 12月 10 17:14 ibdata1          ##innoDB系统表空间-rw-rw----. 1 mysql mysql 50331648 12月 10 17:14 ib_logfile0      ##innodb日志文件0-rw-rw----. 1 mysql mysql 50331648 12月  7 18:09 ib_logfile1      ##innodb日志文件1[root@CentOS 3306]# cd test/                   ##test数据库[root@CentOS test]# ll t1.*-rw-rw----. 1 mysql mysql  8586 12月  8 01:14 t1.frm   ##t1表结构-rw-rw----. 1 mysql mysql 98304 12月  9 01:34 t1.ibd   ##t1表数据（有.ibd文件，表示启用了多重表空间，否则数据会保存在innoDB系统表空间里，即——ibdata1里面）innodb_autoextend_increment          ##innodb系统表空间每次扩展大小innodb_fast_shutdown                 ##这个系统参数是用来控制innoDB的关闭模式。若指定为0模式，则要等到回话关闭、所有事物结束、缓冲区的数据刷新到磁盘等，类似oracle数据库中的shutdown normal；若指定为1模式，类似oracle数据库中的shutdown immediate，也是默认设置值；若指定为2模式，类似oracle数据库中的shutdown abort。若要修改日志文件配置，那么首先检查innodb_fast_shutdown系统参数的值。若当前值为2，则必须先将其修改为1，才可以修改日志文件配置。##设置独立的 undo表空间在MySQL数据库中，回滚段默认都是保存在系统表空间内，不过从MySQL 5.6版本开始，innoDB引擎中的undo日志也可以设置独立的表空间。注意：使用独立的undo 表空间必须得在数据库创建之前指定。mysql> show variables like '%innodb_undo%';+-------------------------+-------+| Variable_name           | Value |+-------------------------+-------+| innodb_undo_directory   | .     |     ##undo 日志文件的物理文件的位置| innodb_undo_logs        | 128   |     ##undo 表空间中回滚段的数量| innodb_undo_tablespaces | 0     |     ##undo 表空间的数量，每个undo 表空间都是独立的.idb文件，因此可以理解为undo 数据文件的数量。+-------------------------+-------+3 rows in set (0.00 sec)2.3 MEMORYMEMORY 存储引擎使用存在内存中的内容来创建表。每个MEMORY 表只实际对应一个磁盘文件，格式是.frm。MEMORY 类型的表访问非常得快，因为它的数据是放在内存中的，并且默认使用HASH 索引，但是一旦服务关闭，表中的数据就会丢失掉。在启动MySQL 服务的时候使用--init-file 选项，把INSERT INTO ... SELECT 或LOAD DATA INFILE 这样的语句放入这个文件中，就可以在服务启动时从持久稳固的数据源装载表。mysql> create table t_meml (id int) engine=memory;Query OK, 0 rows affected (0.04 sec)[root@CentOS 3306]# vi my.cnf [mysqld]init-file=/mydata/data/3306/t_meml_init_file[root@CentOS 3306]# cat /mydata/data/3306/t_meml_init_fileinsert into test.t_meml select id from test.t1;[root@CentOS 3306]# ./stop.sh Enter password: [root@CentOS 3306]# ./start.sh 服务器需要足够内存来维持所有在同一时间使用的MEMORY 表，当不再需要MEMORY表的内容之时，要释放被MEMORY 表使用的内存，应该执行DELETE FROM 或TRUNCATE TABLE，或者整个地删除表（使用DROP TABLE 操作）。每个MEMORY 表中可以放置的数据量的大小，受到max_heap_table_size 系统变量的约束，这个系统变量的初始值是16MB，可以按照需要加大。此外，在定义MEMORY 表的时候，可以通过MAX_ROWS 子句指定表的最大行数。MEMORY 类型的存储引擎主要用在那些内容变化不频繁的代码表，或者作为统计操作的中间结果表，便于高效地对中间结果进行分析并得到最终的统计结果。对MEMORY 存储引擎的表进行更新操作要谨慎，因为数据并没有实际写入到磁盘中，所以一定要对下次重新启动服务后如何获得这些修改后的数据有所考虑。2.4 MERGEMERGE 存储引擎是一组MyISAM 表的组合，这些MyISAM 表必须结构完全相同，MERGE表本身并没有数据，对MERGE 类型的表可以进行查询、更新、删除的操作，这些操作实际上是对内部的实际的MyISAM 表进行的。对于MERGE 类型表的插入操作，是通过INSERT_METHOD 子句定义插入的表，可以有3 个不同的值，使用FIRST 或LAST 值使得插入操作被相应地作用在第一或最后一个表上，不定义这个子句或者定义为NO，表示不能对这个MERGE 表执行插入操作。可以对MERGE 表进行DROP 操作，这个操作只是删除MERGE 的定义，对内部的表没有任何的影响。MERGE 表在磁盘上保留两个文件，文件名以表的名字开始，一个.frm 文件存储表定义，另一个.MRG 文件包含组合表的信息，包括MERGE 表由哪些表组成、插入新的数据时的依据。可以通过修改.MRG 文件来修改MERGE 表，但是修改后要通过FLUSH TABLES 刷新。2.5 CSV存储引擎数据库中的CSV存储引擎与CSV格式之间存在着巨大的关联，实际上，CSV存储引擎正式基于CSV格式文件存储数据。mysql> create table t_csv(id int,vl varchar(20)) engine=csv;ERROR 1178 (42000): The storage engine for the table doesn't support nullable columnsmysql> create table t_csv(id int not null,vl varchar(20) not null) engine=csv;Query OK, 0 rows affected (0.01 sec)mysql> insert into t_csv values(1,'a'),(2,'b');Query OK, 2 rows affected (0.02 sec)Records: 2  Duplicates: 0  Warnings: 0mysql> select * from t_csv;+----+----+| id | vl |+----+----+|  1 | a  ||  2 | b  |+----+----+2 rows in set (0.00 sec)需要注意，CSV存储引擎所有列必须强制指定not null，CSV引擎不支持索引，不支持分区。[root@CentOS test]# ll t_csv.*-rw-rw----. 1 mysql mysql   35 12月  9 22:36 t_csv.CSM-rw-rw----. 1 mysql mysql   12 12月  9 22:36 t_csv.CSV-rw-rw----. 1 mysql mysql 8582 12月  9 22:35 t_csv.frm[root@CentOS test]# cat t_csv.CSV1,"a"2,"b"CSV引擎表也会包括一个表的结构定义文件，扩展名为.frm，此外还要创建一个扩展名为.CSV的数据文件。这个文件就是CSV格式的平面文本文件，该文件保存表中的实际数据。可以在EXCEL中直接打开、编辑。此外，还有一个同名的元信息文件，该文件的扩展名为.CSM，用来保存表的状态及表中保存的数据量。如果CSV文件中的内存损坏了，也可以用CHECK TABLE 或 REPAIR TABLE 命令进行检查和修复。CHECK 语句会检查CSV文件中分隔符是否合法，分隔列的数量与表定义中是否相同。REPAIR语句执行修复时，会尝试从当前CSV中复制合法数据，清楚不合法的数据。但要注意，修复时只要发现文件有损坏的记录行，那么之后的数据也就全部丢失了，不管中间是否有合法的数据。2.6 ARCHIVE存储引擎ARCHIVE存储引擎使用的场景恰如其名——归档，能够将大量数据压缩存储。ARCHIVE存储引擎仅能支持insert和select语句，不支持delete、replace、update语句；能够支持order by操作、blob列等常规类型，也能够使用行级锁定，但是不支持索引；ARCHIVE存储引擎就是为极少访问的归档数据准备的。mysql> create table t_arcl(id int) engine=archive;Query OK, 0 rows affected (0.01 sec)[root@CentOS test]# ll t_arcl.*-rw-rw----. 1 mysql mysql 8644 12月  9 23:03 t_arcl.ARZ-rw-rw----. 1 mysql mysql 8556 12月  9 23:03 t_arcl.frmARCHIVE存储引擎表除了拥有标准的.frm结构定义文件外，还有一个扩展名为.ARZ的数据文件。执行优化操作时可能还会出现一个扩展名为.ARN的文件。2.7 BLACKHOLE存储引擎BLACKHOLE存储引擎，这个存储引擎的功能正像它的名字那样：“黑洞”。只管入不管存的存储引擎。尽管它也是像其它存储引擎一样接受数据，但是，注意，所有插入的数据并不会保存，BLACKHOLE存储引擎表永远保持为空。mysql> create table t_bhl(id int,vl varchar(20)) engine=BLACKHOLE;Query OK, 0 rows affected (0.00 sec)mysql> insert into t_bhl values(1,'a'),(2,'b');Query OK, 2 rows affected (0.00 sec)Records: 2  Duplicates: 0  Warnings: 0mysql> select * from t_bhl;Empty set (0.00 sec)BLACKHOLE存储引擎，它压根儿就没有保存数据，BLACKHOLE存储引擎表对应的物理文件也只有.frm文件，也就是说只有表的定义。[root@CentOS test]# ll t_bhl.*-rw-rw----. 1 mysql mysql 8582 12月  9 23:10 t_bhl.frm尽管BLACKHOLE存储引擎对象不会保存任何数据，但是如果启用了binlog，那么执行的SQL语句实际上是被记录的了（也就是说能够被复制到slave端）。mysql> show master status;+------------------+----------+--------------+------------------+-------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+-------------------+| mysql-bin.000010 |     1525 |              |                  |                   |+------------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)[root@CentOS 3306]# mysqlbinlog mysql-bin.000010 | grep t_bhlcreate table t_bhl(id int,vl varchar(20)) engine=BLACKHOLEinsert into t_bhl values(1,'a'),(2,'b')BLACKHOLE对象中insert触发器仍会按照标准方式触发，不过由于BLACKHOLE对象为空，update、delete触发器实际上也没有什么意义。其他应用BLACKHOLE存储引擎的场景包括：a、验证dump文件语法。b、通过对比启用及禁用二进制日志文件时的性能，来评估二进制日志对负载的影响。c、在深入想一想，BLACKHOLE存储引擎实际上市no-op（无操作）引擎，因此可以用于发现与存储引擎无关的性能瓶颈。2.8 FEDERATED存储引擎FEDERATED存储引擎功能相当于oracle数据库中的database link。FEDERATED存储引擎默认情况下是不安装的。[root@CentOS 3306]# vi my.cnf [mysqld]federated[root@CentOS 3306]# ./stop.sh Enter password: [root@CentOS 3306]# ./start.sh mysql> SHOW ENGINES;+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         || CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         || MRG_MYISAM         | YES     | Collection of identical MyISAM 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         || FEDERATED          | YES     | Federated MySQL storage engine                                 | NO           | NO   | NO         || ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         || InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        || PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+9 rows in set (0.01 sec)三、 如何选择合适的存储引擎MyISAM：如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常适合的。MyISAM 是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。InnoDB：用于事务处理应用程序，支持外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询以外，还包括很多的更新、删除操作，那么InnoDB 存储引擎应该是比较合适的选择。InnoDB 存储引擎除了有效地降低由于删除和更新导致的锁定，还可以确保事务的完整提交（Commit）和回滚（Rollback），对于类似计费系统或者财务系统等对数据准确性要求比较高的系统，InnoDB 都是合适的选择。MEMORY：将所有数据保存在RAM 中，在需要快速定位记录和其他类似数据的环境下，可提供极快的访问。MEMORY 的缺陷是对表的大小有限制，太大的表无法CACHE 在内存中，其次是要确保表的数据可以恢复，数据库异常终止后表中的数据是可以恢复的。MEMORY 表通常用于更新不太频繁的小表，用以快速得到访问结果。MERGE：用于将一系列等同的MyISAM 表以逻辑方式组合在一起，并作为一个对象引用它们。MERGE 表的优点在于可以突破对单个MyISAM 表大小的限制，并且通过将不同的表分布在多个磁盘上，可以有效地改善MERGE 表的访问效率。这对于诸如数据仓储等VLDB环境十分适合。四、 选择合适的数据类型4.1 CHAR 与VARCHAR在MySQL 中，不同的存储引擎对CHAR 和VARCHAR 的使用原则有所不同，这里简单概括如下。 MyISAM 存储引擎：建议使用固定长度的数据列代替可变长度的数据列。 MEMORY 存储引擎：目前都使用固定长度的数据行存储，因此无论使用CHAR 或VARCHAR 列都没有关系。两者都是作为CHAR 类型处理。 InnoDB 存储引擎：建议使用VARCHAR 类型。对于InnoDB 数据表，内部的行存储格式没有区分固定长度和可变长度列（所有数据行都使用指向数据列值的头指针），因此在本质上，使用固定长度的CHAR 列不一定比使用可变长度VARCHAR 列性能要好。因而，主要的性能因素是数据行使用的存储总量。由于CHAR 平均占用的空间多于VARCHAR，因此使用VARCHAR 来最小化需要处理的数据行的存储总量和磁盘I/O 是比较好的。4.2 TEXT 与BLOB一般在保存少量字符串的时候，我们会选择CHAR 或者VARCHAR；而在保存较大文本时，通常会选择使用TEXT 或者BLOB，二者之间的主要差别是BLOB 能用来保存二进制数据，比如照片；而TEXT 只能保存字符数据，比如一篇文章或者日记。对含有TEXT 和BLOB 字段的表，如果经常做删除和修改记录的操作要定时执行OPTIMIZE TABLE 功能对表进行碎片整理。4.3 浮点数与定点数浮点数一般用于表示含有小数部分的数值。当一个字段被定义为浮点类型后，如果插入数据的精度超过该列定义的实际精度，则插入值会被四舍五入到实际定义的精度值，然后插入，四舍五入的过程不会报错。在MySQL 中float、double（或real）用来表示浮点数。定点数不同于浮点数，定点数实际上是以字符串形式存放的，所以定点数可以更加精确的保存数据。如果实际插入的数值精度大于实际定义的精度，则MySQL 会进行警告（默认的SQLMode 下），但是数据按照实际精度四舍五入后插入；如果SQLMode 是在TRADITIONAL（传统模式）下，则系统会直接报错，导致数据无法插入。在MySQL中，decima（l 或numberic）用来表示定点数。在精度要求比较高的应用中（比如货币）要使用定点数而不是浮点数来保存数据。注意：在今后关于浮点数和定点数的应用中，用户要考虑到以下几个原则： 浮点数存在误差问题； 对货币等对精度敏感的数据，应该用定点数表示或存储； 在编程中，如果用到浮点数，要特别注意误差问题，并尽量避免做浮点数比较； 要注意浮点数中一些特殊值的处理。4.4 日期类型选择 根据实际需要选择能够满足应用的最小存储的日期类型。如果应用只需要记录“年份”，那么用1 个字节来存储的YEAR 类型完全可以满足，而不需要用4 个字节来存储的DATE 类型。这样不仅仅能节约存储，更能够提高表的操作效率。 如果要记录年月日时分秒，并且记录的年份比较久远，那么最好使用DATETIME，而不要使用TIMESTAMP。因为TIMESTAMP 表示的日期范围比DATETIME 要短得多。 如果记录的日期需要让不同时区的用户使用，那么最好使用TIMESTAMP，因为日期类型中只有它能够和实际时区相对应。