Improved execution efficiency problem with MySQL temp table (RPM)

• Problem:

Recently, the online MySQL found a slow SQL, each time to query more than 1000ms, seriously affect the user experience

Today hollow to diagnose, record as follows:

SQL Original sentence:

[HTML]View PlainCopyprint?

2. SELECT r.object_id as CardId, COUNT (1) as Attachs from Hzresource_object R
4. Left JOIN
6. (SELECT card_id from Card_member WHERE user_id = #uid # and card_member.deleted=0
8. UNION
10. SELECT card_id from card_subscribed where user_id = #uid # and card_subscribed.deleted=0
12. ) m on r.object_id = m.card_id
14. WHERE r.object_type = #objectType # and r.deleted = 0
16. GROUP by r.object_id;

• Solve the problem:

Because of the database optimization smattering, completely impossible, can only help degrees Niang and Gu elder brother, test all kinds of methods, not see effect

After a few twists and turns, the focus is finally on the temp table, because explain view the execution plan, you can see the using temporary

MySQL may use temporary tables when executing SQL queries, and in general, temporary tables mean lower performance.

So we'll try to modify the SQL statement, discard the temporary table, and modify the following:

[HTML]View PlainCopyprint?

2. SELECT r.object_id as CardId, COUNT (1) as Attachs from Hzresource_object R
4. WHERE r.object_type = #objectType # and r.deleted = 0 and r.object_id in (
6. SELECT card_id from Card_member WHERE user_id = #uid # and card_member.deleted=0
8. UNION
10. SELECT card_id from card_subscribed where user_id = #uid # and card_subscribed.deleted=0
12. )
14. GROUP by r.object_id;

The statement is split into two SQL words, with in operation splicing

• Native test:

Optimize the pre-execution time 1040ms, optimize the execution time: 85ms, the execution speed is 12 times times more than the original! Praise

• Ps:

It's common sense that we all repel the in operation, with union instead, why is it faster to use in?

With questions, and then go online to find, originally:

SQL execution generates a huge temporary table that, when not in memory, is copied to disk, causing Io to soar and time overhead to increase.

Additional harvesting knowledge is collected as follows:

• Temporary table storage

The MySQL temp table is divided into "Memory temp table" and "Disk temp table", in which the memory temp table uses MySQL storage engine, the disk temp table uses MySQL's MyISAM storage engine;
In general, MySQL will first create a temporary memory table, but after the memory temp table exceeds the configured value, MySQL will export the memory temporary table to the disk temporary table;

• Scenes that use temporary tables

1) The ORDER by clause differs from the GROUP BY clause, for example: Ordery by Price GROUP by name;
2) in a join query, ORDER by or group by uses a column that is not the first table, for example: SELECT * from TableA, TableB order by Tablea.price GROUP by Tableb.name
3) The DISTINCT keyword Ordery by DISTINCT (price) is used in ORDER by
4) The Sql_small_result keyword specified in the SELECT statement Sql_small_result means to tell MySQL that the results will be small, use the memory temp table directly, and do not need to use an index sort Sql_small_ Result must be used with group by, distinct, or distinctrow in general, we do not need to use this option to let MySQL server choose.

• Scenes that use disk staging tables directly

1) The table contains text or BLOB columns;
2) The GROUP by or DISTINCT clause contains a column of length greater than 512 bytes;
3) When using union or union ALL, the SELECT clause contains a column greater than 512 bytes;

• Design principles for tables

The use of temporary tables generally means that performance is relatively low, especially with the use of disk temporary tables, performance is slower, so we should try to avoid the use of temporary tables in the actual application. The common ways to avoid temporary tables are:
1) CREATE INDEX: Create an index on the column of order by or group by;
2) split a very long column: In general, TEXT, blobs, strings larger than 512 bytes, are basically intended to display information, and are not used for query criteria, so the table design should be independent of these columns to another table.

• SQL optimization

If the design of the table has been determined and the modifications are difficult, you can also improve the efficiency of SQL execution by optimizing the SQL statement to reduce the size of the temporary table.
The common methods for optimizing SQL statements are as follows:
1) Splitting SQL statements
Temporary tables are primarily used for sorting and grouping, and many businesses require sorting and then fetching detailed paging data, in which case the sorting and fetching details can be split into different SQL to reduce the size of the temporary table when sorting or grouping, improve the efficiency of sorting and grouping, and this is how we use this approach.
2) Optimize business, remove sorting and grouping operations
Sometimes the business does not need to sort or group, just for the sake of good-looking or easy to read and sorted, such as data export, data query and other operations, in this case, the removal of sorting and grouping does not have much impact on the business.

• How can I tell if a temporary table is used?

Using explain to view the execution plan, extra column sees the using temporary means that the temporary table is used.

Improved execution efficiency problem with MySQL temp table (RPM)