blocks|key|655346|text|在MYSQL中有一种叫做低优先级更新的东西，它的文档记录在->+http://dev.mysql.com/doc/refman/5.5/en/server-options.html#option_mysqld_low-priority-updates中。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|655347|**仅适用于MyISAM、内存、合并存储引擎....|655348|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.5/en/server-options.html#option_mysqld_low-priority-updates^0|W|2L|0|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@$A|O|B|P|1|Q]]|C|$]]|$1|D|3|E|5|6|7|R|8|@]|9|@]|C|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|C|$]]]|G|$H|$5|I|J|K|C|$L|M]]]]

There is such a thing in MYSQL as low-priority-updates - its documented here -> <a href="http://dev.mysql.com/doc/refman/5.5/en/server-options.html#option_mysqld_low-priority-updates" rel="nofollow">http://dev.mysql.com/doc/refman/5.5/en/server-options.html#option_mysqld_low-priority-updates</a>

** only works for MyISAM, MEMORY, MERGE storage engines ....

blocks|key|658007|text|MYSQL中的低优先级请求之一是INSERT+DELAYED。但它只适用于插入，而不适用于更新或删除。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|658008|编辑:我只知道删除也可以延迟！|658009|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.5/en/insert-delayed.html^0|G|E|0|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@$A|O|B|P|1|Q]]|C|$]]|$1|D|3|E|5|6|7|R|8|@]|9|@]|C|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|C|$]]]|G|$H|$5|I|J|K|C|$L|M]]]]

One of the low priority requests in MYSQL is <a href="http://dev.mysql.com/doc/refman/5.5/en/insert-delayed.html" rel="nofollow">INSERT DELAYED</a>. But it only works on INSERT, not UPDATE or DELETE.

EDIT: I just knew that DELETE also can be delayed!

blocks|key|4749027|text|一种可能的解决方案是在从属复制服务器上复制旧数据。这样一来，沉重的负载就完全落在了那个从属机器上。在从服务器上完成复制后，您可以在主服务器上删除。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4749028|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

One possible solution would be to do the copying of old data on a slaved replication server. That way, the heavy load is entirely on that slave box. Once the copying is finished on slave, you could delete on master.

blocks|key|658046|text|正如the+documentation中所解释的，您可以将LOW_PRIORITY添加到查询中。因此，您的查询将变为：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|658047|$sql+=+"INSERT+LOW_PRIORITY+INTO+#__bet_oddslog+(id,+team1,+team2,+league,+sport,+time,+q1,+q2)";+$sql+.=+"+SELECT+a.id,+a.team1,+a.team2,+a.league,+a.sport,+a.time,+AVG(b.q1)+as+q1,+";+$sql+.=+"+AVG(b.q2)+as+q2+FROM+#__bet_details+as+a,+#__bet_1x2_best+as+b+WHERE+time<'".date('Y-m-d+H:i:s',+strtotime("now"))."'+AND+a.id=b.bet_id+GROUP+BY+b.bet_id";|code-block|syntax|javascript|658048|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.6/en/insert-select.html^0|T|C|2|H|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@$9|T|A|U|B|C]]|D|@$9|V|A|W|1|X]]|E|$]]|$1|F|3|G|5|H|7|Y|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Z|8|@]|D|@]|E|$]]]|L|$M|$5|N|O|P|E|$Q|R]]]]

As explained in <a href="http://dev.mysql.com/doc/refman/5.6/en/insert-select.html" rel="nofollow">the documentation</a>, you could add <code>LOW_PRIORITY</code> to your query. So, your query would become:

<pre><code>$sql = "INSERT LOW_PRIORITY INTO #__bet_oddslog (id, team1, team2, league, sport, time, q1, q2)"; $sql .= " SELECT a.id, a.team1, a.team2, a.league, a.sport, a.time, AVG(b.q1) as q1, "; $sql .= " AVG(b.q2) as q2 FROM #__bet_details as a, #__bet_1x2_best as b WHERE time&lt;'".date('Y-m-d H:i:s', strtotime("now"))."' AND a.id=b.bet_id GROUP BY b.bet_id";
</code></pre>

blocks|key|4749104|text|我认为清理工作中最繁重的任务是删除操作，您可以使用LOW_PRIORITY来完成该操作。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4749105|从MySql+manual+page中删除语法|offset|length|4749106|4749107|从tbl_name中删除LOW_PRIORITY+QUICK+...|blockquote|style|BOLD|4749108|4749109|4749110|和描述：|4749111|4749112|如果指定LOW_PRIORITY，则服务器会延迟执行删除操作，直到没有其他客户端正在从表中读取数据。这只会影响只使用表级锁定的存储引擎(如MyISAM、内存和合并)。|4749113|4749114|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.0/en/delete.html^0|0|7|B|0|0|0|C|C|0|0|0|0|0|4|C|0|0^^$0|@$1|2|3|4|5|6|7|11|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|12|8|@]|9|@$D|13|E|14|1|15]]|A|$]]|$1|F|3|-4|5|6|7|16|8|@]|9|@]|A|$]]|$1|G|3|H|5|I|7|17|8|@$D|18|E|19|J|K]]|9|@]|A|$]]|$1|L|3|-4|5|6|7|1A|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|1B|8|@]|9|@]|A|$]]|$1|N|3|O|5|6|7|1C|8|@]|9|@]|A|$]]|$1|P|3|-4|5|6|7|1D|8|@]|9|@]|A|$]]|$1|Q|3|R|5|I|7|1E|8|@$D|1F|E|1G|J|K]]|9|@]|A|$]]|$1|S|3|-4|5|6|7|1H|8|@]|9|@]|A|$]]|$1|T|3|-4|5|6|7|1I|8|@]|9|@]|A|$]]]|U|$V|$5|W|X|Y|A|$Z|10]]]]

I think the heaviest task in your cleanup is the DELETE operation for which you can use LOW_PRIORITY.

DELETE syntax from MySql <a href="http://dev.mysql.com/doc/refman/5.0/en/delete.html" rel="nofollow">manual page</a>:

<blockquote>
 DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name ...
</blockquote>

and the description:

<blockquote>
 If you specify LOW_PRIORITY, the server delays execution of the DELETE until no other clients are reading from the table. This affects only storage engines that use only table-level locking (such as MyISAM, MEMORY, and MERGE).
</blockquote>

I Have a site and a database with 5 million rows, it's working like a charm. However, I'm running a cleanup cronjob each hour, to put the old data to a 'log' table and delete the old data, and in this time the server response is very slow. Is it possible to give that job a lower priority via PHP or MySQL?

Low priority request in PHP or MySQL

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我有一个网站和一个有500万行的数据库，它的运行效果非常好。但是，我每小时运行一次清理cronjob，将旧数据放到'log‘表中并删除旧数据，此时服务器响应非常慢。有没有可能通过PHP或MySQL给该作业一个较低的优先级？

问PHP或MySQL中的低优先级请求
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问PHP或MySQL中的低优先级请求EN