Go--Optimize temporary table usage, SQL statement performance 100 times times increase

Transferred from: http://www.51testing.com/html/01/n-867201-2.html

"Problem phenomenon"On-line MySQLDatabase burst a slow query, the DBA observed that the query server IO soared, io occupancy rate of 100%, execution time up to about 7s. SQLThe statements are as follows: SELECT DISTINCT g.*, cp.name as Cp_name, c.name as Category_name, t.name as Type_name fromgm_game G left JOIN Gm_c P cp on cp.id = g.cp_id and cp.deleted = 0 left joins 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"Using explain to view the execution plan, the results are as follows: The problem with this SQL statement is still obvious: a large amount of data was queried (including the number of data bars, and g.*), and then the temporary table order by was used, but eventually only 20 data was returned. The high IO that the DBA observes is because the SQL statement generates a huge temporary table, the memory doesn't fit, and then it's all copied to disk, causing Io to soar. "Optimization scheme"The overall idea of optimization is to split the SQL, separating the sort operation from the operation of querying all information. First statement: Query matching data, only need to query g.id can SELECT DISTINCT g.id from Gm_game G left JOIN gm_cp cp on cp.id = g.cp_id and cp.deleted = 0 Left joins Gm_category c on c.id = g.category_id and c.deleted = 0 left joins 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; The second statement: query for detailed data that meets the criteria, using the in operation to stitch the results of the first SQL to the second SQL SELECT DISTINCT g.*, cp.name as cp_name,c.name as category_name,t.name as T Ype_name Fromgm_game G left join GM_CP cp in cp.id = g.cp_id and cp.deleted = 0 left joins gm_category c on c.id = G.catego ry_id and c.deleted = 0 left joins 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; "measured effect"On the SATA machine Test, before the optimization of about 50s, optimized after the first 0.3s, the second 0.1s, the optimized execution speed is 100 times times more, Io from 100% to less than 1% on the SSD machine testing, optimization before the need for 7s, Optimization after the first 0.3s, the second 0.1s, the optimized execution speed is 10 times times more, Io from 100% to less than 1% can be seen, the optimization of disk IO is a performance bottleneck, SSD speed is significantly faster than SATA, optimized disk is no longer a bottleneck, SSD and SATA performance no difference. "Theoretical Analysis"MySQL may use temporary tables when executing SQL queries, and in general, temporary tables mean lower performance. temporary table StorageThe 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 temporary table uses MySQL's MyISAM storage engine, and normally MySQL creates the memory temp table first. However, after the memory temp table exceeds the configured value, MySQL exports the memory temp table to the disk temp table; LinuxThe default on the platform is/tmp directory, the/tmp directory is small system to pay attention to. scenes that use temporary tables1) The ORDER BY clause differs from the GROUP BY clause, for example: Ordery by Price GROUP by name; 2) in a join query, the order by or group by uses a column that is not the first table, for example: SELECT * from Ta Blea, TableB ORDER by Tablea.price GROUP by Tableb.name 3) in order by using the DISTINCT keyword Ordery by DISTINCT (price) 4) in the SELECT statement Specifies the Sql_small_result keyword sql_small_result means to tell MySQL that the results will be small, please use the memory temporary table directly, do not need to use the index sort Sql_small_result must and group BY, Distinct or distinctrow in general, we don't need to use this option to let MySQL server choose. scenes that use disk staging tables directly1) The table contains the text or BLOB column, 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; temporary table-related configurationTmp_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 final system-created memory temp table size is the minimum value for the above two configuration values. design principles for tablesThe 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. Common ways to avoid temporary tables are: 1) Create an index: Create an index on an order by or group by column, 2) split a very long column: In general, TEXT, blobs, strings greater than 512 bytes, are basically intended to display information, and are not used for query criteria. As a result of table design, these columns should be isolated to a different table. SQL OptimizationIf 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 optimization SQL statements are as follows: 1) Splitting the SQL statement staging table is primarily 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.　　This approach is used in our case to improve the efficiency of sorting and grouping. 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 sort, such as data export, data query and other operations, in this case, the removal of sorting and grouping on the business is not much impact. 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.

Go--Optimize temporary table usage, SQL statement performance 100 times times increase