SoFunction
Updated on 2025-03-03

Pay attention to the implementation of resource limitations before online query of mysql

Resource limitation is a means to prevent database queries from taking up too much time and resources. The following is an example of how to set query execution time in MySQL and SQL Server.

MAX_EXECUTION_TIME for MySQL

In MySQL 5.7.8 and above, you can useMAX_EXECUTION_TIMEPrompt to limit the execution time of SQL statements. This prompt tells the MySQL server to allow queries to run a specific number of milliseconds only. If the query execution time exceeds this limit, the server terminates the query.

example:

SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM my_table;

The above query sets the maximum execution time to 1000 milliseconds (1 second). If the query executes more than this time, it will be interrupted.

SQL Server's QUERY_GOVERNOR_COST_LIMIT

SQL Server uses the query processor cost estimate to determine whether a query should be executed.QUERY_GOVERNOR_COST_LIMITis a configuration value that represents the maximum cost that a query can be allowed to execute. If the query cost exceeds this value, SQL Server will not execute the query.

example:

-- Set the query governor cost limit to 300
SET QUERY_GOVERNOR_COST_LIMIT 300;
GO

-- Run a query
SELECT * FROM my_table;
GO

In the above example,QUERY_GOVERNOR_COST_LIMITSet to 300. This means that any query with a cost estimate of more than 300 will not be executed. This limit will last until the end of the session or the value is changed.

Notice

  • MySQLMAX_EXECUTION_TIMELimits apply only to SELECT statements.
  • SQL ServerQUERY_GOVERNOR_COST_LIMITAll queries that apply to the session until it is reset or the session ends.
  • These restrictions should not be set arbitrarily, but should be determined based on system performance and business needs. Setting too low may cause normal queries to fail to complete, and too high may not prevent excessive consumption of system resources.
  • When setting resource limits, other related configurations and limitations should also be considered, such as MySQL'sinnodb_lock_wait_timeoutUsed to limit the time when a transaction is waiting for lock, SQL ServerMAXDOP(Maximum parallelism) is used to limit the maximum number of processors that can be used by the query, etc.

In actual applications, timeouts can also be set through the database management tools or at the application level, so that even if the database itself does not terminate execution, the application can interrupt excessively long queries as needed.

This is the article about the implementation of resource restrictions before online querying mysql. This is all about this article. For more related content on mysql resource restrictions, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!