数据库
搜索
分类

MySQL内部临时表的具体使用

作者：Yungyu 时间：2024-01-20 13:09:54　

UNION

UNION语义：取两个子查询结果的并集，重复的行只保留一行

表初始化


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

执行语句


(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

  • 表示在对子查询的结果做UNION RESULT的时候，使用了临时表

UNION RESULT

MySQL内部临时表的具体使用

  • 创建一个内存临时表，这个内存临时表只有一个整型字段f，并且f为主键

  • 执行第一个子查询，得到1000，并存入内存临时表中

  • 执行第二个子查询

    • 违反唯一性约束，插入失败，继续执行

    • 拿到第一行id=1000，试图插入到内存临时表，但由于1000这个值已经存在于内存临时表

    • 拿到第二行id=999，插入内存临时表成功

    • 从内存临时表中按行取出数据，返回结果，并删除内存临时表，结果中包含id=1000和id=999两行

    • 内存临时表起到了暂存数据的作用，还用到了内存临时表主键id的唯一性约束，实现UNION的语义

    UNION ALL

    UNION ALL没有去重的语义，一次执行子查询，得到的结果直接发给客户端，不需要内存临时表


    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

    内存充足


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

    执行语句


    -- MySQL 5.6上执行
    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：表示需要排序

    执行过程

    MySQL内部临时表的具体使用

    • 创建内存临时表，表里有两个字段m和c，m为主键

    • 扫描t1的索引a，依次取出叶子节点上的id值，计算id％10，记为x

      • 如果内存临时表中没有主键为x的行，插入一行记录(x,1)

      • 如果内存临时表中有主键为x的行，将x这一行的c值加1

    • 遍历完成后，再根据字段m做排序，得到结果集返回给客户端

    排序过程

    MySQL内部临时表的具体使用

    ORDER BY NULL


    -- 跳过最后的排序阶段，直接从临时表中取回数据
    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中的数据是从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 |
    +------+-----+

    内存不足


    SET tmp_table_size=1024;

    执行语句


    -- 内存临时表的上限为1024 Bytes，但内存临时表不能完全放下100行数据，内存临时表会转成磁盘临时表，默认采用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 |
    +------+----+

    优化方案

    优化索引

    MySQL内部临时表的具体使用

    不论使用内存临时表还是磁盘临时表，GROUP BY都需要构造一个带唯一索引的表，执行代价较高

    需要临时表的原因：每一行的id％100是无序的，因此需要临时表，来记录并统计结果

    如果可以确保输入的数据是有序的，那么计算GROUP BY时，只需要
    从左到右顺序扫描，依次累加即可

    • 当碰到第一个1的时候，已经累积了X个0，结果集里的第一行为(0,X)

    • 当碰到第一个2的时候，已经累积了Y个1，结果集里的第一行为(1,Y)

    • 整个过程不需要临时表，也不需要排序


    -- MySQL 5.7上执行
    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语句需要放到临时表的数据量特别大，还是按照先放在内存临时表，再退化成磁盘临时表

    可以直接用磁盘临时表的形式，在GROUP BY语句中SQL_BIG_RESULT（告诉优化器涉及的数据量很大）

    磁盘临时表原本采用B+树存储，存储效率还不如数组，优化器看到SQL_BIG_RESULT，会直接用数组存储

    • 即放弃使用临时表，直接进入排序阶段

    执行过程

    MySQL内部临时表的具体使用


    -- 没有再使用临时表，而是直接使用了排序算法
    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内存不够时，会利用磁盘临时文件辅助排序）

    排序完成后，得到一个有序数组，遍历有序数组，得到每个值出现的次数（类似上面优化索引的方式）

    对比DISTINCT


    -- 标准SQL，SELECT部分添加一个聚合函数COUNT(*)
    SELECT a,COUNT(*) FROM t GROUP BY a ORDER BY NULL;
    -- 非标准SQL
    SELECT a FROM t GROUP BY a ORDER BY NULL;

    SELECT DISTINCT a FROM t;

    标准SQL：按照字段a分组，计算每组a出现的次数

    非标准SQL：没有了COUNT(*)，不再需要执行计算总数的逻辑

    • 按照字段a分组，相同的a的值只返回一行，与DISTINCT语义一致

    如果不需要执行聚合函数 ，DISTINCT和GROUP BY的语义、执行流程和执行性能是相同的

    • 创建一个临时表，临时表有一个字段a，并且在这个字段a上创建一个唯一索引

    • 遍历表t，依次取出数据插入临时表中

      • 如果发现唯一键冲突，就跳过

      • 否则插入成功

    • 遍历完成后，将临时表作为结果集返回给客户端

    小结

    • 用到内部临时表的场景

      • 如果语句执行过程中可以一边读数据，一边得到结果，是不需要额外内存的

      • 否则需要额外内存来保存中间结果

    • join_buffer是无序数组，sort_buffer是有序数组，临时表是二维表结构

    • 如果执行逻辑需要用到二维表特性，就会优先考虑使用临时表如果对GROUP BY语句的结果没有明确的排序要求，加上ORDER BY NULL（MySQL 5.6）

    • 尽量让GROUP BY过程用上索引，确认EXPLAIN结果没有Using temporary和Using filesort

    • 如果GROUP BY需要统计的数据量不大，尽量使用内存临时表（可以适当调大tmp_table_size）

    • 如果数据量实在太大，使用SQL_BIG_RESULT来告诉优化器直接使用排序算法（跳过临时表）

    参考资料

    《MySQL实战45讲》

    来源：https://www.cnblogs.com/yungyu16/p/12961696.html

    标签：MySQL,内部临时表
    0
    投稿

    猜你喜欢

  • INSERT INTO SELECT语句与SELECT INTO FROM语句的一些区别

    2012-06-06 19:38:28

  • selenium跳过webdriver检测并模拟登录淘宝

    2023-06-14 18:22:52

  • Python bsddb模块操作Berkeley DB数据库介绍

    2024-01-18 05:32:02

  • 解决在window下执行SQLSERVER定时备份的问题

    2024-01-13 15:17:09

  • 关于Python Socket编程的要点详解

    2021-11-25 17:44:11

  • PowerDesigner16生成SQL2005列注释的方法

    2024-01-26 06:06:36

  • 浅谈Python 中整型对象的存储问题

    2021-08-12 10:33:58

  • 浅谈golang并发操作变量安全的问题

    2024-04-26 17:22:23

  • Linux安装MySQL教程（二进制分发版）

    2024-01-15 03:24:02

  • python io.BytesIO简介及示例代码

    2021-04-25 04:52:31

  • 在Spring中用select last_insert_id()时遇到问题

    2009-05-24 19:50:00

  • Python中list列表的一些进阶使用方法介绍

    2023-12-24 10:31:47

  • 浅谈MySQL中四种常用存储引擎

    2024-01-23 00:10:29

  • Windows Server 2008之数据安全保护

    2008-12-04 12:49:00

  • Dreaweaver MX 2004新功能:图片处理

    2010-09-02 12:38:00

  • SQL Server重温 事务

    2024-01-14 21:47:00

  • 解决Pytorch自定义层出现多Variable共享内存错误问题

    2023-12-14 14:43:46

  • 详解Python迭代和迭代器

    2023-04-30 23:31:13

  • pytest使用parametrize将参数化变量传递到fixture

    2022-03-28 23:30:18

  • 五个方便好用的Python自动化办公脚本的实现

    2022-04-13 10:10:59
    • asp之家 网络编程 m.aspxhome.com