什么时候需要创建索引？

简要回答

• 在 WHERE 子句中经常使用的列： 当查询需要根据某个列进行筛选时，在该列上创建索引可以快速定位符合条件的记录，避免全表扫描。
• 在 JOIN 操作中经常使用的连接列： 在表与表之间进行关联查询时，如果连接列上创建了索引，可以显著提高连接效率。
• 在 ORDER BY 或 GROUP BY 子句中经常使用的列： 如果查询结果需要按照某些列进行排序或分组，这些列上的索引可以加速这些操作，甚至避免额外的排序或分组步骤。
• 数据量较大的表： 对于包含大量数据的表，索引带来的性能提升会更加明显。
• 主键和唯一约束对应的列： 数据库会自动为这些列创建索引，以保证数据的唯一性并加速查找。
• 外键约束对应的列： 在外键列上创建索引通常可以提高关联查询的效率。

详细回答

• 从查询语句的关键子句来看：
  1. WHERE 子句中的条件列： 当查询需要根据某个列的值来过滤数据时（例如 SELECT * FROM users WHERE status = 'active';），如果在 status 列上创建索引，数据库可以快速通过索引找到所有 status 为 'active' 的记录，而无需扫描整个表。
  2. JOIN 操作中的连接列： 当进行多表关联查询时（例如 SELECT * FROM orders JOIN customers ON orders.customer_id = customers.id;），如果在 orders.customer_id 和 customers.id 这两个连接列上创建索引，数据库可以更高效地匹配来自不同表的记录，从而提高 JOIN 操作的效率。
  3. ORDER BY 子句中的排序列： 如果查询结果需要按照某些列进行排序（例如 SELECT * FROM products ORDER BY price DESC;），如果在 price 列上创建索引，并且索引的顺序与排序顺序一致，数据库可以直接按照索引的顺序读取数据，避免额外的排序操作（如文件排序），从而提高查询速度。
  4. GROUP BY 子句中的分组列： 当查询需要根据某些列进行分组聚合时（例如 SELECT COUNT(*) FROM orders GROUP BY customer_id;），如果在 customer_id 列上创建索引，数据库可以利用索引的有序性来加速分组操作，甚至在某些情况下直接通过索引完成分组统计。
• 从数据量和访问模式来看：
  1. 数据量较大的表： 索引对于数据量较大的表来说，性能提升更为明显。在小表中，全表扫描的开销可能很小，索引带来的额外开销（存储空间、维护成本）可能 outweighs 性能提升。但随着数据量的增长，索引的优势会越来越突出。
  2. 经常被访问的列或表： 经常被用于查询条件的列，或者经常被查询的表，更适合创建索引。因为索引的价值在于被频繁使用，如果一个索引很少被用到，那么它带来的维护成本和存储开销就是不必要的。
• 从数据完整性和关联关系来看：
  1. 主键和唯一约束对应的列： 当在表上定义主键或唯一约束时，数据库会自动为这些列创建对应的索引（通常是唯一索引）。这既保证了数据的唯一性，也为按主键或唯一列进行查找提供了高效的索引。
  2. 外键约束对应的列： 在外键列上创建索引通常可以提高关联查询的效率，并且在维护数据完整性（如删除或更新主表记录时检查子表是否存在关联记录）时也能提供帮助。
• 如何判断是否需要创建索引：
  1. 慢查询日志： 开启数据库的慢查询日志，监控执行时间超过阈值的查询语句，找出性能瓶颈。
  2. EXPLAIN 命令： 使用 EXPLAIN 命令分析慢查询语句的执行计划，查看是否使用了索引，以及索引的使用情况。根据执行计划来判断是否需要创建或优化索引。

知识拓展

1. 有无索引的查询过程对比，示意图如下： image
2. 面试官可能的追问1： “创建索引一定会提高查询性能吗？有没有例外情况？”

• 简答： 不一定。对于数据量很小的表，索引带来的开销可能大于性能提升。另外，如果查询条件不能有效地利用索引（例如对索引列进行函数操作、使用 LIKE '%keyword' 进行模糊匹配等），索引也可能失效。

3. 面试官可能的追问2： “在多列上创建联合索引时，需要注意什么？”

• 简答： 创建联合索引时需要注意列的顺序，并遵循最左前缀原则。将最常用于查询条件的列放在联合索引的最左边，可以更有效地利用索引。

4. 面试官可能的追问3： “除了创建索引，还有哪些方法可以优化数据库查询性能？”

• 简答： 除了创建索引，还可以通过优化 SQL 语句（避免全表扫描、减少不必要的数据返回）、优化数据库结构（如表设计、字段类型）、调整数据库配置参数、使用缓存、读写分离等方法来优化查询性能。