mysql常用存储引擎INNODB-MYISAM(12)

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

show engines;命令可以查看mysql当前支持的引擎，再次截取了myisam引擎和innodb引擎作为对比，
可以看到myisam引擎不支持事务，XA事务，也不支持保存点，而这些innodb引擎都支持。

--我们提前创建两个相同的表格，分别采用innodb引擎和myisam引擎，表格结构如下：
--并且插入相同的数据，有1572864条记录的数据
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(30)      | NO   |     | abcd    |                |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.03 sec)

1. 存储结构差异
   可以看到innodb本地只有两个文件，一个表格结构文件(.frm)，另外一个数据文件(.ibd)，myisam有三个文件，一个.frm表格文件，.MYD数据文件, *.MYI索引文件。从存储空间来看，innodb引擎需要更大的磁盘空间，myisam引擎所需要的空间较小。

--依赖INNODB引擎创建的test_innodb表格
-rw-r----- 1 mysql mysql     8586 Aug 15 09:27 test_innodb.frm
-rw-r----- 1 mysql mysql 54525952 Aug 15 09:27 test_innodb.ibd

--依赖MYISAM引擎创建的test_myisam表格
-rw-r----- 1 mysql mysql     8586 Aug 15 09:29 test_myisam.frm
-rw-r----- 1 mysql mysql 31457280 Aug 15 09:29 test_myisam.MYD
-rw-r----- 1 mysql mysql 16167936 Aug 15 09:29 test_myisam.MYI

2.事务处理
myisam类型的表强调的是性能，其执行数度比innodb类型更快，但是不支持外键、不提供事务支持。
innodb提供事务支持事务，外部键（foreign key）等高级数据库功能。

select、update、insert、delete操作
如果执行大量的select以及insert，myisam是更好的选择。
如果你的数据执行大量的update，出于性能方面的考虑，应该使用innodb表。

--将test_innodb表格中的记录插入到myisam引擎的表格中，可以看到6291456条记录，
--花费时间42.61秒
mysql> insert into test_myisam(name) (select name from test_innodb);      
Query OK, 6291456 rows affected (42.61 sec)
Records: 6291456  Duplicates: 0  Warnings: 0

--将test_innodb表格中的记录插入到innodb引擎的表格中，可以看到6291456条记录，
--花费时间64.12秒
mysql> insert into test_innodb_2(name) (select name from test_innodb);
Query OK, 6291456 rows affected (1 min 4.12 sec)
Records: 6291456  Duplicates: 0  Warnings: 0

--下面测试select查询，两个表格一样的数据，在name字段上面没有任何索引
mysql> select * from test_innodb where name="daebaaaaaa";
+-------+------------+
| id    | name       |
+-------+------------+
| 20284 | daebaaaaaa |
+-------+------------+
1 row in set (0.38 sec)

mysql> select * from test_myisam where name="daebaaaaaa";
+-------+------------+
| id    | name       |
+-------+------------+
| 20284 | daebaaaaaa |
+-------+------------+
1 row in set (0.17 sec)

update以及delete操作，读者可以自行测试一下性能。

delete from table时，innodb不会重新建立表，而是一行一行的删除。而myisam则是重新建立表。在innodb上如果要清空保存有大量数据的表，最好使用truncate table这个命令。

auto_increment
myisam：可以和其他字段一起建立联合索引。引擎的自动增长列必须是索引，如果是组合索引，自动增长可以不是第一列，他可以根据前面几列进行排序后递增。
innodb中必须包含只有该字段的索引。引擎的自动增长列必须是索引，如果是组合索引也必须是组合索引的第一列。

3.表的具体行数
myisam：保存有表的总行数，如果select count(*) from table;会直接取出该值。
innodb：没有保存表的总行数，如果使用select count(*) from table；就会遍历整个表，消耗相当大，但是在加了where后，myisam和innodb处理的方式都一样。

4.全文索引
myisam：支持 FULLTEXT类型的全文索引，不支持中文。
innodb：不支持FULLTEXT类型的全文索引。

5.表锁差异
myisam：只支持表级锁，只支持表级锁，用户在操作myisam表时，select，update，delete，insert语句都会给表自动加锁。
innodb：支持事务和行级锁，是innodb的最大特色。行锁大幅度提高了多用户并发操作的新能。但是innodb的行锁也不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，innodb表同样会锁全表， 例如update table set num=1 where name like “%aaa%”

一般来说：
myisam适合：
(1)做很多count 的计算；
(2)插入不频繁，查询非常频繁；
(3)没有事务。
innodb适合：
(1)可靠性要求比较高，或者要求事务；
(2)表更新和查询都相当的频繁，并且表锁定的机会比较大的情况。