Analysis of MySQL query optimizer

1. Define

The MySQL query optimizer's job is to select the appropriate execution path for the query statement. The code for the query optimizer is generally variable, which is not the same as the storage engine. Therefore, you need to understand how the latest version of the query optimizer is organized, refer to the appropriate source code. Overall, the optimizer has a lot of the same nature, the MySQL version of the optimizer to achieve the overall grasp, understand the new version of MySQL and other database optimizer are similar.

The optimizer transforms the query statements into equivalent query statements. For example, the optimizer transforms the following statement:

SELECT ... WHERE 5=a;

The equivalent statements after conversion are:

SELECT ... WHERE a=5;

Because the result set of the two statements is consistent, the two statements are equivalent.

Here I need to make a point to note if the query statement does not take an order by. The result of Query Statement 1 is (1,1), (2,2), the result of query Statement 2 is (2,2), (1,1), we think this is equivalent, because the query statement without order BY is unordered and how to sort.

2 Code organization

In the kernel, the Handle_select () function is the top-level function that handles the query statement, which has two branches, one dealing with union and the other with no union, where we just list a simple path to illustrate, and the call level is shown below.

handle_select()
　 mysql_select()
　　 JOIN::prepare()
　　　 setup_fields()
　　 JOIN::optimize()　　　　　　/* optimizer is from here ... */
　　 　 optimize_cond()
　　　 opt_sum_query()
　　　 make_join_statistics()
　　　　 get_quick_record_count()
　　　　 choose_plan()
　　　　　 /* Find the best way to access tables */
　　　　　 /* as specified by the user.　　　　　*/
　　　　　 optimize_straight_join()
　　　　　　 best_access_path()
　　　　　 /* Find a (sub-) optimal plan among all or subset */
　　　　　 /* of all possible query plans where the user　　*/
　　　　　 /* controlls the exhaustiveness of the search.　 */
　　　　　 greedy_search()
　　　　　　 best_extension_by_limited_search()
　　　　　　　 best_access_path()
　　　　　 /* Perform an exhaustive search for an optimal plan */
　　　　　 find_best()
　　　 make_join_select()　　　　/* ... to here */
　　 JOIN::exec()