詳解Mysql 函數調用優化

函數調用優化

MySQL函數在內部被標記為確定性或不確定性。如果給定參數固定值的函數可以為不同的調用返回不同的結果,則它是不確定的。不確定函數的示例: RAND()UUID()

如果某個函數被標記為不確定的,則將WHERE針對每一行(從一個表中選擇時)或行的組合(從多表聯接中選擇時)評估子句中對該函數的引用。

MySQL還根據參數的類型(參數是表列還是常量值)確定何時評估函數。每當表列更改值時,都必須評估將表列作為參數的確定性函數。

非確定性函數可能會影響查詢性能。例如,某些優化可能不可用,或者可能需要更多鎖定。以下討論使用 RAND()但也適用於其他不確定性函數。

假設一個表t具有以下定義:

CREATE TABLE t (id INT NOT NULL PRIMARY KEY, col_a VARCHAR(100));

考慮以下兩個查詢:

SELECT * FROM t WHERE id = POW(1,2);
SELECT * FROM t WHERE id = FLOOR(1 + RAND() * 49);

由於與主鍵的相等性比較,兩個查詢似乎都使用瞭主鍵查找,但這僅適用於第一個查詢:

  • 第一個查詢始終最多產生一行,因為POW()帶有常量參數的常量是一個常量值,並用於索引查找。
  • 第二個查詢包含一個使用非確定性函數的表達式,該表達式 RAND()在查詢中不是常量,但實際上對表的每一行都有一個新值t。因此,查詢讀取表的每一行,評估每一行的謂詞,並輸出主鍵與隨機值匹配的所有行。根據id列值和RAND()序列中的值, 它可以是零行,一行或多行 。

非確定性的影響不僅限於 SELECT陳述。該 UPDATE語句使用非確定性函數來選擇要修改的行:

UPDATE t SET col_a = some_expr WHERE id = FLOOR(1 + RAND() * 49);

大概目的是最多更新主鍵與表達式匹配的一行。但是,它可能會更新零,一或多個行,具體取決於 id列值和RAND()序列中的值 。

剛剛描述的行為對性能和復制有影響:

  • 由於不確定函數不會產生恒定值,因此優化器無法使用其他可能適用的策略,例如索引查找。結果可能是表掃描。
  • InnoDB 可能升級為范圍鍵鎖,而不是為一個匹配的行獲取單行鎖。
  • 無法確定執行的更新對於復制是不安全的。

困難源於RAND()對表的每一行都對函數進行一次評估的事實 。為瞭避免進行多功能評估,請使用以下技術之一:

  • 將包含不確定性函數的表達式移到單獨的語句,將值保存在變量中。在原始語句中,將表達式替換為對變量的引用,優化器可以將該變量視為常量值:
SET @keyval = FLOOR(1 + RAND() * 49);
UPDATE t SET col_a = some_expr WHERE id = @keyval;
  • 將隨機值分配給派生表中的變量。此技術使變量在WHERE子句中的比較中使用之前被分配一個值 :
SET optimizer_switch = 'derived_merge=off';
UPDATE t, (SELECT @keyval := FLOOR(1 + RAND() * 49)) AS dt
SET col_a = some_expr WHERE id = @keyval;

如前所述,該WHERE子句中的不確定性表達式 可能會阻止優化並導致表掃描。但是,WHERE如果其他表達式是確定性的,則可以部分優化該子句。例如:

SELECT * FROM t WHERE partial_key=5 AND some_column=RAND();

如果優化器可以partial_key用來減少所選行的集合, RAND()則執行的次數更少,這可以減少不確定性對優化的影響。

以上就是詳解Mysql 函數調用優化的詳細內容,更多關於Mysql 函數調用優化的資料請關註WalkonNet其它相關文章!