浅谈Mysql在什么情况下会使用内部临时表

union鎵ц��

涓轰簡渚夸簬鍒嗘瀽锛屼娇鐢ㄤ竴涓媠ql鏉ヨ繘琛屼妇渚?br />

CREATE TABLE t1 ( id INT PRIMARY KEY, a INT, b INT, INDEX ( a ) );

delimiter ;;
CREATE PROCEDURE idata ( ) BEGIN
DECLARE
        i INT;
 SET i = 1;
 WHILE
   ( i <= 1000 ) DO
   INSERT INTO t1
  VALUES
   ( i, i, i );
  SET i = i + 1;
 END WHILE;
 END;;

delimiter ;
CALL idata ( );

鐒跺悗鎴戜滑鎵ц�屼互涓媠ql

(select 1000 as f) union (select id from t1 order by id desc limit 2);

杩欐�祍ql鐨勮��涔夋槸锛屽彇涓や釜瀛愭煡璇㈢殑鐨勫苟闆嗭紝骞朵笖鍘婚噸

鍙�浠ョ湅鍒帮紝绗�浜岃�岀殑key鏄痯rimary锛岃�存槑绗�浜屼釜瀛愭煡璇�浣跨敤绱㈠紩id銆傜��涓夎�岀殑Extra瀛楁�碉紝琛ㄧず鍦ㄥ瓙鏌ヨ��union鐨勬椂鍊欙紝浣跨敤浜嗕复鏃惰〃锛圲sing temporary锛夈�?br /> 杩欎釜璇�鍙ョ殑鎵ц�屾祦绋嬫槸杩欐牱鐨勶細
1锛夊垱寤轰竴涓�鍐呭瓨涓存椂琛�锛岃繖涓�涓存椂琛ㄥ彧鏈変竴涓�鏁村舰瀛楁�礷锛屽苟涓攆鏄�涓婚敭瀛楁�?br /> 2锛夋墽琛岀��涓�涓�瀛愭煡璇�锛屽皢1000瀛樺湪涓存椂琛?br /> 3锛夋墽琛岀��浜屼釜瀛愭煡璇�锛屾嬁鍒扮��涓�琛宨d=1000锛屽苟璇曞浘鎻掑叆鍒颁复鏃惰〃锛屼絾鏄�鐢变�?000杩欎釜鍊煎凡缁忓瓨鍦ㄤ复鏃惰〃浜嗭紝杩濇硶浜嗗敮涓�鎬х害鏉燂紝鎵�浠ユ彃鍏ュけ璐ワ紝鎺ョ潃鍙栧埌绗�浜岃�屾暟鎹?99锛屾彃鍏ヤ复鏃惰〃鎴愬姛
4锛変粠涓存椂琛ㄤ腑鎸夎�屽彇鍑烘暟鎹�锛岃繑鍥炵粨鏋滐紝骞跺垹闄や复鏃惰〃锛岀粨鏋滀腑鍖呭惈涓ゆ潯鏁版嵁灏辨槸1000鍜?99
鍙�浠ョ湅鍒帮紝涓存椂琛ㄨ捣鍒颁簡鏆傚瓨鏁版嵁鐨勪綔鐢�锛岃�屼笖瀛樺湪鍞�涓�鎬х害鏉燂紝瀹炵幇浜唘nion鍘婚噸鐨勮��涔?br />

group by

鍙﹀�栦竴涓�甯歌�佺殑浣跨敤涓存椂琛ㄧ殑渚嬪瓙灏辨槸group by锛屾垜浠�鐪嬩竴涓嬩互涓媠ql

select id%10 as m, count(*) as c from t1 group by m;

杩欎釜璇�鍙ュ氨鏄�鏍规嵁t1琛ㄧ殑鏁版嵁锛屾牴鎹甶d%10杩涜�屽垎缁勶紝骞舵寜鐓�m鐨勭粨鏋滄帓搴忓悗杈撳嚭

鍦‥xtra瀛楁�典腑锛屾垜浠�鐪嬪埌浜嗕笁涓�淇℃伅锛�

1锛塙sing index锛岃〃绀鸿繖涓�璇�鍙ヤ娇鐢ㄤ簡瑕嗙洊绱㈠紩锛岄�夋嫨浜嗙储寮?a锛?br /> 2锛塙sing temporary锛岃〃绀轰娇鐢ㄤ簡涓存椂琛�锛�
3锛塙sing filesort锛岃〃绀洪渶瑕佹帓搴忥紱

杩欎釜璇�鍙ョ殑鎵ц�屾祦绋嬫槸杩欐牱鐨勶細

1锛夊垱寤哄唴瀛樹复鏃惰〃锛岃〃閲屾湁瀛楁�祄鍜宑锛屼富閿�鏄痬锛?br /> 2锛夋壂鎻忚〃t1鐨勭储寮昦锛屼緷娆″彇鍑哄彾瀛愯妭鐐逛笂闈㈢殑id鍊硷紝璁＄畻id%10鐨勭粨鏋滐紝璁颁负x锛?/p>

• 濡傛灉涓存椂琛ㄦ病鏈変富閿畑锛屽氨鎻掑叆涓�涓�璁板綍锛坸,1锛夛紱
• 濡傛灉琛ㄤ腑鏈変富閿畑鐨勮�岋紝灏卞皢x杩欎竴琛岀殑c鍊煎姞1锛?/li>

3锛夐亶鍘嗗畬鎴愪箣鍚庯紝鍐嶆牴鎹�瀛楁�祄鍋氭帓搴忥紝寰楀埌缁撴灉

鍐呭瓨涓存椂琛ㄧ殑澶у皬鏄�鏈夐檺鍒剁殑锛屽弬鏁皌mp_table_size灏辨槸鎺у埗杩欎釜鍐呭瓨澶у皬鐨勶紝榛樿�ゆ�?6M锛屽�傛灉鍐呭瓨涓存椂琛ㄥぇ灏忚揪鍒颁簡涓婄嚎锛岃繖鏃跺�欏氨浼氭妸鍐呭瓨涓存椂琛ㄨ浆鎴愮�佺洏涓存椂琛�锛岀�佺洏涓存椂琛ㄧ殑榛樿�ゅ紩鎿庢槸InnoDB锛屽�傛灉琛ㄧ殑鏁版嵁閲忓緢澶э紝寰堝彲鑳芥煡璇㈠氨浼氬崰鐢ㄥぇ閲忕殑纾佺洏绌洪�?br />

鍒版�よ繖绡囧叧浜庢祬璋圡ysql鍦ㄤ粈涔堟儏鍐典笅浼氫娇鐢ㄥ唴閮ㄤ复鏃惰〃鐨勬枃绔犲氨浠嬬粛鍒拌繖浜?鏇村�氱浉鍏矼ysql 鍐呴儴涓存椂琛ㄥ唴瀹硅�锋悳绱㈣剼鏈�涔嬪�朵互鍓嶇殑鏂囩珷鎴栫户缁�娴忚�堜笅闈㈢殑鐩稿叧鏂囩珷甯屾湜澶у�朵互鍚庡�氬�氭敮鎸佽剼鏈�涔嬪�讹紒