有效的方法来标记mysql中具有最小字段值和常见fieldX值的记录 [英] Efficient way to flag a record with min field value and common fieldX value in mysql

问题描述

我试图标记一个表中所有记录的所有记录，这些记录的所有记录都具有一个通用的FieldX值。

I am trying to flag all records in a table that have the minimum value for all records with a common FieldX Value.

我的查询是这样的：
TableA

My query was as such: TableA

Update TableA as T1
Inner Join (Select ID,Name,Min(ValueField) from TableA 
where GroupFlag='X' 
Group by CommonTermField) as T2
On T1.ID=T2.ID
Set MainFlag='Y';

这个工作一段时间后，但我不断得到超时/表锁定错误，我假设它是因为该表有2600万条记录（具有适当的索引）。在这种情况下，是否有更有效的方式来更新vs使用内部连接？

This worked awhile back but I keep getting a timeout/table locked error and I am assuming that it is because the table is 26 million records long (with appropriate indexes). Is there a more efficient way to update vs using an inner-join in this case?

更新：
尝试运行后另一个更新/内连接，以前工作，也得到表锁定类型的错误，它发生在我身上，最近我们迁移到较大的服务器，所以我们将有开销处理这些表。我在DevOps出局时做了一些检查，结果表明设置没有被迁移（还），所以我们之前2GB的innodb_buffer_pool只有128MB。我一直在等待，直到他们进入迁移该设置和其他设置，但99％确定查询中的低效率（以前运行良好）是由于此原因。我将在此之前将Q打开，如果innodb_pool修复程序能够解决我们自己的问题，并且改变了我们的设置，以及在遇到其他人遇到此问题时的情况（看起来事实上mysql查询效率低下问题）。

Update: After trying to run another Update/Inner-Join that previously worked and also getting a table-locked type error, it occurred to me that recently we migrated to larger servers so we would have overhead to work with these tables. I did some checking while DevOps is out and it turns out the settings weren't migrated (yet) so our "innodb_buffer_pool" which had previously been 2GB was only 128MB. I am waiting until they get in to migrate that and other settings, but am 99% sure the "inefficiency" in the query (which previously worked fine) is due to that. I will leave the Q open until then and if the innodb_pool fix works answer my own question with the settings we changed and how in case anyone else runs into this issue (seeming query inefficiency in fact mysql settings issue).

推荐答案

好的，回答的问题是Mysql设置。显然，当我们迁移服务器时，DevOps / SysAdmin确实迁移了设置，但没有重新启动服务器，因为我跳入查询模式。

Ok so answer to the question was Mysql settings. Apparently when we migrated servers DevOps/SysAdmin did migrate settings but didn't restart server as I jumped right into query-mode. We restarted last night and things worked swimmingly.

问题在于innodb_buffer_pool默认设置为128MB，我们的自定义设置为2GB。

The issue was that innodb_buffer_pool was set to 128MB by default and our custom settings had it at 2GB.

这篇关于有效的方法来标记mysql中具有最小字段值和常见fieldX值的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！