How to Turn Slow MySQL Queries into Millisecond Responses: Real‑World Optimization Case Study

Database Performance Bottleneck Analysis and SQL Optimization: From Slow Queries to Millisecond Responses

Author's Preface: As an on‑site operations engineer with eight years of experience, I have been woken up many nights by database performance issues. Here I share several real‑world optimization cases to help you avoid common pitfalls. If you find this useful, please like and follow!

Case Background: E‑commerce System Performance Crisis

Problem Symptoms

Order query interface response time: 15‑30 seconds

Database CPU usage: continuously >90%

Slow‑query log: >300 entries per minute

User complaints: increased by 500%

Sounds familiar? Let's solve it step by step.

Step 1: Locate Performance Bottlenecks

1.1 System Monitoring Data Analysis

First, view the problem from a global perspective:

# 查看数据库连接数
mysql> SHOW PROCESSLIST;
# 结果：发现大量 QUERY 状态的连接，平均执行时间 >10s

# 检查慢查询配置
mysql> SHOW VARIABLES LIKE 'slow_query%';
mysql> SHOW VARIABLES LIKE 'long_query_time';

# 查看数据库状态
mysql> SHOW ENGINE INNODB STATUS\G

Key Findings:

Active connections: 512/800 (near limit)

Average query time: 12.5 seconds

Lock wait events: frequent

1.2 Slow‑Query Log Analysis

Use mysqldumpslow to analyze:

# Analyze the 10 slowest queries
mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log

# Analyze the most frequent queries
mysqldumpslow -s c -t 10 /var/log/mysql/mysql-slow.log

Core Problem SQL (sanitized):

-- Problem SQL 1: Order query
SELECT * , u.username, p.product_name, p.price
FROM orders o
LEFT JOIN users u ON o.user_id = u.id
LEFT JOIN order_items oi ON o.id = oi.order_id
LEFT JOIN products p ON oi.product_id = p.id
WHERE o.create_time >= '2023-11-01'
  AND o.status IN (1,2,3,4,5)
ORDER BY o.create_time DESC
LIMIT 20;

-- Execution time: avg 18.5 seconds
-- Scanned rows: 2,847,592
-- Returned rows: 20

Step 2: Deep Execution‑Plan Analysis

2.1 EXPLAIN Analysis

EXPLAIN SELECT o.*, u.username, p.product_name, p.price
FROM orders o
LEFT JOIN users u ON o.user_id = u.id
LEFT JOIN order_items oi ON o.id = oi.order_id
LEFT JOIN products p ON oi.product_id = p.id
WHERE o.create_time >= '2023-11-01'
  AND o.status IN (1,2,3,4,5)
ORDER BY o.create_time DESC
LIMIT 20;

Execution Plan Result:

id | select_type | table | type | key | rows | Extra
---+-------------+-------+------+-----+------+---------------------------
1  | SIMPLE      | o     | ALL  | NULL| 2847592 | Using where; Using filesort
1  | SIMPLE      | u     | eq_ref| PRIMARY| 1   | NULL
1  | SIMPLE      | oi    | ref   | order_id_idx| 3 | NULL
1  | SIMPLE      | p     | eq_ref| PRIMARY| 1   | NULL

Problem Analysis: orders table full scan (type=ALL)

No suitable index covering the WHERE clause

Filesort used for ordering

Nearly 3 million rows scanned

2.2 Index Status Check

# 查看 orders 表的索引
SHOW INDEX FROM orders;

Existing Indexes:

PRIMARY KEY (id)

KEY idx_user_id (user_id)

Missing Critical Indexes: create_time column has no index status column has no index

No composite index to optimize the query

Step 3: Hands‑On SQL Optimization

3.1 Index Optimization Strategy

Create a composite index with the most selective columns first:

# 创建复合索引（顺序很重要！)
ALTER TABLE orders
ADD INDEX idx_status_createtime_id (status, create_time, id);

-- Why this order?
-- 1. status: used in WHERE, low cardinality
-- 2. create_time: range condition
-- 3. id: ORDER BY can use index order, avoiding filesort

3.2 SQL Rewrite Optimization

Optimized SQL – Version 1 (Pagination Improvement):

-- 优化版本 1：分页优化
SELECT o.*, u.username, p.product_name, p.price
FROM orders o
LEFT JOIN users u ON o.user_id = u.id
LEFT JOIN order_items oi ON o.id = oi.order_id
LEFT JOIN products p ON oi.product_id = p.id
WHERE o.create_time >= '2023-11-01'
  AND o.status IN (1,2,3,4,5)
  AND o.id <= (
    SELECT id FROM orders
    WHERE create_time >= '2023-11-01'
      AND status IN (1,2,3,4,5)
    ORDER BY create_time DESC
    LIMIT 1 OFFSET 19
  )
ORDER BY o.create_time DESC, o.id DESC
LIMIT 20;

Further improvement with delayed join:

-- 优化版本 2：延迟关联
SELECT o.id, o.user_id, o.total_amount, o.status, o.create_time,
       u.username, p.product_name, p.price
