WL#8243: Index Level Hints for MySQL 5.7
Status: Complete — Priority: Medium
F-1: The hints specified in this work log shall conform with the syntax for index level hints as specified in WL#8017. (Repeated in HLS). F-2: If one of the listed indexes of an MRR hint is chosen for range access, engine optimized MRR (DS-MRR) shall be used if possible. F-3: Engine-optimized MRR (DS-MRR) shall not be used for any of the listed indexes of a NO_MRR hint. F-4: Index condition pushdown (ICP) shall not be used for any of the listed indexes of a NO_ICP hint. F-5: Index range scan shall not be used for any of the listed indexes of a NO_RANGE_OPTIMIZATION hint. NF-1: No warnings shall be given if an MRR hint is ignored because it is not compatible with the selected query plan. NF-2: MRR and NO_MRR hint will only be supported for InnoDB and MyISAM storage engines.
The hints to be added this work log is all index level hints as specified in WL#8017. Index level hints have the following syntax: HINT([@QB_NAME] table index[, index]...) or HINT(table@QB_NAME [index[, index]...]) The optional QB_NAME is used to specify the query block of the specified table(s). See WL#8017 for details. For simplicity, QB_NAME is not included in specifications below. Hints for Multi-range-read (MRR) 1. Specify that engine-optimized multi-range read (DS-MRR) should be used, if possible, for the specified index(es). Syntax: /*+ MRR(table [index[, index]...]) */ If no indexes are specified, the hint will apply to all indexes of the given table. This hint will not guarantee that DS-MRR will be used for the query. However, given that one of the specified indexes is chosen for range or ref access, DS-MRR will be used if possible. In other words: a. If one of the listed indexes is chosen for range access, DS-MRR will will be used if possible. (Note that for some query plans, index range scan needs to deliver sorted output, e.g., Semi-join LooseScan. In such cases, the MRR hint will be ignored.) b. The use of this hint will not guarantee that one of the listed indexes will be used for range access. To ensure this, FORCE/USE INDEX will have to be used. c. If range access is selected on an index not specified by the hint, DS-MRR may or may not be used. (Whether to use DS-MRR will in this case be based on the normal cost-based optimization). d. This hint may cause it to be less likely that one of the specified indexes is used for range access. If the use of DS-MRR by the optimizer is considered to be more expensive that default MRR, the cost of range access for this index will increase, making it more likely that another index is chosen. 2. Specify that engine-optimized multi-range read (DS-MRR) should NOT be used for the specified index(es). Syntax: /*+ NO_MRR(table [index[, index]...]) */ If no indexes are specified, the hint will apply to all indexes of the given table. Note: MRR and NO_MRR hints will only be implemented for InnoDB and MyISAM storage engines (shares the same MRR implementation). A separate worklog will be created for NDB support. Hints for Index Condition Push-Down (ICP) 3. Specify that index condition push-down (ICP) should NOT be used for the specified index(es). Syntax: /*+ NO_ICP(table [index[, index]...]) */ If no indexes are specified, the hint will apply to all indexes of the given table. Note: There is not proposed any hint for forcing the use of Index Condition Pushdown. Such hint would not have much use since ICP will by default be used whenever possible. Hint to turn off range optimizer: 5. Do not invoke range optimizer on specified indexes. Syntax: /*+ NO_RANGE_OPTIMIZATION(table [index[, index]...]) */ If no indexes are specified, the hint will apply to all indexes of the given table. The main purpose of this hint is to be able to avoid range optimization when it will require much resources. (E.g, when the number of ranges becomes very high). Note: Using this hint will in addition to index range scan, also disable use of index merge and loose index scan for the given indexes. There are possible more optimizations that will be less likely to be used if you specify this hint.
DS-MRR cost calculations and the MRR hint ========================================= For an index specified in an MRR hint, the cost calculation for DS-MRR will always be used when estimating the cost of range access on this index. This means that if this cost is larger that the cost estimated for "default" MRR, the cost of range access on this index will be increased by the hint. Hence, using the hint may make it less likely that this index is chosen. Storage-Engine access to hint information ========================================= The choice to use default MRR or engine-optimized MRR is done in the storage engine (in the handler). According to WL#8017, the hint information is either stored in the TABLE_LIST or in the KEY structure depending on whether the hint applies to all indexes in the table or only to some of the indexes. The handler has access to the KEY structure via the TABLE object. However, The TABLE_LIST object is not currently used within the HANDLER. Alternative solutions: a) the handler does lookup both in TABLE->keys and in THD->join->TABLE_LIST b) we add a (new) flag to handler::multi_range_read_info() and handler::multi_range_read_info_const() that says "use engine optimized MRR" for this index. These two functions already have a flag argument so it should likely be easy to add a new flag. c) In case the hint applies to all indexes of a table, the server will iterate add this information to all KEY elements of the table before the handler is involved. Alternative c) will be implemented.
Copyright (c) 2000, 2019, Oracle Corporation and/or its affiliates. All rights reserved.