面试题：MySQL索引在多表复杂连接查询优化中的作用

Question

有两张表，orders表（包含字段order_id、customer_id、order_date）和customers表（包含字段customer_id、customer_name、city）。要查询所有来自‘北京’的客户在2023年的订单信息，如何通过合理设计索引来优化这个多表连接查询？详细说明索引设计思路以及对查询性能的影响。

Answer 1

索引设计思路

1. orders表：
   • 针对order_date字段创建索引，因为要筛选2023年的订单，CREATE INDEX idx_order_date ON orders(order_date);。在查询时，通过该索引可以快速定位到2023年的订单记录，减少全表扫描的数据量。
   • 针对customer_id字段创建索引，因为这是连接两张表的关键字段，CREATE INDEX idx_customer_id_orders ON orders(customer_id);。通过此索引，在连接customers表时，能快速匹配到对应的customer_id记录，提高连接效率。
2. customers表：
   • 针对customer_id字段创建索引，这同样是连接字段，CREATE INDEX idx_customer_id_customers ON customers(customer_id);，加速连接操作。
   • 针对city字段创建索引，因为要筛选来自‘北京’的客户，CREATE INDEX idx_city ON customers(city);，利用该索引能快速定位到城市为‘北京’的客户记录。

对查询性能的影响

1. 减少数据扫描量：通过对order_date和city字段的索引，查询时能快速定位到符合条件的小部分数据，而无需扫描全表，大大减少了磁盘I/O操作，提升查询速度。
2. 加速表连接：对customer_id字段在两张表上分别创建索引，在进行多表连接时，数据库可以利用索引快速匹配连接字段，减少连接操作的时间复杂度，提高连接效率，进而提升整个查询的性能。