FROM (
  SELECT id, user_id, total_amount, status, create_time
  FROM orders
  WHERE create_time >= '2023-11-01'
    AND status IN (1,2,3,4,5)
  ORDER BY create_time DESC, id DESC
  LIMIT 20
) o
LEFT JOIN users u ON o.user_id = u.id
LEFT JOIN order_items oi ON o.id = oi.order_id
LEFT JOIN products p ON oi.product_id = p.id;

3.3 Performance Comparison

Optimization Stage | Execution Time | Scanned Rows | CPU Usage
----------------------------------------------------------
Original SQL      | 18.5 seconds   | 2,847,592    | 85%
Add Index         | 2.1 seconds    | 24,156       | 45%
Delayed Join      | 0.08 seconds   | 20           | 15%

Performance improvement: 230× faster!

Step 4: Deep‑Level Optimization Strategies

4.1 Partitioned Table Optimization

Archive historical orders using monthly partitions:

# 创建按月分区的订单表
CREATE TABLE orders_partitioned (
  id BIGINT PRIMARY KEY,
  user_id INT NOT NULL,
  total_amount DECIMAL(10,2),
  status TINYINT,
  create_time DATETIME,
  -- other columns ...
)
PARTITION BY RANGE (YEAR(create_time)*100 + MONTH(create_time)) (
  PARTITION p202310 VALUES LESS THAN (202311),
  PARTITION p202311 VALUES LESS THAN (202312),
  PARTITION p202312 VALUES LESS THAN (202401),
  -- add more partitions as needed
  PARTITION p_future VALUES LESS THAN MAXVALUE
);

4.2 Read‑Write Splitting Architecture (Python Example)

# Python 示例：智能读写分离
class DatabaseRouter:
    def __init__(self):
        self.master = get_master_connection()
        self.slaves = get_slave_connections()
        self.cache_ttl = 300  # 5 minutes

    def execute_query(self, sql, is_write=False):
        if is_write or self.is_write_operation(sql):
            return self.master.execute(sql)
        else:
            slave = random.choice(self.slaves)
            return slave.execute(sql)

    def is_write_operation(self, sql):
        write_keywords = ['INSERT', 'UPDATE', 'DELETE', 'ALTER']
        return any(keyword in sql.upper() for keyword in write_keywords)

4.3 Cache Strategy Optimization (Redis Example)

# Redis 缓存策略
import redis, json
from datetime import timedelta

class OrderCacheManager:
    def __init__(self):
        self.redis_client = redis.Redis(host='localhost', port=6379, db=0)
        self.cache_ttl = 300  # 5 minutes

    def get_orders(self, user_id, page=1, size=20):
        cache_key = f"orders:{user_id}:{page}:{size}"
        cached_data = self.redis_client.get(cache_key)
        if cached_data:
            return json.loads(cached_data)
        orders = self.query_from_database(user_id, page, size)
        self.redis_client.setex(cache_key, self.cache_ttl, json.dumps(orders, default=str))
        return orders

Step 5: Monitoring & Alerting System

5.1 Key Metric Monitoring (Prometheus + Grafana)

# Prometheus + Grafana 监控配置
# mysql_exporter 关键指标
# 慢查询监控
mysql_global_status_slow_queries

# 连接数监控
mysql_global_status_threads_connected / mysql_global_variables_max_connections

# QPS 监控
rate(mysql_global_status_queries[5m])

# 锁等待监控
mysql_info_schema_innodb_metrics_lock_timeouts

5.2 Automated Optimization Script

#!/bin/bash
# auto_optimize.sh - 自动优化脚本

# 检查慢查询数量
slow_queries=$(mysql -e "SHOW GLOBAL STATUS LIKE 'Slow_queries';" | awk 'NR==2{print $2}')

if [ $slow_queries -gt 100 ]; then
  echo "发现大量慢查询，开始分析..."
  mysqldumpslow -s t -t 5 /var/log/mysql/mysql-slow.log > /tmp/slow_analysis.log
  mail -s "数据库慢查询告警" [email protected] < /tmp/slow_analysis.log
fi

Practical Experience Summary

Common Optimization Pitfalls

Blindly Adding Indexes

❌ Wrong: index every column

✅ Right: create composite indexes based on query patterns

Ignoring Index Order

❌ Wrong: KEY idx_time_status (create_time, status)

✅ Right: KEY idx_status_time (status, create_time)

Pagination Mistakes

❌ Wrong: LIMIT 10000, 20 (deep pagination)

✅ Right: use cursor pagination or delayed join

Optimization Golden Rules

Index Optimization Principles

Left‑most prefix matching

Put range columns last

Covering indexes are better than back‑table lookups

SQL Writing Standards

SELECT only needed columns

WHERE clauses should use indexes

Avoid functions on indexed columns in WHERE

Architecture Considerations

Read‑write splitting reduces primary DB load

Appropriate caching

Data archiving and partitioning

Optimization Effect Summary

Metric                | Before | After | Improvement
---------------------------------------------------
Average response time | 18.5 s | 0.08 s | 99.6%
CPU usage            | 90%+   | 15%   | 83%
Slow queries/min     | 300+   | <5    | 98%
User satisfaction    | 60%    | 95%   | 58%

The case demonstrates that systematic bottleneck identification, proper indexing, query rewriting, partitioning, read‑write separation, and caching can collectively reduce query latency from seconds to milliseconds and dramatically lower resource consumption.