Database Query Optimization Strategies and Execution Plan Analysis

Database Query Optimization Strategies and Execution Plan Analysis

Topic Description:
In database systems, when application performance encounters bottlenecks, query optimization is one of the core solutions. The interviewer aims to assess your understanding of common query optimization strategies and how to locate and resolve slow queries by analyzing Execution Plans. This includes: the optimizer's working principle, query rewriting techniques, effective utilization of indexes, and how to interpret key metrics within an execution plan.

Knowledge Explanation:

1. Basic Goals of Query Optimization
The core goal of database query optimization is to return correct results quickly while minimizing resource consumption (e.g., CPU, I/O). The optimization process is divided into two steps:

  • Logical Optimization: Rewriting the query statement based on relational algebra (e.g., projection, selection, join order adjustment) without involving specific physical structures.
  • Physical Optimization: Selecting the optimal execution path (e.g., which join algorithm to use, whether to use an index) based on data distribution, indexes, and other factors.

2. Common Query Optimization Strategies
Strategy 1: Avoid Full Table Scans

  • Problem Scenario: Using WHERE conditions on large tables without indexes on the involved columns leads to Full Table Scans, resulting in very poor performance.
  • Optimization Method:
    Create indexes (e.g., B+ tree indexes) on columns frequently used in query conditions to convert full table scans into Index Range Scans. 
    -- Before optimization: Without an index, each row must be checked for age
SELECT * FROM users WHERE age > 25;

-- After optimization: Create an index on age
CREATE INDEX idx_age ON users(age);

Strategy 2: Optimize Join Queries

  • Problem Scenario: Improper join order or algorithms during multi-table joins can generate large temporary intermediate tables, consuming significant memory.
  • Optimization Method:
    • Small Table Drives Large Table: Use the table with fewer rows as the driving table (e.g., the optimizer often automatically selects the smaller table for INNER JOIN).
    • Ensure Join Columns Have Indexes: Especially the join column on the driven table (the larger table) should be indexed to avoid scanning the large table during Nested Loop joins.

Strategy 3: Avoid Using SELECT *

  • Problem Scenario: SELECT * reads all columns, including unused ones, increasing I/O overhead.
  • Optimization Method:
    Query only the necessary columns to reduce data transfer volume. 
    -- Before optimization
SELECT * FROM orders WHERE user_id = 100;

-- After optimization
SELECT order_id, amount FROM orders WHERE user_id = 100;

3. Execution Plan Analysis in Practice
An execution plan is a diagram of the query execution path generated by the database optimizer. Taking MySQL's EXPLAIN as an example:

EXPLAIN SELECT u.name, o.amount 
FROM users u 
JOIN orders o ON u.id = o.user_id 
WHERE u.age > 30;

Key Metric Interpretation:

  • type column: Indicates the method of data access. Common values from best to worst performance:
    const (primary key equality lookup) > range (index range scan) > index (full index scan) > ALL (full table scan).
    If you see ALL, consider adding an index.
  • key column: The index actually used. If NULL, it means no index was used.
  • rows column: Estimated number of rows to be scanned. Fewer rows indicate higher efficiency.
  • Extra column: Additional information. If Using filesort (requires extra sorting) or Using temporary (requires temporary table) appears, optimization is needed.

Case Analysis:
If the execution plan shows type as ALL and key as NULL for the orders table, it indicates no index was used for the join. Optimization method:

-- Add an index on orders.user_id
CREATE INDEX idx_user_id ON orders(user_id);

4. Advanced Optimization Techniques

  • Covering Index: If an index contains all columns required by the query, there's no need for a 'table lookup' (accessing the main storage), greatly improving speed. 
    -- If the index (idx_age) includes the age and name columns, this query can return data directly from the index
SELECT age, name FROM users WHERE age > 25;
  • Update Statistics: The optimizer relies on statistics (e.g., data distribution) to choose execution plans. Regularly execute ANALYZE TABLE to update statistics and prevent optimizer misjudgments.

Summary:
Query optimization requires combining index design, SQL rewriting, and execution plan analysis. The core idea is to reduce the amount of data scanned and avoid high-cost operations (e.g., temporary tables, filesorts). In practice, it's essential to repeatedly use EXPLAIN to verify optimization effectiveness.