红穆笔记
首頁 › 踩坑總結 › db數據庫的php存儲類
踩坑總結

db數據庫的php存儲類

瀏覽 170 📖 約 32 分鐘
db数据库的php存储类

做一個備份。

研究了一下布隆過濾，可以達到小網站大數據大流量。

本質還是用了文本緩存，用廉價的硬盤空間，換取高價的內存。

效果還行，備份一下

<?php
/**
 * SQLite 緩存庫（已分離，僅供備份或兼容舊代碼）
 * 如需使用，請手動包含此文件
 */

// 定義緩存數據庫目錄
if (!defined('CACHE_DB_DIR')) {
    define('CACHE_DB_DIR', dirname(dirname(__FILE__)) . '/cache_db/');
}

// 性能常量
if (!defined('MAX_MEMORY_USAGE')) {
    define('MAX_MEMORY_USAGE', 64 * 1024 * 1024);
}
if (!defined('MAX_CACHE_SIZE')) {
    define('MAX_CACHE_SIZE', 100 * 1024 * 1024);
}
if (!defined('CACHE_CLEANUP_THRESHOLD')) {
    define('CACHE_CLEANUP_THRESHOLD', 0.7);
}

// 內存使用檢查函數
function checkMemoryUsage() {
    $currentUsage = memory_get_usage(true);
    if ($currentUsage > MAX_MEMORY_USAGE * 0.8) {
        if (function_exists('gc_collect_cycles')) {
            gc_collect_cycles();
        }
        return true;
    }
    return false;
}

// 緩存清理函數
function cleanupOldCache() {
    static $lastCleanupCheck = 0;
    if (time() - $lastCleanupCheck < 3600) {
        return;
    }
    $lastCleanupCheck = time();
    $cacheTypes = ['search_cache'];
    foreach ($cacheTypes as $type) {
        $cacheDir = CACHE_DB_DIR . $type;
        $cacheFiles = glob($cacheDir . "/*.db");
        $totalSize = 0;
        foreach ($cacheFiles as $file) {
            $totalSize += filesize($file);
        }
        if ($totalSize > MAX_CACHE_SIZE / 3) {
            $filesWithMtime = [];
            foreach ($cacheFiles as $file) {
                $filesWithMtime[$file] = filemtime($file);
            }
            asort($filesWithMtime);
            $deletedSize = 0;
            $targetSize = (MAX_CACHE_SIZE / 3) * CACHE_CLEANUP_THRESHOLD;
            foreach ($filesWithMtime as $file => $mtime) {
                if ($totalSize - $deletedSize <= $targetSize) break;
                $fileSize = filesize($file);
                if (@unlink($file)) {
                    $deletedSize += $fileSize;
                    DBPool::closeConnection($file);
                }
            }
            error_log("緩存清理完成 ({$type}): 刪除了 " . round($deletedSize/1024/1024, 2) . "MB 數據");
        }
    }
}

// 數據庫連接池管理類
class DBPool {
    private static $connections = [];
    private static $maxConnections = 10;
    
    public static function getConnection($dbFile) {
        if (isset(self::$connections[$dbFile])) {
            return self::$connections[$dbFile];
        }
        if (count(self::$connections) >= self::$maxConnections) {
            self::cleanup();
        }
        try {
            $db = new PDO("sqlite:{$dbFile}");
            $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            $db->exec("PRAGMA cache_size = -1000");
            $db->exec("PRAGMA temp_store = MEMORY");
            self::$connections[$dbFile] = $db;
            return $db;
        } catch (PDOException $e) {
            error_log("數據庫連接失敗: " . $e->getMessage());
            return false;
        }
    }
    
    public static function closeConnection($dbFile) {
        if (isset(self::$connections[$dbFile])) {
            self::$connections[$dbFile] = null;
            unset(self::$connections[$dbFile]);
        }
    }
    
    public static function cleanup() {
        $count = count(self::$connections);
        if ($count > self::$maxConnections * 0.7) {
            $keys = array_keys(self::$connections);
            $removeCount = (int)($count * 0.6);
            for ($i = 0; $i < $removeCount; $i++) {
                if (isset($keys[$i])) {
                    self::$connections[$keys[$i]] = null;
                    unset(self::$connections[$keys[$i]]);
                }
            }
        }
    }
    
