MySQL的存储引擎MEMORY_database

MySQL的存储引擎主要有四类:MyISAM、InnoDB、MEMORY和Archive。

MyISAM在MySQL 5.5之前一直是默认的存储引擎,不过在近些年来MySQL的发展下,InnoDB逐渐替代了MyISAM存储引擎,变为了默认的存储引擎。



MyISAM和InnoDB对比

MySQL的锁机制相对于其它数据库产品而言比较简单,可以分为3类:
1、表级锁:开销小,加锁快,不会出现死锁,锁粒度大(整张表),并发度低,发生锁竞争概率大
2、行级锁:开销大,加锁慢,会出现死锁,锁粒度最小(一行数据),并发度高,发生锁竞争概率小
3、页面锁:开销、加锁速度、锁粒度、并发度都介于表级锁和行级锁之间,会出现死锁。

很难说那种锁更好,只能说就具体应用程序的特点说哪种锁更合适。对于查询操作远大于修改、插入操作的表而言,采用表级锁更加合适。行级锁更加适合有大量按索引条件查询并发更新少量不同数据,同时又有并发查询的应用。

MyISAM

只支持表锁,有两种模式:表共享读锁和表独占写锁。

对MyISAM的读操作,不会阻塞其它连接对同一张表的读请求,只会阻塞写请求。只有对MyISAM的写操作,才会阻塞其它连接对这张表的读和写操作

MyISAM会自动加锁解锁,在用户执行SELECT语句时,会自动给整张表加上读锁,在执行UPDATE、INSERT、DELETE操作时会自动加上写锁。

在默认情况下,如果同一时间有一个连接需要进行读操作,另外一个连接需要进行写操作,那么MyISAM会优先让写操作的连接获取到写锁,这也是MyISAM表不适合有大量写入操作和读操作应用的缘由。

InnoDB

支持表级锁和行级锁,实现了两种类型的行锁:共享锁、排他锁。
共享锁:允许一个事务去读一行,阻止其它事务获取相同数据集的排他锁。
排他锁:允许获得排他锁的事务更新数据,阻止其他事务取得相同数据集的共享读锁和排他写锁。

此外,为了允许表级锁和行级锁的共存,实现多粒度的锁,InnoDB还实现了两种内部使用的意向锁(属于表锁):
意向共享锁:当一个事务想要给数据行加上行共享锁的时候,必须先取得该表的意向共享锁。
意向排他锁:当一个事务想要给数据行加上行排他锁的时候,必须先取得该表的意向排他锁。

意向锁是InnoDB存储引擎自动加上的,无需SQL语句的控制。

InnoDB的行锁是通过给索引上的索引项加锁来实现的,如果没有索引,InnoDB会通过隐藏的聚簇索引来对记录进行加锁。这也就意味着:如果不通过索引条件检索数据,那么InnoDB将会对表中所有记录加锁,其效果等同于表锁。
 


事务

MyISAM

不支持事务,它强调的是高性能的查询适合读多写少、原子性要求低的情形。

InnoDB

提供了较为完善的事务支持,一共支持4个等级的事务隔离级别:读未提交(ISOLATION_READ_UNCOMMITTED)、读已提交(ISOLATION_READ_COMMITTED)、可重复读(ISOLATION_REPEATABLE_READ)、串行化(ISOLATION_SERIALIZABLE)。

  1. 读未提交:最低的隔离级别,允许读取未提交的事务变更,会导致脏读、不可重复读和幻读。
  2. 读已提交:是MySQL默认的隔离级别,允许读取事务提交后的结果,但是依然可能发生不可重复读和幻读问题。
  3. 可重复读:对同一字段多次读取都是一致的,但是由于Inno DB的Gap Lock算法的存在,并不会导致幻读问题,但是对于Oracle或者SQL Server,则会发生幻读。
  4. 串行化:是最高级别的隔离级别,它要求同一时间最多只能有一个事务在执行,但是会严重影响数据库的性能。

同时,对于较高的隔离级别可能更容易造成锁冲突和死锁。


索引

MySQL一共提供了四种索引实现:
B+Tree索引:最常见的索引类型,通过B+树来实现数据的快速访问
HASH索引:通过构造哈希表来实现数据的快速访问,只有Memory引擎支持
R-Tree索引:通过空间索引来实现数据快速访问
Full-Text索引:全文索引,用于快速检索较长的文本。

1、索引的支持情况

下面会针对具体的索引结构讲解

MySQL的存储引擎MEMORY_mysql_02
2、主键
MyISAM可以不设置主键;
InnoDB必须设置主键;如果设置了主键,那么InnoDB会选择主键作为​​​聚集索引​​、如果没有显式定义主键,则InnoDB会选择第一个不包含有NULL值的唯一索引作为主键索引、如果也没有这样的唯一索引,则InnoDB会选择内置6字节长的ROWID作为隐含的聚集索引(ROWID随着行记录的写入而主键递增)。之所以这么做是因为和InnoDB的表文件结构密切相关,待会我们会提到。

3、自增列(自增主键)
对于InnoDB的自增列,InnoDB规定必须包含只有该字段的索引(主键索引);
MyISAM的自增列,则可以将该字段与其他字段组成联合索引。

关于主键

如果表使用自增主键,那么每次插入新的记录,记录就会顺序添加到当前索引节点的后续位置,主键的顺序按照数据记录的插入顺序排列,自动有序。当一页写满,就会自动开辟一个新的页;

如果使用非自增主键(如果身份证号或学号等),由于每次插入主键的值近似于随机,因此每次新纪录都要被插到现有索引页得中间某个位置,此时MySQL不得不为了将新记录插到合适位置而移动数据,甚至目标页面可能已经被回写到磁盘上而从缓存中清掉,此时又要从磁盘上读回来,这增加了很多开销,同时频繁的移动、分页操作造成了大量的碎片,得到了不够紧凑的索引结构,后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。

