澄清一个概念:innodb中,在聚簇索引之上创建的索引称之为辅助索引,辅助索引访问数据总是需要二次查找,非聚簇索引都是辅助索引,像复合索引、前缀索引、唯一索引,辅助索引叶子节点存储的不再是行的物理位置,而是主键值
由于聚簇索引是将数据跟索引结构放到一块,因此一个表仅有一个聚簇索引
聚簇索引默认是主键 ,如果表中没有定义主键,InnoDB 会选择一个 唯一的非空索引 代替。如果没有这样的索引,InnoDB 会 隐式定义一个主键 来作为聚簇索引。InnoDB 只聚集在同一个页面中的记录。包含相邻健值的页面可能相距甚远。 如果你已经设置了主键为聚簇索引,必须先删除主键,然后添加我们想要的聚簇索引,最后恢复设置主键即可 。
此时其他索引只能被定义为非聚簇索引。这个是最大的误区。有的主键还是无意义的自动增量字段,那样的话Clustered index对效率的帮助,完全被浪费了。
刚才说到了,聚簇索引性能最好而且具有唯一性,所以非常珍贵,必须慎重设置。 一般要根据这个表最常用的SQL查询方式来进行选择,某个字段作为聚簇索引,或组合聚簇索引 ,这个要看实际情况。
记住我们的 最终目的 就是 在相同结果集情况下,尽可能减少逻辑IO 。
MyISM使用的是非聚簇索引, 非聚簇索引的两棵B+树看上去没什么不同 ,节点的结构完全一致只是存储的内容不同而已,主键索引B+树的节点存储了主键,辅助键索引B+树存储了辅助键。表数据存储在独立的地方,这两颗B+树的叶子节点都使用一个地址指向真正的表数据,对于表数据来说,这两个键没有任何差别。由于 索引树是独立的,通过辅助键检索无需访问主键的索引树 。
看上去聚簇索引的效率明显要低于非聚簇索引,因为 每次使用辅助索引检索都要经过两次B+树查找 ,这不是多此一举吗?聚簇索引的优势在哪?
所以建议使用int的auto_increment作为主键
主键的值是顺序的,所以 InnoDB 把每一条记录都存储在上一条记录的后面。当达到页的最大填充因子时(InnoDB 默认的最大填充因子是页大小的 15/16,留出部分空间用于以后修改),下一条记录就会写入新的页中。一旦数据按照这种顺序的方式加载,主键页就会近似于被顺序的记录填满(二级索引页可能是不一样的)
聚簇索引的数据的物理存放顺序与索引顺序是一致的 ,即: 只要索引是相邻的,那么对应的数据一定也是相邻地存放在磁盘上的 。如果主键不是自增id,那么可以想 象,它会干些什么,不断地调整数据的物理地址、分页,当然也有其他一些措施来减少这些操作,但却无法彻底避免。但,如果是自增的,那就简单了,它只需要一 页一页地写,索引结构相对紧凑,磁盘碎片少,效率也高。
因为 MyISAM的主索引并非聚簇索引,那么他的数据的物理地址必然是凌乱的,拿到这些物理地址,按照合适的算法进行I/O读取,于是开始不停的寻道不停的旋转 。 聚簇索引则只需一次I/O 。(强烈的对比)
不过,如果 涉及到大数据量的排序、全表扫描、count之类的操作的话,还是MyISAM占优势些,因为索引所占空间小,这些操作是需要在内存中完成的 。
聚簇索引 默认是主键 ,如果表中没有定义主键,InnoDB 会选择一个 唯一的非空索引 代替。如果没有这样的索引,InnoDB 会 隐式定义一个主键 来作为聚簇索引。 InnoDB 只聚集在同一个页面中的记录。包含相邻健值的页面可能相距甚远。