- 1.什么是聚簇索引?
不是一种单独的索引方式,而是一种数据存储方式,innoDB的聚簇索引实际上在同一个结构中保存了B-tree索引和数据行;
当表有聚簇索引时,它的数据行实际上存放在索引的叶子页上,叶子页包含了行的全部数据,而节点页只包含索引列;"聚簇"表示数据行和相邻的键值紧凑地存储在一起,因为无法同时把数据行存放在两个不同的地方,所以一个表只能有一个聚簇索引,类似于oracle的索引组织表;
下图便展示了mysql的Innodb主键索引的原理图:
主键索引的原理图.png
在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶子页节点data域保存了完整的数据记录，这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引，所以必须有主键，也就是说innoDB通过主键聚集数据，如果没有显示定义，innoDB会选择一个唯一的非空索引代替.如果没有这样的索引,innoDB会隐式定义一个主键来作为聚簇索引.
- 2.聚簇索引的优点和缺点
优点:
1 .可以把相关的数据保存在一起;比如根据用户ID来聚集邮件数据;
2.数据访问更快,聚簇索引将索引和数据保存在同一个B-tree中,因此聚簇索引中获取数据通常比在非聚簇索引中查找要快。
3.使用覆盖索引(索引列和查询列一致)扫描的查询可以直接使用聚簇索引页节点中的主键值;
缺点:
1.聚簇数据最大限度地提高了I/O密集型应用的性能,但如果数据全部都放在内存中,则访问的顺序就没那么重要了;
2.插入速度严格依赖于插入顺寻,按照主键的顺序插入是加载到innodb;如果不是按照主键顺序加载数据,那么加载完成后最好使用OPTIMIZE TABLE TABLENAME命令重新组织一下表。
3.更新聚簇索引列的代价很高,因为会强制innodb将每个被更新的行移动到新的位置。
4.基于聚簇索引的表在插入新行,或者主键被更新导致需要移动行的时候,可能面临“页分裂”的问题.当行的主键值要求必须将这一行插入到某个已满的页中时,存储引擎会将该页分裂成两个页面来容纳该行,这就是一次页分裂操作,页分裂会导致表占用更多的磁盘空间;
5.聚簇索引可能导致全表扫描变慢,尤其是行比较疏松,或者由于页分裂导致数据存储不连续的时候;
6.二级索引(非聚簇索引) 可能比想象的要更大,因为在二级索引的叶子节点包含了引用行的主键列;二级索引访问需要两次索引查找,而不是一次;
不应该适用二级索引,应该使用hash索引;
-
3.MyISAM支持的索引
MyISAM是按照数据插入的顺序存储在磁盘上的，叶子节点存储数据行的指针引用，隐藏了行的物理细节 ，只显示索引中的节点，索引中的每个叶子节点包含了具体行的指针引用。其原理如图所示:
MyISAM支持的索引.png
由此可得出:MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址；
-
4.InnoDB和MyISAM的二级索引
innoDB二级索引的叶子节点中存储的不是“行指针”，而是主键值，并以此作为指向行的指针，如图所示:
InnoDB主键索引与二级索引示意图.png
这样做有什么好处嘞?
这样就减少了出现当行移动或者数据页分裂时二级索引的维护工作，使用主键值当作指针会让二级索引占用更多的空间，但是，在移动行时无需更新二级索引中的这个主键值呀。
解释下页分裂:页是InnoDB存储引擎管理数据库的最小磁盘单位，类型为B-Tree 节点的页，存放的即是表中行的实际数据了,InnoDB中的页大小为16KB，负载因子是15，且不可以更改,当超过这个数据就会进行页分裂，且分裂时InnoDB一般会记录下一个页的数据信息。
页分裂带来的问题:页会变得稀疏且被不规则的填充,导致最终数据碎片化。
解决:OPTIMIZE TABLE 来重建表并优化页的填充。 -
5.MyISAM的二级索引
MyISAM中，主索引和辅助索引(Secondary key)在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key是可以重复的，如图所示:
MyISAM二级索引.png
这样相对于使用B+的InnoDB可通过辅助索引快速找到所有的数据，而不需要再遍历一边主键索引，所以适用于OLAP。
以上便是对索引的相关补充，后续将会对索引优化继续展开书写...
网友评论