面试题：如何写出比给定语句更高效的SQL？及原语句问题分析

Let’s use a common inefficient query as our example to walk through both parts of your question. Suppose your original query looks like this:

SELECT * 
FROM orders 
WHERE customer_id IN (SELECT customer_id FROM customers WHERE country = 'USA')
AND order_date >= '2023-01-01';

Part 1: More Efficient Alternative Query #

A faster replacement would use an INNER JOIN instead of a subquery with IN, and explicitly select only the columns you need (instead of SELECT *):

SELECT o.order_id, o.customer_id, o.order_date, o.total_amount
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
WHERE c.country = 'USA'
AND o.order_date >= '2023-01-01';

Why is this better? Most query optimizers handle joins more efficiently than IN subqueries, especially with large datasets. Skipping SELECT * also cuts down on unnecessary data transfer and memory usage—only fetching what you need is a quick, high-impact win.

If you prefer subqueries, swapping IN for EXISTS can also boost performance (especially if the subquery returns duplicates):

SELECT o.order_id, o.customer_id, o.order_date, o.total_amount
FROM orders o
WHERE EXISTS (
    SELECT 1 FROM customers c 
    WHERE c.customer_id = o.customer_id 
    AND c.country = 'USA'
)
AND o.order_date >= '2023-01-01';

Part 2: Issues in the Original Query & Fixes #

Let’s break down the problems and actionable fixes:

1. SELECT * wastes resources #

• Problem: Retrieves every column from orders, even if you only need a handful. This increases I/O, memory load, and network transfer time.
• Fix: List only the columns you actually use (e.g., order_id, total_amount) instead of using *.

2. IN subqueries can be slow for large datasets #

• Problem: Some database optimizers struggle to optimize IN subqueries, especially when the subquery returns thousands of rows. IN also checks for duplicates, adding unnecessary overhead.
• Fix: Replace with INNER JOIN (as shown above) or EXISTS for better execution plan optimization.

3. Missing indexes (a common hidden bottleneck) #

• Problem: Without indexes on orders.customer_id, orders.order_date, or customers.country/customers.customer_id, the database will do full table scans—painfully slow on large tables.
• Fix: Create targeted indexes:

  -- Speed up filtering orders by date and joining to customers
  CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);
  
  -- Speed up filtering customers by country and joining to orders
  CREATE INDEX idx_customers_country_id ON customers(country, customer_id);
  

4. Implicit type conversions (if applicable) #

• Problem: If order_date is stored as a string instead of a date/time type, comparing it to '2023-01-01' can block index usage.
• Fix: Ensure order_date is a date/time column, and use database-appropriate date literals (e.g., DATE '2023-01-01' in PostgreSQL, CAST('2023-01-01' AS DATE) in SQL Server).

Bonus: Verify Improvements with Execution Plans #

Always use your database’s execution plan tool to confirm optimizations:

• PostgreSQL: Prefix your query with EXPLAIN ANALYZE
• MySQL: Use EXPLAIN or EXPLAIN ANALYZE
• SQL Server: Enable the Actual Execution Plan (Ctrl+M)

This will show you if indexes are being used, full scans are avoided, and where remaining bottlenecks lie.

内容的提问来源于stack exchange，提问作者SBT