MySQL - 存储引擎
Mysql逻辑架构视图
主要存储引擎:
引擎 | Mysql版本 | 事务 | 锁粒度 | 主要引用 | 忌用 |
---|---|---|---|---|---|
InnoDB | 全部 | 支持 | 支持Mvcc行级锁 | 事务处理 | |
MyISAM | 全部 | 不支持 | 支持并发插入的表锁 | Select,insert高负载 | 读写并重的场合 |
MyISAM Merge | 全部 | 不支持 | 支持并发插入的表锁 | 分段归档, | 许多全局查找 |
Memory(HEAP) | 全部 | 不支持 | 表锁 | 中间计算,静态数据查证 | 大型数据集,持久性存储,重启后数据丢失 |
Falcon | 6.0 | 支持 | 支持Mvcc行级锁 | 事务处理 | |
Archive | 4.1 | 支持 | 支持Mvcc行级锁 | 日志记录,聚合分析,只支持insert,select操作 | 需要随机读取 删除 |
CSV | 4.1 | 不支持 | 表锁 | 日志记录,大规模加载外部数据 | 需要随机读取,索引等 |
NDB Cluster | 5.0 | 支持 | 行级锁 | 高可用集群 | 典型引用 |
Maria | 6.x | 支持 | 支持mvcc行级锁 | 替代MyISAM |
可以通过show table status like '表名称'\G 来查看表的信息
锁粒度:
1.表锁 (Table Lock)
当一个用户对表进行写操作(新增,删除,修改), 会获得一个写锁,写锁会禁止其他用户的读写操作,当无人做写操作时,其他用户才能获得读锁,读锁与读锁之间不会冲突。表锁是最小锁策略,其性能良好,表锁由Mysql本身 和 存储引擎实现。
2.行级锁(Row Lock)
行级锁可以支持最大的并发处理,同时也带来了最大锁开销,行级锁在InnoDB,Falcon存储引擎出以实现。 行级锁由存储引擎实现,而不是Mysql服务器本身。
3.MVCC行级锁
MVCC 是一种多版本并发控制(Multiversion Concurrency Control)的技术,MVCC不是mysql独有的技术,Oracle,PostgreSQL等其他数据库也使用了该技术。
每种存储引擎实现MVCC的方式是不同的,例如乐观并发控制,悲观并发控制,下面以Innodb的实现方式 说说其简要工作原理:
Innodb通过为每个数据行增加两个隐士的字段来实现MVCC,这两个隐士字段记录了行的创建的时间,以及过期时间(删除时间),每一行都存储了 事件发生时的系统版本号,用来替代事件发生时的实际时间。每一次开启一个新的事务时,版本号都会递增,每个事务都会保存它在开始时的“系统版本”的记录, 而每个查询都会根据事务的版本号,检查每行数据的版本号。
SELECT
Innodb 检查每行数据,确保它们符合两个标准
*Innodb只查找版本号小于或等于当前版本号的数据,这确保了当前事务读取的数据行都是在事务开始前已存在,或者是当前事务创建或修改的行。
*数据行的删除标识必须是未定义,或者是大于事务版本的,这确保了查询出来的数据在事务开始时是未被删除的。
INSERT
Innodb为新增的数据行记录当前版本号
DELETE
修改要删除行的过期时间标识
UPDATE
为每个需要更新的行建立一个新的行的拷贝,并为新的行拷贝记录当前系统版本。也为更新前的数行记录系统版本号,作为旧行的删除版本标识。保存这些额外记 录的好处是大多数并发操作都不必申请枷锁,这使读操作变得更快,因为读操作是要选取符合标准的数据行即可,这种方式的缺点是:存储引擎必须为每行数据存储 更多的额外数据,浪费空间,做更多的检查工作,以及整理一些额外的数据带来的开销。
加锁策略 并发 系统开销 引擎 表级加锁 最低 最低 MyISAM,Merge,MyISAM Merge 行级加锁 高 高 NDB Cluster 支持MVCC的行级加锁 最高 最高 InnoDB,Falcon
选择合适的引擎:
1.事务
如果应用需要事务处理操作,Innodb仍是最稳定的,如果不需要事务,主要操作是处理一些SELECT和INSERT操作或专用日志记录,那么MYISAM是一个不错的选择。
2.并发
3.备份
4.崩溃后恢复