【轉載】MySQL Temporary Table 相關問題的探究

【問題的引入】

讓我們先來觀察幾條非常簡單的 mysql 語句：

<code>mysql></code><code>create</code> <code>temporary</code> <code>table</code> <code>tmp(id</code><code>int</code><code>, data</code><code>char</code><code>(20));</code>

<code>query ok, 0</code><code>rows</code> <code>affected (0.01 sec)</code>

<code>mysql></code><code>create</code> <code>table</code> <code>tmp(id</code><code>int</code><code>, data</code><code>char</code><code>(20));</code>

<code>mysql></code><code>drop</code> <code>table</code> <code>tmp;</code>

<code>query ok, 0</code><code>rows</code> <code>affected (0.00 sec)</code>

<code>error 1051 (42s02): unknown</code><code>table</code> <code>'test.tmp'</code>

<code>mysql></code>

這是丁奇提出的引導性的問題，幾條語句看似簡單，不過接下來我們提出的一連串問題與進行的研究可都是圍繞它們來的！

看到以上語句，你很容易會産生類似于以下的疑問：

上述語句在一個 session 中先後建立了兩個名為 'tmp' 的 table ，隻不過一個是 temporary table ，一個是 normal table 。問題來了：temporary table 為何可以與同名的 normal table 共存？

上述語句成功執行了兩條 drop table 語句，那麼每一條語句操作的對象是哪個 table 呢？亦即同名的 temporary table 與 normal table 之間的優先級關系是如何的？

很好，有了問題就知道了前進的方向！接下來我們就從這兩個問題入手，由淺入深，開始我們的探索之旅吧！

【單機模式下的同名問題與優先級問題的探究】

我們不妨從現象入手，先來驗證第二個問題的結果究竟如何，即哪個表擁有較高的優先級？

為此我們設計如下的語句：

<code>mysql></code><code>create</code> <code>temporary</code> <code>table</code> <code>tmp(id1</code><code>int</code><code>, data1</code><code>char</code><code>(20));</code>

<code>mysql> describe tmp;</code>

<code>mysql></code><code>insert</code> <code>into</code> <code>tmp</code><code>values</code><code>(1,</code><code>"some"</code><code>);</code>

<code>query ok, 1 row affected (0.00 sec)</code>

<code>mysql></code><code>select</code> <code>*</code><code>from</code> <code>tmp;</code>

<code>mysql></code><code>create</code> <code>table</code> <code>tmp(id2</code><code>int</code><code>, data2</code><code>char</code><code>(20));</code>

<code>mysql></code><code>insert</code> <code>into</code> <code>tmp</code><code>values</code><code>(2,</code><code>"some"</code><code>);</code>

以上語句做的工作很簡單：先建立一個名為 'tmp' 的 temporary table ，并 insert 一個值；之後建立一個名為 'tmp' 的 normal table ，也 insert 一個值。最終 select 時發現，兩次 insert 操作均作用于 temporary table 。

至此我們可以得到初步的印象是，同名的 temporary table 與 normal table 共存時， temporary table 具有較高的優先級。但是别忘了還存在另一種情況：先建立的表總有着較高的優先級。這個猜想是很容易來驗證它的對錯的，我們隻需将剛才的建立表的順序調換一下即可。這裡就不再重複代碼，直接給出結果：即使 temporary table 在 normal table 之後建立，諸如 select，insert，update 等操作仍然優先作用于 temporary table 之上。于是我們可以進一步猜測 drop 表的時候，先 drop 的也是 temporary table 。馬上來驗證一下：

/* 緊接着之前的代碼 */

<code>empty</code><code>set</code> <code>(0.01 sec)</code>

<code>mysql> show tables;</code>

<code>empty</code><code>set</code> <code>(0.00 sec)</code>

<code>error 1146 (42s02):</code><code>table</code> <code>'test.tmp'</code> <code>doesn't exist</code>

沒問題吧！到這裡我們已經從現象得出了初步的結論：在同一個 session 下同名的 temporary table 與 normal table 共存時，temporary table 總是優先被操作的。那麼我們可以更進一步提問：為什麼 temporary table 的優先級會高于normal table 呢？

而且别忘了在本段開始時我們還提出了一個問題：為什麼在同一 session 下同名的 temporary table 與 normal table 可以共存？衆所周知兩個同名的 temporary table 或 normal table 都是不被允許的。我們可以先做出猜想：temporary table 與normal table 是存儲在不同的位置的。這個猜想對嗎？要回答這些問題，我們必須到 mysql 的源碼中一探究竟，找尋答案了！

我們可以從建立一張表的流程入手，來探究這個過程(以下代碼中，如果沒有特别注明，其注釋均為原碼注釋。)。對于語句

<code>create</code> <code>temporary</code> <code>table</code> <code>tmp(id</code><code>int</code><code>, data</code><code>char</code><code>(20));</code>

