Mysql SQL statement performance is improved by 100 times (avoid temporary tables)

Performance Optimization in mysql databases has always been a hot topic. Next I will introduce how to avoid temporary tables and improve the performance of your SQL statements by 100 times.[Symptom]A slow query occurred in the online mysql database. DBA observed that,When querying, the server IO soared, IO usage reached 100%, and the execution time was about 7 s.. The SQL statement is as follows: SELECT DISTINCT g. *, cp. name AS cp_name, c. name AS category_name, t. name AS type_name FROM gm_game g left join gm_cp cp ON cp. id = g. cp_id AND cp. deleted = 0 left join gm_category c ON c. id = g. category_id AND c. deleted = 0 left join gm_type t ON t. id = g. type_id AND t. deleted = 0 WHERE g. deleted = 0 order by g. modify_time desc limit 20;[Problem Analysis]Use explain to view the execution plan. The result is as follows: the problem with this SQL statement is obvious: a large amount of data (including the number of data entries, and g. *), and then use the temporary table order by, but only 20 data records are returned. The DBA observed high IO because the SQL statement generated a huge temporary table and the memory could not be stored. Therefore, all the data was copied to the disk, causing IO to soar.[Optimization solution]The general idea of optimization is to split SQL statements and separate sorting operations from querying all information. The first statement: to query Qualified Data, you only need to query g. id to SELECT DISTINCTG. idFROM gm_game g left join gm_cp cp ON cp. id = g. cp_id AND cp. deleted = 0 left join gm_category c ON c. id = g. category_id AND c. deleted = 0 left join gm_type t ON t. id = g. type_id AND t. deleted = 0 WHERE g. deleted = 0 order by g. modify_time desc limit 20; second statement: Query detailed data that meets the conditions, concatenate the results of the first SQL statement using the in operation to the second SQL SELECT DISTINCTG .*,Cp. name AS cp_name, c. name AS category_name, t. name AS type_name FROM gm_game g left join gm_cp cp ON cp. id = g. cp_id AND cp. deleted = 0 left join gm_category c ON c. id = g. category_id AND c. deleted = 0 left join gm_type t ON t. id = g. type_id AND t. deleted = 0 WHERE g. deleted = 0 and g. id in (.....................) Order by g. modify_time DESC;[Tested results]Testing on SATA machines requires about 50 s before optimization, the first 0.3 s after optimization, and the second 0.1 s after optimization. After optimization, the execution speed is more than 100 times the original speed, IO is reduced from 100% to less than 1% and tested on SSD machines. It takes about 7 s before optimization, the first 0.3 s after optimization, and the second 0.1 s after optimization, after optimization, the execution speed is more than 10 times that of the original one. IO is reduced from 100% to less than 1%. It can be seen that the disk I/O is a performance bottleneck before optimization, and the SSD speed is faster than that of SATA, after optimization, the disk is no longer a bottleneck, and there is no difference between SSD and SATA performance.[Theoretical analysis]MySQL may use temporary tables when executing SQL queries. Generally, using temporary tables means low performance.

• Temporary table Storage
MySQL temporary tables are divided into "MEMORY temporary tables" and "disk temporary tables". The MEMORY temporary tables use the MEMORY storage engine of MySQL, and the disk temporary tables use the MyISAM storage engine of MySQL; in general, MySQL creates a temporary memory table first, but after the temporary memory table exceeds the value specified in the configuration, MySQL exports the temporary memory table to the temporary disk table; on the Linux platform, the/tmp directory is used by default. Note that the/tmp directory is small.
• Use of temporary tables
1) The order by clause is different from the group by clause, for example, ordery by price group by name; 2) in the JOIN query, order by or group by uses columns that are not the first table, for example: SELECT * from TableA, TableB order by TableA. price GROUP by TableB. name 3) order by uses the DISTINCT keyword ordery by distinct (price) 4) the SELECT statement specifies the SQL _SMALL_RESULT keyword SQL _SMALL_RESULT, which means to tell MySQL that the result will be very small, please use the memory temporary table directly. SQL _SMALL_RESULT must be used together with GROUP BY, DISTINCT, or DISTINCTROW without using indexes. In general, we do not need to use this option for MySQL server to select Select.
• Direct use of temporary disk tables
1) The table contains TEXT or BLOB columns; 2) the group by or DISTINCT clause contains columns with a length greater than 512 bytes; 3) when UNION or union all is used, the SELECT clause contains columns larger than 512 bytes;
• Temporary table Configuration
Tmp_table_size: specifies the maximum size of the memory temporary table created by the system; http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_tmp_table_size max_heap_table_size: specifies the maximum size of the memory table created by the user; http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_max_heap_table_size note: the size of the temporary memory table created by the system is the minimum value of the preceding two configuration values.
• Table Design Principles
The use of temporary tables generally means that the performance is relatively low, especially when using disk temporary tables, which slows down the performance. Therefore, we should try to avoid the use of temporary tables in practical applications. Common methods to avoid temporary tables include: 1) Create an index: Create an index on an order by or group by column; 2) split a long column: Generally, TEXT, BLOB, strings larger than 512 bytes are basically used to display information and are not used for query conditions. Therefore, when designing a table, these columns should be independent of another table.
• How can I determine that a temporary table is used?
Use explain to view the execution plan. If the Extra column shows Using temporary, a temporary table is used.