mysql 每次查询会生效几个索引|极客笔记

mysql 每次查询会生效几个索引

在 MySQL 中，为了提高检索效率，我们经常会对经常被查询的列创建索引。索引可以帮助数据库快速定位到符合条件的数据，从而加快查询速度。但是，在一个查询中，MySQL 实际上会同时生效多个索引吗？这个问题经常困扰着一些开发者。在本文中，我们将深入探讨这个问题。

索引的作用

首先让我们回顾一下索引的作用。索引是一种数据结构，它可以帮助数据库系统快速地定位到符合条件的数据。在 MySQL 中，常见的索引类型包括 B树索引、Hash 索引、全文索引等。创建合适的索引可以减少数据库的全表扫描，加快查询的速度。

当我们执行一个 SQL 查询语句时，MySQL 会根据查询条件，选择合适的索引来提高查询效率。如果查询条件中涉及到了索引的列，MySQL 就会利用这个索引进行定位。但是，一条 SQL 查询语句是否一次只能生效一个索引呢？这就是我们接下来要探讨的问题。

MySQL 查询中索引的生效情况

在 MySQL 中，一条 SQL 查询语句在执行时，通常只会最多生效一个索引。当我们执行一条查询语句时，MySQL 会根据查询条件选择一个最优的索引来加速查询，这个索引通常是能够覆盖查询条件的最左前缀索引。换句话说，MySQL 会选择一个能够尽量减少数据检索次数的索引来提高查询效率。

下面我们通过一个示例来演示一条查询语句中只能生效一个索引的情况。

CREATE TABLE employee (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    department VARCHAR(100),
    INDEX idx_name (name),
    INDEX idx_department (department)
);

EXPLAIN SELECT * FROM employee WHERE name = 'Alice' AND department = 'IT';

在上面的示例中，我们创建了一个名为 employee 的表，包含了 id、name、age 和 department 四个字段。我们分别在 name 和 department 字段上创建了索引。接着我们执行一条查询语句，查询 employee 表中 name 为 ‘Alice’ 且 department 为 ‘IT’ 的记录，并通过 EXPLAIN 关键字来查看查询计划。

根据查询计划可以看到，MySQL 实际上只会使用一个索引进行查询，而不会同时生效两个索引。这是因为 MySQL 会尽量选择一个能够覆盖查询条件的最左前缀索引来减少数据检索次数。

多个索引的优化

虽然一条查询语句只会生效一个索引，但是我们仍然可以通过优化来提高查询效率。以下是一些优化技巧：

联合索引

如果查询条件中涉及多个列，并且这些列组合在一起经常被查询，我们可以考虑创建联合索引。联合索引可以针对多个列进行排序，这样可以减少查询的时候需要检索的数据量。

ALTER TABLE employee ADD INDEX idx_name_department (name, department);

EXPLAIN SELECT * FROM employee WHERE name = 'Alice' AND department = 'IT';

覆盖索引

当查询的字段在索引中都能够找到时，MySQL 就可以直接从索引中获取数据，而无需再去查找对应的行。这样的索引称为覆盖索引。使用覆盖索引可以避免访问表的成本，提高查询效率。

EXPLAIN SELECT name, department FROM employee WHERE name = 'Alice' AND department = 'IT';

最左前缀原则

在创建联合索引时，需要遵循最左前缀原则。也就是说，当我们查询索引中的一个列时，从左边开始，以该列作为最左前缀的索引才会被使用，而不会使用其他部分。

 CREATE INDEX idx_name_department_age ON employee (name, department, age);

EXPLAIN SELECT * FROM employee WHERE name = 'Alice';

总结

在一条 SQL 查询语句中，MySQL 实际上只会生效一个索引。通过优化查询条件、创建联合索引和覆盖索引等方式，我们可以提高查询的效率，避免全表扫描，加快数据库的查询速度。在实际应用中，我们应该合理设计索引结构，以提高数据库查询性能。

mysql 每次查询会生效几个索引