查询优化器_mysql查询优化器 - CSDN
精华内容
参与话题
  • Mysql 查询优化器

    2019-04-25 19:34:30
    MySQL Query Optimizer(查询优化器) 图中Optimizer部分为本文研究的重点,主要对Parser解析之后的SQL,根据统计的数据,对访问代价进行权衡,制定执行计划。查询优化器是MySQL中比较活跃的一部分,代码会经常变动。...

    MySQL Query Optimizer(查询优化器)

    在这里插入图片描述图中Optimizer部分为本文研究的重点,主要对Parser解析之后的SQL,根据统计的数据,对访问代价进行权衡,制定执行计划。查询优化器是MySQL中比较活跃的一部分,代码会经常变动。但整体而言,对查询优化器整体把握和理解之后,其他的版本也基本可以轻松理解。以下内容将通过对MySQL 5.5.20版本的源码进行分析,供大家参考。

    代码分析

    1、Parser阶段

    查询优化器相关的代码主要在sql/sql_select.cc文件中,而在执行该部分代码时,需要对之前的内容做简要说明。本文从Parser阶段case SQLCOM_SELECT:中调用execute_sqlcom_select()函数开始(参看源码sql/sql_parse.cc:2124行),该函数是实际的sql执行阶段,而sql真正执行需要按照一定的规则去执行。因此,需要先制定一个执行计划。但制定计划不能随便制定,需要有一定的依据。并且不能保证所有人写的SQL都是最优的,也就不能根据SQL就简单制定一个规则。于是,出现了SQL Optimizer,该部分不仅对SQL进行了优化,并且根据优化的结果制定查询的执行计划。最终根据执行计划执行,得到数据结果并返回。从代码看,execute_sqlcom_select()函数做了以上的所有工作。

    以下将从execute_sqlcom_select()函数开始,逐层次的分析MySQL的查询优化是如何实现的。

    2、Optimizer阶段

    execute_sqlcom_select()函数中,调用了handle_select()函数,而该函数正是处理SQL查询的“入口函数”。

    2.1 函数调用层次

    首先,将主要的函数调用层次列出,函数层次以之前的“|”标示,以“>”标示进入函数,以“<”标示退出函数,红色标示为递归调用的函数,绿色和蓝色标示为互斥操作。

    | | | | >handle_select

    | | | | | >mysql_select/mysql_union

    | | | | | | >JOIN::prepare

    | | | | | | | >setup_tables

    | | | | | | |

    | | | | | | | >setup_fields

    | | | | | | |

    | | | | | | | >setup_without_group

    | | | | | | | | >setup_conds

    | | | | | | | |

    | | | | | | | | >setup_order

    | | | | | | | |

    | | | | | | | | >setup_group

    | | | | | | | |

    | | | | | | |

    | | | | | | | >setup_procedure

    | | | | | | |

    | | | | | |

    | | | | | | >JOIN::optimize

    | | | | | | | >simplify_joins

    | | | | | | |

    | | | | | | | >build_bitmap_for_nested_joins

    | | | | | | |

    | | | | | | | >optimize_cond

    | | | | | | |

    | | | | | | | >prune_partitions

    | | | | | | |

    | | | | | | | >make_join_statistics

    | | | | | | | | >make_select

    | | | | | | | |

    | | | | | | | | >get_quick_record_count

    | | | | | | | | | >SQL_SELECT::test_quick_select

    | | | | | | | | | | >get_mm_tree

    | | | | | | | | | |

    | | | | | | | | | | >get_best_group_min_max

    | | | | | | | | | |

    | | | | | | | | | | >get_key_scans_params

    | | | | | | | | | |

    | | | | | | | | | | >get_best_ror_intersect

    | | | | | | | | | |

    | | | | | | | | | | >get_best_disjunct_quick

    | | | | | | | | | |

    | | | | | | | | | | >TRP_RANGE::make_quick

    | | | | | | | | | |

    | | | | | | | | |

    | | | | | | | |

    | | | | | | | | >choose_plan

    | | | | | | | | | >optimize_straight_join

    | | | | | | | | | | >best_access_path

    | | | | | | | | | |

    | | | | | | | | |

    | | | | | | | | | >greedy_search

    | | | | | | | | | | >best_extension_by_limited_search

    | | | | | | | | | | | >best_access_path

    | | | | | | | | | | |

    | | | | | | | | | |

    | | | | | | | | |

    | | | | | | | |

    | | | | | | |

    | | | | | | | >get_best_combination

    | | | | | | |

    | | | | | | | >make_join_select

    | | | | | | |

    | | | | | |

    | | | | | | >JOIN::exec

    | | | | | | | >do_select

    | | | | | | |

    | | | | | |

    | | | | | | >st_select_lex::cleanup()

    | | | | | |

    | | | | |

    | | | |

    2.2 Optimizer流程图

         通过以上的函数调用层次,对optimizer的处理流程图形化,方便清晰的理解和查看具体的流程。
    

    由于函数调用层次不利于清晰的显示处理流程,因此以下流程图摒弃了调用层次的界限,更关注逻辑处理。

    2.3 函数详细介绍

    1. handle_select():(sql/sql_select.cc:265)

    handle_select()函数用于执行查询操作。该函数对Union进行了判断,如果查询SQL中不包含union关键字,函数直接执行mysql_select()函数处理;否则,将执行mysql_union()函数。

    1. mysql_select()/mysql_union():(sql/sql_select.cc:2498|sql/sql_union.cc:31)

        mysql_select()函数是单个select查询的“入口函数”。该函数执行SQL优化的一些列操作。区别于mysql_select()函数,mysql_union()函数逐个执行union中单个select查询优化的一系列操作。执行SQL优化的过程调用相同的函数实现,仅在执行前期的处理过程不一致。
      
    2. JOIN::prepare():(sql/sql_select.cc:499)

        JOIN::prepare()函数用于为整个查询做准备工作。其中包括准备表、检查表是否可以访问、检查字段、检查非group函数、准备procedure等工作。以下是该过程调用的重要函数,具体查看响应函数的解释。
      

    该函数简化后,并且忽略函数调用层次的流程图如下图所示:

    1. setup_tables():(sql/sql_base.cc:7963)

        setup_tables()函数用于准备查询所需要的所有表。该函数被setup_tables_and_check_access()函数(sql/sql_base.cc:8058)调用,而setup_tables_and_check_access()函数用于准备表以及检查表的是否可以访问,在JOIN::prepare()函数中被调用,而该函数分别调用setup_tables()和check_single_table_access(),其中后者对所有需要的表,循环进行检查是否可以访问。
      
    2. setup_fields():(sql/sql_base.cc:7825)

    setup_fields()函数用于检查所有给定字段是否存在。该函数检查所有查询中给定的数据表的字段,对数据表的列进行查找。其中调用了更深层次的函数,如下所示:find_field_in_tables() -> find_field_in_table_ref() -> find_field_in_table()。其中find_field_in_tables()根据查询的表,循环调用find_field_in_table_ref()函数。

    1. setup_without_group():(sql/sql_select.cc:446)

        setup_without_group()函数用于准备非group函数。该函数是一个内嵌函数,但是该函数的逻辑并不简单。该函数调用了setup_conds()、setup_order()、setup_group()函数,具体的函数解释见下面内容。
      
    2. setup_conds():(sql/sql_base.cc:8379)

    setup_conds()函数用于准备SQL查询中where的所有条件。该函数对where中给定的条件,检查对应数据表的字段。其中调用了find_field_in_table_ref()函数,用于检查数据表中的字段。

    1. setup_order():(sql/sql_select.cc:14996)

        setup_order()函数用于准备SQL查询中order by的条件。该函数对每个order by字段都调用了find_order_in_list()函数,而find_order_in_list()用于处理group by和order by的字段。
      
    2. setup_group():(sql/sql_select.cc:15037)

        setup_group()函数用于初始化SQL查询中group by的条件。该函数同setup_order()的处理逻辑类似,不在赘述。
      
    3. setup_procedure():(sql/procedure.cc:80)

       setup_procedure()函数用于准备procedure处理。
      

    注意:除以上函数调用外,JOIN::prepare()过程中如果存在order by和group by的条件时,在调用以上函数之后,还有一系列操作,比如分配额外的隐藏字段、分离sum结果和item树等内容。

    1. JOIN::optimize():(sql/sql_select.cc:854)

       JOIN::optimize()是整个查询优化器的核心内容。查询优化主要对Join进行简化、优化where条件、优化having条件、裁剪分区partition(如果查询的表是分区表)、优化count()/min()/max()聚集函数、统计join的代价、搜索最优的join顺序、生成执行计划、执行基本的查询、优化多个等式谓词、执行join查询、优化distinct、创建临时表存储临时结果等操作。以下将对该过程调用的重要函数,进行详细的讲解。
      

    该函数简化后的流程图如下图所示:

    1. simplify_joins():(sql/sql_select.cc:8940)

    simplify_joins()函数简化join操作。该函数将可以用内连接替换的外连接进行替换,并对嵌套join使用的表、非null表、依赖表等进行计算。该函数的实现是一个递归过程,在递归中,所有的特征将被计算,所有可以被替换的外连接被替换,所有不需要的括号也被去掉。此外,该函数前的注释中,给出了多个例子,用于解释外连接替换为内连接的一些特征。

    Sample 1:

    SELECT * FROM t1 LEFT JOIN t2 ON t2.a=t1.a WHERE t2.b < 5

    ==>

    SELECT * FROM t1 INNER JOIN t2 ON t2.a=t1.a WHERE t2.b < 5

    ==>

    SELECT * FROM t1, t2 ON t2.a=t1.a WHERE t2.b < 5 AND t2.a=t1.a

    Sample 2:

    SELECT * from t1 LEFT JOIN (t2, t3) ON t2.a=t1.a t3.b=t1.b WHERE t2.c < 5

    ==>

    SELECT * FROM t1, (t2, t3) WHERE t2.c < 5 AND t2.a=t1.a t3.b=t1.b

    Sample 3:

    SELECT * FROM t1 LEFT JOIN t2 ON t2.a=t1.a LEFT JOIN t3 ON t3.b=t2.b WHERE t3 IS NOT NULL

    ==>

    SELECT * FROM t1 LEFT JOIN t2 ON t2.a=t1.a, t3 WHERE t3 IS NOT NULL AND t3.b=t2.b

    ==>

    SELECT * FROM t1, t2, t3 WHERE t3 IS NOT NULL AND t3.b=t2.b AND t2.a=t1.a

    1. build_bitmap_for_nested_joins():(sql/sql_select.cc:9135)

       build_bitmap_for_nested_joins()函数为每个嵌套join在bitmap中分配一位。该函数也是一个递归过程,返回第一个没有使用的bit。
      
    2. optimize_cond():(sql/sql_select.cc:9405)

    optimize_cond()函数分别对where条件和having条件建立多个等价谓词,并且删除可以被推导出的等价谓词。该函数调用build_equal_items()函数(sql/sql_select.cc:8273)用于该处理过程,该函数是一个递归过程,并在函数前,列举了多个实例,供参考和理解。

    Sample 1:

    SELECT * FROM (t1,t2) LEFT JOIN (t3,t4) ON t1.a=t3.a AND t2.a=t4.a WHERE t1.a=t2.a;

    ==>

    SELECT * FROM (t1,t2) LEFT JOIN (t3,t4) ON multiple equality (t1.a,t2.a,t3.a,t4.a)

    Sample 2:

    SELECT * FROM (t1,t2) LEFT JOIN (t3,t4) ON t1.a=t3.a AND t3.a=t4.a WHERE t1.a=t2.a

    ==>

    SELECT * FROM (t1 LEFT JOIN (t3,t4) ON t1.a=t3.a AND t3.a=t4.a),t2 WHERE t1.a=t2.a

    Sample 3:

    SELECT * FROM (t1,t2) LEFT JOIN (t3,t4) ON t2.a=t4.a AND t3.a=t4.a WHERE t1.a=t2.a

    ==>

    SELECT * FROM (t2 LEFT JOIN (t3,t4)ON t2.a=t4.a AND t3.a=t4.a), t1 WHERE t1.a=t2.a

    此外,该函数调用propagate_cond_constants()函数(sql/sql_select.cc:8763),用于处理(a=b and b=1)为(a=1 and b=1)操作,该函数也是一个递归过程。调用remove_eq_conds()函数(sql/sql_select():9617),用于删除(a=a以及1=1、1=2)条件,该函数调用了internal_remove_eq_conds()递归处理函数(sql_select.cc:9462)。

    1. prune_partitions():(sql/opt_range.cc:2611)

    prune_partitions()函数通过分析查询条件,查找需要的数据分区。该函数仅对分区表有效,普通的表不做处理。

    1. make_join_statistics():(sql/sql_select.cc:2651)

       make_join_statistics()函数用于计算最好的join执行计划。该过程较复杂,分为以下几个步骤:
      

    首先如果join是外连接,利用Floyd Warshall(弗洛伊德)算法建立传递闭包依赖关系,该过程较复杂,具体参考该算法的一些内容。

    之后进行主键或唯一索引的常量查询处理,该过程是一个循环过程,与执行计划中的ref类型有关。

    接下来,计算每个表中匹配的行数以及扫描的时间,该过程得到的结果是估计的值,并非实际查询需要的记录数和时间。并且调用了一下的make_select()函数和get_quick_recond_count()函数,具体内容参照以下相应函数的分析。该过程从生成执行计划来看,与index和full类型有关。

    最后,根据以上统计的记录数和时间,选择一个最优的join顺序。实际该处理逻辑,调用choose_plan()函数处理,参照以下choose_plan()函数分析。

    该函数简化后的流程图如下图所示:

    1. make_select():(sql/opt_range.cc:1067)

    make_select()函数是普通查询和快速范围查询处理的函数。该函数通过计算查询记录在cache中的偏移量除以ref长度的商,得到查询的行数。如果cache中没有缓存,那么退出该过程,执行get_quick_record_count()函数处理过程。

    1. get_quick_record_count():(sql/sql_select.cc:2602)

    get_quick_record_count()函数用于估计查询需要每个表的记录数。该过程调用SQL_SELECT::test_quick_select()函数用于具体的处理逻辑,具体见以下函数的详细分析过程。

    1. SQL_SELECT::test_quick_select():(sql/opt_range.cc:2149)

    SQL_SELECT::test_quick_select()函数用于实际的处理估计范围查询需要的记录数和时间。从函数名和注释来看,该函数是用于检查范围查询中key是否可用,而从函数逻辑来看,该函数主要用于对范围查询进行处理。

    处理逻辑有以下几步:

    首先,根据记录数,计算处扫描时间(scan_time)和读取(read_time)。计算公式如下:

    scan_time=records/5 +1 //其中5表示每个where条件进行比较的次数,该值为常量。

    read_time=((file_len)/4096+2)+scan_time+1.1 //其中4096表示IO buffer size

    如果read_time的值小于2.0并不强制使用索引,那么就不使用索引进行查询。否则,进行索引查询。

         接着,根据查询条件,调用get_mm_tree()函数生成所有查询条件的一棵查询树。接下来的一系列操作将对查询树进行优化。具体get_mm_tree()函数的分析参照以下内容。
    

    接下来,计算最小覆盖索引进行全索引读的代价。该逻辑需要首先查找合适的索引,以便于进行全表扫描。由于辅助索引(secondary key)是主键索引(primary key)的子集,并且无法比较两者的键长对IO的影响,因此,mysql选择了首先利用辅助索引,只有当辅助索引不能覆盖查找的条件或者辅助索引包含表中的列,才选择主键进行查询。根据索引,计算索引读取(key_read_time)时间,计算公式如下。如果得到的read_time大于key_read_time,则read_time=key_read_time。

    key_read_time=(records+keys_per_block-1)/keys_per_block

    keys_per_block=(block_size/2/(key_len+ref_len+1))

    //block_size是文件的block大小,mysql默认为16K;

    //key_len是索引的键长度;

    //ref_len是主键索引的长度;

    然后,调用get_best_group_min_max()函数,用于获得最优的处理group by中max/min的查询计划。具体内容参照该函数的具体介绍。

    如果不需要索引合并,调用get_key_scans_params()函数,获得最优的范围查询计划,具体处理逻辑参照以下函数详解。调用get_best_ror_intersect ()函数,获得最优的非覆盖的ROR交叉执行计划,调用get_best_covering_ror_intersect()函数根据get_best_ror_intersect()函数提供的数据建立覆盖的ROR交叉执行计划。

    否则,调用get_best_disjunct_quick()函数,获得索引合并的最优执行计划。具体内容参照以下具体介绍。

    最后,调用TRP_RANGE::make_quick()函数,根据最优的范围查询计划,执行快速查询。详细见以下函数的详细介绍。

    该函数简化后的流程图如下图所示:

    1. get_mm_tree():(sql/opt_range.cc:5518)

    get_mm_tree()函数用于根据所有查询条件的键,生成一颗查询树。如果查询条件的类型是COND_ITEM类型,即查询条件也是一个查询条件,则递归调用get_mm_tree()函数生成子查询树。如果为简单的查询类型,则构建查询树。如果查询条件类型是between、in以及默认调用get_full_func_mm_tree()函数(sql/opt_range.cc:5474),对查询条件进行转换后,构建查询树。例如:(c BETWEEN a AND b)转换为(c>=a AND c<=b),(c NOT BETWEEN a AND b)转化为(c<=a or c>=b),(f in (a,b,c))转换为(f=a OR f=b OR f=c)。

    get_full_func_mm_tree()函数的主要处理逻辑在get_func_mm_tree()函数(sql/opt_range.cc:5180)中实现,后者对不同的情况进行了处理。

    1. get_best_group_min_max():(sql/opt_range.cc:9407)

    get_best_group_min_max()函数用于获得最优的处理group by中max/min的查询计划。该处理过程分别对不同的组合情况进行了处理,具体的处理过程较复杂,设计到很多数学推理和逻辑组合的知识。

    1. get_key_scans_params():(sql/opt_range.cc:4923)

    get_key_scans_params()用于获得最优的范围查询计划。如果使用索引查找,那么调用check_quick_select()函数获得估计查询的记录数,在仅使用一个索引查找的情况下,读取时间(found_read_time)的值如表中(1)公式所示。否则,读取时间的值如表中(2)的公式所示。如果此时read_time大于found_read_time的值,则read_time=found_read_time,生成read_plan并且返回。

    (1)只使用一个索引读取

    found_read_time=key_read_time+cpu_cost+0.01

    //0.01是为了避免范围扫描和索引扫描排序

    cpu_cost=found_records/5

    //5表示每个where条件进行比较的次数

    //found_records表示check_quick_select()获得的查询记录数。

    (2)多个范围索引读取

    found_read_time=found_records+range_count+cpu_cost+0.01

    该函数的核心是调用check_quick_select()函数(sql/opt_range.cc:7519)获取查找的记录数,而check_quick_select()函数调用check_quick_keys()函数(sql/opt_range.cc:7628)通过聚集主键索引获得记录数。而check_quick_keys()函数是一个递归过程,通过扫描主键索引的SEL_ARG树,估计查询的记录数。SEL_ARG结构在定义之前的说明中,对该结构进行了详细解释,在此不做介绍。

    1. get_best_ror_intersect():(sql/opt_range.cc)

    get_best_ror_intersect()函数获得最优的非覆盖的ROR交叉执行计划(ROR-intersection plan)。该过程在函数注视中命名为“非覆盖ROR-intersection查询算法(non-covering ROR-intersection search algorithm)”,从算法描述中可以看出,主要用于获得or查询的最小查询记录数和读取时间。

    1. get_best_disjunct_quick():(sql/opt_range.cc)

    get_best_disjunct_quick()函数用于获得索引合并的最优执行计划。该过程中使用SEL_IMERGE结构,该结构用于索引的合并操作。索引合并的代价计算在函数的注释中给出,如表中所示。其中index_reads是所有通过索引读的代价之和;rowid_to_row_scan如果是聚集主键索引,那么读的代价就是主键索引读的代价,否则根据文件的偏移量和block大小计算,此外还考虑到block中包含记录数和空block的情况;unique_use计算唯一索引读的代价。

    index_merge_cost=index_reads+rowid_to_row_scan+unique_use

    1. TRP_RANGE::make_quick():(sql/opt_range.cc:1881)

    TRP_RANGE::make_quick()函数根据最优的范围查询计划,执行范围快速查询确定执行计划。其中调用get_quick_select()函数(sql/opt_range.cc:7903)通过键值和SEL_ARG树创建QUICK_RANGE_SELECT结构,并调用get_quick_keys()函数(sql/opt_range.cc:7945)获得所有可能的子范围。其中get_quick_keys()是一个递归过程。

    1. choose_plan():(sql/sql_select.cc:4913)

       choose_plan()函数用于获得一个最优的join顺序的执行计划。如果使用STRAIGHT_JOIN,调用optimize_straight_join()函数进行优化。否则,调用greedy_search()函数搜索一个最优的执行计划(之前的实现处理函数是find_best(),以后将要废弃)。optimize_straight_join()和greedy_search()函数参照以下详细分析。
      

    该函数简化后的流程图如下图所示:

    1. optimize_straight_join():(sql/sql_select.cc:5108)

    在分析optimize_straight_join()函数逻辑之前,首先对STRAIGHT_JOIN这种join方式进行简单介绍。STRAIGHT_JOIN 是 MySQL 对标准 SQL 的扩展,用于在多表查询时指定表载入的顺序。在 JOIN 表连接中,同样可以指定表载入的顺序[1]。理解了这一点,那么以下逻辑就容易理解了。

         optimize_straight_join()函数对每一个join表,基于现有的部分执行计划,查找最优的访问计划,并将该计划添加到执行计划中。该过程主要调用best_access_path()函数,具体处理逻辑参照以下具体分析内容。
    
    1. best_access_path():(sql/sql_select():4365)

    best_access_path()基于现有“部分查询计划(partial QEP)”查找最优的访问计划,并添加到执行计划中。该过程根据当前可用的索引区的数目,对每一部分索引查找最优的计划,并计算访问计划的记录数和读取时间。其中针对不同的索引类型,对记录数和读取时间的估计方式也不同。

    1. greedy_search():(sql/sql_select.cc:5219)

    greedy_search()函数使用混合的贪婪/有限穷举搜索算法,查找最优的查询计划。每次评估还未优化的表的期望值(promising),选择期望最大的表来扩展当前“部分查询计划(partial QEP)”。期望值(promising)最大的表是带来的扩展代价最小的没有优化的表。该过程调用best_extension_by_limited_search()函数,用于实际有限次的穷举搜索最优的查询计划。具体best_extension_by_limited_search()函数的内容见以下分析。

    1. best_extension_by_limited_search():(sql/sql_select.cc:5425)

       best_extension_by_limited_search()函数用于有限次的穷举搜索最优查询计划。该过程利用递归的深度优先搜索的方式进行搜索,搜索的深度通过变量“search_depth”控制。从该函数之前的注释中,给出了该函数的算法思想。
      

    从函数逻辑来看,该函数依次每个未被优化的join表,调用best_access_path()函数,查找最优的访问计划,估计当前查询的记录数和读取时间,与现有的“部分查询计划”的查询记录数和读取时间进行比较,如果优于现有的查询计划,则保存当前最优的访问计划到“部分查询计划”。

    如果目前的搜索的深度大于1,,递归调用best_extension_by_limited_search()函数,并且search_depth减1。否则,当前保存的是“部分最优的查询计划(best partial QEP)”或者“完全最优的查询计划(best complete QEP)”。

    该函数简化后的流程图如下图所示:

    1. get_best_combination():(sql/sql_select.cc:5793)

    get_best_combination()函数根据greedy_search()函数得到的“最优查询计划”,获得最优的join结构,为执行查询做准备。

    1. make_join_select():(sql/sql_select.cc:6374)

    make_join_select()函数用于执行各种不同情况的join查询。该函数通过join时,连接表的不同搜索方式(唯一索引查找、ref查找、快速范围查找、合并索引查找、全表扫描等不同方式),进行join操作的处理。

    1. JOIN::exec():(sql/sql_select.cc:1817)

    JOIN::exec()函数根据执行计划,针对不同的查询(存储过程、表定义查询、where查询、having查询、order by条件、join查询(单纯的join,join和group,join、group by以及order by等不同查询)等查询),进行相应的查询处理。join查询中并调用do_select()函数,对所有的join表执行join查询操作,具体函数处理如下所示。

    1. do_select():(sql/sql_select.cc:11431)

    do_select()函数对所有的join表进行join查询操作。并将join查询后的结果通过socket发送。

    1. st_select_lex::cleanup():(sql/)

    st_select_lex::cleanup()函数用于清理查询的临时结构、临时变量。该过程调用JOIN::destroy()函数清理join操作使用的存储结构和变量。

    总结

    通过对mysql查询优化器的分析,对查询的逻辑处理流程有一定的深入了解。通过对简单的语句进行调试并得出以上分析文档,以上文档更侧重函数的逻辑处理过程。由于时间原因,对各种不同类型的查询,未进行逐一的跟踪调试,对工作原理和优化策略不敢妄自分析。因此,对不同类型的查询,mysql查询优化器的工作原理和优化策略,还需要进一步的深入研究。

    由于跟踪调试语句的局限性和个人目前能力有限,对源码处理逻辑的理解不可避免存在错误,但本人已尽力做到准确。希望有相关研究经验的同事,给予批评和指正,共同讨论、共同进步。

    展开全文
  • 实际项目中,我们都会用到sql,根据个人的习惯等一些因素sql各有不同,这些sql 到MYSQL数据时不会直接拿过来执行,MYSQL 会先把sql交给查询优化器,将sql进行优化,按照mysql最优的方式去执行。    ...

           实际项目中,我们都会用到sql,根据个人的习惯等一些因素sql各有不同,这些sql 到MYSQL数据时不会直接拿过来执行,MYSQL 会先把sql交给查询优化器,将sql进行优化,按照mysql最优的方式去执行。

    一、SQL JOIN 转换(join_preparation)


           对于一个sql 语句,查询优化器先看看能不能转换为JOIN,再讲JOIN进行优化 如一下sql :
    select * from test_db.t1 as t1 where t1.a in (select t2.a from test_db.t2 as t2 )
    

    查询优化器会将sql转换为:

    select `test_db`.`t1`.`a` AS `a`,`test_db`.`t1`.`b` AS `b`,`test_db`.`t1`.`c` AS `c`,`test_db`.`t1`.`d` AS `d`,`test_db`.`t1`.`e` AS `e` 
    from `test_db`.`t1` semi join (`test_db`.`t2`) where (1 and (`test_db`.`t1`.`a` = `test_db`.`t2`.`a`)) limit 0,1000
    

    semi join 为MYSQL内部关键字,不对外提供使用。后面 JOIN 优化时会详细讲


    二、SQL 优化分为:

    1. 条件优化
    2. 计算全表扫描成本
    3. 根据查询条件,找出所有可用的索引
    4. 计算各个索引的访问成本
    5. 选择成本最小的索引以及访问方式

    开启查询优化器日志

    为了能查看查询优化器优化的细节,我们需要开启查询优化器日志。

    --开启
    set optimizer_trace="enabled=on";
    
    --执行sql
    --查询日志信息
    select * from information_schema.OPTIMIZER_TRACE;
    
    --关闭
    set optimizer_trace="enabled=off";
    

    1、条件优化

    a. 等值传递

    select * from test_db.t1 t1 where t1.a=t1.b and t1.b=t1.c and t1.c = 2;
    

    查询优化器进行等值传递处理:

    "condition_processing": {
              "condition": "WHERE",
              "original_condition": "((`test_db`.`t1`.`a` = `test_db`.`t1`.`b`) and (`test_db`.`t1`.`b` = `test_db`.`t1`.`c`) and (`test_db`.`t1`.`c` = 2))",
              "steps": [
                {
                  "transformation": "equality_propagation", -- 等值传递 转换后是a=2 and b=2 and c=2
                  "resulting_condition": "(multiple equal(2, `test_db`.`t1`.`a`, `test_db`.`t1`.`b`, `test_db`.`t1`.`c`))"
                },
                {
                  "transformation": "constant_propagation",-- 常量传递
                  "resulting_condition": "(multiple equal(2, `test_db`.`t1`.`a`, `test_db`.`t1`.`b`, `test_db`.`t1`.`c`))"
                },
                {
                  "transformation": "trivial_condition_removal", -- 移除无效条件
                  "resulting_condition": "multiple equal(2, `test_db`.`t1`.`a`, `test_db`.`t1`.`b`, `test_db`.`t1`.`c`)"
                }
              ]
            }
    

    b. 常量传递

    select * from test_db.t1 t1 where t1.a=4 and t1.b>t1.a;
    

    查询优化器优化后

    "condition_processing": {
              "condition": "WHERE",
              "original_condition": "((`test_db`.`t1`.`a` = 4) and (`test_db`.`t1`.`b` > `test_db`.`t1`.`a`))",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "((`test_db`.`t1`.`b` > 4) and multiple equal(4, `test_db`.`t1`.`a`))"
                },
                {
                  "transformation": "constant_propagation", -- 常量传递后,b>4 and a=4
                  "resulting_condition": "((`test_db`.`t1`.`b` > 4) and multiple equal(4, `test_db`.`t1`.`a`))"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "((`test_db`.`t1`.`b` > 4) and multiple equal(4, `test_db`.`t1`.`a`))"
                }
              ]
            }
    

    c. 移除无用查询条件

    select * from test_db.t1 t1 where t1.a=4 and t1.b>t1.a;
    

    移除无效条件后

    "condition_processing": {
              "condition": "WHERE",
              "original_condition": "((`test_db`.`t1`.`a` = 4) and (`test_db`.`t1`.`b` > `test_db`.`t1`.`a`) and (1 = 1))",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "((`test_db`.`t1`.`b` > 4) and (1 = 1) and multiple equal(4, `test_db`.`t1`.`a`))"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "((`test_db`.`t1`.`b` > 4) and (1 = 1) and multiple equal(4, `test_db`.`t1`.`a`))"
                },
                {
                  "transformation": "trivial_condition_removal",--移除无效条件 b>4 and a =4
                  "resulting_condition": "((`test_db`.`t1`.`b` > 4) and multiple equal(4, `test_db`.`t1`.`a`))"
                }
              ]
            }
    

    2、根据查询条件,找出所有可能使用的索引

    • emp_no > ‘10101’,这个搜索条件可以使用主键索引PRIMARY。
    • to_date = ‘1991-10-10’,这个搜索条件可以使用二级索引idx_titles_to_date

    综上所述,这条sql可能用到的索引有主键索引PRIMARY和二级索引idx_titles_to_date


    3、计算全表索引的成本

    基于成本

    一个查询可以有不同的执行方案,可以选择某个索引进行查询,也可以选择全表扫描,查询优化器会选择其中成本最低的方案去执行查询。

    I/O成本

    InnorDB存储引起都是将数据和索引存储在磁盘中的,当查询表中数据时,需要先把索引和表数据加载到内存中再进行操作,加载索引和表数据都是需要消耗时间,这里消耗的时间就是I/O成本。

    CPU成本

    读取以及检测记录是否满足查询条件、对结果集进行排期等这些操作消耗的时间,称之为CPU成本。


    InnorDB 引擎规定读取一个页数据页I/O默认成本为1.0,CUP读取以及检查一条数据是否满足查询条件默认成本为0.2


    基于成本的优化步骤
    在一条sql真正执行之前,MYSQL的查询优化器会找出该语句所有可能的执行方案,经过比较每个方案的成本后,找出最小成本的方案。这个最小成本的方案就是所谓的执行计划,之后才会调用存储u引擎提供的接口真正的执行查询。
    下面我们就以一个实力来分析一下这些步骤,单表查询语句如下:

    select * from employees.titles where emp_no > '10101' and emp_no < '20000' and to_date = '1991-10-10';
    

    这个地方使用mysql官网提供的示例数据库:https://dev.mysql.com/doc/employee/en/employees-installation.html
    github地址:https://github.com/datacharmer/test_db.git
    大家可以根据自行选择方式导入数据库。


    计算全表扫描的代价:
    对于InnorDB存储引擎而言,全表扫描就是把聚簇索引中的页加载到内存中,然后再逐条检查记录是否符合查询条件,将符合查询条件的记录放入结果集中。
    由于查询成本=I/O成本 + CPU成本,因此计算全表扫描的成本包含两部分:
    1、聚簇索引占用的页数
    2、表中的记录数


    MySQL为每个表维护了一系列的统计信息, SHOW TABLE STATUS 语句来查看表的统计信息。
    SHOW TABLE STATUS LIKE 'titles';
    

    !!!

    Rows
    表示表中的记录条数。对于使用MyISAM存储引擎的表来说,该值是准确的,对于使用InnoDB存储引擎的表来说,该值是一个估计值

    Data_length
    表示表占用的存储空间字节数。使用MyISAM存储引擎的表来说,该值就是数据文件的大小,对于使用InnoDB存储引擎的表来说,该值就相当于聚簇索引占用的存储空间大小,也就是说可以这样计算该值的大小:Data_length = 聚簇索引的页数 + 每个页的大小

    我们的titles使用默认16KB的页面大小,而上边查询结果显示Data_length的值是20512768,所以我们可以反向来推导出聚簇索引的页面数量:
    聚簇索引的页面数 = Data_length ÷ 16 ÷ 1024 = 20512768 ÷ 16 ÷ 1024 = 1252
    我们现在已经得到了聚簇索引占用的页面数量以及该表记录数的估计值,所以就可以计算全表扫描成本了。但是MySQL在真实计算成本时会进行一些微调。

    I/O成本:12521 = 1252。1252指的是聚簇索引占用的页面数,1.0指的是加载一个页面的成本常数。
    CPU成本:442070
    0.2=88414。442070指的是统计数据中表的记录数,对于InnoDB存储引擎来说是一个估计值,0.2指的是访问一条记录所需的成本常数
    总成本:1252+88414 = 89666。
    综上所述,对于titles的全表扫描所需的总成本就是89666。

    我们前边说过表中的记录其实都存储在聚簇索引对应B+树的叶子节点中,所以只要我们通过根节点获得了最
    左边的叶子节点,就可以沿着叶子节点组成的双向链表把所有记录都查看一遍。也就是说全表扫描这个过程
    其实有的B+树内节点是不需要访问的,但是MySQL在计算全表扫描成本时直接使用聚簇索引占用的页面数作
    为计算I/O成本的依据,是不区分内节点和叶子节点的


    4、计算各个索引的成本

    计算PRIMARY需要的成本
    计算PRIMARY需要多少成本的关键问题是:需要预估预估出根据对应的where条件在主键索引B+树中存在多少条符合条件的记录

    范围区间数
    当我们从索引中查询记录时,不管是=、in、>、<这些操作都需要从索引中确定一个范围,不论这个范围区间的索引到底占用了多少页面,查询优化器粗暴的认为读取索引的一个范围区间的I/O成本和读取一个页面是相同的。
    本例中使用PRIMARY的范围区间只有一个:(10101, 20000),所以相当于访问这个范围区间的索引付出的I/O成本就是:1 x 1.0 = 1.0

    预估范围内的记录数
    优化器需要计算索引的某个范围区间到底包含多少条记录,对于本例来说就是要计算PRIMARY在(10101, 20000)
    这个范围区间中包含多少条数据记录,计算过程是这样的:

    • 步骤1:先根据emp_no > 10101这个条件访问一下PRIMARY对应的B+树索引,找到满足emp_no> 10101这个条件的第一条记录,我们把这条记录称之为区间最左记录。
    • 步骤2:然后再根据emp_no < 20000这个条件继续从PRIMARY对应的B+树索引中找出第一条满足
      这个条件的记录,我们把这条记录称之为区间最右记录。
    • 步骤3:如果区间最左记录和区间最右记录相隔不太远(只要相隔不大于10个页面即可),那就可
      以精确统计出满足emp_no > ‘10101’ and emp_no < '20000’条件的记录条数。否则只沿着区间最
      左记录向右读10个页面,计算平均每个页面中包含多少记录,然后用这个平均值乘以区间最左记
      录和区间最右记录之间的页面数量就可以了。那么问题又来了,怎么估计区间最左记录和区间最右
      记录之间有多少个页面呢?计算它们父节点中对应的目录项记录之间隔着几条记录就可以了

    根据上面的步骤可以算出来PRIMARY索引的记录条数,所以读取记录的CPU成本为:26808*0.2=5361.6,其中26808是预估的需要读取的数据记录条数,0.2是读取一条记录成本常数

    PRIMARY的总成本:确定访问的IO成本+过滤数据的CPU成本=1+5361.6=5362.6

    计算idx_titles_to_date的成本
    因为通过二级索引查询需要回表,所以在计算二级索引需要成本时还要加上回表的成本,而回表的成本就相当于下面这个SQL执行:

    select * from employees.titles where 主键字段 in (主键值1,主键值2,主键值3,……);
    

    所以idx_titles_to_date的成本 = 辅助索引的查询成本 + 回表查询的成本

    5、根据成本,选择成本最小的索引执行

    选择成本最小的索引


    基于索引统计数据的成本计算
    有时候使用索引执行查询时会有许多单点区间,比如使用IN语句就很容易产生非常多的单点区间,比如下边这个查询:

    	select * from employees.titles where to_date in ('a','b','c','d', ..., 'e')
    

    很显然,这个查询可能使用到的索引就是idx_titles_to_date,由于这个索引并不是唯一二级索引,所以并不能确定一个单点区间对应的二级索引记录的条数有多少,需要我们去计算。计算方式我们上边已经介绍过了,就是先获取索引对应的B+树的区间最左记录和区间最右记录,然后再计算这两条记录之间有多少记录(记录条数少的时候可以做到精确计算,多的时候只能估算)。这种通过直接访问索引对应的B+树来计算某个范围区间对应的索引记录条数的方式称之为index dive。
    如果只有几个单点区间的话,使用index dive的方式去计算这些单点区间对应的记录数也不是什么问题,可是如果很多呢,比如有20000次,MySQL的查询优化器为了计算这些单点区间对应的索引记录条数,要进行20000次index dive操作,那么这种情况下是很耗性能的,所以MySQL提供了一个系统变量eq_range_index_dive_limit,我们看一下这个系统变量的默认值: SHOW VARIABLES LIKE ‘%dive%’; 为200。也就是说如果我们的IN语句中的参数个数小于200个的话,将使用index dive的方式计算各个单点区间对应的记录条数,如果大于或等于200个的话,可就不能使用index dive了,要使用所谓的索引统计数据来进行估算。像会为每个表维护一份统计数据一样,MySQL也会为表中的每一个索引维护一份统计数据,查看某个表中索引的统计数据可以使用 SHOW INDEX FROM 表名的语法。

    Cardinality属性表示索引列中不重复值的个数。比如对于一个一万行记录的表来说,某个索引列的Cardinality属性是10000,那意味着该列中没有重复的值,如果Cardinality属性是1的话,就意味着该列的值全部是重复的。不过需要注意的是,对于InnoDB存储引擎来说,使用SHOW INDEX语句展示出来的某个索引列的Cardinality属性是一个估计值,并不是精确的。可以根据这个属性来估算IN语句中的参数所对应的记录数:

    • 使用SHOW TABLE STATUS展示出的Rows值,也就是一个表中有多少条记录。
    • 使用SHOW INDEX语句展示出的Cardinality属性。
    • 根据上面两个值可以算出idx_key1索引对于的key1列平均单个值的重复次数:Rows/Cardinality
    • 所以总共需要回表的记录数就是:IN语句中的参数个数*Rows/Cardinality

    NULL值处理
    上面知道在统计列不重复值的时候,会影响到查询优化器
    对于NULL,有三种理解方式:

    1. NULL值代表一个未确定的值,每一个NULL值都是独一无二的,在统计列不重复值的时候应该都当作独立。
    2. NULL值在业务上就是代表没有,所有的NULL值代表的意义是一样的,所以所有的NULL值都一样,在统
      计列不重复值的时候应该只算一个。
    3. NULL完全没有意义,在统计列不重复值的时候应该忽略NULL

    innodb提供了一个系统变量:

    show global variables like '%innodb_stats_method%';
    

    这个变量有三个值:

    1. nulls_equal:认为所有NULL值都是相等的。这个值也是innodb_stats_method的默认值。如果某个索引
      列中NULL值特别多的话,这种统计方式会让优化器认为某个列中平均一个值重复次数特别多,所以倾向
      于不使用索引进行访问。
    2. nulls_unequal:认为所有NULL值都是不相等的。如果某个索引列中NULL值特别多的话,这种统计方式
      会让优化器认为某个列中平均一个值重复次数特别少,所以倾向于使用索引进行访问。
    3. nulls_ignored:直接把NULL值忽略掉。
      最好不在索引列中存放NULL值才是正解

    数据统计
    InnoDB提供了两种存储统计数据的方式:
    1、统计数据存储在磁盘上。
    2、统计数据存储在内存中,当服务器关闭时这些这些统计数据就都被清除掉了。

    MySQL给我们提供了系统变量innodb_stats_persistent来控制到底采用哪种方式去存储统计数据。在MySQL
    5.6.6之前,innodb_stats_persistent的值默认是OFF,也就是说InnoDB的统计数据默认是存储到内存的,之后的版本中innodb_stats_persistent的值默认是ON,也就是统计数据默认被存储到磁盘中。
    不过InnoDB默认是以表为单位来收集和存储统计数据的,也就是说我们可以把某些表的统计数据(以及该表的索引统计数据)存储在磁盘上,把另一些表的统计数据存储在内存中。我们可以在创建和修改表的时候通过指定STATS_PERSISTENT属性来指明该表的统计数据存储方式

    基于磁盘的永久性统计数据
    当我们选择把某个表以及该表索引的统计数据存放到磁盘上时,实际上是把这些统计数据存储到了两个表里:
    innodb_table_stats存储了关于表的统计数据,每一条记录对应着一个表的统计数据
    innodb_index_stats存储了关于索引的统计数据,每一条记录对应着一个索引的一个统计项的统计
    数据

    定期更新统计数据
    系统变量innodb_stats_auto_recalc决定着服务器是否自动重新计算统计数据,它的默认值是ON,
    也就是该功能默认是开启的。每个表都维护了一个变量,该变量记录着对该表进行增删改的记录条
    数,如果发生变动的记录数量超过了表大小的10%,并且自动重新计算统计数据的功能是打开
    的,那么服务器会重新进行一次统计数据的计算,并且更新innodb_table_stats和
    innodb_index_stats表。不过自动重新计算统计数据的过程是异步发生的,也就是即使表中变动的
    记录数超过了10%,自动重新计算统计数据也不会立即发生,可能会延迟几秒才会进行计算。
    如果innodb_stats_auto_recalc系统变量的值为OFF的话,我们也可以手动调用ANALYZE TABLE语
    句来重新计算统计数据。 ANALYZE TABLE single_table;

    控制执行计划
    INDEX Hints

    • USE INDEX:限制索引的使用范围,们在数据表里建立了很多索引,当MySQL对索引进行选择时,这些索引都在考虑的范围内。但有时我们希望MySQL只考虑几个索引,而不是全部的索引,这就需要用到USE INDEX对查询语句进行设置
    • IGNORE INDEX :限制不使用索引的范围
    • FORCE INDEX:我们希望MySQL必须要使用某一个索引(由于 MySQL在查询时只能使用一个索引,因此只能强迫MySQL使用一个索引)。这就需要使用FORCE INDEX来完成这个功能

    基本语法格式:

    SELECT * FROM table1 USE|IGNORE|FORCE INDEX (col1_index,col2_index) WHERE col1=1 AND col2=2 AND col3=3;
    










    参考文档:https://blog.csdn.net/tengdazhang770960436/article/details/94065557

    展开全文
  • mysql查询优化器

    2020-09-22 20:44:20
    查询优化器是mysql中非常重要且复杂的部件,mysql优化器优化策略分为静态优化和动态优化:静态优化可以直接对解析树进行分析并完成优化,不依赖于特别的数值,依次完成后就一直有效,如通过简单的代数变换将where...

    查询优化器是mysql中非常重要且复杂的部件,mysql优化器优化策略分为静态优化和动态优化:静态优化可以直接对解析树进行分析并完成优化,不依赖于特别的数值,依次完成后就一直有效,如通过简单的代数变换将where条件转换成另一种等价形式; 动态优化和查询的上下文有关,也可能和多种其他因素有关,如where条件的取值、索引中条目对应数据行数等,它需要每次在查询的时候进行评估,可以理解为 “运行时优化”。

    mysql能处理到的优化类型:

    1. 重新定义关联表顺序:数据表的关联不会总是按照在查询中指定顺序进行,关联的顺序是由关联查询优化器决定。关于关联查询、关联查询优化器,后面会详细介绍。
    2. 外连接转化成内连接:mysql会把等价于一个内连接的外连接重写,让其可以调整关联顺序。
    3. 使用等价变化规则:mysql可以使用一些等价变换来简化并规范表达式,它可以合并和减少一些比较,移除一些恒成立和恒不成立的判断。
    4. 优化count()、min()、max():索引和列是否可为空通常可以帮助mysql优化这类表达式。如,查询某一列的最小值,只要查询B-Tree索引的最左端的第一行记录;查询最大值,查询B-Tree索引最右端数据;查询没有where条件的count(*),则可以通过使用存储引擎提供的一些优化,如MyISAM维护一个变量来存储数据表的行数。
    5. 预估并转化为成熟表达式:mysql检测到一个表达式可以转化为常数的时候,会一直把该表达式作为常数进行优化。
    6. 覆盖索引扫描:如果索引中的列包含查询中所有需要使用的列,mysql可以使用索引返回需要的数据,而无需查询对应行数据。
    7. 子查询优化:mysql在一些情况下可以将子查询转换成一种更高效的形式,从而减少多个查询多次对数据进行访问。
    8. 提前终止查询:发现满足查询条件的需求后,mysql会立即终止查询。如在使用limit子句,当查询到我们需要的数据行后,即停止访问其他行数据。
    9. 等值传播:如果两个列的值通过等式关联,那么mysql能把其中一个列的where条件传递到另一列上,如:select f.film_id from film f  inner join film_actor fa USING (film_id) where f.film_id > 500;  这里使用film_id 进行等值关联,mysql知道where条件中的film_id 不仅适用于film表,还适用于film_actor 表,两张表都会使用film_id > 500 进行筛选。
    10. 列表in() 的比较: in() 我们一般理解为多个or条件,而且两者确实是等价的。但是在mysql中这点并不成立,mysql将in() 列表中的数据先进行排序,然后通过二分查找的方式确定列表中的值是否满足条件,这是一个O(log n) 时间复杂度的操作,等价换成or条件的复杂度为 O(n) , 对于in()列表中有大量取值的时候,mysql的处理速度相对更快。

    上面列举的并非mysql优化器的全部,mysql还会做大量的其他优化。了解mysql优化器,可以帮助我们在编写高性能的SQL时少走一些弯路。但是优化器给出的结果有时候也并不是最优的结果,这需要我们在更加了解真实的数据后,对其进行逻辑调整。

    数据和索引的统计信息:

    了解mysql执行基础 一文中,给出了mysql执行的流程图,在服务器层有查询的优化器,没有保存数据和索引的统计信息。统计信息由存储引擎实现,不同的存储引擎可能会存储不同的统计信息。mysql查询优化器在生成查询的执行计划时,需要向存储引擎获取响应的统计信息,包括:每个表或者索引有多少页面、每个表的每个索引基数是多少、数据行和索引长度、索引的分布信息等。

    Mysql执行关联查询的过程:

    Mysql的任何一次查询都是一次关联,并不是一个查询用到两个表匹配才叫关联。mysql对任何关联查询都执行嵌套循环关联操作,即先从一张表中循环取出单条数据,然后再嵌套寻循环找到下一个表中寻找匹配的行,依次执行直到直到所有表中匹配的行。用代码表示如下:

    //遍历表1的数据行
    while(table_1_row.next()!=-1)
      //遍历子表表2的数据行
      while(table_2_row.next()!=-1)
         if(符合连接条件)
                ......
    

     mysql基本上将所有的关联查询类型(单表查询、子查询、连接查询)都转换为这种嵌套形式(其实全表扫描是mysql最简单最暴力的一种方式,真实情况下还会选择更优的方式,例如索引扫描,但原理基本相通)。例如使用from+子查询的方式查询时,会先将内部查询结果保存到临时表中(可能是内存,也可能是磁盘中),然后将这个临时表作为普通表对待,然后使用上面方法进行查询。

    我们看一下,下面这个查询语句的执行过程:

    select tabl1.col1, tabl2.col2
    from tabl1 inner join tabl2 using (col3)
    where tabl1.col1 in (5,6);

    mysql对它实行嵌套查询的伪代码为:

       outer_iter = iterator_over tbl1 where col1 in(3,4)
      outer_row = outer_iter.next
    
      while outer_row
    
        inner_iter = iterator over tbl2 where col3=outer_row.col3
        inner_row = inner_iter.next
    
          while inner_row
    
            output[outer_row.col1,inner_row.col2]
            inner_row = inner_iter.next
    
          end
        out_row = outer_iter.next
      end

    但是,并非关联查询就要用到临时表。举个例子,比如有如下一个查询语句:

     select * from teacher inner join student using(teacher_id);

    在这个查询中,teacher_id是两个表的索引,mysql会通过索引找到student对应的数据行,而不是直接取出某一个表作为临时表。这样就大大提高了效率,所以合理的索引对于数据库是十分重要的。

    关联查询优化器:

    关联查询优化器是mysql优化器的重要组成部分,它决定了多个表关联时的顺序。上面我们说过,mysql执行关联查询是使用嵌套循环的方式执行的,嵌套循环的层级关系一定程度上决定了嵌套循环的执行次数。

    关联插叙优化器会尝试在所有的关联顺序中选择一个成本最小的来生成执行计划树,但是入股有n个表的关联,它需要检查n的阶乘种关联顺序。而我们没增加一张表,那么可能出现的查询结果就会增加n+1倍,它的增长速度非常快,msyql要在众多中可能中计算出最优的策略成本就会很高。这时候mysql就会使用“贪婪”搜索的方式查找最优关联顺序,不会遍历全部的可能性。

    所以在编写关联查询sql时,特别是关联表比较多时,表的关联顺序不能随意安排,这时关联优化器可以根据这些规则大大减少搜索空间,如左连接、相关子查询。因为后面的表的查询需要依赖于前面的表的查询结果,这种依赖关系通常可以帮助优化器减少需要扫描的执行计划数量。

    排序优化:

    排序操作是一个成本很高的操作,从性能角度考虑应该尽量避免排序或尽可能不对大量数据进行排序。特别是不能使用索引进行排序是,mysql需要自己进行排序,如果数据量小则在内存中进行排序,如果数据量大需要使用磁盘,mysql将这个过程统称为文件排序(filesort) 。

    如果排序的数据量小于“排序缓冲区”,mysql使用内存进行“快速排序”;如果内存不够,mysql会先将数据分块,然后对每个独立的块使用“快速排序”进行排序,并将各个块的排序结果存放在磁盘上,然后将排序好的块进行合并。mysql排序时分配的临时空间要比磁盘上原表的数据大很多,它要为定长空间准备足够长的字符串,如varchar列要分配完整长度;UTF-8字符集,要为每个字符留三个字节。

    在关联查询排序时,mysql会分两种情况来处理文件排序:如果Order by 子句中的所有列都来自关联表的第一个表,那么mysql在关联处理第一个表的时候就进行文件排序,在EXPLAIN中会看到Extra字段会有“Using filesort”;其它情况下,mysql会先将关联的结果存放到临时表中,然后再所有的关联都结束后,再进行文件排序。

    展开全文
  • 数据库查询优化器

    2020-01-09 10:39:20
    查询优化器两种分类: 1、RBO:Rule-Based Optimizer 基于规则的优化器 这是一种比较老的技术,简单说基于规则的优化就是当数据库执行一条query语句的时候必须遵循预先定义好的一系列规则来确定执行过程,它不...

    查询优化器两种分类:

    1、RBO:Rule-Based Optimizer 基于规则的优化器

         这是一种比较老的技术,简单说基于规则的优化就是当数据库执行一条query语句的时候必须遵循预先定义好的一系列规则来确定执行过程,它不关心访问表的数据分布情况,仅仅凭借规则经验来确定,所以说是一种比较粗放的优化策略。

         RBO所用的判断规则是一组内置的规则,这些规则是硬编码在数据库的编码中的,RBO会根据这些规则去从SQL诸多的路径中来选择一条作为执行计划(比如在RBO里面,有这么一条规则:有索引使用索引。那么所有带有索引的表在任何情况下都会走索引)所以,RBO现在被很多数据库抛弃(oracle默认是CBO,但是仍然保留RBO代码,MySQL只有CBO)

         RBO最大问题在于硬编码在数据库里面的一系列固定规则,来决定执行计划。并没有考虑目标SQL中所涉及的对象的实际数量,实际数据的分布情况,这样一旦规则不适用于该SQL,那么很可能选出来的执行计划就不是最优执行计划了。


    2、CBO:Cost-Based Optimizer 基于成本的优化器

         CBO优化器根据SQL语句生成一组可能被使用的执行计划,估算出每个执行计划的成本,并调用计划生成器(Plan Generator)生成执行计划,比较执行计划的成本,最终选择选择一个成本最小的执行计划。查询优化器由查询转换器(Query Transform)、代价估算器(Estimator)和计划生成器(Plan Generator)组成。

         CBO在会从目标诸多的执行路径中选择一个成本最小的执行路径来作为执行计划。这里的成本他实际代表了MySQL根据相关统计信息计算出来目标SQL对应的步骤的IO,CPU等消耗。也就是意味着数据库里的成本实际上就是对于执行目标SQL所需要IO,CPU等资源的一个估计值。而成本值是根据索引,表,行的统计信息计算出来的。(计算过程比较复杂)

         CBO优于RBO是因为RBO是一种呆板、过时的优化器,它只认规则,对数据不敏感。毕竟规则是死的,数据是变化的,这样生成的执行计划往往是不可靠的,不是最优的。

    总结:

         基于规则的优化器更像是一个经验丰富熟知各条路段的老司机,大部分情况可以根据自己的经验来判断走哪条路可以更快的到达目的地,而基于代价的优化更像手机里面的地图,它可以选择出许多不同的路径根据实时的路况信息综合考虑路程长度,交通状况来挑出最优的路径。

     

    展开全文
  • Innodb查询优化器

    2019-04-26 12:29:16
    查询优化器如何找到最优执行计划 优化器的主要作用就是为待执行的sql语句找到最优的执行计划,其基本优化方式如下: 等价变化规则 例如: 5=5 and a>5 改写成 a>5 a<b and a=5 改写成 b>5 and a=5 基于...
  • 5 . 2 查 询 优 化

    2019-06-27 23:19:34
    2 查 询 优 化 器从本质上来说,优化器执行的就是一个把逻辑查询操作映射为物理操作,并把产生的 执行计划传递给执行引擎运行并返回结果给用户的过程。优化器会通过一定数量的算法计 算,选择最低开销的操作,尽...
  • 查询优化器

    2019-09-02 12:18:23
    查询优化器 一 查询过程 二 查询优化器 2.1 产生执行计划 <1> 评估每个计划的开销 <2>...
  • 在本系列的数据库四:浅谈数据库查询过程(Query Processing)中大致地说明了一下数据库的查询过程,但是没提到查询优化器的具体策略与实现。 对于查询而言,我们期望优化器的作用是找到最小代价的正确执行方案。...
  • 1,查询优化器简介 查询优化器(简称优化器)是内置的数据库软件,在访问请求数据时,它决定了SQL语句的最有效访问方法。 1.1,查询优化器的目的 优化器尝试为SQL语句生成最优的执行计划。优化器在所有候选计划中...
  • 不过MySQL查询优化器只对少部分查询不适用,而且我们往往可以通过改写查询让MySQL高效的完成工作。 1 关联子查询 MySQL的子查询实现的非常糟糕。最糟糕的一类查询时where条件中包含in()的子查询语句。因为MySQL对...
  • MySQL查询优化之查询优化器

    千次阅读 2009-08-20 17:07:00
    这一部分将介绍查询优化器是如何工作的。如果你想知道MySQL采用的优化手段,可以查看MySQL参考手册。phpma.com  当然,MySQL查询优化器也利用了索引,但是它也使用了其它一些信息。例如,如果你提交如下所示的查询...
  • 查询优化器的基本原理小明考上了北清大学的计算机研究生,今年学校开了数据库原理的课程,小明对查询优化的内容不是很理解,虽然已经使出了洪荒之力,仍觉得部分原理有些晦涩难懂,于是打算问一下自己的哥哥大明。...
  • [玩转MySQL之六]MySQL查询优化器

    千次阅读 2019-04-26 12:35:31
    注:由于查询优化器涉及面很广也比较复杂,作者也没有完全领会,本文主要来自书籍<<数据库查询优化的艺术: 原理解析和SQL性能优化>>,如果涉及到版权,请告知作者,删除本文。 一、查询语句的执行过程...
  • 本书是一本数据库内核相关书籍,从数据库的查询优化器入手,对数据库的查询优化引擎进行了分析和对比,对查询优化的技术做了全面的总结和剖析。从不同角度看,可能有着不同的感受;不同角色的人,可能对本书有着不同...
  • MySQL查询优化器工作原理解析

    万次阅读 2016-05-29 10:29:37
    手册上MYSQL查询优化器概述;个人对MySQL优化器的理解;分析优化器优化过程中的信息;调节MySQL优化器的优化等
  • Mysql查询优化器浅析(上)

    千次阅读 2007-12-17 08:52:00
    Mysql查询优化器浅析(上)译者:杨万富 1 定义 Mysql查询优化器的工作是为查询语句选择合适的执行路径。查询优化器的代码一般是经常变动的,这和存储引擎不太一样。因此,需要理解最新版本的查询优化器是如何组织...
  • explain可以帮助我们分析select语句,找出select语句的瓶颈,从而可以针对性地去做优化,让MySQL查询优化器更好地工作。 MySQL查询优化器有几个目标,其中最主要的目标是尽可能地使用索引,并且使用最严格的索引来...
  • 查询优化器是关系数据库系统的核心模块,是数据库内核开发的重点和难点,也是衡量整个数据库系统成熟度的“试金石”。 查询优化理论诞生距今已有四十来年,学术界和工业界其实已经形成了一套比较完善的查询优化框架...
  • SQL Server的查询优化器详解

    千次阅读 2015-11-18 17:47:12
    SQL Server的查询优化器在select查询执行的时候产生一个高效的查询执行计划。如果优化器不能选择最优的计划,那么就需要检查查询计划、统计信息、支持的索引等,而通过使用提示可以改变优化器选择查询计划的工程,使...
  • 在“查询优化器常用的方式”一文中列出了一些优化器常用的优化手段。查询优化器在提供这些特性的同时,也存在一定的局限性,这些局限性往往会随着MYSQL版本的升级而得到改善,所以本文会列出一些常见的局限性,且不...
1 2 3 4 5 ... 20
收藏数 587,604
精华内容 235,041
关键字:

查询优化器