MySQL創(chuàng)建內(nèi)部臨時表的所有場景盤點
引言
作者總結(jié)了 MySQL 中所有觸發(fā)使用內(nèi)部臨時表的場景。
作者：劉嘉浩，愛可生團隊 DBA 成員，重度競技游戲愛好者。
愛可生開源社區(qū)出品，原創(chuàng)內(nèi)容未經(jīng)授權(quán)不得隨意使用，轉(zhuǎn)載請聯(lián)系小編并注明來源。
本文約 2000 字，預(yù)計閱讀需要 5 分鐘。
臨時表屬于是一種臨時存放數(shù)據(jù)的表，這類表在會話結(jié)束時會被自動清理掉，但在 MySQL 中存在兩種臨時表，一種是外部臨時表，另外一種是內(nèi)部臨時表。
外部臨時表指的是用戶使用 CREATE TEMPORARY TABLE 手動創(chuàng)建的臨時表。而內(nèi)部臨時表用戶是無法控制的，并不能像外部臨時表一樣使用 CREATE 語句創(chuàng)建，MySQL 的優(yōu)化器會自動選擇是否使用內(nèi)部臨時表。
那么由此引發(fā)一個問題，MySQL 到底在什么時候會使用內(nèi)部臨時表呢？
我們將針對 UNION、GROUP BY 等場景進行分析。
UNION 場景
首先準(zhǔn)備一個測試表。
CREATE TABLE `employees` (
`id` int NOT NULL AUTO_INCREMENT,
`first_name` varchar(100) COLLATE utf8mb4_bin DEFAULT NULL,
`last_name` varchar(100) COLLATE utf8mb4_bin DEFAULT NULL,
`sex` enum('M','F') COLLATE utf8mb4_bin DEFAULT NULL,
`age` int DEFAULT NULL,
`birth_date` date DEFAULT NULL,
`hire_date` date DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `last_name` (`last_name`),
KEY `hire_date` (`hire_date`)
) ENGINE=InnoDB AUTO_INCREMENT=500002 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;準(zhǔn)備插入數(shù)據(jù)的腳本。
#! /usr/bin/python
#! coding=utf-8
import random
import pymysql
from faker import Faker
from datetime import datetime, timedelta
# 創(chuàng)建Faker實例
fake = Faker()
# MySQL連接參數(shù)
db_params = {
'host': 'localhost',
'user': 'root',
'password': 'root',
'db': 'db1',
'port': 3311
}
# 連接數(shù)據(jù)庫
connection = pymysql.connect(**db_params)
# 創(chuàng)建一個新的Cursor實例
cursor = connection.cursor()
# 生成并插入數(shù)據(jù)
for i in range(5000):
id = (i+1)
first_name = fake.first_name()
last_name = fake.last_name()
sex = random.choice(['M', 'F'])
age = random.randint(20, 60)
birth_date = fake.date_between(start_date='-60y', end_date='-20y')
hire_date = fake.date_between(start_date='-30y', end_date='today')
query = f"""INSERT INTO employees (id, first_name, last_name, sex, age, birth_date, hire_date)
VALUES ('{id}', '{first_name}', '{last_name}', '{sex}', {age}, '{birth_date}', '{hire_date}');"""
cursor.execute(query)
# 每1000提交一次事務(wù)
if (i+1) % 1000 == 0:
connection.commit()
# 最后提交事務(wù)
connection.commit()
# 關(guān)閉連接
cursor.close()
connection.close()在創(chuàng)建好測試數(shù)據(jù)后，執(zhí)行一個帶有 UNION 的語句。
root@localhost:mysqld.sock[db1]> explain (select 5000 as res from dual) union (select id from employees 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 | employees | 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 | +----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------+ 3 rows in set, 1 warning (0.00 sec)
可見第二行中 key 值是 PRIMARY，即第二個查詢使用了主鍵 ID。第三行 extra 值是 Using temporary，表明在對上面兩個查詢的結(jié)果集做 UNION 的時候，使用了臨時表。
UNION 操作是將兩個結(jié)果集取并集，不包含重復(fù)項。要做到這一點，只需要先創(chuàng)建一個只有主鍵的內(nèi)存內(nèi)部臨時表，并將第一個子查詢的值插入進這個表中，這樣就可以避免了重復(fù)的問題。因為值 5000 早已存在臨時表中，而第二個子查詢的值 5000 就會因為沖突無法插入，只能插入下一個值 4999。
UNION ALL 與 UNION 不同，并不會使用內(nèi)存臨時表，下列例子是使用 UNION ALL 的執(zhí)行計劃。
root@localhost:mysqld.sock[db1]> explain (select 5000 as res from dual) union all (select id from employees 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 | employees | NULL | index | NULL | PRIMARY | 4 | NULL | 2 | 100.00 | Backward index scan; Using index | +----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------+ 2 rows in set, 1 warning (0.01 sec)
因為 UNION ALL 并不需要去重，所以優(yōu)化器不需要新建一個臨時表做去重的動作，執(zhí)行的時候只需要按順序執(zhí)行兩個子查詢并將子查詢放在一個結(jié)果集里就好了。
可以看到，在實現(xiàn) UNION 的語義上，臨時表起到的是一個暫時存儲數(shù)據(jù)并做去重的動作的這么一種作用的存在。
GROUP BY
除了 UNION 之外，還有一個比較常用的子句 GROUP BY 也會使用到內(nèi)部臨時表。下列例子展示了一個使用 ID 列求余并進行分組統(tǒng)計，且按照余數(shù)大小排列。
root@localhost:mysqld.sock[db1]> explain select id%5 as complementation,count(*) from employees group by complementation order by 1; +----+-------------+-----------+------------+-------+-----------------------------+-----------+---------+------+------+----------+----------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+-------+-----------------------------+-----------+---------+------+------+----------+----------------------------------------------+ | 1 | SIMPLE | employees | NULL | index | PRIMARY,last_name,hire_date | hire_date | 4 | NULL | 5000 | 100.00 | Using index; Using temporary; Using filesort | +----+-------------+-----------+------------+-------+-----------------------------+-----------+---------+------+------+----------+----------------------------------------------+ 1 row in set, 1 warning (0.00 sec)
可以看到 extra 的值是 using index、using temporary、using filesort; 這三個值分別是：使用索引、使用臨時表、使用了排序。
注意：在 MySQL 5.7 版本中 GROUP BY 會默認(rèn)按照分組字段進行排序，在 MySQL 8.0 版本中取消了默認(rèn)排序功能，所以此處使用了 ORDER BY 進行復(fù)現(xiàn)。
對于 GROUP BY 來說，上述的語句執(zhí)行后，會先創(chuàng)建一個內(nèi)存內(nèi)部臨時表，存儲 complementation 與 count(*) 的值，主鍵為 complementation。然后按照索引 hire_date 對應(yīng)的 ID 值依次計算 id%5 的值記為 x，如果臨時表中沒有主鍵為 x 的值，那么將會在臨時表中插入記錄；如果存在則累加這一行的計數(shù) count(*)。在遍歷完成上述的操作后，再按照 ORDER BY 的規(guī)則對 complementation 進行排序。
在使用 GROUP BY 進行分組或使用 DISTINCT 進行去重時，MySQL 都給我們提供了使用 hint 去避免使用內(nèi)存內(nèi)部臨時表的方法。
| hint | 解釋 |
|---|---|
| SQL_BIG_RESULT | 顯式指定該 SQL 語句使用磁盤內(nèi)部臨時表，適合大數(shù)據(jù)量的操作；適用于 InnoDB 引擎與 Memory 引擎。 |
| SQL_SMALL_RESULT | 顯式指定該 SQL 語句使用內(nèi)存內(nèi)部臨時表，速度更快，適合小數(shù)據(jù)量的操作；適用于 Memory 引擎。 |
下列是一個使用了 SQL_BIG_RESULT 的例子。
root@localhost:mysqld.sock[db1]> explain select SQL_BIG_RESULT id%5 as complementation,count(*) from employees group by complementation order by 1; +----+-------------+-----------+------------+-------+-----------------------------+-----------+---------+------+------+----------+-----------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+-------+-----------------------------+-----------+---------+------+------+----------+-----------------------------+ | 1 | SIMPLE | employees | NULL | index | PRIMARY,last_name,hire_date | hire_date | 4 | NULL | 5000 | 100.00 | Using index; Using filesort | +----+-------------+-----------+------------+-------+-----------------------------+-----------+---------+------+------+----------+-----------------------------+ 1 row in set, 1 warning (0.00 sec)
從執(zhí)行計劃中我們可以看出，使用了 SQL_BIG_RESULT 這個 hint 進行查詢后，在 extra 列中 Using Temporary 字樣已經(jīng)不見了，即避免了使用內(nèi)存內(nèi)部臨時表。
其他場景
當(dāng)然，除了上述兩個例子外，MySQL 還會在下列情況下創(chuàng)建內(nèi)部臨時表：
- 對于UNION語句的評估，但有一些后續(xù)描述中的例外情況。
- 對于某些視圖的評估，例如使用 TEMPTABLE 算法、UNION 或聚合的視圖。
- 對派生表的評估。
- 對公共表達式的評估。
- 用于子查詢或半連接材料化的表。
- 對包含 ORDER BY 子句和不同 GROUP BY 子句的語句的評估，或者對于其中 ORDER BY 或 GROUP BY 子句包含來自連接隊列中第一個表以外的表的列的語句。
- 對于 DISTINCT 與 ORDER BY 的組合，可能需要一個臨時表。
- 對于使用 SQL_SMALL_RESULT 修飾符的查詢，MySQL 使用內(nèi)存中的臨時表，除非查詢還包含需要在磁盤上存儲的元素。
- 為了評估從同一表中選取并插入的 INSERT … SELECT 語句，MySQL 創(chuàng)建一個內(nèi)部臨時表來保存 SELECT 的行，然后將這些行插入目標(biāo)表中。
- 對于多表 UPDATE 語句的評估。
- 對于 GROUP_CONCAT() 或 COUNT(DISTINCT) 表達式的評估。
- 窗口函數(shù)的評估，根據(jù)需要使用臨時表。
值得注意的是，某些查詢條件 MySQL 不允許使用內(nèi)存內(nèi)部臨時表，在這種情況下，服務(wù)器會使用磁盤內(nèi)部臨時表。
- 表中存在 BLOB 或 TEXT 列。MySQL 8.0 中用于內(nèi)存內(nèi)部臨時表的默認(rèn)存儲引擎 TempTable 從 8.0.13 開始支持二進制大對象類型。
- 如果使用了 UNION 或 UNION ALL，SELECT 的列表中存在任何最大長度超過 512 的字符串列（對于二進制字符串為字節(jié)，對于非二進制字符串為字符）。
- SHOW COLUMNS 和 DESCRIBE 語句使用 BLOB 作為某些列的類型，因此用于此結(jié)果的臨時表是將會是磁盤內(nèi)部臨時表。
以上就是MySQL創(chuàng)建內(nèi)部臨時表的所有場景盤點的詳細(xì)內(nèi)容，更多關(guān)于MySQL創(chuàng)建內(nèi)部臨時表的資料請關(guān)注腳本之家其它相關(guān)文章！
相關(guān)文章
這篇文章主要介紹了MySQL?根據(jù)表名稱生成完整select語句,本文通過實例代碼給大家介紹的非常詳細(xì)，對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值，需要的朋友可以參考下2023-06-06
MySQL的隔離級別是指數(shù)據(jù)庫事務(wù)的隔離程度,用于控制并發(fā)事務(wù)之間的相互影響,本文就詳細(xì)的介紹一下如何修改mysql的隔離級別,感興趣的可以了解一下2023-08-08
當(dāng)前非安裝版是從MySQL.com下載來的v5.1.40;下載下來的是一個ZIP壓縮包，解壓到C:\MySQL5.1.40目錄，接下來，設(shè)置系統(tǒng)環(huán)境變量，好在CMD命令行下使用MySQL。2010-02-02
服務(wù)從mysql遷移達夢數(shù)據(jù)庫的實現(xiàn)方案
達夢數(shù)據(jù)庫(DM8)作為主流國產(chǎn)關(guān)系型數(shù)據(jù)庫,兼容MySQL協(xié)議和大部分語法,這篇文章主要介紹了服務(wù)從mysql遷移達夢數(shù)據(jù)庫的實現(xiàn)方案,文中通過代碼介紹的非常詳細(xì),需要的朋友可以參考下2026-05-05
這篇文章主要為大家詳細(xì)介紹了mysql 5.7.25 安裝配置方法圖文教程，文中安裝步驟介紹的非常詳細(xì)，具有一定的參考價值，感興趣的小伙伴們可以參考一下2019-10-10
這篇文章給大家介紹MySQL中日期型單行函數(shù)的相關(guān)知識，包括返回當(dāng)前日期時精確到日、秒的參考代碼，代碼簡單易懂，對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值，需要的朋友參考下吧2021-06-06
mysql group_concat()函數(shù)用法總結(jié)
這篇文章主要介紹了mysql group_concat()函數(shù)用法,結(jié)合實例形式較為詳細(xì)的group_concat()函數(shù)的功能、使用方法與相關(guān)注意事項,需要的朋友可以參考下2016-06-06
MySQL數(shù)據(jù)庫中正則表達式(Regex)和like的區(qū)別詳析
MySQL正則表達式是一種強大的文本匹配工具,允許執(zhí)行復(fù)雜的字符串搜索和處理,這篇文章主要介紹了MySQL數(shù)據(jù)庫中正則表達式(Regex)和like區(qū)別的相關(guān)資料,文中通過代碼需要的朋友可以參考下2025-11-11
本文主要介紹了MySQL行格式的實現(xiàn),行格式包含了行格式包括compact、redundant、dynamic和compressed四種,文中通過示例代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2024-11-11
MySQL之MHA高可用配置及故障切換實現(xiàn)詳細(xì)部署步驟
這篇文章主要介紹了MySQL之MHA高可用配置及故障切換實現(xiàn)詳細(xì)部署步驟，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2021-03-03