慢查询定位与优化

🟡 中等

题目描述

线上数据库出现慢查询，如何定位和优化？以下 SQL 很慢，如何优化？

SELECT 
    u.name, 
    o.order_id, 
    p.product_name,
    o.amount
FROM orders o
LEFT JOIN users u ON o.user_id = u.id
LEFT JOIN products p ON o.product_id = p.id
WHERE o.status = 1
  AND o.created_at >= '2024-01-01'
  AND u.city = 'Beijing'
ORDER BY o.created_at DESC
LIMIT 1000, 10;

表结构

CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    user_id BIGINT,
    product_id BIGINT,
    status INT,
    amount DECIMAL(10,2),
    created_at DATETIME,
    KEY idx_status (status),
    KEY idx_created_at (created_at)
) ENGINE=InnoDB;

CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    name VARCHAR(50),
    city VARCHAR(50),
    KEY idx_city (city)
) ENGINE=InnoDB;

CREATE TABLE products (
    id BIGINT PRIMARY KEY,
    product_name VARCHAR(100)
) ENGINE=InnoDB;

提示

开启慢查询日志
使用 EXPLAIN 分析执行计划
优化索引
优化查询条件

解法

参考答案 (3 个标签)

慢查询 SQL优化索引

步骤 1：开启慢查询日志

-- 查看慢查询配置
SHOW VARIABLES LIKE 'slow_query%';
-- slow_query_log = OFF
-- slow_query_log_file = /var/log/mysql/slow.log
-- long_query_time = 10 (秒)

-- 开启慢查询
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- 超过 1 秒记录

-- 或在 my.cnf 中配置
[mysqld]
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = ON  -- 记录未使用索引的查询

步骤 2：分析慢查询日志

# 查看慢查询日志
tail -f /var/log/mysql/slow.log

# 使用 mysqldumpslow 工具
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
# -s t: 按查询时间排序
# -t 10: 显示前 10 条

# 输出示例：
# Count: 10  Time=3.50s (35s)  Lock=0.00s (0s)  Rows=1000.0 (10000), ...
# SELECT * FROM orders WHERE status=1 AND created_at >= '2024-01-01'

步骤 3：使用 EXPLAIN 分析

EXPLAIN SELECT 
    u.name, 
    o.order_id, 
    p.product_name,
    o.amount
FROM orders o
LEFT JOIN users u ON o.user_id = u.id
LEFT JOIN products p ON o.product_id = p.id
WHERE o.status = 1
  AND o.created_at >= '2024-01-01'
  AND u.city = 'Beijing'
ORDER BY o.created_at DESC
LIMIT 1000, 10;

-- 输出：
-- +----+-------------+-------+------+---------------+-----------+---------+------+------+-------------+
-- | id | select_type | table | type | possible_keys | key       | key_len | ref  | rows | Extra       |
-- +----+-------------+-------+------+---------------+-----------+---------+------+------+-------------+
-- |  1 | SIMPLE      | o     | ALL  | idx_status    | NULL      | NULL    | NULL | 100K | Using where |
-- |  1 | SIMPLE      | u     | ALL  | NULL          | NULL      | NULL    | NULL |  10K | Using where |
-- |  1 | SIMPLE      | p     | eq_ref| PRIMARY      | PRIMARY   | 8       | o.id |    1 | NULL        |
-- +----+-------------+-------+------+---------------+-----------+---------+------+------+-------------+

-- 问题：
-- 1. orders: type=ALL (全表扫描)
-- 2. users: type=ALL (全表扫描)
-- 3. key=NULL (未使用索引)

步骤 4：优化索引

-- 创建联合索引
CREATE INDEX idx_status_created ON orders(status, created_at);
CREATE INDEX idx_user_id_city ON users(user_id, city);

-- 再次 EXPLAIN
EXPLAIN SELECT ...
-- +----+-------------+-------+--------+---------------------+---------------------+---------+------+------+-------------+
-- | id | select_type | table | type   | possible_keys       | key                 | key_len | ref  | rows | Extra       |
-- +----+-------------+-------+--------+---------------------+---------------------+---------+------+------+-------------+
-- |  1 | SIMPLE      | o     | ref    | idx_status_created  | idx_status_created  | 9       | NULL |  500 | Using where |
-- |  1 | SIMPLE      | u     | eq_ref | PRIMARY,idx_user_city| idx_user_city      | 153     | o.id |    1 | Using where |
-- |  1 | SIMPLE      | p     | eq_ref| PRIMARY             | PRIMARY             | 8       | o.id |    1 | NULL        |
-- +----+-------------+-------+--------+---------------------+---------------------+---------+------+------+-------------+