    public static function shutdown() {
        foreach (self::$connections as $dbFile => $db) {
            self::$connections[$dbFile] = null;
        }
        self::$connections = [];
    }
    
    public static function getStats() {
        return [
            'current_connections' => count(self::$connections),
            'max_connections' => self::$maxConnections,
            'connection_files' => array_keys(self::$connections)
        ];
    }
}

register_shutdown_function(['DBPool', 'shutdown']);

// 確保緩存數據庫目錄存在
if (!file_exists(CACHE_DB_DIR)) {
    if (!mkdir(CACHE_DB_DIR, 0755, true)) {
        error_log('無法創建緩存數據庫目錄: ' . CACHE_DB_DIR);
        define('CACHE_DB_DIR', sys_get_temp_dir() . '/cache_db/');
        if (!file_exists(CACHE_DB_DIR)) {
            mkdir(CACHE_DB_DIR, 0755, true);
        }
    }
}

// 創建子目錄函數
function createCacheSubdirectories() {
    $subdirs = ['song_cache', 'search_cache', 'searchmore'];
    foreach ($subdirs as $subdir) {
        $dir = CACHE_DB_DIR . $subdir;
        if (!file_exists($dir)) {
            mkdir($dir, 0755, true);
        }
    }
}
createCacheSubdirectories();

// 獲取數據庫連接（基於MD5前3位分片）
function getCacheDB($type, $key) {
    $shard = substr(md5($key), 0, 3);
    switch ($type) {
        case 'song':
            $dbFile = CACHE_DB_DIR . "song_cache/{$type}_cache_{$shard}.db";
            break;
        case 'search':
            $dbFile = CACHE_DB_DIR . "search_cache/{$type}_cache_{$shard}.db";
            break;
        case 'searchmore':
            $dbFile = CACHE_DB_DIR . "searchmore/{$type}_{$shard}.db";
            break;
        default:
            $dbFile = CACHE_DB_DIR . "{$type}_cache_{$shard}.db";
    }
    $db = DBPool::getConnection($dbFile);
    if (!$db) return false;
    try {
        if ($type === 'song') {
            $db->exec("CREATE TABLE IF NOT EXISTS song_cache (
                song_id INTEGER PRIMARY KEY,
                detail TEXT,
                lyrics TEXT,
                timestamp INTEGER
            )");
            $db->exec("CREATE INDEX IF NOT EXISTS idx_timestamp ON song_cache(timestamp)");
        } elseif ($type === 'search') {
            $db->exec("CREATE TABLE IF NOT EXISTS search_cache (
                key TEXT PRIMARY KEY,
                keyword TEXT,
                data TEXT,
                timestamp INTEGER,
                access_count INTEGER DEFAULT 0,
                last_access INTEGER
            )");
            $db->exec("CREATE INDEX IF NOT EXISTS idx_keyword ON search_cache(keyword)");
            $db->exec("CREATE INDEX IF NOT EXISTS idx_timestamp ON search_cache(timestamp)");
            $db->exec("CREATE INDEX IF NOT EXISTS idx_access_count ON search_cache(access_count)");
            $db->exec("CREATE INDEX IF NOT EXISTS idx_last_access ON search_cache(last_access)");
        } elseif ($type === 'searchmore') {
            $db->exec("CREATE TABLE IF NOT EXISTS searchmore_suggestions (
                md5_key TEXT PRIMARY KEY,
                keyword TEXT NOT NULL,
                create_time INTEGER,
                last_access INTEGER,
                access_count INTEGER DEFAULT 0
            )");
            $db->exec("CREATE INDEX IF NOT EXISTS idx_keyword ON searchmore_suggestions(keyword)");
            $db->exec("CREATE INDEX IF NOT EXISTS idx_create_time ON searchmore_suggestions(create_time)");
            $db->exec("CREATE INDEX IF NOT EXISTS idx_last_access ON searchmore_suggestions(last_access)");
        }
        return $db;
    } catch (PDOException $e) {
        error_log("數據庫初始化失敗: " . $e->getMessage());
        DBPool::closeConnection($dbFile);
        return false;
    }
}

