联合索引的最左原则、回表、覆盖索引1 最左原则概述1.2 示例解释1.3 联合索引使用场景1.4 联合索引注意事项2 回表查询2.1 回表含义与概念2.2 聚集索引2.2.1 特性2.2.2 示例2.3 非聚集索引2.3.1 特性2.3.2 示例3 覆盖索引4 总结

大家好，我是欧阳方超，公众号同名。本次博文讲讲SQL联合索引中的最左原则，并由此引出回表和覆盖索引的问题。

1 最左原则概述

在MySQL的联合索引中，最左原则是一个重要的概念，它规定了联合索引的使用方式，即什么情况下的查询会使用到联合索引，什么情况下的查询不会使用联合索引。比如创建一个这样的包含多个列的联合索引：

create index idx_col1_col2_col3 on test_table (col1, col2, col3);

这个索引会按照col1、col2、col3的顺序存储数据。

最左原则意味着，该索引可以用于优化这样的查询，查询中包含对创建联合索引时指定的多个列的最左边的列col1的单独查询，对col1和col2组合的查询，对col1和col3组合的查询，以及对col1和col2和col3组合的查询。但是仅对col2或col3的单独查询，或者col2和col3的组合查询，该联合索引通常不会被充分利用（除非col1在where子句中已经有确定的值）。

1.2 示例解释

结合一个示例解释一下联合索引。假设有一个名为idx_customer_order_product的联合索引是在orders表上创建的，索引包含customer_id、order_date和product_id列，即：

create index idx_customer_order_product on orders (customer_id, order_date, product_id)

下面是不同查询语句对该联合索引的使用情况

使用最左列的查询

SELECT * FROM orders WHERE customer_id = 123;

此查询可以充分利用联合索引，因为它使用了联合索引的最左边的列customer_id。MySQL会使用该索引快递定位满足customer_id=123的行。

使用最左两列的查询

SELECT * FROM orders WHERE customer_id = 123 AND order_date = '2024-01-01';

该查询页可以使用联合索引，首先通过customer_id缩小范围，然后在这些行中找到满足order_date=2024-01-01的行。

使用全部三列的查询

SELECT * FROM orders WHERE customer_id = 123 AND order_date = '2024-01-01' AND product_id = 456;

这个查询通常不会充分利用联合索引，因为它没有使用最左边的列customer_id。索然MySQL可能会扫描索引，但可能不会获取性能提升，因为它不是按联合索引的最左原则使用。

SELECT * FROM orders WHERE order_date = '2024-01-01' AND product_id = 456;

这个查询也不会很好地利用联合索引，因为它跳过了最左边的customer_id列。

SELECT * FROM orders WHERE product_id = 456;

此查询也不会使用联合索引，因为它只涉及联合索引的最右边的列，不符合最左原则。

1.3 联合索引使用场景

通常，联合索引适用于经常在多个列上进行查询或排序的情况，例如在where子句中同时使用多个列作为过滤条件，或者在order by子句中对多个列进行排序。

1.4 联合索引注意事项

合理设计联合索引的列顺序

创建联合索引时列的出现顺序很重要，因为MySQL会按照列的顺序使用索引。一般来说，将选择性最高的列放在最左边，因为这样可以更快地缩小搜索范围。选择性是指不重复的索引值与表中记录总数的比值。例如，如果 customer_id 有很多不同的值，使用 customer_id 作为最左边的列可以快速将搜索范围缩小到一小部分数据，然后再通过后续列进行筛选。把最常用的列和选择性高的列放在联合索引的左边。这样可以确保在大多数情况下，查询都能使用联合索引。例如，如果 customer_id 经常用于单独查询，而 order_date 和 product_id 经常一起使用，那么 (customer_id, order_date, product_id) 的顺序可能是一个不错的选择。

联合索引不是万能的

联合索引不是万能的，它仅在涉及联合索引中的列的查询或排序操作时有用，对于其他列的查询可能无法提供帮助。

联合索引遇上范围查询条件

在 WHERE 子句中使用范围条件时，最左原则仍然适用，但范围条件之后的列可能无法充分利用索引。例如：

CREATE INDEX idx_col1_col2_col3 ON your_table (col1, col2, col3);SELECT * FROM your_table WHERE col1 = 1 AND col2 > 10 AND col3 = 20;

在此查询中，col1 和 col2 可以利用索引，但 col3 可能无法充分利用，因为 col2 使用了范围条件 > 10。因此，对于包含范围条件的查询，要将范围条件的列放在联合索引的右侧。

与 ORDER BY 和 GROUP BY 的配合使用

联合索引可以帮助优化 ORDER BY 和 GROUP BY 操作，但要确保列的顺序与联合索引的顺序一致。例如：

