MySQL的查询优化技术-04

数据库查询优化技术/li_hx/TheQueryOptimizationTechniqueOfMySQL(5.6.X)@那海蓝蓝1数据库查询优化技术/li_hx/1Adatatechnologylearners,practitionersBook:《数据库查询优化器的艺术：原理解析与SQL性能优化》23Blog:/li_hx4Twitter：@那海蓝蓝Email:database_XX@126.com5Direction:DB(PostgreSQL,MySQL.etc)63/li_hx/课程大纲：1数据库与关系代数2数据库查询优化技术总揽3查询优化技术理论与MySQL实践（一）------子查询的优化（一）4查询优化技术理论与MySQL实践（二）------子查询的优化（二）5查询优化技术理论与MySQL实践（三）------视图重写与等价谓词重写6查询优化技术理论与MySQL实践（四）------条件化简7查询优化技术理论与MySQL实践（五）------外连接消除、嵌套连接消除与连接消除8查询优化技术理论与MySQL实践（六）------数据库的约束规则与语义优化9查询优化技术理论与MySQL实践（七）------非SPJ的优化10MySQL物理查询优化技术概述11MySQL索引的利用、优化12表扫描与连接算法与MySQL多表连接优化实践13查询优化的综合实例------TPCH实践（一）14查询优化的综合实例------TPCH实践（二）15关系代数对于数据库的查询优化的指导意义------查询优化技术总结/li_hx/Lesson4:LogicalQueryOptimizationSubquery(2)

3MySQL支持哪些子查询的优化技术？5/li_hx/1MySQL可以优化什么格式的子查询？2MySQL支持对哪些类型的子查询进行优化?4MySQL子查询总结6/li_hx/1MySQL可以优化什么格式的子查询？MySQL支持对简单SELECT查询中的子查询优化，包括：简单SELECT查询中的子查询。带有DISTINCT、ORDERBY、LIMIT操作的简单SELECT查询中的子查询。7/li_hx/1MySQL可以优化什么格式的子查询？CREATETABLEt1(a1INT,b1INT,PRIMARYKEY(a1));CREATETABLEt2(a2INT,b2INT,PRIMARYKEY(a2));CREATETABLEt3(a3INT,b3INT,PRIMARYKEY(a3));插入10000行与上例同样的数据。查询执行计划如下：mysql>EXPLAINEXTENDEDSELECT*FROMt1WHEREt1.a1<100ANDa1IN(SELECTa2FROMt2WHEREt2.a2>10);+----+-------------+-------+--------+---------+--------------------------+|id|select_type|table|type|key|Extra|+----+-------------+-------+--------+---------+--------------------------+|1|SIMPLE|t2|range|PRIMARY|Usingwhere;Usingindex||1|SIMPLE|t1|eq_ref|PRIMARY|NULL|+----+-------------+-------+--------+---------+--------------------------+2rowsinset,1warning(0.00sec)8/li_hx/1MySQL可以优化什么格式的子查询？MySQL不支持对如下情况的子查询进行优化：带有UNION操作。带有GROUPBY、HAVING、聚集函数。使用ORDERBY中带有LIMIT。内表、外表的个数超过MySQL支持的最大表的连接数。9/li_hx/1MySQL可以优化什么格式的子查询？聚集函数操作在子查询中，查询执行计划如下：mysql>EXPLAINEXTENDEDSELECT*FROMt1WHEREt1.a1>(SELECTMIN(t2.a2)FROMt2);+----+-------------+-------+------+---------------+------------------------------+|id|select_type|table|type|possible_keys|Extra|+----+-------------+-------+------+---------------+------------------------------+|1|PRIMARY|t1|ALL|t_idx_t1|Usingwhere||2|SUBQUERY|NULL|NULL|NULL|Selecttablesoptimizedaway|+----+-------------+-------+------+---------------+------------------------------+10/li_hx/2MySQL支持哪些子查询的优化技术？1子查询合并技术,不支持：--例1mysql>explainEXTENDEDSELECT*FROMt1WHEREa1<4AND(EXISTS(SELECTa2FROMt2WHEREt2.a2<5ANDt2.b2=1)OREXISTS(SELECTa2FROMt2WHEREt2.a2<5ANDt2.b2=2));+----+-------------+-------+-------+---------------+------------------------------------+|id|select_type|table|type|possible_keys|Extra|+----+-------------+-------+-------+---------------+------------------------------------+|1|PRIMARY|t1|range|t_idx_t1|Usingindexcondition||3|SUBQUERY|t2|range|t_idx_t2|Usingindexcondition;Usingwhere||2|SUBQUERY|t2|range|t_idx_t2|Usingindexcondition;Usingwhere|+----+-------------+-------+-------+---------------+------------------------------------+t2表上执行了2次子查询如果支持子查询合并技术,则t2表上只执行一次子查询11/li_hx/2MySQL支持哪些子查询的优化技术？1子查询合并技术,不支持：--例1被查询优化器处理后的语句为：/*select#1*/select`t1`.`id1`AS`id1`,`t1`.`a1`AS`a1`,

