mysql学习教程，从入门到精通，SQL 创建索引(CREATE INDEX 语句)（35）

1、SQL 创建索引(CREATE INDEX 语句)

在SQL中，创建索引（CREATE INDEX）是一种用于提高数据库查询性能的方法。索引类似于书的目录，通过它可以更快地定位到表中的特定行。以下是一个创建索引的示例，以及对其各部分的解释。
假设我们有一个名为 employees 的表，表结构如下：

CREATE TABLE employees (employee_id INT PRIMARY KEY,first_name VARCHAR(50),last_name VARCHAR(50),department_id INT,hire_date DATE
);

我们希望在 last_name 列上创建一个索引，以便更快地执行基于姓氏的查询。以下是创建索引的SQL语句：

CREATE INDEX idx_last_name
ON employees (last_name);

解释

• CREATE INDEX：这是创建索引的SQL命令。
• idx_last_name：这是索引的名称。在创建索引时，为其指定一个有意义的名称是一个好习惯，这有助于在将来管理索引。
• ON employees (last_name)：这指定了索引要创建的表和列。在这个例子中，索引将创建在 employees 表的 last_name 列上。

创建复合索引

有时，你可能希望在一个或多个列上创建复合索引，以优化涉及这些列的多个列的查询。例如，我们希望在 last_name 和 first_name 列上创建一个复合索引：

CREATE INDEX idx_full_name
ON employees (last_name, first_name);

创建唯一索引

如果你希望确保列中的值是唯一的，可以创建唯一索引。例如，我们希望在 employee_id 列上创建一个唯一索引（虽然在这个例子中 employee_id 已经是主键，自然唯一，但这里仅作为示例）：

CREATE UNIQUE INDEX idx_unique_employee_id
ON employees (employee_id);

创建索引时的注意事项

1. 性能：虽然索引可以显著提高查询性能，但它们也会增加插入、更新和删除操作的成本，因为数据库需要维护索引。
2. 空间：索引会占用额外的存储空间。
3. 选择性：索引在选择性高的列上更有效。选择性是指列中不同值的数量与总行数之比。例如，性别列（只有“男”和“女”两个值）的选择性较低，而身份证号列的选择性较高。

当然可以，以下是几个使用CREATE INDEX语句创建索引的具体案例：

案例一：创建普通索引

假设我们有一个名为students的表，包含以下字段：student_id（学生ID）、first_name（名字）、last_name（姓氏）、grade（年级）和enrollment_date（入学日期）。我们希望在last_name字段上创建一个索引，以便加快基于姓氏的查询速度。

CREATE INDEX idx_last_name
ON students (last_name);

案例二：创建复合索引

现在，我们希望在students表上创建一个复合索引，以便能够同时加快基于姓氏和名字的查询速度。复合索引是在多个字段上创建的索引，当查询条件包含这些字段时，数据库可以利用复合索引来提高查询性能。

CREATE INDEX idx_full_name
ON students (last_name, first_name);

案例三：创建唯一索引

假设我们有一个名为courses的表，包含以下字段：course_id（课程ID）、course_name（课程名称）和course_code（课程代码）。我们希望确保course_code字段中的值是唯一的，因此可以在该字段上创建一个唯一索引。

CREATE UNIQUE INDEX idx_unique_course_code
ON courses (course_code);

唯一索引不仅提高了查询性能，还确保了数据的唯一性。如果尝试插入一个已经存在的course_code值，数据库将拒绝该操作并返回错误。

案例四：在大数据表上创建索引

假设我们有一个名为orders的表，该表包含大量的订单数据，并且我们经常需要基于customer_id和order_date字段来查询订单。为了提高查询性能，我们可以在这两个字段上创建一个复合索引。

CREATE INDEX idx_customer_order_date
ON orders (customer_id, order_date);

这个复合索引将帮助数据库更快地定位到特定客户的订单，特别是当查询条件同时包含customer_id和order_date字段时。

注意事项

