一文了解mysql 回表查询、索引覆盖、最左匹配、聚簇索引、非聚簇索引、索引失效

MySQL中的回表查询、索引覆盖、最左匹配、聚簇索引和非聚簇索引等概念如下：

1. 回表查询：当查询的数据不能全部通过索引被覆盖时，MySQL会需要回到原表去查询这些数据。
2. 索引覆盖：当查询的列都包含在某个索引中，那么MySQL不需要回表查询，可以直接从索引中获取所需数据，这称为索引覆盖。
3. 最左匹配：在创建复合索引时，MySQL会遵循最左匹配原则，即在查询中使用索引时，必须从索引的最左边开始。
4. 聚簇索引：在InnoDB存储引擎中，表数据和索引数据是存储在一起的，这样的索引被称为聚簇索引。
5. 非聚簇索引：与聚簇索引相对，存储的是行的指针的索引。
6. 索引失效：由于各种原因，索引不能正确工作，这时候就发生了索引失效。常见原因包括使用函数、类型转换、操作符等。

示例代码：

-- 创建一个简单的表
CREATE TABLE users (
    id INT NOT NULL AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    username VARCHAR(50),
    email VARCHAR(100),
    PRIMARY KEY (id),
    INDEX idx_lastname (last_name),
    INDEX idx_firstname_lastname (first_name, last_name)
);
 
-- 最左匹配示例，使用索引idx_firstname_lastname
SELECT first_name, last_name FROM users WHERE first_name = 'John' AND last_name = 'Doe';
 
-- 索引覆盖示例，不需要回表查询
SELECT first_name, last_name FROM users WHERE last_name = 'Doe';
 
-- 查询时使用函数导致索引失效
SELECT * FROM users WHERE YEAR(created_at) = 2020;

在这个示例中，我们创建了一个包含一个聚簇索引和两个非聚簇索引的表。第一个查询使用了索引idx_firstname_lastname，满足最左匹配原则。第二个查询只查询索引覆盖的列，不需要回表。最后一个查询使用了函数YEAR()，导致索引失效。