<code>create</code> <code>table</code> <code>tmp(id</code><code>int</code><code>, data</code><code>char</code><code>(20));</code>

定位到 ./sql/sql_parse.cc 中的 mysql_execute_command() 函數。

<code>case</code> <code>sqlcom_create_table:</code>

<code> </code><code>if</code> <code>((res= create_table_precheck(thd, select_tables, create_table)))</code>

<code> </code><code>goto</code> <code>end_with_restore_list;</code>

<code> </code><code>/* regular create */</code>

<code> </code><code>if</code> <code>(create_info.options & ha_lex_create_table_like)</code>

<code> </code><code>res= mysql_create_like_table(thd, create_table, select_tables,</code>

<code> </code><code>&create_info);</code>

<code> </code><code>res= mysql_create_table(thd, create_table->db,</code>

<code> </code><code>create_table->table_name, &create_info,</code>

<code> </code><code>&alter_info, 0, 0);</code>

首先我們檢視同檔案中 create_table_precheck() 函數的實作：

<code> </code><code>for temporary tables we don't have to check if the created table exists</code>

<code> </code><code>if</code> <code>(!(lex->create_info.options & ha_lex_create_tmp_table) &&</code>

<code> </code><code>find_table_in_global_list(tables, create_table->db,</code>

<code> </code><code>create_table->table_name))</code>

<code> </code><code>error= false;</code>

而 find_table_in_global_list() 函數實質上調用了 ./sql/sql_base.cc 檔案中的 find_table_in_list() 函數。這個函數的功能就是去記憶體中的全局 table list 中周遊，确認是否已有同名的 normal table 存在。注意，對于 temporary table ，到這裡為止是不做重名檢查的。

繼續跟蹤到 ./sql/sql_talbe.cc 中的 mysql_create_table() 函數。開頭的注釋說的很清楚：

<code> </code><code>database and name-locking aware wrapper for mysql_create_table_no_lock(),</code>

這個函數實際上是對 mysql_create_table_no_lock() 的一個封裝，并且處理了一些加鎖機制。我們繼續跟蹤到同檔案的 mysql_create_table_no_lock() 函數。

<code> </code><code>/* check if table exists */</code>

<code> </code><code>if</code> <code>(create_info->options & ha_lex_create_tmp_table)</code>

<code> </code><code>path_length= build_tmptable_filename(thd, path,</code><code>sizeof</code><code>(path));</code>

<code> </code><code>create_info->table_options|=ha_create_delay_key_write;</code>

<code> </code><code>path_length= build_table_filename(path,</code><code>sizeof</code><code>(path) - 1, db, alias, reg_ext,</code>

<code> </code><code>internal_tmp_table ? fn_is_tmp : 0);</code>

這裡我們看到了一個關鍵函數 build_tmptable_filename() ，它位于 ./sql/sql_table.cc 檔案中，這個函數是為 temporary table 命名的。在該函數内部我們又看到如下一段關鍵代碼：

<code> </code><code>my_snprintf(p, bufflen - (p - buff),</code><code>"/%s%lx_%lx_%x%s"</code><code>,</code>

<code> </code><code>tmp_file_prefix, current_pid,</code>

<code> </code><code>thd->thread_id, thd->tmp_table++, reg_ext);</code>

有了以上這段代碼，temporary table 的命名規則就非常清楚了，其中 current_pid 為 16 進制形式，thd->thread_id 是 client 的線程式号，thd->tmp_table 就是臨時表序号了，而 reg_ext 就是形如 *.frm 這樣的字尾。

現在我們回到函數 mysql_create_table_no_lock() ，緊接着剛才的代碼：

<code>/* check if table already exists */</code>

<code> </code><code>if</code> <code>((create_info->options & ha_lex_create_tmp_table) &&</code>

<code> </code><code>find_temporary_table(thd, db, table_name))</code>

<code> </code><code>// 如果找到重名的表，那麼執行這裡的錯誤處理代碼(非原注釋)</code>

在上面這段代碼中我們又看到了一個關鍵函數 find_temporary_table() ，這個函數内部是大有文章的，它會去tmp_table list 中去周遊并檢查 temporary table 是否已經存在。如果一切沒有問題，那麼繼續往下執行：

<code> </code><code>if</code> <code>(rea_create_table(thd, path, db, table_name,</code>

<code> </code><code>create_info, alter_info->create_list,</code>

<code> </code><code>key_count, key_info_buffer, file))</code>

這裡我們可以看到 rea_create_table() 函數的功能是建立 normal table 的實際資料檔案。

<code> </code><code>/* open table and put in temporary table list */</code>

<code> </code><code>if</code> <code>(!(open_temporary_table(thd, path, db, table_name, 1)))</code>

<code> </code><code>(</code><code>void</code><code>) rm_temporary_table(create_info->db_type, path);</code>

