MySQL創(chuàng)建內(nèi)部臨時(shí)表的所有場(chǎng)景盤(pán)點(diǎn)
引言
作者總結(jié)了 MySQL 中所有觸發(fā)使用內(nèi)部臨時(shí)表的場(chǎng)景。
作者：劉嘉浩，愛(ài)可生團(tuán)隊(duì) DBA 成員，重度競(jìng)技游戲愛(ài)好者。
愛(ài)可生開(kāi)源社區(qū)出品，原創(chuàng)內(nèi)容未經(jīng)授權(quán)不得隨意使用，轉(zhuǎn)載請(qǐng)聯(lián)系小編并注明來(lái)源。
本文約 2000 字，預(yù)計(jì)閱讀需要 5 分鐘。
臨時(shí)表屬于是一種臨時(shí)存放數(shù)據(jù)的表，這類(lèi)表在會(huì)話結(jié)束時(shí)會(huì)被自動(dòng)清理掉，但在 MySQL 中存在兩種臨時(shí)表，一種是外部臨時(shí)表，另外一種是內(nèi)部臨時(shí)表。
外部臨時(shí)表指的是用戶(hù)使用 CREATE TEMPORARY TABLE 手動(dòng)創(chuàng)建的臨時(shí)表。而內(nèi)部臨時(shí)表用戶(hù)是無(wú)法控制的，并不能像外部臨時(shí)表一樣使用 CREATE 語(yǔ)句創(chuàng)建，MySQL 的優(yōu)化器會(huì)自動(dòng)選擇是否使用內(nèi)部臨時(shí)表。
那么由此引發(fā)一個(gè)問(wèn)題，MySQL 到底在什么時(shí)候會(huì)使用內(nèi)部臨時(shí)表呢？
我們將針對(duì) UNION、GROUP BY 等場(chǎng)景進(jìn)行分析。
UNION 場(chǎng)景
首先準(zhǔn)備一個(gè)測(cè)試表。
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實(shí)例
fake = Faker()
# MySQL連接參數(shù)
db_params = {
'host': 'localhost',
'user': 'root',
'password': 'root',
'db': 'db1',
'port': 3311
}
# 連接數(shù)據(jù)庫(kù)
connection = pymysql.connect(**db_params)
# 創(chuàng)建一個(gè)新的Cursor實(shí)例
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)建好測(cè)試數(shù)據(jù)后，執(zhí)行一個(gè)帶有 UNION 的語(yǔ)句。
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)
可見(jiàn)第二行中 key 值是 PRIMARY，即第二個(gè)查詢(xún)使用了主鍵 ID。第三行 extra 值是 Using temporary，表明在對(duì)上面兩個(gè)查詢(xún)的結(jié)果集做 UNION 的時(shí)候，使用了臨時(shí)表。
UNION 操作是將兩個(gè)結(jié)果集取并集，不包含重復(fù)項(xiàng)。要做到這一點(diǎn)，只需要先創(chuàng)建一個(gè)只有主鍵的內(nèi)存內(nèi)部臨時(shí)表，并將第一個(gè)子查詢(xún)的值插入進(jìn)這個(gè)表中，這樣就可以避免了重復(fù)的問(wèn)題。因?yàn)橹?5000 早已存在臨時(shí)表中，而第二個(gè)子查詢(xún)的值 5000 就會(huì)因?yàn)闆_突無(wú)法插入，只能插入下一個(gè)值 4999。
UNION ALL 與 UNION 不同，并不會(huì)使用內(nèi)存臨時(shí)表，下列例子是使用 UNION ALL 的執(zhí)行計(jì)劃。
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)
因?yàn)?UNION ALL 并不需要去重，所以?xún)?yōu)化器不需要新建一個(gè)臨時(shí)表做去重的動(dòng)作，執(zhí)行的時(shí)候只需要按順序執(zhí)行兩個(gè)子查詢(xún)并將子查詢(xún)放在一個(gè)結(jié)果集里就好了。
可以看到，在實(shí)現(xiàn) UNION 的語(yǔ)義上，臨時(shí)表起到的是一個(gè)暫時(shí)存儲(chǔ)數(shù)據(jù)并做去重的動(dòng)作的這么一種作用的存在。
GROUP BY
除了 UNION 之外，還有一個(gè)比較常用的子句 GROUP BY 也會(huì)使用到內(nèi)部臨時(shí)表。下列例子展示了一個(gè)使用 ID 列求余并進(jìn)行分組統(tǒng)計(jì)，且按照余數(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; 這三個(gè)值分別是：使用索引、使用臨時(shí)表、使用了排序。
注意：在 MySQL 5.7 版本中 GROUP BY 會(huì)默認(rèn)按照分組字段進(jìn)行排序，在 MySQL 8.0 版本中取消了默認(rèn)排序功能，所以此處使用了 ORDER BY 進(jìn)行復(fù)現(xiàn)。
對(duì)于 GROUP BY 來(lái)說(shuō)，上述的語(yǔ)句執(zhí)行后，會(huì)先創(chuàng)建一個(gè)內(nèi)存內(nèi)部臨時(shí)表，存儲(chǔ) complementation 與 count(*) 的值，主鍵為 complementation。然后按照索引 hire_date 對(duì)應(yīng)的 ID 值依次計(jì)算 id%5 的值記為 x，如果臨時(shí)表中沒(méi)有主鍵為 x 的值，那么將會(huì)在臨時(shí)表中插入記錄；如果存在則累加這一行的計(jì)數(shù) count(*)。在遍歷完成上述的操作后，再按照 ORDER BY 的規(guī)則對(duì) complementation 進(jìn)行排序。
在使用 GROUP BY 進(jìn)行分組或使用 DISTINCT 進(jìn)行去重時(shí)，MySQL 都給我們提供了使用 hint 去避免使用內(nèi)存內(nèi)部臨時(shí)表的方法。
| hint | 解釋 |
|---|---|
| SQL_BIG_RESULT | 顯式指定該 SQL 語(yǔ)句使用磁盤(pán)內(nèi)部臨時(shí)表，適合大數(shù)據(jù)量的操作；適用于 InnoDB 引擎與 Memory 引擎。 |
| SQL_SMALL_RESULT | 顯式指定該 SQL 語(yǔ)句使用內(nèi)存內(nèi)部臨時(shí)表，速度更快，適合小數(shù)據(jù)量的操作；適用于 Memory 引擎。 |
下列是一個(gè)使用了 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í)行計(jì)劃中我們可以看出，使用了 SQL_BIG_RESULT 這個(gè) hint 進(jìn)行查詢(xún)后，在 extra 列中 Using Temporary 字樣已經(jīng)不見(jiàn)了，即避免了使用內(nèi)存內(nèi)部臨時(shí)表。
其他場(chǎng)景
當(dāng)然，除了上述兩個(gè)例子外，MySQL 還會(huì)在下列情況下創(chuàng)建內(nèi)部臨時(shí)表：
- 對(duì)于UNION語(yǔ)句的評(píng)估，但有一些后續(xù)描述中的例外情況。
- 對(duì)于某些視圖的評(píng)估，例如使用 TEMPTABLE 算法、UNION 或聚合的視圖。
- 對(duì)派生表的評(píng)估。
- 對(duì)公共表達(dá)式的評(píng)估。
- 用于子查詢(xún)或半連接材料化的表。
- 對(duì)包含 ORDER BY 子句和不同 GROUP BY 子句的語(yǔ)句的評(píng)估，或者對(duì)于其中 ORDER BY 或 GROUP BY 子句包含來(lái)自連接隊(duì)列中第一個(gè)表以外的表的列的語(yǔ)句。
- 對(duì)于 DISTINCT 與 ORDER BY 的組合，可能需要一個(gè)臨時(shí)表。
- 對(duì)于使用 SQL_SMALL_RESULT 修飾符的查詢(xún)，MySQL 使用內(nèi)存中的臨時(shí)表，除非查詢(xún)還包含需要在磁盤(pán)上存儲(chǔ)的元素。
- 為了評(píng)估從同一表中選取并插入的 INSERT … SELECT 語(yǔ)句，MySQL 創(chuàng)建一個(gè)內(nèi)部臨時(shí)表來(lái)保存 SELECT 的行，然后將這些行插入目標(biāo)表中。
- 對(duì)于多表 UPDATE 語(yǔ)句的評(píng)估。
- 對(duì)于 GROUP_CONCAT() 或 COUNT(DISTINCT) 表達(dá)式的評(píng)估。
- 窗口函數(shù)的評(píng)估，根據(jù)需要使用臨時(shí)表。
值得注意的是，某些查詢(xún)條件 MySQL 不允許使用內(nèi)存內(nèi)部臨時(shí)表，在這種情況下，服務(wù)器會(huì)使用磁盤(pán)內(nèi)部臨時(shí)表。
- 表中存在 BLOB 或 TEXT 列。MySQL 8.0 中用于內(nèi)存內(nèi)部臨時(shí)表的默認(rèn)存儲(chǔ)引擎 TempTable 從 8.0.13 開(kāi)始支持二進(jìn)制大對(duì)象類(lèi)型。
- 如果使用了 UNION 或 UNION ALL，SELECT 的列表中存在任何最大長(zhǎng)度超過(guò) 512 的字符串列（對(duì)于二進(jìn)制字符串為字節(jié)，對(duì)于非二進(jìn)制字符串為字符）。
- SHOW COLUMNS 和 DESCRIBE 語(yǔ)句使用 BLOB 作為某些列的類(lèi)型，因此用于此結(jié)果的臨時(shí)表是將會(huì)是磁盤(pán)內(nèi)部臨時(shí)表。
以上就是MySQL創(chuàng)建內(nèi)部臨時(shí)表的所有場(chǎng)景盤(pán)點(diǎn)的詳細(xì)內(nèi)容，更多關(guān)于MySQL創(chuàng)建內(nèi)部臨時(shí)表的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！
相關(guān)文章
簡(jiǎn)單學(xué)習(xí)SQL的各種連接Join
sql語(yǔ)句中join是一種高效的語(yǔ)句，下面小編來(lái)帶大家詳細(xì)了解一下它的詳細(xì)情況2019-05-05
在MySQL數(shù)據(jù)庫(kù)中,操作回滾日志是一種記錄數(shù)據(jù)庫(kù)中事務(wù)操作的重要機(jī)制,本文主要介紹了mysql查看回滾日志的方法步驟,具有一定的參考價(jià)值,感興趣的可以了解一下2024-05-05
在 MySQL 中導(dǎo)入大文件時(shí),mysql source 導(dǎo)入大文件速度太慢,可能會(huì)遇到性能問(wèn)題或內(nèi)存限制,以下是一些優(yōu)化導(dǎo)入大文件的建議,需要的朋友可以參考一下2025-02-02
Sql group by 分組取時(shí)間最新的一條數(shù)據(jù)(示例代碼)
這篇文章主要介紹了Sql group by 分組取時(shí)間最新的一條數(shù)據(jù),本文通過(guò)示例代碼給大家介紹的非常詳細(xì),感興趣的朋友跟隨小編一起看看吧2024-04-04
這篇文章主要給大家介紹了關(guān)于Mysql多主一從數(shù)據(jù)備份的相關(guān)資料，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面來(lái)一起看看吧2018-12-12
用HAProxy來(lái)檢測(cè)MySQL復(fù)制的延遲的教程
這篇文章主要介紹了用HAProxy來(lái)檢測(cè)MySQL復(fù)制的延遲的教程,HAProxy需要使用到PHP腳本,需要的朋友可以參考下2015-04-04
MySql版本問(wèn)題sql_mode=only_full_group_by的完美解決方案
這篇文章主要介紹了MySql版本問(wèn)題sql_mode=only_full_group_by的完美解決方案,需要的朋友可以參考下2017-07-07
這篇文章主要介紹了MySQL 數(shù)據(jù)備份與還原的相關(guān)知識(shí),本文通過(guò)示例代碼給大家介紹的非常詳細(xì)，具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2018-09-09
今天小編就為大家分享一篇關(guān)于Mysql5.7修改root密碼教程，小編覺(jué)得內(nèi)容挺不錯(cuò)的，現(xiàn)在分享給大家，具有很好的參考價(jià)值，需要的朋友一起跟隨小編來(lái)看看吧2019-02-02
MySQL SQL優(yōu)化教程之in和range查詢(xún)
這篇文章主要給大家介紹了關(guān)于MySQL SQL優(yōu)化教程之in和range查詢(xún)的相關(guān)資料，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2020-12-12