面试题：MySQL索引优化中的覆盖索引与回表

Question

在MySQL查询优化中，什么是覆盖索引和回表？请举例说明在何种情况下会出现回表操作，以及如何通过合理创建索引来避免回表，以提升查询性能。

Answer 1

覆盖索引

覆盖索引指一个查询语句的执行只用从索引中就能够获取所有需要的数据，而不需要回表操作。当索引包含了查询所需的所有列时，就会使用覆盖索引。例如有表users，结构为(id INT, name VARCHAR(50), age INT)，假设创建了复合索引(name, age)，如果查询SELECT name, age FROM users WHERE name = 'John'，此时仅通过这个索引就能获取到所需数据，因为索引中包含了查询的name和age列，这就是覆盖索引。

回表

回表是指先通过索引找到满足条件的记录的主键值，然后再根据主键值回到表中获取完整的行记录数据。例如对于上述users表，若只创建了name列的单索引，当执行查询SELECT * FROM users WHERE name = 'John'时，通过name索引只能获取到name列及主键id，但查询需要所有列的数据，所以就需要根据id再回到表中获取其他列的数据，这个过程就是回表。

出现回表操作的情况

1. 查询列不包含在索引中：如上述例子，若索引不包含查询的所有列，就会回表。
2. 使用了索引范围查询且查询列不完整：例如SELECT * FROM users WHERE age > 20，如果只对age建立单索引，MySQL通过索引定位到满足age > 20的记录主键后，还需回表获取完整行数据。

避免回表提升查询性能的方法

1. 创建复合索引：根据查询需求，将经常一起查询的列创建为复合索引。例如经常查询SELECT name, age FROM users WHERE name = 'John' AND age = 30，可创建复合索引(name, age)，这样能利用覆盖索引避免回表。
2. 覆盖索引设计：尽量让索引包含查询中涉及的列。如查询SELECT id, email FROM users WHERE phone = '123456789'，可以创建包含phone、id和email的复合索引(phone, id, email) ，实现覆盖索引，避免回表。