PHPStorm 的数据库工具在处理海量数据时的性能优化

// 配置数据库连接池
$dsn = 'mysql:host=localhost;dbname=mydatabase';
$username = 'root';
$password = 'password';
$options = [
    PDO::ATTR_PERSISTENT => true,
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
];
$pdo = new PDO($dsn, $username, $password, $options);

-- 创建索引示例
CREATE INDEX idx_user_email ON users(email);

-- 使用 EXPLAIN 分析查询
EXPLAIN SELECT * FROM users WHERE email = 'example@example.com';

// 分页查询示例
$page = 1;
$perPage = 50;
$offset = ($page - 1) * $perPage;

$stmt = $pdo->prepare("SELECT * FROM users LIMIT :offset, :perPage");
$stmt->bindParam(':offset', $offset, PDO::PARAM_INT);
$stmt->bindParam(':perPage', $perPage, PDO::PARAM_INT);
$stmt->execute();
$users = $stmt->fetchAll();

// 键值分页示例
$lastId = isset($_GET['last_id']) ? $_GET['last_id'] : 0;
$perPage = 50;

$stmt = $pdo->prepare("SELECT * FROM users WHERE id > :lastId ORDER BY id LIMIT :perPage");
$stmt->bindParam(':lastId', $lastId, PDO::PARAM_INT);
$stmt->bindParam(':perPage', $perPage, PDO::PARAM_INT);
$stmt->execute();
$users = $stmt->fetchAll();

$lastUser = end($users);
$nextPageUrl = "?last_id=" . $lastUser['id'];

// 设置结果集最大行数
$stmt = $pdo->prepare("SELECT * FROM users");
$stmt->execute();
$stmt->setFetchMode(PDO::FETCH_ASSOC);
$stmt->setMaxRows(100); // 设置最大行数为100
$users = $stmt->fetchAll();