月度归档:2017年01月

mysql数据库中聚簇索引与非聚簇索引的区别

聚簇索引是对磁盘上实际数据重新组织以按指定的一个或多个列的值排序的算法。特点是存储数据的顺序和索引顺序一致。
一般情况下主键会默认创建聚簇索引,且一张表只允许存在一个聚簇索引。

聚簇索引的叶子节点就是数据节点,找到了叶子节点就可以找到数据节点。

非聚簇索引的叶子节点仍然是索引节点,有指向对应数据块的指针。

以上的说法比较抽象,简单的说,比如翻一本书,聚簇索引就是找到100页,如果找到50页就往后翻,超过100页就往前翻,就可以看到内容,而非聚簇索引就是,要找某一章节需先查看目录在定位到页码,目录就是这个非聚簇索引。由此可见,聚簇索引,索引的顺序就是数据存放的物理顺序,所以,很容易理解,一张数据表只能有一个聚簇索引。
聚簇索引要比非聚簇索引查询效率高很多,特别是范围查询的时候。

  1. MYSQL的索引
    mysql中,不同的存储引擎对索引的实现方式不同,大致说下MyISAM和InnoDB两种存储引擎。
    MyISAM的BTree的叶子节点上的data,并不是数据本身,而是数据存放的地址(磁盘上的数据块block)。主索引和辅助索引没啥区别,只是主索引中的key一定得是唯一的。这里的索引都是非聚簇索引。MyISAM还采用压缩机制存储索引,比如,第一个索引为“her”,第二个索引为“here”,那么第二个索引会被存储为“3,e”,这样的缺点是同一个节点中的索引只能采用顺序查找。
    MYISAM引擎是按列值与行号来组织索引的。它的叶子节点中保存的实际上是指向存放数据的物理块的指针。查看MYISAM引擎的数据文件可以看出他的的索引文件(.MYI)和数据文件(.MYD)是相互独立的。                                                                                                                                                                                                                            InnoDB的数据文件本身就是索引文件,BTree的叶子节点上的data就是数据本身,key为主键,这是聚簇索引。非聚簇索引,叶子节点上的data是主键(所以聚簇索引的key,不能过长)。为什么存放的主键,而不是记录所在地址呢,理由相当简单,因为记录所在地址并不能保证一定不会变,但主键可以保证。
    至于为什么主键通常建议使用自增id呢?
  2. 聚簇索引
    聚簇索引的数据的物理存放顺序与索引顺序是一致的,即:只要索引是相邻的,那么对应的数据一定也是相邻地存放在磁盘上的。如果主键不是自增id,那么可以想象,它会干些什么,不断地调整数据的物理地址、分页,当然也有其他一些措施来减少这些操作,但却无法彻底避免。但,如果是自增的,那就简单了,它只需要一页一页地写,索引结构相对紧凑,磁盘碎片少,效率也高。
    聚簇索引不但在检索上可以大大滴提高效率,在数据读取上也一样。比如:需要查询f~t的所有单词。
    一个使用MyISAM的主索引,一个使用InnoDB的聚簇索引。两种索引的B+Tree检索时间一样,但读取时却有了差异。
    因为MyISAM的主索引并非聚簇索引,那么他的数据的物理地址必然是凌乱的,拿到这些物理地址,按照合适的算法进行I/O读取,于是开始不停的寻道不停的旋转。聚簇索引则只需一次I/O。
    不过,如果涉及到大数据量的排序、全表扫描、count之类的操作的话,还是MyISAM占优势些,因为索引所占空间小,这些操作是需要在内存中完成的。
    鉴于聚簇索引的范围查询效率,很多人认为使用主键作为聚簇索引太多浪费,毕竟几乎不会使用主键进行范围查询。但若再考虑到聚簇索引的存储,就不好定论了。