mysql - ERROR 1206 (HY000): 锁总数超过锁表大小

问题描述

我有一张大桌子，大约有 8000 万行。我正在尝试使用以下查询生成一些统计信息并将其存储在其他表中 -

insert into table2
select col1, col2,
STR_TO_DATE(date(col_timestamp), '%Y-%m-%d') as col_date,
sum(col4)/1000000 as total_size ,count(*) as total_count
from table1 group by col1,col2, col_date;

目的是对主表中的一些统计数据进行预处理，因为需要 15 分钟才能获得简单的计数。我收到此错误 -

 The total number of locks exceeds the lock table size

我尝试增加锁的总数超过锁表大小并重新启动mysqlinnodb_buffer_pool_size中1G提到的。有效值已成功增加为

'innodb_buffer_pool_size', '1073741824'

但错误仍然相同。所以，我的问题是——

我应该将它进一步增加到2G吗？
查询的哪一部分导致了这个问题——是 select 语句还是 insert 语句？

标签： mysqlsql

这将有助于发布表的架构，因为很难知道列是什么数据类型，或者你的索引是什么。

除此之外，最突出的是您的 col_date 字段是在 SELECT 时间创建的，这意味着您正在为查询创建一个新的/未索引的列，该列正在 GROUP BY 中使用 - 所以您实际上是在创建一个整个新列逐行获取 80m 行，然后表格扫描 80m 行的结果以找出分组。我会考虑在 table1 中添加一个 DATE 类型的新列，并将转换后的时间戳数据永久存储在那里。然后，您的 GROUP BY 应该能够更优化地工作（在新的 DATE 列上使用正确的索引）。我还将 table2 更改为类型 DATE，并避免将 DATE 转换为 STRING - 如果/当您需要以与其他表不同的格式读取它时，只需使用日期即可。

如果您使用 SELECT 语句，我想如果您从 SELECT/GROUP BY 中删除 col_date，则查询的其余部分应该运行得非常快，从而确认计算列是问题所在。如果没有，我会尝试从该 SELECT 中添加/删除不同的列，并玩弄你的索引，以确定哪些列特别减慢了查询速度。不幸的是，如果没有从模式中创建确切的表，然后有 80m 行样本数据来测试，其他人很难为您测试

更多信息：虽然最初发布的问题是关于增加数据库资源，但在我看来，这并不是真正正确的解决方案。如果您有一个需要 15 分钟才能运行的查询，并且由于使用了所有数据库资源而无法完成，那么增加这些资源实际上只是一种创可贴的解决方案。桌子仍然会变大，再次需要更多资源，这不是永久修复。

任何运行这么长时间并使用这么多资源的查询都需要优化。我看不到任何关于您尝试执行的操作的内容，只要优化数据和索引以使数据库可以正确完成工作，就不需要花费几秒钟的时间来运行-数据库可以吃这种东西起来，我已经看到更复杂的查询在更大的数据集上运行，没有你遇到的问题（一旦优化）。

另外，一旦您在 table1 上设置了 DATE 列，我们是否可以假设记录从未添加过旧时间戳？如果是这样，您甚至不必每次都对整个表执行查询，您实际上只需要每天重新运行查询以获取新数据，历史数据的统计信息在计算后将保持不变。或者您可以分解查询以针对不同的日期范围多次运行，再次减少使用的资源 - 有很多方法可以优化这种情况。

mysql - ERROR 1206 (HY000): 锁总数超过锁表大小

问题描述

解决方案

推荐阅读