type
status
date
slug
summary
tags
category
icon
password
索引
在数据库中索引最核心的作用是:加速查找。 例如:在含有300w条数据的表中查询，无索引需要700秒，而利用索引可能仅需1秒。
索引原理
索引在数据库中的作用类似于书籍的目录，通过创建索引，数据库可以更快速地查找到所需的数据，从而提高查询的性能。索引的原理和实现方式可以从以下几个方面进行理解：
1. 索引的基本原理
索引的基本原理是为数据库表中的一个或多个列创建一个有序的数据结构（例如B树、哈希表等）。当执行查询时，数据库可以通过查找索引中的键值快速定位到表中的数据行，而不需要扫描整个表。这大大减少了数据检索所需的时间。
2. 常见的索引类型
- B-Tree索引: 这是最常见的索引类型，尤其在关系型数据库中。B-Tree索引的节点按顺序排列，并且通过子节点的指针链接，保证了平衡性。查询时，通过从根节点到叶子节点的路径快速定位数据。
- 哈希索引: 基于哈希表实现，适用于等值查询（如
=、IN等）。哈希索引通过将键值映射到哈希表中的一个桶来实现快速查找，但不支持范围查询（如BETWEEN、>等）。
- 全文索引: 用于对文本数据进行快速的全文搜索，适用于查找大型文本字段中的关键词。
- 空间索引: 适用于处理几何数据类型（如GIS应用中的地理位置数据），通过对空间数据创建索引来加速空间查询。
3. 索引的工作过程
当查询包含一个被索引的列时，数据库会：
- 查找索引: 首先查找索引中的键值。对于B-Tree索引，这是一个二分查找过程，可以快速定位到对应的叶子节点。
- 读取数据行: 一旦找到索引项，数据库就会通过指针或者行ID（row ID）直接访问对应的数据行，从而获取完整的数据。
4. 索引的优缺点
- 优点:
- 查询加速: 大大提高了数据检索的速度，尤其是在大表中进行复杂查询时。
- 排序优化: 如果查询中的
ORDER BY或者GROUP BY子句使用了索引列，可以避免排序操作。 - 唯一性约束: 索引可以用于唯一性约束（如主键），确保数据的唯一性。
- 缺点:
- 插入/更新/删除性能降低: 由于需要维护索引结构，插入、更新、删除操作的性能会受到影响，尤其是在频繁写操作的场景中。
- 占用存储空间: 索引需要额外的存储空间来保存索引数据结构，对于大型数据库系统来说，这可能会增加存储成本。
- 复杂性增加: 在创建和管理索引时，需要考虑查询的类型、数据的分布等因素，这增加了系统的复杂性。
5. 索引的使用场景
- 主键和唯一性约束: 通常为主键列和唯一性约束列创建索引，以确保数据的完整性和唯一性。
- 频繁查询的列: 为经常出现在
WHERE、JOIN、ORDER BY或GROUP BY子句中的列创建索引，可以显著提高查询性能。
- 大表的查询: 在行数非常多的表中，通过索引可以避免全表扫描，从而大幅减少查询时间。
6. 索引的优化
为了有效地使用索引，通常需要进行优化，包括选择合适的索引类型、避免在低选择性的列上创建索引、以及在查询中合理使用索引等。这可以通过分析查询的执行计划（EXPLAIN）来帮助识别和解决索引的瓶颈。
总结
索引通过为表中的数据列创建一个有序的数据结构，使得数据库能够快速定位和检索所需数据。尽管索引可以显著提高查询性能，但它也带来了维护成本和存储开销，因此在使用索引时需要权衡利弊。
MySQL数据库中两种常见的存储引擎
1. MyISAM引擎
- 非聚簇索引（也称为非聚集索引）：在MyISAM存储引擎中，数据和索引结构是分开存储的。
- 数据文件：实际的数据存储在一个独立的文件中。
- 索引文件：索引存储在另一个独立的文件中。索引文件中的索引指向数据文件中的实际数据位置。
这种方式的优点是索引结构比较简单，但在大规模数据查询时，可能需要更多的磁盘I/O操作，因为索引和数据存储在不同的位置。
2. InnoDB引擎
- 聚簇索引（也称为聚集索引）：在InnoDB存储引擎中，数据和主键索引存储在一起。
- 聚簇索引：每个表的主键都会作为索引结构的一部分，同时数据也存储在这个索引结构中。聚簇索引的叶节点保存了行数据。
- 二级索引：InnoDB的二级索引（非主键索引）并不存储实际的数据，而是存储主键的值，通过这个主键值可以快速访问聚簇索引中的行数据。
因为数据和主键索引存储在一起，InnoDB引擎的查询性能在主键查询时通常较好，尤其是在范围查询中。聚簇索引的结构使得相关数据在物理上存储得更接近，从而减少了磁盘I/O操作。
总结
- MyISAM引擎：数据和索引分开存储，使用非聚簇索引。
- InnoDB引擎：数据和主键索引存储在一起，使用聚簇索引。
这两种引擎在处理大量数据时有不同的性能特点，选择合适的存储引擎取决于具体的应用场景和需求。
两种引擎在mysql文件存储中体现
聚簇索引生成两个文件，非聚簇索引生成三个文件
常见索引
1. 主键索引（Primary Key Index）
- 功能:
- 用于唯一标识表中的每一行记录。
- 主键索引不仅可以加速查找，还保证了索引列的值不能为
NULL，并且不能重复。
- 特点:
- 唯一性: 主键列中的值必须是唯一的，没有重复。
- 非空性: 主键列不能包含
NULL值。 - 自动创建索引: 定义主键时，数据库会自动为该列创建一个聚簇索引（在InnoDB中），即主键索引。
- 联合主键索引:
- 可以在多个列上定义一个主键，这种情况下，主键索引会包含所有指定的列，确保这些列的组合值是唯一的。
2. 唯一索引（Unique Index）
- 功能:
- 用于确保列中的所有值都是唯一的，不能重复，但可以为
NULL。
- 特点:
- 唯一性: 列中的值不能重复，但与主键不同的是，唯一索引允许列中包含
NULL值（通常NULL值视为不同的）。 - 加速查找: 因为唯一索引保证了列中的值是唯一的，查找速度通常会更快。
- 联合唯一索引:
- 可以在多个列上定义一个唯一索引，确保这些列的组合值是唯一的。
3. 普通索引（Normal Index）
- 功能:
- 主要用于加速查找操作，不对列中的值施加任何约束（如唯一性或非空性）。
- 特点:
- 加速查找: 普通索引仅用于提高数据检索的速度，不限制列中的数据，可以包含重复值和
NULL值。
- 联合索引:
- 可以在多个列上创建普通索引，以便加速对多个列的查询操作。
总结
- 主键索引:
- 必须唯一且非空。
- 是表中用于唯一标识记录的索引。
- 数据库会自动为主键创建索引（通常是聚簇索引）。
- 唯一索引:
- 必须唯一，但允许
NULL值。 - 用于确保数据的唯一性，但不是主键。
- 普通索引:
- 不要求唯一性，也允许
NULL值。 - 主要用于提高查询性能，没有唯一性或非空约束。
这些索引类型在数据库设计中扮演着不同的角色，选择合适的索引类型可以有效地提高数据库的性能，并确保数据的完整性和一致性。
主键和联合主键索引
唯一和联合唯一索引
索引和联合索引
特点区别
下面我们详细探讨主键索引、联合主键索引、唯一索引、联合唯一索引、普通索引和联合索引的特点与区别，并举例说明它们的使用场景。
1. 主键索引（Primary Key Index）
特点:
- 唯一性: 主键必须是唯一的，不能有重复值。
- 非空性: 主键列不能包含
NULL值。
- 自动创建索引: 数据库在创建主键时，通常会自动为该列创建一个聚簇索引（在InnoDB中），并按照该索引排序存储数据。
- 每个表只能有一个主键。
示例:
在这个示例中，
id列是主键，这意味着每个用户的id都是唯一且非空的。2. 联合主键索引（Composite Primary Key Index）
特点:
- 多个列组合唯一: 联合主键由多个列组合而成，确保组合在一起的列值是唯一的。
- 自动创建聚簇索引: 和主键一样，数据库为联合主键创建索引，并使用这些列的组合来唯一标识每一行数据。
- 每个表只能有一个联合主键。
示例:
在这个示例中，
order_id和product_id的组合是唯一的，这意味着同一个订单中的每个产品必须有唯一的组合。联合主键索引详细解释
联合主键索引（Composite Primary Key Index）是一个由多个列组合在一起组成的主键，这个组合在表中必须是唯一的。它用于确保某些列的组合在整个表中是独一无二的，而不仅仅是单个列的唯一性。
联合主键索引示例
假设我们有一个订单表
orders，这个表记录了每个订单中包含的不同产品。这个表的列可能包括：order_id：订单的唯一ID。
product_id：产品的唯一ID。
quantity：购买的产品数量。
如果我们希望确保在同一个订单中，不能添加相同的产品多次，那么我们可以使用
order_id和product_id的组合作为联合主键。这个联合主键将确保每个订单中的每个产品只能出现一次。创建联合主键索引的SQL语句
示例数据
order_id | product_id | quantity |
1 | 101 | 2 |
1 | 102 | 1 |
2 | 101 | 3 |
2 | 103 | 5 |
在这个表中：
- 第一行表示订单
1中购买了产品101，数量为2。
- 第二行表示订单
1中还购买了产品102，数量为1。
- 第三行表示订单
2中购买了产品101，数量为3。
- 第四行表示订单
2中购买了产品103，数量为5。
唯一性解释
由于
order_id和product_id的组合是主键，所以每个组合在表中必须是唯一的。这意味着：- 你可以在同一个订单中有多个不同的产品，例如订单
1可以包含产品101和102。
- 你可以在多个订单中有相同的产品，例如产品
101可以同时出现在订单1和订单2中。
- 但你不能在同一个订单中多次添加同一个产品。例如，你不能在订单
1中再插入一行记录说又购买了产品101。如果尝试这样做，会违反主键约束并导致数据库抛出错误。
插入数据时的行为
假设你尝试插入以下数据：
这将会失败，因为组合键
(1, 101)已经存在于表中。联合主键索引确保了这种组合只能出现一次，这样可以避免数据重复和数据不一致问题。总结
联合主键索引确保了表中某些列的组合是唯一的。例如在订单系统中，
order_id和product_id的组合作为联合主键，确保了在同一个订单中不能有相同的产品多次出现。这种设计在需要防止重复数据输入时非常有用，同时也可以帮助保持数据的完整性和一致性。3. 唯一索引（Unique Index）
特点:
- 唯一性: 确保索引列中的每个值都是唯一的，但允许
NULL值。
- 不保证物理存储顺序: 唯一索引并不一定会影响数据的存储顺序，具体实现取决于数据库引擎。
示例:
在这个示例中，
username列具有唯一索引，确保每个用户名在表中是唯一的。4. 联合唯一索引（Composite Unique Index）
特点:
- 多个列组合唯一: 联合唯一索引由多个列组合而成，确保这些列的组合值是唯一的。
- 允许
NULL值: 如果某个列允许NULL值，联合唯一索引会视NULL为不同的值，允许多个NULL组合。
示例:
在这个示例中，
first_name和last_name的组合必须唯一，即使有多个用户姓“张”，也不能有两个叫“张三”的用户。5. 普通索引（Index）
特点:
- 加速查询: 普通索引用于加速对特定列的查询，不要求列的值唯一或非空。
- 不影响数据唯一性: 普通索引不会强制数据的唯一性，也不约束
NULL值的存在。
示例:
在这个示例中，
username列有一个普通索引，用于加速基于username的查询。6. 联合索引（Composite Index）
特点:
- 多个列组合加速查询: 联合索引用于在多个列组合上加速查询，特别是当查询条件涉及多个列时。
- 索引顺序影响使用效果: 联合索引中列的顺序很重要，通常需要按照查询使用的顺序来排列索引列。
示例:
在这个示例中，
username和email列有一个联合索引，可以加速基于这两个列的组合查询。例如，查询WHERE username = 'John' AND email = 'john@example.com'时会利用这个索引。总结和对比
- 主键索引与联合主键索引：主键索引确保单个列的唯一性和非空性，而联合主键索引确保多个列的组合唯一性和非空性。
- 唯一索引与联合唯一索引：唯一索引确保单个列的值唯一，而联合唯一索引确保多个列组合的值唯一。两者都允许
NULL值，但NULL值被视为不同的值。
- 普通索引与联合索引：普通索引用于单个列的查询加速，而联合索引用于多个列组合的查询加速。联合索引的列顺序对其使用效果有重要影响。
这些索引类型各自适用于不同的场景，选择合适的索引可以有效提高数据库查询性能，同时确保数据的完整性和一致性。
查询需要命中索引才能提升速度
执行计划
打赏
如果您觉得我的内容对你有所帮助，不要吝啬你的一键三连!如果你有能力的话也可以通过下面请我喝杯咖啡~金额您随意~如果对文章内容有任何疑问，欢迎加入群组联系我~
- 作者:Don Mark
- 链接:null/article/53b00e38-3bf6-4e30-a530-2ea79d0225c6
- 声明:本文采用 CC BY-NC-SA 4.0 许可协议，转载请注明出处。