PHP 数据库访问深入：PDO、Eloquent 与 MySQL 性能优化

$dsn = 'mysql:host=127.0.0.1;port=3306;dbname=app;charset=utf8mb4';
$pdo = new PDO($dsn, 'app', 'secret', [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,   // 用真预处理
]);

// 预处理：SQL 与参数分离，天然防注入
$stmt = $pdo->prepare(
    'SELECT id, name FROM users WHERE email = ? AND active = ?'
);
$stmt->execute([$email, $active]);
$users = $stmt->fetchAll();

// 命名占位符（可读性更好）
$stmt = $pdo->prepare('UPDATE users SET name = :name WHERE id = :id');
$stmt->execute([':name' => 'Alice', ':id' => 1]);
// 注意：execute 的数组键要么都带 :，要么都不带

// 绑定参数（精确控制类型）
$stmt->bindParam(':id', $id, PDO::PARAM_INT);

// 推荐：抛异常，捕获后统一处理
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION

// 反例：静默返回 false，容易漏查
PDO::ATTR_ERRMODE => PDO::ERRMODE_SILENT

// execute 数组里的字符串会被当字符串绑定
// "0" 与 0 在不同 MySQL 模式下可能行为不同
$stmt = $pdo->prepare('SELECT * FROM t WHERE id = ?');
$stmt->execute([$id]);        // 若 $id 是 "1" 字符串，某些场景走索引可能异常
// 稳妥：bindParam 指定类型，或强制转换
$stmt->execute([(int) $id]);

$users = User::query()
    ->where('active', true)
    ->orderBy('id', 'desc')
    ->limit(20)
    ->get();

$user = User::find(1);
$user->name = 'Alice';
$user->save();

// 实体类 + 注解/属性映射
#[ORM\Entity]
#[ORM\Table(name: 'users')]
class User {
    #[ORM\Id, ORM\Column(type: 'integer')]
    private int $id;
    #[ORM\Column(type: 'string', length: 100)]
    private string $name;
}

// 通过 EntityManager 操作
$em->persist($user);
$em->flush();

$pdo->beginTransaction();
try {
    $pdo->exec('UPDATE accounts SET balance = balance - 100 WHERE id = 1');
    $pdo->exec('UPDATE accounts SET balance = balance + 100 WHERE id = 2');
    $pdo->commit();
} catch (Throwable $e) {
    $pdo->rollBack();
    throw $e;
}

DB::transaction(function () {
    $order = Order::create([...]);
    Stock::where('product_id', $order->product_id)->decrement('qty', 1);
});

// 短连接成本高，一个请求可能建多次连接
// 用静态/单例缓存连接（同进程内复用）
final class Db {
    private static ?PDO $pdo = null;
    public static function pdo(): PDO {
        return self::$pdo ??= new PDO($dsn, ...);
    }
}

// Swoole 协程连接池（示意）
$pool = new ConnectionPool(size: 10, factory: fn() => new PDO($dsn, ...));
$conn = $pool->get();
try {
    // 查询
} finally {
    $pool->put($conn);   // 归还复用
}

-- 常用索引
CREATE INDEX idx_email ON users(email);
CREATE INDEX idx_user_status ON users(user_id, status);   -- 复合索引
-- 覆盖索引：查询列都在索引里 → 免回表

EXPLAIN SELECT * FROM orders WHERE user_id = 1 AND created_at > '2026-01-01';

-- 函数包裹列 → 索引失效
WHERE YEAR(created_at) = 2026;            -- 坏
WHERE created_at >= '2026-01-01'
  AND created_at <  '2027-01-01';         -- 好

-- 前导通配符 → 索引失效
WHERE name LIKE '%abc';                    -- 坏
WHERE name LIKE 'abc%';                    -- 好

-- 类型不一致 → 隐式转换失效
WHERE mobile = 13800138000;                -- 若 mobile 是 VARCHAR，索引失效
WHERE mobile = '13800138000';              -- 好

slow_query_log = ON
long_query_time = 1        # 超过 1 秒记录

// 坏：循环里每次查询（N+1）
$orders = Order::all();
foreach ($orders as $order) {
    echo $order->customer->name;   // 每次触发一条 SELECT
}

// 好：预加载（Eager Loading）
$orders = Order::with('customer')->get();   // 一条查询 + JOIN/IN

// 批量插入：一条语句多组值
$stmt = $pdo->prepare('INSERT INTO logs (msg, level) VALUES (?, ?)');
foreach ($logs as $log) {
    $stmt->execute([$log['msg'], $log['level']]);   // 复用预处理
}
// 或
INSERT INTO logs (msg, level) VALUES ('a', 1), ('b', 2), ('c', 3);

$page  = max(1, (int)($_GET['page'] ?? 1));
$size  = 20;
$stmt  = $pdo->prepare(
    'SELECT * FROM orders ORDER BY id DESC LIMIT ? OFFSET ?'
);
$stmt->bindValue(1, $size, PDO::PARAM_INT);
$stmt->bindValue(2, ($page - 1) * $size, PDO::PARAM_INT);

// 用上次的 id 做游标，走索引无扫描
SELECT * FROM orders
WHERE id < :lastId          -- 上一页最后一条 id
ORDER BY id DESC
LIMIT 20;