Mysql
為什麼這個 SELECT 創建臨時表？
當我執行以下 SELECT 時，它需要很長時間，通常是 30 或 40 分鐘（我使用的是 Debian，MySQL 5.5.62）：
SELECT ga.currency, o.organisation_name, os.hostname, g.global_name, SUM(IF(ga.type ='WAGER', ga.amount,0)) AS 'total wager lc', SUM(IF(ga.type ='WAGER', ga.amount,0)/fx.gbp) AS 'total wager gbp', SUM(IF(ga.type ='WAGER', ga.amount,0)/fx.eur) AS 'total wager eur', SUM(IF(ga.type ='WAGER', ga.amount,0)/fx.usd) AS 'total wager usd', SUM(IF(ga.type = 'WIN', ga.amount, 0)) AS 'total win lc', SUM(IF(ga.type = 'WIN', ga.amount, 0)/fx.gbp) AS 'total win gbp', SUM(IF(ga.type = 'WIN', ga.amount, 0)/fx.eur) AS 'total win eur', SUM(IF(ga.type = 'WIN', ga.amount, 0)/fx.usd) AS 'total win usd', CONCAT( DATE(ga.created_timestamp),' ', HOUR(ga.created_timestamp)) AS date_and_hour FROM game_action ga JOIN user u ON u.user_id = ga.user_id JOIN game g ON g.game_id = ga.game_id JOIN game_instance gi ON gi.game_instance_id = ga.game_instance_id JOIN organisation_site os ON u.organisation_site_id = os.organisation_site_id JOIN organisation o ON o.organisation_id = os.organisation_id INNER JOIN reporting.fx_conversion fx ON fx.date = date(ga.created_timestamp) AND fx.currency = ga.currency WHERE ga.created_timestamp BETWEEN str_to_date(CONCAT(DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 WEEK),'%Y-%m-%d'),' 00:00:00'),'%Y-%m-%d %T') AND str_to_date(CONCAT(DATE_SUB(LAST_DAY(DATE_SUB(NOW(), INTERVAL 1 WEEK)), INTERVAL -1 DAY),' 23:59:59'),'%Y-%m-%d %T') AND gi.status IN ('RESOLVED', 'AUTO_COMPLETED') GROUP BY ga.currency , o.organisation_name , os.hostname , g.global_name , date_and_hour ORDER BY o.organisation_name , os.hostname , ga.created_timestamp ASC ;但是，解釋表明這不應該發生（或者我不明白事情是如何運作的）：
mysql> explain extended ... +----+-------------+-------+--------+-----------------------------------------------------------------------+---------+---------+--------------------------------+----------+----------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+--------+-----------------------------------------------------------------------+---------+---------+--------------------------------+----------+----------+----------------------------------------------+ | 1 | SIMPLE | ga | ALL | GA_IX01,GA_IX02,GA_IX03,game_action_created_timestamp,ga_id_cur_tstmp | NULL | NULL | NULL | 23851088 | 49.75 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | g | eq_ref | PRIMARY | PRIMARY | 4 | gameiom.ga.game_id | 1 | 100.00 | | | 1 | SIMPLE | fx | ref | PRIMARY | PRIMARY | 3 | func | 22 | 100.00 | Using where | | 1 | SIMPLE | u | eq_ref | PRIMARY,U_UK01,U_IX_04 | PRIMARY | 4 | gameiom.ga.user_id | 1 | 100.00 | | | 1 | SIMPLE | os | eq_ref | PRIMARY,ORGST_UK1_orgid_extid,ORGST_FK01 | PRIMARY | 4 | gameiom.u.organisation_site_id | 1 | 100.00 | | | 1 | SIMPLE | o | eq_ref | PRIMARY | PRIMARY | 4 | gameiom.os.organisation_id | 1 | 100.00 | | | 1 | SIMPLE | gi | ref | PRIMARY,game_instance_status | PRIMARY | 8 | gameiom.ga.game_instance_id | 1 | 100.00 | Using where | +----+-------------+-------+--------+-----------------------------------------------------------------------+---------+---------+--------------------------------+----------+----------+----------------------------------------------+ 7 rows in set, 1 warning (0.00 sec) mysql> show warnings\G *************************** 1. row *************************** Level: Note Code: 1003 Message: select `gameiom`.`ga`.`currency` AS `currency`,`gameiom`.`o`.`organisation_name` AS `organisation_name`,`gameiom`.`os`.`hostname` AS `hostname`,`g`.`global_name` AS `global_name`,sum(if((`gameiom`.`ga`.`type` = 'WAGER'),`gameiom`.`ga`.`amount`,0)) AS `total wager lc`,sum((if((`gameiom`.`ga`.`type` = 'WAGER'),`gameiom`.`ga`.`amount`,0) / `reporting`.`fx`.`gbp`)) AS `total wager gbp`,sum((if((`gameiom`.`ga`.`type` = 'WAGER'),`gameiom`.`ga`.`amount`,0) / `reporting`.`fx`.`eur`)) AS `total wager eur`,sum((if((`gameiom`.`ga`.`type` = 'WAGER'),`gameiom`.`ga`.`amount`,0) / `reporting`.`fx`.`usd`)) AS `total wager usd`,sum(if((`gameiom`.`ga`.`type` = 'WIN'),`gameiom`.`ga`.`amount`,0)) AS `total win lc`,sum((if((`gameiom`.`ga`.`type` = 'WIN'),`gameiom`.`ga`.`amount`,0) / `reporting`.`fx`.`gbp`)) AS `total win gbp`,sum((if((`gameiom`.`ga`.`type` = 'WIN'),`gameiom`.`ga`.`amount`,0) / `reporting`.`fx`.`eur`)) AS `total win eur`,sum((if((`gameiom`.`ga`.`type` = 'WIN'),`gameiom`.`ga`.`amount`,0) / `reporting`.`fx`.`usd`)) AS `total win usd`,concat(cast(`gameiom`.`ga`.`created_timestamp` as date),' ',hour(`gameiom`.`ga`.`created_timestamp`)) AS `date_and_hour` from `gameiom`.`game_action` `ga` join `gameiom`.`user` `u` join `gameiom`.`game` `g` join `gameiom`.`game_instance` `gi` join `gameiom`.`organisation_site` `os` join `gameiom`.`organisation` `o` join `reporting`.`fx_conversion` `fx` where ((`gameiom`.`u`.`user_id` = `gameiom`.`ga`.`user_id`) and (`g`.`game_id` = `gameiom`.`ga`.`game_id`) and (`gameiom`.`os`.`organisation_site_id` = `gameiom`.`u`.`organisation_site_id`) and (`gameiom`.`o`.`organisation_id` = `gameiom`.`os`.`organisation_id`) and (`gameiom`.`ga`.`created_timestamp` between <cache>(str_to_date(concat(date_format((now() - interval 1 week),'%Y-%m-%d'),' 00:00:00'),'%Y-%m-%d %T')) and <cache>(str_to_date(concat((last_day((now() - interval 1 week)) - interval -(1) day),' 23:59:59'),'%Y-%m-%d %T'))) and (`gameiom`.`gi`.`status` in ('RESOLVED','AUTO_COMPLETED')) and (`reporting`.`fx`.`date` = cast(`gameiom`.`ga`.`created_timestamp` as date)) and (convert(`reporting`.`fx`.`currency` using utf8) = `gameiom`.`ga`.`currency`) and (`gameiom`.`gi`.`game_instance_id` = `gameiom`.`ga`.`game_instance_id`)) group by `gameiom`.`ga`.`currency`,`gameiom`.`o`.`organisation_name`,`gameiom`.`os`.`hostname`,`g`.`global_name`,concat(cast(`gameiom`.`ga`.`created_timestamp` as date),' ',hour(`gameiom`.`ga`.`created_timestamp`)) order by `gameiom`.`o`.`organisation_name`,`gameiom`.`os`.`hostname`,`gameiom`.`ga`.`created_timestamp` 1 row in set (0.00 sec)使用分析執行 SELECT 顯示：
mysql> show profile; +--------------------------------+------------+ | Status | Duration | +--------------------------------+------------+ | starting | 0.000013 | | Waiting for query cache lock | 0.000003 | | Waiting on query cache mutex | 0.000002 | | checking query cache for query | 0.000147 | | checking permissions | 0.000003 | | checking permissions | 0.000002 | | checking permissions | 0.000002 | | checking permissions | 0.000002 | | checking permissions | 0.000002 | | checking permissions | 0.000002 | | checking permissions | 0.000004 | | Opening tables | 0.000046 | | System lock | 0.000065 | | init | 0.000078 | | optimizing | 0.000046 | | statistics | 0.000157 | | preparing | 0.000026 | | Creating tmp table | 0.000199 | | executing | 0.000003 | | Copying to tmp table | 999.999999 | | Sorting result | 0.034862 | | Sending data | 0.019785 | | end | 0.000005 | | removing tmp table | 0.000379 | | end | 0.000005 | | query end | 0.000005 | | closing tables | 0.000080 | | freeing items | 0.000040 | | logging slow query | 0.000002 | | logging slow query | 0.000111 | | cleaning up | 0.000006 | +--------------------------------+------------+ 31 rows in set (0.00 sec)現在，我不得不承認我不理解 EXPLAIN 之後的警告，也許它指的是導致 tmp 表（？）的東西。但是，除此之外，SQL 的形狀是否會導致 tmp 表？我在文件中發現的內容有點難以理解。
編輯：
對不起，我昨天有幾件緊急的事情要做，但我現在有時間重新審視這個話題。我嘗試再次對我的選擇執行解釋，現在它看起來更像我以為我記得它：
+----+-------------+-------+--------+------------------------------------------+-----------------------+---------+---------------------------------+------+----------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+--------+------------------------------------------+-----------------------+---------+---------------------------------+------+----------+---------------------------------+ | 1 | SIMPLE | o | ALL | PRIMARY | NULL | NULL | NULL | 2 | 100.00 | Using temporary; Using filesort | | 1 | SIMPLE | os | ref | PRIMARY,ORGST_UK1_orgid_extid,ORGST_FK01 | ORGST_UK1_orgid_extid | 4 | gameiom.o.organisation_id | 5 | 100.00 | | | 1 | SIMPLE | u | ref | PRIMARY,U_UK01,U_IX_04 | U_IX_04 | 4 | gameiom.os.organisation_site_id | 1566 | 100.00 | Using index | | 1 | SIMPLE | ga | ref | GA_IX01,GA_IX02,GA_IX03 | GA_IX02 | 4 | gameiom.u.user_id | 287 | 100.00 | Using where | | 1 | SIMPLE | g | eq_ref | PRIMARY | PRIMARY | 4 | gameiom.ga.game_id | 1 | 100.00 | | | 1 | SIMPLE | gi | ref | PRIMARY | PRIMARY | 8 | gameiom.ga.game_instance_id | 1 | 100.00 | Using where | | 1 | SIMPLE | fx | ref | PRIMARY | PRIMARY | 3 | func | 5 | 100.00 | Using where | +----+-------------+-------+--------+------------------------------------------+-----------------------+---------+---------------------------------+------+----------+---------------------------------+ 7 rows in set, 1 warning (0.00 sec)事實上，我從問題開始時上傳的內容中複製了 SELECT。我能想到的唯一解釋是我一定是複制了錯誤的輸出。對於那個很抱歉。我上傳的分析數據應該是正確的，但我會重新執行以確保並更新是否有任何更改。
臨時表會發揮作用也就不足為奇了。為什麼 ？？？
根據關於join_buffer_size的 MySQL 5.5 文件（斜體字）：
用於普通索引掃描、範圍索引掃描和不使用索引並因此執行全表掃描的連接的緩衝區的最小大小。通常，獲得快速連接的最佳方法是添加索引。當無法添加索引時，增加 join_buffer_size 的值以獲得更快的完全連接。為兩個表之間的每個完全連接分配一個連接緩衝區。對於幾個不使用索引的表之間的複雜連接，可能需要多個連接緩衝區。
將緩衝區設置為大於容納每個匹配行所需的大小並沒有任何好處，並且所有連接都至少分配最小大小，因此在全域將此變數設置為較大的值時要小心。最好保持全域設置較小，僅在進行大型連接的會話中更改為較大的設置。如果全域大小大於大多數使用它的查詢所需的記憶體分配時間，則記憶體分配時間會導致性能大幅下降。
join_buffer_size 的最大允許設置為 4GB-1。64 位平台允許使用更大的值（64 位 Windows 除外，其大值會被截斷為 4GB-1 並發出警告）。
有關連接緩衝的更多資訊，請參閱第 8.2.1.5 節，“嵌套循環連接算法”。
鑑於上述文件和您的個人資料，join_buffer_size很可能太小了。這將觸發 mysqld 將 JOIN 緩衝區發送到臨時表。更糟糕的是，您有六 (6) 個 JOIN 子句。這意味著您有六 (6) 個 JOIN 緩衝區。
嘗試增加join_buffer_size但不要發瘋。
建議
嘗試使用更大的join_buffer_size動態執行查詢。
SET @oldvalue = @@session.join_buffer_size; SET @newvalue = @oldvalue * 2; SET SESSION join_buffer_size = @newvalue; <Run Your Query Here>檢查您解釋計劃和配置文件，看看是否有所作為。
試試看 ！！！
更新 2020-01-15 10:34 EDT
假設連接的所有索引都存在，只剩下一個 JOIN 緩衝區，最後一個 JOIN 子句
INNER JOIN reporting.fx_conversion fx ON fx.date = date(ga.created_timestamp) AND fx.currency = ga.currency看起來很討厭，因為您正在使用該
DATE()功能。上面寫滿了臨時表。為什麼 ？？？由於復雜的表達式，該 JOIN 不能使用索引。也許一個或多個 JOIN 緩衝區正在使用中。
實際上強制溫度的兩件事：
GROUP BY <<columns from multiple tables>> ORDER BY <<columns from multiple tables>>這
str_to_date(CONCAT(DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 WEEK),'%Y-%m-%d'),' 00:00:00'),'%Y-%m-%d %T')大概可以簡化為
CURDATE() - INTERVAL 1 WEEK注意：
DATE和DATETIME格式可以自由比較。也就是說，您使用其中的哪一個並不重要：SELECT str_to_date(CONCAT(DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 WEEK),'%Y-%m-%d'),' 00:00:00'),'%Y-%m-%d %T') AS LongForm, CURDATE() - INTERVAL 1 WEEK AS ShortForm; +---------------------+------------+ | LongForm | ShortForm | +---------------------+------------+ | 2020-01-11 00:00:00 | 2020-01-11 | +---------------------+------------+
SUM()我建議您檢查數學 - 在很多JOINs可能會誇大值之後進行聚合（例如）。我們需要查看您擁有的索引以判斷它們是否是最優的。