联合索引、聚簇索引

版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/liujia110/article/details/62282690

1、联合索引
1.1、多个独立索引很难形成三星索引
1.2、当出现服务器对多个索引做相交操作(and),通常异味着需要创建联合索引来取代多个独立的单列索引
1.3、当出现服务器对多个索引做联合操作(OR),通常需要耗费大量的CPU或者内存资源在算法的缓存、排序和合并操作上,特别是当有些索引列的选择性不高的时候,会返回大量数据进行合并操作
1.4、优化器不会把1.3中的排序、合并等操作计算到查询成本中,优化器只关心随机页读取成本。这样可能导致该执行计划不如全表扫描,导致查询成本被低估
1.5、遵循最左前缀排序,当不需要考虑排序和分组时,采用将选择性最高的列放在最左边通常是最好的选择。
2、聚簇索引
2.1、聚簇索引是一种数据存储方式,将索引与数据存储在同一个叶子节点中。聚簇索引由搜索引擎负责实现。(如图1)
2.2、聚簇索引优点:
2.2.1、把相关数据保存在一起,因为mysql数据库读取数据是按照页读取的,当读取某一个用户数据时,相邻的数据也会加载到内存中。根据用户读取一个id的数据时,相邻数据被读取的可能性会非常高,这种按页加载就减少了IO操作
2.2.2、数据访问更快
2.2.3、使用覆盖索引扫描的查询可以直接使用叶节点中的主键值
2.3、聚簇索引缺点:
2.3.1、聚簇索引最大限度提高了IO密集型应用性能,但是当数据都在内存中时,聚簇索引优势就没有了
2.3.2、插入速度严重依赖插入顺序。如果不是按照顺序插入,可能导致数据的移动设置页分裂,从而影响性能
2.3.3、更新聚簇索引的代价非常高,因为会强制INNODB将每个给跟新的行移动到新的位置上去
2.3.4、聚簇索引插入新列或者更新聚簇索引的时候可能导致页分裂
2.3.5、聚簇索引可能导致全表扫描变慢,尤其是行比较稀疏或者由于页分裂导致数据存储不连续
2.3.6、二级索引需要的存储空间更大,因为二级索引中包含了主键列,同时二级索引需要两次查询才能查询到行数据。(如图2)
这里写图片描述
(图一)
这里写图片描述
(图二)
2.4、InnoDB与MyISAM对比
这里写图片描述
2.4.1、对于MyISAM来说,主键与普通索引的区别在于主键为唯一的,相同点就在于节点存储的结构是一样的,都是存储的key以及行地址
2.4.2、对于InnoDB来说,主键索引是将主键与行数据一起存储在叶子节点中,起到聚簇索引的作用,而其他索引则是将key与主键值存储在一个叶子节点中,然后通过二次查找得当行数据。
2.4.3、聚簇索引的每一个叶子节点都包含了主键值、事务ID、用于事务和MVCC回滚的指针以及剩余的其他列
这里写图片描述

展开阅读全文

没有更多推荐了,返回首页