Skip to main content

数据库索引

介绍​

数据库索引类似于书籍的目录，它帮助数据库快速定位和检索数据。在MySQL（关系型数据库）和MongoDB（非关系型数据库）中，索引都是用来提高查询效率的。它们通过为数据库表或集合中的一个或多个字段创建索引，减少了数据库搜索整个表或集合的需要。索引特别有用于处理大量数据和执行复杂查询的场景，因为没有索引的查询可能会导致全表扫描，这在大型数据库中会非常耗时。因此，合理使用索引是优化数据库性能的关键。

单列索引​

在MySQL中设置索引通常涉及使用CREATE INDEX语句或在CREATE TABLE或ALTER TABLE语句中指定索引。例如，为表中的某个列创建索引的语句如下：

CREATE INDEX 索引名 ON 表名(列名);

索引的原理是通过为表中的一列或多列数据创建一个快速查找的参照系统。它类似于书籍的目录，使数据库能够快速找到数据，而不是扫描整个表。索引通常以B树（平衡树）或哈希表的形式实现，这些结构使得查找、插入和删除操作更加高效。正确使用索引可以显著提高数据库的查询性能，特别是在处理大量数据时。

复合索引​

在MySQL中，要创建包含多列的复合索引，您可以在CREATE INDEX命令中指定多个列名。例如，如果您有一个包含firstName和lastName两列的employees表，想要基于这两列创建索引，可以这样做：

CREATE INDEX idx_name ON employees (firstName, lastName);

这里，idx_name是索引的名称，而employees是表名。这个命令会在employees表上创建一个新的索引idx_name，该索引基于firstName和lastName两列。复合索引可以提高涉及这些列的查询的效率，但要注意选择性和查询模式，以确保索引的有效性。

索引必须是主键吗​

如果您在MySQL中对某列设置了索引，那么查询这一列的速度通常会更快，因为数据库可以利用索引快速定位数据，而不需要扫描整个表。索引并不必须是主键。虽然主键自动成为索引，但您可以在非主键的列上创建索引。在选择是否创建索引时，应考虑查询的需求和表的数据结构，因为索引虽然可以提高查询速度，但也会增加写操作的开销，并占用更多存储空间。

索引必须唯一吗​

您可以将产品表的name列设置为索引，这样查询该列时速度通常会更快。索引并不要求唯一，除非它是一个唯一索引或主键索引。普通索引允许重复的值。索引的主要要求包括：

  • 适用性：适合用于经常作为查询条件的列。
  • 选择性：具有高度唯一性的列更适合索引，因为它们提供更好的过滤效果。 请注意，虽然索引可以加速查询，但它们也会增加数据插入、更新和删除的开销，并占用额外的存储空间。因此，在决定对哪些列创建索引时，需要权衡利弊。

需要每过一段时间设置一次索引吗​

创建索引通常是一次性的操作，而不是需要定期重复的任务。一旦在表的列上创建了索引，它会随着表中数据的插入、更新和删除自动维护。不过，有几种情况可能需要您重新考虑和调整索引：

  • 数据变化：如果表的数据分布或查询模式发生显著变化，可能需要更新或优化索引。
  • 性能监控：定期监控数据库性能可以帮助识别是否需要对现有索引进行调整。
  • 系统升级：在进行数据库系统升级或更改时，可能需要重新审视索引策略。

复合索引的优点​

复合索引相比单列索引的优点主要体现在多列查询上。当查询条件涉及复合索引中的多个列时，复合索引可以提供更高的查询效率。例如，在一个由两列组成的复合索引中，如果查询条件同时使用这两列，查询将非常快速。但是，复合索引的效果取决于查询条件和索引中列的顺序。使用复合索引时，一般是查询条件从左到右匹配索引列时效果最佳。如果查询只使用索引中的第一个列，仍然可以利用索引，但如果查询使用的是索引后面的列而跳过了前面的列，索引可能就不会被使用

复合索引示例​

例如，假设您有一个名为Orders的表，它包含customerID和orderDate两个字段。您想要根据这两个字段快速查询订单。您可以创建一个复合索引，同时覆盖这两个字段：

CREATE INDEX idx_customer_order ON Orders (customerID, orderDate);

要有效利用customerID和orderDate的复合索引，您的查询应该首先使用customerID，可选地再使用orderDate。例如：

SELECT * FROM Orders WHERE customerID = 123;

SELECT * FROM Orders WHERE customerID = 123 AND orderDate = '2021-01-01';

SELECT * FROM Orders WHERE customerID = 123 ORDER BY orderDate;

这些查询都会优先使用customerID，这与复合索引的第一个字段匹配。如果您的查询只涉及orderDate而不是customerID，则索引不会被有效利用。