MySQL的存储引擎(一)

MySQL的存储引擎

存储引擎位于MySQL三层架构当中的第三层, 它负责MySQL中数据的存储和提取. 和Linux下各种文件系统一样, 每个存储引擎都有它的优势和劣势. 服务器通过API与存储引擎进行通讯. 这些接口屏蔽了不同引擎之间的差异, 使得这些差异对上层查询过程不可见. 存储引擎的底层函数执行各种单一操作, 但是不会去解析SQL, 不同引擎之间不会互相通信, 只是执行上层的指令, 响应请求. MySQL中的事务也是通过存储引擎对表的行级锁和表锁实现的.

可以使用 SHOW TABLE STATUS 命令来查询表的相关信息:

mysql> show table status like 'tb_goods'\G;
*************************** 1. row ***************************
Name: tb_goods
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 46814
Avg_row_length: 236
Data_length: 11075584
Max_data_length: 0
Index_length: 11599872
Data_free: 9437184
Auto_increment: NULL
Create_time: 2018-01-13 21:51:22
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment: 摘要
1 row in set (0.00 sec)
其中Engine: InnoDB 说明这个表用的是InnoDB存储引擎。
对结果的其他信息的解释如下：
- Name: 表名
- Engine: 表的存储引擎类型
- Row_format: 行的格式. 对于MyISAM表, 可选的值为Dynamic, Fixed 或者Compressed. Dynamic的长度是可变的, 一般包含可变长度的字段, 如VARCHAR或BLOB. Fixed的行长度是固定的, 只包含长度固定的列, 如CHAR和INTEGER. Compressed的行则只在压缩表中存在（压缩表的介绍见下文‘MyISAM压缩表’）.
- Rows: 表的中行数. 对于MyISAM和其他一些存储引擎, 该值是精确的, 但是对于InnoDB, 该值是估计值.
- Avg_row_length: 平均每行包含的字节数.
- Data_length: 表数据的大小(字节).
- Max_data_length:表数据的最大容量, 该值和存储引擎有关.
- Index_length: 索引的大小(字节).
- Data_free: 对于MyISAM表, 表已分配但目前没有使用的空间,这部分空间包括之前删除的行,以及后续可以被INSERT利用到的空间.
- Auto_increment: 下一个AUTO_INCREMENT的值.
- Create_time: 表的创建时间.
- Update_time: 表数据的最后修改时间.
- Check_time: 使用CHECK TABLE 命令或者 myisamchk工具最后一次检查表的时间.
- Collation: 表的默认字符集和字符排列顺序规则.
- Checksum: 如果启用, 保存的是整个表的实时校验和.
- Create_options: 创建表时指定的其他选项.
- Comment: 该列包含了一些其他的额外信息. 对于InnoDB表,保存的时表在创建时带的注释. 对于MyISAM表,则保存的时InnoDB表空间的剩余空间信息. 如果是一个视图, 则该列包含VIEW文本字样.

InnoDB存储引擎

InnoDB是MySQL的默认事务性引擎, 也是最重要,使用最广泛的存储引擎. 它的优势在于处理大量短期事务, 短期事务的特点是并发高,而且绝大部分的情况下都会正常提交, 很少会被回滚. 由于InnoDB的性能和自动崩溃恢复特性, 使得它在非事务性存储的需求中也可以发挥很好的作用. 所以MySQL将InnoDB设置为默认的存储引擎, 如果没有非常特别的原因, 都应该优先InnoDB引擎.

InnoDB的历史

InnoDB有很复杂的历史, 其开发团队在2008年发布了InnoDB plugin,适用于MySQL5.1, 但这是Oracle创建的下一代InnoDB引擎, 其拥有着是InnoDB而不是MySQL. 所以MySQL默认还是选择集成旧的InnoDB引擎. 当然用户可以自行选择性能更好、扩展性更好的InnoDb plugin来代替旧版本。 这种情况一直持续到了Oracle收购了Sun公司后发布的MySQL5.5才使用InnoDB plugin替换了旧版本的InnoDB。
我们现在所用的InnoDB，也就是之前的InnoDB plugin，支持一些新的特性，诸如利用排序创建索引、删除或者增加索引时不需要复制全表数据、新的支持亚索的存储格式、新的大型列值如BLOB（当然现在看来已经不新了）的存储方式，以及文件格式管理等。
InnoDB是一个很重要的存储引擎，很多个人和公司都对其贡献代码，为它的发展作出重要的贡献，为了改善性能，Oracle也投入大量的资源，并做了很多卓有成效的工作。

InnoDB特性

InnoDB的数据存储在表空间中，表空间是由InnoDB管理的黑盒子。InnoDB可以将每个表的数据和索引存放在单独的文件中，也可以使用裸设备作为表的存储介质，但是在现代文件系统如此发达的情况下，使用裸设备并不是必要的选项。

