MySQL 5.6 Query Optimizer improvements

I. ICP

Note the conditions of use of the ICP:

1. Can only be used for level two indexes (secondary index).

2. Explain displays the type value (join type) in the execution plan as range, ref, Eq_ref, or Ref_or_null. And the query needs to access the entire row of data in the table, that is, the query results (index overrides) cannot be obtained directly from the tuple data of level two indexes.

3. The ICP can be used for the MyISAM and Innnodb storage engines, and the partition table is not supported (5.7 will solve this problem).

two.  ORDER BY: Limit ... Optimized

From MySQL 5.6.2 start to ORDER by: Limit ... Optimization:

Select Col1,col2 from TX ORDER by No_key_col limit Offset,rowcount;

From 5.6.2 by treating the sort buffer as a priority queue:

• Scan the table, inserting the select list columns from each selected row in sorted order in the queue. If the queue is full, bump out the last row in the sort order.

• Return the firstNRows from the queue. (IfMwas specified, skip the firstMRows and return the nextNRows.)

Before 5.6.2,previously, the server performed this operation by using a merge file for the sort:

• Scan the table, repeating these steps through the end of the table:

  • Select rows until the sort buffer is filled.

  • Write the firstNRows in the buffer (M+NRows ifMwas specified) to a merge file.

• Sort the merge file and return the firstNRows. (IfMwas specified, skip the firstMRows and return the nextNRows.)

The cost of the table scan was the same for the queue and Merge-file methods, so the optimizer chooses between methods base D on other costs:

• The queue method involves more CPU for inserting rows to the queue in order

• The Merge-file method has I/O costs to write and read the file and CPU cost to sort it

Algorithm:

5.6 Using the queue:

The column of the Select is placed in the queue, when the queue is full of the last queue of the queue, is the largest, sub-large 、..... Push to the front of the queue, and after all the rows of the table are all finished, put the first rowcount out of the queue, sequentially, all sorted in sort buffer .

Before 5.6: Using the merge file

insert row into sort Buffer,sort buffer equivalent to a stack, sORT buffer is fully inserted, and then the currentStackin front ofRowCountThe out queue is placed into the merge file until All of them are ordered and then sorted out by the merge file .

Subsequent...........................

This article is from the My DBA life blog, so be sure to keep this source http://huanghualiang.blog.51cto.com/6782683/1596176

MySQL 5.6 Query Optimizer improvements