【轉載】MySQL之臨時表和記憶體表

      今天（2013-10-17）又看了一篇很久以前的部落格文章，題目是《 How to eliminate temporary tables in MySQL 》，裡面的一些觀點和結論摘錄如下： 

• to make statement-based replication reliable is eliminate temporary tables.
• true temporary tables are created with CREATE TEMPORARY TABLE, and internal temporary tables are created internally by MySQL for sorting or processing subqueries.
• replace temporary tables with real tables in the systems by not naming tables randomly.

=========== 我是分割線 ============ 

      今天（2013-10-11）看了另外一邊部落格文章，感覺之前記錄的內容似乎有些地方說的不太對。這裡的內容來自 MySQL 官網翻譯。

MySQL 如何使用內部臨時表？ 


在一些情況下，伺服器會在處理 query 的時候建立內部臨時表。這種表有兩種存在形式： 

• 位於記憶體中，使用 MEMORY 儲存引擎（記憶體臨時表）；
• 位於磁碟上，使用 MyISAM 儲存引擎（磁碟臨時表）。

      伺服器可能在最初建立的是記憶體臨時表，之後當其變大到一定程度時再轉變為磁碟臨時表。對於伺服器何時建立內部臨時表或者臨時表使用哪種儲存引擎，使用者沒有直接控制的能力。 

【伺服器可能會建立臨時表的場景】 
1）ORDER BY 子句和 GROUP BY 子句不同， 
例如：ORDERY BY price GROUP BY name；

2）在 JOIN 查詢中，ORDER BY 或者 GROUP BY 使用了不是第一個表的列 
例如：SELECT * from TableA, TableB ORDER BY TableA.price GROUP by TableB.name

3）ORDER BY 中使用了 DISTINCT 關鍵字 
ORDERY BY DISTINCT(price)

4）SELECT 語句中指定了 SQL_SMALL_RESULT 關鍵字 
SQL_SMALL_RESULT 的意思就是告訴 MySQL，結果會很小，請直接使用記憶體臨時表，不需要使用索引排序 
SQL_SMALL_RESULT 必須和 GROUP BY、DISTINCT 或 DISTINCTROW 一起使用 
一般情況下，我們沒有必要使用這個選項，讓 MySQL 伺服器選擇即可。

5）由 FROM 語句中的子查詢產生的派生表 
6）由於子查詢或者  semi-join   materialization 所建立的表 

【直接使用磁碟臨時表的場景】 

• 表包含 TEXT 或者 BLOB 列；
• GROUP BY 或者 DISTINCT 子句中包含長度大於 512 位元組的列；
• 使用 UNION 或者 UNION ALL 時，SELECT 子句中包含大於 512 位元組的列；

【臨時表相關配置】 

• tmp_table_size: 指定系統建立的記憶體臨時表最大大小；
• max_heap_table_size: 指定使用者建立的記憶體表的最大大小；

注意：最終的系統建立的記憶體臨時表大小是取上述兩個配置值的最小值。 

      伺服器可能在最初建立的是記憶體臨時表，之後當其變大到一定程度時再轉變為磁碟臨時表。這種表與通過 CREATE TABLE 顯式 建立的 MEMORY 記憶體表是不同的：對於後者，系統變數  max_heap_table_size 決定的是 MEMORY 記憶體表允許的最大大小，而不會出現向磁碟錶轉變的動作。 

      當伺服器建立了內部臨時表後（無論是在記憶體中還是在磁碟上），狀態變數  Created_tmp_tables 都會增加。如果伺服器建立了臨時表在磁碟上（無論是初始建立在磁碟還是後來轉化到磁碟）， 狀態變數  Created_tmp_disk_tables 的值都會增加。 


=========== 我是分割線 ============ 

mysql 複製和臨時表 temporary table 

當你建立臨時表的時候，你可以使用 temporary 關鍵字。如： 

      臨時表只在當前連線可見，當這個連線關閉的時候，會自動 drop 。這就意味著你可以在兩個不同的連線裡使用相同的臨時表名，並且相互不會衝突，或者使用已經存在的表，但不是臨時表的表名。（當這個臨時表存在的時候，存在的表被隱藏了，如果臨時表被 drop，存在的表就可見了）。建立臨時表你必須有 create temporary table 許可權。  

