第一章 MySQL体系结构和存储引擎

内容源于《MySQL技术内幕InnoDB存储引擎》一书，重点笔记

1.1 定义数据库和实例

    数据库：物理操作系统文件或其他形式文件类型的集合。在MySQL中，数据库文件可以使frm、myd、ymi、idb结尾的文件。使用NDB引擎时，文件不是caozuo操作操作系统上的文件，而是存放于内存中的文件，但是定义仍然不变

    数据库实例：由数据库后台进程/线程以及一个共享内存区组成。共享内存区可以被运行的后台进程/线程所共享。数据库实例才是用来操作数据库文件的。

    数据库是一个文件系统，而数据库实例是操作文件系统的管理软件。

    数据库和数据库实例是一对一的关系。一个实例操作一个数据库，但集群情况下，一个数据库可能被多个数据库实例操作。

    MySQL数据库被设计为一个单进程多线程架构的数据库。

    启动数据库：./mysqld_safe &

    查看数据库的进程：ps -ef | grep mysqld

    3578进程就是MySQL数据库实例。

    MySQL启动时会去读取配置文件my.cnf，如果没有找到配置文件会使用MySQL默认的参数启动。

配置文件路径Linux：

Windows路径：

配置文件中有一个datadir参数，该参数指定了数据库所在的路径。

1.2 MySQL体系结构

MySQL体系结构图：

MySQL由以下几部分组成：

•     连接池组件
•     管理服务和工具组件
•     SQL接口组件
•     查询分析器组件
•     优化器组件
•     缓冲（Cache）组件
•     插件式存储引擎
•     物理文件

    MySQL区别于其他数据库的重要特点就是其插件式的存储引擎。MySQL插件式的存储引擎架构提供了一系列标准的管理和服务支持，这些标准与存储引擎本身无关，可能是每个数据库系统都必须的，如sql分析器和优化器等，而存储引擎是底层的物理结构的实现，每个存储引擎开发者都可以按照自己的意愿来进行开发。对于存储引擎，按照面向对象思想我是这样理解的，既然是插件式的，那么前六个组件就是存储引擎的上层接口定义，而存储引擎就是这六组接口的实现类，无论使用哪个存储引擎，这个引擎都要针对这些接口编程实现。接口定义的就是对物理文件的操作，但接口没有实现细节，细节的操作留给了插入进来的存储引擎，最终操作文件的是存储引擎。实现的细节工作留给了存储引擎，那么存储引擎就可以百家争鸣，各有各的优点。用户就可以根据自己的需求选择合适的存储引擎，甚至可以修改或自己开发存储引擎来使用。

注意：存储引擎是基于表的，而不是数据库。牢牢记住MySQL体系结构图对深入了解MySQL有极大的帮助。

1.3 MySQL表存储引擎

    存储引擎分为MySQL官方存储引擎和第三方存储引擎。以下是对存储引擎的简单介绍，便于选择时参考。

1.3.1 InnoDB存储引擎

    现在属于Oracle，InnoDB存储引擎支持事务，主要面向在线事务处理（OLTP Online Transaction Proccessing）方面的应用。其特点是行锁设计、支持外键、非锁定读。MySQL在Windows版本下的InnoDB是默认的存储引擎。

    InnoDB存储引擎将数据放在一个逻辑的表空间中，这个表空间就像黑盒一样由InnoDB自身进行管理，从MySQL 4.1版本开始，它可以将每个InnoDB存储引擎的表单独存放在一个独立的ibd文件中。与Oracle类似，同样可以使用裸设备（row disk）来建立表空间。

    InnoDB通过使用多版本并发控制（MVCC）来获得高并发性，并且实现了SQL标准的4种隔离级别，默认repeatable级别。同时使用一种被称为next-key locking的策略来避免幻读（phantom）现象的产生。除此次之外，InnoDB引擎还提供了插入缓冲（insert buffer）、二次写（double write）、自适应哈希索引（adaptive hash index）、预读（read ahead）等高性能和高可用的功能。

    对于表中的数据的存储，InnoDB存储引擎采用了聚集（clustered）的方式，这种方式类似于Oracle的索引聚集表（index organized table，IOT）。每张表的存储都按主键的顺序存放，如果没有显示地定义时指定主键，InnoDB存储引擎会为每一行生成一个6字节的ROWID，并以此作为主键。

