如何在MySql中限制查询或确定查询的优先级 [英] How to throttle or prioritize a query in MySql

问题描述

在MySQL中到底有没有优先排序或限制查询的条件?

Is there anyway to prioritize or throttle a query at all in MySQL?

我是服务器上的DBA，看到很多未优化的查询进入服务器，它们只是破坏了CPU.我正在考虑限制某些以不良方式访问数据库的用户.

I'm a DBA on a server that sees a lot of unoptimized queries come into the server and they just destroy the CPU. I'm looking at throttling certain users that hit on the database in poor fashion.

说明:

我意识到MySQL具有内置的工具来限制查询，连接等的数量.但是这些并不是真正的问题，因为一旦用户放心，用户将发送未优化的查询，我需要超时或类似的时间.

I realize that MySQL has facilities built in to limit number of queries, connections, etc. But those aren't really the issue, it's that once in a blue moon a user will send an unoptimized query, and I'd need to time it out or something similar.

推荐答案

从MySQL 4.0.2开始，您可以限制单个帐户对以下服务器资源的访问:

Starting from MySQL 4.0.2, you can limit access to the following server resources for individual accounts:

一个帐户每小时可以发出的查询数量

The number of queries that an account can issue per hour

一个帐户每小时可以发布的更新次数

The number of updates that an account can issue per hour

一个帐户每小时可以连接到服务器的次数

The number of times an account can connect to the server per hour

其他信息-

MySQL没有查询超时值或任何其他限制单个查询的内置方法.但是，编写一个脚本将杀死长时间运行的查询非常简单.您甚至可以捕获用户并进行查询，以便以后可以击败有问题的用户/程序员.

MySQL does not have a query timeout value, or any other built-in way to throttle an individual query. However, it is pretty trivial to write a script that will kill long-running queries. You could even capture the user and query so that you can beat the offending user/programmer later.

此处是使用PERL的示例.

Here is an example of one using PERL.

这篇关于如何在MySql中限制查询或确定查询的优先级的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！