数据库 索引-介绍

数据库索引是数据库管理系统中的一个重要概念，它是对数据库表中一列或多列的值进行排序的一种数据结构，主要目的是加快对表中数据的检索速度。

一、定义与特点

• 定义：索引是对数据库表中一列或多列的值进行排序的结构，通过索引可以快速访问数据库表中的特定信息。
• 特点：
  • 索引能够极大地提高数据检索的效率，减少数据库的I/O成本。
  • 索引需要占用额外的物理存储空间。
  • 索引的创建和维护（如插入、删除、更新操作）需要一定的时间成本。
  • 索引不是万能的，它并不适用于所有场景，需要根据实际的数据使用模式来决定是否创建索引。

二、类型

• 聚簇索引（Clustered Index）：聚簇索引决定了表中数据的物理存储顺序。一个表只能有一个聚簇索引，因为数据只能以一种顺序存放。如果指定了聚簇索引，那么表中的数据就会按照聚簇索引指定的列的顺序进行存储。
• 非聚簇索引（Non-clustered Index）：非聚簇索引不改变表中数据的物理存储顺序，而是为表中的每一行数据生成一个索引项，并将这些索引项存储在索引页中。非聚簇索引可以包含多个，每个非聚簇索引都包含索引键值和指向表中相应行的指针（或行定位器）。
• 唯一索引（Unique Index）：唯一索引保证索引列中的每个值都是唯一的。这有助于保证数据的唯一性，防止重复数据的插入。
• 主键索引（Primary Key Index）：主键索引是一种特殊的唯一索引，它除了具有唯一索引的所有特性外，还具有标识表中每一行记录的唯一标识作用。主键索引是表中数据的逻辑入口，表中的每一行数据都有一个与之对应的主键索引值。

三、操作举例

这些例子以MySQL数据库为例进行说明

1. 创建索引

创建单列索引

假设有一个名为employees的表，想要在first_name字段上创建一个普通索引，可以使用以下SQL语句：

CREATE INDEX idx_firstname ON employees(first_name);

如果想要在email字段上创建一个唯一索引，以确保每个email地址都是唯一的，可以使用以下SQL语句：

CREATE UNIQUE INDEX uni_email ON employees(email);

创建复合索引

复合索引是基于多个字段创建的索引，可以同时加速多个字段的查询速度。假设想要在employees表的first_name和last_name字段上创建一个复合索引，可以使用以下SQL语句：

CREATE INDEX idx_first_last ON employees(first_name, last_name);

在创建表时创建索引

在创建表的时候，也可以直接在字段定义后指定索引。例如，创建一个名为users的表，并在username字段上创建唯一索引：

CREATE TABLE users ( 
id INT AUTO_INCREMENT PRIMARY KEY, 
username VARCHAR(50) UNIQUE, 
email VARCHAR(100) 
);

或者，在表的最后指定索引：

CREATE TABLE users ( 
id INT AUTO_INCREMENT, 
username VARCHAR(50), 
email VARCHAR(100), 
PRIMARY KEY (id), 
UNIQUE INDEX uni_username (username) 
);

2. 查询索引

要查看表上的索引信息，可以使用SHOW INDEX语句。例如，查看employees表上的索引：

SHOW INDEX FROM employees;

这将列出employees表上的所有索引，包括索引名称、索引类型、索引列等信息。

3. 删除索引

当索引不再需要时，可以使用DROP INDEX语句来删除它。例如，删除employees表上的idx_firstname索引：

ALTER TABLE employees DROP INDEX idx_firstname;

或者，如果使用的是较新版本的MySQL，并且知道索引名在数据库中是唯一的（不包括表名前缀），也可以使用以下语句：

DROP INDEX idx_firstname ON employees;

四、索引的适用场景

• 当经常需要根据某个字段进行查询时，可以考虑在该字段上创建索引。
• 当表中的数据量较大，且经常需要进行全表扫描时，索引可以显著提高查询效率。
• 当表中的数据经常需要按照某个字段进行排序时，可以在该字段上创建索引以提高排序效率。
• 在进行表连接操作时，如果连接条件中涉及的字段没有索引，则会导致连接效率低下。此时，可以考虑在连接条件涉及的字段上创建索引以提高连接效率。

五、索引的优缺点

• 优点：
  • 提高数据检索效率，减少数据库的I/O成本。
  • 保证数据的唯一性（对于唯一索引和主键索引）。
  • 加速表与表之间的连接。
  • 在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。
• 缺点：
  • 占用额外的物理存储空间。
  • 创建和维护索引需要一定的时间成本。
  • 当对表中的数据进行增加、删除和修改操作时，索引也需要动态地维护，这可能会降低数据的维护速度。

数据库索引是数据库管理系统中一个非常重要的特性，它能够提高数据检索的效率、保证数据的唯一性并加速表与表之间的连接。然而，索引并不是万能的，它也有其缺点和局限性。在实际应用中需要根据实际的数据使用模式来合理选择和创建索引。