目录

  1. CREATE INDEX 语句简介
  2. CREATE INDEX 语句的语法
  3. CREATE INDEX 语句的使用场景
  4. CREATE INDEX 语句的注意事项
  5. 参考资料

CREATE INDEX 语句简介

CREATE INDEX 语句用于在表的一个或多个列上创建索引，以提高查询效率。索引是数据库对象，它帮助数据库管理系统（DBMS）更快速地查询数据。通过索引，查询时不必逐行扫描表中的所有记录，而是通过索引直接定位到数据。

虽然索引能加速查询，但会增加数据插入、更新和删除时的开销，因为数据库必须更新索引。通常在频繁查询的列上创建索引，以提高查询性能，但对于更新频繁的列，创建索引时需要谨慎。

CREATE INDEX 语句的语法

1. 基本语法

CREATE INDEX index_name
ON table_name (column_name);
  • index_name：指定索引的名称。
  • table_name：指定要在其上创建索引的表名。
  • column_name：指定要创建索引的列。

例如，创建一个简单的索引：

CREATE INDEX idx_employee_name
ON employees (last_name);

在这个例子中，idx_employee_name 是索引的名称，employees 表上的 last_name 列上创建了索引。

2. 创建复合索引（多个列的组合）

如果查询涉及多个列，可以创建复合索引，索引多个列的组合：

CREATE INDEX idx_employee_name_and_age
ON employees (last_name, first_name);

在这个例子中，last_name 和 first_name 列的组合被索引。如果查询中同时包含这两个列，将受益于该复合索引。

3. 创建唯一索引（UNIQUE）

在创建索引时，可以指定 UNIQUE 关键字，确保索引列中的值唯一。这对于确保数据的完整性非常重要，例如避免重复的电子邮件地址。

CREATE UNIQUE INDEX idx_unique_email
ON employees (email);

这个索引确保 employees 表中的 email 列没有重复的电子邮件地址。

4. 创建全文索引（FULLTEXT）

在支持全文索引的数据库中（如 MySQL 和 PostgreSQL），可以创建全文索引，以加速文本搜索：

CREATE FULLTEXT INDEX idx_fulltext_description
ON products (description);

在这个例子中，description 列创建了一个全文索引，适用于执行复杂的文本搜索。

5. 创建聚集索引（CLUSTERED）

在某些数据库中，PRIMARY KEY 通常会创建聚集索引。聚集索引将数据表中的数据按索引排序。每个表只能有一个聚集索引。

CREATE CLUSTERED INDEX idx_clustered_order
ON orders (order_date);

在这个例子中，orders 表的 order_date 列上创建了一个聚集索引，数据将按 order_date 排序存储。

CREATE INDEX 语句的使用场景

  1. 提高查询性能：
    • 创建索引的主要目的是加速查询。尤其对于经常被查询的列（如 WHERE 子句中使用的列），创建索引能够显著提高查询效率。
    CREATE INDEX idx_employee_department ON employees (department_id); 这个索引提高了按 department_id 查询员工的效率。
  2. 优化排序操作：
    • 对于经常进行排序（ORDER BY）的列，创建索引也能提高性能。例如：
    CREATE INDEX idx_employee_name_sorted ON employees (last_name, first_name); 在查询时，按 last_name 和 first_name 排序会更快。
  3. 加速连接操作（JOIN）：
    • 在多个表进行连接（JOIN）时，连接条件中的列应该有索引，能够加速表与表之间的连接操作。
    CREATE INDEX idx_order_customer_id ON orders (customer_id); 这个索引将加速 orders 表与 customers 表之间基于 customer_id 列的连接操作。
  4. 确保数据唯一性：
    • 使用 UNIQUE 关键字创建索引，可以确保列中没有重复值，从而保证数据的唯一性。

CREATE INDEX 语句的注意事项

  1. 性能权衡：
    • 虽然索引能够加速查询，但也会带来额外的存储开销，并且在进行数据插入、更新或删除时，需要额外的时间来维护索引。因此，在选择是否创建索引时，需要平衡查询性能与写入性能。
  2. 避免不必要的索引：
    • 在列的选择上应谨慎，避免在不常用于查询、排序或连接的列上创建索引。过多的索引会消耗大量磁盘空间，并降低数据更新和删除的性能。
  3. 索引优化：
    • 当查询中涉及多个列时，复合索引可以提高性能，但是复合索引的顺序应该与查询的过滤条件（WHERE 子句）一致。索引列的顺序对于查询效率至关重要。
  4. 唯一性和约束：
    • PRIMARY KEY 和 UNIQUE 约束会自动为相应的列创建索引，因此无需为这些列再次创建索引。
  5. 删除索引：
    • 如果不再需要索引，可以通过 DROP INDEX 语句删除索引。删除不再使用的索引有助于节省存储空间。
    DROP INDEX idx_employee_name;

参考资料