1.3.2 MyISAM存储引擎

    MyISAM存储引擎是MySQL官方提供的存储引擎。其特点是不支持事务、表锁和全文索引，对于一些OLAP(Online Analytical Proccessing，在线分析处理)操作速度快。除Windows版本外，是所有MySQL版本默认的存储引擎。

    MyISAM存储引擎表由MYD和MYI组成，MYD用来存放数据文件，MYI用来存放索引文件。可以通过myisampack工具对数据文件压缩，由于采用的是赫夫曼编码静态算法来压缩数据，因此压缩后的表是只读的，当然也可以通过myisampack来解压数据文件。

    在MySQL5.0版本之前，MyISAM默认支持的表大小为4G，如果需要支持大于4G的MyISAM表时，则需要定制MAX_ROWS和AVG_ROW_LENGTH属性。从MySQL5.0版本开始，MyISAM默认支持256T的单表数据，这足够满足一般应用的需求。

注意：对于MyISAM存储引擎表，MySQL数据库只缓存其索引文件，数据文件的缓存交由操作系统本身来完成，这与其他使用LRU(Least Resently Used，最近最少使用算法，即缓存淘汰算法)算法缓存数据的大部分数据库大不相同。此外，在MySQL5.1.23版本之前，无论在32位还是64位操作系统环境下，缓存索引的缓冲区最大只能设置为4G。在这之后的版本中，64位系统可以支持大于4G的索引缓冲区。

    前两个引擎提到了OLTP和OLAP，OLTP是再现事务处理，InnoDB支持OLTP，那么它就适合在线交易系统；OLAP是在线分析系统，MyISAM支持OLAP，那么它就适合数据分析系统，比如数据仓库。注重事务安全性的选择InnoDB，而注重存储数据和分析数据的速度时选择MyISAM。OLTP和OLAP详细说明

1.3.3 NDB存储引擎

    2003年MySQL AB公司从Sony Ericsson（索尼爱立信）公司收购了NDB集群引擎。NDB存储引擎是一个集群存储引擎，类似于Oracle的RAC集群；不过与Oracle RAC share everything结构不同的是，其结构时sharenothing的集群架构，因此能提供更高级别的高可用性。NDB的特点是数据全部放在内存中（5.1版本开始，可以将非索引数据放在磁盘上），因此主键查找（primary key lookup）的速度极快，并且通过添加NDB数据存储节点（Data Node）可以线性地提高数据库性能，是高可用、高性能的集群系统。

    关于NDB存储引擎，有一个问题值得注意，那就是NDB存储引擎的连接操作（JOIN）是在MySQL数据库层完成的，而不是在存储引擎层完成的，这意味着，复杂的连接操作需要巨大的网络开销，因此链接操作很慢。如果解决这个问题，NDB存储引擎的市场应该非常巨大。

    NDB是数据放在内存里，那么说明存储速度和主键查询速度非常快，是其他数据库存储引擎无法媲美的。但最大的缺点是表关联查询速度比其他的引擎还要慢。

1.3.4 Memory存储引擎

    Memory存储引擎（之前称为HEAP存储引擎）将表中的数据存放在内存中，如果数据库重启或发生崩溃，表中的数据都将消失。它非常适合用于存储临时数据的临时表，以及数据仓库中的经纬度表。它默认使用哈希索引，而不是我们熟悉的B+树索引。

    虽然Memory存储引擎速度非常快，但在使用上还是有一定的限制。比如其只支持表锁，并发性能较差，并且不支持TEXT和BLOB列类型。最重要的是存储变长字段（varchar）时是按照定长字段（char）的方式进行的，因此会浪费内存（这个问题之前已经提到，eBuy的Igor Chernyshev工程师已经给出了Patch方案）。

    此外有一点常被忽视的是，MySQL数据库使用Memory存储引擎作为临时表来存放查询中间结果集（intermediate result）。如果中间结果集大于Memory存储引擎表的容量设置，又或者中间结果含有TEXT或BLOB列类型字段，则MySQL数据库会把其转换到MyISAM存储引擎表而存放到磁盘。之前提到MyISAM不缓存数据文件，因此这时产生的临时表的性能对于查询会有损失。

