sql-optimization-patterns

SQL Optimization Patterns — Make Database Queries Fly

Skills Overview

SQL optimization patterns are a systematic approach to database performance tuning. By designing indexing strategies, rewriting queries, and analyzing execution plans, slow queries can be transformed into efficient operations with millisecond-level response times.

Applicable Scenarios

1. Debugging Slow Queries

When the application experiences response latency or the database load becomes too high, use this skill to quickly identify performance bottlenecks. Analyze the EXPLAIN execution plan to find root causes such as full table scans and index invalidation, and then provide targeted optimization solutions.

2. Designing Database Architecture

During the early stages of a project or during refactoring, design a reasonable index structure based on query patterns. Plan table structures and relationships to prevent performance issues from the source, ensuring the system scales smoothly as data grows.

3. Optimizing Application Performance

Resolve common N+1 query problems, optimize complex JOIN statements, reduce the number of database round trips, significantly improve API response times, and enhance user experience.

Core Features

1. EXPLAIN Execution Plan Analysis

Deeply interpret the query execution plans of MySQL/PostgreSQL. Identify key metrics such as type, key, rows, and Extra to determine whether the query uses the correct indexes. Detect performance killers like implicit conversions and file sorts.

2. Index Strategy Design

Choose appropriate index types based on query patterns (single-column indexes, composite indexes, covering indexes). Master the leftmost-prefix principle, understand advanced features such as index condition pushdown, and avoid redundant indexes and index invalidation.

3. Query Rewriting and Optimization

Master techniques such as subquery optimization, JOIN optimization, and pagination optimization. Learn to use EXISTS instead of IN, avoid SELECT *, and apply LIMIT appropriately and other best practices to make queries more efficient.

Common Questions

What should I do if SQL queries are slow?

First, enable the slow query log to locate problematic SQL statements. Then use EXPLAIN to analyze the execution plan and check whether indexes are used and whether the scanned row count is reasonable. Common causes include: missing indexes, invalid indexes, full table scans, lock waits, and excessively large data volumes.

How do I use EXPLAIN to analyze query performance?

Add the EXPLAIN keyword before the SQL statement to view the execution plan. Focus on the type column (access type, from best to worst: const > eq_ref > ref > range > index > ALL), the key column (the actual index used), the rows column (estimated number of scanned rows), and the Extra column (additional information, e.g., Using filesort indicates extra sorting is needed).

When can an index become invalid?

Common situations that lead to index invalidation include: using functions or expressions in computations (e.g., WHERE YEAR(create_time) = 2024), implicit type conversions (e.g., passing a number as a string for a string field), LIKE with a leading wildcard (e.g., '%abc'), OR conditions that involve non-indexed fields, negative queries (e.g., !=, NOT IN), and composite indexes that do not satisfy the leftmost-prefix principle.