CREATE INDEX idx_order_group ON your_table (col1, col2);SELECT * FROM your_table WHERE col1 = 1 ORDER BY col2;

这个查询可以利用联合索引，因为 ORDER BY 的列顺序与联合索引的顺序匹配，并且 WHERE 子句中使用了最左边的列。

2 回表查询2.1 回表含义与概念

回表查询是指在使用非聚集索引（也叫二级索引）查询数据时，需要先通过索引先找到对应的主键值，然后再通过主键值去聚集索引中查找完整的记录行的过程。这个查找完整记录的额外步骤，就叫“回表”。这个概念需要两个概念支持：聚集索引和非聚集索引，下面来解释一下。

2.2 聚集索引

聚集索引是表的物理存储顺序与索引顺序相同的索引。换句话说，数据表中的记录是按照聚集索引的顺序存储的。每个表只能有一个聚集索引，因为表的数据只能按一个顺序排列。在 MySQL 中，默认情况下，主键索引（primary key）是聚集索引。即，主键索引决定了表数据的物理顺序。

2.2.1 特性

物理顺序与索引顺序一致：表中的数据按聚集索引的顺序存储，因此读取数据时是按照索引的顺序进行的。这意味着聚集索引的数据存储会直接影响查询性能。

只有一个聚集索引：每个表只能有一个聚集索引，因为表的物理存储顺序只能有一个。

数据存储在叶子节点：在聚集索引中，索引的叶子节点存储了实际的表数据，不仅是索引的关键字。

快速查找主键：因为聚集索引直接存储表数据，所以通过主键查找记录非常快。

2.2.2 示例

假设有一个 users 表，其中有 id（主键）、name 和 email 字段：

CREATE TABLE users (    id INT PRIMARY KEY,    name VARCHAR(100),    email VARCHAR(100));

在这个表中，主键 id 则是聚集索引。MySQL 会将所有数据按 id 排序存储。通过 id 查找记录时，不需要额外查找，直接通过主键即可找到对应的行。

2.3 非聚集索引

非聚集索引是指索引的结构和数据表的存储顺序无关。非聚集索引包含索引的键值和指向实际数据的指针（即行标识符）。非聚集索引的数据并不影响表的物理存储顺序。一个表可以有多个非聚集索引，联合索引、普通索引都属于非聚集索引。

2.3.1 特性

索引与数据分离：非聚集索引的结构独立于表的物理数据存储。它包含了索引的列和指向数据表中实际行的指针（通常是聚集索引的主键）。

多个非聚集索引：一个表可以创建多个非聚集索引。每个非聚集索引都包含了不同的索引列。

查询时需要回表：对于查询所需的列不包含在非聚集索引中的情况，MySQL 会首先通过非聚集索引定位到行的主键或行标识符，再回到表中获取完整的记录数据，即“回表”操作。

2.3.2 示例

假设在上面的 users 表中，除了 id 主键索引，还创建一个关于 email 字段的非聚集索引：

CREATE INDEX idx_email ON users(email);

在这种情况下，idx_email 是一个非聚集索引，存储了 email 字段的值和对应的 id（主键）。如果按照email查询所有字段的值，MySQL 会通过非聚集索引找到对应的 id，然后再使用 id 查找完整的行数据，即可得到所有字段的值。

3 覆盖索引

为了避免回表的操作，通常可以通过设计 覆盖索引（Covering Index），即索引包含了查询所需的所有列，这样查询可以仅通过索引来完成，避免了回表的过程。

覆盖索引是指查询所需的数据可以直接从索引中获取，而无需回表查询数据行。在联合索引中，如果查询的字段都包含在联合索引的列中，就可能形成覆盖索引。

示例

假设有以下一个联合索引：

CREATE INDEX idx_user_info ON users (user_id, username, email);

对于以下查询：

SELECT user_id, username FROM users WHERE user_id = 123;

这个查询仅需要user_id和username，而则会两个字段都在联合索引中，因此该查询可以直接从索引中获取数据，无需再访问表的数据行，这就是一个覆盖索引的使用场景。

覆盖索引的优点在于其提高了查询性能，因为避免了从表中读取数据行，减少了I/O操作，特别是对于大表或复杂查询，使用覆盖索引可以显著提高性能。

4 总结

本文主要讲解 SQL 联合索引的最左原则，包括其概念、示例解释及使用场景和注意事项，同时阐述回表和覆盖索引。最左原则影响联合索引使用，回表是二级索引查完整记录的过程，聚集索引与表物理存储同序，非聚集索引结构分离，覆盖索引可避免回表提升性能。

我是欧阳方超，把事情做好了自然就有兴趣了，如果你喜欢我的文章，欢迎点赞、转发、评论加关注。我们下次见。