下面幾點是臨時表的限制： 

• 臨時表只能用在 memory、myisam、merge 或者 innodb 儲存引擎中；
• 臨時表不支援 mysql cluster（簇）；
• 在同一個 query 語句中，你只能查詢一次臨時表。例如：下面的就不可用；
  
  ?

  1 2	mysql> SELECT * FROM temp_table, temp_table AS t2; ERROR 1137: Can`t reopen table: `temp_table`

• 如果在一個儲存函式裡，你用不同的別名查詢一個臨時表多次，或者在這個儲存函式裡用不同的語句查詢，這個錯誤都會發生；
• show tables 語句不會列舉臨時表；
• 你不能用 rename 來重新命名一個臨時表。但是，你可以 alter table 代替：
  
  ?

  1	mysql>ALTER TABLE orig_name RENAME new_name;

在資料庫複製中使用臨時表也有問題，詳情參看 Section 16.4.1, “Replication Features and Issues”。 

複製和臨時表 

為了避免出現臨時表複製問題，當臨時表開啟時，不要直接停止 slave 服務。而是用下面的步驟代替： 

1. 使用 stop slave sql_thread 語句；
2. 使用 show status 檢視 Slave_open_temp_tables 的值；
3. 如果這個值不是 0 ，使用 start slave sql_thread 重啟從庫 SQL 執行緒，一會兒後再重複執行這個步驟；
4. 當這個值是 0 時，使用 mysqladmin shutdown 命令停止 slave 。

       預設，所有的臨時表都是被複制的，無論是否匹配 –replicate-do-db、–replicate-do-table 或者 –replicate-wild-do-table，複製臨時表都會發生。但是，–replicate-ignore-table 和 –replicate-wild-ignore-table 兩個選項是用來忽略臨時表的。 
      如果你不想複製某些臨時表，請使用 –replicate-wild-ignore-table 選項。如 –replicate-wild-ignore-table=foo%.bar% 意思是告訴 slave 執行緒不要複製匹配以 foo 開頭和以 bar 開頭的表。 

下面是轉自網上某人的配置資訊： 

master端配置：

slave端配置： 

=========== 我是分割線 ============ 

基於mysql主從複製測試對臨時表和記憶體表的支援 


臨時表測試：

1. 在主伺服器上 
a. 建立臨時表 tmp1 

b. 插入資料 

c. 檢視資料 

在從伺服器上檢視： 
1） 從伺服器連線主伺服器的狀態是否正常；

2）檢視是否同步了臨時表： 

2. 在主伺服器上刪除 tmp1： 

從伺服器沒有報錯！！！ 


記憶體表測試：

主伺服器上操作： 
a. 建立記憶體表 tmp_test2 

b. 插入資料 

c. 檢視資料 

在從伺服器上檢視狀態： 
1） 從伺服器連線主伺服器狀態是否正常 

2）檢視資料 

2. 在主伺服器上刪除 tmp_test2 表： 

之後會發現，從服務上 tmp_test2 表也刪除了，從伺服器連線主伺服器狀態正常。 

注：以上實驗不能說明臨時表不會在主從之間被複制、記憶體表會在主從之間複製。因為臨時表在主從之間是否會被複制有一些前提條件要約束的！ 


=========== 我是分割線 ============ 

臨時表在MySQL的複製中的處理 

背景：在應用過程中，同事直接關閉了作為 slave 的 MySQL server 導致了臨時表問題。

      在 MySQL5.1 手冊（6.7. 複製特性和已知問題）中提到，關閉 slave 的正確流程（個人認為在 4 步驟只啟動slave_SQL 執行緒好一些）： 

1. 執行 STOP SLAVE 語句。
2. 使用 SHOW STATUS 檢查 slave_open_temp_tables 變數的值。
3. 如果值為 0，使用 mysqladmin shutdown 命令關閉從伺服器。
4. 如果值不為 0，用 START SLAVE 重啟從伺服器執行緒。
5. 後面再重複該程式看下次的運氣是否好一些。

slave_open_temp_tables 的值顯示，當前 slave 建立了多少臨時表。

提出幾個問題： 

• 關掉slave的mysqld, 那臨時表肯定是不存在了，這樣再次start slave，slave_sql 執行緒執行bin-log時，肯定會出現找不臨時表的錯誤，
• 這就為什麼手冊中會提出以上操作流程了，這個問題容易理解。
• 眾所周知，MySQL臨時表只是當前connection有效(沒有全域性臨時表），當connection斷開，此臨時也就會被刪除，也就不存在了。
• MySQL 5.1的replication，slave的sql執行緒只有一個，那stop slave後，slave_sql_thread也就停止了，那在Slave上建立的臨時表應該隨之刪除，
• 但從上面步驟來看，說明Stop slave後, 臨時表還是存在的，這是為什麼呢？
• 如果Slave不停止，那由slave建立的臨時是如何正常刪除的？ 它們在slave上的儲存形式又是怎麼樣的？

以下簡單分析一下 2，3 問題。 
分析： 
1，臨時表只對當前會話可見，連線斷開時，自動刪除！ 
2，檢視臨時表，在Master的binlog中的記錄形式：

2.1 MySQL 對臨時表的複製，如果在 mixed 的 binlog_format 情況下，會以 Statement 的形式記錄到 binlog中，當然也可以用 Row 形式 ，因為臨時表是基於 Session 的（也可以說是 Connection 的），所以在複製中，MySQL 會把執行緒 ID 新增到臨時表操作的事件中 ，此時的臨時表是屬於某個正在執行的 Thread 。 通過 mysqlbinlog 來檢視 binlog ，可以看到事件上繫結了 thread_id=297 就是這個臨時表的 宿主執行緒，當然你也可以用 Show processlist; 來檢視這個執行緒。 

2.2 從 Master 的 binlog 可以看到，有一個 SET @@session.pseudo_thread_id=297，這個記錄 salve 的Sql_thread 在執行此 binlog 時， 會建立一個 id 號為 297 的”偽執行緒”, 這樣在 slave 上建立的此臨時表 cache2的宿主執行緒就此偽執行緒。

2.3 當 stop slave 後，Slave_SQL 執行緒已經關閉，但此時在 Slave 的臨時表是還存在的，可以通過在 Slave 上檢視 Status 變數 Slave_open_temp_tables，其實是不為 0 的，也就說由 Master 複製來的臨時表還存在，因為這些臨時表是所屬於 Master 上建立臨時表的 Thread 的 Thread_ID 對應的 pseudo_thread，所以雖然Slave_SQL connection 已經斷開，但臨時表是還存在的。 

對於問題 2， 為何 slave sql thread 停掉後，臨時表還存在的原因。

3, Slave 中的臨時是如何刪除的呢？ 
      當在 Master 上的、建立此臨時表的 Session 斷開後，binlog 會記錄一個 Drop 臨時表的事件， 這樣 Slave 對應的臨時表也就被刪除了，可以檢視臨時的狀態變數可得。從下面可以看，在我測試環境中 Master 上 thread_id=297 的這個 connection，一共建立了 3 個臨時表，當退出 mysql 後，Master 的 binlog 中會記錄一個 Drop temporary table 的事件。  

當 slave 的 slave_sql_thread 執行此事件，也就把剛才建立的臨時表刪除了。

4, Slave 建立的臨時表放在哪裡呢？ 
      MySQL 建立的臨時表的檔案，其實是放在 show variables like `tmp_dir` 這個變數指定的目錄下。 預設情況是下在 /tmp 目錄下。 

同時也會在slave上的/tmp目錄下找到 

也可以根據mysqld開啟的檔案來檢視。

5，關於臨時表有兩個問題： 
5.1 在重新啟動 Slave 的 mysqld 服務時，Stop Slave 後，一定要檢查 Slave_open_temp_tables 這個狀態值是否已經是 0，如果不是， 要重新 start slave, 再 stop slave，檢視，直接是 0 後，才 stop mysql 。因為 mysql 重新啟動後，在 Slave 上的所有臨時表都沒有了，這樣重新進行復制時， 後面還有對臨時表的操作的 binlog 事件，因為 Slave 上的臨時表已不存在，此時肯定會出錯了。 
5.2 在用 binlog 進行 point_in_time 恢復資料庫時，一定要注意，把所有的 binlog 放在同一個 session 裡面執行，否則可能導致臨時表操作失敗。