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

柔情只为你懂 2022-05-16 03:27 251阅读 0赞

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引擎都支持。

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)表更新和查询都相当的频繁，并且表锁定的机会比较大的情况。