MySQL 中处理高负载表的常见做法
我在 MySQL 5 (InnoDB) 中有一个表用作守护进程处理队列，因此它的访问非常频繁。通常每天插入大约 250,000 条记录。当我选择要处理的记录时，使用 FOR UPDATE 查询读取它们以消除竞争条件（一切都是基于事务的）。
现在我正在开发一个“队列存档”，我无意中遇到了严重的死锁问题。我需要在处理（实时）表时从表中删除“已执行”记录，但如果我这样做，表偶尔会死锁（每天两到三次）。
我虽然倾向于延迟删除（在低负载时间每天一次），但这并不能消除问题，只会让它变得不那么明显。
MySQL 中处理高负载表有常见做法吗？
I have a table in MySQL 5 (InnoDB) that is used as a daemon Processing Queue, thus it is being accessed very often. It is typical to have around 250 000 records inserted per day. When I select records to be processed, they are read using a FOR UPDATE query to eliminate race conditions (everything is Transaction Based).
Now I am developing a "queue archive" and I have stumbled into a serious dead-lock problem. I need to delete "executed" records from the table as they are being processed (live), yet the table dead-locks every once in a while if I do so (two-three times per day at).
I though of moving towards delayed deletion (once per day at low load times) but this will not eliminate the problem only make it less obvious.
Is there a common-practice in dealing with high-load tables in MySQL?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
InnoDB锁定它检查的所有行，而不仅仅是那些请求的行。有关更多详细信息，请参阅此问题。
您需要创建一个与您的搜索条件完全匹配的索引，以摆脱不必要的锁定，并确保它被使用。
不幸的是，
MySQL中的DML查询不接受提示。InnoDBlocks all rows it examines, not only those requested.See this question for more details.
You need to create an index that would exactly match your search condition to get rid of unnecessary locks, and make sure it is used.
Unfortunately,
DMLqueries inMySQLdo not accept hints.