How to use Optimizer Hints? 1. Optimizer Hints is written in the comment block of the SQL query, starting with / and ending with /, for example: SELECT / NO_INDEX(emp, idx_salary) / * FROM employees emp WHERE salary > 50000; 2. The prompt can be placed in SELECT, INSERT, UPDATE or DELETE statements and act on a specific part; 3. Common prompts include NO_INDEX forcing the specified index, USE_INDEX forcing the specified index, SET_VAR to set session variables, JOIN_FIXED_ORDER forcibly joining in table order, BKA/NO_BKA controls batch key access; 4. Usage scenarios include the wrong index selection of the optimizer, debugging execution plan, complex OLAP query, and compatibility considerations; 5. Notes include prompting non-SQL standards, not fixing fundamental problems, not recommending abuse, and spelling errors may be ignored.

MySQL's query optimizer is usually intelligent, and it determines execution plans based on statistics, indexes, and table structure. But in some cases, you may want to manually interfere with its choice, such as forcing an index or changing the order of the connection. At this time, you can use Optimizer Hints (optimizer prompt) .

Here are some common MySQL optimizer tips and how to use them to help you better control query behavior when needed.

How to use Optimizer Hints?

In MySQL, the optimizer prompt is a comment block written in a SQL query, starting with /* and ending with */ . They do not affect semantics, but only affect how the plan is selected.

For example:

 SELECT /* NO_INDEX(emp, idx_salary) */ * FROM employees emp WHERE salary > 50000;

This statement tells the optimizer not to use idx_salary index to execute this query.

Prompts can be placed in statements such as SELECT, INSERT, UPDATE, or DELETE, and each prompt acts on a specific part (such as a table or JOIN operation).

Common Optimizer Hints and uses

Here are some of the most commonly used tips and their practical application scenarios:

• NO_INDEX(table_name, index_name)
  Force ignore the specified index. Suitable for situations where an index exists but has poor actual results.

• USE_INDEX(table_name, index_name)
  Forces the use of the specified index. You can use this prompt when you know that an index is more suitable for the current query.

• SET_VAR(variable=value)
  Set session variables to temporarily adjust optimizer behavior, such as setting optimizer_search_depth .

• JOIN_FIXED_ORDER()
  Force joins in the order in which tables appear in SQL. This prevents the optimizer from making inefficient connection order selections when joining multiple tables.

• BKA(table_name) / NO_BKA(table_name)
  Enable or disable Batched Key Access, which has an impact on multi-table connection performance.

These prompts can be used in combination, or different prompts can be set for different tables.

When should I use Optimizer Hints?

Most of the time, the optimizer is smart enough to not require human intervention. However, in the following scenarios, the use prompt may bring benefits:

• When the optimizer selects the wrong index, the query becomes slower.
• When you need to debug a specific execution plan, such as testing whether an index is valid.
• In complex queries of OLAP type, the estimation error of the optimizer can lead to inefficiency, and manual intervention is more reliable at this time.
• For compatibility considerations, the original execution path is maintained when migrating the database or upgrading the version.

But remember that prompts are not omnipotent. They make the query more sensitive to data distribution, and if the data structure changes, the original prompts may no longer apply, or even backfire.

Issues to be aware of when using prompts

• The prompt is not part of the SQL standard, and the syntax supported by different database systems may be different.
• The prompt cannot fix the fundamental problem. If you often need to use prompts to "correct" the execution plan, it may be that there is something wrong with the index design or statistics.
• It is not recommended to add prompts in all queries. The problem should be solved first by analyzing the execution plan, adding appropriate indexes, updating statistical information, etc.
• If the prompt is a spelling error or the object that does not exist, MySQL will usually ignore it silently and will not report an error. Therefore, be careful whether the verification prompt takes effect.

Basically that's it. Mastering a few common tips can help you bypass the small pits of the optimizer at critical moments. However, it is still recommended to give priority to optimizing the table structure and index design so that the optimizer can choose a good plan by itself.

The above is the detailed content of Understanding MySQL Optimizer Hints for Query Control. For more information, please follow other related articles on the PHP Chinese website!