// 獲取歌曲完整緩存
function getSongCache($songId) {
    checkMemoryUsage();
    $db = getCacheDB('song', $songId);
    if (!$db) return false;
    try {
        $stmt = $db->prepare("SELECT detail, lyrics, timestamp FROM song_cache WHERE song_id = ?");
        $stmt->execute([$songId]);
        $result = $stmt->fetch(PDO::FETCH_ASSOC);
        if ($result) {
            return [
                'detail' => !empty($result['detail']) ? json_decode($result['detail'], true) : null,
                'lyrics' => !empty($result['lyrics']) ? json_decode($result['lyrics'], true) : null,
                'timestamp' => $result['timestamp']
            ];
        }
        return false;
    } catch (PDOException $e) {
        error_log("獲取歌曲緩存失敗: " . $e->getMessage());
        return false;
    }
}

// 設置歌曲完整緩存
function setSongCache($songId, $detail = null, $lyrics = null) {
    checkMemoryUsage();
    $db = getCacheDB('song', $songId);
    if (!$db) return false;
    $timestamp = time();
    try {
        $stmt = $db->prepare("SELECT detail, lyrics FROM song_cache WHERE song_id = ?");
        $stmt->execute([$songId]);
        $existing = $stmt->fetch(PDO::FETCH_ASSOC);
        $newDetail = $detail;
        $newLyrics = $lyrics;
        if ($existing) {
            if ($newDetail === null && !empty($existing['detail'])) {
                $newDetail = json_decode($existing['detail'], true);
            }
            if ($newLyrics === null && !empty($existing['lyrics'])) {
                $newLyrics = json_decode($existing['lyrics'], true);
            }
        }
        $stmt = $db->prepare("INSERT OR REPLACE INTO song_cache (song_id, detail, lyrics, timestamp) VALUES (?, ?, ?, ?)");
        return $stmt->execute([
            $songId,
            $newDetail ? json_encode($newDetail) : null,
            $newLyrics ? json_encode($newLyrics) : null,
            $timestamp
        ]);
    } catch (PDOException $e) {
        error_log("設置歌曲緩存失敗: " . $e->getMessage());
        return false;
    }
}

// 獲取搜索緩存
function getSearchCache($keyword, $type = 1) {
    checkMemoryUsage();
    $cacheKey = md5($keyword . '_' . $type);
    $db = getCacheDB('search', $cacheKey);
    if (!$db) return false;
    try {
        $stmt = $db->prepare("SELECT data, timestamp, access_count FROM search_cache WHERE key = ?");
        $stmt->execute([$cacheKey]);
        $result = $stmt->fetch(PDO::FETCH_ASSOC);
        if ($result) {
            $accessCount = $result['access_count'] + 1;
            $currentTime = time();
            $updateStmt = $db->prepare("UPDATE search_cache SET access_count = ?, last_access = ? WHERE key = ?");
            $updateStmt->execute([$accessCount, $currentTime, $cacheKey]);
            return [
                'data' => !empty($result['data']) ? json_decode($result['data'], true) : null,
                'timestamp' => $result['timestamp'],
                'access_count' => $accessCount
            ];
        }
        return false;
    } catch (PDOException $e) {
        error_log("獲取搜索緩存失敗: " . $e->getMessage());
        return false;
    }
}

// 設置搜索緩存
function setSearchCache($keyword, $type, $data) {
    checkMemoryUsage();
    $cacheKey = md5($keyword . '_' . $type);
    $db = getCacheDB('search', $cacheKey);
    if (!$db) return false;
    $timestamp = time();
    try {
        $stmt = $db->prepare("INSERT OR REPLACE INTO search_cache (key, keyword, data, timestamp, access_count, last_access) VALUES (?, ?, ?, ?, 1, ?)");
        return $stmt->execute([$cacheKey, $keyword, json_encode($data), $timestamp, $timestamp]);
    } catch (PDOException $e) {
        error_log("設置搜索緩存失敗: " . $e->getMessage());
        return false;
    }
}

