為什麼在 MySQL 中插入忽略如此昂貴？

TL;DR： 插入大量沒有衝突的行比（不）重新插入相同的行要快得多，兩者都使用語法INSERT IGNORE。

為什麼是這樣？我假設插入和“忽略”插入的索引查找成本相同，因為 MySQL 不知道傳入數據是否有重複/衝突數據（因此需要被忽略）……因此，索引發生在初始插入和忽略的插入執行中。

此外，我認為“忽略”行應該更便宜，因為它不需要任何磁碟寫入。

但這絕對不是這種情況。

長版： 在這個問題中，我們使用 AWS 的 Aurora/MySQL 和LOAD DATA FROM S3 FILE語法來刪除任何傳輸或性能變數。我們載入一個對應於下面架構的 4 兆行 CSV 文件，並載入它兩次，兩次都使用LOAD ... IGNORE.

請注意，該問題也發生在 standardINSERT ... IGNORE中，但在使用批處理行插入時。這裡的用途LOAD ... IGNORE是引導討論轉向測量結果的反直覺性質，而不是“如何執行大量被忽略的插入”。這不是這裡的問題，因為已經制定了特定於域的方法。

在被測試的模型中，有一個三層索引：前兩列是基數非常低的可列舉分類列，第三列本質上是“實際”數據。為了便於對這個問題進行限定，我只是堅持我目前的設置。

假設以下簡單模式：

我們的表：

id: basic auto-increment bigint, primary key
constkey1: varchar(50) -- this is a constant in the insert
constkey2: varchar(50) -- this is a constant in the insert
datakey:   varchar(50) -- this is pulled in from the CSV file
datafield: varchar(50) -- this is pulled in from the CSV file
consttime: datetime    -- this is a constant in the insert

Unique Index: (constkey1, constkey2, datakey)

以下查詢執行兩次：

LOAD DATA FROM S3 FILE  's3://...'
   IGNORE
   INTO TABLE our_table
   COLUMNS TERMINATED BY ','
   (datakey, datafield)
   SET constkey1 = 'some string',
       constkey2 = 'some other string',
       consttime = CURRENT_TIMESTAMP  -- so that I can verify changes

還假設our_table已經包含幾百萬行，其中 NONE與查詢中給出的NOR****都不相同。constkey1 constkey2

• 我第一次執行上面的查詢，插入4兆行需要3到5分鐘（大概基於執行時目前數據庫環境的雜亂無章），所有4兆行都被正確插入。這是一個可接受的基線。
• 我執行查詢的第二次，4 個兆行需要大量時間來（不）插入，並且正如預期的那樣，所有 4 個兆行都沒有正確更新。時間足夠長，甚至不值得在這裡排位賽（一次跑近一個小時）。

這個測試執行了很多次，總是產生相同的結果。

更新： 在修改這個問題時，SE提出了以下問題

幾百萬條記錄後，mysql插入索引表需要很長時間

雖然我認為它在這裡並不完全通用，但也許它與索引範圍的記憶體大小有關？在這裡的範例中，執行 1 中的“子索引”大小('some string', 'some other string', * )為零，而執行 2 中充滿 4 個兆條目。也許這是一個問題？

另請注意，最終解決方案採用以下問題中給出的形式。這對手頭的問題無關緊要，因為我想知道為什麼原始解決方案不起作用。這只是給那些好奇的人。

我可以辨識大型 MySQL 插入中的重複項嗎？

從原始碼中可以看出，當向 InnoDB 表中插入一行時，它首先被添加到聚集索引中，然後依次添加到所有二級索引中。在您的情況下，聚集索引基於自動增量列，因此第一個操作始終成功。由於重複的鍵值，對二級唯一索引的下一個操作失敗。然後引擎繼續回滾先前的操作，從而導致不必要的性能損失。

邏輯顯然針對“快樂路徑”進行了優化——非衝突插入，代價是次優異常路徑。如果他們在應用任何更改之前驗證約束，衝突解決成本會降低，但“快樂路徑”會變得更加昂貴，讓更多使用者不快樂。

你是一個 AWS Aurora 實例，問題可能會變得更糟，因為這兩個更改都需要寫入更改日誌的仲裁以復製到輔助節點，以及它可能對快速的負面影響插入加速。

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