【学习笔记】mysql索引原理之聚簇索引

  1. 云栖社区>
  2. 博客>
  3. 正文

【学习笔记】mysql索引原理之聚簇索引

般若灵芝 2018-10-18 10:48:22 浏览823
展开阅读全文

索引分为聚簇索引和非聚簇索引。

以一本英文课本为例,要找第8课,直接翻书,若先翻到第5课,则往后翻,再翻到第10课,则又往前翻。这本书本身就是一个索引,即“聚簇索引”。

如果要找"fire”这个单词,会翻到书后面的附录,这个附录是按字母排序的,找到F字母那一块,再找到"fire”,对应的会是它在第几课。这个附录,为“非聚簇索引”。

由此可见,聚簇索引,索引的顺序就是数据存放的顺序,所以,很容易理解,一张数据表只能有一个聚簇索引。

聚簇索引要比非聚簇索引查询效率高很多,特别是范围查询的时候。所以,至于聚簇索引到底应该为主键,还是其他字段,这个可以再讨论。

1、MYSQL的索引

mysql中,不同的存储引擎对索引的实现方式不同,大致说下MyISAM和InnoDB两种存储引擎。

MyISAM的B+Tree的叶子节点上的data,并不是数据本身,而是数据存放的地址。主索引和辅助索引没啥区别,只是主索引中的key一定得是唯一的。这里的索引都是非聚簇索引。

MyISAM还采用压缩机制存储索引,比如,第一个索引为“her”,第二个索引为“here”,那么第二个索引会被存储为“3,e”,这样的缺点是同一个节点中的索引只能采用顺序查找。

a7554600baa1cd11e027796db912c8fcc2ce2d77

InnoDB的数据文件本身就是索引文件,B+Tree的叶子节点上的data就是数据本身,key为主键,这是聚簇索引。非聚簇索引,叶子节点上的data是主键(所以聚簇索引的key,不能过长)。为什么存放的主键,而不是记录所在地址呢,理由相当简单,因为记录所在地址并不能保证一定不会变,但主键可以保证。

至于为什么主键通常建议使用自增id呢?

2、聚簇索引

聚簇索引的数据的物理存放顺序与索引顺序是一致的,即:只要索引是相邻的,那么对应的数据一定也是相邻地存放在磁盘上的。如果主键不是自增id,那么可以想象,它会干些什么,不断地调整数据的物理地址、分页,当然也有其他一些措施来减少这些操作,但却无法彻底避免。但,如果是自增的,那就简单了,它只需要一页一页地写,索引结构相对紧凑,磁盘碎片少,效率也高。

聚簇索引不但在检索上可以大大滴提高效率,在数据读取上也一样。比如:需要查询f~t的所有单词。

一个使用MyISAM的主索引,一个使用InnoDB的聚簇索引。两种索引的B+Tree检索时间一样,但读取时却有了差异。

因为MyISAM的主索引并非聚簇索引,那么他的数据的物理地址必然是凌乱的,拿到这些物理地址,按照合适的算法进行I/O读取,于是开始不停的寻道不停的旋转。聚簇索引则只需一次I/O。

不过,如果涉及到大数据量的排序、全表扫描、count之类的操作的话,还是MyISAM占优势些,因为索引所占空间小,这些操作是需要在内存中完成的。

鉴于聚簇索引的范围查询效率,很多人认为使用主键作为聚簇索引太多浪费,毕竟几乎不会使用主键进行范围查询。但若再考虑到聚簇索引的存储,就不好定论了。

网友评论

登录后评论
0/500
评论
般若灵芝
+ 关注