【二】MySql高性能优化——存储引擎

    在上一篇中主要说到了MySql所涉及到的基础知识，其中很多知识在其他的编程语言中也有引用，所以整体来说不会很难理解。今天主要讨论一下MySql中所涉及到的存储引擎。存储引擎主要负责数据的存取，有的存储引擎也会负责事务的管理。如：InnoDB和XtraDB。不同的存储引擎都会有各自的特性，以及适用的场景，所以了解各个存储引擎对于实际工作中进行数据库的设计是很有必要的，有助于我们设计出更高效的数据库架构（注意：我们这里不谈论数据库设计，后面我会单独开一系列专门去聊聊数据库设计）。

    在讲数据库引擎之前我们先说说MySql的一些存储方法。MySql会为每个数据库（schema）在数据目录下新建一个子目录，并且在新建表时会同时创建一个.frm文件，这个文件中保存了表的一些定义。MySql表的定义是在服务层统一处理，与存储引擎无关。

//显示表的相关信息
SHOW TABLE STATUS LIKE 'user' \G

        数据库存储引擎种类繁多，我们只需要了解业界流行的常用的就可以了。如果对数据库存储引擎有特别兴趣的朋友可以自行深入学习。转换表的存储引擎会丢失原引擎的所有特性。

//修改表的存储引擎
ALTER TABLE mytable ENGINE = InnoDB

    InnoDB

    InnoDB将数据存储在自己管理的黑盒子（表空间）中，并且会将每个表的数据和索引存放在单独的文件中。其采用的默认隔离级别为REPEATABLE READ（可重复读），在上篇文章中我们讲过，这一隔离级别会有幻读问题，如果想要避免幻读，只有SERIALIZABLE（可串行化）级别可以做到，而InnoDB通过间隙锁对索引中的间隙进行锁定，防止了幻行的插入，解决了幻读问题。这样就同时具有REPEATABLE READ的性能，又具有SERIALIZABLE级别的安全。InnoDB采用的索引方式是聚簇索引，其对主键查询具有很高的性能。对于索引我们会在后面的博文中讲述。InnoDB内部做了很多的优化，引入了hash索引用于提高查询效率（这里是伪hash索引），同时引入插入缓冲区用于提高插入操作效率。其为了支持高并发引入了MVCC（多版本并发控制）。InnoDB现在是MySql的默认存储引擎，经过这么多年的发展，其在性能的各个方面都极具优势，所以如非特别需要我们都建议优先使用InnoDB作为我们数据库的存储引擎。

    MyISAM

    MyISAM存储引擎在MySql 5.1以前的版本汇总是其默认引擎。其提供了全文索引、压缩、空间函数等特性，但并不支持事务和行级锁。同时MyISAM有崩溃后无法安全恢复的缺陷。MyISAM将数据存储在两个文件中：数据文件（.MYD）和索引文件（.MYI）。可包含动态或静态行。存储大小由磁盘空间和操作系统中单个文件最大尺寸决定。5.0版本中由于默认数据记录指针是6个字节，所以只能处理256TB的数据。如果需要8字节指针，可通过修改表的MAX_ROWS和AVG_ROW_LENGTH选项的值来实现，算法：两者相乘=可达到的最大大小。这样的操作会重建整个表和索引非常耗时。

    特性：

        ①只有表级锁，没有行级锁；

        ②执行表的自动修复会导致一些数据丢失非常耗时；

        ③对于任何存储类型（包括BLOB和TEXT）都可以基于前500字符创建索引。同时也支持全文索引；

        ④延迟更新索引键。需要设置DELAY_KEY_WRITE，可支持全局和单表。虽然可以提升写入性能，但当数据库或主机崩溃时会造成索引损坏。

    使用：

    如果表中的数据导入后不再做修改可以使用压缩表。压缩表是独立压缩，读取单行不需要解压整个表。压缩表可以减少磁盘空间占用，因此也减少I/O，从而提高性能。索引为只读型。如果要对数据做修改需要先解压再修改。往往修改数据的开销是可以接受的。

    Archive

    Archive为非事务引擎，只支持INSERT和SELECT操作。采用压缩的方式让数据存储比MyISAM更少。其SELECT需要全表扫描，所以该存储引擎只适合日志和数据采集类应用。支持行级锁和专用的缓存区，可实现高并发插入操作。

    Blackhole

    Blackhole不做数据存储，只会记录操作到Blackhole表的日志中。可用于复制数据库到备库或简单的日志记录功能。虽然该存储引擎有独特的特性，但即时在符合的场景下也不建议使用该存储引擎。

    CSV

    CSV存储引擎中的表都不支持索引。其可在数据库运行时拷入或拷出文件。适用于数据交换场景。

    Federated

    Federated引擎会创建一个到远程MySql服务器的客户端链接，并将查询传输到远程服务器执行，然后提取或发送需要的数据。

    Memory

   Memory引擎将数据存储在内存当作，因此具有快速访问数据的特性。但如果数据库重启则数据会丢失。适用于查找（lookup）或者映射（mapping）表、缓存周期性聚合的数据、保存数据分析中产生的中间数据。支持Hash索引。只有表级锁并发写入性能较低，且不知从BLOB或TEXT类型的列，每行长度固定，所以VARCHAR类型会被强制转换成CHAR类型。不同于临时表，临时表只是单个连接可见，连接断了临时表也就不存在了。

    NDB集群

    NDB是SQL和NDB原生协议之间的接口。MySql服务器、NDB集群存储引擎、NDB数据库组合（分布式、share-nothing、容灾、高可用）被成为MySql集群（MySql Cluster）。

    我们简单了解了这么多MySql存储引擎在工作中需要根据具体情况进行选择，总的来说需要考虑四个方面：事务、备份、崩溃恢复、特有的特性。这里需要说下MyISAM不一定比InnoDB块。

=======================================================================

名词解释：

间隙锁：首先对选中的索引记录加上锁，再对索引记录两边的间隙（向左扫描到第一个比给定参数小的值，向右扫描到第一个比给定参数大的值，然后以此为界，构建一个区间），加上间隙锁。如果一个间隙锁被事务T1加了锁，其他事务是不能在这个间隙插入记录的。