mysql臨時表,臨時表空間,ibtmp1表空間暴增原因初探
問題的形式解答:
一、MySQL在什麼情況下會建立臨時表(Internal Temporary Table Use in MySQL)?
我列舉3個
1. UNION查詢;
2. insert into select ...from ...
3. ORDER BY和GROUP BY的子句不一樣時;
4.資料表中包含blob/text列
等等,其實還有好多。具體參考 https://dev.mysql.com/doc/refman/5.7/en/internal-temporary-tables.html
二、怎麼知道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會反覆利用。
參考:老葉茶館
https://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ==&mid=207355450&idx=3&sn=3e3a2c0a7497a8cd099ddc5c33a9932d&scene=21#wechat_redirect
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/30393770/viewspace-2638834/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- oracle 臨時表空間Oracle
- oracle臨時表空間Oracle
- Oracle臨時表空間檢視、新增臨時表空間資料檔案、修改預設臨時表空間 方法!Oracle
- MySQL InnoDB臨時表空間配置MySql
- 批量處理時臨時增加回滾表空間臨時表空間檔案
- 臨時表空間的建立、刪除,設定預設臨時表空間
- Oracle 臨時表空間概念Oracle
- oracle臨時表空間組Oracle
- oracle的臨時表空間Oracle
- Oracle Temp 臨時表空間Oracle
- MySQL 5.7 新特性 共享臨時表空間及臨時表改進MySql
- 【臨時表空間組】臨時表空間組的建立、維護及應用
- 【儲存管理】建立臨時表空間組、建立臨時表空間組及使用
- 【實驗】重建臨時表空間解決臨時表空間過大問題
- 臨時表空間的增刪改查
- 【臨時表空間】11g中使用 SHRINK方法縮小臨時表空間和臨時檔案
- oracle 表空間,臨時表空間使用率查詢Oracle
- oracle臨時表空間相關Oracle
- Oracle TEMP臨時表空間概念Oracle
- Oracle 臨時表空間的概念Oracle
- 臨時表空間操作總結
- ORACLE臨時表空間總結Oracle
- oracle 重建臨時表空間 tempfileOracle
- 刪掉Oracle臨時表空間Oracle
- ORACLE臨時表空間的清理Oracle
- 【臨時表空間】11g中使用 SHRINK方法縮小臨時表空間和臨時檔案(續)
- oracle 臨時表空間的增刪改查Oracle
- ORACLE 臨時表空間的增刪改查:Oracle
- oracle臨時表空間的增刪改查Oracle
- Oracle修改預設表空間和預設臨時表空間Oracle
- 臨時表空間被佔滿的原因查詢
- oracle臨時表空間過大的原因&&處理Oracle
- MYSQL造資料佔用臨時表空間MySql
- Oracle Temp臨時表空間處理Oracle
- ORACLE預設的臨時表空間Oracle
- oracle的臨時表空間temporary tablespaceOracle
- oracle清理和重建臨時表空間Oracle
- 臨時表空間和回滾表空間使用率查詢