国产探花免费观看_亚洲丰满少妇自慰呻吟_97日韩有码在线_资源在线日韩欧美_一区二区精品毛片,辰东完美世界有声小说,欢乐颂第一季,yy玄幻小说排行榜完本

首頁 > 數據庫 > MySQL > 正文

mysql臨時表，臨時表空間，ibtmp1表空間暴增原由初探

2024-07-24 12:31:54
字體：大 中 小
來源：轉載
供稿：網友
        問題的形式解答：
 
一、MySQL在什么情況下會創建臨時表(Internal Temporary Table Use in MySQL)？
 
      我列舉3個
 
     1. UNION查詢;
 
     2. insert into select ...from ...
 
     3. ORDER BY和GROUP BY的子句不一樣時;
 
     4.數據表中包含blob/text列
 
     等等，其實還有好多。 

二、怎么知道mysql用了臨時表呢？
 
這個問題很簡單， EXPLAIN 查看執行計劃結果的 Extra 列中，如果包含 Using Temporary 就表示會用到臨時表。舉個例子，有個感性認識。
 
創建測試表t22 :create  table  t22 as select *  from information_schema.tables;
 
mysql> desc t22;
+-----------------+---------------------+------+-----+---------+-------+
| Field           | Type                | Null | Key | Default | Extra |
+-----------------+---------------------+------+-----+---------+-------+
| TABLE_CATALOG   | varchar(512)        | NO   |     |         |       |
| TABLE_SCHEMA    | varchar(64)         | NO   |     |         |       |
| TABLE_NAME      | varchar(64)         | NO   |     |         |       |
| TABLE_TYPE      | varchar(64)         | NO   |     |         |       |
| ENGINE          | varchar(64)         | YES  |     | NULL    |       |
| VERSION         | bigint(21) unsigned | YES  |     | NULL    |       |
| ROW_FORMAT      | varchar(10)         | YES  |     | NULL    |       |
| TABLE_ROWS      | bigint(21) unsigned | YES  |     | NULL    |       |
| AVG_ROW_LENGTH  | bigint(21) unsigned | YES  |     | NULL    |       |
| DATA_LENGTH     | bigint(21) unsigned | YES  |     | NULL    |       |
| MAX_DATA_LENGTH | bigint(21) unsigned | YES  |     | NULL    |       |
| INDEX_LENGTH    | bigint(21) unsigned | YES  |     | NULL    |       |
| DATA_FREE       | bigint(21) unsigned | YES  |     | NULL    |       |
| AUTO_INCREMENT  | bigint(21) unsigned | YES  |     | NULL    |       |
| CREATE_TIME     | datetime            | YES  |     | NULL    |       |
| UPDATE_TIME     | datetime            | YES  |     | NULL    |       |
| CHECK_TIME      | datetime            | YES  |     | NULL    |       |
| TABLE_COLLATION | varchar(32)         | YES  |     | NULL    |       |
| CHECKSUM        | bigint(21) unsigned | YES  |     | NULL    |       |
| CREATE_OPTIONS  | varchar(255)        | YES  |     | NULL    |       |
| TABLE_COMMENT   | varchar(2048)       | NO   |     |         |       |
+-----------------+---------------------+------+-----+---------+-------+
21 rows in set (0.02 sec)
mysql> explain
    -> select  table_schema  ,table_name, create_time  from   t22  where table_schema  like 'test%'
    -> union
    -> select  table_schema  ,table_name, create_time  from   t22  where table_schema  like 'information%'  
    -> ;
+----+--------------+------------+------------+------+---------------+------+---------+------+----------+----------+-----------------+
| id | select_type  | table      | partitions | type | possible_keys | key  | key_len | ref  | rows     | filtered | Extra           |
+----+--------------+------------+------------+------+---------------+------+---------+------+----------+----------+-----------------+
|  1 | PRIMARY      | t22        | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 12522369 |    11.11 | Using where     |
|  2 | UNION        | t22        | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 12522369 |    11.11 | Using where     |
| NULL | UNION RESULT | <union1,2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL |     NULL |     NULL | Using temporary |
+----+--------------+------------+------------+------+---------------+------+---------+------+----------+----------+-----------------+
3 rows in set, 1 warning (0.02 sec)
三、臨時表有關的參數有哪些？
 
    innodb_temp_data_file_path = ibtmp1:12M:autoextend
     tmp_table_size = 16777216
     max_heap_table_size =16777216
     default_tmp_storage_engine=InnoDB
 
      internal_tmp_disk_storage_engine= InnoDB
 
四、mysql臨時表配置參數是tmp_table_size，當臨時表空間不夠用的時候怎么辦？
 
       如果臨時表中需要存儲的數據量超過了上限（ tmp-table-size 或 max-heap-table-size 中取其大者），這時候就需要生成基于磁盤的臨時表了。也就是放在innodb_temp_data_file_path指定的臨時表空間中。
 
      如果你對這句話有疑問，那我舉個例子來看下：反復執行語句： insert into  t22  select * from t22; 同時查看表空間ibtmp1的大小變化。反復執行insert 語句，插入表中的數量指數級增長。
  
五、看圖說話，做了上個實驗，不知道你是否會有如下想法：既然內部臨時表(Internal Temporary Table)用于排序，分組，當需要的存儲空間超過 tmp-table-size 上限的時候，使用臨時表空間。臨時表空間是磁盤，速度比不上內存，那是不是可以加大tmp_table_size來優化需要使用臨時表的SQL語句？
 
      當然可以呀，tmp_table_size最大值是18446744073709551615，如果建議256M。
  
六、mysql中是如何監控臨時表和臨時表空間使用情況的？
 
mysql> show  status like '%tmp%';         
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 1     |
| Created_tmp_files       | 7     |
| Created_tmp_tables      | 18    |
+-------------------------+-------+
    建議Created_tmp_disk_tables/Created_tmp_tables不要超過25%。如果Created_tmp_disk_tables數量很大，查看是否有很多慢sql，是否有很多使用臨時表的語句。加大 tmp_table_size 的值。
七、mysql的臨時表空間文件暴增，可以達到幾百G，你認為形成的原因是什么？
      第四個問題做的例子，如果你不停的反復的實驗，你會發現ibtmp1增長的速度驚人。有個項目，曾經ibtmp1暴增到300G。一看慢sql日志，有大量慢sql，而且有很多語句需要排序。所以給ibtmp1加上限制最大值。innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:5G，mysql會反復利用。

（編輯：武林網）

發表評論 共有條評論
用戶名: 密碼:
驗證碼: 匿名發表
主站蜘蛛池模板： 九寨沟县| 东山县| 茌平县| 读书| 连南| 兰考县| 琼海市| 滁州市| 浦江县| 射阳县| 象山县| 曲麻莱县| 望谟县| 汝阳县| 广饶县| 吉林省| 灌云县| 德安县| 金坛市| 柳林县| 临朐县| 石城县| 莱阳市| 聂荣县| 重庆市| 志丹县| 枝江市| 永丰县| 福海县| 靖西县| 板桥市| 海门市| 叶城县| 崇明县| 密云县| 汕尾市| 聊城市| 延安市| 大余县| 富源县| 霍邱县|