这篇文章将为大家详细讲解有关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内部临时表策略的示例分析”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，使各位可以学到更多知识，如果觉得文章不错，请把它分享出去让更多的人看到。
