您现在的位置：程式碼之家 >> 編程語言 > >> 網頁程式設計 >> PHP 程式設計 >> PHP 相關知識

關鍵詞匹配系統性能優化：分表策略的深度實踐與代碼實現

編輯：PHP 相關知識

關鍵詞匹配系統性能優化：分表策略的深度實踐與代碼實現

隨著業務增長，關鍵詞庫從數萬條激增至百萬級，單表查詢性能急劇下降。本文記錄某電商關鍵詞匹配項目引入水平分表策略的完整決策過程、技術方案與代碼落地，為面臨相似擴容挑戰的團隊提供參考。

問題背景與痛點

系統上線初期，關鍵詞表僅 5 萬條，單表查詢毫秒級返回。隨著自動采集系統上線，詞庫在兩周內暴增至 300 萬條，導致：

關鍵詞選詞接口 P99 延遲從 50ms 飆升至 3s+
運營團隊反饋「選詞等待時間過長，嚴重影響工作效率」
MySQL CPU 長期佔用 90%+，慢查詢日誌暴增

技術方案選型：水平分表

經架構評審，決定採用按商品類目（cid）水平分表：

分表維度	優勢	劣勢	適用性
按類目 CID	業務隔離性強，單表數據量可控，查詢路由簡單	熱門類目可能仍較大，需二級分片	✅ 最佳
按時間（月/周）	實現簡單，冷熱數據天然分離	跨時間範圍查詢複雜，不符合業務檢索習慣	❌ 不適合
按關鍵詞 Hash	數據分布均勻	業務無關，篩選時需掃描所有分表	❌ 不適合

核心代碼實現

1. 關鍵詞模型層適配

<?php
// lib/Keyword.php
class Keyword {
    public $word;
    public static $conn = null;
    
    private static $dbConfig = [
        'host' => '127.0.0.1',
        'user' => 'xiaoshuaishuai',
        'pass' => 'xiaoshuaishuai',
        'charset' => 'utf8',
        'dbname' => 'dict',
    ];

    public function getDbConn() {
        if (self::$conn === null) {
            self::$conn = mysqli_connect(
                self::$dbConfig['host'],
                self::$dbConfig['user'],
                self::$dbConfig['pass']
            );
            mysqli_set_charset(self::$dbConfig['charset']);
            mysqli_select_db(self::$conn, self::$dbConfig['dbname']);
        }
        return self::$conn;
    }

    public function save() {
        // 寫入時需根據業務邏輯確定目標分表
        $sql = "INSERT INTO keywords_{$this->cid} (word) VALUES (?)";
        $stmt = mysqli_prepare($this->getDbConn(), $sql);
        mysqli_stmt_bind_param($stmt, 's', $this->word);
        return mysqli_stmt_execute($stmt);
    }

    /**
     * 分頁獲取指定類目的關鍵詞
     */
    public static function getWordsSource($cid, $limit = 0, $offset = 40) {
        $sql = "SELECT * FROM keywords_{$cid} LIMIT {$limit}, {$offset}";
        return DB::makeArray($sql);
    }

    public static function getWordsCount($cid) {
        $sql = "SELECT COUNT(*) FROM keywords_{$cid}";
        return DB::queryScalar($sql);
    }
}

2. 數據庫操作基礎類增強

<?php
// lib/DB.php
class DB {
    public static $conn = null;
    private static $config = [
        'host' => '127.0.0.1',
        'user' => 'xiaoshuaishuai',
        'pass' => 'xiaoshuaishuai',
        'charset' => 'utf8',
        'dbname' => 'dict',
    ];

    public static function connect() {
        if (self::$conn === null) {
            self::$conn = mysqli_connect(
                self::$config['host'],
                self::$config['user'],
                self::$config['pass']
            );
            mysqli_set_charset(self::$conn, self::$config['charset']);
            mysqli_select_db(self::$conn, self::$config['dbname']);
        }
        return self::$conn;
    }

    public static function query($sql) {
        return mysqli_query(self::connect(), $sql);
    }

    public static function makeArray($sql) {
        $result = [];
        $rs = self::query($sql);
        while ($row = mysqli_fetch_assoc($rs)) {
            $result[] = $row;
        }
        return $result;
    }

    /**
     * 獲取單值（如 COUNT(*)）
     */
    public static function queryScalar($sql) {
        $rs = self::query($sql);
        $row = mysqli_fetch_row($rs);
        return $row ? (int)$row[0] : 0;
    }
}

3. 選詞服務層邏輯重構

<?php
// selector/Selector.php
class Selector {
    private static $charListHandle = [
        "黑名單" => "BacklistCharListHandle",
        "近義詞" => "LinklistCharListHandle"
    ];

    public static function select($numIid) {
        $selectorItem = SelectorItem::createFromApi($numIid);
        Logger::trace($selectorItem->props_name);
        
        $charList = new CharList();
        foreach (self::$charListHandle as $matchKey => $className) {
            $handle = self::createCharListHandle($className, $charList, $selectorItem);
            $handle->exec();
        }

        $selectWords = [];
        // 關鍵變化：針對具體 cid 分表查詢
        $wordsCount = Keyword::getWordsCount($selectorItem->cid);
        $pageSize = 40;
        $totalPages = ceil($wordsCount / $pageSize);

        for ($page = 0; $page < $totalPages; $page++) {
            $limit = $page * $pageSize;
            $keywords = Keyword::getWordsSource($selectorItem->cid, $limit, $pageSize);
            
            foreach ($keywords as $val) {
                $keywordEntity = SplitterApp::split($val["word"]);
                if (MatchExecutor::match($keywordEntity, $charList)) {
                    $selectWords[] = $val["word"];
                }
            }
        }
        return $selectWords;
    }

    private static function createCharListHandle($className, $charList, $selectorItem) {
        if (class_exists($className)) {
            return new $className($charList, $selectorItem);
        }
        throw new Exception("Handler class not exists: {$className}");
    }
}