-- 结果：
-- 1. orders: type=ref, rows=500 (从 100K 降到 500)
-- 2. users: type=eq_ref, rows=1 (从 10K 降到 1)
-- 3. 使用了索引

步骤 5：优化查询

-- 优化 1：避免 SELECT *
SELECT 
    u.name, 
    o.order_id, 
    p.product_name,
    o.amount  -- 只查询需要的字段
FROM orders o
INNER JOIN users u ON o.user_id = u.id
INNER JOIN products p ON o.product_id = p.id  -- 改为 INNER JOIN
WHERE o.status = 1
  AND o.created_at >= '2024-01-01'
  AND u.city = 'Beijing'
ORDER BY o.created_at DESC
LIMIT 1000, 10;

-- 优化 2：延迟关联（优化分页）
SELECT u.name, t.order_id, p.product_name, t.amount
FROM (
    SELECT o.order_id, o.amount, o.user_id, o.product_id
    FROM orders o
    WHERE o.status = 1
      AND o.created_at >= '2024-01-01'
    ORDER BY o.created_at DESC
    LIMIT 1000, 10
) AS t
JOIN users u ON t.user_id = u.id
JOIN products p ON t.product_id = p.id
WHERE u.city = 'Beijing';

-- 优化 3：使用覆盖索引
CREATE INDEX idx_status_created_user_product ON 
    orders(status, created_at, user_id, product_id, amount);

SELECT o.user_id, o.product_id, o.amount, o.created_at
FROM orders o
WHERE o.status = 1
  AND o.created_at >= '2024-01-01'
ORDER BY o.created_at DESC
LIMIT 1000, 10;
-- Extra: Using index (覆盖索引，不回表)

慢查询优化检查清单

1. 索引问题

-- ✗ 未使用索引
EXPLAIN SELECT * FROM orders WHERE status = 1;
-- type: ALL

-- ✓ 添加索引
CREATE INDEX idx_status ON orders(status);

2. 索引失效

-- ✗ 函数运算
SELECT * FROM orders WHERE YEAR(created_at) = 2024;

-- ✓ 优化
SELECT * FROM orders WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

-- ✗ 隐式转换
SELECT * FROM users WHERE phone = 12345;  -- phone 是 VARCHAR

-- ✓ 优化
SELECT * FROM users WHERE phone = '12345';

-- ✗ LIKE 前缀
SELECT * FROM users WHERE name LIKE '%Alice%';

-- ✓ 优化（如果可能）
SELECT * FROM users WHERE name LIKE 'Alice%';

3. JOIN 优化

-- ✗ 小表驱动大表
SELECT * FROM orders o JOIN users u ON o.user_id = u.id;

-- ✓ 大表驱动小表
SELECT * FROM users u JOIN orders o ON u.id = o.user_id;

-- ✗ 子查询
SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE city = 'Beijing');

-- ✓ JOIN
SELECT o.* FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.city = 'Beijing';

4. 分页优化

-- ✗ 深分页（性能差）
SELECT * FROM orders ORDER BY created_at DESC LIMIT 1000000, 10;

-- ✓ 使用上次的最大 ID
SELECT * FROM orders 
WHERE id < last_max_id
ORDER BY id DESC 
LIMIT 10;

-- ✓ 延迟关联
SELECT * FROM orders o
JOIN (
    SELECT id FROM orders ORDER BY created_at DESC LIMIT 1000000, 10
) AS t ON o.id = t.id;

5. COUNT 优化

-- ✗ COUNT(*)
SELECT COUNT(*) FROM orders WHERE status = 1;

-- ✓ 使用 Redis 缓存
-- 或使用近似值
SELECT table_rows FROM information_schema.tables 
WHERE table_schema = 'database_name' AND table_name = 'orders';

慢查询分析工具

1. pt-query-digest

# 安装 Percona Toolkit
apt-get install percona-toolkit

# 分析慢查询日志
pt-query-digest /var/log/mysql/slow.log

# 输出：
-- # Profile
-- # Rank Query ID           Response time  Calls   R/Call
-- # ==== ================== ============= ======= ========
-- #    1 0x123456789ABCDEF  35.0000       10      3.5000
-- # SELECT * FROM orders WHERE status=1 ...

2. MySQL Enterprise Monitor

-- 商业工具，提供实时监控
-- Dashboard: 慢查询、索引使用、锁等待

下一章节索引设计与优化