MySQL存储引擎比较

什么是存储引擎呢？

存储引擎说白了就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。因为在关系数据库中数据的存储是以表的形式存储的，所以存储引擎也可以称为表类型（即存储和操作此表的类型）。
在Oracle 和SQL Server等数据库中只有一种存储引擎，所有数据存储管理机制都是一样的。 而MySql数据库提供了多种存储引擎。用户可以根据不同的需求为数据表选择不同的存储引擎，用户也可以根据自己的需要编写自己的存储引擎。

MySql中有哪些存储引擎？
    1 MyISAM：

可以分为静态MyISAM、动态MyISAM 和压缩MyISAM三种：

    静态MyISAM：如果数据表中的各数据列的长度都是预先固定好的，服务器将自动选择这种表类型。因为数据表中每一条记录所占用的空间都是一样的，所以这种表存取和更新的效率非常高。当数据受损时，恢复工作也比较容易做。
    动态MyISAM：如果数据表中出现varchar、xxxtext或xxxBLOB字段时，服务器将自动选择这种表类型。相对于静态MyISAM，这种表存储空间比较小，但由于每条记录的长度不一，所以多次修改数据后，数据表中的数据就可能离散的存储在内存中，进而导致 执行效率下降 。同时，内存中也可能会出现很多碎片。因此，这种类型的表要经常用optimize table 命令或优化工具来进行碎片整理。
    压缩MyISAM：以上说到的两种类型的表都可以用myisamchk工具压缩。这种类型的表进一步减小了占用的存储，但是这种表压缩之后不能再被修改。另外，因为是压缩数据，所以这种表在读取的时候要先实行解压缩。
    但是，不管是何种MyISAM表，目前它都不支持事务，行级锁和外键约束的功能。
2 MyISAM Merge引擎：

是MyISAM类型的一种变种。合并表是将几个相同的MyISAM表合并为一个虚表。常应用于日志和数据仓库。
3 InnoDB： InnoDB表类型可以看作是对MyISAM的进一步更新产品，它提供了事务、行级锁机制和外键约束的功能。
4 memory(heap)： 这种类型的数据表只存在于内存中。它使用散列索引，所以数据的存取速度非常快。 因为是存在于内存中，所以这种类型常应用于临时表中。
5 archive： 这种类型只支持select 和 insert语句，而且不支持索引。常应用于日志记录和聚合分析方面。
当然MySql支持的表类型不止上面几种。

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

查看数据库支持的存储引擎

mysql>  SHOW ENGINES  \G

*************************** 1. row ***************************

            Engine: FEDERATED

              Support: NO

      Comment: Federated MySQL storage engine

  Transactions: NULL

              XA: NULL

  Savepoints: NULL

*************************** 2. row ***************************

      Engine: MRG_MYISAM

      Support: YES

      Comment: Collection of identical MyISAM tables

Transactions: NO

                XA: NO

  Savepoints: NO

*************************** 3. row ***************************

          Engine: MyISAM

        Support: YES

      Comment: MyISAM storage engine

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: MEMORY

        Support: YES

      Comment: Hash based, stored in memory, useful for temporary tables

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: InnoDB

        Support: DEFAULT

      Comment: Supports transactions, row-level locking, and foreign keys

Transactions: YES

              XA: YES

  Savepoints: YES

support值为yes表示支持的可以使用的存储引擎，默认的存储引擎为DEFAULT,

Transactions表示是否支持事物

SHOW CREATE TABLE  staff \G

*************************** 1. row ***************************

        Table: staff

