为什么 DTS 升级 MySQL 会造成查询缓慢？

最近我有一个朋友遇到了一个 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 的缓存命中流程来讲解一下步骤。这样好理解一些。

关键步骤如下：

1. MySQL Server 查询执行时，线程首先检查当前线程本地的 Table_cache 实例是否已经缓存了所需表的句柄。
2. 如果当前线程的 Table_cache 没有命中，线程会检查全局 table_open_cache 中是否有表对象存在。
3. 如果全局缓存也未命中，则需要从磁盘上打开表文件并创建新的表对象。
4. 新打开的表会添加到全局 table_open_cache 和线程本地 Table_cache 中。作为后续的缓存机制使用。
5. 如果 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 通过扫描源表和目标表，比较数据的差异（如新增、更新、删除行）。避免因定位失败导致部分变更数据丢失或同步错误。