MySQL内部临时表策略

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
简介:

通过对MySQL数据库源码的跟踪和调试,以及参考MySQL官方文档,对MySQL内部临时表使用策略进行整理,以便于更加深入的理解。

  使用内部临时表条件

  MySQL内部临时表的使用有一定的策略,从源码中关于SQL查询是否需要内部临时表。可以总结如下:

  1、DISTINCT查询,但是简单的DISTINCT查询,比如对primary key、unique key等DISTINCT查询时,查询优化器会将DISTINCT条件优化,去除DISTINCT条件,也不会创建临时表;

  2、不是第一个表的字段使用ORDER BY 或者GROUP BY;

  3、ORDER BY和GROUP BY使用不同的顺序;

  4、用户需要缓存结果;

  5、ROLLUP查询。

  源码如下所示

  代码地址:sql_select.cc:854,函数:JOIN::optimize(),位置:sql_select.cc:1399

/*
    Check if we need to create a temporary table.
    This has to be done if all tables are not already read (const tables)
    and one of the following conditions holds:
    - We are using DISTINCT (simple distinct's are already optimized away)
    - We are using an ORDER BY or GROUP BY on fields not in the first table
    - We are using different ORDER BY and GROUP BY orders
    - The user wants us to buffer the result.
    When the WITH ROLLUP modifier is present, we cannot skip temporary table
    creation for the DISTINCT clause just because there are only const tables.
  */
  need_tmp= (( const_tables != tables &&
               (( select_distinct || !simple_order || !simple_group) ||
                ( group_list && order ) ||
                test(select_options & OPTION_BUFFER_RESULT))) ||
             ( rollup.state != ROLLUP:: STATE_NONE && select_distinct ));

  内部临时表使用原则

  但是使用了内部临时表,那么他是怎么存储的呢?原则是这样的:

  1、当查询结果较小的情况下,使用heap存储引擎进行存储。也就是说在内存中存储查询结果。

  2、当查询结果较大的情况下,使用myisam存储引擎进行存储。

  3、当查询结果最初较小,但是不断增大的情况下,将会有从heap存储引擎转化为myisam存储引擎存储查询结果。

  什么情况算是查询结果较小呢?从源码中if的几个参数可以看出:

  1、有blob字段的情况;

  2、使用唯一限制的情况;

  3、当前表定义为大表的情况;

  4、查询结果的选项为小结果集的情况;

  5、查询结果的选项为强制使用myisam的情况

  源码如下所示

  代码地址:sql_select.cc:10229,函数:create_tmp_table(),位置:sql_select.cc:10557

/* If result table is small; use a heap */
  /* future: storage engine selection can be made dynamic? */
  if ( blob_count || using_unique_constraint
      || ( thd->variables .big_tables && !( select_options & SELECT_SMALL_RESULT ))
      || ( select_options & TMP_TABLE_FORCE_MYISAM ))
  {
    share->db_plugin = ha_lock_engine(0, myisam_hton);
    table->file = get_new_handler( share, &table ->mem_root,
                                 share->db_type ());
    if (group &&
          ( param->group_parts > table-> file->max_key_parts () ||
           param->group_length > table-> file->max_key_length ()))
      using_unique_constraint=1;
  }
  else
  {
    share->db_plugin = ha_lock_engine(0, heap_hton);
    table->file = get_new_handler( share, &table ->mem_root,
                                 share->db_type ());
  }

  代码地址:sql_select.cc:11224,函数:create_myisam_from_heap(),位置:sql_select.cc:11287

/*
    copy all old rows from heap table to MyISAM table
    This is the only code that uses record[1] to read/write but this
    is safe as this is a temporary MyISAM table without timestamp/autoincrement
    or partitioning.
  */
  while (! table->file ->rnd_next( new_table.record [1]))
  {
    write_err= new_table .file-> ha_write_row(new_table .record[1]);
    DBUG_EXECUTE_IF("raise_error" , write_err= HA_ERR_FOUND_DUPP_KEY ;);
    if (write_err )
      goto err ;
  }

  官方文档相关内容

  以上内容只是源码表面的问题,通过查询MySQL的官方文档,得到了更为权威的官方信息。

  临时表创建的条件:

  1、如果order by条件和group by的条件不一样,或者order by或group by的不是join队列中的第一个表的字段。

  2、DISTINCT联合order by条件的查询。

  3、如果使用了SQL_SMALL_RESULT选项,MySQL使用memory临时表,否则,查询询结果需要存储到磁盘。

  临时表不使用内存表的原则:

  1、表中有BLOB或TEXT类型。

  2、group by或distinct条件中的字段大于512个字节。

  3、如果使用了UNION或UNION ALL,任何查询列表中的字段大于512个字节。

  此外,使用内存表最大为tmp_table_size和max_heap_table_size的最小值。如果超过该值,转化为myisam存储引擎存储到磁盘。









