MySQL学习笔记--InnoDB存储引擎的索引的问题

philip_zhong · 发表于 2011-11-8 17:07

本帖最后由 philip_zhong 于 2011-11-8 17:08 编辑

在给你贴下Cluster (Oracle)的解释

In Oracle database, multiple tables can be joined into a cluster (not to be confused with clustered index described above). The records for the tables sharing the value of a cluster key shall be stored together in the same or nearby data blocks. This may improve the joins of these tables on the cluster key, since the matching records are stored together and less I/O is required to locate them.
The data layout in the tables which are parts of the cluster is defined by the cluster configuration. A cluster can be keyed with a B-Tree index or a hash table. The data block in which the table record will be stored is defined by the value of the cluster key.

philip_zhong · 发表于 2011-11-8 17:32

Oracle Index-organized tables (IOTs) are a unique style of table structure that is stored in a B-tree index structure. Besides storing the primary key values of an Oracle indexed-organized tables row, each index entry in the B-tree also stores the non-key column values. Oracle Indexed-organized tables provide faster access to table rows by the primary key or any key that is a valid prefix of the primary key. Because the non-key columns of a row are present in the B-tree leaf block itself, there is no additional block access for index blocks.

icer_repls · 发表于 2011-11-8 19:06

本帖最后由 icer_repls 于 2011-11-8 19:09 编辑

philip_zhong 发表于 2011-11-8 17:05
@icer_repls,mysql中，mysql innodb的pk默认就是cluster index，并且叶子节点上是挂接数据的，而普通索引 ...

thxs.

我理解你的意思。
1.cluster index就是数据在物理磁盘上的存储和索引组织顺序一模一样。然后由于MySQL中不存在rowid，也就是叶子节点上不是放得rowid，而是实际的每一行的数据，那么在定位叶子节点时，不要再像oracle那样通过rowid再去读物理磁盘。这样确实是效率高，但是跟IOT应该一样吧，在效率上。

2.mysql innodb只要创建了主键，那么默认的就会创建cluster index。那么如果表没有创建主键（当然实际可能出现得比较少，我是假设），那么这个表的数据在物理磁盘上的组织是不是就是无序的呢？只有创建了主键的表的数据在物理磁盘上的组织才是有序的？

jinguanding · 发表于 2011-11-8 21:23

icer_repls 发表于 2011-11-8 19:06
thxs.

我理解你的意思。

www.mysqlops.com上曾经写过一篇文章：InnoDB主键选型

先回答你这个问题：
1.有主键则是cluster index
2.无主键存在NOT NULL的UNIQUE INDEX则作为cluster index
3.无主键无符合要求的UNIQUE INDEX，则默认创建一个6个字节的字段，内部使用作为cluster index，可以理解为这是类似Oracle 或SQL Server 的rowid