MYSQL:在存储过程中并行执行多个语句? [英] MYSQL: Parallel execution of multiple statements within a stored procedure?

问题描述

我有一个过程 (procedureA)，它循环遍历一个表并使用从该表派生的变量调用另一个过程 (procedureB).

I have a procedure (procedureA) that loops through a table and calls another procedure (procedureB) with variables derived from that table.

对 procedureB 的每次调用都独立于最后一次调用.

Each call to procedureB is independent of the last call.

当我运行 procedureA 时，我的系统资源显示最大 CPU 使用率为 50%(我假设这是我的 2 个 CPU 内核中的 1 个).

When I run procedureA my system resources show a maximum CPU use of 50% (I assume that is 1 out of my 2 CPU cores).

但是，如果我打开两个 mysql 终端实例并在两个终端中执行查询，则两个 CPU 内核都被使用(CPU 使用率可以达到接近 100%).

However, if I open two instances of the mysql terminal and execute a query in both terminals, both CPU cores are used (CPU usage can reach close to 100%).

如何在存储过程中实现同样的效果?

How can I achieve the same effect inside a stored procedure?

我想做这样的事情:

BEGIN
    CALL procedureB(var1); -> CPU CORE #1
    SET var1 = var1+1;
    CALL procedureB(var1); -> CPU CORE #2
END

我知道这不会那么容易...有小费吗?

I know its not going to be that easy... Any tips?

推荐答案

在 MySQL 中，要异步完成某事，您必须使用 CREATE EVENT，但我不确定是否创建一个在存储过程中是允许的.(附带说明:异步插入当然可以使用 INSERT DELAYED 完成，但这是 1 个线程，句点).

Within MySQL, to get something done asynchronously you'd have to use an CREATE EVENT, but I'm not sure whether creating one is allowed within a stored procedure. (On a side note: async. inserts can of course be done with INSERT DELAYED, but that's 1 thread, period).

通常情况下，您最好有几个进程/工人/守护进程，它们可以由您的程序异步访问并拥有自己的数据库连接，但当然不会在同一过程中.

Normally, you are much better of having a couple of processes/workers/deamons which can be accessed asynchronously by you program and have their own database connection, but that of course won't be in the same procedure.

这篇关于MYSQL:在存储过程中并行执行多个语句?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！