強制 InnoDB 建立二級索引

當INSERTing 時，InnoDB推遲二級索引的建構。例如，

CREATE TABLE item_creators
(
item_id int(11) unsigned NOT NULL,
creator_id int(11) unsigned NOT NULL,
INDEX(creator_id),
PRIMARY KEY(item_id)
) ENGINE=InnoDB

第一次查詢後，

INSERT INTO item_creators (item_id,creator_id)
   SELECT item_id,creator_id FROM t1;

SELECT a.item_id, b.creator FROM
   item_creators a JOIN creators ON a.creator_id=b.creator_id;

InnoDB開始INDEX(creator_id)在後台重建二級索引。如果沒有這個索引，第二次查詢會很慢。

• 首先，我們如何InnoDB在第一次查詢後強制重建二級索引？
• 其次，我們如何在執行第二個查詢之前檢查所需的索引是否已經完成？

請注意，它是關於非常大的表（數億行），其中索引建構需要時間。

我覺得你有一些誤解。當您插入一行時，InnoDB 不會重建整個索引。它只是向現有索引添加一個條目。如果在每次 INSERT 之後都必須在大表上重建索引，這對性能來說將是非常糟糕的。

另一個誤解是關於延遲索引更新。該索引在此期間仍然可用。InnoDB 知道如何檢查更改緩衝區中的條目以及索引。如果查詢從更改緩衝區中讀取值，則會立即將其合併到索引中。如果查詢讀取已合併到索引中的其他值，則索引會幫助該查詢。

您可能正在嘗試解決一個不存在的問題。

至於你問的問題，如何判斷索引是否完全合併，這樣做很棘手。您可以查詢以查看有多少緩衝池分配給了更改緩衝區（此手冊頁對此進行了說明：https://dev.mysql.com/doc/refman/8.0/en/innodb-change-buffer。 html )

SELECT (SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
      WHERE PAGE_TYPE LIKE 'IBUF%') AS change_buffer_pages,
      (SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE) AS total_pages,
      (SELECT ((change_buffer_pages/total_pages)*100))
      AS change_buffer_page_percentage;

+---------------------+-------------+-------------------------------+
| change_buffer_pages | total_pages | change_buffer_page_percentage |
+---------------------+-------------+-------------------------------+
|                2064 |        8191 |                       25.1984 |
+---------------------+-------------+-------------------------------+

這表明在我將幾百萬行插入測試表後，我的緩衝池中大約 25% 被等待合併的更改緩衝區內容佔用。但它並沒有告訴我哪個表或索引，因此可能是其他表的更改導致了這一點。

隨著時間的推移，更改緩衝區頁面會隨著索引更改的合併和其他 INSERT/UPDATE/DELETE 流量的進來而增長和縮小。

大型 INSERT 之後的 I/O 活動可能來自延遲的“更改緩衝區”活動。

這是更新非唯一二級索引的地方。

這樣想吧。當一行被更新/插入/刪除時，

• 首要任務（在事務等之後）是將數據放入表中。
• 對二級索引的更新需要對索引 BTree 塊進行讀取-更新-寫入，但是，需要完成的操作的一小部分記錄儲存在稱為“更改緩衝區”的緩衝池的一部分中。
• 在時間允許的情況下（即在後台），對這些更新進行排序（它們可能保存在“優先隊列”中，因此它們既可訪問又可排序），執行讀-更新-寫操作。
• 通過批量更新，需要發生的讀取更少。（也就是說，讀取一個 16KB 的索引塊；在寫入之前插入/刪除幾個“行”。）
• 生成的塊現在就像任何正常塊一樣，因此它作為“臟”塊等待刷新到磁碟。
• 甚至稍後，該索引塊實際上將被寫入磁碟。

所以，是的，很多 I/O 活動。 但它的存在是為了使系統更高效，並導致更少的 I/O。

我認為只有兩個設置可以控制更改緩衝區：

• buffer_pool 的百分比——96% 的使用者離開innodb_change_buffer_max_size時預設為“25”。
• 要處理哪些操作——94% 的使用者有innodb_change_buffering = all（預設）

無需“等待”它完成；所有這些都在後台透明地處理。

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