色噜噜狠狠成人中文综合,少妇高潮无套内谢,色天天天综合色天天

UNION

UNION語義：取兩個子查詢結(jié)果的并集，重復(fù)的行只保留一行

表初始化

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

執(zhí)行語句

(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，Using temporary

表示在對子查詢的結(jié)果做UNION RESULT的時候，使用了臨時表

UNION RESULT

創(chuàng)建一個內(nèi)存臨時表，這個內(nèi)存臨時表只有一個整型字段f，并且f為主鍵
執(zhí)行第一個子查詢，得到1000，并存入內(nèi)存臨時表中
執(zhí)行第二個子查詢
- 拿到第一行id=1000，試圖插入到內(nèi)存臨時表，但由于1000這個值已經(jīng)存在于內(nèi)存臨時表
  - 違反唯一性約束，插入失敗，繼續(xù)執(zhí)行
- 拿到第二行id=999，插入內(nèi)存臨時表成功
從內(nèi)存臨時表中按行取出數(shù)據(jù)，返回結(jié)果，并刪除內(nèi)存臨時表，結(jié)果中包含id=1000和id=999兩行
內(nèi)存臨時表起到了暫存數(shù)據(jù)的作用，還用到了內(nèi)存臨時表主鍵id的唯一性約束，實(shí)現(xiàn)UNION的語義

UNION ALL

UNION ALL沒有去重的語義，一次執(zhí)行子查詢，得到的結(jié)果直接發(fā)給客戶端，不需要內(nèi)存臨時表

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

內(nèi)存充足

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

執(zhí)行語句

-- MySQL 5.6上執(zhí)行
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：表示使用了覆蓋索引，選擇了索引a，不需要回表

Using temporary：表示使用了臨時表

Using filesort：表示需要排序

執(zhí)行過程

創(chuàng)建內(nèi)存臨時表，表里有兩個字段m和c，m為主鍵
掃描t1的索引a，依次取出葉子節(jié)點(diǎn)上的id值，計算id%10，記為x
- 如果內(nèi)存臨時表中沒有主鍵為x的行，插入一行記錄(x,1)
- 如果內(nèi)存臨時表中有主鍵為x的行，將x這一行的c值加1
遍歷完成后，再根據(jù)字段m做排序，得到結(jié)果集返回給客戶端

排序過程

ORDER BY NULL

-- 跳過最后的排序階段，直接從臨時表中取回數(shù)據(jù)
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中的數(shù)據(jù)是從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 |
+------+-----+

內(nèi)存不足

SET tmp_table_size=1024;

執(zhí)行語句

-- 內(nèi)存臨時表的上限為1024 Bytes，但內(nèi)存臨時表不能完全放下100行數(shù)據(jù)，內(nèi)存臨時表會轉(zhuǎn)成磁盤臨時表，默認(rèn)采用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 |
+------+----+

優(yōu)化方案

優(yōu)化索引

不論使用內(nèi)存臨時表還是磁盤臨時表，GROUP BY都需要構(gòu)造一個帶唯一索引的表，執(zhí)行代價較高

需要臨時表的原因：每一行的id%100是無序的，因此需要臨時表，來記錄并統(tǒng)計結(jié)果

如果可以確保輸入的數(shù)據(jù)是有序的，那么計算GROUP BY時，只需要
從左到右順序掃描，依次累加即可

當(dāng)碰到第一個1的時候，已經(jīng)累積了X個0，結(jié)果集里的第一行為(0,X)
當(dāng)碰到第一個2的時候，已經(jīng)累積了Y個1，結(jié)果集里的第一行為(1,Y)
整個過程不需要臨時表，也不需要排序

-- MySQL 5.7上執(zhí)行
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

直接排序

一個GROUP BY語句需要放到臨時表的數(shù)據(jù)量特別大，還是按照先放在內(nèi)存臨時表，再退化成磁盤臨時表

可以直接用磁盤臨時表的形式，在GROUP BY語句中SQL_BIG_RESULT（告訴優(yōu)化器涉及的數(shù)據(jù)量很大）

磁盤臨時表原本采用B+樹存儲，存儲效率還不如數(shù)組，優(yōu)化器看到SQL_BIG_RESULT，會直接用數(shù)組存儲

即放棄使用臨時表，直接進(jìn)入排序階段

執(zhí)行過程

-- 沒有再使用臨時表，而是直接使用了排序算法
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 |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------+

初始化sort_buffer，確定放入一個整型字段，記為m

掃描t1的索引a，依次取出里面的id值，將id%100的值放入sort_buffer

掃描完成后，對sort_buffer的字段m做排序（sort_buffer內(nèi)存不夠時，會利用磁盤臨時文件輔助排序）

排序完成后，得到一個有序數(shù)組，遍歷有序數(shù)組，得到每個值出現(xiàn)的次數(shù)（類似上面優(yōu)化索引的方式）

對比DISTINCT

-- 標(biāo)準(zhǔn)SQL，SELECT部分添加一個聚合函數(shù)COUNT(*)
SELECT a,COUNT(*) FROM t GROUP BY a ORDER BY NULL;
-- 非標(biāo)準(zhǔn)SQL
SELECT a FROM t GROUP BY a ORDER BY NULL;
SELECT DISTINCT a FROM t;

標(biāo)準(zhǔn)SQL：按照字段a分組，計算每組a出現(xiàn)的次數(shù)

非標(biāo)準(zhǔn)SQL：沒有了COUNT(*)，不再需要執(zhí)行計算總數(shù)的邏輯

按照字段a分組，相同的a的值只返回一行，與DISTINCT語義一致

如果不需要執(zhí)行聚合函數(shù) ，DISTINCT和GROUP BY的語義、執(zhí)行流程和執(zhí)行性能是相同的

創(chuàng)建一個臨時表，臨時表有一個字段a，并且在這個字段a上創(chuàng)建一個唯一索引
遍歷表t，依次取出數(shù)據(jù)插入臨時表中
- 如果發(fā)現(xiàn)唯一鍵沖突，就跳過
- 否則插入成功
遍歷完成后，將臨時表作為結(jié)果集返回給客戶端

小結(jié)

用到內(nèi)部臨時表的場景
- 如果語句執(zhí)行過程中可以一邊讀數(shù)據(jù)，一邊得到結(jié)果，是不需要額外內(nèi)存的
- 否則需要額外內(nèi)存來保存中間結(jié)果
join_buffer是無序數(shù)組，sort_buffer是有序數(shù)組，臨時表是二維表結(jié)構(gòu)
如果執(zhí)行邏輯需要用到二維表特性，就會優(yōu)先考慮使用臨時表如果對GROUP BY語句的結(jié)果沒有明確的排序要求，加上ORDER BY NULL（MySQL 5.6）
盡量讓GROUP BY過程用上索引，確認(rèn)EXPLAIN結(jié)果沒有Using temporary和Using filesort
如果GROUP BY需要統(tǒng)計的數(shù)據(jù)量不大，盡量使用內(nèi)存臨時表（可以適當(dāng)調(diào)大tmp_table_size）
如果數(shù)據(jù)量實(shí)在太大，使用SQL_BIG_RESULT來告訴優(yōu)化器直接使用排序算法（跳過臨時表）

參考資料

《MySQL實(shí)戰(zhàn)45講》

到此這篇關(guān)于MySQL內(nèi)部臨時表的具體使用的文章就介紹到這了,更多相關(guān)MySQL內(nèi)部臨時表內(nèi)容請搜索本站以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持本站！

美國服務(wù)器租用

版權(quán)聲明：本站文章來源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請保持原文完整并注明來源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非maisonbaluchon.cn所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來，僅供學(xué)習(xí)參考，不代表本站立場，如有內(nèi)容涉嫌侵權(quán)，請聯(lián)系alex-e#qq.com處理。

相關(guān)文章

mysql學(xué)習(xí)筆記之表的基本操作

centos編譯安裝mysql 5.6及安裝多個mysql實(shí)例詳解

mysql 5.7.11 winx64.zip安裝配置方法圖文教程

mysql 5.7.17 winx64.zip安裝配置方法圖文教程

CentOS安裝mysql5.7 及簡單配置教程詳解

MySQL 5.7 zip版本(zip版)安裝配置步驟詳解

MySQL5.6.31 winx64.zip 安裝配置教程詳解

MySQL注入繞開過濾的技巧總結(jié)

一次Mysql死鎖排查過程的全紀(jì)錄

Windows10 64位安裝MySQL5.6.35的圖文教程