我有一个 Percona 8 MySQL 服务器,通过 Docker 运行,充当频繁使用的服务的后备数据存储。整点时,将运行一个脚本,从大约 170 万行的虚拟列中读取一个值(表大小估计为 2.3 GiB),并将该特定值和关联的关键数据插入到系统仅读取的另一个表中。虚拟列是 JSON 查找json_extract(jsonData, '$.root.interestingValue')
并具有GENERATED
标志。其背后的想法是,当用户只对一些特定值感兴趣并且不需要检索绝对最新值时,对被视为实时且定期更新的表施加较小的压力。
查询如下(表/列已重命名)
CREATE TEMPORARY TABLE t1_cache_temp
SELECT
t2.id as uid,
t3.displayText as dt,
t2.virtualColumn as interestingValue
FROM liveTable t2
JOIN otherLiveTable t3 on t2.id = t3.id;
TRUNCATE TABLE t1_cache;
INSERT INTO t1_cache
SELECT uid, dt, interestingValue FROM t1_cache_temp;
DROP TEMPORARY TABLE t1_cache_temp;
该脚本花费的总时间为 47 秒。
虽然从 InnoDB 表读取数据并写入临时表没问题,但将其写入另一个 InnoDB 表会导致数据库上的所有其他操作在前几秒后挂起。INSERT INTO t1_cache...
我通过单独执行每个语句来将范围缩小到该语句。
我还尝试写入新的和完全未使用的表(也使用 InnoDB),这给出了相同的结果。在执行最后一次测试时,只有允许的最大连接数的 2% 正在使用中。此外,只有 48% 的 InnoDB 缓冲池正在使用。
如果我将表切换到 MyISAM,一切都会正常工作,不会出现中断或挂起的情况。此外,如果我删除临时表,无论 InnoDB 还是 MyISAM 引擎,都会导致相同的问题。
虽然使用 MyISAM 可能是更好的选择,但什么可能导致这种情况呢?在配置级别我可以做些什么吗?