MySQL 使用联合索引和两个单独的索引的区别|极客笔记

MySQL 使用联合索引和两个单独的索引的区别

在MySQL中，索引是一种用于加快数据库查询速度的重要工具。当我们需要在某个字段上进行查询时，使用索引可以大大提高查询的效率。在实际应用中，有时候我们会遇到需要在多个字段上进行联合查询的情况，这时可以使用联合索引。那么，MySQL中使用联合索引和两个单独的索引有什么区别呢？接下来我们将详细解释这两者之间的区别。

什么是联合索引

在MySQL中，如果我们需要在多个字段上进行联合查询，并且这些字段涉及到多个WHERE条件，那么我们可以考虑创建一个联合索引。在创建联合索引时，可以将多个字段组合成一个联合索引，以提高查询效率。

假设我们有一个employees表，其中包含字段id、name、department和salary，现在我们需要根据department和salary字段进行查询，我们可以使用联合索引来提高查询效率。

CREATE INDEX idx_department_salary ON employees (department, salary);

为什么使用联合索引

减少索引的数量：使用联合索引可以减少索引的数量，节省存储空间。
提高查询效率：在多个字段上创建联合索引可以提高联合查询的效率，减少索引扫描的次数。

联合索引和两个单独的索引的区别

联合索引的顺序对查询的影响

联合索引的顺序十分重要，查询的效率取决于WHERE条件中的字段顺序与联合索引的顺序是否匹配。如果查询条件中的字段顺序与联合索引的顺序一致，那么查询效率会很高；反之，则会导致查询效率降低。
1. 单独的索引更适合单字段查询
当我们需要在某个字段上进行单字段查询时，使用单独的索引更加适合。单独的索引可以针对单个字段进行优化，提高查询的效率。
1. 联合索引更适合多字段查询
当我们需要在多个字段上进行联合查询时，使用联合索引更为适合。通过使用联合索引，可以在多个字段之间建立联系，提高查询效率。
1. 更新索引的开销
联合索引需要维护多个字段之间的关系，因此更新索引的开销会相对较大。而单独的索引只需要维护单个字段的关系，更新索引的开销较小。

示例代码

接下来我们通过示例代码来演示使用联合索引和两个单独的索引的区别。

-- 创建一个包含10000条数据的employees表
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    department VARCHAR(50),
    salary INT
);

DELIMITER //

CREATE PROCEDURE generate_data()
BEGIN
    DECLARE i INT DEFAULT 1;

    WHILE i <= 10000 DO
        INSERT INTO employees (id, name, department, salary) VALUES (i, CONCAT('Employee', i), 'IT', RAND() * 10000);
        SET i = i + 1;
    END WHILE;

END//

DELIMITER ;

CALL generate_data();

-- 创建联合索引
CREATE INDEX idx_department_salary ON employees (department, salary);

-- 创建单独的索引
CREATE INDEX idx_department ON employees (department);
CREATE INDEX idx_salary ON employees (salary);

-- 查询语句
EXPLAIN SELECT * FROM employees WHERE department = 'IT' AND salary > 5000;

运行结果

使用EXPLAIN语句可以查看查询语句的执行计划，从而分析索引的使用情况。在本例中，我们将查询语句SELECT * FROM employees WHERE department = 'IT' AND salary > 5000;的执行计划打印出来，来看看在不同索引情况下的执行效果。

使用联合索引idx_department_salary的执行计划：

id  select_type table    type    possible_keys          key                key_len  ref   rows  Extra
1   SIMPLE      employees range   idx_department_salary   idx_department_salary  106   NULL  500   Using where

可以看到，MySQL使用了idx_department_salary来进行范围查询，查询效率较高。

使用单独索引idx_department和idx_salary的执行计划：

id  select_type table    type    possible_keys    key      key_len  ref          rows  Extra
1   SIMPLE      employees ref     idx_department   idx_department  106    const         5000  Using where
2   SIMPLE      employees index   idx_salary       idx_salary      4    NULL          10000 Using where

使用单独索引时，MySQL分别使用了idx_department和idx_salary进行查询，可能导致扫描的次数增多，影响查询效率。