跳到主要内容

MySQL 复合索引

在MySQL中，索引是提高查询性能的重要工具。复合索引（也称为组合索引或多列索引）是一种在多个列上创建的索引。与单列索引不同，复合索引可以同时涵盖多个列，从而在查询中更高效地过滤数据。

什么是复合索引？​

复合索引是在表的多个列上创建的索引。它允许数据库在这些列的组合上快速查找数据。复合索引的顺序非常重要，因为MySQL只能从左到右使用索引。这意味着，如果你在列A和列B上创建了一个复合索引，查询中必须包含列A才能有效利用该索引。

复合索引的语法​

创建复合索引的语法如下：

sql
CREATE INDEX index_name ON table_name (column1, column2, ...);

例如，假设我们有一个名为 users 的表，包含 first_name 和 last_name 列。我们可以创建一个复合索引：

sql
CREATE INDEX idx_name ON users (first_name, last_name);

复合索引的工作原理​

复合索引的工作原理类似于电话簿。电话簿通常按姓氏和名字排序。如果你知道某人的姓氏和名字，你可以快速找到他们的电话号码。同样，复合索引允许数据库在多个列上快速查找数据。

复合索引的顺序​

复合索引的顺序非常重要。MySQL只能从左到右使用索引。例如，如果你在 (A, B) 上创建了一个复合索引，以下查询可以利用该索引：

sql
SELECT * FROM table_name WHERE A = 'value1' AND B = 'value2';

但是，以下查询无法有效利用该索引：

sql
SELECT * FROM table_name WHERE B = 'value2';

因为索引是从左到右使用的，查询中必须包含列A才能有效利用复合索引。

复合索引的实际应用​

案例1：用户表查询优化​

假设我们有一个 users 表，包含以下列：

sql
CREATE TABLE users (
    id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100)
);

我们经常需要根据 first_name 和 last_name 来查询用户信息。为了提高查询性能，我们可以创建一个复合索引：

sql
CREATE INDEX idx_name ON users (first_name, last_name);

现在，以下查询将能够利用该索引：

sql
SELECT * FROM users WHERE first_name = 'John' AND last_name = 'Doe';

案例2：订单表查询优化​

假设我们有一个 orders 表，包含以下列：

sql
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    total_amount DECIMAL(10, 2)
);

我们经常需要根据 customer_id 和 order_date 来查询订单信息。为了提高查询性能，我们可以创建一个复合索引：

sql
CREATE INDEX idx_customer_order ON orders (customer_id, order_date);

现在，以下查询将能够利用该索引：

sql
SELECT * FROM orders WHERE customer_id = 123 AND order_date = '2023-10-01';

复合索引的注意事项​

  1. 索引顺序：复合索引的顺序非常重要。确保查询中的列顺序与索引中的列顺序一致。
  2. 索引选择性：选择性高的列（即具有唯一值的列）应该放在索引的前面，以提高索引的效率。
  3. 索引大小：复合索引会占用更多的存储空间，因此在创建复合索引时要权衡存储和性能。

总结​

复合索引是MySQL中优化查询性能的强大工具。通过在多个列上创建索引，可以显著提高查询效率。然而，复合索引的顺序和选择性是决定其效果的关键因素。在实际应用中，应根据查询需求和数据特点来设计和创建复合索引。

附加资源​

练习​

  1. 创建一个包含 first_name 和 last_name 列的复合索引，并编写一个查询来测试其性能。
  2. 分析你的查询，看看是否可以通过调整复合索引的顺序来进一步提高性能。
提示

在实际应用中，建议使用 EXPLAIN 语句来分析查询的执行计划，以确定是否有效利用了复合索引。