MySQL存储引擎

一、Mysql数据库的体系结构

图中需要注意的是存储引擎是基于表的，不是基于数据库的。

二、存储引擎

下面只介绍几种常见的存储类型

1、InnoDB

​ InnoDB存储引擎支持事务，其设计目标是面向在线事务处理（OLTP）的应用，起特点是行锁设计、支持外键、并支持类似于Oracle的非所定读，即默认读取操作不会产生锁。从MySQL数据库5.5.8版本开始，InnoDB存储引擎是默认的存储引擎。

​ InnoDB通过使用多版本并发控制（MVVM）来获得高并发、并且实现了标准的4种隔离级别，默认为REPEATABLE级别，同时，使用一种被称为next-key-locking的策略来避免幻读的现象。除此之外，InnoDB存储引擎还提供了插入缓冲，二次写，自适应哈希索引，预读等高性能和高可用功能。

InnoDB存储引擎4大特性：插入缓冲，二次写，自适应哈希索引，预读

​ 对于表中数据的存储，InnoDB存储引擎采用了聚集的方式，因此每张表的存储都是按主键的顺序进行存放。如果没有显示的声明主键，InnoDB会为每一行生成一个6字节的ROWID，并以此作为主键。

InnoDB为什么推荐使用自增ID作为主键？

​ 自增ID可以保证每次插入时B+索引是从右边扩展的，可以避免B+树频繁的合并和分裂（对比UUID来说）。如果使用字符串主键和随机主键，会使得数据随机插入，效率比较差。

InnoDB的B+树主键索引的叶子节点就是数据文件，辅助索引的叶子节点是主键的值。

2、MyISAM

MyISAM存储引擎不支持事务，表锁设计，支持全文索引，主要面向一些在线分析处理（OLAP）的数据库应用。在MySQL5.5.8之前MyISAM存储引擎是默认的存储引擎。MyISAM存储引擎的另一个与众不同的地方是它的缓冲池只缓存索引文件，不缓存数据文件。MyISAM存储引擎由MYD和MYI组成，MYD用来存放数据文件，MYI用来存放索引文件。

​ 在MySQL5.0之前，默认的MyISAM支持的表大小为4G，从5.0开始，MyISAM默认支持256TB的单表数据。

MyISAM的B+树主键索引和辅助索引的叶子节点都是数据文件的地址指针。

3、Memory

​ Memory存储引擎（之前叫做HEAP存储引擎）将表中的数据存放在内存中，如果数据库重启或者发生崩溃，表中的数据将会丢失，它非常适合于存储临时数据的临时表以及数据仓库中的维度表。Memory存储引擎默认使用哈希索引，而不是我们熟悉的B+数索引。

​ 虽然Memory存储引擎速度非常快，但在使用上还是有一定的限制，比如：只支持表锁，并发性能差，并且不支持TEXT和BLOB列类型。最重要的是，存储变长类型如varchar时是按照定长字段char类型进行的，因此会浪费内存。

三、InnoDB和MyISAM的对比

	InnoDB	MyISAM
事务	支持	不支持
外键	支持	不支持
索引	聚集索引	非聚集索引
全文索引	InnoDB1.2.x之后支持，1.2.x之前不支持，mysql5.5.8-5.7之间都不支持全文索引，mysql5.7开始支持全文索引	支持
锁	表、行（默认行锁）	表锁
主键	必须要有（没定义的话就用ROWID）	可以没有

InnoDB存储文件有frm、ibd，而MyISAM是frm、MYD、MYI

InnoDB: frm是表定义文件，ibd是数据文件

MyISAM: frm是表定义文件，MYD是数据文件，MYI是索引文件

InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快（注意不能加where条件）

那为什么InnoDB没有这个变量呢？

​ 因为InnoDB的事务特性，在同一时刻表中的行数对于不同的事务而言是不一样的，因此count统计会计算对于当前事务而言可以统计到的行数，而不是将总行数存储起来方便快速查询。

InnoDB的行锁实现在索引上的，而不是锁在物理记录上。如果访问没有走索引，也就无法使用行锁，将会退化成表锁。

t_user(uid,uname,age,sex) innodb;  uid PK 无其它索引
update t_user set age = 10 where uid = 1; 命中索引，行锁
update t_user set age = 10 where uid != 1; 未命中索引，表锁
update t_user set age = 10 where uname = "zhangsan"; 无索引，表锁

四、如何选择用哪种存储引擎

​ 1、看是否支持事务，如果需要支持事务就用InnoDB，不需要的话可以考虑使用MyISAM；

​ 2、如果表中大多数都只是读操作，可以考虑使用MyISAM，如果写和读差不多或者写多请使用InnoDB

​ 3、系统崩溃后，MyISAM恢复起来更加困难，能接受或者保证系统不崩溃可以考虑使用MyISAM

综上：可以看出不知道使用哪种存储引擎请使用InnoDB，因为使用InnoDB存储引擎总不会错，而且MySQL5.5.8就默认使用InnoDB存储引擎了。