PHP 数据库查询慢

wen PHP项目 2026-08-05 285

本文目录导读：

PHP 数据库查询慢

第一步：定位瓶颈（先诊断，再优化）
第二步：SQL优化（高频原因）
第三步：索引优化（核心）
第四步：PHP代码层面优化
第五步：数据库配置优化
第六步：Redis 缓存优化（强烈推荐）
第七步：监控与排查工具
第八步：架构级优化（进阶）
调试速查清单（按优先级排序）
最强排查组合

PHP 数据库查询慢是一个综合性问题，通常涉及数据库本身、SQL语句、PHP代码、服务器硬件四个层面,以下是系统性的排查与优化指南：

第一步：定位瓶颈（先诊断，再优化）

开启慢查询日志（MySQL）

-- 查看当前状态
SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time';
-- 临时开启（重启失效）
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;  -- 超过2秒记录
-- 永久开启（my.cnf）
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
log_queries_not_using_indexes = 1

查看慢查询日志

tail -f /var/log/mysql/slow.log

使用 EXPLAIN 分析SQL

EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';
-- 注意 type 字段：ALL（全表扫描）→ index → range → ref → eq_ref → const

第二步：SQL优化（高频原因）

避免 SELECT *

// ❌ 差
$sql = "SELECT * FROM users WHERE status = 1";
// ✅ 好
$sql = "SELECT id, name, email FROM users WHERE status = 1";

使用 LIMIT 限制返回行数

// ❌ 可能返回百万行
$sql = "SELECT * FROM logs ORDER BY created_at DESC";
// ✅ 只取100条
$sql = "SELECT * FROM logs ORDER BY created_at DESC LIMIT 100";

避免在 WHERE 中使用函数

// ❌ 索引失效
$sql = "SELECT * FROM users WHERE YEAR(created_at) = 2023";
// ✅ 索引可用
$sql = "SELECT * FROM users 
        WHERE created_at >= '2023-01-01' 
          AND created_at < '2024-01-01'";

避免隐式类型转换

// ❌ user_id 是 int，传字符串
$sql = "SELECT * FROM users WHERE user_id = '123'";
// ✅ 传整数
$sql = "SELECT * FROM users WHERE user_id = " . intval($userId);

LIKE 模糊查询优化

// ❌ 以 % 开头，索引失效
$sql = "SELECT * FROM users WHERE name LIKE '%张三%'";
// ✅ 前缀匹配，可以用索引
$sql = "SELECT * FROM users WHERE name LIKE '张三%'";

使用 UNION 代替 OR

// ❌ 可能导致全表扫描
$sql = "SELECT * FROM users 
        WHERE name = '张三' OR email = 'zhangsan@example.com'";
// ✅ 利用各自索引
$sql = "SELECT * FROM users WHERE name = '张三'
        UNION
        SELECT * FROM users WHERE email = 'zhangsan@example.com'";

使用 JOIN 代替子查询

// ❌ 子查询可能慢
$sql = "SELECT * FROM orders 
        WHERE user_id IN (SELECT id FROM users WHERE status = 1)";
// ✅ JOIN 更高效
$sql = "SELECT o.* FROM orders o
        INNER JOIN users u ON o.user_id = u.id
        WHERE u.status = 1";

第三步：索引优化（核心）

创建合适的索引

-- 单列索引
CREATE INDEX idx_email ON users(email);
-- 复合索引（最左前缀原则）
CREATE INDEX idx_status_created ON users(status, created_at);
-- 唯一索引
CREATE UNIQUE INDEX idx_username ON users(username);

复合索引最左原则

// 索引：idx_status_created (status, created_at)
// ✅ 好用（包含最左列）
WHERE status = 1
WHERE status = 1 AND created_at > '2023-01-01'
// ❌ 索引无效（没有最左列）
WHERE created_at > '2023-01-01'

查看索引使用情况

-- 查看表索引
SHOW INDEX FROM orders;
-- 查看实际使用了哪个索引
EXPLAIN SELECT * FROM orders WHERE order_no = 'ABC123';

第四步：PHP代码层面优化

使用 PDO 预处理语句

// ✅ 推荐：预处理 + 绑定参数
$stmt = $pdo->prepare("SELECT * FROM users WHERE email = ? AND status = ?");
$stmt->execute(['test@example.com', 1]);
$users = $stmt->fetchAll();

批量插入优化

// ❌ 慢：逐条插入
foreach ($data as $row) {
    $pdo->exec("INSERT INTO users (name, email) VALUES ('{$row['name']}', '{$row['email']}')");
}
// ✅ 快：批量插入
$values = [];
foreach ($data as $row) {
    $values[] = "('{$row['name']}', '{$row['email']}')";
}
$sql = "INSERT INTO users (name, email) VALUES " . implode(',', $values);
$pdo->exec($sql);
// 或者使用事务 + 分批提交
$pdo->beginTransaction();
foreach ($data as $i => $row) {
    $stmt = $pdo->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
    $stmt->execute([$row['name'], $row['email']]);
    if ($i % 1000 == 0) {
        $pdo->commit();
        $pdo->beginTransaction();
    }
}
$pdo->commit();

