innoDB存储引擎分析与应用优化
zhaoshunyao
一、内部数据结构
innoDB存储引擎在逻辑上将所有数据都存放在一个表空间中，表空间由段和分散的页组成。
innoDB引擎表的逻辑存储结构
图示表明了表空间由各个段组成的，常见的段主要有数据段、索引段、回滚段。
innoDB存储引擎表是索引组织的（基于主键构造的一棵B+树），因此数据即索引，索引即数据。数据段即为B+树的叶节点，索引段即为B+树的非索引节点。可以形象点讲，数据页都被“挂在树上”。
B+树索引又分为聚集索引和非聚集索引，两者内部都是B+树的，即高度平衡的，叶节点存放着所有数据。两者不同的是，前者的叶节点存放的是一整行的数据。
非聚集索引
图2 聚集索引与非聚集索引的关系
每张表只能有一个聚集索引，因为聚集索引就是按照每张表的主键构造的一棵B+树，并且叶节点中存放着整张表的行记录数据。实际的数据页只能按照一棵B+树进行排序。 所以在创建innoDB表时，在每张表中都只能有一个主键，如果在创建表时没有明确地定义主键（Primary Key），则innoDB引擎会自动按照如下方式选择或创建主键： 首先看表中是否有非空唯一索引（Unique NOT NULL）如果有，则该列即为主键。 不符合上述条件，自动创建一个6字节大小的RowID。
在大多情况下，查询优化器非常倾向于采用聚集索引，因为聚集索引能在索引的叶节点上直接找到的数据。另外，由于定义了数据的逻辑顺序，聚集索引能够特别快地访问针对范围值的查询和对基于主键的排序查找。
非聚集索引（辅助索引）
叶节点不包含行的全部数据，叶节点除了包含键值外，每个叶节点中的索引行中还包含一个“书签”，该书签指示innoDB存储引擎在哪里可以找到与索引相对应的行数据。 因此非聚集索引的书签就是相应行数据的聚集索引键。
Primary Key key 非聚集索引的存在并不影响数据在聚集索引中的组织，所以每张表上可以有多个非聚集索引。当通过非聚集索引查找数据时，引擎会遍历非聚集索引并通过叶节点的指针获得指向主键索引的主键，然后再通过主键索引找到一个完整的行记录。 举例来讲，如果在一棵高度为3的非聚集索引树中查找数据，那么需要对这棵非聚集索引树遍历3次找到指定主键，如果聚集索引数高度也是3，那么还需要对聚集索引进行3次查找，才能最终找到一个完整的行数据所在页，因此一共需要6次逻辑IO才访问到最终的一个数据页。
数据页的内部结构
页是innoDB磁盘管理的最小单位（也称块），每页大小为16KB。页内部按行存放多条记录，每个页存放的行记录有硬性定义，最多允许存放16KB/2~200行的记录（即7992行），至少要放两行记录，否则失去了B+树的意义，变成链表了。
从页结构可以知道，根据B+树索引并不能找到一个给定键值的具体行记录，而只能找到被查的数据行所在的页，然后把页读入内存，再在内存中进行查找（二分查找法），最后得到查找的数据。
数据行记录格式
innoDB存储引擎提供了compact和redundant两种格式来存放行记录数据，redundant是了为兼容老版本而保留的，compact是mysql5.0开始引入的（5.1中成为默认的行格式），其设计目标是高效存放数据。可以这么认为，如果一个页中存放的行数据越多其性能就越高。
变长字段长度列表 NULL标志位 记录头信息 列1数据 列2数据 ... ...
compact行记录格式
页内部是通过一种链表的结构连串联各个行记录的。 前面提到innoDB页大小为16KB，即16384字节。但mysql手册上定义的varchar列长度总和为65535字节，显然超出页容量，发生了行溢出。一般情况下，数据都放在B+树的叶节点中，但发生行溢出时，则会把溢出数据存放在uncompress BLOB page（行溢出页）中。
由于innoDB表是索引组织的（B+树结构），因此每个页中至少要有两个行记录，所以如果当页中只能存放下一条记录，那么innoDB引擎会自动将行数据放到溢出页中。
... ... 768字节前缀
图4 行溢出
关于Varchar 与Char字符类型
通常理解varchar是存储变长数据的字符类型，char是存储固定长数据的字符类型。 但实际上mysql在不同的字符集下，char的内部存储的就不是固定长度数据。 比如gbk编码一个汉字占两个字节，而utf-8编码会占三个字节，因此对于多字节字符编码char数据类型存储，innoDB引擎在内部将其视为是变长字符。 因此可以明确一个观点，在多字节字符编码环境，char和varchar的行存储基本没有区别了。 Varchar类型字段长度如果不超过700字节，行记录基本存储在B+树的页上。
二、innoDB表应用优化
1、 主键选择利用
使用场景： Ting音乐社区平台，每首歌曲都有很多评论。 浏览时会执行：SELECT ... FROM ... WHERE song_id= ...
ORDER BY comment_id ...
老的表设计方案：使用了最常见的自增字段comment_id作为主键，同时使用song_id,commtime作为索引。
CREATE TABLE
`comment` (
`comment_id` bigint(20) unsigned NOT NULL auto_increment,
`username` varchar(15) NOT NULL,
`content` varchar(300) NOT NULL,
`song_id` bigint(20) unsigned NOT NULL,
`commtime` int(10) unsigned NOT NULL,
PRIMARY KEY
(`comment_id`),
KEY `song_id` (`song_id`),
KEY `commtime` (`commtime`) ) ENGINE=InnoDB;
数据按照主键进行排序，当执行查询时，根据索引进行数据对位。 由innoDB表引擎结构分析很容易得出这个表的问题所在，同一首歌曲的评论数据，在磁盘上会分散到多个数据页之上。这也就意味着在查询这些数据的时候，磁盘要不断的调整数据定位。这是一个不小的IO开销。
下面改造一下表结构，虽然也使用了自增字段，但是采用的是联合主键song_id, commtime, comment_id，并把comment_id作为索引。 同时需要注意的是，缩减了自增字段的长度，这样，主键的长度会短一些，有助于提升Innodb的性能。
CREATE TABLE `comment` (
`comment_id` int(10) unsigned NOT NULL auto_increment,
`username` varchar(15) NOT NULL,
`content` varchar(300) NOT NULL,
`song_id` int(10) unsigned NOT NULL,
`commtime` int(10) unsigned NOT NULL,
PRIMARY KEY
(`song_id`,`commtime`,`comment_id`),
UNIQUE KEY `comment_id` (`comment_id`) ) ENGINE=InnoDB;
数据按照联合主键进行排序，由于song_id字段是联合主键中的第一个字段，所以对于一首歌曲而言，它所有的评论都保存在同一个页或向近页上，即磁盘中相邻的位置上。 在这种情况下，查询可以直接根据主键定位到数据页，即使跨页定位，Innodb也会进行预读取，所谓预读，指的是当发生一次读操作时，并不一定是直接从文件系统里物理读，而只是从缓冲池中逻辑读，Innodb内部的优化机制可以合并多次逻辑读为一次物理读，从而降低IO消耗，提高磁盘性能。
还有一个问题要考虑，使用song_id, commtime, comment_id联合主键时，如果对一个老歌曲（song_id较小的歌曲）发表评论时，数据会记录在相对靠前的数据页上（因为数据在硬盘上保存的物理顺序是按主键排序的），和直接使用comment_id自增主键相比，这样会带来一些写IO负担，因为自增主键在插入新数据时，新数据始终位于数据文件的结尾。所以，实际应用中，新的方案是否可用，还要根据客观情况分析而定，比如说评论主要集中在新歌曲上，则IO问题不大，因为新歌曲的记录位于数据文件靠后的位置上。如果评论分布的歌曲比较随机的话，那么新方法要值得考虑再变通下了。
2、 索引使用
一般情况下，会认为查询条件下出现的的列（字段）上都要加索引。但有时候并不需求，比如性别字段、地区字段、类型字段、小分类字段等等，他们的可取指范围很小，即低选择性。 那什么时候添加索引合适呢，由B+树特性和经过实际数据测试，可以总结出一个经验是：查询表中很少一部分记录行时才添加索引。 示例： SELECT * FROM user WHERE sex=’man’ 对于性别可取值范围只有’man’,’woman’。查询结果可能是趋近于该表50%的数据，这样sex字段添加索引就完全没必要了。 相反如果某个字段取值范围很广，几乎没有重复（即高选择性），则该字段使用索引是最合适了。比如查询用户名字段，基本上在一个应用中不允许重名出现。 如果使用的字段也是高选择性的，但取得的行记录占全表中大部分数据时，innoDB引擎就不会使用索引了。比如执行下面的语句：SELECT * FROM user WHERE username>’robin’ 如果取出的行占很大比例，索引不再被使用。
经过实际数据测试，大概能得出一个取出比例为20%，即当取出的数据量超过全表中数据的20%，优化器就不会使用索引，而是进行全表扫描。
------------------------------------------------------------------------------------------------------------------------------ 附注： 1、以上是进期针对tingweb产品进行db表优化过程中的心得总结。 随着后续的优化工作，还会继续对innoDB存储引擎进行深入研究。
2、B+树索引性能：在一般的存储应用中（行记录1000万内），B+树的高度一般都在2~3层，也就是查找某一键值的行记录，最多只需要2~3次IO。现在一般的硬盘每秒至少可以做100次IO，2~3次IO大概需要查询0.02~0.03s的时间。
参考资料： 1、 mysql官方手册 2、 百度搜索结果，相关innodb资料文献 3、 Innodb1.0.6源代码原文地址:/doit8791/archive//2522556.html
今天新开始的项目在做数据库设计，发现自己对MySql的varchar类型还不熟悉，故又上网收集资料整理如下。
1.varchar类型的变化
MySQL 数据库的varchar类型在4.1以下的版本中的最大长度限制为255，其数据范围可以是0~255或1~255（根据不同版本数据库来定）。在 MySQL5.0以上的版本中，varchar数据类型的长度支持到了65535，也就是说可以存放65532个字节的数据，起始位和结束位占去了3个字 节，也就是说，在4.1或以下版本中需要使用固定的TEXT或BLOB格式存放的数据可以使用可变长的varchar来存放，这样就能有效的减少数据库文 件的大小。
MySQL 数据库的varchar类型在4.1以下的版本中,nvarchar（存储的是Unicode数据类型的字符）不管是一个字符还是一个汉字,都存为2个字节 ，一般用作中文或者其他语言输入，这样不容易乱码 ;varchar: 汉字是2个字节,其他字符存为1个字节 ，varchar适合输入英文和数字。
4.0版本以下，varchar(20)，指的是20字节，如果存放UTF8汉字时，只能存6个（每个汉字3字节） ；5.0版本以上，varchar(20)，指的是20字符，无论存放的是数字、字母还是UTF8汉字（每个汉字3字节），都可以存放20个，最大大小是65532字节 ；varchar(20)在Mysql4中最大也不过是20个字节,但是Mysql5根据编码不同,存储大小也不同，具体有以下规则：
a) 存储限制
varchar 字段是将实际内容单独存储在聚簇索引之外，内容开头用1到2个字节表示实际长度（长度超过255时需要2个字节），因此最大长度不能超过65535。
b) 编码长度限制
字符类型若为gbk，每个字符最多占2个字节，最大长度不能超过32766;
字符类型若为utf8，每个字符最多占3个字节，最大长度不能超过21845。
若定义的时候超过上述限制，则varchar字段会被强行转为text类型，并产生warning。
c) 行长度限制
导致实际应用中varchar长度限制的是一个行定义的长度。 MySQL要求一个行的定义长度不能超过65535。若定义的表长度超过这个值，则提示
ERROR ): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs。
2.CHAR(M), VARCHAR(M)不同之处
CHAR(M)定义的列的长度为固定的，M取值可以为0～255之间，当保存CHAR值时，在它们的右边填充空格以达到指定的长度。当检索到CHAR值时，尾部的空格被删除掉。在存储或检索过程中不进行大小写转换。CHAR存储定长数据很方便，CHAR字段上的索引效率级高，比如定义char(10)，那么不论你存储的数据是否达到了10个字节，都要占去10个字节的空间,不足的自动用空格填充。
VARCHAR(M)定义的列的长度为可变长字符串，M取值可以为0~65535之间，(VARCHAR的最大有效长度由最大行大小和使用的字符集确定。整体最大长度是65,532字节）。VARCHAR值保存时只保存需要的字符数，另加一个字节来记录长度(如果列声明的长度超过255，则使用两个字节)。VARCHAR值保存时不进行填充。当值保存和检索时尾部的空格仍保留，符合标准SQL。varchar存储变长数据，但存储效率没有CHAR高。如果一个字段可能的值是不固定长度的，我们只知道它不可能超过10个字符，把它定义为 VARCHAR(10)是最合算的。VARCHAR类型的实际长度是它的值的实际长度+1。为什么&+1&呢？这一个字节用于保存实际使用了多大的长度。从空间上考虑，用varchar合适；从效率上考虑，用char合适，关键是根据实际情况找到权衡点。
CHAR和VARCHAR最大的不同就是一个是固定长度，一个是可变长度。由于是可变长度，因此实际存储的时候是实际字符串再加上一个记录字符串长度的字节(如果超过255则需要两个字节)。如果分配给CHAR或VARCHAR列的值超过列的最大长度，则对值进行裁剪以使其适合。如果被裁掉的字符不是空格，则会产生一条警告。如果裁剪非空格字符，则会造成错误(而不是警告)并通过使用严格SQL模式禁用值的插入。
3. VARCHAR和TEXT、BlOB类型的区别
VARCHAR，BLOB和TEXT类型是变长类型，对于其存储需求取决于列值的实际长度(在前面的表格中用L表示)，而不是取决于类型的最大可能尺寸。例如，一个VARCHAR(10)列能保存最大长度为10个字符的一个字符串，实际的存储需要是字符串的长度 ，加上1个字节以记录字符串的长度。对于字符串'abcd'，L是4而存储要求是5个字节。
BLOB和TEXT类型需要1，2，3或4个字节来记录列值的长度，这取决于类型的最大可能长度。VARCHAR需要定义大小，有65535字节的最大限制；TEXT则不需要。如果你把一个超过列类型最大长度的值赋给一个BLOB或TEXT列，值被截断以适合它。
一个BLOB是一个能保存可变数量的数据的二进制的大对象。4个BLOB类型TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB仅仅在他们能保存值的最大长度方面有所不同。
BLOB 可以储存图片,TEXT不行，TEXT只能储存纯文本文件。4个TEXT类型TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT对应于4个BLOB类型，并且有同样的最大长度和存储需求。在BLOB和TEXT类型之间的唯一差别是对BLOB值的排序和比较以大小写敏感方式执行，而对TEXT值是大小写不敏感的。换句话说，一个TEXT是一个大小写不敏感的BLOB。
4.总结char，varchar，text区别
长度的区别，char范围是0～255，varchar最长是64k，但是注意这里的64k是整个row的长度，要考虑到其它的column，还有如果存在not null的时候也会占用一位，对不同的字符集，有效长度还不一样，比如utf8的，最多21845，还要除去别的column，但是varchar在一般情况下存储都够用了。如果遇到了大文本，考虑使用text，最大能到4G。
效率来说基本是char&varchar&text，但是如果使用的是Innodb引擎的话，推荐使用varchar代替char
char和varchar可以有默认值，text不能指定默认值
数据库选择合适的数据类型存储还是很有必要的，对性能有一定影响。这里在零碎记录两笔，对于int类型的，如果不需要存取负值，最好加上unsigned；对于经常出现在where语句中的字段，考虑加索引，整形的尤其适合加索引。
本文已收录于以下专栏：
相关文章推荐
之前的工作中遇到一个需要保存网页的需求，在开发过程中遇到乱码问题，我在将整个网页源码保存在数据库表中。数据库为mysql该字段的类型blob.
在从数据库读取时是保存内容全部为乱码，最后在网上找到一...
1.blob是二进制大对象,可以容纳可变量数量的数据,
其中blob分为4中类型:TINYBLOB,BLOB,mediumblob和LongBlob,他们容纳的长度是不同的.
Text同样也分为四...
MySQL存在text和blob:（1）相同在TEXT或BLOB列的存储或检索过程中，不存在大小写转换,当未运行在严格模式时，如果你为BLOB或TEXT列分配一个超过该列类型的最大长度的值值，值被截取...
原文地址：/lijianwei_123/blog/static/5/
1、 VARCHAR，BLOB和T...
区别* TEXT和BLOB家族之间仅有的不同是BLOB类型存储的是二进制数据，没有排序规则或字符集，而TEXT类型有字符集或排序规则。说白了如果要储存中文则选择TEXT。默认值不能设置默认值，否则会报...
文章出处：http://baifjece./blog/static//
储存不区分大小写的字符数据
TINYTEX...
1、CHAR。CHAR存储定长数据很方便，CHAR字段上的索引效率级高，比如定义char(10)，那么不论你存储的数据是否达到了10个字节，都要占去10个字节的空间,不足的自动用空格填充，所以在读取的...
Unicode字符集就是为了解决字符集这种不兼容的问题而产生的，它所有的字符都用两个字节表示，即英文字符也是用两个字节表示
如果还为了这个纠结，就直接看看后面的解说，做决定吧。
一般如果用到中...
在数据库设计时我们常常为数据类型而烦恼，但是当你熟悉了之后就不再烦恼了，看看吧，看看对你有没有帮助。
char,varchar,text ,nchar,nvarchar,ntext 等学习拾遗，...
他们的存储方式和数据的检索方式都不一样。数据的检索效率是：char&varchar&text空间占用方面，要具体情况具体分析了。CHAR(M)M个字节，0 Char为定长，varchar，text为变...
他的最新文章
讲师：刘文志
讲师：陈伟
您举报文章：
举报原因：
原文地址：
原因补充：
(最多只允许输入30个字)varchar鍜宼ext璇翠笉娓呯殑閭ｄ簺浜