// 獲取緩存統計信息
function getCacheStats() {
    $stats = [
        'song' => ['files' => 0, 'total_size' => 0, 'records' => 0],
        'search' => ['files' => 0, 'total_size' => 0, 'records' => 0, 'total_access_count' => 0, 'avg_access_count' => 0],
        'searchmore' => ['files' => 0, 'total_size' => 0, 'records' => 0],
        'pool_stats' => DBPool::getStats()
    ];
    foreach (['song', 'search', 'searchmore'] as $type) {
        $subdir = $type === 'searchmore' ? 'searchmore' : $type . '_cache';
        $files = glob(CACHE_DB_DIR . "{$subdir}/*.db");
        $stats[$type]['files'] = count($files);
        foreach ($files as $dbFile) {
            $stats[$type]['total_size'] += filesize($dbFile);
            try {
                $db = DBPool::getConnection($dbFile);
                if (!$db) continue;
                $table = '';
                switch ($type) {
                    case 'song':
                        $table = 'song_cache';
                        break;
                    case 'search':
                        $table = 'search_cache';
                        break;
                    case 'searchmore':
                        $table = 'searchmore_suggestions';
                        break;
                }
                $stmt = $db->prepare("SELECT COUNT(*) as count FROM {$table}");
                $stmt->execute();
                $result = $stmt->fetch(PDO::FETCH_ASSOC);
                $stats[$type]['records'] += $result['count'];
                if ($type === 'search') {
                    $stmt = $db->prepare("SELECT SUM(access_count) as total_access, AVG(access_count) as avg_access FROM search_cache");
                    $stmt->execute();
                    $accessResult = $stmt->fetch(PDO::FETCH_ASSOC);
                    $stats[$type]['total_access_count'] += $accessResult['total_access'] ?? 0;
                    if ($stats[$type]['records'] > 0) {
                        $stats[$type]['avg_access_count'] = round(($accessResult['avg_access'] ?? 0), 2);
                    }
                }
            } catch (PDOException $e) {
                // 忽略統計錯誤
            }
        }
        $stats[$type]['total_size_mb'] = round($stats[$type]['total_size'] / 1024 / 1024, 2);
    }
    return $stats;
}

// 獲取搜索緩存使用情況統計
function getSearchCacheUsageStats($limit = 100) {
    $stats = [
        'most_accessed' => [],
        'least_accessed' => [],
        'oldest_accessed' => [],
        'total_records' => 0,
        'total_access_count' => 0
    ];
    $files = glob(CACHE_DB_DIR . "search_cache/*.db");
    $allRecords = [];
    foreach ($files as $dbFile) {
        try {
            $db = DBPool::getConnection($dbFile);
            if (!$db) continue;
            $stmt = $db->prepare("SELECT key, keyword, access_count, last_access, timestamp FROM search_cache ORDER BY access_count DESC LIMIT ?");
            $stmt->execute([$limit * 2]);
            while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
                $allRecords[] = $row;
                $stats['total_records']++;
                $stats['total_access_count'] += $row['access_count'];
            }
        } catch (PDOException $e) {
            // 忽略錯誤
        }
    }
    usort($allRecords, function($a, $b) {
        return $b['access_count'] - $a['access_count'];
    });
    $stats['most_accessed'] = array_slice($allRecords, 0, $limit);
    $leastAccessed = array_slice($allRecords, -$limit);
    $stats['least_accessed'] = array_reverse($leastAccessed);
    usort($allRecords, function($a, $b) {
        return $a['last_access'] - $b['last_access'];
    });
    $stats['oldest_accessed'] = array_slice($allRecords, 0, $limit);
    return $stats;
}

