用 MySQL MEMORY 表替代 PHP Session 儲存的類別實作

動機

• 預設 `files` session 擴展性差 (單機、inode 壓力、NFS 慢)。
• 資料庫 session 便於**多伺服器共享**、**查詢分析**、**集中清理**。
• **MEMORY 引擎** 全記憶體操作，讀寫極快，適合 session 高頻存取。

核心架構

• SessionEngine 介面：定義 `get/set/create/refresh/cleanup/close`。
• MysqlSessionEngine 實作：MEMORY 表 + 可選 MyISAM 溢位表 (資料過長時)。
• Session 外觀類：魔術方法 `__get/__set` 透明代理 `$_SESSION`。

關鍵程式碼節錄

<?php
interface SessionEngine {
    public function get($key = '');
    public function set($key = '', $value = '');
    public function create($key = '', $value = '');
    public function refresh($key = '');
    public function cleanup();
    public function close();
}

class MysqlSessionEngine implements SessionEngine {
    private $id, $table, $tableSlow, $maxLen, $expire, $conn, $ver;

    public function __construct(array $cfg, $sessionId, &$dbConn) {
        $this->id = $sessionId;
        $this->table = $cfg['storage_name'];
        $this->tableSlow = $this->table . '_slow';
        $this->expire = $cfg['expire_time'];
        $this->conn = $dbConn;
        $this->ver = (int)$this->one("SELECT FLOOR(VERSION())");
        $this->maxLen = $this->ver >= 5 ? 2048 : 255; // MEMORY 表 VARCHAR/CHAR 上限
        $this->initTable();
    }

    private function initTable() {
        if ($this->ver >= 5) {
            $sql = "CREATE TABLE IF NOT EXISTS `{$this->table}` (
                `id` CHAR(32) NOT NULL DEFAULT 'ERR',
                `value` VARBINARY({$this->maxLen}) NULL,
                `time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY (`id`), KEY `time` (`time`)
            ) ENGINE=MEMORY;";
        } else {
            // MySQL 4: MEMORY 不支援 VARBINARY > 255，需溢位表
            $sqlSlow = "CREATE TABLE IF NOT EXISTS `{$this->tableSlow}` (
                `id` CHAR(32) NOT NULL DEFAULT 'ERR',
                `value` TEXT NULL,
                `time` INT(10) NOT NULL DEFAULT 0,
                PRIMARY KEY (`id`), KEY `time` (`time`)
            ) ENGINE=MyISAM;";
            $this->exec($sqlSlow);
            $sql = "CREATE TABLE IF NOT EXISTS `{$this->table}` (
                `id` CHAR(32) NOT NULL DEFAULT 'ERR',
                `value` VARCHAR(255) NULL,
                `time` INT(10) NOT NULL DEFAULT 0,
                PRIMARY KEY (`id`), KEY `time` (`time`)
            ) ENGINE=MEMORY;";
        }
        $this->exec($sql);
    }

    public function get($key = '') {
        $key = $key ?: $this->id;
        $val = $this->one("SELECT `value` FROM `{$this->table}` WHERE `id`=?", [$key]);
        if ($val === '{__DATA_TOO_LONG__}') {
            $val = $this->one("SELECT `value` FROM `{$this->tableSlow}` WHERE `id`=?", [$key]);
        }
        return $val ? @unserialize($val) : [];
    }

    public function set($key = '', $value = '') {
        $key = $key ?: $this->id;
        $ser = serialize($value);
        if (strlen($ser) > $this->maxLen) {
            if ($this->ver >= 5) throw new Exception("Session data too long");
            // MySQL 4: 存入溢位表，主表存標記
            $this->exec("REPLACE INTO `{$this->tableSlow}` (`id`,`value`,`time`) VALUES (?,?,UNIX_TIMESTAMP())", [$key, $ser]);
            $ser = '{__DATA_TOO_LONG__}';
        }
        $this->exec("REPLACE INTO `{$this->table}` (`id`,`value`,`time`) VALUES (?,?,CURRENT_TIMESTAMP)", [$key, $ser]);
    }

    public function refresh($key = '') {
        $key = $key ?: $this->id;
        $this->exec("UPDATE `{$this->table}` SET `time`=CURRENT_TIMESTAMP WHERE `id`=?", [$key]);
    }

    public function cleanup() {
        $this->exec("DELETE FROM `{$this->table}` WHERE `time` < DATE_SUB(NOW(), INTERVAL {$this->expire} SECOND)");
        if ($this->ver < 5) $this->exec("DELETE FROM `{$this->tableSlow}` WHERE `time` < UNIX_TIMESTAMP() - {$this->expire}");
    }

    // 輔助: one(), exec() 使用預處理語句防注入
}

使用範例

<?php
$link = mysqli_connect('localhost', 'root', 'pass', 'test');
$session = new Session($link); // 內部 session_start()

$session->lastVisit = $session->lastVisit ?? time();
$session->visitCount = ($session->visitCount ?? 0) + 1;

echo "首次造訪: " . date('Y-m-d H:i:s', $session->lastVisit) . "<br>";
echo "造訪次數: {$session->visitCount}<br>";
?>

現代替代方案建議

**生產環境首選 Redis**，僅在無法引入新基礎設施時才考慮 MySQL MEMORY 表。