Create Table: CREATE TABLE `staff` (

  `staff_id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,

  `first_name` varchar(45) NOT NULL,

  `last_name` varchar(45) NOT NULL,

  `address_id` smallint(5) unsigned NOT NULL,

  `picture` blob,

  `email` varchar(50) DEFAULT NULL,

  `store_id` tinyint(3) unsigned NOT NULL,

  `active` tinyint(1) NOT NULL DEFAULT '1',

  `username` varchar(16) NOT NULL,

  `password` varchar(40) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,

  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (`staff_id`),

  KEY `idx_fk_store_id` (`store_id`),

  KEY `idx_fk_address_id` (`address_id`)

) ENGINE= InnoDB  AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

SHOW TABLE STATUS LIKE  'staff'\G

*************************** 1. row ***************************

              Name: staff

              Engine:  InnoDB

            Version: 10

      Row_format: Compact

                Rows: 1

  Avg_row_length: 65536

      Data_length: 65536

Max_data_length: 0

      Index_length: 32768

            Data_free: 0

  Auto_increment: 3

        Create_time: 2012-12-03 22:32:32

      Update_time: NULL

        Check_time: NULL

          Collation: utf8_general_ci

        Checksum: NULL

  Create_options: 

        Comment:

设置和修改存储引擎

Alter table  tableName  engine =engineName

ALTER TABLE emp ENGINE=MYISAM;

如果表很大，更改存储引擎将是一个很好事的工作，这根词牌IO能力有关，并且会产生表级锁，表将不能正常的读写。

MyISM

不支持事物，不支持外键，优势访问速度快，对事物完整性没有要求或者select、insert为主的应用基本上都可以使用MyISAM来创建表。

每个MyISAM在词牌上存储三个文件，其文件名和表名相同，扩展名不同：

.frm存储表定义

MYD存储数据

MYI存储索引

数据文件和索引文件可以放在不用的目录，平局分布IO，获得更快的速度。要制定索引文件和数据文件的路径，需要在创建表的时候通过date directory和index directory语句指定，文件路径需要绝对路径，并具有访问权限。

CREATE TABLE  myisam_test1(

aa INT,

name  CHAR(10))  ENGINE= MYISAM,

DATA DIRECTORY  = '/var/lib/myisam_data',

INDEX DIRECTORY  =  '/var/lib/myisam_index';

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

静态表的数据在存储的时候会按照例的宽度定义补足空格，但是在应用访问的时候并不会得到这些空格。

如果需要保存的内容后面本来就带有空格，那么在返回结果的时候也会被去掉，这是char数据类型的特性

INSERT INTO myisam_test1(name) VALUES('abcde'),('abcde   '),('   abced'),('abcde   ');

SELECT namebb,LENGTH(name) FROM myisam_test1;

+--------+--------------+

| NAME   | LENGTH(NAME) |

+--------+--------------+

| abcde   |             5 |

| abcde   |             5 |

|   abced |             7 |

| abcde   |             5 |

+---------+--------------+

InnoDB

        事务型数据库首选引擎，支持ACID事务，支持行级锁。InnoDB是为处理巨大数据量时的最大性能设计。InnoDB存储引擎完全与MySQL服务器整合，InnoDB存储引擎为主内存中缓存数据和索引而维持它自己的缓冲池。InnoDB存储它的表&索引在一个表空间中，表空间可以包含数个文件。这与MyISAM表不同，比如在MyISAM表中每个表被存在分离文件中。InnoDB表可以是任何尺寸，即使在文件尺寸呗限制为2GB的操作系统中。InnoDB默认地被包含在MySQL二进制分发中。

    InnoDB给MySQL提供了具有事务（transaction）、回滚（rollback）和崩溃修复能力（crash recovery capabilitie）、多版本并发控制（mulit-sersioned concurrcency contorl）的事务安全（transaction-safe（compliant）型表。InnoDB提供了行级锁(locking on row level)，提供与Oracle类似的不加锁读取(non-locking read in SELECTs)。InnoDB锁定在行级并且也在SELECT语句提供一个Oracle风格一致的非锁定读。这些特色增加了多用户部署和性能。没有在InnoDB中扩大锁定的需要，因为在InnoDB中行级锁适合非常小的空间。InnoDB也支持FOREIGN KEY强制。在SQL查询中，你可以自由地将InnoDB类型的表与其它MySQL的表的类型混合起来，甚至在同一个查询中也可以混合。这些特性均提高了多用户并发操作的性能表现。在InnoDB表中不需要扩大锁定（lock escalation），因为InnoDB的行级锁定(row level locks)适合非常小的空间。InnoDB是MySQL上第一个提供外键约束(FOREIGN KEY constraints)的表引擎。

1.auto increment

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

CREATE TABLE autoincre_demo

(i  SMALLINT NOT NULL AUTO_INCREMENT,

NAME  VARCHAR(10) , PRIMARY KEY (i)

)  ENGINE= INNODB;

INSERT  INTO autoincre_demo  VALUES(1,'1'),(0,'2'),(NULL,'3');

SELECT *  FROM  autoincre_demo;

+---+------+

| i        | NAME |

+---+------+

| 1        | 1     |

| 2        | 2     |

| 3        | 3     |

+---+------+

可以使用LAST_INSERT_ID()查询当前线程后自动插入记录使用的值(注意： 是线程自动插入使用的值，而不是手动插入)。如果一次插入了多条记录，那么返回的是第一条记录使用的自动增值

INSERT INTO autoincre_demo  VALUES(4,'4');

SELECT LAST_INSERT_ID();

+------------------+

| LAST_INSERT_ID() |

+------------------+

|                               2 |

+------------------+

INSERT INTO autoincre_demo(name)  VALUES('5'),('6'),('7');

SELECT LAST_INSERT_ID();

+------------------+

| LAST_INSERT_ID() |

+------------------+

|                               5 |

+------------------+

1 row in set (0.00 sec)

这个是系统自动插入的值，手动插入是并没有制定第一例的值。

InnoDB表自动增长列必须是索引。如何是组合索引，也必须是组合索引的第一列，但是对于MyISAM表，自动增长列可以是组合索引的其他列

CREATE TABLE autoincre_innodb(

d1  SMALLINT NOT NULL AUTO_INCREMENT,

d2  SMALLINT NOT NULL,

NAME  VARCHAR(10),

INDEX( d2,d1)

) ENGINE= INNODB;

CREATE TABLE  autoincre_innodb(

d1  SMALLINT NOT NULL AUTO_INCREMENT,

d2  SMALLINT NOT NULL,

NAME  VARCHAR(10),

INDEX( d1,d2)

) ENGINE= INNODB;

对于MyISAM引擎，前面的第一个语句是可以创建成功的。对于MyISAM表插入一些记录后，可以发现紫自动增长例是按照组合索引的第一例d2进行排序后递增的。

CREATE TABLE autoincre_myisam(

d1  SMALLINT NOT NULL AUTO_INCREMENT,

d2  SMALLINT NOT NULL,

NAME  VARCHAR(10),

INDEX(d2,d1)

) ENGINE=MYISAM;

INSERT INTO autoincre_myisam(d2,NAME)

VALUES(2,'2'),(3,'3'),(4,'4'),(2,'2'),(3,'3'),(4,'4');

+----+----+------+

| d1        | d2 | NAME |

+----+----+------+

|   1        |   2   | 2     |

|   1         |   3   | 3     |

|   1         |   4   | 4     |

|   2        |   2   | 2     |

|   2         |   3   | 3     |

|   2         |   4   | 4     |

+----+----+------+

2.外键约束

支持外键约束。要求父表必须有对应的索引，子表在创建外键的时候也会自动创建对应的索引。

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 AUTO_INCREMENT=110;

ON UPDATE CURRENT_TIMESTAMP表示更新记录时，last_update地段也会自动更新为当前时间。

CREATE TABLE city(

city_id  SMALLINT AUTO_INCREMENT,

NAME  VARCHAR(50)  NOT NULL,

country_id  SMALLINT UNSIGNED NOT NULL,

PRIMARY KEY  (city_id),

CONSTRAINT fk_city_country  FOREIGN KEY (country_id)     REFERENCES country (country_id)  ON DELETE RESTRICT ON UPDATE CASCADE

)  ENGINE= INNODB;

SHOW CREATE TABLE  city \G

*************************** 1. row ***************************

        Table: city

Create Table: CREATE TABLE `city` (

  `city_id` smallint(6) NOT NULL AUTO_INCREMENT,

  `NAME` varchar(50) NOT NULL,

  `country_id` smallint(5) unsigned NOT NULL,

  PRIMARY KEY (`city_id`),

    KEY `fk_city_country` (`country_id`),

  CONSTRAINT `fk_city_country` FOREIGN KEY (`country_id`) REFERENCES `country` (`country_id`) ON UPDATE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8

可以看到外键自动创建。

在创建所索引的时候，可以指定在删除、更新父表时，对子表进行相应的操作，包裹restrict、cascade、set null、no action。其中restrict和no action形同，是指限制在子表有相关联记录的情况下不能更新；CASCADE表示父表在更新或者删除时，更新或者删除子表对应的记录；

SET NULL表示父表在更新或者删除的时候，子表的对应字段呗SET NULL。

INSERT INTO country   SELECT * FROM sakila.country;

INSERT INTO city(city_id,NAME,country_id)   SELECT city_id,city,country_id FROM sakila.city;

导入一些数据

SELECT * FROM country WHERE country_id=1;

+------------+-------------+---------------------+

| country_id | country     | last_update         |

+------------+-------------+---------------------+

|           1 | Afghanistan | 2006-02-15 04:44:00 |

+------------+-------------+---------------------+

SELECT * FROM city WHERE country_id=1;

+---------+-------+------------+

| city_id | NAME   | country_id |

+---------+-------+------------+

|     251 | Kabul |           1 |

+---------+-------+------------+

DELETE FROM country WHERE country_id=1;

ERROR 1451 (23000): Cannot DELETE OR UPDATE a parent ROW: a FOREIGN KEY CONSTRAINT fails (`grzl`.`city`, CONSTRAINT `fk_city_country` FOREIGN KEY (`country_id`) REFERENCES `country` (`country_id`) ON UPDATE CASCADE)

可以看到删除country表中country_id=1的记录时禁止删除的

UPDATE country  SET country_id=1000  WHERE country_id=1;

SELECT *  FROM country  WHERE country='AFghanistan';

+------------+-------------+---------------------+

| country_id   | country       | last_update             |

+------------+-------------+---------------------+

|       1000        | Afghanistan | 2012-12-11 15:20:00 |

+------------+-------------+---------------------+

SELECT *  FROM city  WHERE city_id=251;

+---------+-------+------------+

| city_id        | NAME | country_id |

+---------+-------+------------+

|     251        | Kabul |       1000 |

+---------+-------+------------+

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

注意：当进行ALTER TABLE或者向父表中导入数据时，可以使用SET FOREIGN_KEY_CHECKS=0关闭外键约束来加快处理速度，执行完毕后，再通过SET FOREIGN_KEY_CHECKS=1改回原状态。

3.存储方式

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

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

使用多表空间存储，这种方式创建的表结构依然存储在.frm文件中，但是每个表的数据和索引单独保存在.ibd中。

innodb_file_per_table 这个参数控制是使用共享表空间还是多表空间存储。当为ON时使用多表空间存储，为OFF时使用共享表空间存储。需要重新启动数据库后才可以生效，并且只对新建的表生效，原表仍然使用更改前的存储方式。

多表空间对数据文件没有大小限制，不需要设置初始大小。优点是方便维护，不至于使单个文件过大。但是直接复制.ibd文件是不行的，因为没有共享表空间的数据字典信息，直接复制.ibd 文件和 .frm文件恢复时是不能被目标库使用。如果要将进行备份和恢复操作，需要使用mysqldump 和 mysqlimport工具。

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

MEMORY

MEMORY存储引擎使用在内存中的内容来创建表。每个MEMORY表只实际对应一个磁盘文件，格式是.frm。MEMORY类型的访问非常快，因为她的数据时存放在内存中的，并且默认使用HASH索引，但是一旦服务器关闭，表中的数据就会丢失。给MEMORY表创建索引的时候，默认使用HASH索引。

CREATE TABLE test_memory(

id INT,

NAME VARCHAR(20)

) ENGINE=MEMORY;

INSERT INTO test_memory VALUES(1,'a'),(2,'b'),(3,'c'),(4,'d'),(5,'e');

CREATE INDEX ind_test_memory_id ON test_memory(id);

SHOW INDEX FROM test_memory \G

*************************** 1. row ***************************

        Table: test_memory

    Non_unique: 1

      Key_name: ind_test_memory_id

  Seq_in_index: 1

  Column_name: id

    Collation: NULL

  Cardinality: 2

      Sub_part: NULL

        Packed: NULL

          Null: YES

    Index_type: HASH

      Comment: 

Index_comment:

每个MEMORY表中可以放置的数据量的大小，收到max_heap_table_siaze系统变量约束，这个厨师值是16M。此外，在定义MEMROY表打的时候，可以通过MAX_ROWS子名指定表的最大行数（不准）。

ALTER TABLE test_memory MAX_ROWS=10;

SHOW CREATE TABLE test_memory \G

*************************** 1. row ***************************

            Table: test_memory

Create Table: CREATE TABLE `test_memory` (

  `id` int(11) DEFAULT NULL,

  `name` varchar(20) DEFAULT NULL,

  KEY `ind_test_memory_id` (`id`)

) ENGINE=MEMORY DEFAULT CHARSET=utf8 MAX_ROWS=10

连续执行下面语句四次，最后一次报错

INSERT INTO test_memory VALUES(1,'a'),(2,'b'),(3,'c'),(4,'d'),(5,'e');

ERROR 1114 (HY000): The TABLE 'test_memory' IS FULL

SELECT COUNT(1) FROM test_memory;

+----------+

| COUNT(1) |

+----------+

|       20 |

+----------+

MEMORY类型的存储引擎主要用于哪些内容变化不频繁的代码表，或者作为统计操作的中间结果表。对MEMORY存储引擎的表进行更新操作要谨慎，因为数据并没有实际写入磁盘中。

如何选择合适的存储引擎

MyISAM：如果应用是以读和插入操作为主，并且对事务完整性、并发性要求不高，选择MyISAM不错 

InnoDB：如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，有大量的增删改查操作，支持外键使用InnoDB比较合适。对于类似计费或者财务系统等对数据准确性要求比较高的系统，适合选择InnoDB。

MEMORY：将所有数据保存在RAM中，对数据读取提供了极快的访问速度。 MEMORY是对表的大小有限制，数据不能持久化。MEMORY表通常用于更新不太频繁的小表。