// 從搜索緩存中隨機獲取歌曲
function getRandomSongsFromSearchCache($limit = 18) {
    checkMemoryUsage();
    $allSongs = [];
    $searchCacheFiles = glob(CACHE_DB_DIR . "search_cache/*.db");
    if (empty($searchCacheFiles)) {
        return [];
    }
    shuffle($searchCacheFiles);
    $maxAttempts = min(3, count($searchCacheFiles));
    for ($attempt = 0; $attempt < $maxAttempts && count($allSongs) < $limit; $attempt++) {
        $dbFile = $searchCacheFiles[$attempt];
        try {
            $db = DBPool::getConnection($dbFile);
            if (!$db) continue;
            $stmt = $db->prepare("SELECT data FROM search_cache ORDER BY RANDOM() LIMIT 10");
            $stmt->execute();
            $results = $stmt->fetchAll(PDO::FETCH_ASSOC);
            foreach ($results as $row) {
                if (!empty($row['data'])) {
                    $cacheData = json_decode($row['data'], true);
                    if (isset($cacheData['result']['songs']) && is_array($cacheData['result']['songs'])) {
                        foreach ($cacheData['result']['songs'] as $song) {
                            if (isset($song['id']) && isset($song['name']) && isset($song['artists'])) {
                                $allSongs[] = $song;
                                if (count($allSongs) >= $limit) {
                                    break 3;
                                }
                            }
                        }
                    }
                }
            }
        } catch (PDOException $e) {
            error_log("從搜索緩存獲取隨機歌曲失敗: " . $e->getMessage());
            continue;
        }
    }
    if (count($allSongs) > $limit) {
        shuffle($allSongs);
        $allSongs = array_slice($allSongs, 0, $limit);
    }
    return $allSongs;
}

// 獲取 SearchMore 數據庫連接
function getSearchMoreDB($keyword) {
    $db = getCacheDB('searchmore', $keyword);
    return $db;
}

// 保存搜索建議到數據庫
function saveSearchSuggestion($keyword) {
    checkMemoryUsage();
    $md5Key = md5($keyword);
    $db = getSearchMoreDB($keyword);
    if (!$db) return false;
    $currentTime = time();
    try {
        $stmt = $db->prepare("SELECT access_count FROM searchmore_suggestions WHERE md5_key = ?");
        $stmt->execute([$md5Key]);
        $existing = $stmt->fetch(PDO::FETCH_ASSOC);
        if ($existing) {
            $accessCount = $existing['access_count'] + 1;
            $updateStmt = $db->prepare("UPDATE searchmore_suggestions SET last_access = ?, access_count = ? WHERE md5_key = ?");
            return $updateStmt->execute([$currentTime, $accessCount, $md5Key]);
        } else {
            $stmt = $db->prepare("INSERT INTO searchmore_suggestions (md5_key, keyword, create_time, last_access, access_count) VALUES (?, ?, ?, ?, 1)");
            return $stmt->execute([$md5Key, $keyword, $currentTime, $currentTime]);
        }
    } catch (PDOException $e) {
        error_log("保存搜索建議失敗: " . $e->getMessage());
        return false;
    }
}

// 檢查搜索建議是否存在
function checkSearchSuggestionExists($keyword) {
    $md5Key = md5($keyword);
    $db = getSearchMoreDB($keyword);
    if (!$db) return false;
    try {
        $stmt = $db->prepare("SELECT keyword FROM searchmore_suggestions WHERE md5_key = ?");
        $stmt->execute([$md5Key]);
        return $stmt->fetch(PDO::FETCH_ASSOC) !== false;
    } catch (PDOException $e) {
        error_log("檢查搜索建議失敗: " . $e->getMessage());
        return false;
    }
}

 

微信赞赏

微信

支付宝赞赏

支付寶

版權聲明 · 禁止轉載

本文作者： 紅穆

本文鏈接： https://www.4s5.cn/cht/archives/1766.html

版權說明：本文爲原創文章，未經授權禁止任何形式的轉載、採集、鏡像，違者必究。如需轉載請註明出處並保留原文鏈接。

✍️ 作者： 紅穆

網站管理員 · 感謝閱讀，更多精彩內容敬請關注

作者主頁 查看主頁 →

相關文章

浏览器缓存增加你网站二次访问速度

瀏覽器緩存增加你網站二次訪問速度 踩坑總結

使用瀏覽器緩存官方話語：如果用戶會多次訪問您的網站，那麼靜態資源的瀏覽器緩存可以節省用戶的時間。緩存標頭應當應用到所有可緩存的靜態資源中，而不僅僅是應用到一小部分靜態資源（例如，圖片）中。可緩存的資源包括JS和CSS文件、圖像文件及其他二進制對象文件（媒體文件和PDF文件等）。通常情況下，HTML不…
👁 202
常用正则表达式

