《高性能MySQL》读书笔记（二） ——MySQL存储引擎概述
《高性能MySQL》读书笔记（二） ——MySQL存储引擎概述
《高性能MySQL》读书笔记（二）——MySQL存储引擎概述
（原创内容，转载请注明来源，谢谢）
一、基础信息
mysql将数据库保存在数据目录下的一个子目录，创建表时，会在此目录下，创建与表明相同的文件名，后缀是.frm，大小写根据系统决定，windows大小写不敏感，linux大小写敏感。
可以采用showtable status命令查看表格信息，例如：
上述结果中，name是名字，engine是引擎，row_format是数据的长度是否可变，rows是当前有几行，avg_row_length是平均每行字节数，data_length是表数据字节数，max_data_length是该表最大可以使用的字节数，index_length是当前索引占字节数，data_free是已经分配的空间还未使用的（主要是被删除的行，该行原先的空间可以给新的数据使用），auto_increment是自增步长，三个时间分别是创建、最后修改、最后检查表的时间，collation是编码方式，checksum是整个表的校验和，create_options是其他选项，comment创建表格时候的备注。
二、innodb引擎
innodb是mysql默认的事务性引擎，被设计用来处理大量短期事务，这类事务大多数情况下不会发生需要回滚的事情。innodb是mysql最广泛的引擎，很多公司直接规定绝大部分建表要求采用innodb引擎。
innodb特性如下：
1）innodb数据存储在表空间中，表空间是由一系列文件组成的，由innodb管理的“黑盒子”。
2）innodb采用mvcc的高并发策略，实现四个隔离级别，默认是repeatable read（可重复读），并通过间隙锁避免幻读。间隙锁是指不仅锁定涉及的行，还会将索引中的间隙进行锁定。
3）innodb采用聚簇索引的方式，索引结构和其他存储引擎很大不同，对主键查询有很高的性能。但是由于其要求二级索引都必须要包含主键，所以主键大会导致索引文件大。因此建表应该设计索引尽量小。
4）innodb内部有许多优化，包括从磁盘读取数据时的可预测性预读，自动在内存中创建hash索引，以加速读操作，以及能够加速插入操作的插入缓冲区等。
5）innodb支持热备份，而大多数mysql的其他引擎都不支持。
6）innodb引擎中，索引和表数据是在同一个文件中，这个和mysiam有很大不同。
三、myisam引擎
在mysql5.1之前，mysql默认的引擎是myisam，其提供大量特性，包括全文索引、压缩、空间函数等，但是其不支持事务和行级锁，因此数据库奔溃后很难恢复。
1、存储
myisam将数据文件和索引文件分开存，分别是后缀.MYD和.MYI，其行支持动态或静态，会根据表定义来决定行的格式。
2、特性
1）加锁与并发
myisam会对整个表加锁，读时加共享锁，写时加排他锁，但是在读的时候也可以插入数据，称为并发插入。
2）修复
myisam修复可能导致数据丢失，而且非常慢，通过命令repair table要求修复表。
3）延迟更新索引键
如果设置了delay_key_write，则每次修改完数据，不会立即写入磁盘，而是会写到内存的缓冲区，只有清理缓冲区或关闭表才会写入数据。这样做提升了性能，但是如果数据库奔溃也会导致数据丢失。
3、myisam压缩表
如果创建数据后，不会在修改，则使用myisam压缩表比较合适，可以使用myisampack对表进行压缩。压缩的数据，如果没有解压，是无法修改的。由于每一行的数据是单独压缩的，因此读取数据不需要解压，这样一来，一次性读入一个压缩表，而不用每次通过I/O从磁盘取数据，极大加快了读取速度。
四、其他mysql内建索引
1、archive
只支持insert和select，会缓存所有的写并利用zlib进行压缩，所以磁盘操作比myisam更少。但是查询需要全表扫描，适用于日志和数据采集类系统。
支持行级锁和专用缓冲区，对快速写具有高并发性，在批量插入完成之前，读操作不可见，这个模仿mvcc。
archive是一个针对高速插入和压缩做优化的引擎。
2、csv引擎
该引擎可以将csv文件作为数据库表处理，不支持索引。可以在数据库运行期间拷入、拷出文件，也可以将excel文件转成csv并放在mysql数据目录，这样就可以在mysql直接打开使用。
3、federated引擎
这个引擎是一个访问mysql服务器的代理，可以创建远程访问服务器的客户端连接，并将查询传输到远程执行。
4、memory引擎
如果要快速访问数据，而且数据丢失也没问题，并且不修改数据，则采用memory引擎最佳，这个表保存在内存中，不需要I/O，比myisam快了一个数量级，重启服务器后，表的结构会在，但是里面的数据会全部丢失。
使用场景：
1）用于查找表或映射表。
2）用于缓存周期性聚合数据，或数据分析期间的临时数据。
该引擎支持hash索引，速度非常快，但是是表锁，并发写入性能低，不支持blob或text，并且每行长度固定，即使是varchar，也会转成char。
mysql保存临时结果，内部实现就是用memory引擎，如果太大超出内存允许范围，则会转成myisam引擎。
另外，临时表和memory引擎的数据表不一样。临时表是用create temporary table命令创建的表，适用于任何引擎，只在单个连接有效，连接中断则消失；而memory引擎的表数据，在服务器重启之前，会一直存在。
5、ndb集群引擎
这个引擎是sql和ndb之间的接口，mysql集群，是由mysql服务器、ndb集群存储引擎，以及分布式、share-something、容灾、高可用的ndb数据库集合，共同组成的。
6、第三方引擎
1）OLTP类引擎
XtraDB是innodb引擎的改进版本，可以完全取代innodb，在性能、可测量性、操作灵活性都很优秀，并且兼容innodb数据文件和innodb的所有查询。mariadb包含此引擎。
2）面向列的引擎
通常mysql的引擎都是针对行的，但是在大数据量的情况下，如果不需要行的全部数据，采用面向列的方式效率更高。
Infobright是最有名的面向列的引擎，在数据量达到数十TB时运行良好，是为数据分析和数据仓库设计的引擎。该引擎将数据高度压缩，按块排列，每块有一组元数据。在数据处理时，可以通过元数据判断是否要跳过该块，甚至用块也可以满足要求。该引擎不支持索引，块结构是一种类似的准索引。
五、存储引擎选择
1、通用建议
mysql默认的存储引擎是innodb，并且作者的建议是，除非对innodb所不具备的功能非常的有需求，否则都用innodb。例如要全文索引，可以考虑innodb结合sphinx，而不是用myisam。
当然，某些特定情况，如不在意数据是否丢失，没有并发需求，同时觉得innodb太占空间，则可以用myisam。
另外，非常不建议对不同的表采用不同的索引，即混合索引，这样会出现很多意外的问题，使得程序非常复杂。
例如，选择支持事务的索引和不支持事务的索引混合，则事务回退时，不支持索引的表的信息无法回退。
具体应用大体如下。
2、日志型应用
如果需要对用户访问的记录实时记录等，对插入数据有高要求，则使用archive或者myisam比较合适，但是建议采用读写分离。将对主库进行写操作，对从库进行读操作。这样可以避免获取日志报表等的时候，影响插入的性能。
另外，也可以按照日期等分表，这样查询历史就可以直接读取历史表。
3、大部分情况只读的应用
如各语言、框架等的说明文档，大部分情况下是只读的，基本没有写操作，虽然myisam是对读性能比较好，但是仍支持innodb引擎。因为其聚簇索引的速度非常快，且崩溃后的恢复等也比较容易。
4、订单处理
涉及到订单处理，必须使用事务，这也就使得使用innodb是最佳选择。
5、CD-ROM
如果数据不改变了，则采用myisam的压缩表，速度会非常快。
6、大数据量
在3-5TB的时候，使用innodb可以比较好的运行，但是需要良好的系统设计。超过这个数目，建议使用Infobright或TokuDB。
六、引擎转换
共有三种引擎转换方式。
1、alter table
例如，altertable tablename engine = InnoDB;
这个做法可以适用于任何存储引擎，但是比较耗时，mysql会按行将数据从一张表复制到另一张表，在复制期间可能会消耗所有的I/O，同时原表会加上读锁。转换表会失去原有的特性，如将innodb转换成myisam，再转回innodb，原有的外键会丢失。
2、导出和导入
使用mysqldump工具，将表导出到文件，然后手动修改文件中的create table中的存储引擎的选项，同时由于一个数据库不能有两个表名，还要修改表名。修改完毕后再导入即可。
3、创建与查询
这种方式是结合前两种方式，而且不需要整个表导出。实际方案即创建一张结构和原表一样的新表，且引擎设置为新的引擎，然后使用insert into newtable select * from oldtable的方式。如果数据量太大，可以采用分批的方式进行。为了保证数据的完全一致，也可以在进行复制期间，给原来的表加上读锁。
——written by linhxx 2017.09.12