大家好，我是煎魚。

最近我有一個朋友遇到了一個 MySQL 相關的問題，在開啟 DTS 升級同步后實例上的 SQL 查詢大量緩慢。但監控圖表上是一切正常的。

某技術支持給出的緣由是 table_open_cache 關聯因素造成的。咱們今天目標是深究一下這個指標和問題背后的邏輯。

table_open_cache 是什么

官方文檔介紹

根據 MySQL5.7 文檔，table_open_cache 參數項的具體作用如下：

1、所有線程打開的表的數量。增加該值會增加 mysqld 所需的 fd（文件描述符數量）。

2、table_open_cache 和 max_connections 系統變量會影響服務器保持打開狀態的最大文件數。如果增加其中一個或兩個值，可能會遇到操作系統對每個進程打開文件描述符數量的限制。

圖片

默認值是 2000。該值的修改范圍是全局。

總結一下：table_open_cache 代表 MySQL Server 所允許的所有線程打開表的總數量。這個變量可能會受 fd 文件描述符的影響。因此要經過測試慎重使用。

classTable__cache 緩存設計機制

MySQL 是多線程的，可以使用所有可用的 CPU。這意味著不同的線程內的多個會話都有可能同時訪問同一張表。而表數據最終還是磁盤上的數據文件。

如果每次都去反復重復打開和關閉表的文件句柄，這也太費資源了。肯定會影響使用性能。

因此 MySQL 官方設計了這個與 table_open_cache 相關的機制，在源碼文檔 classTable__cache 中進行了緣由介紹：

The idea behind this cache is that most statements don't need to go to a central table definition cache to get a TABLE object and therefore don't need to lock LOCK_open mutex. Instead they only need to go to one Table_cache instance (the specific instance is determined by thread id) and only lock the mutex protecting this cache. DDL statements that need to remove all TABLE objects from all caches need to lock mutexes for all Table_cache instances, but they are rare.

該機制簡單來講就是：“MySQL 通過線程獲取本地 Table_cache 的緩存實例，減少對全局 LOCK_open 鎖的競爭，從而優化并發查詢性能。DDL 操作需全局加鎖但較為罕見。”

具體場景描述

接下來結合 MySQL Table_cache 的緩存命中流程來講解一下步驟。這樣好理解一些。

關鍵步驟如下：

• MySQL Server 查詢執行時，線程首先檢查當前線程本地的 Table_cache 實例是否已經緩存了所需表的句柄。
• 如果當前線程的 Table_cache 沒有命中，線程會檢查全局 table_open_cache 中是否有表對象存在。
• 如果全局緩存也未命中，則需要從磁盤上打開表文件并創建新的表對象。
• 新打開的表會添加到全局 table_open_cache 和線程本地 Table_cache 中。作為后續的緩存機制使用。
• 如果 table_open_cache 已經滿了，需要添加新的表對象時，會使用 LRU 算法淘汰不用的表對象。

使用不合理有什么問題

1、table_open_cache 配置過低但表極多：如果當實例內的表數量過多，而 table_open_cache 配置相對過低時：可能會發現執行查詢會很慢。此時如果調用 show processlist，可以看到狀態 opening table 要花費好幾秒。

2、當 table_open_cache 配置的數值太大時：會顯著提高 MySQL 占用的內存。網上有位大佬的案例，把數值從 2000 增加到 10000，內存占用就從 500-600M 增長到了 2.5GB 左右。

3、classTable__cache 緩存查詢性能下降：當 table_open_cache 本身使用的是哈希表作為數據結構，以此實現查詢。如果 table_open_cache 設置的過于大，也意味著查詢性能的下降（與數值合理的情況下對比）。

DTS 為什么會誘發這個問題

雖然 DTS 普遍在文檔上標榜通過 binlog 來完成數據的同步和遷移。

作為程序員應該能察覺到一些異常。畢竟軟件設計沒有銀彈。有利有弊。

實際上針對本次問題，無主鍵表就有了明顯的不同差距點。

無主鍵表的掃描壓力

實際上 DTS 針對不包含主鍵的表，會追加一個字段做標識位，并對所有沒有主鍵的表進行掃描。

當沒主鍵的表非常多時，表大小的體積太大時，進行全表掃描，會造成查詢壓力，也會對 table_open_cache 內緩存的表數據產生擠壓（LRU）。

自然會對 MySQL 造成顯著壓力。

為什么無主鍵不用 binlog

為什么這里不直接用 binlog 來做呢？

實際上：對于沒有主鍵或唯一索引的表，難以唯一標識某一行記錄，很難解決并發更新或重復數據的問題。從而無法準確處理 UPDATE 和 DELETE 操作。

例如：沒有主鍵的表：UPDATE table SET columnA = 1 WHERE columnB = 2。如果 columnB 上沒有唯一約束，DTS 無法確定具體更新了哪些行。

而針對沒有沒有主鍵或唯一索引的表，DTS 會選擇全表掃描來確保數據同步的一致性和完整性：

• 完整數據比對：DTS 通過掃描源表和目標表，比較數據的差異（如新增、更新、刪除行）。避免因定位失敗導致部分變更數據丟失或同步錯誤。
• 數據一致性保障：binlog 的變更操作通常是增量同步的一部分，而全表掃描可以同步歷史數據和當前狀態，確保目標表與源表最終一致。

為什么會遇到這個問題

實際上在這位朋友的 MySQL 實例中，由于多租戶的屬性，存在著單實例、單數據庫存在大量數據庫表的場景。同時也存在大量無主鍵表的情況。

本次做的是 MySQL 版本的升級，常見流程是：購買新的 MySQL8 實例，再通過 DTS，從老的 MySQL5.6 同步數據到新的 MySQL 8.0 實例上。接著自動開啟實時同步，進入增量同步。

DTS 必然會先進行全量同步（binlog同步），再進行持續性的增量同步（掃描無主鍵表）。勢必給實例本身帶來一定的壓力。

尤其是這類存在大量非主鍵表時，意味著有持續的大量表掃描，疊加業務使用壓力，擠壓常用表的 cache，業務表 opening table 耗上個秒級，對于用戶端出問題是有較大可能性的。

DTS 需要面向的用戶群體過多，針對這種特殊場景，無限制的使用或其約定數值已無法很好的保護實例本身的使用。這也可以算得上 DTS 在設計上的一個缺陷。說明至少這個場景本身并沒有根據實例查詢情況做到智能調速。

table_open_cache 配置建議

MySQL 官方給出的建議是：在實際使用中，可以通過檢查 Opened_tables （打開過的表的數量總和）變量來確定是否需要增加表緩存。平時我們可以通過執行 show global status 看到該值。

如果 Opened_tables 的值很大，而且不經常使用 FLUSH TABLES（只強制關閉所有表并重新打開），那么就應該增加 table_open_cache 變量的值。

但是要注意，table_open_cache 再大。只要你實例里的表數量足夠多，大小夠大，使用的方式足夠頻繁，緩存的效益（性能）依舊會大大降低。

參考資料

• How MySQL Opens and Closes Tables：https://dev.mysql.com/doc/refman/5.7/en/table-cache.html
• Table_cache Class Reference：https://dev.mysql.com/doc/dev/mysql-server/8.4.3/classTable__cache.html#details