软件开发培训班 >> 编程开发 >> MySQL


MySQL内部临时表的具体使用

UNION

UNION璇箟锛氬彇涓や釜瀛愭煡璇㈢粨鏋滅殑骞堕泦锛岄噸澶嶇殑琛屽彧淇濈暀涓�琛?/p>

琛ㄥ垵濮嬪寲

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();

鎵ц璇彞

(SELECT 1000 AS f) UNION (SELECT id FROM t1 ORDER BY id DESC LIMIT 2);

mysql> EXPLAIN (SELECT 1000 AS f) UNION (SELECT id FROM t1 ORDER BY id DESC LIMIT 2);
+----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------+
| id | select_type  | table      | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                            |
+----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------+
|  1 | PRIMARY      | NULL       | NULL       | NULL  | NULL          | NULL    | NULL    | NULL | NULL |     NULL | No tables used                   |
|  2 | UNION        | t1         | NULL       | index | NULL          | PRIMARY | 4       | NULL |    2 |   100.00 | Backward index scan; Using index |
| NULL | UNION RESULT | <union1,2> | NULL       | ALL   | NULL          | NULL    | NULL    | NULL | NULL |     NULL | Using temporary                  |
+----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------+

绗簩琛岀殑Key=PRIMARY锛?code>Using temporary

  • 琛ㄧず鍦ㄥ瀛愭煡璇㈢殑缁撴灉鍋?code>UNION RESULT鐨勬椂鍊欙紝浣跨敤浜嗕复鏃惰〃

UNION RESULT

  • 鍒涘缓涓�涓唴瀛樹复鏃惰〃锛岃繖涓唴瀛樹复鏃惰〃鍙湁涓�涓暣鍨嬪瓧娈礷锛屽苟涓攆涓轰富閿?/li>
  • 鎵ц绗竴涓瓙鏌ヨ锛屽緱鍒?000锛屽苟瀛樺叆鍐呭瓨涓存椂琛ㄤ腑
  • 鎵ц绗簩涓瓙鏌ヨ
    • 鎷垮埌绗竴琛宨d=1000锛岃瘯鍥炬彃鍏ュ埌鍐呭瓨涓存椂琛紝浣嗙敱浜?000杩欎釜鍊煎凡缁忓瓨鍦ㄤ簬鍐呭瓨涓存椂琛?
      • 杩濆弽鍞竴鎬х害鏉燂紝鎻掑叆澶辫触锛岀户缁墽琛?/li>
    • 鎷垮埌绗簩琛宨d=999锛屾彃鍏ュ唴瀛樹复鏃惰〃鎴愬姛
  • 浠庡唴瀛樹复鏃惰〃涓寜琛屽彇鍑烘暟鎹紝杩斿洖缁撴灉锛屽苟鍒犻櫎鍐呭瓨涓存椂琛紝缁撴灉涓寘鍚玦d=1000鍜宨d=999涓よ
  • 鍐呭瓨涓存椂琛ㄨ捣鍒颁簡鏆傚瓨鏁版嵁鐨勪綔鐢紝杩樼敤鍒颁簡鍐呭瓨涓存椂琛ㄤ富閿甶d鐨勫敮涓�鎬х害鏉燂紝瀹炵幇UNION鐨勮涔?/li>

UNION ALL

UNION ALL娌℃湁鍘婚噸鐨勮涔夛紝涓�娆℃墽琛屽瓙鏌ヨ锛屽緱鍒扮殑缁撴灉鐩存帴鍙戠粰瀹㈡埛绔紝涓嶉渶瑕佸唴瀛樹复鏃惰〃

mysql> EXPLAIN (SELECT 1000 AS f) UNION ALL (SELECT id FROM t1 ORDER BY id DESC LIMIT 2);
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                            |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------+
|  1 | PRIMARY     | NULL  | NULL       | NULL  | NULL          | NULL    | NULL    | NULL | NULL |     NULL | No tables used                   |
|  2 | UNION       | t1    | NULL       | index | NULL          | PRIMARY | 4       | NULL |    2 |   100.00 | Backward index scan; Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------+

GROUP BY

鍐呭瓨鍏呰冻

-- 16777216 Bytes = 16 MB
mysql> SHOW VARIABLES like '%tmp_table_size%';
+----------------+----------+
| Variable_name  | Value    |
+----------------+----------+
| tmp_table_size | 16777216 |
+----------------+----------+

鎵ц璇彞

-- MySQL 5.6涓婃墽琛?
mysql> EXPLAIN SELECT id%10 AS m, COUNT(*) AS c FROM t1 GROUP BY m;
+----+-------------+-------+-------+---------------+------+---------+------+------+----------------------------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                                        |
+----+-------------+-------+-------+---------------+------+---------+------+------+----------------------------------------------+
|  1 | SIMPLE      | t1    | index | PRIMARY,a     | a    | 5       | NULL | 1000 | Using index; Using temporary; Using filesort |
+----+-------------+-------+-------+---------------+------+---------+------+------+----------------------------------------------+

