MySQL 多查詢執行技術與最佳實踐

– 9 分鐘閱讀

MySQL 的多查詢執行對於提升資料函式庫操作效率至關重要。本文介紹了 cmd_query_iter()、cursor.execute(multi=True) 和 cursor.executemany() 等多查詢執行方法，並分析了它們各自的適用場景和效能考量。cmd_query_iter() 適合執行多個不同型別的查詢，而 cursor.execute(multi=True) 則更具彈性，可以處理帶引數的查詢。cursor.executemany() 則專注於批次執行相同結構的查詢，尤其在大量資料插入時效能優異。此外，文章也深入探討了 MySQL Connector/Python 中的緩衝遊標，說明其在更新資料和處理結果集時的優勢，並結合預存程式的應用，展示了更進階的資料函式庫操作技巧。選擇正確的多查詢執行方法和使用緩衝遊標能有效提升資料函式庫操作的效率，而預存程式則能封裝複雜的資料函式庫邏輯，簡化應用程式碼並提升可維護性。

MySQL 資料函式庫中的多查詢執行技術與最佳實踐

在進行資料函式庫操作時，高效地執行多個查詢是一項重要的技術挑戰。本文將深入探討在 MySQL 資料函式庫中執行多查詢的方法，包括使用 cmd_query_iter()、cursor.execute() 和 cursor.executemany() 等技術，並分析其效能與安全性考量。

使用 cmd_query_iter() 執行多查詢

cmd_query_iter() 是一種支援多查詢執行的特殊方法。它允許將多個查詢陳述式組合成一個字串，並透過迭代器傳回結果集。

實作範例

# 匯入必要的函式庫
import mysql.connector

# 建立資料函式庫連線
db = mysql.connector.connect(
 option_files="my.ini",
 # 設定連線引數
)

# 定義多個查詢陳述式
queries = [
 "SELECT * FROM world.city WHERE CountryCode = 'USA'",
 "DO SLEEP(3)",
 "SELECT * FROM world.city WHERE CountryCode = 'IND'"
]

# 使用 cmd_query_iter() 執行多查詢
for result in db.cmd_query_iter(";".join(queries)):
 if result.with_rows:
 print("查詢結果：")
 for row in result.fetchall():
 print(row)
 else:
 print("無結果")

內容解密

此範例展示瞭如何使用 cmd_query_iter() 方法執行多個查詢。查詢陳述式透過分號分隔組合成一個字串，並透過迭代器處理每個查詢的結果。對於傳回結果集的查詢，會印出查詢結果；對於不傳回結果的查詢（如 DO SLEEP(3)），則會顯示「無結果」。

使用 cursor.execute() 執行多查詢

除了 cmd_query_iter()，也可以使用 cursor.execute() 方法執行多查詢。透過設定 multi=True 引數，可以實作多查詢的執行。

實作範例

# 建立遊標物件
cursor = db.cursor()

# 定義查詢陳述式和引數
sql_select = "SELECT Name, CountryCode, Population FROM world.city WHERE CountryCode = %s"
sql_do = "DO SLEEP(3)"
queries = [sql_select, sql_do, sql_select]
params = ("USA", "IND")

# 使用 cursor.execute() 執行多查詢
results = cursor.execute(";".join(queries), params=params, multi=True)

# 處理查詢結果
count = 0
for result in results:
 count += 1
 print(f"查詢 {count} 結果：")
 if result.with_rows:
 for row in cursor.fetchall():
 print(row)
 else:
 print("無結果")

內容解密

此範例展示瞭如何使用 cursor.execute() 方法執行多查詢，並處理每個查詢的結果。需要注意的是，所有查詢的引數必須組合成一個 tuple 或 list，這在某些情況下可能會導致引數管理的複雜性。

使用 cursor.executemany() 執行批次查詢

當需要執行多個相同的查詢，但使用不同的引數時，cursor.executemany() 方法是一個很好的選擇。

實作範例

