PHP页面查询多Schema PostgreSQL加载缓慢，月更数据库求缓存方案

Great question—since your database only updates once a month, caching is an absolute no-brainer here. You can cut that 2-3 minute load time down to practically nothing with these targeted solutions tailored to your setup:

1. Full Page Output Caching (Simplest Approach) #

Since each dropdown selection maps to a unique schema, and results won’t change until the next monthly update, caching the entire rendered page for each schema is the easiest win.

Here’s how to implement it with basic file-based caching:

// Get the selected schema from user input (adjust based on your form method)
$selectedSchema = $_GET['schema'] ?? $_POST['schema'];
$cacheDir = __DIR__ . '/page_cache/';
$cacheFile = $cacheDir . 'page_' . $selectedSchema . '.html';
$currentMonth = date('Y-m');

// Create cache directory if it doesn't exist
if (!is_dir($cacheDir)) {
    mkdir($cacheDir, 0755, true);
}

// Check if valid cache exists (created this month)
if (file_exists($cacheFile) && date('Y-m', filemtime($cacheFile)) === $currentMonth) {
    // Serve cached page immediately
    readfile($cacheFile);
    exit;
}

// If no valid cache, run your database queries and render the page
ob_start(); // Start output buffering

// --- Your existing query and page rendering code goes here ---
// Example: Switch to the selected schema, run queries, echo HTML
$pdo = new PDO("mysql:host=localhost;dbname=$selectedSchema", 'db_user', 'db_pass');
$stmt = $pdo->query('SELECT * FROM your_large_dataset');
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
// ... render HTML with $results ...

// Capture the rendered content and save to cache
$pageContent = ob_get_clean();
file_put_contents($cacheFile, $pageContent);

// Serve the freshly rendered page
echo $pageContent;

• Pro Tip: Set up a cron job to delete all cache files on the 1st of every month (e.g., 0 0 1 * * rm /path/to/your/page_cache/*.html) to ensure users get the latest data right after the database update.

2. Query Result Caching (For Reusable Data) #

If you need more flexibility (e.g., reusing query results across different parts of your app), cache the raw database results instead of the full page. You can use file storage, Redis, or Memcached for this.

Here’s a Redis example (fast and scalable):

$selectedSchema = $_GET['schema'] ?? $_POST['schema'];
$cacheKey = "query_results_$selectedSchema";

// Connect to Redis
$redis = new Redis();
$redis->connect('localhost', 6379);

// Calculate cache expiry: last minute of the current month
$expiryTime = strtotime('last day of this month 23:59:59') - time();

// Check for cached results
$cachedResults = $redis->get($cacheKey);
if ($cachedResults !== false) {
    $results = unserialize($cachedResults);
} else {
    // Run your database queries
    $pdo = new PDO("mysql:host=localhost;dbname=$selectedSchema", 'db_user', 'db_pass');
    $stmt = $pdo->query('SELECT * FROM your_large_dataset');
    $results = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    // Save results to cache with expiry
    $redis->set($cacheKey, serialize($results), $expiryTime);
}

// Use $results to render your page as usual

This lets you reuse the cached data across multiple pages or components without re-running queries.

3. Server-Level Caching (No Code Changes) #

If you don’t want to modify your PHP code, use Apache or Nginx’s built-in caching features to cache responses based on the selected schema.

Nginx Example: #

Add this to your Nginx config to cache responses by schema parameter:

http {
    # Define cache storage
    proxy_cache_path /var/cache/nginx/schema_cache levels=1:2 keys_zone=schema_cache:10m max_size=10g inactive=30d;

    server {
        location /your-slow-php-page.php {
            proxy_cache schema_cache;
            # Cache key includes the schema parameter to avoid mix-ups
            proxy_cache_key "$request_method$host$request_uri$arg_schema";
            # Cache valid responses for 30 days (until end of month)
            proxy_cache_valid 200 30d;
            # Pass requests to your PHP backend
            proxy_pass http://127.0.0.1:9000; # Adjust to your PHP-FPM setup
        }
    }
}

Apache Example (with mod_cache): #

Enable mod_cache and mod_disk_cache, then add this to your .htaccess or server config:

CacheEnable disk /your-slow-php-page.php
CacheRoot /var/cache/apache2/schema_cache
CacheDefaultExpire 2592000 # 30 days in seconds
CacheKeyExpr "%{REQUEST_METHOD} %{HTTP_HOST} %{REQUEST_URI} %{QUERY_STRING}"

This approach offloads caching to the web server, which is extremely efficient.

Key Notes to Avoid Issues #

• Unique Cache Keys: Always include the schema name in your cache key (file name, Redis key, or server cache key) to prevent mixing up results from different schemas.
• Cache Invalidation: Either set cache expiry to the end of the month, or use a cron job to clear cache on the 1st of every month—this ensures users never see stale data after the database update.
• Permissions: If using file-based caching, make sure your web server user (e.g., www-data on Linux) has read/write access to the cache directory.

内容的提问来源于stack exchange，提问作者John Wick