Database Query Execution Plan and Optimization Techniques

Database Query Execution Plan and Optimization Techniques

Description
A query execution plan is a step-by-step blueprint generated by the database optimizer for executing an SQL query. It determines the order of data retrieval, join methods, and resource usage efficiency. Optimization techniques are methods to improve performance by adjusting queries or structures. Understanding the execution plan is the core foundation of database performance tuning.

1. What is a Query Execution Plan
When a database receives an SQL query, the optimizer analyzes various possible execution paths (such as index usage, join order) and selects the lowest-cost plan. The execution plan displays the operation steps in a tree structure, for example:

  • Leaf Nodes: Data scan operations (e.g., full table scan, index scan).
  • Intermediate Nodes: Processing operations like joins (JOIN), sorting (SORT).
  • Root Node: The final result output.
    Key metrics include operation type, estimated row count (Cardinality), actual execution time, and resource consumption.

2. How to Obtain an Execution Plan
Using MySQL and PostgreSQL as examples:

  • MySQL:
    • Use EXPLAIN to view the estimated plan: 
      EXPLAIN SELECT * FROM users WHERE age > 30;
    • Use EXPLAIN ANALYZE to get actual execution data (requires running the query): 
      EXPLAIN ANALYZE SELECT * FROM users WHERE age > 30;
  • PostgreSQL:
    • EXPLAIN (ANALYZE, BUFFERS) can show disk reads and cache usage.

Key columns in the execution plan result:

  • type (MySQL) / Operation (other databases): Operation type, such as ALL (full table scan), index (index scan), range (range scan).
  • rows: Number of rows the optimizer estimates to process.
  • Extra: Additional information (e.g., whether a temporary table or filesort is used).

3. Interpreting Common Operations in Execution Plans

  • Full Table Scan:
    • Characteristics: type=ALL, triggered when no index is available.
    • Issue: Poor performance with large data volumes.
    • Optimization: Add an index to the WHERE condition field.
  • Index Scan:
    • Characteristics: type=index or range, quickly locates data via an index.
    • Note: Indexes are not suitable for low-selectivity fields (e.g., gender).
  • Covering Index:
    • Characteristics: Extra=Using index, the index contains all queried fields, eliminating the need for a table lookup.
    • Optimization: Include all query fields in the index.
  • Nested Loop Join:
    • Suitable when: One table is small, and the other has an index.
    • Issue: Inefficient for large tables without indexes.
  • Hash Join:
    • Suitable for: Equi-joins on large tables without indexes; builds a hash table from one table first, then matches.
  • Sort Operation (Filesort):
    • Characteristics: Extra=Using filesort, sorting in memory or on disk.
    • Optimization: Add an index to the ORDER BY field.

4. Identifying Performance Issues via Execution Plans

  • Large Deviation in Estimated Row Count:
    • Cause: Outdated statistics or uneven data distribution.
    • Solution: Run ANALYZE TABLE (MySQL) to update statistics.
  • Full Table Scan:
    • Check if WHERE condition fields lack an index or if the index is ineffective (e.g., applying functions to the field).
  • Temporary Table Usage:
    • Characteristics: Extra=Using temporary, common with GROUP BY or DISTINCT.
    • Optimization: Try adjusting the query or adding an index.
  • High-Cost Join Order:
    • The optimizer might choose a suboptimal table join order; use STRAIGHT_JOIN (MySQL) to force the order.

5. Optimization Techniques and Practical Case Studies
Case: Querying the number of orders for users over 30
Original query:

SELECT u.name, COUNT(o.order_id) 
FROM users u 
JOIN orders o ON u.id = o.user_id 
WHERE u.age > 30 
GROUP BY u.id;
  • Step 1: Check the execution plan
    • Discovery: Full table scan on the users table; the orders table is joined via the user_id index.
  • Step 2: Add an index to users.age 
    CREATE INDEX idx_age ON users(age);
    • Effect: The users table switches to a range scan, reducing data reads.
  • Step 3: Avoid table lookups
    • If the query only needs u.id and u.name, create a covering index:
    CREATE INDEX idx_age_name ON users(age, name);
  • Step 4: Optimize GROUP BY
    • If the orders table is too large, filter first, then join:
    SELECT u.name, t.order_count 
FROM users u 
JOIN (
  SELECT user_id, COUNT(*) AS order_count 
  FROM orders 
  GROUP BY user_id
) t ON u.id = t.user_id 
WHERE u.age > 30;

6. Advanced Optimization Strategies

  • Query Rewriting:
    • Use EXISTS instead of IN (more efficient when subquery results are large).
    • Avoid SELECT *, only return necessary fields.
  • Indexing Strategy:
    • Pay attention to the leftmost prefix principle for composite indexes.
    • Use prefix indexes for text fields.
  • Database Configuration Tuning:
    • Adjust join_buffer_size (MySQL) to improve join performance.
    • Increase sort_buffer_size to reduce disk sorting.

Summary
The execution plan is the "map" for optimizing queries. It requires comprehensive optimization combining statistics, index design, and SQL rewriting. Regularly monitor slow query logs and conduct targeted analysis of high-frequency SQL to continuously improve database performance.