mysql> SELECT id%10 AS m, COUNT(*) AS c FROM t1 GROUP BY m;
+------+-----+
| m    | c   |
+------+-----+
|    0 | 100 |
|    1 | 100 |
|    2 | 100 |
|    3 | 100 |
|    4 | 100 |
|    5 | 100 |
|    6 | 100 |
|    7 | 100 |
|    8 | 100 |
|    9 | 100 |
+------+-----+

Using index锛氳〃绀轰娇鐢ㄤ簡瑕嗙洊绱㈠紩锛岄�夋嫨浜嗙储寮昦锛屼笉闇�瑕佸洖琛?/p>

Using temporary锛氳〃绀轰娇鐢ㄤ簡涓存椂琛?/p>

Using filesort锛氳〃绀洪渶瑕佹帓搴?/p>

鎵ц杩囩▼

  • 鍒涘缓鍐呭瓨涓存椂琛紝琛ㄩ噷鏈変袱涓瓧娈祄鍜宑锛宮涓轰富閿?/li>
  • 鎵弿t1鐨勭储寮昦锛屼緷娆″彇鍑哄彾瀛愯妭鐐逛笂鐨刬d鍊硷紝璁＄畻id%10锛岃涓簒
    • 濡傛灉鍐呭瓨涓存椂琛ㄤ腑娌℃湁涓婚敭涓簒鐨勮锛屾彃鍏ヤ竴琛岃褰?code>(x,1)
    • 濡傛灉鍐呭瓨涓存椂琛ㄤ腑鏈変富閿负x鐨勮锛屽皢x杩欎竴琛岀殑c鍊煎姞1
  • 閬嶅巻瀹屾垚鍚庯紝鍐嶆牴鎹瓧娈祄鍋氭帓搴忥紝寰楀埌缁撴灉闆嗚繑鍥炵粰瀹㈡埛绔?/li>

鎺掑簭杩囩▼

ORDER BY NULL

-- 璺宠繃鏈�鍚庣殑鎺掑簭闃舵锛岀洿鎺ヤ粠涓存椂琛ㄤ腑鍙栧洖鏁版嵁
mysql> EXPLAIN SELECT id%10 AS m, COUNT(*) AS c FROM t1 GROUP BY m ORDER BY NULL;
+----+-------------+-------+-------+---------------+------+---------+------+------+------------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                        |
+----+-------------+-------+-------+---------------+------+---------+------+------+------------------------------+
|  1 | SIMPLE      | t1    | index | PRIMARY,a     | a    | 5       | NULL | 1000 | Using index; Using temporary |
+----+-------------+-------+-------+---------------+------+---------+------+------+------------------------------+

-- t1涓殑鏁版嵁鏄粠1寮�濮嬬殑
mysql> SELECT id%10 AS m, COUNT(*) AS c FROM t1 GROUP BY m ORDER BY NULL;
+------+-----+
| m    | c   |
+------+-----+
|    1 | 100 |
|    2 | 100 |
|    3 | 100 |
|    4 | 100 |
|    5 | 100 |
|    6 | 100 |
|    7 | 100 |
|    8 | 100 |
|    9 | 100 |
|    0 | 100 |
+------+-----+

鍐呭瓨涓嶈冻

SET tmp_table_size=1024;

鎵ц璇彞

-- 鍐呭瓨涓存椂琛ㄧ殑涓婇檺涓?024 Bytes锛屼絾鍐呭瓨涓存椂琛ㄤ笉鑳藉畬鍏ㄦ斁涓?00琛屾暟鎹紝鍐呭瓨涓存椂琛ㄤ細杞垚纾佺洏涓存椂琛紝榛樿閲囩敤InnoDB寮曟搸
-- 濡傛灉t1寰堝ぇ锛岃繖涓煡璇㈤渶瑕佺殑纾佺洏涓存椂琛ㄥ氨浼氬崰鐢ㄥぇ閲忕殑纾佺洏绌洪棿
mysql> SELECT id%100 AS m, count(*) AS c FROM t1 GROUP BY m ORDER BY NULL LIMIT 10;
+------+----+
| m    | c  |
+------+----+
|    1 | 10 |
|    2 | 10 |
|    3 | 10 |
|    4 | 10 |
|    5 | 10 |
|    6 | 10 |
|    7 | 10 |
|    8 | 10 |
|    9 | 10 |
|   10 | 10 |
+------+----+

浼樺寲鏂规

浼樺寲绱㈠紩