<code> </code><code>goto</code> <code>unlock_and_end;</code>

<code> </code><code>thd->thread_specific_used= true;</code>

上面這段代碼是對 temporary table 操作的，其中 open_temporary_table() 函數打開一個 temporary table 并将其加入 thd->temporary_table 隊列。繼續往下，在函數末尾看到一句代碼：

<code>error= write_create_table_bin_log(thd, create_info, internal_tmp_table);</code>

進入 write_create_table_bin_log() 函數，上來就是一段非常清晰的注釋：

<code> </code><code>don't write statement if:</code>

<code> </code><code>- it is an internal temporary table,</code>

<code> </code><code>- row-based logging is used and it we are creating a temporary table, or</code>

<code> </code><code>- the binary log is not open.</code>

<code> </code><code>otherwise, the statement shall be binlogged.</code>

已經說得很明白了，如果是内部建立的 temporary table 或者 row-based binlog 模式下建立 temporary table 或者 binlog 功能未開啟，那麼不寫 binlog ，其他情況下都會寫。

至此，mysql 一個典型的建立表的流程就走完了。總結上述代碼，我們可以回答第一個問題，也就是同名 normal table 與 temporary table 共存問題。現在我們知道，normal table 與 temporary table 儲存的位置是不同的，temporary table 儲存在 thd->temporary_table 隊列中，而 normal table 是儲存在全局的隊列中的，這樣同名的 normal table 與temporary table 就可以共存。并且，temporary table 是相對于 session 的，因為 session 結束後相應的線程就被回收了，那麼對應于該線程的 temporary table 也就被釋放了。更進一步，從 temporary table 的命名規則我們可以看到，每個temporary table 都對應着獨特的用戶端線程 id ，那麼顯然各個 client 之間同名的 temporary table 是允許共存的。而normal table 顯然是在任何情況下都不允許同。

為了回答第二個問題，即優先級問題，我們隻需要看一下 drop 一個表的過程即可，其他操作的原理也是類似的。這裡我們就不再像剛才那麼詳細的一步步分析源碼，直接給出關鍵代碼(位于函數 mysql_rm_table_part2() 中，該函數位于 ./sql/sql_table.cc)

<code> </code><code>error= drop_temporary_table(thd, table);</code><code>// 這裡删除臨時表(非原注釋)</code>

<code> </code><code>error= ha_delete_table(thd, table_type, path, db, table->table_name,</code>

<code> </code><code>!dont_log_query);</code><code>// 這裡删除表的内容和索引(非原注釋)</code>

<code> </code><code>/* delete the table definition file */</code>

<code> </code><code>strmov(end,reg_ext);</code>

<code> </code><code>// 以下删除表的定義檔案(非原注釋)</code>

<code> </code><code>if</code> <code>(!(new_error=my_delete(path,myf(my_wme))))</code>

<code> </code><code>some_tables_deleted=1;</code>

<code> </code><code>new_error= table_triggers_list::drop_all_triggers(thd, db,</code>

<code> </code><code>table->table_name);</code>

從以上代碼我們不難看出，drop 表的過程總是先走 temporary table ，再走 normal table 的。這也就解釋了為何 temporary table 有着比 normal table 更高的優先權。

好了，到目前為止我們已經從本質上回答了文章開頭提出的兩個問題，這樣看起來問題已經解決的比較圓滿了。但是且慢，我們以上所做的探究全部基于同一台伺服器下，如果是分布式的系統，即主從模式下，又會出現什麼樣的狀況呢？下面一節我們繼續探究。

【主從模式下 temporary table 機制的探究】

注意到 slave 方執行事件的線程隻有一個，那就是 slave sql 線程。想一想按照我們目前的理解，會出現怎樣的問題？回憶剛才的 mysql temporary table 命名規則，其中有一項是線程 id 。再回憶剛才我們說到，由于 temporary table是相對于 session 的，于是不同的 client 可以建立同名的 temporary table 。問題來了：将這個情景移到主從模式下，master 方同時連接了兩個 client ，每一個 client 各自建立了一個名為 a 的 temporary table 。我們假設此時 master 的binlog 模式被設定為 statement-based ，那麼這兩個建表事件都會被寫入 binlog 。現在 slave i/o 線程檢測并讀取了這兩個事件，slave sql 線程要執行這兩個事件了。按照我們的想法，此時 slave 是不能區分這兩個 temporary table 的，因為線程 id 相同！

但是經過實際驗證，mysql 能處理這個問題，而并沒有像我們預想的那樣會報錯。那麼 mysql 内部是如何處理的呢？讓我們再仔細讀一下建表函數 mysql_create_table_no_lock() 中的檢查 temporary table 名字沖突的函數find_temporary_table() 的實作代碼。

<code> </code><code>key_length= create_table_def_key(thd, key, table_list, 1);</code>

