(nnoDB存储引擎分析与应用优化.docxVIP

innoDB存储引擎分析与应用优化2011-5-28 zhaoshunyao一、内部数据结构innoDB存储引擎在逻辑上将所有数据都存放在一个表空间中，表空间由段和分散的页组成。图1 innoDB引擎表的逻辑存储结构图示表明了表空间由各个段组成的，常见的段主要有数据段、索引段、回滚段。innoDB存储引擎表是索引组织的（基于主键构造的一棵B+树），因此数据即索引，索引即数据。数据段即为B+树的叶节点，索引段即为B+树的非索引节点。可以形象点讲，数据页都被“挂在树上”。B+树索引又分为聚集索引和非聚集索引，两者内部都是B+树的，即高度平衡的，叶节点存放着所有数据。两者不同的是，前者的叶节点存放的是一整行的数据。图2 聚集索引与非聚集索引的关系聚集索引每张表只能有一个聚集索引，因为聚集索引就是按照每张表的主键构造的一棵B+树，并且叶节点中存放着整张表的行记录数据。实际的数据页只能按照一棵B+树进行排序。所以在创建innoDB表时，在每张表中都只能有一个主键，如果在创建表时没有明确地定义主键（Primary Key），则innoDB引擎会自动按照如下方式选择或创建主键：首先看表中是否有非空唯一索引（Unique NOT NULL）如果有，则该列即为主键。不符合上述条件，自动创建一个6字节大小的RowID。在大多情况下，查询优化器非常倾向于采用聚集索引，因为聚集索引能在索引的叶节点上直接找到的数据。另外，由于定义了数据的逻辑顺序，聚集索引能够特别快地访问针对范围值的查询和对基于主键的排序查找。非聚集索引（辅助索引）叶节点不包含行的全部数据，叶节点除了包含键值外，每个叶节点中的索引行中还包含一个“书签”，该书签指示innoDB存储引擎在哪里可以找到与索引相对应的行数据。因此非聚集索引的书签就是相应行数据的聚集索引键。非聚集索引的存在并不影响数据在聚集索引中的组织，所以每张表上可以有多个非聚集索引。当通过非聚集索引查找数据时，引擎会遍历非聚集索引并通过叶节点的指针获得指向主键索引的主键，然后再通过主键索引找到一个完整的行记录。举例来讲，如果在一棵高度为3的非聚集索引树中查找数据，那么需要对这棵非聚集索引树遍历3次找到指定主键，如果聚集索引数高度也是3，那么还需要对聚集索引进行3次查找，才能最终找到一个完整的行数据所在页，因此一共需要6次逻辑IO才访问到最终的一个数据页。数据页的内部结构页是innoDB磁盘管理的最小单位（也称块），每页大小为16KB。页内部按行存放多条记录，每个页存放的行记录有硬性定义，最多允许存放16KB/2~200行的记录（即7992行），至少要放两行记录，否则失去了B+树的意义，变成链表了。从页结构可以知道，根据B+树索引并不能找到一个给定键值的具体行记录，而只能找到被查的数据行所在的页，然后把页读入内存，再在内存中进行查找（二分查找法），最后得到查找的数据。数据行记录格式innoDB存储引擎提供了compact和redundant两种格式来存放行记录数据，redundant是了为兼容老版本而保留的，compact是mysql5.0开始引入的（5.1中成为默认的行格式），其设计目标是高效存放数据。可以这么认为，如果一个页中存放的行数据越多其性能就越高。图3 compact行记录格式页内部是通过一种链表的结构连串联各个行记录的。前面提到innoDB页大小为16KB，即16384字节。但mysql手册上定义的varchar列长度总和为65535字节，显然超出页容量，发生了行溢出。一般情况下，数据都放在B+树的叶节点中，但发生行溢出时，则会把溢出数据存放在uncompress BLOB page（行溢出页）中。由于innoDB表是索引组织的（B+树结构），因此每个页中至少要有两个行记录，所以如果当页中只能存放下一条记录，那么innoDB引擎会自动将行数据放到溢出页中。innoDB行图4 行溢出关于Varchar 与Char字符类型通常理解varchar是存储变长数据的字符类型，char是存储固定长数据的字符类型。但实际上mysql在不同的字符集下，char的内部存储的就不是固定长度数据。比如gbk编码一个汉字占两个字节，而utf-8编码会占三个字节，因此对于多字节字符编码char数据类型存储，innoDB引擎在内部将其视为是变长字符。因此可以明确一个观点，在多字节字符编码环境，char和varchar的行存储基本没有区别了。Varchar类型字段长度如果不超过700字节，行记录基本存储在B+树的页上。二、innoDB表应用优化主键选择利用使用场景：Ting音乐社区平台，每首歌曲都有很多评论。浏览时会执行：SELECT ... FROM ... WHERE song_id= ... ORDER BY comment_