涓嶈浣跨敤鍐呭瓨涓存椂琛ㄨ繕鏄鐩樹复鏃惰〃锛?code>GROUP BY閮介渶瑕佹瀯閫犱竴涓甫鍞竴绱㈠紩鐨勮〃锛?em>鎵ц浠ｄ环杈冮珮

闇�瑕佷复鏃惰〃鐨勫師鍥狅細姣忎竴琛岀殑id%100鏄棤搴忕殑锛屽洜姝ら渶瑕佷复鏃惰〃锛屾潵璁板綍骞剁粺璁＄粨鏋?/p>

濡傛灉鍙互纭繚杈撳叆鐨勬暟鎹槸鏈夊簭鐨勶紝閭ｄ箞璁＄畻GROUP BY鏃讹紝鍙渶瑕?br /> 浠庡乏鍒板彸椤哄簭鎵弿锛屼緷娆＄疮鍔犲嵆鍙?/p>

  • 褰撶鍒扮涓�涓?鐨勬椂鍊欙紝宸茬粡绱Н浜哫涓?锛岀粨鏋滈泦閲岀殑绗竴琛屼负(0,X)
  • 褰撶鍒扮涓�涓?鐨勬椂鍊欙紝宸茬粡绱Н浜哬涓?锛岀粨鏋滈泦閲岀殑绗竴琛屼负(1,Y)
  • 鏁翠釜杩囩▼涓嶉渶瑕佷复鏃惰〃锛屼篃涓嶉渶瑕佹帓搴?/li> 
-- MySQL 5.7涓婃墽琛?
ALTER TABLE t1 ADD COLUMN z INT GENERATED ALWAYS AS(id % 100), ADD INDEX(z);
-- 浣跨敤浜嗚鐩栫储寮曪紝涓嶉渶瑕佷复鏃惰〃锛屼篃涓嶉渶瑕佹帓搴?
mysql> EXPLAIN SELECT z, COUNT(*) AS c FROM t1 GROUP BY z;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | index | z             | z    | 5       | NULL | 1000 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
2

鐩存帴鎺掑簭

涓�涓?code>GROUP BY璇彞闇�瑕佹斁鍒颁复鏃惰〃鐨勬暟鎹噺鐗瑰埆澶э紝杩樻槸鎸夌収鍏堟斁鍦ㄥ唴瀛樹复鏃惰〃锛屽啀閫�鍖栨垚纾佺洏涓存椂琛?/p>

鍙互鐩存帴鐢ㄧ鐩樹复鏃惰〃鐨勫舰寮忥紝鍦?code>GROUP BY璇彞涓?code>SQL_BIG_RESULT锛堝憡璇変紭鍖栧櫒娑夊強鐨勬暟鎹噺寰堝ぇ锛?/p>

纾佺洏涓存椂琛ㄥ師鏈噰鐢˙+鏍戝瓨鍌紝瀛樺偍鏁堢巼杩樹笉濡傛暟缁勶紝浼樺寲鍣ㄧ湅鍒?code>SQL_BIG_RESULT锛屼細鐩存帴鐢ㄦ暟缁勫瓨鍌?/p>

  • 鍗虫斁寮冧娇鐢ㄤ复鏃惰〃锛?em>鐩存帴杩涘叆鎺掑簭闃舵

鎵ц杩囩▼

-- 娌℃湁鍐嶄娇鐢ㄤ复鏃惰〃锛岃�屾槸鐩存帴浣跨敤浜嗘帓搴忕畻娉?
mysql> EXPLAIN SELECT SQL_BIG_RESULT id%100 AS m, COUNT(*) AS c FROM t1 GROUP BY m;
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                       |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | t1    | index | PRIMARY,a     | a    | 5       | NULL | 1000 | Using index; Using filesort |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------+

鍒濆鍖?code>sort_buffer锛岀‘瀹氭斁鍏ヤ竴涓暣鍨嬪瓧娈碉紝璁颁负m

鎵弿t1鐨勭储寮昦锛屼緷娆″彇鍑洪噷闈㈢殑id鍊硷紝灏唅d%100鐨勫�兼斁鍏?code>sort_buffer

鎵弿瀹屾垚鍚庯紝瀵?code>sort_buffer鐨勫瓧娈祄鍋氭帓搴忥紙sort_buffer鍐呭瓨涓嶅鏃讹紝浼氬埄鐢ㄧ鐩樹复鏃舵枃浠惰緟鍔╂帓搴忥級

鎺掑簭瀹屾垚鍚庯紝寰楀埌涓�涓湁搴忔暟缁勶紝閬嶅巻鏈夊簭鏁扮粍锛屽緱鍒版瘡涓�煎嚭鐜扮殑娆℃暟锛堢被浼间笂闈紭鍖栫储寮曠殑鏂瑰紡锛?/p>

瀵规瘮DISTINCT

