SQL回表

在面试的时候，面试官很多会提问道优化SQL，至于怎么优化，了不起就不用再继续赘述这个问题，我们往下延伸，你了解索引覆盖么？

索引覆盖（Covering Index）或称为覆盖索引，是数据库中的一种优化手段。

当我们执行一个SQL查询时，如果只需要查询某几个字段的值，并且这几个字段的数据都已经被包含在某一个索引中（而不是全表扫描），那么数据库引擎就会直接通过这个索引来取得数据，而无需再回表查询，从而大大减少了I/O操作，提高了查询效率。

索引覆盖的优点就比如有：

• 减少I/O次数：因为通过覆盖索引可以直接获取数据，所以不需要再回表查询，从而减少了I/O次数。

• 提高查询速度：由于减少了I/O操作，查询速度自然也得到了提高。

• 索引的选择性：选择性是指不重复的索引值与数据表的总记录数的比值。选择性越高，通过索引筛选出的数据就越少，从而提高了查询效率。

这个我们就牵扯到回表查询了？面试官一般就会套路的继续往下问，那你知道回表操作么？

SQL回表

那么什么是 SQL 回表呢？

SQL回表，在MySQL数据库特别是InnoDB存储引擎中，是一个重要的概念。

SQL回表是指在使用非聚簇索引（也称为辅助索引或二级索引）进行查询时，由于非聚簇索引中只存储了索引字段的值和对应的主键（聚簇索引）键值，因此，如果需要获取非索引列的数据，则需要根据主键（聚簇索引）中的键值去查找实际的数据行。这个过程被称为“回表”。

回表的原理

• 非聚簇索引结构：非聚簇索引的叶子节点存储的是（索引列的值，主键的值）。

• 查询过程：

  当使用非聚簇索引进行查询时，首先通过非聚簇索引找到满足条件的主键键值。然后，根据这些主键键值，再回到聚簇索引（主键索引）中查找完整的数据行。

假设有一个用户表users，包含id（主键）、name和age三个字段，其中在name字段上建立了非聚簇索引。

执行查询SELECT * FROM users WHERE name='Tom'时，会发生回表。因为首先会通过name上的非聚簇索引找到满足条件的id，然后再根据这些id回到聚簇索引中查找完整的用户数据。

而查询SELECT id, name FROM users WHERE name='Tom'则不会回表，因为所需的数据都在非聚簇索引中可以找到。

而回表操作会增加I/O次数，从而可能影响查询性能。特别是在大表和复杂查询场景下，回表操作可能成为性能瓶颈。

为了减少回表操作，可以考虑将需要查询的字段加入到索引中，形成复合索引（也称为联合索引或覆盖索引）。这样，查询时就可以直接从索引中获取到需要的数据，而无需回表。

所以，建立索引的时候，我们要非常注意，并不是说索引不好，而是说要会加才可以。

索引的最左匹配原则

有的时候，我们建立索引大部分都不会只是单独的一个字段，所以就有了复合索引。

索引的最左匹配原则（Leftmost Prefix Rule） 主要是在使用复合索引（也称为多列索引或多字段索引）时的一个关键概念。这个原则指出，当使用复合索引进行查询时，查询条件应该尽可能地从索引的最左边开始匹配，这样索引才能被有效地使用。

当你基于复合索引进行查询时，查询条件必须包含索引的最左边的一列或多列，以便索引能够被有效地使用。例如，如果你有一个基于(last_name, first_name)的复合索引，以下查询可以有效地使用这个索引：

查询基于last_name：SELECT * FROM employees WHERE last_name = 'Smith';

查询基于last_name和first_name：SELECT * FROM employees WHERE last_name = 'Smith' AND first_name = 'John'; 但是，以下查询则不能有效地使用这个索引（因为它没有包含索引的最左边的列last_name）：

查询仅基于first_name：SELECT * FROM employees WHERE first_name = 'John';

在创建复合索引时，列的顺序很重要。你应该将最常用于查询条件的列放在索引的最左边。例如，如果你经常基于last_name进行查询，但很少基于first_name进行查询，那么你应该创建一个基于(last_name, first_name)的索引，而不是基于(first_name, last_name)的索引。

虽然最左匹配原则是一个重要的概念，但并不意味着你必须始终遵循它。在实际应用中，你需要根据查询的需求和数据的分布来决定是否使用复合索引以及索引的列顺序。