WL#8243: Index Level Hints for MySQL 5.7

Using the new syntax and infrastructure for hints (see WL#8016 and WL#8017), add
hints to control which indexes should be candidates for the following optimizer
features: Index condition pushdown (ICP), multi-range read (MMR), and range
optimization. 

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.