为什么非主键索引结构叶子结点存储的是主键值?

减少了出现行移动或者数据页分裂时二级索引的维护工作(当数据需要更新的时候,二级索引不需要修改,只需要修改聚簇索引,一个表只能有一个聚簇索引,其他的都是二级索引,这样只需要修改聚簇索引就可以了,不需要重新构建二级索引)


存储结构

MyISAM

将一张表的结构和内容分为三个文件:
1、表结构文件,后缀名为frm
2、索引文件,后缀名为MYI
3、数据文件,后缀名为MYD

索引文件保存记录所在的数据文件的位置,通过读取索引文件获取到位置信息后,再通过读取数据文件快速取得数据。

MyISAM的B+树索引

叶节点的data域存放的是数据记录的地址,MyISAM索引文件和数据文件是分离的,索引文件仅保存数据记录的地址。主键索引和辅助索引(Secondary key)在结构上没有任何区别,只是主索引要求key是唯一的,而辅助索引的key可以重复。

InnoDB

InnoDB只有两个文件:
1、表结构文件,后缀名为frm
2、数据、索引文件,后缀名为ibd

InnoDB采用了聚簇索引(主键索引)的方式实现B+Tree索引,聚簇索引的实现方式是将数据行和相邻主键紧凑地存入文件中。数据文件本身也是索引文件。

InnoDB的B+树索引

叶节点包含了完整的数据记录,表数据文件本身就是按B+Tree组织的一个索引结构,这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键,因此InnoDB表索引即数据本身。

InnoDB的辅助索引(二级索引、非聚集索引)data域存储相应记录主键的值而不是地址。换句话说,InnoDB的所有辅助索引都引用主键作为data域。


其它区别

1、InnoDB支持外键,MyISAM不支持外键。

2、对于SQL语句SELECT COUNT(*) FROM table,InnoDB需要对整张表进行读取才能得出结果。而MyISAM不同,MyISAM文件保存了整张表的记录,可以直接给出结果。但是如果加上了where条件,InnoDB和MyISAM都需要扫描整表给出结果。

3、对多CPU的优化
在单线程的情况下,MyISAM读取、修改速度要比InnoDB快,但是在多线程的情况下,InnoDB读取、修改速度显著提升,而MyISAM在多CPU的情况下几乎没有提升;

4、对于SQL语句DELETE FROM table,InnoDB不会直接删除数据文件,而是一行行删除。对于MyISAM则是直接删除文件重新建立表
5、InnoDB存储引擎的表有着可靠的崩溃恢复机制,数据较容易恢复。MyISAM数据恢复较为困难。



MEMORY

使用存储在内存中的内容来创建表,而且数据全部放在内存中

每个基于MEMORY存储引擎的表实际对应一个文件的文件名与表名相同frm类型的磁盘文件用于存储表的结构数据文件存储在内存中,这样有利于数据的快速处理,提高整个表的效率。值得注意的是,服务器需要有足够的内存来维持MEMORY存储引擎的表的使用。如果不需要了,可以释放内存,甚至删除不需要的表。

MEMORY默认使用哈希索引。速度比使用B型树索引快。当然如果你想用B型树索引,可以在创建索引时指定。

注意,MEMORY用到的很少,因为它是把数据存到内存中,如果内存出现异常就会影响数据。如果重启或者关机,所有数据都会消失。因此,基于MEMORY的表的生命周期很短,一般是一次性的

MEMORY特性

  1. MEMORY表的每个表可以有多达32个索引,每个索引16列,以及500字节的最大键长度
  2. MEMORY存储引擎执行HASH和BTREE索引
  3. 可以在一个MEMORY表中有非唯一键值
  4. MEMORY表使用一个固定的记录长度格式
  5. MEMORY不支持BLOB或TEXT列
  6. MEMORY支持AUTO_INCREMENT列和对可包含NULL值的列的索引
  7. MEMORY表在所有客户端之间共享(就像其他任何非TEMPORARY表)
  8. MEMORY表内存被存储在内存中,内存是MEMORY表和服务器在查询处理时的空闲中,创建的内部表共享
  9. 当不再需要MEMORY表的内容时,要释放被MEMORY表使用的内存,应该执行DELETE FROM或TRUNCATE TABLE,或者删除整个表(使用DROP TABLE)




Archive 

archive是归档的意思,仅仅支持插入和查询两种功能,在MySQL5.5以后支持索引功能,他拥有很好的压缩机制,使用zlib压缩库,在记录请求的时候实时的进行压缩,经常被用来作为仓库使用。适合存储大量的独立的作为历史记录的数据。拥有很高的插入速度但是对查询的支持较差。

使用场景

  1. 如果应用程序对数据的一致性要求比较高,例如提供提交、回滚、崩溃恢复能力的事物安全(ACID兼容)能力,并要求实现并发控制,那么需要选择InnoDB,因为InnoDB支持事务和外键
  2. 以读操作为主的业务,适合使用MyISAM,对于读多写多的业务,适合使用InnoDB。
  3. 如果只是临时存放数据,数据量不大,并且不需要较高的数据安全性,可以选择将数据保存在内存中的Memory引擎,MySQL中使用该引擎作为临时表,存放查询的中间结果
  4. 如果只有INSERT和SELECT操作,可以选择Archive,Archive支持高并发的插入操作,但是本身不是事务安全的。Archive非常适合存储归档数据,如记录日志信息可以使用Archive

MySQL的存储引擎MEMORY_数据_03