企业品牌推广网站,利用帝国软件如何做网站,珠海策划网站建设平台,湛江百度seo公司文章目录 1. 索引的分类1. 存储结构维度2. 功能维度3. 列数维度4. 存储方式维度5. 更新方式维度 2. 聚簇索引2.1 什么是聚簇索引2.2 聚簇索引的工作原理 3. 非聚簇索引#xff08;MySQL官方文档称为Secondary Indexes#xff09;3.1 什么是非聚簇索引3.2 非聚簇索引的工作原理… 文章目录 1. 索引的分类1. 存储结构维度2. 功能维度3. 列数维度4. 存储方式维度5. 更新方式维度 2. 聚簇索引2.1 什么是聚簇索引2.2 聚簇索引的工作原理 3. 非聚簇索引MySQL官方文档称为Secondary Indexes3.1 什么是非聚簇索引3.2 非聚簇索引的工作原理 4. 聚簇索引与非聚簇索引的区别 MySQL的聚簇索引和非聚簇索引翻译为中文也叫聚集索引非聚集索引。英文有两种叫法
Clustered index、
non-Clustered index 。MySQL官方对
非聚集索引称之为
Secondary Indexes。所以遇到
Secondary Indexes 和
non-Clustered index等价。 MySQL官方文档介绍在
《15.6.2.1 Clustered and Secondary Indexes》https://dev.mysql.com/doc/refman/8.0/en/innodb-index-types.html 1. 索引的分类
在了解聚簇索引和非聚簇索引之前我们先对数据库索引的分类进行一个了解不然繁杂的概念和分类会使得搞混通常我们可以听到B-Tree索引、全文索引 、复合索引、 聚簇索引 、静态索引其实这些描述都是站在不同维度的描述B-Tree索引是站在存储结构的维度全文索引是站在功能维度描述所以我们先了解一下不同维度的索引描述。 以下是常见的一些维度
1. 存储结构维度
索引类型描述适用场景B-Tree索引基于平衡多路搜索树的索引结构维护数据有序性范围查询、排序操作Hash索引基于哈希表的索引结构适合等值查询等值查询R-Tree索引适用于存储和查询空间数据的树形结构地理空间数据的查询Bitmap索引适用于低基数列的索引占用空间小且查询效率高低基数列不同值的数目较少的索引
2. 功能维度
索引类型描述适用场景普通索引基本的索引类型无任何限制通用唯一索引类似于普通索引要求索引列的值必须唯一通常用于主键确保索引列的唯一性全文索引用于全文搜索全文搜索空间索引用于地理空间数据的索引地理空间数据的查询
3. 列数维度
索引类型描述单列索引仅包含一个字段的索引复合索引包含多个字段的索引可以是普通索引、唯一索引等
这些索引类型的选择取决于需要索引的列的组合和查询需求。复合索引可以更好地支持涉及多个列的查询但需要权衡索引的大小和维护成本。
4. 存储方式维度
聚簇索引将数据行直接存储在索引中因此范围查询和排序操作效率较高而非聚簇索引则需要通过指针访问数据行。选择适当的索引类型取决于数据访问模式和查询需求。
索引类型描述聚簇索引数据行存储在索引中数据行的物理顺序与索引中的键值顺序一致非聚簇索引索引中的键值顺序与数据行的物理顺序不一致索引中包含指向数据行的指针
5. 更新方式维度
索引类型描述静态索引仅在数据被插入、删除或更新时更新索引动态索引在查询时实时更新索引
静态索引在数据被修改时才更新因此可能存在索引与实际数据不一致的情况。它适用于数据变动较少的场景可以提高插入、删除和更新操作的性能。动态索引则在查询时实时更新确保索引与实际数据保持一致适用于频繁变动的数据环境但可能对写入操作的性能有一定影响。选择适当的索引类型应考虑数据变动频率和查询性能需求。
本章我们着重了解聚簇索引和非聚簇索引的工作原理
2. 聚簇索引
2.1 什么是聚簇索引
聚簇索引是一种特殊类型的索引在存储引擎中数据记录实际的存放方式会根据聚簇索引来组织。一个表中只能有一个聚簇索引但可以有多个非聚簇索引。
在许多数据库系统中聚簇索引通常就是主键索引。例如 在MySQL的InnoDB引擎中聚簇索引默认是主键如果没有定义主键MySQL会选择一个非空唯一索引代替如果没有非空唯一索引MySQL会自动创建一个隐藏的聚簇索引。 在SQL Server中也可以选择用哪个列作为聚簇索引但一般推荐使用主键。 在Oracle中可以明确指定创建聚簇索引。
虽然聚簇索引在很多情况下被设置为主键但并不意味着聚簇索引一定是主键。聚簇索引应该选择最能代表数据存储特征的那一列或几列例如如果一个表的数据经常按照某一列的顺序进行查找那么这一列就非常适合做聚簇索引。
2.2 聚簇索引的工作原理
MySQL 8中的聚簇索引原理与之前版本类似主要的工作原理是基于B树数据结构进行排序和检索操作。
对于MySQL的InnoDB存储引擎聚簇索引是按照主键的顺序来存储数据的。这意味着每个表只能有一个聚簇索引如果没有明确指定主键InnoDB会自动选择一个能作为主键的列或者自行生成一个。
以下是聚簇索引的工作原理 查询操作当执行查询操作时InnoDB引擎会利用B树的特性从根节点开始通过比较索引的键值找到对应的叶子节点数据页从而快速找到需要的数据。因为索引的键值和数据是在一起的所以查询效率非常高。 插入和删除操作当进行插入或删除操作时InnoDB引擎需要找到对应的索引键值然后在对应的位置插入新的数据或删除旧的数据。因为数据是按照键的顺序存储的所以插入和删除操作可能会引发数据的移动尤其是在插入时如果插入的数据键值在当前键值范围内则可能会触发数据页的分裂。 更新操作当进行更新操作时如果更新的是非索引列那么只需定位到数据页并进行更新即可但是如果更新的是索引列那么可能会引发数据的移动因为要保持数据的有序性。 设计聚簇索引时要尽可能选择稳定且不频繁变动的列作为主键这样可以减少因为插入、删除和更新操作引发的数据移动提高数据库的性能。 聚簇索引可以提高大多数查询操作的性能因为它们为数据提供了更线性的访问路径数据存储在页面中。此外由于具有相似索引键值的行存储在一起使用聚簇索引时顺序检测预取更高效。
示例 假设我们有一个学生信息表我们可以通过以下SQL语句创建这个表并且设置id为主键也就是聚簇索引
CREATE TABLE students (id INT PRIMARY KEY,name VARCHAR(100),age INT
);假设我们现在要查询id为100的学生的信息我们可以通过以下SQL语句进行查询
SELECT * FROM students WHERE id 100;在执行这个查询操作时因为id是聚簇索引所以MySQL会通过B树的检索算法从根节点开始比较索引的键值找到对应的叶子节点数据页然后读取该数据页找到id为100的学生的信息这个过程的效率非常高。
同样地如果我们要更新id为100的学生的年龄我们可以通过以下SQL语句进行更新
UPDATE students SET age 20 WHERE id 100;在执行这个更新操作时MySQL也会先通过聚簇索引找到id为100的学生的信息然后直接在数据页上进行更新操作。如果更新的是非索引列在这个例子中是age列那么更新操作的效率也是非常高的。 如果更新的是索引列那么可能会引发数据的移动因为要保持数据的有序性。例如如果我们要改变id为100的学生的id那么可能就会引发数据的移动因此设计聚簇索引时我们应该尽可能选择稳定且不频繁变动的列作为主键。 3. 非聚簇索引MySQL官方文档称为Secondary Indexes
MySQL官方文档介绍在《15.6.2.1 Clustered and Secondary Indexes》
3.1 什么是非聚簇索引
非聚簇索引也被称为二级索引或辅助索引它的工作方式与聚簇索引有所不同。在非聚簇索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同。换句话说非聚簇索引的逻辑顺序是索引的键值顺序但是这个顺序并不等于数据在磁盘上的物理存储顺序。
在非聚簇索引中每一个索引条目都包含了键值和一个指向该键值对应的数据行的指针。这个指针通常是数据行的物理地址或者是一个指向数据行的其他种类的标识符。
一个表可以有多个非聚簇索引。当查询不包含聚簇索引的列时数据库系统会使用非聚簇索引来提高查询性能。
比如在一个员工表中聚簇索引可能会基于员工的ID进行设置而非聚簇索引可能会基于员工的姓名或者部门来设置。这样当查询姓名或者部门时数据库系统就可以直接利用非聚簇索引进行查找而不需要扫描整张表从而提高了查询效率。
3.2 非聚簇索引的工作原理
非聚簇索引的工作原理与聚簇索引有些不同。非聚簇索引也称为二级索引或辅助索引并不会影响表中数据的物理存储顺序而是创建一个单独的数据结构通常是B树来存储索引列的值和对应的行指针。如上图示例。
非聚簇索引的工作原理 查询操作当执行查询操作时数据库会从非聚簇索引的B树的根节点开始通过比较索引的键值找到对应的叶子节点。这个叶子节点包含了键值和一个指向该键值对应的数据行的指针。数据库通过这个指针找到实际的数据行。这个过程通常需要两次磁盘I/O操作第一次是在索引上进行查找第二次是通过找到的指针去数据文件中获取实际的数据行。 插入和删除操作当进行插入或删除操作时数据库需要同时在索引结构和数据文件中进行操作。首先数据库会在索引结构中插入或删除对应的键值和行指针然后在数据文件中插入或删除实际的数据行。 更新操作当进行更新操作时如果更新的是非索引列那么数据库只需要在数据文件中更新对应的数据行即可如果更新的是索引列那么数据库需要同时在索引结构和数据文件中进行更新操作。
4. 聚簇索引与非聚簇索引的区别
聚簇索引非聚簇索引查询速度通常较快因为可以直接定位到数据较慢因为需要先定位到索引然后再通过索引找到数据内存使用使用的内存较少因为数据和索引在一起使用的内存较多因为数据和索引是分开的数据存储聚簇索引就是主数据数据按照索引排序非聚簇索引是数据的一份索引数据的物理排序与索引无关索引数量一个表只能有一个聚簇索引一个表可以有多个非聚簇索引数据存储能力聚簇索引存储数据本身非聚簇索引存储数据的指针并不存储数据本身存储内容聚簇索引存储实际的数据行非聚簇索引存储索引列和行指针叶节点内容在聚簇索引中叶节点就是实际的数据在非聚簇索引中叶节点不是实际的数据而只包含索引和行指针数据顺序在聚簇索引中数据物理存储的顺序与索引的顺序一致在非聚簇索引中数据的物理存储顺序与索引顺序无关索引类型聚簇索引是一种将表记录物理排序以匹配索引的索引类型非聚簇索引是一种索引的逻辑顺序与数据在磁盘上的物理存储顺序无关的索引类型索引大小主聚簇索引的大小一般较大相对而言非聚簇索引的大小较小
