大家好，我是煎魚。

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

某技術(shù)支持給出的緣由是 table_open_cache 關(guān)聯(lián)因素造成的。咱們今天目標(biāo)是深究一下這個指標(biāo)和問題背后的邏輯。

table_open_cache 是什么

官方文檔介紹

根據(jù) MySQL5.7 文檔，table_open_cache 參數(shù)項(xiàng)的具體作用如下：

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

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

圖片

默認(rèn)值是 2000。該值的修改范圍是全局。

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

classTable__cache 緩存設(shè)計(jì)機(jī)制

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

如果每次都去反復(fù)重復(fù)打開和關(guān)閉表的文件句柄，這也太費(fèi)資源了?？隙〞绊懯褂眯阅?。

因此 MySQL 官方設(shè)計(jì)了這個與 table_open_cache 相關(guān)的機(jī)制，在源碼文檔 classTable__cache 中進(jìn)行了緣由介紹：

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.

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

具體場景描述

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

關(guān)鍵步驟如下：

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

使用不合理有什么問題

1、table_open_cache 配置過低但表極多：如果當(dāng)實(shí)例內(nèi)的表數(shù)量過多，而 table_open_cache 配置相對過低時：可能會發(fā)現(xiàn)執(zhí)行查詢會很慢。此時如果調(diào)用 show processlist，可以看到狀態(tài) opening table 要花費(fèi)好幾秒。

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

3、classTable__cache 緩存查詢性能下降：當(dāng) table_open_cache 本身使用的是哈希表作為數(shù)據(jù)結(jié)構(gòu)，以此實(shí)現(xiàn)查詢。如果 table_open_cache 設(shè)置的過于大，也意味著查詢性能的下降（與數(shù)值合理的情況下對比）。

DTS 為什么會誘發(fā)這個問題

雖然 DTS 普遍在文檔上標(biāo)榜通過 binlog 來完成數(shù)據(jù)的同步和遷移。

作為程序員應(yīng)該能察覺到一些異常。畢竟軟件設(shè)計(jì)沒有銀彈。有利有弊。

實(shí)際上針對本次問題，無主鍵表就有了明顯的不同差距點(diǎn)。

無主鍵表的掃描壓力

實(shí)際上 DTS 針對不包含主鍵的表，會追加一個字段做標(biāo)識位，并對所有沒有主鍵的表進(jìn)行掃描。

當(dāng)沒主鍵的表非常多時，表大小的體積太大時，進(jìn)行全表掃描，會造成查詢壓力，也會對 table_open_cache 內(nèi)緩存的表數(shù)據(jù)產(chǎn)生擠壓（LRU）。

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

為什么無主鍵不用 binlog

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

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

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

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

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

為什么會遇到這個問題

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

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

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

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

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

table_open_cache 配置建議

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

如果 Opened_tables 的值很大，而且不經(jīng)常使用 FLUSH TABLES（只強(qiáng)制關(guān)閉所有表并重新打開），那么就應(yīng)該增加 table_open_cache 變量的值。

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

參考資料

• 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