`t1`.`b1`AS`b1`from`t1`where((`t1`.`a1`<4)and

(exists(/*select#2*/select`t2`.`a2`//没有和条件“b2=2”合并在一起判断，而是一个独立结点

from`t2`where((`t2`.`a2`<5)and(`t2`.`b2`=1)))

or

exists(/*select#3*/select`t2`.`a2`//没有和条件“b2=1”合并在一起判断，而是一个独立结点

from`t2`where((`t2`.`a2`<5)and(`t2`.`b2`=2)))

))12/li_hx/2MySQL支持哪些子查询的优化技术？1子查询合并技术,不支持：--例1SQL语句的语义等价于上一条SQL语句，查询执行计划如下：mysql>EXPLAINEXTENDEDSELECT*FROMt1WHEREa1<10ANDEXISTS(SELECTa2FROMt2WHEREt2.a2<5AND(t2.b2=1ORt2.b2=2));+----+-------------+-------+------+------+-------------+|id|select_type|table|type|key|Extra|+----+-------------+-------+------+------+-------------+|1|PRIMARY|t1|ALL|NULL|Usingwhere||2|SUBQUERY|t2|ALL|NULL|Usingwhere|+----+-------------+-------+------+------+-------------+2rowsinset,1warning(0.02sec)人为的合并查询条件为“(t2.b2=1ORt2.b2=2)”t2表上的子查询，只执行一次13/li_hx/2MySQL支持哪些子查询的优化技术？1子查询合并技术,不支持：--例1被查询优化器处理后的语句为：/*select#1*/select`t1`.`id1`AS`id1`,`t1`.`a1`AS`a1`,`t1`.`b1`AS`b1`from`t1`where((`t1`.`a1`<10)and

exists(/*select#2*/select`t2`.`a2`

from`t2`

where((`t2`.`a2`<5)and((`t2`.`b2`=1)or(`t2`.`b2`=2)))))人为的合并查询条件为“(t2.b2=1ORt2.b2=2)”t2表上的子查询，只执行一次14/li_hx/2MySQL支持哪些子查询的优化技术？2子查询展开（子查询反嵌套）技术,支持得不够好：--例2mysql>EXPLAINEXTENDEDSELECT*FROMt1,(SELECT*FROMt2WHEREt2.a2>10)v_t2WHEREt1.a1<10ANDv_t2.a2<20;+----+-------------+------------+------+------+----------------------------------------------------+|id|select_type|table|type|key|Extra|+----+-------------+------------+------+------+----------------------------------------------------+|1|PRIMARY|<derived2>|ALL|NULL|Usingwhere||1|PRIMARY|t1|ALL|NULL|Usingwhere;Usingjoinbuffer(BlockNestedLoop)||2|DERIVED|t2|ALL|NULL|Usingwhere|+----+-------------+------------+------+------+----------------------------------------------------+3rowsinset,1warning(0.00sec)从查询执行计划看，在表t2上的子查询被单独执行，没和表t1进行了嵌套循环连接，子查询没有被消除，所以MySQL支持子查询反嵌套技术有限（需要对比例3和例4）15/li_hx/2MySQL支持哪些子查询的优化技术？2子查询展开（子查询反嵌套）技术,支持得不够好：--例2被查询优化器处理后的语句为：/*select#1*/select`test`.`t1`.`id1`AS`id1`,`test`.`t1`.`a1`AS`a1`,

`test`.`t1`.`b1`AS`b1`,`v_t2`.`id2`AS`id2`,`v_t2`.`a2`AS`a2`,

