MySQL中InnoDB引擎和MyISAM引擎详解

 

目录

 

InnoDB引擎

MyISAM存储引擎

---

InnoDB引擎

InnoDB是MySQL的默认事务型引擎，它被设计用来处理大量的短期事务短期事务大部分是正常提交的，很少会被回滚。它的性能和自动崩溃恢复特性，使得它在非事务型存储的需求中也很流行。MySQL5.1中一定要使用InnoDB plugin，5.1.默认还是集成旧的InnoDB引擎，知道Oracle收购了Sun公司后发布的MySQL5.5中才彻底使用InnoDB plugin代替了旧版本的InnoDB。InnoDB支持一些新特性，例如利用排序创建索引、删除或者增加索引时不需要复制全表数据、新的支持压缩的存储格式、新的大型列值如BLOB的存储方式，以及文件格式管理。

 

InnoDB的数据存储在表空间中，表空间是由InnoDB管理的一个黑盒子，由一系列的数据文件组成。InnoDB可以将每个表的数据和索引存放在单独的文件中。InnoDB采用MVCC来支持高并发，并且实现四个标准的隔离级别。默认的隔离级别是可重复读，并且通过间隙锁（next-key locking）策略防止幻读的出现。间隙锁使得InnoDB不仅仅锁定查询涉及的行，还会对索引中的间隙进行锁定，以防止幻影行的插入。

 

InnoDB表是基于聚簇索引建立的，InnoDB的索引结构和MySQL的其他引擎，聚簇索引对主键的查询有很高的性能。不过它的二级索引(非主键索引)中必须包含主键列，如果主键列很大的话，其他索引也会很大。因此，若表上的索引较多的时候，主键应尽可能的小。

 

InnoDB的二级索引

二级索引

 

InnoDB内部做了很多优化，包括从磁盘读取数据采用的可预测性预读，能够自动在内存中创建hash索引来加速读操作的自适应哈希索引，以及能够加速插入操作的插入缓冲区。

拓展方向：参开官方手册的“InnoDB事务模型和锁”

1、主键很小的意思是什么？

2、间隙锁是如何解决幻读问题的？

3、什么是热备份？

4、间隙锁和MVCC是什么关系？

5、MVCC的工作原理是什么？

 

MyISAM存储引擎

在MySQL5.1及之前的版本，MyISAM是默认的存储引擎。MyISAM提供了大量的特性，包括全文索引、压缩、空间函数（GIS）等，但不支持事务和行级锁，更不用说MVCC了。一个相对于InnoDB引擎的缺陷是崩溃后无法安全恢复。尽管有这么多的缺点，但是也有它适用的场景，它的开销比较低，而且主要还查询和插入操作，或者表比较小、可以忍受修复操作，一般是日志型的应用，可以使用MyISAM。但是建议还是使用InnoDB引擎。

 

MyISAM特性

存储

         MyISAM会将表存储在两个文件中：数据文件和索引文件，分别以.MYD和.MYI为拓展名。MyISAM可以包含动态或者静态行。MyISAM会更根据表的定义来采用哪种行。

加锁和并发

         对整张表加锁，而不是针对行。读取时会对需要读到的所有表加共享锁，写入时则对表加入排他锁。

修复

         修复和崩溃恢复是不同的概念，执行表的修复可能导致一些数据丢失，而且修复操作是非常慢的。可以通过CHECK TABLE mytable检查表的错误，如果有错误可以通过REPAIR TABLE mytable 进行修复。

索引特性

         即使是BLOB和TEXT等长字段，可以基于前500个字符创建索引，同时也支持全文索引，全文索引时一种基于分词的索引。

查看引擎

     SHOW TABLE STATUS LIKE mytable ;

修改引擎

1、最简单的方法就是使用ALTER TABLE语句。例如将mytable的引擎修改为InnoDB

ALTER TABLE mytable ENGINE =InnoDB；

这个语法适合于任何存储引擎，但是会执行很长的时间。MySQL会执行将数据从原表

复制到一张新的表上，在复制期间会消耗系统所有的I/O能力，同时会在原表上加上读锁。

2、采用导入和导出的方式，使用mysqldump工具将数据导出到文件，然后修改文件中CREATE TABLE语句的存储引擎选项，同时注意修改表名。

 

如果英语不错的话可以去MySQL官网深入了解引擎相关的特性：https://www.mysql.com/。