欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

MySQL創(chuàng)建內(nèi)部臨時(shí)表的所有場(chǎng)景盤(pán)點(diǎn)

 更新時(shí)間:2023年11月13日 14:23:50   作者:愛(ài)可生開(kāi)源社區(qū)  
這篇文章主要為大家介紹了MySQL創(chuàng)建內(nèi)部臨時(shí)表的所有場(chǎng)景盤(pán)點(diǎn),有需要的朋友可以借鑒參考下,希望能夠有所幫助,祝大家多多進(jì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

    簡(jiǎn)單學(xué)習(xí)SQL的各種連接Join

    sql語(yǔ)句中join是一種高效的語(yǔ)句,下面小編來(lái)帶大家詳細(xì)了解一下它的詳細(xì)情況
    2019-05-05
  • mysql查看回滾日志的方法步驟

    mysql查看回滾日志的方法步驟

    在MySQL數(shù)據(jù)庫(kù)中,操作回滾日志是一種記錄數(shù)據(jù)庫(kù)中事務(wù)操作的重要機(jī)制,本文主要介紹了mysql查看回滾日志的方法步驟,具有一定的參考價(jià)值,感興趣的可以了解一下
    2024-05-05
  • mysql sql大文件導(dǎo)入正確姿勢(shì)

    mysql sql大文件導(dǎo)入正確姿勢(shì)

    在 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ù)(示例代碼)

    這篇文章主要介紹了Sql group by 分組取時(shí)間最新的一條數(shù)據(jù),本文通過(guò)示例代碼給大家介紹的非常詳細(xì),感興趣的朋友跟隨小編一起看看吧
    2024-04-04
  • Mysql多主一從數(shù)據(jù)備份的方法教程

    Mysql多主一從數(shù)據(jù)備份的方法教程

    這篇文章主要給大家介紹了關(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來(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的完美解決方案

    這篇文章主要介紹了MySql版本問(wèn)題sql_mode=only_full_group_by的完美解決方案,需要的朋友可以參考下
    2017-07-07
  • MySQL 數(shù)據(jù)備份與還原的示例代碼

    MySQL 數(shù)據(jù)備份與還原的示例代碼

    這篇文章主要介紹了MySQL 數(shù)據(jù)備份與還原的相關(guān)知識(shí),本文通過(guò)示例代碼給大家介紹的非常詳細(xì),具有一定的參考借鑒價(jià)值,需要的朋友可以參考下
    2018-09-09
  • Mysql5.7修改root密碼教程

    Mysql5.7修改root密碼教程

    今天小編就為大家分享一篇關(guān)于Mysql5.7修改root密碼教程,小編覺(jué)得內(nèi)容挺不錯(cuò)的,現(xiàn)在分享給大家,具有很好的參考價(jià)值,需要的朋友一起跟隨小編來(lái)看看吧
    2019-02-02
  • MySQL SQL優(yōu)化教程之in和range查詢

    MySQL SQL優(yōu)化教程之in和range查詢

    這篇文章主要給大家介紹了關(guān)于MySQL SQL優(yōu)化教程之in和range查詢的相關(guān)資料,文中通過(guò)示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧
    2020-12-12

最新評(píng)論