# 定義查詢範本和引數
sql = "UPDATE world.city SET Population = %(population)s WHERE Name = %(name)s AND CountryCode = %(country)s"
params = [
 {"name": "Dimitrovgrad", "country": "RUS", "population": 150000},
 {"name": "Lower Hutt", "country": "NZL", "population": 100000},
 {"name": "Wuhan", "country": "CHN", "population": 5000000}
]

# 使用 cursor.executemany() 執行批次查詢
cursor.executemany(sql, params)
print(f"受影響的行數：{cursor.rowcount}")

內容解密

此範例展示瞭如何使用 cursor.executemany() 方法執行批次查詢。查詢範本中使用了命名引數，使得引數的管理更加清晰。需要注意的是，executemany() 方法不支援傳回結果集。

效能比較與選擇

在選擇多查詢執行方法時，需要考慮查詢的型別、引數的複雜度以及效能需求。cmd_query_iter() 和 cursor.execute() 方法適用於執行多個不同的查詢，而 cursor.executemany() 方法則適用於執行多個相同的查詢。

Mermaid 圖表：多查詢執行方法比較

  graph TD
 A[開始] --> B{查詢型別}
 B -->|不同查詢| C[使用 cmd_query_iter() 或 cursor.execute()]
 B -->|相同查詢| D[使用 cursor.executemany()]
 C --> E[處理查詢結果]
 D --> F[執行批次查詢]
 E --> G[結束]
 F --> G

圖表剖析

此圖示展示了根據查詢型別選擇合適的多查詢執行方法的流程。對於不同的查詢，可以選擇 cmd_query_iter() 或 cursor.execute() 方法；對於相同的查詢，則可以使用 cursor.executemany() 方法。最終，根據選擇的方法執行查詢並處理結果。

MySQL Connector/Python 中的 executemany() 方法詳解

在 MySQL Connector/Python 中，executemany() 方法是一種用於執行多條 SQL 陳述式的重要工具。本章將深入探討 executemany() 的使用方法、效能優勢以及相關注意事項。

executemany() 方法的基本用法

executemany() 方法允許開發者透過單一呼叫執行多條 SQL 陳述式。這在需要批次更新或插入資料時特別有用。以下是一個簡單的例子：

# 匯入必要的函式庫
import mysql.connector

# 建立資料函式庫連線
db = mysql.connector.connect(
 option_files="my.ini"
)

# 建立 cursor 物件
cursor = db.cursor()

# 定義 SQL 陳述式範本
sql = "UPDATE world.city SET Population = %s WHERE Name = %s"

# 定義引數列表
params = [
 (5000000, 'Wuhan'),
 (1000000, 'Beijing'),
 (2000000, 'Shanghai')
]

# 執行多條 SQL 陳述式
cursor.executemany(sql, params)

# 取得受影響的行數
print("Row count:", cursor.rowcount)

# 取得最後執行的 SQL 陳述式
print("Last statement:", cursor.statement)

# 關閉 cursor 和資料函式庫連線
cursor.close()
db.close()

程式碼解析

  1. 首先，我們建立了一個資料函式庫連線和一個 cursor 物件。
  2. 定義了一個 SQL 陳述式範本，其中 %s 是引數佔位符。
  3. 準備了一個引數列表，每個元素都是一個包含具體值的元組。
  4. 呼叫 executemany() 方法執行多條 SQL 陳述式。
  5. 透過 rowcount 屬性取得受影響的行數。
  6. 透過 statement 屬性取得最後執行的 SQL 陳述式。

executemany() 與效能最佳化

使用 executemany() 方法可以顯著提高批次操作的效能，特別是在執行 INSERT 陳述式時。MySQL Connector/Python 會自動將多個 INSERT 陳述式合併為一個擴充套件插入陳述式，從而減少與伺服器的互動次數。

# 定義 SQL 陳述式範本
sql = "INSERT INTO world.t1 VALUES (%s, %s)"

# 定義引數列表
params = [
 (1, 'abc'),
 (2, 'def'),
 (3, 'ghi')
]

# 執行插入操作
cursor.executemany(sql, params)

在這個例子中，Connector/Python 會自動將多個 INSERT 陳述式合併為一個：

