mysql查询优化器_查看 mysql 查询优化器 优化后的语句 - CSDN
精华内容
参与话题
  • 描述 MySQL 查询优化器的工作原理。 MySQL 查询优化器主要为执行的查询决断最有效的路线
  • 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会先将关联的结果存放到临时表中,然后再所有的关联都结束后,再进行文件排序。

    展开全文
  • 查询 全表查询:对聚簇索引进行遍历 索引查询:使用索引 单表查询 const

    查询

    全表查询:对聚簇索引进行遍历
    索引查询:使用索引

    单表查询

    • const 等值查询(定位一条记录) 聚簇索引 唯一二级索引
    • ref 等值比较 (可以多条记录) 二级索引
    • ref_null 等值 或null 比较 (多条记录) 二级索引
    • range 范围查询
    • index 遍历二级索引记录
    • all 全表查询
    • 简化,替换true
    • 多个索引,结果大,回表代价大:合并
    • 交集: intersection (and 等值)分别索引,结果相交,再回表 (减少回表的随机io)
    • 并集:union (or 等值)分别索引,结果做并集,在回表
    • 排序并集:sort_union (or 非等值) 分别索引 排序,在并集,再回表
    展开全文
  • 查询优化器是指生成查询计划的子系统,该子系统通常完全处于服务器端,根据要参与连接(join)的表、数据读取方式(所殷读取或表扫描)和索引选择等因素制定查询计划。以基于开销的优化器为例,数据库查询优化器的...

    查询优化器是指生成查询计划的子系统,该子系统通常完全处于服务器端,根据要参与连接(join)的表、数据读取方式(所殷读取或表扫描)和索引选择等因素制定查询计划。以基于开销的优化器为例,数据库查询优化器的任务是,通过产生可供选择的多个执行计划,并从中选出最低估算开销的执行计划,来优化一条SQL语句。这在数据库系统和SQL语句性能表现上扮演了至关重要的角色。

    MySQL解析器

    MySQL解析器主要有两部分组成:

    • 词法分析(Lexical Analysis 或 Scanner)
    • 语法分析(Syntax Analysis 或 Parser)

    词法分析阶段是MySQL解析SQL语句过程的第一个阶段。这个阶段的任务是从左到右一个字符、一个字符地读入源输入,即对构成源程序的字符流进行扫描,然后根据构词规则识别单词(也称单词符号或符号)。词法分析程序实现这个任务。词法分析程序可以使用 lex 或 GNU 开源的 Flex 等工具自动生成。
    语法分析是MySQL解析过程的第二个阶段,该过程是一个逻辑阶段。语法分析的任务是在词法分析的基础上将单词序列组合成各类语法短语,如“程序”,“语句”,“表达式”等。语法分析程序判断源程序在结构上是否正确。源程序的结构由上下文五官文法描述。语法分析常用Bison工具自动处理。

    词法分析程序将整个查询语句分解成各类标志,语法分析根据定义的系统语言将“各类标志”转为对MySQL有意义的组合(Item类)。最后系统生成一个语法树,语法树便是优化器依赖的数据结构。

    下面是一个查询语句的例子:

    SELECT name FROM faculty f, classes c where f.id = c.fac_id AND f.department_id = 'CS' AND c.semester = 'F2001';

    这个查询语句的作用是找到计算机专业在2001年秋季的所有课程名
    语法分析程序检查输入的字节流,并将整个字节流转为各类标志。对于上面的这个查询,标志如下:

    • SELECT
    • name
    • FROM
    • faculty
    • f
    • classes
    • c
    • WHERE
    • f
    • .
    • id
    • =
    • c
    • .
    • fac_id
    • AND
    • f
    • .
    • department_id
    • =
    • CS
    • AND
    • c
    • .
    • semester
    • =
    • F2001

    所有这些标志在MySQL内部都是一个对象。它们都属于Item类的子类,例如关键字、字符串、数字和操作符等。语法分析器(Parser)根据设定的规则,组合这些标志。在本例中,select语句的语法规则可在sql/sql_yacc.cc中找到。最终sql/sql_yacc.cc得到的语法树如下图:
    这里写图片描述

    词法分析程序

    sql/lex.h 中定义了 MySQL 关键字:
    static SYMBOL symbols[] = {
    {“&&”, SYM(AND_AND_SYM)},
    {“<”, SYM(LT)},
    {“<=”, SYM(LE)},
    {“<>”, SYM(NE)},
    {“!=”, SYM(NE)},
    {“=”, SYM(EQ)},
    {“>”, SYM(GT_SYM)},
    {“>=”, SYM(GE)},
    {“<<”, SYM(SHIFT_LEFT)},
    {“>>”, SYM(SHIFT_RIGHT)},
    {“ACTION”, SYM(ACTION)},
    {“ADD”, SYM(ADD)},

    }

    sql/lex.h 中定义的MySQL函数关键字:
    static SYMBOL sql_function[] = {
    “ADDDATE”, SYM(ADDDATE_SYM)},
    “COUNT”, SYM(COUNT_SYM),
    “EXTRACT”, SYM(EXTRACT_SYM),
    “MAX”, SYM(MAX_SYM),
    “MIN”, SYM(MIN_SYM),
    “NOW”, SYM(NOW_SYM),
    }

    语法分析器

    MySQL的语法规则使用了GNU Bison 工具。在sql/sql_yaacc.yy中我们可以找到 MySQL 的语法定义,MySQLL利用 sql_yacc.yy 生成 sql_yacc.cc文件。语法分析器从 yyparse 开始执行。

    例子:几个常见的类

    如下,是SELECT 和 UNION 操作的解析:

    UNION 表达式 := (SELECT 表达式|UNION 表达式) UNION (SELECT 表达式|UNIOIN表达式)
    SELECT 表达式 := SELECT (SELECT 表达式*)
    1. MySQL 用 SELECT 结点来代表一个 SELECT 查询,而用一个 UNIT 结点来代表一个 UNION 操作符或者 SELECT 下的子查询;
    2. LEX::unit 为根节点,LEX::select_lex 指向 SQL 中的第一个 SELECT 语句。

    下面举例说明 MySQL 解析 select 和 union 后生成的解析树:
    这里写图片描述

    以下说明 JOIN 类和 TABLE_LIST 类
    在 MySQL 的解析树中,MySQL 将所有查询语句都理解成 JOIN操作。这里的 JOIN 比 SQL 语句中的 JOIN 概念要广泛得多。在 SQL 语句中,我们对一个表的读取并不采用 JOIN 操作,而在解析树中,一个表也是 JOIN 操作。
    TABLE_LIST 类是 TABLE 容器类,JOIN 操作使用的 TABLE 对象群被放置于 TABLE_LIST 中。TABLE_LIST 类的成员和方法如下:
    JOIN 操作符具有下面的形式:

    • JOIN 表达式 := (JOIN 表达式|JOIN嵌套) JOIN (JOIN 表达式|JOIN嵌套)
    • JOIN 嵌套 := ‘(‘(表引用|JOIN表达式)(.表引用|JOIN表达式)*’)’ | 表引用
      因此所有的JOIN 表达式都可以用JOIN 表达式和 JOIN 嵌套这两个基本结点来代表,如下图所示:
      这里写图片描述
      X,Y结点可以是 JOIN 表达式、JOIN 嵌套或表引用之一,而 ON (…)表达式一定在 Y 结点上。同时当 Y 为表引用时,也称之为内表(inner table)。nest_last_join 和 nested_join 都称之为“嵌套结点”。

    查询优化器

    采用优化器的主要原因:

    • 优化器可以从数据字典中获取许多统计信息,例如表中的行数、表中的每个列的分布情况等。优化器可以根据这些信息选择有效的执行计划,而用户程序则难以获得这些信息;
    • 优化器可以考虑百种不同的执行计划,而程序员一般只能考虑有限的几种可能;
    • 优化器中包含了许多复杂的优化技术,这些优化技术往往比最好的程序员掌握的还要多。系统的自动优化相当于使得所有人都拥有这些优化技术。
    展开全文
  • [玩转MySQL之六]MySQL查询优化器

    千次阅读 2019-04-26 12:35:31
    注:由于查询优化器涉及面很广也比较复杂,作者也没有完全领会,本文主要来自书籍<<数据库查询优化的艺术: 原理解析和SQL性能优化>>,如果涉及到版权,请告知作者,删除本文。 一、查询语句的执行过程...

    注:由于查询优化器涉及面很广也比较复杂,作者也没有完全领会,本文主要来自书籍<<数据库查询优化的艺术: 原理解析和SQL性能优化>>,如果涉及到版权,请告知作者,删除本文。

    一、查询语句的执行过程简介

    MySQL查询语句在数据库中的执行分为5个阶段,具体如下:

    1.1 SQL输入

    数据库接收用户输入的SQL语句,准备执行。

    1.2 语法分析

    对输入的SQL语句进行词法分析、语法分析,得到语法分析树。在这个阶段,输入的是一条SQL语句,输出的是一棵多叉树。

    1.3 语义检查

    根据语法树和系统的元信息进行语义检查,本阶段是对语法分析树进行逻辑判断,树的结构不发生变化。对语法分析树上的各个节点进行语义分析,判断对象是否存在、是否重名等,对不合语义的地方报告错误。

    1.4 SQL优化

    SQL优化通常包括两项工作:一是逻辑优化、二是物理优化。这两项工作都要对语法分析树的形态进行修改,把语法分析树变为查询树。其中,逻辑查询优化将生成逻辑查询执行计划。在生成逻辑查询执行计划过程中,根据关系代数的原理,把语法分析树变为关系代数语法树的样式,原先SQL语义中的一些谓词变化为逻辑代数的操作符等样式,这些样式是一个临时的中间状态,经过进一步的逻辑查询优化,如执行常量传递、选择下推等(如一些节点下移,一些节点上移),从而生成逻辑查询执行计划。
    在生成逻辑查询计划后,查询优化器会进一步对查询树进行物理查询优化。物理优化会对逻辑查询进行改造,改造的内容主要是对连接的顺序进行调整。SQL语句确定的连接顺序经过多表连接算法的处理,可能导致表之间的连接顺序发生变化,所以树的形态有可能调整。
    物理查询优化除了进行表的连接顺序调整外,还会使用代价估算模型对单个表的扫描方式、两表连接的连接算法进行评估,选择每一项操作中代价最小的操作为下一步优化的基础。
    物理查询优化的最终结果是生成最终物理查询执行计划。

    1.5 SQL执行

    在SQL执行阶段,依据物理查询计划执行查询,逐步调用相关算法进行执行。算法包括一趟算法、嵌套循环连接、基于排序的两趟算法、基于散列的两趟算法、基于索引的算法、使用超过两趟的算法等。

    二、 逻辑查询优化

    2.1 逻辑查询优化思路

    查询优化器在逻辑优化阶段主要解决的问题是: 如何找出SQL语句等价的变换形式,使得SQL执行更高效。
    一条SQL查询语句结构复杂,包含多种类型的字句,优化操作依赖于表的一些属性(如索引和约束等)。可用于优化的思路包括:

    • 字句局部优化: 每种类型字句都可能存在优化方式,如等价谓词重写、where和having条件化简中的大部分情况,都属于这种字句范围内的优化。
    • 字句间关联优化: 字句与字句之间关联的语义存在优化的可能,如外连接消除、连接消除、子查询优化、视图重写等都属于字句间的关联优化,因为他们的优化都需要借助其他字句、表定义或列属性等信息进行。
    • 局部与整体的优化: 需要协同考虑局部表达式和整体的关系,如OR重写并集规则需要考虑UNION操作(UNION师变换后的整体的形式)的花费和OR操作(OR是局部表达式)的花费。
    • 形式变化优化: 多个字句存在嵌套,可以通过形式的变化完成优化,如嵌套连接消除。
    • 语义优化:根据完整性约束、SQL表达的含义等信息对语句进行语义优化。

    2.2 查询重写规则

    传统的联机事务处理(OLTP)使用基于选择(SELECT)、投影(PROJECT)、连接(JOIN)3种基本操作相结合的查询,这种查询称为SPJ查询。
    数据库在查询优化的过程中,会对这3种基本操作进行优化。优化的方式如下:

    • 选择操作: 对应的是限制条件(格式类似field consant,field表示列对象,op是操作符,如=,>等),优化方式是选择操作下推,目的是尽量减少连接操作前的远组数,使得中间临时关系尽量少(元组数少,连接得到的远组数就少),这样可减少IO和CPU的消耗,节约内存空间。
    • 投影操作: 对应的SELECT查询的目的列对象,优化方式是投影操作下推,目的是尽量减少连接操作前的列数,使得中间临时关系尽量小(特别注意差别:选择操作是使元组的个数"尽量少",投影操作是使一条元组"尽量小"),这样虽然不能减少IO(多数数据库存储方式是行存储,元组是读取的最基本单位,所以要想操作列则必须读取一行数据),但可以减少连接后中间关系的元组大小,节约内存空间)。
    • 连接关系: 对应的是连接条件(格式类似field_1, field_2,field_1和field_2表示不同表上的列对象,op是操作符,如=,>等),表示两个表连接的条件。这里涉及以下两个子问题:

      • 多表连接中每个表被连接的顺序决定着效率。如果一个查询语句只有一个表,则这样的语句很简单;但如果有多个表,则会涉及表之间以什么样的顺序连接效率最高效(如A、B、C三表连接,如果ABC、ACB、BCA等连接后的结果集一样,则计算哪种连接次序的效率最高,是需要考虑的问题)。
      • 多表连接每个表被连接的顺序由用户语义决定。查询语句多表连接有着不同的语义(如笛卡尔积、内连接 、还是外连接中的左外连接等),这决定着表之间的额前后连接次序是不能随意更换的,否则结果集中数据是不同的。因此,表的前后连接次序是不能随意交换的。
    • 根据SQL语句的形式特点,可以针对SPJ的查询优化,如基于选择、投影、连接3种基本操作相结合的查询。

    2.3 启发式规则再逻辑优化阶段的应用

    逻辑优化阶段使用的启发式规则通常包括如下两类:

    2.3.1 一定能带来优化效果的,主要包括:

    • 优先做选择和投影(选择条件在查询树上下推)
    • 子查询的消除
    • 嵌套连接的消除
    • 外连接的消除
    • 连接的消除
    • 使用等价谓词重写,对条件化简
    • 语义优化
    • 剪掉冗余操作(一些剪枝优化技术)、最小化查询块。

    2.3.2 变换未必会带来性能的提高,需根据代价选择,主要包括:

    • 分组的合并
    • 借用索引优化分组、排序、DISTINCT等操作
    • 对视图的查询变为基于表的查询
    • 连接条件的下推
    • 分组的下推
    • 连接提取公共表达式
    • 谓词的上拉
    • 用连接取代集合操作
    • 用UNIONALL取代OR操作

    三、物理优化

    查询优化器在物理优化阶段,主要解决的问题如下:

    • 从可选的单表扫描方式中,挑选什么样的单表扫描方式是最优的?
    • 对于两个表连接时,如何选择是最优的?
    • 对多个表连接,连接顺序有多种组合,是否要对每种组合都探索?如果不全部探索,怎么找到最优的一种组合?
      在查询优化器实现的早期,使用的是逻辑优化技术,即使用关系代数规则和启发式规则对查询进行优化后,认为生成的执行计划就是最优的。

    在引入了基于代价的查询优化方式后,对查询执行计划做了定量的分析,对每一个可能的执行方式进行评估,挑出代价最小的作为最优的计划。
    目前数据库的查询优化器通常融合这两种方式。

    3.1 查询代价估算

    查询代价估算的重点是代价估算模型,这是物理查询优化的依据。除了代价模型外,选择率对代价求解也起着重要作用。

    3.2 单表扫描算法

    单表扫描需要从表上获取元组,直接关联到物理IO的读取,所以不同的单表扫描方式,有不同的代价。

    3.3 索引

    索引是 建立在表上的,本质上是通过索引直接定位表的物理元组,加快数据获取的方式,所以索引优化的手段应该归属到物理查询优化阶段。

    3.4 两表连接算法

    关系代数的一项重要操作是连接运算,多个表连接是建立在两表之间连接的基础上的。研究两表连接的方式,对连接效率的提高有着直接的影响。

    3.5 多表连接算法

    多表连接算法实现的是在查询路径生成的过程中,根据代价估算,从各种可能的候选路径中找出最优的路径(最优路径是代价最小的路径)。
    多表连接算法需要解决两个问题:

    • 多表连接的顺序: 表的不同连接顺序,会产生许多不同的连接路径;不同的连接路径有不同的效率。
    • 多表连接的搜索空间:因为多表连接的顺序不同,产生的连接组合会有多种,如果这个组合的数据巨大,连接次数会达到一个很高的数量级,最大可能的连接次数是N!(N的阶乘)。比如N=5,连接次数是120;N=10,连接次数是362880。所有的连接可能构成一个巨大的"搜索空间"。如何将搜索空间限制在一个可接受的时间范围内,并高效地生成查询执行计划将成为一个难点。

    四、查询优化器与其他模块的关系

    在数据库内部,根据功能不同,可以划分出多个模块,不同模块之间有的关系紧密,有的关系松散。查询优化器是其中的一个功能模块,是实现查询优化技术的模块。下面介绍数据库中与查询优化器相关的模块:

    4.1 查询优化器与语法分析器

    语法分析器是查询优化器的输入。理解查询优化器,从语法分析器开始,将是个好的开端。因为不同对象有着不同的数据结构,数据结构成员是对象属性的载体,而语法分析器把一个SQL分解为众多数据结构体并给数据结构赋值,这样才能被查询优化器逐项获取并用与计算,比如逻辑查询优化有一条"常量传递"规则,如果没有语法分析器分解条件,也不可能推知列值是常量,也不可能有此优化。

    4.2 优化器与执行器

    查询优化器是执行器的前端输入部分。查询优化器计划一条SQL的具体执行方式和步骤 ,执行器具体去完成计划中的每一步。
    在实践中,一条SQL最耗时的阶段多发生在执行阶段。如果查询计划做得不好,则执行起来非常耗时。

    4.3 优化器与缓冲区

    缓冲区有多种多样,比如与数据相关的缓冲区(如从存储设备加载数据到内存)、与实现过程相关的辅助缓冲区(如排序用到的临时表或内存块),与功能模块相关的缓冲区(如日志缓冲区)等。
    优化器主要是对SQL输入进行逻辑方式的变换,没有涉及数据部分,只涉及对数据量的估计。当估算排序空间的时候,会涉及排序缓冲区;当估算数据IO的时候,需要考虑数据是否在数据缓存中。所以,查询优化器与数据缓冲区有一定的关系。

    4.4 优化器与统计

    MySQL数据库的查询优化器使用了基于代价的查询执行计划估算,所以依赖于被查对象的各种数据,而数据是动态变化的,如表的元组数。如果实时获取这些数据,系统计算的开销会比较大。为了避免这样的问题,定期或者根据需要统计这些数据,则比较切合实际。
    优化器在物理优化阶段,需要对单表读取开销,两表连接开销,多表连接顺序开销等进行比较,比较基于的就是一些基础数据的值,这些数据通常不会被实时更新,所以优化器有时做出的计划未必是最合适的。

    4.5 优化器与索引

    优化器做物理查询优化需要利用索引提高单表扫描效率,进而减少了多表连接时的元组数,所以确定哪些索引可用、怎么有效利用索引等都在查询优化器中得到体现。

    五、 MySQL查询优化器概述

    MySQL 查询优化器的主要功能是完成SELECT语句的执行,在保证SELECT语句正确执行之外,还有一个重要的功能,就是使用关系代数、启发式规则、代价估值模型等不同种类的技术,提高SELECT语句的执行效率。

    MySQL 查询 优化 器 实现 第 2 章介绍 的 大多数查询优化技术,这些 技术, 用于 对 SPJ 和 非 SPJ 类型 的 查询 语句 进行 优化。

    下面将从整体上介绍MySQL查询优化器, 分别对MySQL 查询优化器的执行过程、架构、层次、设计 思想、主要概念、代码结构上宏观探讨MySQL查询优化器的实现。

    5.1 MySQL查询执行过程

    MySQL查询执行过程分为4个阶段,如下所示:

    • 语法分析阶段: 将SQL查询语句经词法和语法分析后变换成为一棵查询树st_select_lex传给优化器,并对SQL表达的语义进行检查。
    • 生成逻辑查询执行计划阶段: 优化器在查询树中遍历每个关系,确定关系是否是常量表、为每个关系查找可用的索引、运用关系代数原理和启发式规则进行逻辑上的查询优化(如消除子查询、消除外连接等)。
    • 生成物理查询执行计划阶段: 优化器对各个连接的表进行排序,然后求解多表连接最优路径,对于每个关系尽量利用索引计算其代价,找出代价最小的路径后保存到JOIN类的bets_positions
    • 执行查询执行计划阶段: 把查询执行计划传到执行器进行执行。

    mysql-exe.png

    MySQL查询优化器在逻辑查询执行计划阶段,机遇关系代数规则和启发式规则,把用户指定的SQL经过"等价"的代数转换,变为一种更节省IO的执行序列,执行起来更为高效。

    MySQL查询优化器在物理查询执行计划阶段,在解决多表连接的问题时,有两套算法:一是用户指定表连接次序的算法;二是混杂了贪婪和穷举思想的算法,解决的是较多表的连接和非用户指定连接次序的多表连接,但不能保证得到最优的查询执行计划。

    5.2 MySQL查询优化器的架构和设计思想

    MySQL查询优化器设计精巧,但层次不够清晰,V5.6之后的版本,混乱状态有所改善,但MySQL查询优化器实用而高效,在充分利用索引的基础上,实现了很多查询优化技术,有很多精巧之处值得学习探索。

    MySQL查询优化过程中,查询优化器通过JOIN对象的方法,如JOIN.prepare()、JOIN.optimize(),完成优化工作。JOIN.prepare()完成的查询优化主要包括:子查询的冗余子句消除、IN类型子查询优化、将ALL/ANY等类型的子查询转换为MIN/MAX等操作,这是对简单子查询进行的优化;JOIN.optimize()函数完成的查询优化主要包括:子查询上拉,把外连接优>化为内连接,把嵌套连接消除,WHRER子句、JOIN/ON子句、HAVING子句条件表达式的化简(尤其是对含有常量的表达式的化简、等式合并),优化没有GROUPBY子句情况下的COUNT(*)、MIN()、MAX(),裁剪分区partition(如果查询的表是分区表),确定多表的连接路径(单表是多表的特例,统计join的代价,两种多表连接算法选其一搜索最优的join顺序、生成执行计划)、优化等式谓词、优化DISTINCT、创建临时表存储临时结果优化分组排序等操作。在这样的过程中,MySQL没有把优化过程明显地分为逻辑查询优化阶段和物理查询优化阶段,而是互为混杂,在物理查询优化之后,继续进行了部分逻辑查询优化。这是MySQL查询优化器的一大特点。

    5.3 MySQL查询优化器架构

    MySQL查询优化器为SQL查询语句求解最优的执行方式。MySQL查询优化器架构和执行过程如下图所示。

    MySQL-opimize-structure.png

    MSQL查询语句的执行主要历经4个过程,分别如下:

    1. P1过程:SQL语句输入变为语法查询树。
    2. P2过程:查询预处理,优化相关的内容主要是子查询优化。
    3. P3过程:语法树变为逻辑关系查询树,进而变为物理查询执行计划,挑出最优计划。
    4. P4过程:依据最优查询执行计划得到查询结果。

    MySQL查询语句的执行,主要历经以下4个模块。

    1. M1模块:语法分析模块,执行过程P1的任务。
    2. M2模块:查询 预处理模块,执行过程P2的任务。
    3. M3模块:查询优化模块,执行过程P3的任务。
    4. M4模块:查询执行模块,执行过程P4的任务。

    实现MySQL查询优化器功能的主要是M3模块,其主要有以下两个子阶段的工作。

    • M3-S1逻辑查询优化阶段:把语法查询树通过关系代数原理,优化为关系代数查询树,关系代数的原理在这个阶段运用;
    • M3-S2物理查询优化阶段:把关系代数查询树用于贪婪算法,生成最优执行计划。

    5.4 MySQL查询优化器的层次

    MySQL整个查询优化器从代码层面看,逻辑结构不是很清晰,但是从技术层面看,还是能够分为两个阶段,一是逻辑查询优化阶段,二是物理查询优化阶段。

    • 逻辑查询优化阶段主要依据关系代数可以推知的规则和启发式规则,对SQL语句进行等价变换。MySQL淋漓尽致地使用了关系代数中可推定的各项规则,对投影、选择等操作进行句式的优化;对条件表达式进行了谓词的优化、条件化简;对连接语义进行了外连接、嵌套连接的优化;对集合、GROUPBY等尽量利用索引、排序算法进行优化。另外还利用子查询优化、视图重写、语义优化等技术对查询语句进行了优化。
    • 在物理查询优化阶段,通过贪婪算法,并依据代价估算模型,在求解多表连接顺序的过程中,对多个连接的表进行排序并探索连接方式,找出花费最小的路径,据此生成查询执行计划。在这个阶段,对于单表扫描和两表连接的操作,高效地使用了索引,提高了查询语句的执行速度。

    六 、 从功能角度看MySQL查询优化

    MySQL的查询优化技术的实现,基本也可以分为逻辑优化和物理优化两个阶段,只是和PostgreSQL相比,界线没有那么清晰。MySQL的查询优化过程概述如下:

    1. 优先处理集合操作,把集合操作分解为普通的SPJ和非SPJ操作。
    2. 应用子查询优化技术,去除子查询中冗余部分,转换为半连接、用物化操作优化子查询、执行In向EXISTS转换、优化ALL/ANY等类型的子查询向MIN/MAX转换等。
    3. 消除外连接、消除嵌套连接。
    4. 利用等价谓词重写优化技术,优化WHERE、JOIN/ON、 HAVING等条件中的表达式,尤其是常量表达式和多重等式。
    5. 利用索引优化count(*),MIN(),MAX()。
    6. 进行多表连接的顺序确定。

      • 找出常量表,求解多表连接的过程中不使用常量表作为连接的表(减少搜索空间)。
      • 尽量利用索引优化GROUP BY、DISTINCT结合的操作
      • 利用代价估算模型,评估连接的花费,找出最优连接。
      • 用物化优化半连接嵌套的形式。
      • 从两种多表连接的算法中任选其一: 一是用户指定连接顺序 ,二是使用贪婪和穷尽结合的方式。
      • "选择"、"投影"操作下推
      • 利用索引对ORDER BY进行优化
      • 对GROUP BY/DISTINCT的组合情况进行优化
      • 确定半连接优化策略,从5种备选策略选择其中之一。
      • 对没有GROUP BY和ORDER BY字句的IN子查询进行优化。

    七、 参考文献

    书籍: <<数据库查询优化的艺术: 原理解析和SQL性能优化>>

    展开全文
  • 在本系列的数据库四:浅谈数据库查询过程(Query Processing)中大致地说明了一下数据库的查询过程,但是没提到查询优化器的具体策略与实现。 对于查询而言,我们期望优化器的作用是找到最小代价的正确执行方案。...
  • 这一讲中将讨论MySQL查询优化器,我将试图找到执行具有最高效率查询的方法。  当你提交一个查询的时候,MySQL会分析它,看是否可以做一些优化使处理该查询的速度更快。这一部分将介绍查询优化器是如何工作的。如果...
  • MySQL-查询优化

    千次阅读 2019-01-01 10:53:33
    对于刚入行的程序猿来说,如何优化MySQL查询,是必须跨过的坎。网上有很多关于SQL优化的博文,但大多是片段和结论。这里,我摘抄了《高性能MySQL》一书的内容,从全局的角度将MySQL查询优化的思路和要点进行串通,...
  • MySQL查询优化器工作原理解析

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

    千次阅读 2007-12-17 08:52:00
    Mysql查询优化器浅析(上)译者:杨万富 1 定义 Mysql查询优化器的工作是为查询语句选择合适的执行路径。查询优化器的代码一般是经常变动的,这和存储引擎不太一样。因此,需要理解最新版本的查询优化器是如何组织...
  • MySQL查询优化之查询优化器

    千次阅读 2009-08-20 17:07:00
    MySQL查询优化之查询优化器phpma.com  当你提交一个查询的时候,MySQL会分析它,看是否可以做一些优化使处理该查询的速度更快。这一部分将介绍查询优化器是如何工作的。如果你想知道MySQL采用的优化手段,可以查看...
  • 不过MySQL查询优化器只对少部分查询不适用,而且我们往往可以通过改写查询MySQL高效的完成工作。 1 关联子查询 MySQL的子查询实现的非常糟糕。最糟糕的一类查询时where条件中包含in()的子查询语句。因为MySQL对...
  • explain可以帮助我们分析select语句,找出select语句的瓶颈,从而可以针对性地去做优化,让MySQL查询优化器更好地工作。 MySQL查询优化器有几个目标,其中最主要的目标是尽可能地使用索引,并且使用最严格的索引来...
  • MySQL 查询优化器有几个目标,但是其中最主要的目标是尽可能地使用索引,并且使用最严格的索引来消除尽可能多的数据行。最终目标是提交 SELECT 语句查找数据行,而不是排除数据行。优化器试图排除数据行的原因在于它...
  • Mysql查询优化器浅析(下)

    千次阅读 2007-12-17 09:20:00
    Mysql查询优化器浅析(下)译者:杨万富 7 存取类型 当我们评估一个条件表达式,MySQL判断该表达式的存取类型。下面是一些存取类型,按照从最优到最差的顺序进行排列:system … 系统表,并且是常量表const …...
  • 常见Mysql的慢查询优化方式

    万次阅读 多人点赞 2018-09-22 09:20:33
     在公司实习的时候,导师分配了SQL慢查询优化的任务,任务是这样的:每周从平台中导出生产数据库的慢查询文件进行分析。进行SQL优化的手段也主要是修改SQL写法,或者新增索引。  现在从记录项目中的一点点做起。 ...
  • Mysql查询性能优化

    千次阅读 2018-02-28 15:50:55
    Mysql查询性能优化要从三个方面考虑,库表结构优化、索引优化查询优化。通常在实际应用中,我们要面对这三种搅和一起的情况。一、 库表结构优化 良好的逻辑设计和物理设计是高性能的基石。库表结构的设计既要关注...
  • MySql查询优化方法总结

    千次阅读 2018-11-07 17:18:04
    常用查询优化 1: max()优化: 在相应列上添加索引 2: count()优化:count(*) 会算出包含null记录的数量, count(field_name)只包含不含 null的数量(这也是很多时候两种count方式结果不一致的原因), count()的...
  • MySQL查询优化之二-范围优化(Range Optimization) 如需转载请标明出处:http://blog.csdn.net/itas109 QQ技术交流群:12951803 环境: MySQL版本:5.5.15 操作系统:windows 本文讨论范围的优化...
  • MySQL查询优化之一-WHERE语句优化

    千次阅读 2018-05-21 09:41:01
    MySQL查询优化之一-WHERE语句优化 如需转载请标明出处:http://blog.csdn.net/itas109 QQ技术交流群:12951803 环境: MySQL版本:5.5.15 操作系统:windows 本文讨论WHERE语句的优化。 这些示例使用SELECT...
1 2 3 4 5 ... 20
收藏数 292,026
精华内容 116,810
关键字:

mysql查询优化器