PHP经验分享MySQL学习笔记

DAY5:MySQL聚簇索引

2018-07-20  本文已影响11人  蚂蚁窝大梦想

一、定义及说明

二、特点

聚簇索引数据的物理存放顺序与索引顺序是一致的,即:索引和对应的数据一定是相邻地存放在磁盘上的【保存在同一个B-Tree树】。聚簇索引比非聚簇索引查询效率高很多。

非聚簇索引,类似于图书的附录,那个专业术语出现在那个章节,这些专业术语是有顺序的,但是出现的位置是没有顺序的。

一个表只能有一个聚簇索引,但一个表可以有多个非聚簇索引。
下图为聚簇索引存放:


MySQL聚簇索引存放.png

三、聚簇数据利弊

优点

缺点

OPTIMIZE TABLE只对MyISAM, BDB和InnoDB表起作用;利用未使用的空间,释放出来,并整理数据文件的碎片
注意:在OPTIMIZE TABLE运行过程中,MySQL会锁定表
pt-online-schema-change可以跳过锁表的坑

二级索引两次查找原因:
二级索引叶子节点保存的是行的主键值,而不是保存的指向行的物理位置的指针。这意味着通过二级索引查找行,存储引擎需先找到二级索引的叶子节点获得对应主键值,然后根据主键值取聚簇索引中找到对应的行。
覆盖索引可以避免二次查找。自适应哈希索引能够减少这样的重复工作。

四、InnoDB聚簇索引 和 MyISAM非聚簇索引

InnoDB聚簇索引

1、InnoDB-主键分布


InnoDB-主键分布.png

上图可知:
聚簇索引的每一个叶子节点都包含主键值(col1)、事务ID、用于事务和MVCC的回滚指针以及所有的剩余列(col2)。

2、InnoDB二级索引分布


InnoDB二级索引分布.png

InnoDB二级索引的叶子节点中存储的不是‘行指针’,而是主键值,并以此作为指向行的‘指针’。这样策略减少当出现行移动或页分裂时二级索引的维护工作。
主键值当索引占用更多的空间,换来好处是,InnoDB在移动行时无需更新二级索引中的这个‘指针’。
上图(InnoDB二级索引分布.png):每一个叶子节点包含了索引列(col2),主键值(col1)。

3、展示B-Tree的叶子节点结构(InnoDB和MyISAM保存数据和索引区别)


InnoDB-MyISAM聚簇索引和非聚簇索引表的对比.png

五、InnoDB表中按主键顺序插入行

InnoDB聚簇索引插入顺序索引值.png InnoDB聚簇索引插入无需值.png

从以上2图得出结论:
使用InnoDB时尽可能按主键顺序插入数据,尽可能功能使用单调增加的聚簇索引值插入新行。

六、主键&自增热点-顺序主键更坏结果

说明:innodb_autoinc_lock_mode这个参数控制着在向有auto_increment 列的表插入数据时,相关锁的行为;

上一篇 下一篇

猜你喜欢

热点阅读