MySQL内部临时表策略的示例分析

这篇文章将为大家详细讲解有关Mysql内部临时表策略的示例分析，小编觉得挺实用的，因此分享给大家做个参考，希望大家阅读完这篇文章后可以有所收获。

mysql内部临时表策略
 
通过对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、用户需要缓存结果；  www.2cto.com  
     5、ROLLUP查询。
 
     源码如下所示
     代码地址：sql_select.cc:854，函数：JOIN::optimize()，位置：sql_select.cc:1399
   www.2cto.com  
  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的情况。
       www.2cto.com  
     源码如下所示
     代码地址：sql_select.cc:10229，函数：create_tmp_table()，位置：sql_select.cc:10557
 
 
  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 ());
  }
  www.2cto.com  
     代码地址：sql_select.cc:11224，函数：create_myisam_from_heap()，位置：sql_select.cc:11287
 
  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存储引擎存储到磁盘。
 

关于“MySQL内部临时表策略的示例分析”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，使各位可以学到更多知识，如果觉得文章不错，请把它分享出去让更多的人看到。