大家好，我是煎魚(yú)。

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

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

table_open_cache 是什么

官方文檔介紹

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

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

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

圖片

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

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

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

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

如果每次都去反復(fù)重復(fù)打開(kāi)和關(guān)閉表的文件句柄，這也太費(fèi)資源了?？隙〞?huì)影響使用性能。

因此 MySQL 官方設(shè)計(jì)了這個(gè)與 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ī)制簡(jiǎn)單來(lái)講就是：“MySQL 通過(guò)線程獲取本地 Table_cache 的緩存實(shí)例，減少對(duì)全局 LOCK_open 鎖的競(jìng)爭(zhēng)，從而優(yōu)化并發(fā)查詢性能。DDL 操作需全局加鎖但較為罕見(jiàn)?！?/p>

具體場(chǎng)景描述

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

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

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

使用不合理有什么問(wèn)題

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

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

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

DTS 為什么會(huì)誘發(fā)這個(gè)問(wèn)題

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

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

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

無(wú)主鍵表的掃描壓力

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

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

自然會(huì)對(duì) MySQL 造成顯著壓力。

為什么無(wú)主鍵不用 binlog

為什么這里不直接用 binlog 來(lái)做呢？

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

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

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

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

為什么會(huì)遇到這個(gè)問(wèn)題

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

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

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

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

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

table_open_cache 配置建議

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

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

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

參考資料

• 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