优化非常大的表查询
我有一个包含接近 2000 万条记录并且还在不断增长的表。该表设置为 innodb。两个主要字段上有一个主索引：
`entries_to_fields`
entry_id int(11) NO PRI NULL
field_id int(11) NO PRI NULL
value text NO NULL
尽管记录数量很多，但对该表的大多数查询都非常快，但以下情况除外：
DELETE FROM `entries_to_fields` WHERE `entry_id` IN (SELECT `id` FROM `entries` WHERE `form_id` = 196)
这将删除特定表单的所有条目数据。
目前，即使条目表没有返回结果，这也需要超过 45 秒。
我的问题是是否可以对 entries_to_fields 结构进行简单的更改，或者我可以进一步优化我的查询。
I have a table that is close to 20million records and growing. The table was setup as innodb. There is a primary index on the two main fields:
`entries_to_fields`
entry_id int(11) NO PRI NULL
field_id int(11) NO PRI NULL
value text NO NULL
Despite the number of records, most of the queries to this table are exceptionally quick, except for the following:
DELETE FROM `entries_to_fields` WHERE `entry_id` IN (SELECT `id` FROM `entries` WHERE `form_id` = 196)
This deletes all entry data for a specific form.
Currently this is taking over 45 seconds, even if the entries table returns no results.
My question is can is there a simple change to the entries_to_fields structure I can make, or can I optomise my query further.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
在我阅读您的答案后，我写了这个查询，它也可能对您有帮助（将来）。
...条目.form_id 字段应该被索引。
After I read your answer, I wrote this query that may help you as well (in future).
... entries.form_id field should be indexed.
经过一番尝试后错误+谷歌搜索，我发现在大型表的索引字段上使用
IN是一种非常糟糕的做法。我已将子查询分解为一个单独的查询，然后创建一个动态查询，如下所示：
尽管生成了潜在的大型查询，但它现在在约 1 秒内执行。即使删除了数千个条目。
After a bit of trial & error + googling, I found using
INon index fields on large tables is a very bad practice.I've broken the sub-query into a separate query and then created a dynamic query as follows:
Despite generating a potential large query, this executes within ~1sec now. Even when deleting 1000's of entries.
我会查看查询计划，我的猜测是子查询返回 NULL 并进行删除完整扫描。
请参阅：
http://dev.mysql.com/doc /refman/5.0/en/in-subquery-optimization.html
I would look at the query plan, my guess is the subquery is returning NULL and making the delete full scan.
see :
http://dev.mysql.com/doc/refman/5.0/en/in-subquery-optimization.html