更新/删除sql语句查询速度慢
mysql中有很多慢查询，只与更新和删除语句有关。这些表有 2 个索引列，并且没有大量索引的表。每个表平均有30K条记录。
请就如何克服与更新和删除查询相关的缓慢查询提出建议。这些类型的查询：
DELETE FROM <table2>
WHERE ID IN (SELECT ID
FROM <table1> WHERE ID2=100);
...或：
UPDATE <table1>
SET <colmunname>=0
WHERE id=1001;
In mysql there are lot of slow queries, only related to update and delete statements. The tables have 2 index columns and not heavily indexed tables. Each table having 30K records on average.
Please give your suggestions on how to overcome the slow queries related to update and delete queries. These kind of queries:
DELETE FROM <table2>
WHERE ID IN (SELECT ID
FROM <table1> WHERE ID2=100);
...or:
UPDATE <table1>
SET <colmunname>=0
WHERE id=1001;
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
由于表已建立索引，我的第一个建议是使用 分析表：
但要注意：
Being that the tables are indexed, my first suggestion is to update the statistics for the tables using ANALYZE TABLE:
But beware:
事实上，您的问题仅存在于更新和删除上，这告诉我您可能索引太多。
索引将大大减少某些查询所花费的时间，但在从表中插入、更新和删除条目时需要额外的工作。尝试消除经常更新的列上的索引，尤其是当它们不经常出现在 SQL 查询的
where子句中时。The fact that your problem only exists on updates and deletes tells me that you are probably indexing too much.
Indexes will vastly reduce the time that certain queries take, but will require extra work when inserting, updating, and deleting entries from your tables. Try eliminating indexes on columns that are often getting updated, especially if they don't often appear in a
whereclause in your SQL queries.管理索引和外键关系都会在更新和删除操作期间产生开销。我将在开发服务器上恢复您的产品数据库的副本，删除所有外键约束以及所有期望的主键索引并查看性能差异。然后，您可以重新添加索引，直到您的应用程序获得更好的性能平衡。
Managing indexes and foreign key relationships both incur overhead during update and delete operations. I would restore a copy of your prod db on a dev server, drop all foreign key constraints and all expect your primary key indexes and see the performance difference. Then you can add back your indexes until you have a better performance balance for your app.
首先也是最重要的：使用存储过程。
下一篇：如果你的数据库有优化能力->使用它们。
最后：考虑使用非关系数据库，例如 CouchDB 或 Cassandra 相反；-)
First and foremost: Use stored procedures.
Next: If your db has optimizing capabilities -> use them.
Finally: Consider using no-relational dbs like CouchDB or Cassandra instead ;-)