1. 索引的选择性：索引在选择性高的列上更有效。选择性是指列中不同值的数量与总行数之比。例如，在性别列上创建索引可能并不是一个好的选择，因为性别列通常只有几个不同的值（如“男”和“女”）。
2. 索引的维护成本：虽然索引可以显著提高查询性能，但它们也会增加插入、更新和删除操作的成本。因为数据库需要在这些操作发生时维护索引。
3. 索引的数量：过多的索引可能会导致性能问题。每个索引都需要占用额外的存储空间，并且在插入、更新和删除操作时都需要进行维护。因此，应该根据实际需求合理创建索引。
   通过以上案例，我们可以看到CREATE INDEX语句在优化数据库查询性能方面的重要作用。在实际应用中，应该根据具体的查询需求和数据特点来选择合适的索引类型和字段。
   以下是一些实际的SQL创建索引的案例，这些案例基于不同的数据库表和查询需求，展示了如何使用CREATE INDEX语句来优化查询性能。

案例一：在员工表上创建索引

假设我们有一个名为employees的表，该表存储了公司的员工信息。我们经常在last_name和department_id字段上进行查询，因此希望在这两个字段上创建索引。

-- 在last_name字段上创建普通索引
CREATE INDEX idx_employees_last_name
ON employees (last_name);-- 在department_id字段上创建普通索引
CREATE INDEX idx_employees_department_id
ON employees (department_id);
-- 或者，我们可以创建一个复合索引，以同时优化基于last_name和department_id的查询
CREATE INDEX idx_employees_last_name_department_id
ON employees (last_name, department_id);

案例二：在订单表上创建索引

假设我们有一个名为orders的表，该表存储了公司的订单信息。我们经常在customer_id、order_date和status字段上进行查询和过滤，因此希望在这些字段上创建索引。

-- 在customer_id字段上创建普通索引
CREATE INDEX idx_orders_customer_id
ON orders (customer_id);
-- 在order_date字段上创建普通索引
CREATE INDEX idx_orders_order_date
ON orders (order_date);-- 在status字段上创建普通索引（如果status字段的选择性较高）
CREATE INDEX idx_orders_status
ON orders (status);-- 或者，我们可以根据查询需求创建复合索引
-- 例如，优化基于customer_id和order_date的查询
CREATE INDEX idx_orders_customer_id_order_date
ON orders (customer_id, order_date);

案例三：在商品表上创建唯一索引

假设我们有一个名为products的表，该表存储了公司的商品信息。我们希望确保每个商品的product_code是唯一的，因此可以在该字段上创建唯一索引。

-- 在product_code字段上创建唯一索引
CREATE UNIQUE INDEX idx_products_unique_product_code
ON products (product_code);

案例四：在文章表上创建全文索引

假设我们有一个名为articles的表，该表存储了公司的文章信息。我们经常需要在文章的content字段中进行全文搜索，因此可以在该字段上创建全文索引（注意，全文索引的创建方式可能因数据库系统而异，以下以MySQL为例）。

-- 在MySQL中创建全文索引（需要确保数据库和表支持全文索引功能）
CREATE FULLTEXT INDEX idx_articles_fulltext_content
ON articles (content);

注意事项

1. 索引的选择性：索引在选择性高的列上更有效。选择性是指列中不同值的数量与总行数之比。例如，在性别或布尔类型的列上创建索引可能并不是一个好的选择，因为这些列的选择性通常很低。
2. 索引的维护成本：虽然索引可以显著提高查询性能，但它们也会增加插入、更新和删除操作的成本。因为数据库需要在这些操作发生时维护索引。因此，在创建索引时，需要权衡查询性能和数据维护成本。
3. 索引的数量：过多的索引可能会导致性能问题。每个索引都需要占用额外的存储空间，并且在插入、更新和删除操作时都需要进行维护。因此，应该根据实际需求合理创建索引。
   以上案例展示了如何在不同的数据库表和字段上创建索引，以优化查询性能。在实际应用中，应该根据具体的查询需求和数据特点来选择合适的索引类型和字段。