Innodb 表和索引结构

作者:吴炳锡 来源:http://www.mysqlsupport.cn/ 联系方式: wubingxi#gmail.com 转载请注明作/译者和出处,并且不能用于商业用途,违者必究.

表的结构:

对于MySQL把有的存储引擎都是把表结构的定义存放到.frm文件中。但对于Innodb表同时有一个内部的字典存放到表空间中。所以对于Innodb表不能单纯的移动.frm在不同的MySQL事例下。对于Innodb引擎的表,如果MySQL 删除相应的表或数据库,同时会删除相应的.frm及在表空间的相应的字典信息。在.frm文件只是用来定义表的结构,Innodb把数据和索引都存放到了表空间中。

聚集索引和次要索引:

每一个Innodb表都有一个聚集索引,这个聚信索引和行数据存在一起。

可以用来做聚集索引的列:

  • 如果有声明了主建(primary key),则这个列可以做为表的主建。
  • 如果没有声明主建,MySQL会用一个唯一索引(UNIQUE)而且是不为空的列做为主建,成为该表的聚信索引。
  • 如果没声明主建,同时也没合适的唯一的索引,Innodb内部会产生一个隐藏的聚集索引:RowID。这个RowID在Innodb的多版本中曾提到过。这个RowID是在插入时产生,并且是自增的增加。所以也是按顺序增长存放。

由于聚集索引和行数据存放一起(在同一个数据页中),所以利用聚集索引访问数据行时,非常的快,同一个数据页在访问索引时,已经把页加载到Buffer中,在访问数据时,等于了一个顺序IO的访问(内存中完成)。大多数情况下索引和数据都不在一块(MyISAM,数据和索引存到不同的文件中),而聚集索引是有结构的通常是按顺序存放,同时和数据存放在一起,利用索引索引访问大表的数据可以节省许多IO。

对于Innodb次要的索引会包含聚信索引,查询在使用次要索引时,找到聚集索引信息,然后利用聚集索引信息访问行。所以,如果聚集索引过长,会造成空间浪费严重。另外,如果对表或是区间进行Count操作的话,大多数情况较短的次要索引比基于聚集索引快。对于Innodb的聚集索引选择,尽量选择比较短的列做为聚集索引列,是一个好的设计习惯。

索引的物理结构:

Innodb的索引以B-tree的形式存到各个叶点上。索引叶点页的大小默认为16K,当有什么的索引插入叶点时,该叶点至少会保留1/16的空闲空间,用于将来该叶点的索引更新或是插入。

对于顺序写入的索引(无论是递增或是递减,顺序的就行),索引叶点可以达到15/16满。如果是随机的索引写入行为,叶点只会达到1/2到15/16满。当叶点填充在1/2以下满,或是被删除到1/2下满时,Innodb会缩短索引树,试图释放该叶点,该叶点可以被继续写入数据。

设计中的Tips:

因为Innodb表的数据是依赖于聚集索引顺序存放,同时聚集索引和数据一块存储,普通索引也需要存放一份聚集索引。所以对于聚集索引的设计尽量按顺序写入,必免数据分页,行迁移等对性能影响的现象。另外聚集索引要设计的尽可能短。从设计上必须锁的时间,大量随机IO的出现。
如对于监控(或是股票类的信息)可以利用时间和类型构成聚集索引,让相关性高的数据尽可能位到一块。以便读取时可以利用顺序IO读取到相应的数据。最好的情况,相关性高的数据在一个Page上,这样读取的效果更好。基于Innodb聚集索引的特性,在设计上也需要考虑利用一下优势,必免其不好的一方面从而达到最佳性能。