Shulou(Shulou.com)05/31 Report--

This article will explain in detail the example analysis of MySQL's internal temporary table strategy. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

MySQL Internal temporary Table Policy

Through the tracking and debugging of the MySQL database, and referring to the official documents of MySQL, the strategies for the use of temporary tables in MySQL are sorted out for a more in-depth understanding.

Use internal temporary table conditions

The use of MySQL internal temporary tables has a certain strategy, from the source code about whether internal temporary tables are needed for SQL query. It can be summarized as follows:

1. DISTINCT queries, but simple DISTINCT queries, such as primary key, unique key and other DISTINCT queries, the query optimizer will optimize the DISTINCT condition, remove the DISTINCT condition, and will not create a temporary table

2. Fields that are not the first table use ORDER BY or GROUP BY

3. ORDER BY and GROUP BY use different order

4. Users need to cache the results; www.2cto.com

5. ROLLUP query.

The source code is as follows

Code address: sql_select.cc:854, function: JOIN::optimize (), location: 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.

* / 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))

Principles for the use of internal temporary tables

But using an internal temporary table, how does it store it? The principle is as follows:

1. When the query result is small, use the heap storage engine for storage. That is, the query results are stored in memory.

2. When the query result is large, myisam storage engine is used for storage.

3. When the query results are initially small, but continue to increase, there will be a transformation from heap storage engine to myisam storage engine to store query results.

What is the case that the query result is small? As can be seen from several parameters of if in the source code:

1. If there is a blob field

2. Situations in which unique restrictions are used

3. When the current table is defined as a large table

4. When the option of the query result is a small result set

5. The option for query results is to force the use of myisam.

Www.2cto.com

The source code is as follows

Code address: sql_select.cc:10229, function: create_tmp_table (), location: 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 ()

}

Www.2cto.com

Code address: sql_select.cc:11224, function: create_myisam_from_heap (), location: 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

}

Relevant contents of official documents

The above content is only a superficial problem of the source code. By consulting the official documents of MySQL, we can get more authoritative official information.

Conditions for temporary table creation:

1. If the condition of order by is different from that of group by, or the field of order by or group by is not the first table in the join queue.

2. The query of DISTINCT joint order by condition.

3. If the SQL_SMALL_RESULT option is used, MySQL uses the memory temporary table, otherwise, the query results need to be stored on disk.

The principle that temporary tables do not use memory tables:

1. There are BLOB or TEXT types in the table.

2. The field in the group by or distinct condition is greater than 512 bytes.

3. If UNION or UNION ALL is used, the fields in any query list are greater than 512 bytes.

In addition, the maximum values for using memory tables are the minimum values of tmp_table_size and max_heap_table_size. If this value is exceeded, it is converted to the myisam storage engine and stored to disk.

This is the end of the article on "sample analysis of MySQL internal temporary table strategy". I hope the above content can be helpful to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.