面试题：MySQL复合索引与避免全表扫描的关系及应用

Question

已知有一张订单表`orders`，表结构为`(order_id INT PRIMARY KEY, customer_id INT, order_date DATE, total_amount DECIMAL(10,2))`，业务中常根据`customer_id`和`order_date`联合查询订单信息。请阐述如何创建合适的复合索引以避免全表扫描，并且说明复合索引的最左前缀原则在这种场景下是如何应用的，如果查询条件只包含`order_date`，该复合索引是否还能避免全表扫描，为什么？

Answer 1

1. 创建复合索引： 在orders表上，应该创建一个基于customer_id和order_date的复合索引。SQL语句如下：

   CREATE INDEX idx_customer_order_date ON orders (customer_id, order_date);
   

2. 最左前缀原则：
   • 最左前缀原则指的是在使用复合索引时，查询条件必须从索引的最左边开始匹配。在上述创建的复合索引idx_customer_order_date中，最左边的列是customer_id。
   • 当查询条件为WHERE customer_id = 123 AND order_date = '2023 - 01 - 01'时，MySQL可以利用这个复合索引，因为它从最左边的customer_id开始匹配，然后再匹配order_date，能够快速定位到符合条件的记录，避免全表扫描。
   • 当查询条件为WHERE order_date = '2023 - 01 - 01'时，由于没有从复合索引的最左边customer_id开始匹配，MySQL无法利用这个复合索引来避免全表扫描。
3. 查询条件只包含order_date时：
   • 该复合索引不能避免全表扫描。
   • 原因是复合索引的最左前缀原则，由于查询条件没有以复合索引的最左边列customer_id开始，MySQL无法通过该复合索引快速定位记录，只能进行全表扫描来获取满足order_date条件的记录。