1.3.5 Archive存储引擎

    Archive存储引擎只支持INSERT和SELECT操作，MySQL5.1开始支持索引。其使用zlib算法将数据行（row）进行压缩存储，压缩比率一般可达1:10。正如其名称所示，Archive存储引擎非常适合存储归档数据，如日志信息。Archive存储引擎使用行锁来实现高并发的插入操作，但是本身并不是食物安全的存储引擎，其设计主要是提供高速的插入和压缩功能。

1.3.6 Federated存储引擎

    Federated存储引擎表并不存放数据，它只是指向一台远程MySQL数据库服务器上的表。这非常类似于SQL Server的链接服务器和Oracle的透明网关，不同的是，当前Federated存储引擎只支持MySQL数据库表，不支持异构数据库表。

1.3.7 Maria存储引擎

    Maria存储引擎是新开发的存储引擎，设计目标主要是用来取代原有的MyISAM存储引擎，从而成为MySQL的默认引擎，开发者是MySQL的创始人之一Michael Widenius。因此，它可以看做是MyISAM的后续版本。其特点是：feishihfeishi存数据和索引文件，行锁设计，提供MVCC功能，支持事务和非事务安全的选项支持，以及更好的BLOB字符类型的处理性能。

1.3.8 其他存储引擎

    除了上面提到的7种存储引擎外，还有很多其他的存储引擎。包括Merge、CSV、Sphinx和Infobright，它们都有各自适用的场合，这里不再一一做介绍了。了解了MySQL拥有这么多存储引擎后，现在我可以回答一些常见的问题了。

    为什么MySQL不支持全文索引？不！MySQL支持，MyISAM、Sphinx存储引擎支持全文索引。

    MySQL快是因为不支持事务吗？错！MySQL MyISAM存储引擎不支持事务，但是InnoDB支持。快是相对于不同应用来说的，对于ETL（数据库之间的数据转存）这种操作，MyISAM当然有其优势。

    当表的数据量大于1000w时，MySQL的性能会急剧下降吗？不！MySQL是数据库，不是文件，随着数据行数的增加，性能会有所下降，但是这些下降不是线性的，如果你选择了正确的存储引擎以及正确的配置，再大的数据量MySQL也是能承受的。如官方手册上提及的，Mytrix和Inc.在InnoDB上存储了超过1TB的数据，还有一些其他网站使用InnoDB存储引擎处理平均每秒800次插入/更新的操作。

1.4 各种存储引擎之间的比较

特点	MyISAM	BDB	Memory	InnoDB	Archive	NDB
存储限制	no	no	yes	64T	no	yes
事务		√		√
锁粒度	table	page	table	row	row	row
多版本并发控制（MVCC/Snapshot Read）				√	√	√
地理空间的支持（geospatial support）	√
B树索引	√	√	√	√		√
哈希索引			√	√		√
全文索引	√			5.5支持
集群索引（clustered）				√
数据缓存			√	√		√
索引缓存	√		√	√		√
压缩数据	√				√
加密数据	√	√	√	√	√	√
存储成本	low	low	N/A	high	very low	low
内存成本	low	low	medium	high	low	high
批量插入速度	high	high	high	low	very high	high
集群数据库支持						√
复制支持	√	√	√	√	√	√
外键支持				√
备份支持	√	√	√	√	√	√
查询缓存	√	√	√	√	√	√
更新数据字典统计信息	√	√	√	√	√	√