`v_t2`.`b2`AS`b2`from`test`.`t1`join(

/*select#2*/select`test`.`t2`.`id2`AS`id2`,`test`.`t2`.`a2`AS`a2`,

`test`.`t2`.`b2`AS`b2`

from`test`.`t2`

where(`test`.`t2`.`a2`>10))`v_t2`where((`test`.`t1`.`a1`<10)and(`v_t2`.`a2`<20))从查询执行计划看，在表t2上的子查询被单独执行，参看/*select#2*/16/li_hx/2MySQL支持哪些子查询的优化技术？2子查询展开（子查询反嵌套）技术,支持得不够好：--例3再看一个IN子查询的例子，查询执行计划如下：mysql>EXPLAINEXTENDEDSELECT*FROMt1WHEREt1.a1<100ANDa1IN(SELECTa2FROMt2WHEREt2.a2>10);+----+--------------+-------------+------+------+----------------------------------------------------+|id|select_type|table|type|key|Extra|+----+--------------+-------------+------+------+----------------------------------------------------+|1|SIMPLE|<subquery2>|ALL|NULL|Usingwhere||1|SIMPLE|t1|ALL|NULL|Usingwhere;Usingjoinbuffer(BlockNestedLoop)||2|MATERIALIZED|t2|ALL|NULL|Usingwhere|+----+--------------+-------------+------+------+----------------------------------------------------+3rowsinset,1warning(0.00sec)从查询执行计划看，在表t2上的子查询被物化17/li_hx/2MySQL支持哪些子查询的优化技术？2子查询展开（子查询反嵌套）技术,支持得不够好：--例3被查询优化器处理后的语句为：/*select#1*/select`t1`.`id1`AS`id1`,`t1`.`a1`AS`a1`,

`t1`.`b1`AS`b1`from`t1`where((`t1`.`a1`<100)and

`t1`.`a1`in(/*select#2*/

select`t2`.`a2`

from`t2`

where(`t2`.`a2`>10)))从查询执行计划看，子查询被物化，但没有被上拉到顶层与t1进行连接。从查询执行计划看，在表t2上的子查询被物化18/li_hx/2MySQL支持哪些子查询的优化技术？2子查询展开（子查询反嵌套）技术,支持得不够好：--例4重新构造表如下，与上面示例不同之处在于这里所用列是主键列。CREATETABLEt1(a1INT,b1INT,PRIMARYKEY(a1));CREATETABLEt2(a2INT,b2INT,PRIMARYKEY(a2));CREATETABLEt3(a3INT,b3INT,PRIMARYKEY(a3));插入10000行与上例同样的数据19/li_hx/2MySQL支持哪些子查询的优化技术？2子查询展开（子查询反嵌套）技术,支持得不够好：--例4执行与例3同样的查询语句，查询执行计划如下：mysql>EXPLAINEXTENDEDSELECT*FROMt1WHEREt1.a1<100ANDa1IN(SELECTa2FROMt2WHEREt2.a2>10);+----+-------------+-------+--------+---------+--------------------------+|id|select_type|table|type|key|Extra|+----+-------------+-------+--------+---------+--------------------------+|1|SIMPLE|t2|range|PRIMARY|Usingwhere;Usingindex||1|SIMPLE|t1|eq_ref|PRIMARY|NULL|+----+-------------+-------+--------+---------+--------------------------+2rowsinset,1warning(0.00sec)20/li_hx/2MySQL支持哪些子查询的优化技术？2子查询展开（子查询反嵌套）技术,支持得不够好：--例4执行与例3同样的查询语句，查询执行计划如下：被查询优化器处理后的语句为：/*select#1*/select`tt`.`t1`.`a1`AS`a1`,`tt`.`t1`.`b1`AS`b1`,

`tt`.`t1`.`c1`AS`c1`from`tt`.`t2`join`tt`.`t1`where((`tt`.`t1`.`a1`=`tt`.`t2`.`a2`)and(`tt`.`t2`.`a2`<100)and(`tt`.`t2`.`a2`>10)从查询执行计划看，子查询不存在，SQL语句被转换为内连接操作，这表明MySQL只有在针对主键列进行类似的子查询时，才把子查询上拉为内连接。所以，MySQL还是支持子查询展开技术的。21/li_hx/2MySQL支持哪些子查询的优化技术？2子查询展开（子查询反嵌套）技术,支持得不够好：--例4执行与例3同样的查询语句，查询执行计划如下：被查询优化器处理后的语句为：/*select#1*/select`tt`.`t1`.`a1`AS`a1`,`tt`.`t1`.`b1`AS`b1`,

`tt`.`t1`.`c1`AS`c1`from`tt`.`t2`join`tt`.`t1`where((`tt`.`t1`.`a1`=`tt`.`t2`.`a2`)and(`tt`.`t2`.`a2`<100)and(`tt`.`t2