Der Aufruf einer gespeicherten Prozedur ist viel langsamer als der Aufruf einer Einfügung, und Masseneinfügungen sind grundsätzlich gleich. Warum?

Question

Ich habe eine Tabelle und eine gespeicherte Prozedur wie unten gezeigt,

CREATE TABLE `inspect_call` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `task_id` bigint(20) unsigned NOT NULL DEFAULT '0',
  `cc_number` varchar(63) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `created_at` bigint(20) unsigned NOT NULL DEFAULT '0',
  `updated_at` bigint(20) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `task_id` (`task_id`)
) ENGINE=InnoDB AUTO_INCREMENT=234031 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 

CREATE PROCEDURE inspect_proc(IN task bigint,IN number varchar(63))
INSERT INTO inspect_call(task_id,cc_number) values (task, number)

Ich hatte angenommen, dass der Aufruf der gespeicherten Prozedur viel schneller wäre als nur der Aufruf der Einfügung. Aber zu meiner Überraschung ist das nicht der Fall. Wenn ich 10.000 Datensatzzeilen einfüge, dauert der Einfügebefehl etwa 4 Minuten und die gespeicherte Prozedur dauert etwa 15 Minuten.

Ich habe den Test mehrmals durchgeführt, um dies zu bestätigen. Der MySQL-Server ist kein High-End-Server, aber ich verstehe nicht, warum der Aufruf gespeicherter Prozeduren so viel langsamer ist.

#using mysql-connector-python 8.0.31
command = ("INSERT INTO inspect_call (task_id,cc_number)"
           "VALUES (%s, %s)")
for i in range(rows): 
    cursor.execute(command, (task_id,f"{cc}{i}"))
    # cursor.callproc("inspect_proc", (task_id,f"{cc}{i}"))
cnx.commit()

Übrigens habe ich in einigen Artikeln gelesen, dass ich innodb_flush_log_at_trx_commit = 2 so einstellen könnte, dass die Einfügegeschwindigkeit erhöht wird, aber das werde ich nicht tun.

--- Update ---

Basierend auf den Antworten, die ich erhalten habe, habe ich das Stapeleinfügen (executemany) versucht, um zu sehen, ob es eine Verbesserung gab, aber zu meiner Überraschung gab es keine .

cursor = cnx.cursor(buffered=True)
for i in range(int(rows/1000)):
    data = []
    for j in range(1000):
        data.append((task_id,f"{cc}{i*1000+j}"))
    cursor.executemany(command,data)
 cnx.commit()

 # no improvement compared to 

 cursor = cnx.cursor()
 for i in range(rows):
    cursor.execute(command, (task_id,f"{cc}{i}"))

Ich habe es viele Male ausprobiert (auch executemany 100 Datensätze auf einmal ausprobiert) und festgestellt, dass ihre Leistung im Grunde die gleiche war.

Warum ist das so?

--- Update 2 ---

Endlich verstehe ich, warum das Einführen so langsam ist! Weil ich das Skript von meinem Laptop aus ausführe und über den externen Hostnamen auf die Datenbank zugreife. Nachdem ich das Skript auf den Server hochgeladen und über das Intranet auf die Datenbank zugegriffen hatte, ging es viel schneller. Das Einfügen von 10.000 Datensätzen dauert etwa 3 bis 4 Sekunden; das Einfügen von 100.000 Datensätzen dauert etwa 36 Sekunden. Mein fehlendes Internet würde einen großen Unterschied machen!

Aber executemany hat in meinem Fall die Leistung nicht verbessert.

Answer 1

您的示例不会归功于存储过程，因为它不会利用存储过程的任何优点。

存储过程的主要优点是：

• 已编译
• 它节省了网络交换（因为计算在服务器端进行）

假设您有一个足够复杂的逻辑，无法通过 UPDATE 进行操作，并且您希望进行操作，例如在Python中，它需要：

• 选择行 -> 网络流量[服务器 -> 客户端]
• 更新行 -> 相当慢：Python 被解释，如果您使用像 SQLAlchemy 这样的 ORM（必须在内存中创建对象），可能会更慢
• 发回更新的行 -> 网络流量[客户端 -> 服务器]

想象一下使用存储过程实现的相同示例。 在这种示例中，存储过程很有可能真正发挥作用。

在您的示例中，您没有任何逻辑，只是插入行。 这是一个 I/O 绑定用例。拥有一个已编译的程序没有或几乎没有什么好处。 您将拥有与使用 INSERT 一样多的网络交换。 无论以何种方式，行都必须发送到服务器。 网络流量也没有增加。

在您的示例中，也许批量插入可以帮助实现最佳性能。