Mysql
如何找到 MySQL 在記憶體或磁碟中創建臨時表？
我有一個查詢，如果我在同一個會話中執行顯示狀態後執行它，如下所示
show status其中變數名like '%Created_tmp_disk_tables%';它增加了一個值。所以這意味著臨時表是在磁碟上創建的嗎？不確定它是否正確。
但是，如果我為該查詢啟用分析。下面是我得到的狀態。
| status | duration | |---------------------- |---------- | | starting | 0.000246 | | checking permissions | 0.000035 | | Opening tables | 0.000098 | | System lock | 0.000044 | | init | 0.000209 | | optimizing | 0.000075 | | statistics | 0.000336 | | preparing | 0.000138 | | Creating tmp table | 0.000435 | | executing | 0.000038 | | Copying to tmp table | 2.923929 | | Sorting result | 0.267926 | | Sending data | 0.000945 | | end | 0.000071 | | removing tmp table | 0.077263 | | query end | 0.000022 | | closing tables | 0.000055 | | freeing items | 0.000092 | | logging slow query | 0.000074 | | cleaning up | 0.000022 |在這裡我找到
Copying to tmp table但沒有Copying to tmp table on disk。為什麼這兩者之間會發生衝突？
顯示狀態變數是否正確或分析是否正確？
需要知道是否在磁碟上創建了臨時表。
Created_tmp_disk_tables在會話或全域範圍內使用變數來了解您何時在磁碟上創建了隱式臨時表：MariaDB [(none)]> SHOW STATUS like 'Created\_tmp%'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | Created_tmp_disk_tables | 0 | | Created_tmp_files | 6 | | Created_tmp_tables | 0 | <--- session starts with 0 tmp tables +-------------------------+-------+ 3 rows in set (0.01 sec) MariaDB [(none)]> SELECT 1 UNION SELECT 1; +---+ | 1 | +---+ | 1 | +---+ 1 row in set (0.00 sec) MariaDB [(none)]> SHOW STATUS like 'Created\_tmp%'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | Created_tmp_disk_tables | 0 | | Created_tmp_files | 6 | | Created_tmp_tables | 1 | <--- one tmp table created, 0 on disk +-------------------------+-------+ 3 rows in set (0.01 sec) MariaDB [(none)]> SELECT 1 UNION ALL SELECT 1; +---+ | 1 | +---+ | 1 | | 1 | +---+ 2 rows in set (0.00 sec) MariaDB [(none)]> SHOW STATUS like 'Created\_tmp%'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | Created_tmp_disk_tables | 0 | | Created_tmp_files | 6 | | Created_tmp_tables | 1 | <--- no tmp table created +-------------------------+-------+ 3 rows in set (0.00 sec) MariaDB [(none)]> create table test.test (a blob); Query OK, 0 rows affected (0.18 sec) MariaDB [(none)]> SELECT * FROM test.test UNION SELECT * FROM test.test; Empty set (0.00 sec) MariaDB [(none)]> SHOW STATUS like 'Created\_tmp%'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | Created_tmp_disk_tables | 1 | <--- tmp table created on disk | Created_tmp_files | 6 | | Created_tmp_tables | 2 | +-------------------------+-------+ 3 rows in set (0.00 sec)如果您啟用performance_schema，預設配置（非常非常低的成本）會為您提供您可能需要的所有內容的摘要：
MariaDB [(none)]> SELECT * FROM performance_schema.events_statements_summary_by_digest\G [...] *************************** 26. row *************************** SCHEMA_NAME: test DIGEST: f0aad09130b2e108d8dd1c58d1713678 DIGEST_TEXT: SELECT * FROM `test` UNION SELECT * FROM `test` COUNT_STAR: 1 SUM_TIMER_WAIT: 716448000 MIN_TIMER_WAIT: 716448000 AVG_TIMER_WAIT: 716448000 MAX_TIMER_WAIT: 716448000 SUM_LOCK_TIME: 308000000 SUM_ERRORS: 0 SUM_WARNINGS: 0 SUM_ROWS_AFFECTED: 0 SUM_ROWS_SENT: 0 SUM_ROWS_EXAMINED: 0 SUM_CREATED_TMP_DISK_TABLES: 1 <---- 1 disk tmp table created SUM_CREATED_TMP_TABLES: 1 <---- 1 tmp table created (including the disk one) SUM_SELECT_FULL_JOIN: 0 SUM_SELECT_FULL_RANGE_JOIN: 0 SUM_SELECT_RANGE: 0 SUM_SELECT_RANGE_CHECK: 0 SUM_SELECT_SCAN: 3 SUM_SORT_MERGE_PASSES: 0 SUM_SORT_RANGE: 0 SUM_SORT_ROWS: 0 SUM_SORT_SCAN: 0 SUM_NO_INDEX_USED: 1 SUM_NO_GOOD_INDEX_USED: 0 FIRST_SEEN: 2018-01-06 21:18:18 LAST_SEEN: 2018-01-06 21:18:18**編輯：**我剛剛注意到您仍在使用 5.5-我強烈建議升級，只有 P_S 值得在調試時不失去時間。目前，會話/全域變數應該適合您，分析將無法正常工作：
MariaDB [(none)]> SET profiling = 1; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> SELECT * FROM test.test UNION SELECT * FROM test.test; Empty set (0.00 sec) MariaDB [(none)]> SHOW PROFILES; +----------+------------+-------------------------------------------------------+ | Query_ID | Duration | Query | +----------+------------+-------------------------------------------------------+ | 1 | 0.00055224 | SELECT * FROM test.test UNION SELECT * FROM test.test | +----------+------------+-------------------------------------------------------+ 1 row in set (0.00 sec) MariaDB [(none)]> SHOW PROFILE FOR QUERY 1; +----------------------+----------+ | Status | Duration | +----------------------+----------+ | starting | 0.000053 | | checking permissions | 0.000011 | | checking permissions | 0.000007 | | Opening tables | 0.000026 | | After opening tables | 0.000009 | | System lock | 0.000008 | | Table lock | 0.000196 | | optimizing | 0.000010 | | statistics | 0.000015 | | preparing | 0.000013 | | optimizing | 0.000007 | | statistics | 0.000006 | | preparing | 0.000009 | | executing | 0.000005 | | Sending data | 0.000019 | | executing | 0.000004 | | Sending data | 0.000009 | | optimizing | 0.000006 | | statistics | 0.000006 | | preparing | 0.000005 | | executing | 0.000004 | | Sending data | 0.000016 | | removing tmp table | 0.000057 | | Sending data | 0.000006 | | query end | 0.000008 | | closing tables | 0.000004 | | Unlocking tables | 0.000008 | | freeing items | 0.000005 | | updating status | 0.000009 | | cleaning up | 0.000012 | +----------------------+----------+ 30 rows in set (0.00 sec)有時你會看到像“從堆複製到 myisam”這樣的步驟，當它在記憶體上執行時，但不會每次都發生，如上所示。您在標籤上提到了 innodb，但隱式臨時表從 5.7開始才開始成為 InnoDB (vs HEAP + MyISAM) 。