我的數據庫中有一個大問題INSERT...SELECT。對於上下文，目標表已經有大約 100 萬個條目，我的SELECT查詢返回大約 200 萬個。在生產伺服器上，查詢在 20 分鐘內執行。

第一個問題：考慮到基數的大小，它是慢還是平均？

第二個問題（如果慢）：這裡的要求，有沒有改進的餘地（我認為有）？我正在考慮使用分塊，但我不確定如何。

INSERT INTO output 
(id_people, id_product) 

SELECT p.id_people, 146 
FROM people p 
INNER JOIN sell s
 ON (s.id_sell= p.id_sell AND s.status <> 'CLOSED' AND s.date < '2017-08-09 13:29:12.46') 
INNER JOIN group g
 ON (g.id_group = s.id_group AND (g.date_cancel IS NULL OR g.date_cancel > '2017-08-09 13:29:12.46'))
INNER JOIN contract c 
 ON (c.id_contract = p.id_contract AND c.date_effect < '2017-08-09 13:29:12.46' AND (c.date_end IS NULL OR c.date_end > '2017-08-09 13:29:12.46') AND c.status <> 'CANCELED' AND (c.date_cancel IS NULL OR c.date_cancel > '2017-08-09 13:29:12.46')) 
INNER JOIN company co
 ON (co.id_company = c.id_company)

WHERE g.type = 'OD' 
AND g.start < '2017-08-09 13:29:12.46' 
AND g.end > '2018-08-09 13:29:12.46'

ON DUPLICATE KEY UPDATE 

任何提示都會非常受歡迎:)

您可能沒有意識到這一點，但您知道 MySQL 為每個附加的 JOIN 子句分配一個不同的連接緩衝區嗎？

根據join_buffer_size的 MySQL 文件：

用於普通索引掃描、範圍索引掃描和不使用索引並因此執行全表掃描的連接的緩衝區的最小大小。通常，獲得快速連接的最佳方法是添加索引。當無法添加索引時，增加 join_buffer_size 的值以獲得更快的完全連接。為兩個表之間的每個完全連接分配一個連接緩衝區。對於幾個不使用索引的表之間的複雜連接，可能需要多個連接緩衝區。

除非使用批量密鑰訪問 (BKA)，否則將緩衝區設置為大於容納每個匹配行所需的大小並沒有任何好處，並且所有連接都至少分配最小大小，因此在全域將此變數設置為較大的值時要小心。最好保持全域設置較小，僅在進行大型連接的會話中更改為較大的設置。如果全域大小大於大多數使用它的查詢所需的記憶體分配時間，則記憶體分配時間會導致性能大幅下降。

使用 BKA 時，join_buffer_size 的值定義了對儲存引擎的每個請求中的密鑰批大小。緩衝區越大，對連接操作右側表的順序訪問就越多，這可以顯著提高性能。

預設值為 256KB。join_buffer_size 的最大允許設置為 4GB-1。64 位平台允許使用更大的值（64 位 Windows 除外，其大值會被截斷為 4GB-1 並發出警告）。

有關連接緩衝的更多資訊，請參閱第 8.2.1.6 節，“嵌套循環連接算法”。有關批量密鑰訪問的資訊，請參閱第 8.2.1.11 節，“阻止嵌套循環和批量密鑰訪問連接”。

這也在Block Nested-Loop Join Algorithm中聲明

MySQL 連接緩衝具有以下特點：

當連接類型為 ALL 或索引（換句話說，當沒有可能的鍵可用，並且分別對數據行或索引行進行完整掃描）或範圍時，可以使用連接緩衝。緩衝的使用也適用於外連接，如第 8.2.1.11 節“阻止嵌套循環和批量鍵訪問連接”中所述。

• 永遠不會為第一個非常量表分配連接緩衝區，即使它是 ALL 或索引類型。
• 只有對連接感興趣的列儲存在其連接緩衝區中，而不是整行。
• join_buffer_size 系統變數確定用於處理查詢的每個連接緩衝區的大小。
• 為每個可以緩衝的連接分配一個緩衝區，因此可以使用多個連接緩衝區處理給定的查詢。
• 連接緩衝區在執行連接之前分配，並在查詢完成後釋放。

基於此，您必須在執行INSERT ... SELECT. 從連接ON子句的外觀來看，我沒有預見到查詢優化器會選擇索引。

我不能告訴你join_buffer_size有多大。您將不得不對其進行試驗並執行EXPLAIN計劃以查看是否SELECT會避免寫入磁碟。

警告：根據MySQL 文件Nested-Loop Join Algorithms的最後一段：

t3 掃描的次數隨著 join_buffer_size 值的增加而減少，直到 join_buffer_size 大到足以容納所有先前的行組合。此時，將其變大並不會獲得速度。

這就是為什麼你應該嘗試join_buffer_size的原因。

如何實驗join_buffer_size

您可以一次將join_buffer_size更改為1 MB。然後，執行SELECT併計時。

STEP 01：設置初始大小為 0，初始執行時間為 48 小時（172800 秒）

SET @jbsize = 0;
SET @prev_runtime = 172800;

STEP 02 : 增加 1MB

SET @jbsize = jbsize + 1;
SET SESSION join_buffer_size = @jbsize * 1048576;

STEP 03 : 計時SELECT

SET @t1 = UNIX_TIMESTAMP();
SELECT p.id_people, 146 
FROM people p 
INNER JOIN sell s
 ON (s.id_sell= p.id_sell AND s.status <> 'CLOSED' AND s.date < '2017-08-09 13:29:12.46') 
INNER JOIN group g
 ON (g.id_group = s.id_group AND (g.date_cancel IS NULL OR g.date_cancel > '2017-08-09 13:29:12.46'))
INNER JOIN contract c 
 ON (c.id_contract = p.id_contract AND c.date_effect < '2017-08-09 13:29:12.46' AND (c.date_end IS NULL OR c.date_end > '2017-08-09 13:29:12.46') AND c.status <> 'CANCELED' AND (c.date_cancel IS NULL OR c.date_cancel > '2017-08-09 13:29:12.46')) 
INNER JOIN company co
 ON (co.id_company = c.id_company)
WHERE g.type = 'OD' 
AND g.start < '2017-08-09 13:29:12.46' 
AND g.end > '2018-08-09 13:29:12.46'
SET @t2 = UNIX_TIMESTAMP();

步驟 04：計算時間

SET @curr_runtime = @t2 - @t1;
SET @diff_runtime= @prev_runtime - @curr_runtime;
SELECT
   sec_to_time(@prev_runtime)     Previous_Running_Time,
   sec_to_time(@curr_runtime)      Current_Running_Time,
   sec_to_time(@diff_runtime) Improvement_From_Last_Run
;

步驟 05：決策時間

如果Improvement_From_Last_Run在小時、分鐘和秒方面很大，並且您覺得讓join_buffer_size更大會改善執行時間，則循環回STEP 02. 繼續這樣做直到Improvement_From_Last_Runis 00:00:00。

試一試 ！！！

group:  INDEX(type, date_cancel)
sell:   INDEX(id_group)
people: INDEX(id_sell)
company -- remove from the query since it seems to be unused.

考慮擺脫冗餘資訊，例如status: CANCELED和date_cancel。

引用自：https://dba.stackexchange.com/questions/183178