問題:
在 實驗 05 中,我們看到了內部臨時表會使用到不少內存。
那麼如果需要的臨時表再大一些,必然要使用到磁盤來承載,那麼內部臨時表是何時使用磁盤的?
實驗:
我們仍使用 實驗 05 中的環境,略去準備數據的過程。
我們仍然使用兩個會話,一個會話 run,用於運行主 SQL;另一個會話 ps,用於進行 performance_schema 的觀察:
主會話線程號為 29,
將 performance_schema 中的統計量重置,
臨時表的表大小限制取決於參數 tmp_table_size 和 max_heap_table_size 中較小者,我們實驗中以設置 max_heap_table_size 為例。
我們將會話級別的臨時表大小設置為 2M(小於上次實驗中臨時表使用的空間),執行使用臨時表的 SQL:
查看內存的分配記錄:
會發現內存分配略大於 2M,我們猜測臨時表會比配置略多一點消耗,可以忽略。
查看語句的特徵值:
可以看到語句使用了一次需要落磁盤的臨時表。
那麼這張臨時表用了多少的磁盤呢?
我們開啟 performance_schema 中 waits 相關的統計項:
重做實驗,略過。
再查看 performance_schema 的統計值:
可以看到幾個現象:
1. 臨時表空間被寫入了 7.92MiB 的數據。
2. 這些數據是語句寫入後,慢慢逐漸寫入的。
來看看這些寫入操作的特徵,該方法我們在 實驗 03 使用過:
可以看到寫入的線程是 page_clean_thread,是一個刷髒操作,這樣就能理解
數據為什麼是慢慢寫入的。也可以看到每個 IO 操作的大小是 16K,也就是刷數據頁的操作。
結論:
我們可以看到,
1. MySQL 會基本遵守 max_heap_table_size 的設定,在內存不夠用時,直接將錶轉到磁盤上存儲。
2. 由於引擎不同(內存中表引擎為 heap,磁盤中表引擎則跟隨 internal_tmp_disk_storage_engine 的配置),本次實驗寫磁盤的數據量和 實驗 05 中使用內存的數據量不同。
3. 如果臨時表要使用磁盤,表引擎配置為 InnoDB,那麼即使臨時表在一個時間很短的 SQL 中使用,且使用後即釋放,釋放後也會刷髒頁到磁盤中,消耗部分 IO。