Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-33281

Implement optimizer hints like in MySQL 8

Details

    • Optimizer Hints

    Description

      Hints in MySQL

      https://dev.mysql.com/doc/refman/8.0/en/optimizer-hints.html

      1.1 Syntax

      The syntax is

        /*+ hint(s) */
      

      The comment must be at the right place in the query:

      SELECT /*+ hint(s) */ ... ;
      UPDATE /*+ hint(s) */ ... ; 
      ... (SELECT /*+ hint(s) */ ) ...;
      

      hints in other parts of query are silently ignored.

      Hints have a separate parser in sql_hints.yy, invoked by the main lexer through consume_optimizer_hints().

      1.2 Hint effect

      There are hints attached to

      • The whole statement
      • [previously named] query_block
      • table
      • table and its index, or set of its indexes.

      Errors in hint parsing/processing are emitted as warnings.

      Hint processing is "best effort", that is, if the optimizer is not able to follow the directions of some hint it will silently ignore it.

      1.3 Supported hints

      Hints controlling table access methods

      Hint Scope
      MRR, NO_MRR Table, index
      NO_ICP Table, index
      NO_RANGE_OPTIMIZATION Table, index
      INDEX_MERGE, NO_INDEX_MERGE Table, index

      Hints similar to existing hints:

      Hint Scope  
      ORDER_INDEX, NO_ORDER_INDEX Index  
      GROUP_INDEX, NO_GROUP_INDEX Index  
      JOIN_INDEX, NO_JOIN_INDEX Index  
      INDEX, NO_INDEX Index Acts as the combination of JOIN_INDEX, GROUP_INDEX, and ORDER_INDEX

      Hints controlling access method in the context of join optimization

      BKA, NO_BKA Query block, table
      BNL, NO_BNL Query block, table

      Hints controlling join optimization

      Hint Scope
      JOIN_FIXED_ORDER Query block
      JOIN_ORDER Query block
      JOIN_PREFIX Query block
      JOIN_SUFFIX Query block

      Hints controlling other optimizations:

      Hint Scope
      DERIVED_CONDITION_PUSHDOWN, NO_DERIVED_CONDITION_PUSHDOWN Query block, table
      MERGE, NO_MERGE Table
      SEMIJOIN, NO_SEMIJOIN Query block
      SUBQUERY Query block

      Hints serving the system itself

      Hint Scope
      QB_NAME Query block

      Not really optimizer hints

      Hint Scope
      RESOURCE_GROUP Global
      MAX_EXECUTION_TIME Global
      SET_VAR Global

      Not applicable

      Hint Scope
      Only 8.0.20 HASH_JOIN, NO_HASH_JOIN Query block, table
      Not supported in MariaDB SKIP_SCAN, NO_SKIP_SCAN Table, index

      1.3.1 Extra hints needed in MariaDB

      Off the top of the head, we would need:

      Hint Scope
      ROWID_FILTER, NO_ROWID_FILTER Table, index
      SPLIT_MATERIALIZED, NO_SPLIT_MATERIALIZED Table, index

      1.4 Data structures

      Hints have two kinds of data structures:

      • PT_hint_XXX
      • Opt_hint_XXX

      PT_hint_XXX are created by the parser. After the parsing, contextualize() operation is performed (this is fix_fields() right?). During that, some (not all) PT_hint* structures create Opt_hint_XXX data structures and attach them to the appropriate Table_ref (TABLE_LIST) or Query_block (SELECT_LEX).

      1.5 Other details

      1.5.1 Printout in EXPLAIN

      Hints are printed back in EXPLAIN + SHOW WARNINGS output. Hints that weren't processed do not seem to be printed.
      Example:

      explain select /*+ JOIN_ORDER(t20, t21) JOIN_ORDER(t21,t20) */  * from t20, t21 where t20.a=t21.a;
      

      gives

      /* select#1 */ select /*+ JOIN_ORDER(@`select#1` `t20`,`t21`) */ `test`.`t20`.`a` AS `a`...
      

      1.5.2 Implicit Query Block names.

      Printout a few lines above shows @`select#1` for an unnamed query block.

      One can use this syntax in a query, see (USE-IMPLICIT-QB-NAME-EXAMPLE) below.
      However, I was only able to get it to work by:

      • starting the client with --comments
      • using sql_mode='ansi_quotes' and so using double quotes.
        (TODO: verify this)

      1.5.3 VIEWs

      One can create a VIEW with hints but they seem to be ignored when using the VIEW:
      https://gist.github.com/spetrunia/5973166d0686e3787d0f2d37f7faba1d

      Also one cannot control query blocks inside a VIEW (mysql client started with --comments so it doesn't truncate at '#' ): (note: use of ansi_quotes is actually not necessary here )

      mysql>  set sql_mode='ansi_quotes';
      mysql>  explain  select /*+ JOIN_ORDER(@"select#2" twenty,ten) */ * from v1;
      +----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
      | id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                      |
      +----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
      |  1 | PRIMARY     | <derived2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   20 |   100.00 | NULL                                       |
      |  2 | DERIVED     | ten        | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |   100.00 | NULL                                       |
      |  2 | DERIVED     | twenty     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   20 |    10.00 | Using where; Using join buffer (hash join) |
      +----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
      3 rows in set, 2 warnings (0.01 sec)
      

      mysql> show warnings;
      +---------+------+-----------------------------------------------------------------+
      | Level   | Code | Message                                                         |
      +---------+------+-----------------------------------------------------------------+
      | Warning | 3127 | Query block name "select#2" is not found for JOIN_ORDER hint    |
      | Note    | 1003 | /* select#1 */ select "test2"."v1"."a" AS "a" from "test2"."v1" |
      +---------+------+-----------------------------------------------------------------+
      2 rows in set (0.00 sec)
      

      For comparison, one can control a derived table this way (USE-IMPLICIT-QB-NAME-EXAMPLE):

      mysql>  explain  select /*+ JOIN_ORDER(@"select#2" twenty,ten) */ * from (select "ten"."a" AS "a" from ("ten" join "twenty") where ("ten"."a" = "twenty"."a") limit 1000 ) T;
          -> ;
      +----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
      | id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                      |
      +----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
      |  1 | PRIMARY     | <derived2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   20 |   100.00 | NULL                                       |
      |  2 | DERIVED     | twenty     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   20 |   100.00 | NULL                                       |
      |  2 | DERIVED     | ten        | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |    10.00 | Using where; Using join buffer (hash join) |
      +----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
      3 rows in set, 1 warning (0.00 sec)
      

      1.6 Hint processing

      Join order hints are "compiled" into updates to tables' dependency maps.
      The dependency map is updated right before the optimizer starts considering the join orders:

        #0  set_join_hint_deps (join=0x7f6b04155590, hint_table_list=0x7f6b041196a0, type=JOIN_ORDER_HINT_ENUM) at /home/psergey/dev-git2/mysql-8.0-dbg2/sql/opt_hints.cc:492
        #1  0x00005624ab0bc7bd in Opt_hints_qb::apply_join_order_hints (this=0x7f6b041546d0, join=0x7f6b04155590) at /home/psergey/dev-git2/mysql-8.0-dbg2/sql/opt_hints.cc:522
        #2  0x00005624aa9ef752 in JOIN::make_join_plan (this=0x7f6b04155590) at /home/psergey/dev-git2/mysql-8.0-dbg2/sql/sql_optimizer.cc:5349
        #3  0x00005624aa9e1f73 in JOIN::optimize (this=0x7f6b04155590, finalize_access_paths=true) at /home/psergey/dev-git2/mysql-8.0-dbg2/sql/sql_optimizer.cc:694
        #4  0x00005624aaaad439 in Query_block::optimize (this=0x7f6b04119048, thd=0x7f6b04001000, finalize_access_paths=true) at /home/psergey/dev-git2/mysql-8.0-dbg2/sql/sql_select.cc:2013
        #5  0x00005624aab61a0e in Query_expression::optimize (this=0x7f6b04118f60, thd=0x7f6b04001000, materialize_destination=0x0, create_iterators=true, finalize_access_paths=true) at /home/psergey/dev-
      

      1.7 Source code

      Hints code is here:

         964 sql/opt_hints.cc
         797 sql/opt_hints.h
         585 sql/parse_tree_hints.cc
         351 sql/parse_tree_hints.h
         718 sql/sql_hints.yy
         236 sql/sql_lex_hints.cc
         480 sql/sql_lex_hints.h
        4131 total
      

      This doesn't include the checks that the optimizer makes to follow the hints.

      1.8 Hooking hints parser into the main SQL parser

      sql/sql_yacc.yy has this:

      %token  INSERT_SYM 496                    /* SQL-2003-R */
      %token  SELECT_SYM 748                    /* SQL-2003-R */
       
      %type <lexer.optimizer_hints> SELECT_SYM INSERT_SYM REPLACE_SYM UPDATE_SYM DELETE_SYM
      

      That is, the parsed hints are the "payload" of SELECT/INSERT/etc tokens.

      2 MariaDB's implementation notes

      2.1 Error handling

      Monty: we need to introduce @@hint_mode with these options:

      • Default: errors for hint conflicts, warnings for missing tables, keys, etc.
      • "warnings strict mode": Everything is detected and produces warnings.
      • (errors for any kind of error conditions)

      If the hint is impossible to follow, produce warnings or errors according to above.
      (Us giving a warnings where MySQL doesn't isn't considered breach of compatibility)

      If the hint comment is mis-placed, like this:

      SELECT col  FROM /*+ hint_here */ tbl1 ...
      

      then MySQL will not attempt to parse it and will silently ignore it. Monty: we should produce warnings in cases like this. It is easy to recognize hint start /*+ at any point in the query.

      Hint processing

      A question: MySQL's processing of QB_NAME requires hint parsing to use two passes?
      Monty: Can "apply" QB_NAME at parsing stage and then we need one pass.

      Attachments

        Issue Links

          Activity

            oleg.smirnov Oleg Smirnov added a comment -

            psergei, can you please review the PR?

            oleg.smirnov Oleg Smirnov added a comment - psergei , can you please review the PR ?
            psergei Sergei Petrunia added a comment - - edited

            Some review notes (this is not input, perhaps all this should be proof-read and then added as comment):

            Hints architecture
             
            == Parsing ==
            Hints have a separate parser, see sql/opt_hint_parser.*
            The parser is invoked separately for each /*+ ... */ 
            piece in each SELECT. Result of parsing is saved in
            SELECT_LEX::parsed_optimizer_hints.
             
            == Hint "resolution" ==
             
            This is done using "resolve" method of parsed data structures.
            This process
            - Interprets QB_NAME hints and assigns Query Block names.
            - Creates Opt_hints_XXX data structures for hints.
            - Table and Index-based hints are put into their Query Block's 
              Opt_hints_qb data structure. They are NOT yet attached to their tables.
             
            == Hint "adjustment" ==
             
            In Name Resolution/setup_tables() each table calls adjust_table_hints().
            This fills the contents of TABLE_LIST::opt_hints_table.
            Later it is used to check hints pertaining to this table and its indexes.
             
            == Hint hierarchy
             
            Hints have this hierarchy, parent to child:
             
              Opt_hints_global // no such hints currently
                Opt_hints_qb  // just QB_NAME
                  Opt_hints_table  // per-table flags like BKA.
                    Opt_hints_key  //  per-index flags
             
            Some hints need to check their parent, e.g. MRR can be disabled
            on a per-index or a per-table basis.
             
            == Consulting hints
             
            The optimizer checks what hints specify using these:
             
              hint_table_state()
              hint_table_state_or_fallback()
              hint_key_state()
             
            interestingly they want hint name as well as optimizer_switch flag value...
            

            psergei Sergei Petrunia added a comment - - edited Some review notes (this is not input, perhaps all this should be proof-read and then added as comment): Hints architecture   == Parsing == Hints have a separate parser, see sql/opt_hint_parser.* The parser is invoked separately for each /*+ ... */ piece in each SELECT. Result of parsing is saved in SELECT_LEX::parsed_optimizer_hints.   == Hint "resolution" ==   This is done using "resolve" method of parsed data structures. This process - Interprets QB_NAME hints and assigns Query Block names. - Creates Opt_hints_XXX data structures for hints. - Table and Index-based hints are put into their Query Block's Opt_hints_qb data structure. They are NOT yet attached to their tables.   == Hint "adjustment" ==   In Name Resolution/setup_tables() each table calls adjust_table_hints(). This fills the contents of TABLE_LIST::opt_hints_table. Later it is used to check hints pertaining to this table and its indexes.   == Hint hierarchy   Hints have this hierarchy, parent to child:   Opt_hints_global // no such hints currently Opt_hints_qb // just QB_NAME Opt_hints_table // per-table flags like BKA. Opt_hints_key // per-index flags   Some hints need to check their parent, e.g. MRR can be disabled on a per-index or a per-table basis.   == Consulting hints   The optimizer checks what hints specify using these:   hint_table_state() hint_table_state_or_fallback() hint_key_state()   interestingly they want hint name as well as optimizer_switch flag value...

            On the question of handling VIEWs : TiDB's re-implementation of hints does handle views: https://docs.pingcap.com/tidb/stable/optimizer-hints

            psergei Sergei Petrunia added a comment - On the question of handling VIEWs : TiDB's re-implementation of hints does handle views: https://docs.pingcap.com/tidb/stable/optimizer-hints

            Latest (with code cleanup patches): bb-11.6-hints-v2-input2

            psergei Sergei Petrunia added a comment - Latest (with code cleanup patches): bb-11.6-hints-v2-input2

            This is ok to push:

            commit 824c20ae6170275f6fe8d2ff248652022ef7da0c (HEAD -> bb-11.6-hints-v2-input2, origin/bb-11.6-hints-v2-input2)
            Author: Oleg Smirnov <olernov@gmail.com>
            Date:   Mon Sep 16 21:53:20 2024 +0700
             
                MDEV-33281 Make BNL() hint work for join_cache_levels from 0 to 3
                
                BNL() hint effectively increases join_cache_level up to 4 if it is
                set to value less than 4.
                This commit also makes the BKA() hint override not only
                `join_cache_bka` optimizer switch but `join_cache_level` as well.
                I.e., BKA() hint enables BKA and BKAH join buffers both flat and
                incremental despite `join_cache_level` and `join_cache_bka` setting.
            
            

            psergei Sergei Petrunia added a comment - This is ok to push: commit 824c20ae6170275f6fe8d2ff248652022ef7da0c (HEAD -> bb-11.6-hints-v2-input2, origin/bb-11.6-hints-v2-input2) Author: Oleg Smirnov <olernov@gmail.com> Date: Mon Sep 16 21:53:20 2024 +0700   MDEV-33281 Make BNL() hint work for join_cache_levels from 0 to 3 BNL() hint effectively increases join_cache_level up to 4 if it is set to value less than 4. This commit also makes the BKA() hint override not only `join_cache_bka` optimizer switch but `join_cache_level` as well. I.e., BKA() hint enables BKA and BKAH join buffers both flat and incremental despite `join_cache_level` and `join_cache_bka` setting.

            People

              psergei Sergei Petrunia
              psergei Sergei Petrunia
              Votes:
              2 Vote for this issue
              Watchers:
              10 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.