常用正則表達式 踩坑總結

正則表達式網址（URL）[a-zA-z]+://[^\s]*IP地址(IP Address)((2[0-4]\d|25[0-5]|[01]?\d\d?)\.){3}(2[0-4]\d|25[0-5]|[01]?\d\d?)電子郵件(Email)\w+([-+.]\w+)*@\w+([-.]\w+)*…
👁 243

推薦閱讀

(自适应手机版)响应式统一战线单位机构类网站pbootcms模板 红色部门机构网站源码下载 0470

(自適應手機版)響應式統一戰線單位機構類網站pbootcms模板 紅色部門機構網站源碼下載 0470 實用收藏 pbootcms模板

一款自適應手機端的黑色風景攝影工作室與個人寫真PbootCMS網站模板。設計風格酷炫藝術，適合攝影師、攝影工作室展示作品集及服務。有助於攝影機構在線上吸引高端客戶，提升品牌調性。模板展示 安裝說明 網站後臺：/admin.php 賬號：admin 密碼：admin 解壓密碼：www.4s5.cn相關…
👁 41
响应式spa芳香理疗网站模板 1038

響應式spa芳香理療網站模板 1038 實用收藏 易優模板

此套eyoucms響應式模板適用於SPA與芳香理療行業，設計風格優雅舒適，能夠展示SPA服務、芳香產品、理療項目及會所環境。有助於養生美容機構在線上吸引高端女性客戶。模板展示 安裝說明 網站後臺：/login.php 賬號：admin 密碼：admin 相關文章易優CMS安裝常見問題總結易優CMS（…
👁 50
帝国cms 视频收费播放下载新闻资讯门户HTML5整站响应式模板 044

帝國cms 視頻收費播放下載新聞資訊門戶HTML5整站響應式模板 044 實用收藏 帝國模板

採用帝國CMS新版核心製作，安全可靠，性能優越。所有操作均爲後臺操作，無需懂得代碼，即可通過簡單的配置來排版首頁調用！默認SEO已經處理好，無需模板上再進行處理。多種欄目列表樣式滿足各種欄目頻道風格需要，自帶MP4 M3U8等通用多終端播放功能，並能實現權限控制和扣點收費等操作。下載也實現了扣點和權…
👁 353
响应式精品美食特色汤盅网站模板 0440

響應式精品美食特色湯盅網站模板 0440 實用收藏 易優模板

此套eyoucms響應式模板適用於精品美食與特色湯盅行業，設計風格美食誘人，能夠展示特色菜品、湯盅美食、品牌故事及門店形象。有助於餐飲品牌在線上吸引食客，提升品牌知名度。模板展示 安裝說明 網站後臺：/login.php 賬號：admin 密碼：admin 相關文章易優CMS安裝常見問題總結易優CM…
👁 59
(自适应手机端)牙科诊所网站pbootcms模板 1034

(自適應手機端)牙科診所網站pbootcms模板 1034 實用收藏 pbootcms模板

一款牙科診所PbootCMS網站模板，支持PC與WAP端。設計風格專業潔淨，適合牙科診所展示醫療服務、醫生團隊及診療環境。有助於牙科醫療機構在線上吸引患者，提升品牌專業度。模板展示 安裝說明 網站後臺：/admin.php 賬號：admin 密碼：admin 解壓密碼：www.4s5.cn相關文章P…
👁 56
(自适应手机版)响应式营销型智能水表类网站pbootcms模板 html5蓝色智能水表网站源码下载 0471

(自適應手機版)響應式營銷型智能水錶類網站pbootcms模板 html5藍色智能水錶網站源碼下載 0471 實用收藏 pbootcms模板

本套自適應移動端的HTML5響應式律師律所PbootCMS網站模板。設計風格專業權威，適合律師及律所展示法律服務與成功案例。有助於法律服務機構在線上建立專業形象，吸引案源。模板展示 安裝說明 網站後臺：/admin.php 賬號：admin 密碼：admin 解壓密碼：www.4s5.cn相關文章P…
👁 60