巩义企业网站快速优化多少钱,安徽建设工程信息网中项网,python可以做网站前台么,阐述商业网站开发岗位需求分析目录
一、InnoDB中的B 树索引介绍
二、聚簇索引
#xff08;一#xff09;使用记录主键值的大小进行排序 页内记录排序
页之间的排序
目录项页的排序
#xff08;二#xff09;叶子节点存储完整的用户记录 数据即索引
自动创建
#xff08;三#xff09;聚簇索引…目录
一、InnoDB中的B 树索引介绍
二、聚簇索引
一使用记录主键值的大小进行排序 页内记录排序
页之间的排序
目录项页的排序
二叶子节点存储完整的用户记录 数据即索引
自动创建
三聚簇索引的优缺点
三、二级索引
一二级索引的特点
基于非主键列排序
叶子节点存储部分数据
二二级索引的工作流程
三二级索引的优缺点
四、联合索引
一联合索引的特点
多列排序规则
联合索引的组成
二联合索引与单列索引的区别
联合索引
单列索引
三联合索引的优缺点
四联合索引的使用建议
五、总结 参考文献、书籍及链接 干货分享感谢您的阅读
在现代数据库系统中索引是提高数据检索速度的关键机制之一。InnoDB作为MySQL的默认存储引擎采用了高效的B树结构来实现其索引功能。这种结构不仅确保了数据的快速检索还支持高效的插入、更新和删除操作。理解InnoDB中的B树索引对于数据库优化和性能调优至关重要。
为了更好地理解 InnoDB 中 B 树索引的工作机制我们从创建一个示例表index_demo开始并通过详细的示意图展示记录在页中的存储结构及索引的作用。
CREATE TABLE index_demo (c1 INT,c2 INT,c3 CHAR(1),PRIMARY KEY (c1)
) ROW_FORMAT Compact;这个表中有两个 INT 类型的列 c1 和 c2一个 CHAR(1) 类型的列 c3并且 c1 列为主键。表的行格式为 Compact。其基础可见
一、InnoDB中的B 树索引介绍
B 树索引是一种自平衡的树结构其节点分为内部节点和叶子节点
内部节点Internal Nodes用于索引导航存储键值和指向子节点的指针。叶子节点Leaf Nodes存储实际的数据记录或指向数据记录的指针称为记录指针。
在 B 树中所有的数据记录都存储在叶子节点中而内部节点仅用于存储键值和导航信息。 不论是存放用户记录的数据页还是存放目录项记录的数据页我们都把它们存放到B树这个数据结构中所以我们也称这些数据页为节点。
从图中可以看出来我们的实际用户记录都存放在B树的最底层的节点上这些节点也被称为叶子节点或叶节点其余用来存放目录项的节点称为非叶子节点或者内节点其中B树最上面的那个节点也称为根节点。
依据InnoDB存储引擎B树的树高推导当树高为4时可以存放200百多亿行数据。这样的数据容量可以满足绝大部分应用的需求因此我们可以说在绝大部分应用中B树高度为3或4就可以满足数据存储的需求。B树这种高扇出低树高的特征也大大的提高了主键查询性能。
二、聚簇索引
在InnoDB存储引擎中聚簇索引Clustered Index是数据存储和索引的一种特殊而重要的结构。聚簇索引主要特点 一使用记录主键值的大小进行排序
聚簇索引通过主键值对记录和页进行排序这涉及三个方面 页内记录排序
在每个页内记录按照主键值的大小顺序排成一个单向链表确保了页内记录的有序性方便快速查找。页内的记录被划分成若干个组每个组中主键值最大的记录在页内的偏移量会被当作槽依次存放在页目录中当然Supermum记录比任何用户记录都大我们可以在页目录内通过二分法定位到主键列等于某个值的记录。
页之间的排序
存放用户记录的页按照页内记录的主键大小顺序排成一个双向链表。这种结构使得范围查询和顺序扫描更加高效。
目录项页的排序
存放目录项记录的页根据页内目录项记录的主键大小顺序排成一个双向链表。不同层次的页同样遵循这种排序规则确保树的平衡性和查询效率。
二叶子节点存储完整的用户记录
B树的叶子节点存储的是完整的用户记录即包括所有列的值包括隐藏列在InnoDB中叶子节点不仅仅是索引还包含了实际的数据记录。这种特性使得聚簇索引与普通索引有所不同。
数据即索引
聚簇索引中的叶子节点存储了完整的用户记录因此聚簇索引就是数据的存储方式。换句话说索引即数据数据即索引。
自动创建
在InnoDB存储引擎中聚簇索引会自动为每个表创建并且不需要在MySQL语句中显式使用INDEX语句去创建。通常情况下聚簇索引是基于表的主键创建的。
三聚簇索引的优缺点
聚簇索引的优点聚簇索引的缺点快速数据访问由于数据和索引存储在一起基于主键的查询非常高效不需要额外的索引查找。插入和删除成本较高由于需要维护数据的有序性插入和删除操作可能需要移动大量记录导致性能开销。有序数据存储记录按照主键顺序存储适合范围查询和顺序扫描提高查询性能。更新成本较高如果更新操作导致主键变化会引发记录的重新定位和页的重新排序影响性能。
聚簇索引是InnoDB存储引擎中一种关键的索引类型通过主键排序和存储完整用户记录提供了高效的数据访问和有序的数据存储。在优化数据库性能时理解和合理使用聚簇索引可以显著提升查询和数据操作的效率。具体优化可见MySQL索引性能优化分析。
三、二级索引
聚簇索引只能在搜索条件是主键值时才能发挥作用因为B树中的数据都是按照主键进行排序的。那如果我们想以别的列作为搜索条件该咋办呢难道只能从头到尾沿着链表依次遍历记录么不我们可以多建几棵B树不同的B树中的数据采用不同的排序规则。比方说我们用c2列的大小作为数据页、页中记录的排序规则再建一棵B树效果如下图所示 在InnoDB存储引擎中除了聚簇索引Clustered Index我们还可以使用二级索引Secondary Index来提高非主键列上的查询性能。二级索引是一种基于非主键列的B树结构用于快速定位数据记录。
一二级索引的特点
基于非主键列排序
二级索引的B树结构基于指定的非主键列进行排序这包括以下几个方面
页内记录排序在每个页内记录按照指定列例如c2列的大小顺序排成一个单向链表。页之间的排序存放用户记录的页按照页内记录的指定列顺序排成一个双向链表。这种结构便于快速范围查询和顺序扫描。目录项页的排序存放目录项记录的页根据页内目录项记录的指定列顺序排成一个双向链表不同层次的页同样遵循这种排序规则。
叶子节点存储部分数据
与聚簇索引不同二级索引的叶子节点存储的是索引列和主键列的值而不是完整的用户记录。这种设计减少了存储空间的占用但在查询过程中需要进行回表操作以获取完整的用户记录。
二二级索引的工作流程
假设我们创建了一个基于c2列的二级索引并通过c2列的值查找某些记录以查找c2列的值为4的记录为例查找过程如下 确定目录项记录页 从根页面开始根据c2列的值4定位到目录项记录所在的页通过页44快速定位到目录项记录所在的页为页42因为2 4 9。 通过目录项记录页确定用户记录真实所在的页 在页42中根据c2列的值确定实际存储用户记录的页。由于c2列没有唯一性约束值为4的记录可能分布在多个数据页中。最终确定实际存储用户记录的页在页34和页35中因为2 4 ≤ 4。 在真实存储用户记录的页中定位到具体的记录 在页34和页35中定位到具体的记录但二级索引的叶子节点中仅存储c2列和主键列c1的值。 回表操作 根据主键值到聚簇索引中查找完整的用户记录。这个过程称为回表操作即从二级索引定位到主键再通过主键在聚簇索引中查找完整记录。
三二级索引的优缺点
二级索引的优点二级索引的缺点提高查询效率基于非主键列的查询可以利用二级索引快速定位数据减少全表扫描的开销。回表操作查询完整记录时需要回表操作增加了一次I/O开销。灵活性可以为多个列创建二级索引提升多种查询条件下的性能。占用空间虽然叶子节点不存储完整记录但仍会占用额外的存储空间。
二级索引通过基于非主键列排序和存储索引列与主键列的值为非主键列的查询提供了高效的解决方案。然而由于叶子节点仅存储部分数据查询完整记录时需要回表操作。因此合理使用和配置二级索引对于提升数据库查询性能至关重要。 具体优化可见MySQL索引性能优化分析。
四、联合索引
在InnoDB存储引擎中联合索引Composite Index是一种基于多个列的索引用于提高复杂查询的效率。联合索引通过对多个列进行排序能够更有效地处理包含多个条件的查询。
同时以多个列的大小作为排序规则也就是同时为多个列建立索引比方说我们想让B树按照c2和c3列的大小进行排序这个包含两层含义
先把各个记录和页按照c2列进行排序。在记录的c2列相同的情况下采用c3列进行排序
为c2和c3列建立的索引的示意图如下 如图所示我们需要注意一下几点 每条目录项记录都由c2、c3、页号这三个部分组成各条记录先按照c2列的值进行排序如果记录的c2列相同则按照c3列的值进行排序。 B树叶子节点处的用户记录由c2、c3和主键c1列组成。
一联合索引的特点
多列排序规则
联合索引按照多个列的值进行排序其排序规则包括以下两个层次
第一列排序首先按照第一个指定列例如c2列的值进行排序。第二列排序在第一列相同的情况下按照第二个指定列例如c3列的值进行排序。
在这个结构中每个目录项记录由c2、c3和页号组成叶子节点存储c2、c3和主键c1。
联合索引的组成
目录项记录每条目录项记录由c2、c3和页号组成先按照c2列排序如果c2列相同则按照c3列排序。叶子节点记录叶子节点处的用户记录包含c2、c3和主键c1列。这种结构使得查询包含c2和c3列的条件时更加高效。
二联合索引与单列索引的区别
联合索引
建立联合索引会生成一棵B树该树按照c2和c3列进行排序。查询时如果使用c2和c3作为条件能够快速定位记录减少查询时间。
单列索引
为c2和c3分别建立索引会生成两棵独立的B树每棵树分别按照c2或c3进行排序。查询时如果只使用c2或c3作为条件可以利用相应的索引。但如果同时使用c2和c3作为条件可能需要进行多次索引查找和合并操作增加查询开销。
三联合索引的优缺点
联合索引的优点联合索引的缺点高效的多列查询联合索引能够显著提高包含多个列条件的查询性能。插入和维护成本较高由于需要对多个列进行排序和维护插入和更新操作可能较慢。减少单列索引的数量通过一个联合索引代替多个单列索引可以节省存储空间。部分匹配限制联合索引在查询中只能高效利用前缀列如果查询条件不包括索引的最左列索引的利用率会降低。
四联合索引的使用建议
前缀匹配原则
联合索引在查询中按照列的顺序生效因此查询条件应尽量包括索引的最左列即前缀列。例如创建了(c2, c3)的联合索引后查询条件包含c2或(c2, c3)时能够有效利用索引。
适用场景
联合索引适用于需要同时基于多个列进行查询的场景。例如在电商系统中可以为商品类别和价格区间创建联合索引以优化相关查询。
联合索引是InnoDB中一种重要的索引类型通过对多个列进行排序和索引提高了多列查询的性能。与单列索引相比联合索引在处理复杂查询时更加高效。然而合理的索引设计和使用对于优化数据库性能至关重要。理解联合索引的工作原理和最佳实践可以帮助我们更好地利用MySQL数据库。 具体优化可见MySQL索引性能优化分析。
五、总结
InnoDB中的索引是提高数据检索效率的关键。本文介绍了三种主要索引类型
聚簇索引基于主键排序存储完整的用户记录适合快速主键查询和范围查询。二级索引基于非主键列排序提升非主键查询性能但需要回表操作。联合索引基于多个列排序适用于复杂查询能够显著提升多列条件查询的效率。
通过合理使用和配置这些索引能有效提升数据库查询和数据操作的性能。理解索引的工作机制和最佳实践对于优化MySQL数据库性能至关重要。 参考文献、书籍及链接
《MySQL技术内幕InnoDB存储引擎》第2版MySQL技术内幕 (豆瓣)《MySQL 是怎样运行的从根儿上理解 MySQL》《Inside InnoDB: The InnoDB Storage Engine》MySQL :: MySQL 8.0 Reference Manual :: 15 The InnoDB Storage Engine《InnoDB: The Ultimate Guide》https://www.percona.com/blog/2018/06/05/innodb-the-ultimate-guide/《InnoDB Storage Engine Internals》https://mariadb.com/kb/en/innodb-storage-engine-internals/InnoDB的数据页结构InnoDB存储引擎B树的树高推导_b树一般多少层-CSDN博客MySQL索引性能优化分析_mysql索引和性能分析(实战)-CSDN博客