InnoDB采用MVCC来支持高并发，并且实现了四个标准的隔离级别。其通过间隙锁（next-key locking）策咯防止幻读。间隙锁不仅锁定查询涉及的行，还会对索引中的间隙进行锁定，来阻止幻行的插入。InnoDB的默认事务隔离级别是“可重复读”。

InnoDB表是基于聚簇索引建立的，InnoDB的索引结构和MySQL的其他存储引擎有很大的不同，聚簇索引对主键查询有很高的性能。但是它的二级索引中必须包含主键列，所以如果主键列很大的话，其他的所有索引都会很大。因此，如果表上的索引比较多，主键应当尽可能小。另外，InnoDB的存储格式是平台独立的。
InnoDB内部做了很多的优化，包括从磁盘读取数据时采用的可预测性预读，能够自动在内存中创建hash索引来加速读取操作的自适应hash索引，以及能够加速插入操作的插入缓冲区（insert buffer）等。

MyISAM存储引擎

在MySQL5.1及之前的版本，MyISAM 是默认存储引擎。 它提供很多的特性，比如全文索引、压缩、空间函数（GIS）等，但是不支持事务和行级锁，最重要的是它在崩溃后无法安全恢复，这对于现代MySQL的使用者来说，基本是不可接受的。 尽快MyISAM有很多的缺点，但它对于只读的数据、或者表比较小、可以忍受修复操作的情况，依然可以作为一个选择，但是请不要将默认存储引擎改为MyISAM，而应该默认使用InnoDB。

MyISAM特性

作为MySQL最早的存储引擎之一，MyISAM有一些久经考验的特性，可以满足用户的一些实际需求。
- 加锁与并发
MyISAM对整张表加索，而不是行级锁。读取时会对需要读到的所有表加共享锁，写入时则对表加排他锁。但是在表有读取查询的同事，也可以往表中插入新的记录（CUNCURRENT INSERT)
- 修复
对于MyISAM表，MySQL可以手工或者自动执行检查和修复操作（注意不是崩溃恢复）。执行表的修复可能会导致一些数据丢失，而且修复操作非常缓慢。可以通过CHECK TABLE tableName 进行修复。另外如果MySQL服务器已经关闭，可以通过myisamchk命令行工具进行检查和修复操作。
- 索引特性
对于MyISAM表，即使是BLOG和TEXT等长字段，也可以基于前500个字符创建索引，而且其也支持全文索引，这是一种基于分词创建的索引，可以支持复杂的查询。
- **延迟更新索引键（Delayed Key Write）
创建MyISAM表的实际，如果指定了DELAY_KEY_WRITE选项，在每次修改执行完成时，不回立刻将修改的索引数据写入磁盘，而是会写到内存中的键缓冲区，只有在清理键缓冲区或者关闭表的时候才会将对应的索引块写入到磁盘。通过这种操作来提升写入性能是非常有效的，但是在数据库或者主机崩溃时会造成索引损坏，这时需要执行修复操作。
修改表的存储引擎

查看当前索引

开启延迟更新索引：这个选项默认开启，可以使用SET BLOBAL DELAY_KEY_WRITE=0来修改

由于暂时无法模拟数据库崩溃的操作，延迟更新索引的效果没有演示。

MyISAM压缩表

如果表在创建并导入数据后，不会再进行修改操作，那么这样的表获取适合采用MyISAM压缩表。
可以使用myisamchk对MyISAM进行表亚索。压缩表不能修改，但是能极大减少磁盘空间占用，从而减少磁盘I/O，从而提升查询性能。压缩表支持索引，但索引也是只读的。对大多数场景，读取压缩表时的解压带来的开销并不大，而减少I/O带来的好处要大的多。
可以通过SHOW GLOBAL VERIABLES LIKE 'datadir%'查看数据库文件的位置，MyISAM引擎将数据库以不同的文件类型分开存储，分别是：数据文件(.MYD),索引文件(.MYI)和结构文件(.frm)。
使用myisampack工具对数据库进行压缩。
# myisampack person.MYI
Compressing person.MYD: (14 records)
- Calculating statistics
- Compressing file
20.5%
Remember to run myisamchk -rq on compressed tables

MyISAM性能

MyISAM引擎设计比较简单，数据以紧密格式存储，所以在某些场景下的性能很好MyISAM有一些服务器级别的性能扩展限制，比如对索引键缓冲区的Mutex锁，MariaDB基于断（segment）的索引键缓冲区机制来避免该问题，但是MyISAM最典型的问题还是表锁问题，如果发现所有查询都长期处于Locked状态，那么毫无疑问是表锁在搞鬼。