mysql - Mysql中的低优先级查询

Question

是否可以为 MySQL 中的查询分配较低的优先级，类似于命令行上的 nice 命令(在 Linux 中)？如果没有，是否有数据库可以做类似的事情？

Answer 1

您可以根据执行的查询类型在查询中使用 LOW_PRIORITY 或 HIGH_PRIORITY:

INSERT [LOW_PRIORITY | HIGH_PRIORITY] INTO ...
SELECT [HIGH_PRIORITY] * FROM ...
UPDATE [LOW_PRIORITY] table ...

来自Mysql 5.7 documentation对于 INSERT 查询，例如:

If you use the LOW_PRIORITY keyword, execution of the INSERT is delayed until no other clients are reading from the table. This includes other clients that began reading while existing clients are reading, and while the INSERT LOW_PRIORITY statement is waiting. It is possible, therefore, for a client that issues an INSERT LOW_PRIORITY statement to wait for a very long time.

但也有人说:

This affects only storage engines that use only table-level locking (such as MyISAM, MEMORY, and MERGE)

因此，例如，除非您想使用 LOCK_TABLES，否则您将无法将此功能与 innodb 一起使用从而降低其性能。