顯然 create_table_def_key() 函數是區分每個 temporary table 的關鍵，我們繼續看這個函數内部的細節：

<code> </code><code>int4store(key + key_length + 4, thd->variables.pseudo_thread_id);</code>

這裡我們看到一個關鍵資訊：thd->variables.pseudo_thread_id 。如果使用 gdb 調試，我們發現在find_temporary_table() 函數中 thd->variables.pseudo_thread_id 的值等于 relay-log 中的線程 id ，也就是 master 的binlog 中記錄 client 的線程 id 的值。然而注意到 slave sql 線程初始化函數 handle_slave_sql() 中調用的 init_slave_thread() 函數中有這樣一句代碼：

<code> </code><code>thd->thread_id= thd->variables.pseudo_thread_id= thread_id++;</code>

在這裡，thd->variable.pseudo_thread_id 是被初始化為 slave 目前線程 id 的。那麼它是何時被修改的呢？繼續看代碼：

<code> </code><code>while</code> <code>(!sql_slave_killed(thd,rli))</code>

<code> </code><code>if</code> <code>(exec_relay_log_event(thd,rli))</code>

以上代碼進入了執行 relay log 的循環。exec_relay_log_event() 中調用了函數 apply_event_and_update_pos() ，而這個函數中調用了 ev->apply_event() ，最終調用了 query_log_event::do_apply_event() 。在該函數中我們看到：

<code> </code><code>thd->variables.pseudo_thread_id= thread_id; </code><code>// for temp tables</code>

就是在這裡，thd->variables.pseudo_thread_id 已經被置為我們想要看到的值了。很神奇吧！

【主從模式下 temporary table 可能造成的不同步問題】

現在我們來考慮另外一個問題，即主從模式下 temporary table 可能引起的主從間不同步問題。

回憶 mysql 建立 temporary table 過程。該過程除了将 temporary table 資訊加入目前線程所擁有的 temporary table隊列之外，還做了一項工作，即在 /tmp 目錄下建立了臨時資料檔案，如：

<code>#sql64d6_18_0.ibd (innodb下)</code>

考慮以下情形：master 機上建立了一個 temporary table ，并且此時 binlog 模式為 statement-based 。于是 slave 上讀到了這個事件，并且在 slave上也同步了這個操作，即同樣建立了一個 temporary table 。此時由于某種原因，slave 突然意外重新開機。我們知道伺服器重新開機會導緻所有 /tmp 檔案夾下的資料檔案被清空，那麼在 slave 上，原先的 temporary table 不複存在。但是此時 master 上的原始的 temporary table 還是好好的！這樣，如果我們在 master 上做任何對該temporary table 上的修改操作都會引起 slave 端報錯，産生類似以下資訊：

<code>error</code><code>'table '</code><code>test.tmp</code><code>' doesn'</code><code>t exist</code><code>' on query. default database: '</code><code>test</code><code>'.</code>

<code>query: '</code><code>insert</code> <code>into</code> <code>tmp</code><code>values</code><code>(somevalue)'</code>

我們知道在 slave server 關閉後直到重新開機前，/tmp 目錄下的資料檔案都是存在的。問題的本質在于：slave server 關閉後，記憶體中的 temporary table 連結清單被回收，導緻 /tmp 下的資料檔案沒有對應的資料結構，那麼我們也就無從知曉對應的建立該表的 client 到底是哪一個。

解決這個問題的基本思路就是在 slave 重新開機時以某種方式恢複原先記憶體中的相關資訊。其中一種思路是，在 slave 建立 temporary table 時，我們額外寫一個檔案來記錄與維護資料檔案與客戶端線程 id 、表名、資料庫名的對應關系。另外一種思路是，在 slave 建立 temporary table 時，我們将相應的 binlog 記錄下來，然後在啟動的時候重做這些記錄。具體的實作這裡就不再詳細展開。

【轉載】MySQL Temporary Table 相關問題的探究

繼續閱讀

SQL優化SQL語句優化的目的

資料遷移方法資料遷移原則資料遷移之雙寫方案資料遷移之級聯同步方案

redis叢集資料一緻性_RedisRaft為Redis叢集帶來強大的資料一緻性

JAVA高效程式設計指南

寶塔面闆mysql恢複2018.1.8更新

Centos7 MySQL 5.7 安裝MySQL 5.7 安裝

查找入職員工時間排名倒數第三的員工所有資訊

Hibernate使用Hibernate的“3個準備，7個步驟”Hibernate API簡介操作實體對象對象識别

雲計算面試題——mysql/存儲引擎/備份

關于SQL語言

SQL語言基礎：常用的資料查詢語句

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

MySQL的4種隔離級别？出現問題

neo4j之cypher使用文檔

mysql使用source指令導入.sql檔案

sqlServer根據經緯查距離