MySQL-索引：聚集索引、覆盖索引、组合索引、前缀索引、唯一索引（附带例子解释）

1、聚集索引

• 在数据库中，聚集索引决定了表数据行的物理存储顺序。在InnoDB存储引擎中，每个表都有一个聚簇索引。
• 聚集索引的叶子节点直接包含行记录的数据（主键值和列数据），因此对于查询来说，如果查询条件命中了聚集索引，可以直接从索引树找到所需的数据，无需回表操作。
• 一个表只能有一个聚集索引，并且通常建议选择那些具有唯一性、访问频繁且高度排序的列作为聚集索引。

假设我们有一个用户表users，其中包含以下字段

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT, -- 假设这是主键
    username VARCHAR(50),
    email VARCHAR(255),
    created_at TIMESTAMP
);

在InnoDB存储引擎中，默认情况下id作为聚集索引。这意味着表中的行是按id的升序物理存储的，并且每个叶子节点包含了完整的行数据（包括username, email, created_at）。当你执行如下查询时，可以直接利用聚集索引来快速定位到指定id的数据行

SELECT * FROM users WHERE id = 123;

2、覆盖索引

• 覆盖索引是指在查询过程中，只需要通过索引就能获取到所有需要的数据，而不需要再回到表中去查找行记录，这样可以减少磁盘I/O，显著提高查询性能。
• 如果一个查询语句所涉及的所有字段都出现在某个索引中，那么这个索引就被称作覆盖索引。不仅包括查询列，还包括结果集中所需的其他列（如在SELECT列表中的列）。

对于上述users表，如果我们创建了一个包含所有查询列的复合索引：

CREATE INDEX idx_username_email ON users (username, email);

现在执行如下查询：

SELECT username, email FROM users WHERE username = 'Alice';

这个查询可以完全使用索引 idx_username_email 来完成，因为索引包含了查询所需的所有列，无需回表获取其他列数据，这就是一个覆盖索引的例子。

3、组合索引

同样以上述的idx_username_email为例，它是一个基于username和email两个列创建的组合索引。当执行如下的查询时，由于遵循了最左前缀原则，可以有效利用该索引：

-- 可以利用索引
SELECT * FROM users WHERE username = 'Bob' AND email LIKE '%example.com';

-- 不会利用索引（仅使用了索引的一部分）
SELECT * FROM users WHERE email LIKE '%example.com';

4、前缀索引

考虑一个文章表articles，其内容字段content非常大：

CREATE TABLE articles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255),
    content TEXT
);

为了减少索引空间占用并加速对title列的模糊查询，我们可以只对title的前几个字符创建前缀索引：

CREATE INDEX idx_title_prefix ON articles (SUBSTR(title, 1, 10));

这样，在执行如下查询时就可以利用前缀索引进行优化：

SELECT * FROM articles WHERE title LIKE 'Introduction%';

5、唯一索引

在users表中，id列已经是一个主键，自动带有唯一约束。如果还需要确保邮箱地址的唯一性，可以添加一个唯一索引：

ALTER TABLE users ADD UNIQUE INDEX idx_unique_email (email);

此时，尝试插入重复邮箱地址的记录将会失败：

INSERT INTO users (username, email) VALUES ('Charlie', 'charlie@example.com');
INSERT INTO users (username, email) VALUES ('David', 'charlie@example.com'); -- 这条语句将抛出违反唯一约束的错误

6、再深入理解覆盖索引

假设我们有一个销售订单表orders，包含以下字段：

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT,
    product_id INT,
    order_date DATE,
    quantity INT,
    price DECIMAL(10,2)
);

为了优化如下的查询性能，我们可以创建一个覆盖索引：

SELECT customer_id, product_id, SUM(quantity) as total_quantity 
FROM orders 
WHERE order_date BETWEEN '2021-01-01' AND '2021-12-31'
GROUP BY customer_id, product_id;

这个查询需要检索在指定日期范围内的所有订单，并按客户ID和产品ID进行分组并计算数量总和。为了使这个查询能使用覆盖索引，我们需要创建一个包括order_date, customer_id, product_id以及quantity的索引：

CREATE INDEX idx_covering ON orders (order_date, customer_id, product_id, quantity);

现在，当执行上述查询时，MySQL可以直接从索引idx_covering中获取到所需的所有数据（customer_id, product_id, quantity），而不需要访问实际的数据行，从而显著提高了查询效率。这就是覆盖索引的应用实例。

7、拓展

索引对WHERE子句中的列名起作用，同时也可以在SELECT子句中发挥作用。但具体情况取决于索引的类型和查询的执行计划。

1）对WHERE条件中的列名起作用：
当SQL查询语句包含WHERE子句时，如果该子句涉及的列有索引，数据库系统可以根据索引快速定位到符合条件的数据行，从而减少磁盘I/O和扫描的数据量，显著提升查询性能。
例如：

   SELECT * FROM orders WHERE order_id = 123;

如果order_id上有索引，那么这个查询可以迅速定位到特定记录。
2）对SELECT子句中的列名间接起作用（覆盖索引）：
如果创建了一个覆盖索引，即索引包含了查询所需要的所有列，那么数据库在执行查询时无需回表获取其他列数据，直接从索引中获取结果，这种情况下索引也对SELECT子句起到了优化效果。
例如：

   CREATE INDEX idx_orders ON orders (order_date, customer_id);
   SELECT customer_id FROM orders WHERE order_date BETWEEN '2021-01-01' AND '2021-12-31';

在这个例子中，虽然我们是从SELECT子句中选择customer_id，但由于idx_orders索引已经包含了查询所需的order_date和customer_id字段，因此能够通过索引来完成整个查询过程，实现所谓的“覆盖索引”。
总之，索引主要用于提高WHERE子句中条件过滤的速度，并且在满足特定条件的情况下，也能有效优化SELECT子句所涉及的列的查询效率。