MySQL系統暫存資料表、使用者暫存資料表

標籤：

　　MySQL暫存資料表分為系統使用的暫存資料表和使用者使用的暫存資料表。

 

       系統使用的暫存資料表是指MySQL在執行某些SQL語句時需要依賴暫存資料表來完成整個過程。系統使用的暫存資料表的情況可以分為以下幾種：

       *  group by和order by中的列不相同，例如：ORDERY BY price GROUP BY name；

       *  order by的列或者group by的列不是引用from 表列表中 的第一表，例如：SELECT * from TableA, TableB ORDER BY TableA.price GROUP by TableB.name

       *  使用了sql_small_result選項 ，SQL_SMALL_RESULT的意思就是告訴MySQL，結果會很小，請直接使用記憶體暫存資料表

       *  含有distinct的order by語句，例如：ORDERY BY DISTINCT(price)

       *  from子查詢會用到暫存資料表

　　

　　查看MySQL中關於暫存資料表的配置參數：

　　mysql> show variables like ‘%tmp_table%‘;

　　+-----------------------+--------------+

　　| Variable_name  | Value    |

　　+-----------------------+--------------+

　　| max_tmp_tables | 32       |

　　| tmp_table_size | 47185920 |

　　+-----------------------+---------------+

　　下面是2個查詢中用到了暫存資料表的例子：

　　mysql> select * from person;

　　+----------+---------+

　　| name | age  |

　　+-----------+--------+

　　| tom  |   22 |

　　| tony   |   31 |

　　+-----------+--------+ 

　　mysql> select * from grade;

　　+------------+-----------+

　　| name   | grade |

　　+------------+------------+

　　| tom    |    22 |

　　| tony   |    31 |

　　| unkown |    40 |

　　+-------------+-------------+　　

　　mysql> explain select g.name,g.grade from person p,grade g where p.name = g.name group by(g.name);

　　+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+

　　| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                           |

　　+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+

　　|  1 | SIMPLE      | p     | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using temporary; Using filesort |

　　|  1 | SIMPLE      | g     | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where; Using join buffer  |

　　+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+

　　mysql> explain select m.name,p.age from person as p inner join(select person.name  from person,grade where person.name = grade.name )as m on p.name = m.name;

　　+----+-------------+------------+------+---------------+------+---------+------+------+--------------------------------+

　　| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra                          |

　　+----+-------------+------------+------+---------------+------+---------+------+------+--------------------------------+

　　|  1 | PRIMARY     | p          | ALL  | NULL          | NULL | NULL    | NULL |    2 |                                |

　　|  1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where; Using join buffer |

　　|  2 | DERIVED     | person     | ALL  | NULL          | NULL | NULL    | NULL |    2 |                                |

　　|  2 | DERIVED     | grade      | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where; Using join buffer |

　　+----+-------------+------------+------+---------------+------+---------+------+------+--------------------------------+

　　 當查詢的資料量很大時，暫存資料表又轉換為了磁碟暫存資料表，這時的查詢速度就會較慢。

　　有些情況下MySQL很可能會直接使用磁碟暫存資料表：

　　 * 表包含TEXT或者BLOB列；

　　 * GROUP BY 或者 DISTINCT 子句中包含長度大於512位元組的列；

　　 * 使用UNION或者UNION ALL時，SELECT子句中包含大於512位元組的列；

 

       常見的避免暫存資料表的方法有：

      * 建立索引：在ORDER BY或者GROUP BY的列上建立索引；

      * 分拆很長的列：一般情況下，TEXT、BLOB，大於512位元組的字串，基本上都是為了顯示資訊，而不會用於查詢條件， 因此表設計的時候，應該將這些列獨立到另外一張表。　　

　　暫存資料表主要是用於排序和分組，很多業務都是要求排序後再取出詳細的分頁資料，這種情況下可以將排序和取出詳細資料拆分成不同的SQL，以降低排序或分組時暫存資料表的大小，提升排序和分組的效率。

 

       自己定義的暫存資料表可以作為大資料表的一部分，方便查詢。

       通過語句CREATE TEMPORARY TABLE XXX 來建立應用所需的暫存資料表。我們自訂的暫存資料表有以下一些特點：

　　*  暫存資料表的存在的周期是當前登入的session有效，中斷連線將會被立即刪除，也可手動將其drop table，無論以任何類型建立的暫存資料表，在對應的資料庫目錄下都沒有具體的檔案產生；既然是session有效，那麼不同的session間是不能訪問別人定義的暫存資料表的，不同session定義的暫存資料表名就可以相同。

　　* 支援多種類型的表：MyISAM、InnoDB等，通過max_heap_table_size參數值來控制暫存資料表的大小； 

　　*  為什麼要用它呢？暫存資料表可以將我們之後可能頻繁使用到的中間資料集臨時儲存下來，這樣就會提高業務的處理速度，再有就是可以減少程式碼量，將一定的邏輯處理放到資料庫上去完成，這之間的代價需要實際去評估；到了5.0以後出現了視圖，對於開發人員有多了一種可選擇的方法，而視圖的定義是可以永久儲存到磁碟上的，視圖是不能重名的。

　　

MySQL系統暫存資料表、使用者暫存資料表