Tips for MySQL query optimizer (hit)

If you are dissatisfied with the execution plan selected by the optimizer, you can use several hints provided by the optimizer to control the final execution plan, the specific usage of each hint, the recommended direct reading of the official manual, some hints and versions have a direct relationship, some of the tips you can use are as follows:

High_priority and low_priority:

This tip tells MySQL which statements have a relatively high priority when multiple statements access a table at the same time, and which statements have a relatively low priority.

When high_priority is used for SELECT statements,MySQL places it at the front of the table's queue, rather than waiting in the usual order, andhigh_priority can also be used for INSERT statements. The effect is simply to counteract the effect of the global low_priority setting on the statement.

Low_priority the other way around, it keeps the statement waiting, as long as there are statements in the queue that need access to the same table, even after the request is inserted in front of the statement. Obviously, it's easy to starve yourself,low_priority hints can be used in both select,insert,update and DELETE statements.

Note: These two hints are only valid for the storage engine using table locks, and do not use the InnoDB or other engines with fine-grained locking mechanisms and concurrency control, even in MyISAM It is also important to note that because these two hints cause concurrent insertions to be disabled, performance can be severely degraded. These two hints simply control the queue order of MySQL accessing a data table, and that's all.

Delayed

This hint is valid for insert and Replace, andMySQL returns the statement that uses the hint to the client immediately, and writes the inserted row data to the buffer, then write the data in batches when the table is idle, and the log system uses such hints to be very effective. or other IO applications that need to write large amounts of data but the client does not need to wait for a heads-up statement to complete . But this usage has some love you know, not all storage engines are supported , and this hint will cause the function last_insert_id () to not work correctly.

Straight_join:

This hint can be placed after the Select keyword, or before the table name of any two associated tables, and the first use is to have all the tables in the query are associated in the order in which they appear in the statement, and the second rule is to fix the order in which the two tables are related to each other. Straight_join is useful when MySQL does not select the correlation sequence correctly, or because there are too many possible sequences, so that MySQL cannot evaluate all the association orders, andif the associated table may be in too many order, it can cause MySQL spends a lot of time in the statistics state. You can use the explain statement to see the association order, then add this hint and then use explain to see if there are any changes.

Sql_small_result and Sql_big_result:

These two hints are only valid for select, and they tell the optimizer how to use temporal tables and their ordering for group by or distinct queries, andSql_small_result tells the optimizer that the result assembly is small, You can place the result set in an indexed temporary table in memory to avoid sorting operations, and if it is Sql_big_result, tell the optimizer that the result set may be very large, and it is recommended that you use the disk staging table for sorting operations.

Sql_cache and Sql_no_cache:

This tip tells MySQL whether the result set should be cached in the query cache, such asselect Sql_cache|sql_no_cache * from Tb_name, immediately following the SELECT keyword.

Sql_calc_found_rows:

Strictly speaking, this is not an optimizer hint, it does not tell the optimizer anything about the execution plan, it will let MySQL return the result set containing more relevant information, the query plus the hint that MySQL will calculate the removal The total number of result sets that the query will return after the limit clause. Instead of actually returning only the result set required by the limit, the total number of result sets can be obtained by Found_row (). generally do not use this hint .

For update and lock in share mode:

This is not a real optimizer hint, these two tips mainly control The lock mechanism of the SELECT statement, but only for the implementation of the row-level lock engine, using the hint to match the query criteria of the data row lock, for the insert: The SELECT statement does not require these two hints, because for mysql5.0 and newer versions will default to these records plus read locks. The built-in support for both prompts is InnoDB, and it is also important to remember that these two hints will make some optimizations unusable, such as an index overlay scan,InnoDB cannot lock the row exclusively without accessing the primary key, because the row's version information is saved in the primary key. These two hints are often exploited and can easily cause a server lock contention problem and should be avoided as much as possible. You can often use other, better ways to achieve the same goal.

Use Index,ignore index and Force index:

These hints tell the optimizer to use or not use or enforce which of the query records have been used. In mysql5.0 and earlier versions, these hints did not affect the optimizer to select that index for sorting and grouping, after 5.1 and

Version can specify whether sorting and grouping are valid by adding options for order by and for group by. Force Index and use index are basically the same, except for a point where Forceindex tells the optimizer that the cost of a full table scan is much higher than the index scan. Even if the index is actually not very useful. The optimizer also uses the index specified by force index.

In mysql5.0 and later versions, a number of new parameters have been added to control the behavior of the optimizer:

Optimizer_search_depth:

This parameter controls the limit of the optimizer's execution plan, and if the query is in a statistics state for a long time , consider lowering the value of this parameter by default to 62, which also controls the maximum number of associated tables.

Optimizer_prune_level:

The parameter is open by default, 1, which allows the optimizer to decide whether to skip certain execution plans, depending on the number of rows that need to be scanned

Optimizer_switch:

This variable contains some flag bits for the on /off optimizer feature, such as mysql5.1, whichcan be used to control the disabling of index merging.

5.5 The default is:

Index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_ Pushdown=on

5.6 The default is:

Index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_ Pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off, Materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_ Extensions=on

These three parameters, the first two parameters are used to control the optimizer can take a few shortcuts, these shortcuts can make the optimizer in the processing of very complex SQL statements can still be very efficient, but it may also let the optimizer miss some really optimal execution plan, so should be based on actual needs to modify these parameters.

Note: It is not good to be smart in front of the optimizer, because this does not only result in very little, but also brings a lot of extra work to the post-maintenance, when the MySQL version upgrade, this problem is very prominent, you set the optimizer hints will likely make the new version of the optimizer's optimization strategy invalidated. Do not use these hints to change the default execution plan unless specifically required. mysql5.5 and 5.6 have great improvements in all aspects, generally the upgrade is smooth, but it is still recommended to check the details, you can use the pt-upgrade tool in Percona Toolkit to check the new version of the SQL returns the same result as the old version.

Tips for MySQL query optimizer (hit)