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

摩雲飛發表於2016-05-11
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 關鍵字。如： 

?
1
create temporary table tmp_table(name varchar(10) not null,passwd char(6) not null)

      臨時表只在當前連線可見，當這個連線關閉的時候，會自動 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端配置：

?
1
2
3
4
5
6
7
8
9
[mysqld]  
  #Master start  
  #日誌輸出地址 主要同步使用  
  log-bin=/var/log/mysql/updatelog
  #同步資料庫  
  binlog-do-db=cnb 
  #主機id 不能和從機id重複  
  server-id=1   
  #Master end

slave端配置： 

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
[mysqld]   
  #Slave start       
  #從機id，區別於主機id   
  server-id=2  
  #主機ip，供從機連線主機用   
  master-host=192.168.0.24 
  #主機埠   
  master-port=3307  
  #剛才為從機複製主機資料新建的賬號   
  master-user=slave    
  #剛才為從機複製主機資料新建的密碼   
  master-password=123456  
  #重試間隔時間10秒   
  master-connect-retry=10    
  #需要同步的資料庫   
  replicate-do-db=cnb  
  #啟用從庫日誌，這樣可以進行鏈式複製   
  log-slave-updates   
  #從庫是否只讀，0表示可讀寫，1表示只讀   
  read-only=1  
    
  #只複製某個表   
  #replicate-do-table=tablename                    
  #只複製某些表（可用匹配符）   
  #replicate-wild-do-table=tablename%   
  #只複製某個庫   
  #replicate-do-db=dbname  
  #不復制某個表   
  #replicate-ignore-table=tablename  
  #不復制某些表   
  #replicate-wild-ignore-table=tablename%   
  #不復制某個庫   
  #replicate-ignore-db=dbname  
  #Slave end



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

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


臨時表測試：

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

?
1
create temporary table tmp1(id int not null);

b. 插入資料 

?
1
mysql> insert into tmp1(id) values(26);

c. 檢視資料 

?
1
2
3
4
5
6
7
mysql> select * from tmp1;            
+----+
| id |
+----+
| 23 |
+----+
1 row in set (0.00 sec)


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

?
1
show slave statusG

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

?
1
2
mysql> select * from tmp1;
ERROR 1146 (42S02): Table `cacti.tmp1` doesn`t exist


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

?
1
drop table tmp1;

從伺服器沒有報錯！！！ 


記憶體表測試：

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

?
1
2
3
4
CREATE TABLE tmp_test2 (
  Id int(11) AUTO_INCREMENT,
  name varchar(255)
) ENGINE=MEMORY ;

b. 插入資料 

?
1
2
insert into tmp_test2(id)
values(10);

c. 檢視資料 

?
1
2
3
4
5
6
7
mysql>select * from tmp_test2;                          
+------+------+
| id   | name |
+------+------+
|   10 | NULL |
+------+------+
1 row in set (0.00 sec)


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

?
1
show slave statusG

2）檢視資料 

?
1
2
3
4
5
6
7
mysql> select * from tmp_test2;
+------+------+
| id   | name |
+------+------+
|   10 | NULL |
+------+------+
1 row in set (0.00 sec)

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

?
1
drop table 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; 來檢視這個執行緒。 

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# at 106
#120318 1:42:30 server id 1 end_log_pos 291 Query thread_id=297 exec_time=0 error_code=0
use rep/*!*/;
SET TIMESTAMP=1332006150/*!*/;
SET @@session.pseudo_thread_id=297/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!C latin1 *//*!*/;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create temporary table cache2( id int unsigned not null, value char(10) not null default ``, primary key(id) )engine=myisam
/*!*/;
show processlist;
| 297 | root | localhost | rep | Query | 0 | NULL | 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 已經斷開，但臨時表是還存在的。 

?
1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> show status like `%slave%`;
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Slave_open_temp_tables | 3 |
| Slave_retried_transactions | 0 |
| Slave_running | ON |
+----------------------------+-------+
7 rows in set (0.00 sec)

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

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

?
1
2
3
4
5
6
7
8
#120318 1:45:53 server id 1 end_log_pos 734 Query thread_id=297 exec_time=0 error_code=0
SET TIMESTAMP=1332006353/*!*/;
/*!C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `cache3`,`cache2`,`cache`
/*!*/;
DELIMITER ;
# End of log file

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

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

?
1
2
-rw-rw---- 1 mysql mysql 98304 Mar 23 05:39 #sql2625_18_0.ibd
-rw-rw---- 1 mysql mysql 8586 Mar 23 05:39 #sql2625_18_0.frm

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

?
1
2
-rw-rw---- 1 mysql mysql 8586 Mar 24 18:28 #sqld0b_7_2.frm
-rw-rw---- 1 mysql mysql 98304 Mar 24 18:28 #sqld0b_7_2.ibd

也可以根據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 裡面執行，否則可能導致臨時表操作失敗。 


相關文章