大家好，我是煎鱼。
最近我有一个朋友遇到了一个 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
