MySQL内部临时表策略_MySQL
bitsCN.com
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、用户需要缓存结果; 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存储引擎存储到磁盘。
参考资料:1、MySQL源码:MySQL-5.5.202、MySQL官方文档:http://dev.mysql.com/doc/refman/5.5/en/internal-temporary-tables.html 作者 king_wangheng bitsCN.com

Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics



Key points of price strategy and promotion design in PHP flash sale system In a flash sale system, price strategy and promotion design are very important parts. Reasonable price strategies and well-designed promotions can attract users to participate in flash sale activities and improve the user experience and profitability of the system. The following will introduce the key points of price strategy and promotional activity design in the PHP flash sale system in detail, and provide specific code examples. 1. Key points in price strategy design: Determine the benchmark price: In the flash sale system, the benchmark price refers to the price of the product when it is normally sold. exist

EXE to PHP: An effective strategy to achieve function expansion. With the development of the Internet, more and more applications have begun to migrate to the web to achieve wider user access and more convenient operations. In this process, the demand for converting functions originally run as EXE (executable files) into PHP scripts is also gradually increasing. This article will discuss how to convert EXE to PHP to achieve functional expansion, and give specific code examples. Why Convert EXE to PHP Cross-Platformness: PHP is a cross-platform language

Table of Contents Astar Dapp Staking Principle Staking Revenue Dismantling of Potential Airdrop Projects: AlgemNeurolancheHealthreeAstar Degens DAOVeryLongSwap Staking Strategy & Operation "AstarDapp Staking" has been upgraded to the V3 version at the beginning of this year, and many adjustments have been made to the staking revenue rules. At present, the first staking cycle has ended, and the "voting" sub-cycle of the second staking cycle has just begun. To obtain the "extra reward" benefits, you need to grasp this critical stage (expected to last until June 26, with less than 5 days remaining). I will break down the Astar staking income in detail,

I started to officially come into contact with Linux in 2010. The entry-level distribution was Ubuntu10.10, and later transitioned to Ubunu11.04. During this period, I also tried many other mainstream distributions. After entering the laboratory, I started using CentOS5, then CentOS6, and now it has evolved to CentOS7. I have been using Linux for four years. The first three years were spent messing around, wasting a lot of time, and gaining a lot of experience and lessons. Maybe I am really old now and am no longer willing to bother with it. I just hope that after configuring a system, I can continue to use it. Why write/read this article? When using Linux, especially CentOS, you will encounter some pitfalls, or some things that people with mysophobia can't tolerate: software from official sources

MyBatis cache strategy analysis: best practices for first-level cache and second-level cache When developing using MyBatis, we often need to consider the choice of cache strategy. The cache in MyBatis is mainly divided into two types: first-level cache and second-level cache. The first-level cache is a SqlSession-level cache, while the second-level cache is a Mapper-level cache. In practical applications, rational use of these two caches is an important means to improve system performance. This article will use specific code examples to analyze a MyBatis

User feedback and improvement strategies for PHP blog system Introduction: With the popularity and development of the Internet, blogs have become an important way for people to share their knowledge and experience. In order to meet the needs of users, it is crucial to develop a stable, easy-to-use, and comprehensive blog system. However, as the software continues to iterate, user feedback and suggestions become particularly important because they can help us discover system problems and improve the system. This article will discuss user feedback and improvement strategies for the PHP blog system, and explain the improvement steps and methods through code examples.

The password policy of win7 is related to our password format, the characters required for the password, etc. If the password policy we set is too complex, it may make us very troublesome when using passwords, but it will also make our passwords more difficult to be hacked. So how to change the password policy in win7? Let’s take a look together below. Win7 password policy change tutorial 1. Press "win+r" on the keyboard to open Run, enter "gpedit.msc" 2. Press Enter to confirm and enter the local group policy editor. 3. Find "Account Policy" according to the path shown in the figure. 4. Double-click to open the "Password Policy" on the right. 5. Then you can set these password policies on the right. (Double-click to open to edit) 6. After the settings are completed, we can see the overall password policy.

JavaMap is a key-value pair-based data structure that allows developers to quickly store and retrieve data. The keys of a Map can be any object, and the values can be any type of data. Each key in the Map can only be associated with at most one value. If multiple values are set for the same key, only the last set value will be retained. There are two main implementations of Map: HashMap: uses a hash table to store key-value pairs. The performance of HashMap depends on how the hash table is implemented, and in most cases HashMap performs better than TreeMap. TreeMap: uses red-black trees to store key-value pairs. The performance of TreeMap is similar to HashMap, but in some cases, the performance of TreeMap can be