INSERT INTO world.t1 VALUES (1, 'abc'), (2, 'def'), (3, 'ghi')

這種擴充套件插入的方式可以大幅提升批次插入的效能。

緩衝結果集的使用

MySQL Connector/Python 支援緩衝結果集的功能。當啟用緩衝後，查詢結果會被立即擷取並儲存在客戶端，避免佔用伺服器資源。這種方式特別適合處理小型結果集。

# 建立帶有緩衝功能的 cursor
cursor = db.cursor(buffered=True)

# 執行查詢
cursor.execute("SELECT * FROM world.city WHERE CountryCode = 'AUS'")

# 處理查詢結果
for row in cursor:
 print(row)

緩衝結果集的優勢

  1. 允許在同一個連線上同時使用多個 cursor。
  2. 可以在處理結果集的同時執行新的查詢。
  3. 適合用於複雜的業務邏輯處理。

重要注意事項

  1. 使用 executemany() 時需注意 SQL 注入風險，建議使用引數化查詢。
  2. 對於非 INSERT 陳述式，executemany() 不一定能提升效能。
  3. 緩衝結果集會增加客戶端記憶體使用量。

效能比較分析

下表總結了 executemany() 與單一執行陳述式的效能差異：

  flowchart TD
 A[開始比較] --> B{是否使用executemany()}
 B -->|是| C[批次執行SQL陳述式]
 B -->|否| D[單一執行SQL陳述式]
 C --> E[效能較高]
 D --> F[效能較低]
 E --> G[適合大批次操作]
 F --> H[適合少量操作]

圖表解析

此圖展示了使用 executemany() 與單一執行陳述式之間的效能差異。當使用 executemany() 時，可以顯著提升批次操作的效能，特別是在大批次資料處理的場景下。

MySQL Connector/Python 中的緩衝遊標與預存程式應用

在 MySQL Connector/Python 中，遊標（Cursor）是執行 SQL 查詢和操作結果集的核心元件。其中，緩衝遊標（Buffered Cursor）是一種特殊的遊標，它能夠在客戶端快取查詢結果，從而提供更靈活的資料處理方式。

使用緩衝遊標更新資料列

當需要遍歷查詢結果並根據結果執行更新操作時，使用緩衝遊標可以避免因結果集被多個操作影響而導致的錯誤。以下是一個範例，展示如何使用緩衝遊標更新澳洲城市的人口資料：

from math import ceil

# 定義更新人口的 SQL 陳述式
SQL_UPDATE = """
UPDATE world.city
SET Population = %(new_population)s
WHERE ID = %(city_id)s"""

# 定義計算新人口的函式，增加10%
def new_population(old_population):
 return int(ceil(old_population * 1.10))

# 建立 MySQL 連線
db = mysql.connector.connect(option_files="my.ini")

# 建立兩個遊標：cursor1 為緩衝遊標，用於查詢；cursor2 用於更新
cursor1 = db.cursor(buffered=True, dictionary=True)
cursor2 = db.cursor()

# 查詢澳洲城市的 ID 和人口
cursor1.execute("""SELECT ID, Population FROM world.city WHERE CountryCode = %s""", params=("AUS",))

# 遍歷查詢結果，更新每座城市的人口
city = cursor1.fetchone()
while city:
 old_pop = city["Population"]
 new_pop = new_population(old_pop)
 print(f"ID: {city['ID']}, Old Population: {old_pop}, New Population: {new_pop}")

 # 執行更新操作
 cursor2.execute(SQL_UPDATE, params={"city_id": city["ID"], "new_population": new_pop})
 print(f"Statement: {cursor2.statement}")

 city = cursor1.fetchone()

# 回復變更（在實際應用中應使用提交或回復）
db.rollback()

# 關閉遊標和連線
cursor1.close()
cursor2.close()
db.close()

程式碼解析

此範例展示瞭如何使用兩個不同的遊標對同一資料函式庫進行查詢和更新操作。其中，cursor1 作為緩衝遊標，用於遍歷查詢結果；而 cursor2 則用於執行更新操作。透過這種方式，可以在遍歷結果的同時進行資料修改，而不會影響查詢結果的完整性。程式碼中還展示瞭如何使用引數化查詢來避免 SQL 注入風險。