-- 鏍囧噯SQL锛孲ELECT閮ㄥ垎娣诲姞涓�涓仛鍚堝嚱鏁癈OUNT(*)
SELECT a,COUNT(*) FROM t GROUP BY a ORDER BY NULL;
-- 闈炴爣鍑哠QL
SELECT a FROM t GROUP BY a ORDER BY NULL;

SELECT DISTINCT a FROM t;

鏍囧噯SQL锛氭寜鐓у瓧娈礱鍒嗙粍锛岃绠楁瘡缁刟鍑虹幇鐨勬鏁?/p>

闈炴爣鍑哠QL锛氭病鏈変簡COUNT(*)锛屼笉鍐嶉渶瑕佹墽琛岃绠楁�绘暟鐨勯�昏緫

  • 鎸夌収瀛楁a鍒嗙粍锛岀浉鍚岀殑a鐨勫�煎彧杩斿洖涓�琛岋紝涓?code>DISTINCT璇箟涓�鑷?/li>

濡傛灉涓嶉渶瑕佹墽琛岃仛鍚堝嚱鏁?锛?code>DISTINCT鍜?code>GROUP BY鐨勮涔夈�佹墽琛屾祦绋嬪拰鎵ц鎬ц兘鏄浉鍚岀殑

  • 鍒涘缓涓�涓复鏃惰〃锛屼复鏃惰〃鏈変竴涓瓧娈礱锛屽苟涓斿湪杩欎釜瀛楁a涓婂垱寤轰竴涓敮涓�绱㈠紩
  • 閬嶅巻琛╰锛屼緷娆″彇鍑烘暟鎹彃鍏ヤ复鏃惰〃涓?
    • 濡傛灉鍙戠幇鍞竴閿啿绐侊紝灏辫烦杩?/li>
    • 鍚﹀垯鎻掑叆鎴愬姛
  • 閬嶅巻瀹屾垚鍚庯紝灏嗕复鏃惰〃浣滀负缁撴灉闆嗚繑鍥炵粰瀹㈡埛绔?/li>

灏忕粨

  • 鐢ㄥ埌鍐呴儴涓存椂琛ㄧ殑鍦烘櫙
    • 濡傛灉璇彞鎵ц杩囩▼涓彲浠ヤ竴杈硅鏁版嵁锛屼竴杈瑰緱鍒扮粨鏋滐紝鏄笉闇�瑕侀澶栧唴瀛樼殑
    • 鍚﹀垯闇�瑕侀澶栧唴瀛樻潵淇濆瓨涓棿缁撴灉
  • join_buffer鏄棤搴忔暟缁勶紝sort_buffer鏄湁搴忔暟缁勶紝涓存椂琛ㄦ槸浜岀淮琛ㄧ粨鏋?/li>
  • 濡傛灉鎵ц閫昏緫闇�瑕佺敤鍒颁簩缁磋〃鐗规�э紝灏变細浼樺厛鑰冭檻浣跨敤涓存椂琛ㄥ鏋滃GROUP BY璇彞鐨勭粨鏋滄病鏈夋槑纭殑鎺掑簭瑕佹眰锛屽姞涓?code>ORDER BY NULL锛圡ySQL 5.6锛?/li>
  • 灏介噺璁?code>GROUP BY杩囩▼鐢ㄤ笂绱㈠紩锛岀‘璁XPLAIN缁撴灉娌℃湁Using temporary鍜?code>Using filesort
  • 濡傛灉GROUP BY闇�瑕佺粺璁＄殑鏁版嵁閲忎笉澶э紝灏介噺浣跨敤鍐呭瓨涓存椂琛紙鍙互閫傚綋璋冨ぇtmp_table_size锛?/li>
  • 濡傛灉鏁版嵁閲忓疄鍦ㄥお澶э紝浣跨敤SQL_BIG_RESULT鏉ュ憡璇変紭鍖栧櫒鐩存帴浣跨敤鎺掑簭绠楁硶锛堣烦杩囦复鏃惰〃锛?/li>

鍙傝�冭祫鏂?/h2>

銆?a target="_blank" href="https://www.jb51.net/books/790640.html">MySQL瀹炴垬45璁?/a>銆?/p>

鍒版杩欑瘒鍏充簬MySQL鍐呴儴涓存椂琛ㄧ殑鍏蜂綋浣跨敤鐨勬枃绔犲氨浠嬬粛鍒拌繖浜?鏇村鐩稿叧MySQL鍐呴儴涓存椂琛ㄥ唴瀹硅鎼滅储鑴氭湰涔嬪浠ュ墠鐨勬枃绔犳垨缁х画娴忚涓嬮潰鐨勭浉鍏虫枃绔犲笇鏈涘ぇ瀹朵互鍚庡澶氭敮鎸佽剼鏈箣瀹讹紒