MST

星途 面试题库

面试题：PostgreSQL复杂场景下条件过滤与索引结合的查询优化

在PostgreSQL数据库中有一个订单表orders，表结构为（order_id, customer_id, order_date, total_amount, status）。同时还有一个客户表customers，表结构为（customer_id, customer_name, region）。现在需要查询来自特定地区（例如 'Asia'）且订单状态为 'completed'，并且订单日期在最近一年以内的订单信息，包括订单号、客户名称和订单总金额。已知在orders表的order_date、status字段以及customers表的region字段上都有索引。请写出优化后的SQL查询，并详细阐述条件过滤和索引在这个查询优化过程中的相互作用机制。
36.3万 热度难度
数据库PostgreSQL

知识考点

AI 面试

面试题答案

一键面试

SQL查询

SELECT o.order_id, c.customer_name, o.total_amount
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE c.region = 'Asia'
  AND o.status = 'completed'
  AND o.order_date >= CURRENT_DATE - INTERVAL '1 year';

条件过滤和索引相互作用机制

  1. 索引的使用：
    • customers表的region索引：在 WHERE c.region = 'Asia' 条件中，PostgreSQL可以利用 customers 表 region 字段上的索引，快速定位到来自 'Asia' 地区的客户记录，减少需要扫描的行数。
    • orders表的status索引：对于 WHERE o.status = 'completed' 条件，通过 orders 表 status 字段上的索引，能高效筛选出订单状态为 'completed' 的订单记录。
    • orders表的order_date索引：WHERE o.order_date >= CURRENT_DATE - INTERVAL '1 year' 条件使用 orders 表 order_date 字段上的索引，按日期范围快速过滤订单。
  2. 条件过滤顺序：PostgreSQL优化器会基于统计信息和索引情况决定执行顺序。理想情况下，会先利用每个表上的索引分别过滤出满足条件的行，再通过 JOIN 操作将符合条件的订单和客户信息关联起来。这减少了参与 JOIN 操作的数据量，进而提升查询效率。
  3. 索引选择性：索引的选择性越高（即索引列中不同值的比例越高），其过滤数据的效果越好，查询优化的效果也越显著。例如，如果 region 字段有很多不同的地区值，region 索引就能更有效地过滤出 'Asia' 地区的客户，加快查询速度。