tencent cloud

PrevNext

Feedback

search by keyword

Recent Pages

Documentation
TDSQL-C for MySQL
    Documentation
    Download PDF

    Optimizing SQL Execution Through HINT

    Last updated: 2024-12-17 16:57:18
    Download PDF

      What Is Hint

      The optimizer generally selects the optimal SQL execution plan for you, but in certain scenarios related to statistics estimation errors, cost model fitting biases, and so on, the execution plan generated by the optimizer may not be optimal. At this time, you can guide the optimizer to generate a better execution plan through the Hint mechanism.
      Hints are case-insensitive and appear in the form of /*+ ... */ comments after the SELECT keyword. Multiple Hints are separated by spaces or commas. Below is an example of using Hints.
      SELECT /*+ [hint_text] [hin_text]... */ * FROM ....

      Effective Range of Hints

      Hints take effect at the Query Block level. In DML statements, each Query Block has a QB_NAME (Query Block Name). The read-only analysis engine assigns QB_NAME for each Query Block from left to right in the format @sel_1, @sel_2, and so on. Take the following SQL statement as an example.
      SELECT * FROM (SELECT * FROM t) t1, (SELECT * FROM t) t2;
      This SQL statement contains three Query Blocks. The name of the Query Block in the outermost SELECT layer is sel_1. The names of the two SELECT subqueries are sel_2 and sel_3, incrementally numbered. Using QB_NAME in a Hint allows you to control the effective scope and target of the Hint. If QB_NAME is not explicitly specified in a Hint, the effective scope of the Hint is the current Query Block where the Hint is located, as shown below.
      SELECT /*+ HASH_JOIN_PROBE(@sel_2 t1) */ * FROM (SELECT t1.a, t1.b FROM t t1, t t2 WHERE t1.a = t2.a) t1, t t3 WHERE t1.b = t3.b;
      SELECT /*+ HASH_JOIN_PROBE(t1@sel_2) */ * FROM (SELECT t1.a, t1.b FROM t t1, t t2 WHERE t1.a = t2.a) t1, t t3 WHERE t1.b = t3.b;
      The above two SQL statements use two different methods to specify QB_NAME in the Hint. The first SQL specifies QB_NAME in the first parameter of the Hint, separated by a space from other parameters. The second SQL specifies the effective scope of the Hint by appending QB_NAME to the parameter.

      Supported Hint Overview

      The parameters, semantics, and syntax of Hints related to the read-only analysis engine are shown in the table below.
      Name
      Syntax
      Semantics
      SHUFFLE_JOIN
      SHUFFLE_JOIN([QB_NAME] tbl1_name,tbl2_name ...)
      Specifies using the shuffle method in a JOIN operation to distribute data.
      BROADCASR_JOIN
      BROADCAST_JOIN([QB_NAME] tbl1_name,tbl2_name ...)
      Specifies using the broadcast method in a JOIN operation to distribute data.
      HASH_JOIN_BUILD
      HASH_JOIN_BUILD([QB_NAME] tbl1_name,tbl2_name ...)
      Specifies the Build table in a HASH JOIN operation.
      HASH_JOIN_PROBE
      HASH_JOIN_PROBE([QB_NAME] tbl1_name,tbl2_name ...)
      Specifies the Probe table in a HASH JOIN operation.
      LEADING
      LEADING([QB_NAME] tbl1_name,tbl2_name ...)
      Specifies the Join Order of a JOIN operation.
      SET_VAR
      SET_VAR(setting_name = value)
      Sets system parameters at the SQL level.
      NO_PX_JOIN_FILTER_ID/PX_JOIN_FILTER_ID
      NO_PX_JOIN_FILTER_ID(rf_id1,rf_id2...)/
      PX_JOIN_FILTER_ID(rf_id1,rf_id2...)
      Controls the enabling and disabling of the runtime filter.

      Hint Syntax Details of the Read-only Analysis Engine

      SHUFFLE_JOIN(t1_name, t2_name ...)

      Use instructions
      SHUFFLE_JOIN(t1_name, t2_name ...) controls the read-only analysis engine optimizer to adopt the Shuffle Join algorithm during JOIN operations, shuffling the data of both tables for redistribution, performing a JOIN operation, and returning the results.
      Reference example
      EXPLAIN SELECT /*+ SHUFFLE_JOIN(t1) */ * FROM t1, t2 WHERE t1.id = t2.id;
      In this SQL statement, a JOIN operation is performed on the t1 and t2 tables, and the data distribution method for the Join is specified as Shuffle Join through the SHUFFLE_JOIN Hint. The final plan is as shown in the figure below, where the EXCHANGE TYPE in Rows 2 and 5 of Details have changed to HASH, indicating the use of Hash Shuffle.
      
      Besides specifying a single table, you can also specify intermediate results of the JOIN for data redistribution.
      EXPLAIN SELECT /*+ SHUFFLE_JOIN((t1@sel_2,t2@sel_2)) */ * FROM (SELECT t1.a, t1.b FROM t t1, t t2 WHERE t1.a = t2.a) t1, t t3 WHERE t1.b = t3.b;
      As shown above, by enclosing the t1 and t3 tables in parentheses and specifying the QB_NAME for each table, you can specify using Shuffle Join when the intermediate results of the JOIN between the t1 and t3 tables are joined with the t2 table.
      
      Note:
      This Hint takes effect only when a distributed plan is generated, and is ineffective for standalone plans.

      BROADCAST_JOIN(t1_name [, tl_name ...])

      Use instructions
      BROADCAST_JOIN(t1_name,t2_name ...) controls the read-only analysis engine optimizer to adopt the Broadcast Join algorithm during JOIN operations, broadcasting the specified table data to all nodes, performing a JOIN operation, and returning the results.
      Reference example
      EXPLAIN SELECT /*+ BROADCAST_JOIN(t1) */ * FROM t1, t2 WHERE t1.id = t2.id;
      In this SQL statement, a JOIN operation is performed on the t1 and t2 tables, and the JOIN method is specified as Broadcast Join through the BROADCAST_JOIN Hint. The final plan is as shown in the figure below, where the EXCHANGE TYPE in Rows 2 and 5 of Details have changed to BCJ, indicating the use of Broadcast.
      
      Besides specifying a single table, you can also specify intermediate results of the JOIN for data broadcasting.
      EXPLAIN SELECT /*+ BROADCAST_JOIN((t1@sel_2,t3@sel_1)) */ * FROM (SELECT t1.a, t1.b FROM t t1, t t2 WHERE t1.a = t2.a) t1, t t3 WHERE t1.b = t3.b;
      As shown above, by enclosing the t1 and t3 tables in parentheses and specifying the QB_NAME for each table, you can specify using Broadcast Join when the intermediate results of the JOIN between the t1 and t3 tables are joined with the t2 table. The result is as shown in the figure below.
      
      Note:
      This Hint takes effect only when a distributed plan is generated, and is ineffective for standalone plans.
      The read-only analysis engine optimizer will select the Build side of the Hash Join as the broadcast table. If needed, adjustments can be made in conjunction with HASH_JOIN_BUILD.

      HASH_JOIN_BUILD(t1_name,t2_name ...)

      Use instructions
      HASH_JOIN_BUILD(t1_name, t2_name ...) controls the read-only analysis engine optimizer to adopt the HASH JOIN algorithm for a specified table and use the specified table as the Build side of the HASH JOIN algorithm, that is, build a Hash table with the specified table.
      Reference example
      EXPLAIN SELECT /*+ HASH_JOIN_BUILD(t2)*/ * FROM t t1, t t2 WHERE t1.a = t2.a;
      This SQL statement specifies the t2 table as the Build table in the HASH JOIN, with the final plan as shown in the figure below.
      
      Besides specifying a single table, you can also specify the intermediate results of the JOIN as the BUILD side. An example is as follows.
      EXPLAIN SELECT /*+ HASH_JOIN_BUILD((t1@sel_2,t3@sel_1)) */ * FROM (SELECT t1.a, t1.b FROM t t1, t t2 WHERE t1.a = t2.a) t1, t t3 WHERE t1.b = t3.b;
      As shown above, you just need to enclose the t1 and t2 tables in parentheses and specify the QB_NAME for each table.
      

      HASH_JOIN_PROBE(t1_name,t2_name ...)

      Use instructions
      HASH_JOIN_PROBE(t1_name, t2_name ...) controls the optimizer to adopt the HASH JOIN algorithm for a specified table and use the specified table as the Probe side of the HASH JOIN algorithm, that is, use the specified table as the Probe table of the HASH JOIN.
      Reference example
      EXPLAIN SELECT /*+ HASH_JOIN_PROBE(t2)*/ * FROM t t1, t t2 WHERE t1.a = t2.a;
      This SQL statement specifies the t2 table as the Probe table in the HASH JOIN, with the final plan shown in the figure below.
      
      Besides specifying a single table, you can also specify the intermediate results of the JOIN as the Probe side. An example is as follows.
      EXPLAIN SELECT /*+ HASH_JOIN_PROBE((t1@sel_2,t3@sel_1)) */ * FROM (SELECT t1.a, t1.b FROM t t1, t t2 WHERE t1.a = t2.a) t1, t t3 WHERE t1.b = t3.b;
      As shown above, you just need to enclose the t1 and t2 tables in parentheses and specify the QB_NAME for each table. The final plan is as shown in the figure below.
      

      LEADING(t1_name,t2_name ...)

      Use instructions
      Leading(t1_name,t2_name ...) controls the optimizer to generate Join Order during the Join Reorder phase. The optimizer will determine the Join Order according to the sequence tables appear in the Leading Hint.
      Reference example
      EXPLAIN SELECT /*+ LEADING(t1,t3,t2,t4)*/ * FROM t1,t2,t3,t4 WHERE t1.a = t2.a and t2.a = t3.a and t3.a = t4.a;
      Through a LEADING Hint, this SQL statement explicitly specifies that the t1 table first joins with the t3 table, then with the t2 table, and finally with the t4 table. The final plan is as shown in the figure below.
      
      The JOIN order specified in the above method can only generate a Left Deep Tree. The read-only analysis engine also provides an advanced syntax for LEADING, which can be used together with parentheses to generate a Bushy Tree. An example is as follows.
      EXPLAIN SELECT /*+ LEADING((t1,t3),(t2,t4))*/ * FROM t1,t2,t3,t4 WHERE t1.a = t2.a and t2.a = t3.a and t3.a = t4.a;
      The LEADING Hint in this SQL statement controls the optimizer to first perform a JOIN between the t1 and t3 tables through (t1,t3), then perform a JOIN between the t2 and t4 tables through (t2,t4), and finally merge the results of these two JOIN operations through ((t1,t3),(t2,t4)). The final execution plan is as shown in the figure below.
      
      Note:
      When multiple LEADING Hints exist, it may cause the Hint to become ineffective.
      The Hint becomes ineffective if the optimizer cannot connect the tables according to LEADING.

      SET_VAR(NAME="VALUE")

      Use instructions
      SET_VAR(XXXX="YY") is used to temporarily modify system variables during SQL execution. After the SQL execution is completed, the specified system variables will automatically revert to their original values, as demonstrated below.
      Reference example
      SELECT /*+ SET_VAR(max_threads=64) */ * FROM t1
      This SQL statement temporarily specifies the maximum thread count during SQL execution as 64 by using the SET_VAR Hint.
      Note:
      Not all parameters support the SET_VAR Hint. You should verify whether the target parameter supports Hint modifications before use. For parameters that can be modified through Hints, see System Variables.

      NO_PX_JOIN_FILTER_ID(ID)/PX_JOIN_FILTER_ID(ID)

      Use instructions
      no_px_join_filter_id(ID)/px_join_filter_id(ID) controls the optimizer to disable or enable the RuntimeFilter.
      Reference example
      For specific usage, see Runtime Filter Use Instructions.

      Common Hint Issues

      MySQL Clients Removing Hints Caused the Hint to Be Ineffective

      The MySQL command line client removed Optimizer Hints before version 5.7 by default. To use Hint syntax in these earlier version clients, you need to add the --comments option when launching the client, for example, mysql -h 127.0.0.1 -P 4000 -u root -c.

      Cross-Database Query Without Specifying the Database Name Caused the Hint to Be Ineffective

      For queries involving tables that require cross-database access, you need to explicitly specify the database name in the Hint, otherwise the Hint may be ineffective. Take the following SQL as an example:
      SELECT /*+ SHUFFLE_JOIN(t1) */ * FROM test1.t1, test2.t2 WHERE t1.id = t2.id;
      Since the current t1 table is in the current database, it causes the Hint to be ineffective. The Warning information is as follows.
      mysql> show warnings;
      +---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Level   | Code | Message                                                                                                                                                 |
      +---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Warning | 1815 | There are no matching table names for (t1) in optimizer hint /*+ SHUFFLE_JOIN(t1) */ or /*+ SHUFFLE_JOIN(t1) */. Maybe you can use the table alias name |
      +---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
      1 row in set (0.00 sec)

      QB_NAME Not Specified/Incorrect Caused the Hint to Be Ineffective

      For queries with multiple QB_NAMEs, if the Hint is not written in the Query Block of the target table, you need to explicitly specify QB_NAME in the Hint, otherwise the Query Hint may be ineffective. For example:
      SELECT /*+ HASH_JOIN_PROBE(t2) */ * FROM (SELECT t1.a, t1.b FROM t t1, t t2 WHERE t1.a = t2.a) t1, t t3 WHERE t1.b = t3.b;
      The Hint did not take effect because the t1 table in the Hint did not explicitly specify QB_NAME. The Warning information is as follows.
      mysql> show warnings;
      +---------+------+---------------------------------------------------------------------------------------------------------------------------------+
      | Level   | Code | Message                                                                                                                         |
      +---------+------+---------------------------------------------------------------------------------------------------------------------------------+
      | Warning | 1815 | There are no matching table names for (t2) in optimizer hint /*+ HASH_JOIN_PROBE(t2) */. Maybe you can use the table alias name |
      +---------+------+---------------------------------------------------------------------------------------------------------------------------------+
      1 row in set (0.00 sec)
      At this time, you can query the SQL plan by keyword to confirm the QB_NAME of the Query Block where each table is located.
      

      Incorrect Position Caused the Hint to Be Ineffective

      If the Hint is not correctly placed after the specified keyword according to the Optimizer Hints syntax, it will not take effect. For example:
      SELECT * /*+ SET_VAR(max_threads = 64)) */ FROM t;
      SHOW WARNINGS;
      The Warning information is as follows.
      +---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Level   | Code | Message                                                                                                                                                                                                                 |
      +---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Warning | 1064 | You have an error in your SQL syntax; check the manual that corresponds to your TiDB version for the right syntax to use [parser:8066]Optimizer hint can only be followed by certain keywords like SELECT etc. |
      +---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      1 row in set (0.01 sec)
      In the above example, you need to place the Hint directly after the SELECT keyword.
      
      Contact Us

      Contact our sales team or business advisors to help your business.

      Contact Us
      Technical Support

      Open a ticket if you're looking for further assistance. Our Ticket is 7x24 avaliable.

      Submit a Ticket
      7x24 Phone Support
      Copyright © 2013-2024 Tencent Cloud. All Rights Reserved.
      Privacy PolicyLegalCookie Policy