分页查询优化

// ❌ 深分页慢
$sql = "SELECT * FROM posts ORDER BY id DESC LIMIT 100000, 20";
// ✅ 使用游标分页（基于索引）
$lastId = $_GET['last_id'] ?? PHP_INT_MAX;
$sql = "SELECT * FROM posts 
        WHERE id < " . intval($lastId) . "
        ORDER BY id DESC LIMIT 20";

使用连接池/复用连接

// 推荐使用 Swoole、ReactPHP 等常驻内存方案
// 或者使用 PDO 长连接
$pdo = new PDO($dsn, $user, $pass, [
    PDO::ATTR_PERSISTENT => true  // 长连接
]);

第五步：数据库配置优化

my.cnf 建议配置

[mysqld]
# 缓冲区大小
innodb_buffer_pool_size = 2G  # 设为物理内存的 60-70%
key_buffer_size = 256M
# 查询缓存（MySQL 5.7以下适用）
query_cache_type = 1
query_cache_size = 64M
# 连接数
max_connections = 500
# 临时表
tmp_table_size = 64M
max_heap_table_size = 64M
# InnoDB 配置
innodb_log_file_size = 512M
innodb_flush_method = O_DIRECT

第六步：Redis 缓存优化（强烈推荐）

// 1. 缓存查询结果
function getUserById($pdo, $cache, $id) {
    $key = "user:{$id}";
    // 先查缓存
    $user = $cache->get($key);
    if ($user) {
        return json_decode($user, true);
    }
    // 没缓存，查数据库
    $stmt = $pdo->prepare("SELECT * FROM users WHERE id = ?");
    $stmt->execute([$id]);
    $user = $stmt->fetch(PDO::FETCH_ASSOC);
    // 写入缓存，设置过期时间
    $cache->setex($key, 3600, json_encode($user));
    return $user;
}
// 2. 缓存列表（防止缓存穿透）
function getHotProducts($pdo, $cache) {
    $key = "hot_products";
    if ($cache->exists($key)) {
        return json_decode($cache->get($key), true);
    }
    // 数据库查询
    $stmt = $pdo->query("SELECT * FROM products WHERE is_hot = 1 LIMIT 10");
    $products = $stmt->fetchAll(PDO::FETCH_ASSOC);
    // 写入缓存，防止缓存穿透设置过期时间稍短
    $cache->setex($key, 600, json_encode($products));
    return $products;
}

第七步：监控与排查工具

# 1. 实时查看进程
SHOW FULL PROCESSLIST;
# 2. 查看执行最多的SQL
SELECT SCHEMA_NAME, DIGEST_TEXT, COUNT_STAR 
FROM performance_schema.events_statements_summary_by_digest
ORDER BY COUNT_STAR DESC LIMIT 10;
# 3. 查看表状态（数据量、碎片）
SHOW TABLE STATUS FROM your_database;
# 4. 优化表（减少碎片）
OPTIMIZE TABLE your_table;
# 5. 使用工具分析
# mysqldumpslow /opt/mysql/data/slow.log
# pt-query-digest /opt/mysql/data/slow.log

第八步：架构级优化（进阶）

读写分离

主库写，从库读
使用中间件：ProxySQL、MaxScale

分库分表

按业务拆库
按主键 hash 分表
使用 ShardingSphere、Vitess

使用搜索引擎

全文搜索用 Elasticsearch
复杂聚合用 ClickHouse

合并请求

避免 N+1 查询

// ❌ N+1 查询
$users = $pdo->query("SELECT * FROM users")->fetchAll();
foreach ($users as $user) {
    $posts = $pdo->query("SELECT * FROM posts WHERE user_id = {$user['id']}");
}
// ✅ 一次 JOIN
$sql = "SELECT u.*, p.* 
        FROM users u
        LEFT JOIN posts p ON u.id = p.user_id";

调试速查清单（按优先级排序）

优先级	检查项	方法
1	EXPLAIN 分析SQL	找 type=ALL 的行
2	索引是否合理	SHOW INDEX + 删除冗余索引
3	是否有缓存	加入 Redis 缓存
4	LIMIT 是否限制	必须加 LIMIT
5	是否 SELECT *	只取需要的字段
6	表数据量	超过千万行考虑分表
7	PHP代码是否有N+1	用 JOIN 代替循环查询

最强排查组合

// 在开发环境临时输出慢查询日志
try {
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $start = microtime(true);
    // 你的SQL
    $result = $pdo->query($sql);
    $execTime = microtime(true) - $start;
    // 超过0.5秒记录
    if ($execTime > 0.5) {
        error_log("【慢查询】耗时：" . $execTime . "ms | SQL: " . $sql);
    }
} catch (PDOException $e) {
    error_log("SQL错误：" . $e->getMessage());
}

如果以上方法都无法解决，建议提供：表结构、SQL语句、EXPLAIN结果、数据量,我可以帮你具体分析。

本文地址： https://bmjup.com/post/38604.html