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ù)的表,這類表在會(huì)話結(jié)束時(shí)會(huì)被自動(dòng)清理掉,但在 MySQL 中存在兩種臨時(shí)表,一種是外部臨時(shí)表,另外一種是內(nèi)部臨時(shí)表。
外部臨時(shí)表指的是用戶使用 CREATE TEMPORARY TABLE
手動(dòng)創(chuàng)建的臨時(shí)表。而內(nèi)部臨時(shí)表用戶是無(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è)查詢使用了主鍵 ID。第三行 extra 值是 Using temporary,表明在對(duì)上面兩個(gè)查詢的結(jié)果集做 UNION 的時(shí)候,使用了臨時(shí)表。
UNION 操作是將兩個(gè)結(jié)果集取并集,不包含重復(fù)項(xiàng)。要做到這一點(diǎn),只需要先創(chuàng)建一個(gè)只有主鍵的內(nèi)存內(nèi)部臨時(shí)表,并將第一個(gè)子查詢的值插入進(jìn)這個(gè)表中,這樣就可以避免了重復(fù)的問(wèn)題。因?yàn)橹?5000 早已存在臨時(shí)表中,而第二個(gè)子查詢的值 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 并不需要去重,所以優(yōu)化器不需要新建一個(gè)臨時(shí)表做去重的動(dòng)作,執(zhí)行的時(shí)候只需要按順序執(zhí)行兩個(gè)子查詢并將子查詢放在一個(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)行查詢后,在 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)估。
- 用于子查詢或半連接材料化的表。
- 對(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 修飾符的查詢,MySQL 使用內(nèi)存中的臨時(shí)表,除非查詢還包含需要在磁盤(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í)表。
值得注意的是,某些查詢條件 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ì)象類型。
- 如果使用了 UNION 或 UNION ALL,SELECT 的列表中存在任何最大長(zhǎng)度超過(guò) 512 的字符串列(對(duì)于二進(jìn)制字符串為字節(jié),對(duì)于非二進(jìn)制字符串為字符)。
- SHOW COLUMNS 和 DESCRIBE 語(yǔ)句使用 BLOB 作為某些列的類型,因此用于此結(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-05Sql group by 分組取時(shí)間最新的一條數(shù)據(jù)(示例代碼)
這篇文章主要介紹了Sql group by 分組取時(shí)間最新的一條數(shù)據(jù),本文通過(guò)示例代碼給大家介紹的非常詳細(xì),感興趣的朋友跟隨小編一起看看吧2024-04-04用HAProxy來(lái)檢測(cè)MySQL復(fù)制的延遲的教程
這篇文章主要介紹了用HAProxy來(lái)檢測(cè)MySQL復(fù)制的延遲的教程,HAProxy需要使用到PHP腳本,需要的朋友可以參考下2015-04-04MySql版本問(wèn)題sql_mode=only_full_group_by的完美解決方案
這篇文章主要介紹了MySql版本問(wèn)題sql_mode=only_full_group_by的完美解決方案,需要的朋友可以參考下2017-07-07