成都科技网站建设费,工业企业网络推广,国外做SEO网站,成品源码站文章目录 索引的优缺点优点缺点 聚簇索引特点优点缺点 非聚簇索引特点优点缺点使用场景#xff1a; 在MyISAM与InnoDB中的使用 索引的优缺点
索引概述 MySQL官方将索引定义为帮助MySQL高效获取数据的数据结构。索引的本质是一种排好序的快速查找数据结构#xff0c;用于满足… 文章目录 索引的优缺点优点缺点 聚簇索引特点优点缺点 非聚簇索引特点优点缺点使用场景 在MyISAM与InnoDB中的使用 索引的优缺点
索引概述 MySQL官方将索引定义为帮助MySQL高效获取数据的数据结构。索引的本质是一种排好序的快速查找数据结构用于满足特定查找算法。这些数据结构指向数据允许实现高级查找算法。
优点
索引带来多个优点
提高数据检索效率降低数据库的IO成本类似于大学图书馆的书目索引是主要创建索引的原因。创建唯一索引可以确保数据库表中每一行数据的唯一性。有助于实现数据参考完整性可以加速表和表之间的连接尤其在有依赖关系的子表和父表联合查询时可以提高查询速度。在使用分组和排序子句进行数据查询时可以显著减少查询中分组和排序的时间降低CPU的消耗。
缺点
增加索引也伴随着一些不利的方面
创建和维护索引需要耗费时间随着数据量的增加所需时间也会增加。索引需要占用磁盘空间除了数据表占用的数据空间之外每个索引还需要一定的物理空间存储在磁盘上。如果有大量的索引索引文件可能会比数据文件更快达到最大文件尺寸。尽管索引大大提高了查询速度却会降低更新表的速度。当对表中的数据进行增加、删除和修改时索引也需要动态地维护从而降低了数据的维护速度。
因此在选择是否使用索引时需要综合考虑索引的优点和缺点。’
聚簇索引
特点
聚簇索引Clustered Index具有以下特点 物理存储顺序 聚簇索引决定了数据行在表中的物理存储顺序。数据行按照聚簇索引的键值顺序在磁盘上连续存储这有助于减少磁盘I/O操作提高查询性能。 唯一性要求 聚簇索引要求键值是唯一的因为它确定了数据行的物理存储顺序。通常表的主键列用于创建聚簇索引以确保数据行的唯一性。 主键索引 聚簇索引通常与表的主键相关联。主键是表的唯一标识符使用聚簇索引对主键列进行快速查找非常有效。 只有一个聚簇索引 通常情况下每张表只能有一个聚簇索引因为数据行的物理存储顺序只能有一个。这与非聚簇索引不同一张表可以有多个非聚簇索引。 使用记录主键值的大小进行记录和页的排序这包括三个方面的含义 页内 的记录是按照主键的大小顺序排成一个 单向链表 。各个存放 用户记录的页 也是根据页中用户记录的主键大小顺序排成一个 双向链表 。存放 目录项记录的页 分为不同的层次在同一层次中的页也是根据页中目录项记录的主键 大小顺序排成一个 双向链表 。 B树的叶子节点存储的是完整的用户记录 所谓完整的用户记录就是指这个记录中存储了所有列的值包括隐藏列。
优点
数据访问更快 因为聚簇索引将索引和数据保存在同一个B树中因此从聚簇索引中获取数据比非聚簇索引更快聚簇索引对于主键的 排序查找 和 范围查找 速度非常快按照聚簇索引排列顺序查询显示一定范围数据的时候由于数据都是紧密相连数据库不用从多个数据块中提取数据所以 节省了大量的io操作 。
缺点
插入速度严重依赖于插入顺序 按照主键的顺序插入是最快的方式否则将会出现页分裂严重影响性能。因此对于InnoDB表我们一般都会定义一个自增的ID列为主键更新主键的代价很高 因为将会导致被更新的行移动。因此对于InnoDB表我们一般定义主键为不可更新二级索引访问需要两次索引查找 第一次找到主键值第二次根据主键值找到行数据。
非聚簇索引
特点
非聚簇索引不改变数据表的物理顺序而是在索引中维护了数据行的引用。每张表可以有多个非聚簇索引每个非聚簇索引都有自己的独立存储结构。非聚簇索引通常包括索引键和指向数据行的指针或物理地址。Best important非聚簇索引的构建和维护会增加存储空间和维护成本但提供了更快的读取性能。
优点
提高检索性能非聚簇索引允许快速查找和过滤数据因为它们提供了索引键的有序列表减少了需要扫描整个表的需求。支持覆盖索引非聚簇索引可以覆盖查询的所有列从而避免了对实际数据行的访问进一步提高查询性能。支持多个索引可以在同一表上创建多个非聚簇索引以满足不同查询的需求。
缺点
额外存储开销每个非聚簇索引都需要额外的存储空间来存储索引键和指向数据行的指针。更新性能下降插入、更新和删除操作需要维护非聚簇索引这可能导致性能下降尤其是在频繁的大批量数据更改情况下。索引选择的复杂性在设计数据库时需要仔细选择哪些列应该创建非聚簇索引以避免不必要的索引浪费存储空间。
使用场景
支持频繁的查询操作非聚簇索引适用于需要快速检索数据的查询操作特别是针对某些列的查找、过滤和排序操作。覆盖查询需求当查询需要覆盖索引中的所有列非聚簇索引是一个不错的选择因为它可以减少对数据行的访问次数。辅助索引非聚簇索引通常用于支持数据表的主要聚簇索引以外的其他查询需求例如范围查询、联接操作等。
在MyISAM与InnoDB中的使用
MyISAM 与 InnoDB 是两种不同的存储引擎它们在索引和数据存储方面有很多区别。以下是它们之间的主要比较 索引类型 MyISAM 使用非聚簇索引所有索引都是二级索引不包含实际数据记录。InnoDB 包含一个聚簇索引可以根据主键值一次查找实际数据记录。 数据文件和索引文件 InnoDB 的数据文件本身就是索引文件包含实际数据记录。MyISAM 的索引文件和数据文件是分离的索引文件只保存数据记录的地址。 非聚簇索引中的数据存储 InnoDB 的非聚簇索引中的data域存储相应记录的主键值。MyISAM 的索引记录存储的是数据记录的地址。 回表操作 MyISAM 的回表操作是快速的因为它直接使用地址偏移量来获取数据记录。 “回表”在数据库查询中当使用非聚簇索引或次要索引来查找数据时需要额外的步骤来获取完整的数据行。 InnoDB 通过获取主键之后再去聚簇索引中找记录虽然也较快但不如直接使用地址来访问。 主键要求 InnoDB 要求表必须有主键如果没有显式指定MySQL会自动选择一个非空且唯一标识数据记录的列作为主键。MyISAM 可以没有显式主键。
总之InnoDB 的设计更加适用于需要事务支持和高并发读写的应用而MyISAM 更适合于只有读操作的应用或者不需要事务支持的情况。选择哪种引擎应该根据具体的应用需求来决定。
