随笔分类
InnoDB
普及:InnoDB中数据和索引是在一起进行存储的:.idb
.frm:表结构定义的文件
InnoDB是 MySQL默认的事务型存储引擎,也是最重要的、使用最广泛的存储引擎.
InnoDB被设计来处理大量的短期事务(short-lived),短期事务大部分情况下是可以正常提交的,很少情况下会被回滚.
InnoDB支持行锁设计,以及外键
InnoDB的性能和自动崩溃恢复的特性,使得它在非事务型存储的需求中也很流行. 除非真的有非常特别的原因需要使用其它的存储引擎,否则应该优先去考虑使用 InnoDB.
InnoDB的 数据存储在表空间(tablespace),表空间是 InnoDB管理的一个黑盒子,由 一系列的数据文件组成.
在 MySQL4.1及其以后版本,InnoDB可以将每个表的数据和索引放在单独的文件中.
InnoDB采用 MVCC来支持高并发,并且实现了 4个标准的隔离级别,其默认的事务隔离级别是 repeatable read,并且通过 间隙锁(next-key locking) 策略来 防止幻读的出现.
间隙锁使得 InnoDB 不仅锁定查询涉及的行,还会对索引中的间隙进行锁定,以来防止幻影行的出现.
InnoDB表是基于 聚簇索引建立的. InnoDB的索引结构和 MySQL的其他存储引擎有很大的不同,聚簇索引对于主键的查询有着很高的性能.
- 对于表数据的存储,InnoDB存储引擎采用了 聚集(clustered)的方式,因此每张表的储存都是按主键的顺序进行存放的,如果没有显示定义主键,则会去选择 非空的唯一索引作为主键,如果也没有,InnoDB存储引擎会为每一行生成一个 6字节的 rowid,并以此为主键.
不过它的 二级索引(secondary index,非主键索引)必须包含主键列,如果主键列很大的话,那么其它的所有索引都会很大. 因此,如果表上的索引很多的话,主键列应尽可能的小. InnoDB的存储格式是平台独立的,也就是说可以将数据和索引文件从一个平台复制到别的平台.
InnoDB内部做了很多优化,包括从磁盘读取数据时采用的 可预测性预读,能够自动在内存中创建 hash索引以加速操作的自适应哈希索引(adaptive hash index),以及能够加速插入操作的插入缓冲区(insert buffer)
MyISAM
普及:MyISAM中数据文件(.MYD)和索引(.MYI)是分开进行存储的
.frm:表结构定义的文件
在MySQL 5.1即之前的版本,MyISAM是默认的存储引擎,MyISAM提供了大量的特性,包括全文索引、压缩、空间函数(GIS)等,但 MyISAM 不支持事务以及行级锁,而且有一个毫无疑问的缺陷:崩溃之后无法安全恢复
对于只读的数据,或者表比较小、可以忍受修复操作(repair),则依然可以继续使用 MyISAM.
MyISAM不支持行锁设计以及外键定义
MyISAM会将表存储在两个文件中:数据文件和索引文件,分别以 .MYD和 .MYI为扩展名. MyISAM表可以包含动态或者静态(长度固定)行. MySQL会根据表的定义来决定采用何种行格式. MyISAM表可以来存储行记录数,一般受限于可用的磁盘空间,或者操作系统中单个文件的最大尺寸.
- 数据文件和索引文件分开存储
- 对于 MyISAM存储引擎,数据库只缓存其索引文件,数据文件的缓存由操作系统而言,也就是说,对于 MyISAM的索引而言,叶子节点 data域存储的仅仅是 行记录的地址,还需根据地址去寻找对应的行记录,可见,索引效率其实是不如 InnoDB的.
特性
加锁与并发
- MyISAM对整张表进行加锁,而不是针对行. 读取时会对读到的所有表加共享锁,写入时则对表加排它锁. 但是在表读取查询的同时,也可以往表中插入新的数据(并发插入,Concurrent Insert)
修复
- 对于MyISAM表,MySQL可以手工或者自动执行检查和修复操作,此处的修复和事务恢复以及崩溃后的恢复时不同的改变
- 执行表的修复可能导致一些数据丢失,而且修复操作是比较缓慢的,可以通过
check table 表名
来检查表的错误,如果有错误,可以执行repair table 表名
来进行修复. 另外,如果 MySQL服务器已经关闭,也可以通过 myisamchk命令行工具进行检查和修复操作.
索引特性
- MyISAM支持全文索引,这是一种基于分词创建的索引,可以支持比较复杂的查询.
延迟更新索引键
Delayed Key Write
- 创建 MyISAM表时,如果指定了 DELAY_KEY_WRITE选项,在每次修改执行完成时,并不会将修改的索引数据写入磁盘,而是写到内存中的键缓冲区(in-memory key buffer),只有在 清理键缓冲区或者关闭表时才会将对应的索引块写入到磁盘.
- 这种方式极大地提升写入性能,但是在数据库或者主机崩溃时会造成索引损坏,需要执行修复操作.
- 延迟更新索引键的特性,可以全局设置,也可以为单个表设置.
压缩表
如果表在创建并导入数据后,不会再进行修改操作,那么这样的表或许比较适合来使用 MyISAM压缩表.
压缩表是 不能进行修改的(除非现将表解除压缩,修改数据,然后在进行压缩)
压缩表可以 极大地减少磁盘空间占用,因此可以来减少磁盘 I/O(减少了磁盘上数据库的大小,使得用户不必频繁地操作写入和读取便可以访问到数据,这也便减少了磁盘 I/O,减少可 I/O 是关键!),从而提升查询性能. 压缩表也支持索引,但是索引也是只读的
以现在的硬件能力,大于大多数应用场景,读取压缩表数据时的解压所带来的开销影响并不大,而 减少 I/O 带来的好处要好得多.
压缩时表的 记录是独立压缩的,所以读取单行时不需要去解压整个表(甚至不需要解压行所在的整个页面)