Database Indices_MySQL

数据库table的index是建立在一个或多个column上的一个数据结构, 选定的一个或若干个column称作index的key, 用来加快相应key所对应的record(tuple)的定位.

从数据结构的角度来看, 索引是一个map, 将key映射到对应的record的指针. 索引能提供更好的查找性能, 关键之处在于, 一个block可以存储的(key, pointer_to_record)是可以很多的, 要远大于一个block存储的record的个数, 这意味着查找过程中磁盘io可以大大减少.索引可以分为dense index和sparse index, 前者对于每个record都建立索引, 后者只对一个block上存储的多个record中的某一个(如第一个)建立索引.

最直接的构建索引想法就是将key所在的column提取出来, 排序之后存储起来即可. 之后, 查找过程就可以二分来进行. 如果索引本身也比较大, 那进一步可以对索引再做索引, 沿着这个思路走下去, 就得到了B树了, 下图是一棵B+树.

Non-clusteredIndex:record本身不按照该index排序(当然, index内的key是排序的), 只不过index内的指针指向了不同的record位置.

ClusteredIndex:record按照该index的key来排序, 即存储在data block里面的record是按照这个index排序的. 换句话说,这个index的key决定了record是如何存储的.

实例分析

MicrosoftSQL Server 2000

1, 如何创建index,参见http://msdn.microsoft.com/en-us/library/aa258260(v=SQL.80).aspx

2, SQL Server 2000中(后续版本未确认), 如果没有创建 clustered index, 创建primary key的时候会自动创建clustered index. 更多关于clustered index, 参见

3, clustered index与non-clusteredindex都是用B-tree实现的, 参见http://msdn.microsoft.com/en-us/library/aa174523(v=SQL.80).aspx

与http://msdn.microsoft.com/en-us/library/aa174537(v=SQL.80).aspx

4, Non-clustered index中, 如果这张表有clustered index, non-clustered index的pointer存储的是clustered index key (因此clustered index key应该尽量小).

MySQLInnoDB & MyISAM

InnoDB的做法和上面提到的SQL Server的做法差不多:索引都是B树, 用primary key当clustered index, secondary-index中的recordlocator是clustered index key等. 稍有不同的是, InnoDB在没有合适的column充当cluster key的时候, 会自动创建一个column来作为cluster index key column, 参见http://dev.mysql.com/doc/refman/5.5/en/innodb-index-types.html

MySQL的另一个存储引擎, MyISAM, 做法就土了. MyISAM中, 没有clusteredindex, 所有的record locator都直接指向record的位置. InnoDB与MyISAM在index上的对比参见http://www.xaprb.com/blog/2006/07/04/how-to-exploit-mysql-index-optimizations/

ClusteredIndex与record的插入

Clustered Index要求record按照cluster index key的值来排序, 因此, 插入过程首先是一个查找的过程, 找到对应的位置以后, 除了在data block中插入这个record(可能要引起block split, 因为这个block快满了), 还要在index里也插入这个key,同样也可能引起block split.

同理, 删除的时候也会有这样的问题.

也正是这个原因, SQL Server和InnoDB的secondary index的record locator存储的都是clustered index key, 这样, secondary index就独立出去了, 不用每次更新都要更新所有的index. 代价是secondary index查完以后, 还要再拿得到的key再走一遍clusteredindex, 不过clustered index基本上都在内存里面了, 而且就是用来做快速访问的(良好优化过了), 所以仍然是值得的.