呼叫預存程序比呼叫插入慢得多，並且批量插入基本上相同，為什麼？

Question

我有一個表格和一個預存程序，如下所示，

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)

我曾假設呼叫預存程序會比僅呼叫插入快得多。但令我驚訝的是，事實並非如此。當我插入 10000 行記錄時，插入命令大約需要 4 分鐘，而預存程序大約需要 15 分鐘。

我已經多次執行測試來確認這一點。 MySQL伺服器不是高階伺服器，但我不明白為什麼呼叫預存程序慢得多。

#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()

順便說一句，我讀到一些文章說我可以設定 innodb_flush_log_at_trx_commit = 2 來提高插入速度，但我不打算這樣做。

--- 更新 ---

根據我得到的答案，我嘗試批量插入（executemany）以查看是否有任何改進，但令我驚訝的是沒有。

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}"))

我嘗試了很多次（也嘗試了executemany 1次拍攝100筆記錄），發現他們的表現基本上相同。

這是為什麼？

--- 更新 2 ---

我終於明白為什麼插入這麼慢了！因為我從筆記型電腦運行腳本並從其外部主機名稱存取資料庫。一旦我將腳本上傳到伺服器並從內部網路內部存取資料庫，速度就會快得多。插入10000筆記錄大約需要3到4秒；插入 100,000 筆記錄大約需要 36 秒。我沒有網路會造成這樣的差異！

但是 executemany 並沒有提高我的情況下的效能。

Answer 1

您的範例不會歸功於預存程序，因為它不會利用預存程序的任何優點。

預存程序的主要優點是：

• 已編譯
• 它節省了網路交換（因為計算在伺服器端進行）

#假設您有一個足夠複雜的邏輯，無法透過 UPDATE 進行操作，並且您希望進行操作，例如在Python中，它需要：

• 選擇行 -> 網路流量[伺服器 -> 用戶端]
• #更新行 -> 相當慢：Python 被解釋，如果您使用像 SQLAlchemy 這樣的 ORM（必須在記憶體中建立物件），可能會更慢
• #傳回更新的行 -> 網路流量[客戶端 -> 伺服器]

想像一下使用預存程序實作的相同範例。 在這種範例中，預存程序很有可能真正發揮作用。

在您的範例中，您沒有任何邏輯，只是插入行。 這是一個 I/O 綁定用例。擁有一個已編譯的程式沒有或幾乎沒有什麼好處。 您將擁有與使用 INSERT 一樣多的網路交換。 無論以何種方式，行都必鬚髮送到伺服器。 網路流量也沒有增加。

在您的範例中，也許批次插入可以幫助實現最佳效能。