使用 Mermaid 圖表展示流程

  flowchart TD
 A[開始處理澳洲城市資料] --> B[查詢澳洲城市列表]
 B --> C[遍歷城市資料]
 C --> D{無結果}
```mermaid
D -->|是| E[計算新人口並更新資料函式庫]
 D -->|否| F[結束處理]
 E --> C

圖表解析

此圖表描述了遍歷澳洲城市資料並更新人口的流程。首先，正在處理澳洲城市資料，接著查詢城市列表並逐一遍歷。對於每一筆城市資料，程式計算新的人口數並更新至資料函式庫。這個流程持續進行，直到所有城市資料都被處理完畢。

預存程式的應用

MySQL 的預存程式（Stored Procedure）是一種在資料函式庫中儲存複雜邏輯的機制，可以提高效能並簡化應用程式碼。以下是一個範例預存程式，用於查詢某國符合特定條件的城市資料：

DELIMITER $$
CREATE PROCEDURE world.min_max_cities(
 IN in_country CHAR(3),
 INOUT inout_min INT,
 OUT out_max INT
)
SQL SECURITY INVOKER
BEGIN
 SELECT MIN(Population), MAX(Population)
 INTO inout_min, out_max
 FROM world.city
 WHERE CountryCode = in_country AND Population >= inout_min;

 SELECT *
 FROM world.city
 WHERE CountryCode = in_country AND Population >= inout_min
 ORDER BY Population;

 SELECT *
 FROM world.city
 WHERE CountryCode = in_country AND Population >= inout_min
 ORDER BY Population DESC;
END$$
DELIMITER ;

程式碼解析

此預存程式接受三個引數：in_country（國家程式碼）、inout_min（最低人口數）和 out_max（用於傳回最大人口數）。程式首先查詢符合條件的城市中最小和最大的人口數，接著傳回符合條件的城市資料，分別按照人口數的升序和降序排列。這個程式展示了預存程式如何處理複雜查詢並傳回多個結果集。

呼叫預存程式

要呼叫上述預存程式，可以使用以下 Python 程式碼：

# 呼叫預存程式
cursor.callproc('world.min_max_cities', args=('AUS', 500000, 0))

# 遍歷預存程式傳回的結果集
for result in cursor.stored_results():
 print(result.fetchall())

程式碼解析

此程式碼展示瞭如何使用 callproc 方法呼叫預存程式，並透過 stored_results 方法遍歷預存程式傳回的多個結果集。預存程式的呼叫過程是同步的，意味著必須等待程式執行完成後才能繼續執行後續操作。

從資料函式庫效能最佳化的角度來看，MySQL 多查詢執行技術的選用至關重要。分析cmd_query_iter()、cursor.execute(multi=True) 和 cursor.executemany() 的特性後，可以發現，針對不同場景，它們各有千秋。cmd_query_iter() 適合需要逐條處理結果的多查詢情境；cursor.execute(multi=True) 適用於執行多個不同型別的查詢，但引數管理較為複雜；而 cursor.executemany() 則在批次處理相同查詢時展現出顯著的效能優勢，尤其在大量插入資料時，能大幅減少資料函式庫互動次數。然而，開發者仍需注意 SQL 注入風險，並根據實際情況選擇合適的引數化方式。隨著資料函式庫技術的演進，預期會有更多針對多查詢執行的最佳化策略出現，例如非同步查詢執行、更精細的結果集處理機制等。對於追求極致效能的應用，建議深入研究 MySQL 伺服器端的組態最佳化，例如調整 max_allowed_packet 引數、使用查詢快取等，並結合 Connector/Python 的最佳實踐，才能最大限度地提升資料函式庫操作效率。玄貓認為，精準地理解多查詢執行技術的優劣，並結合實際業務需求進行選型，是資料函式庫效能最佳化的關鍵一環。

玄貓

玄貓

技術愛好者，專注於分享程式開發、雲端技術與 AI 應用的心得體會。