====================================分割线================================



最新内容请见作者的GitHub页:http://qaseven.github.io/

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
打赏
0
0
0
0
26197
分享
相关文章
MySQL慢查询优化策略
MySQL慢查询优化是一个复杂的过程,需要根据具体的应用场景和数据特点进行。以上策略是提升数据库查询性能的有效途径,但最关键的是对系统进行持续的监控和分析,及时发现并解决性能瓶颈。通过实践这些策略,你可以显著提高MySQL数据库的性能,为用户提供更快的响应时间和更好的体验。
190 10
MySQL索引策略与查询性能调优实战
在实际应用中,需要根据具体的业务需求和查询模式,综合运用索引策略和查询性能调优方法,不断地测试和优化,以提高MySQL数据库的查询性能。
405 66
MySQL执行计划选择策略:揭秘查询优化的艺术
【10月更文挑战第15天】 在数据库性能优化中,选择最优的执行计划是提升查询效率的关键。MySQL作为一个强大的关系型数据库管理系统,提供了复杂的查询优化器来生成执行计划。本文将深入探讨如何选择合适的执行计划,以及为什么某些计划更优。
233 2
Aurora MySQL负载突增应对策略与优化方案
通过以上策略,企业可以有效应对 Aurora MySQL 的负载突增,确保数据库在高负载情况下依然保持高性能和稳定性。这些优化方案涵盖了从架构设计到具体配置和监控的各个方面,能够全面提升数据库的响应速度和处理能力。在实际应用中,应根据具体的业务需求和负载特征,灵活调整和应用这些优化策略。
63 22
MySQL 中如何实现分库分表?常见的分库分表策略有哪些?
在MySQL中,分库分表(Sharding)通过将数据分散到多个数据库或表中,以应对大量数据带来的性能和扩展性问题。常见策略包括:哈希分片(分布均匀,查询效率高)、范围分片(适合范围查询)、列表分片(适用于特定值查询)、复合分片(灵活性高)和动态分片(灵活应对负载变化)。每种策略各有优劣,需根据业务需求选择。常用工具如MyCAT、ShardingSphere和TDDL可简化实现过程。
MySQL战记:Count( *)实现之谜与计数策略的选择
本文深入探讨了MySQL中`count(*)`的不同实现方式,特别是MyISAM和InnoDB引擎的区别,以及各种计数方法的性能比较。同时,文章分析了使用缓存系统(如Redis)与数据库保存计数的优劣,并强调了在高并发场景下保持数据一致性的挑战。
128 0
MySQL战记:Count( *)实现之谜与计数策略的选择
PHP与MySQL的高效协同开发策略####
本文深入探讨了PHP与MySQL在Web开发中的协同工作机制,通过优化配置、最佳实践和高级技巧,展示了如何提升数据库交互性能,确保数据安全,并促进代码可维护性。我们将从环境搭建讲起,逐步深入到查询优化、事务管理、安全防护及性能调优等核心环节,为开发者提供一套实战驱动的解决方案框架。 ####
MySQL自增ID耗尽应对策略:技术解决方案全解析
在数据库管理中,MySQL的自增ID(AUTO_INCREMENT)属性为表中的每一行提供了一个唯一的标识符。然而,当自增ID达到其最大值时,如何处理这一情况成为了数据库管理员和开发者必须面对的问题。本文将探讨MySQL自增ID耗尽的原因、影响以及有效的应对策略。
305 3
Linux环境下MySQL数据库自动定时备份策略
在Linux环境下,MySQL数据库的自动定时备份是确保数据安全和可靠性的重要措施。通过设置定时任务,我们可以每天自动执行数据库备份,从而减少人为错误和提高数据恢复的效率。本文将详细介绍如何在Linux下实现MySQL数据库的自动定时备份。
185 3
MySQL自增ID耗尽解决方案:应对策略与实践技巧
在MySQL数据库中,自增ID(AUTO_INCREMENT)是一种特殊的属性,用于自动为新插入的行生成唯一的标识符。然而,当自增ID达到其最大值时,会发生什么?又该如何解决?本文将探讨MySQL自增ID耗尽的问题,并提供一些实用的解决方案。
176 1