在之前一次大数据技术分享(大数据-Apache Kylin 2.5更智能、更敏捷、更易用的OLAP引擎)中,有一页的ppt是这样讲sql优化的,有些感触现分享出来。SQL语句的逻辑处理顺序,指的是SQL语句按照一定的规则,一整条语句应该如何执行,每一个关键字、子句部分在什么时刻执行。除了逻辑顺序,还有物理执行顺序。物理顺序是SQL语句真正被执行时的顺序(执行计划),它是由各数据库系统的关系引擎中的语句分析器、优化器等等组件经过大量计算、分析决定的。
通常一般人谈起mysql查询优化,侃侃而谈。其实很多人都忽略了这样的一个问题,mysql的逻辑执行顺序是什么样子的。
以下是对上述逻辑执行顺序的描述:
- .首先从FROM语句中获取要操作的表并计算笛卡尔积。如果有要联接的表,则还获取联接表。对它们计算笛卡尔积,笛卡尔积的结果形成一张虚拟表vt1。这里就体现了物理顺序和逻辑顺序的一个不同点:按照逻辑顺序,在执行SQL语句之初总是会进行笛卡尔积的计算,如果是两张或多张非常大的表,计算笛卡尔积是非常低效的行为,这是不能容忍的。所以物理顺序会进行一些优化决定,比如使用索引跳过一部分或整个笛卡尔积让计算变得很小。
- .对虚拟表vt1执行ON筛选语句,得到虚拟表vt2。
- .根据联接类型,将保留表的外部行添加到vt2中得到虚拟表vt3。
- .对vt3执行where条件筛选,得到虚拟表vt4。
- .执行分组,得到虚拟表vt5。注意,分组之后,整个SQL的操作上下文就变成了分组列,而不再是表中的每一列,后续的一切操作都是围绕所分的组作为操作对象进行操作的。也就是说,不在分组列表中的列不能在后续步骤中使用。例如,使用"group by a"对a列分组,那么后续的select列表中就不能使用b列,除非是对b列进行分组聚合运算。SQL Server、Oracle和MariaDB、Mysql最大的区别就在于此步,后两者可以引用分组列以外的列。
- .对vt5执行集合操作cube或者rollup,得到虚拟表vt6。
- .对分组的最终结果vt6执行having筛选,得到虚拟表vt7。
- .根据给定的选择列列表,将vt7中的选择列插入到虚拟表vt8中。注意,选择列是"同时性操作",在选择列中不能使用列别名来引用列表中的其他列。例如 select col1+1 as a,a+1 as b from t1 是错误的,因为"col1+1"和"a+1"之间没有执行上的先后顺序,所以它认为"a+1"中的a列是不存在的。
- .对vt8进行窗口分组相关的计算,得到虚拟表vt9。
- .对vt9按照指定的列去除重复行,得到虚拟表vt10。这一步是将数据复制到内存中相同的临时表结构中进行的,不过该临时表多出了一个唯一性索引列用来做重复消除。
- .对vt10进行排序,排序后的表为虚拟表vt11。
- .从vt11中根据top条件挑出其中满足的行,得到虚拟表vt12。如果没有应用order by,则记录是无序的集合,top挑出的行可能是随机的。也因此top一般要和order by字句一起使用。
- .将vt12从服务端返回给客户端作为最终查询结果。
总之,sql优化要关注执行顺序,优化项的执行顺序再靠后的效果越差,性能提升越小。
那接下来我们试一下吧!
--SELECT VERSION(); 5.6.24-log
--opt_trade_payment rows>100w
--opt_taskorder(PRIMARY KEY (`taskorder_id`)) rows>20w
--下面sql很慢
SELECT p.taskorder_paymentid,p.taskorder_fee_type,p.total_fee FROM opt_trade_payment p WHERE p.taskorder_id IN(SELECT t.taskorder_id FROM opt_taskorder t WHERE t.trans_trade_type=2 AND t.hub_fee>0) LIMIT 20000;
--查看执行计划和sql优化后的
EXPLAIN EXTENDED SELECT p.taskorder_paymentid,p.taskorder_fee_type,p.total_fee FROM opt_trade_payment p WHERE p.taskorder_id IN(SELECT t.taskorder_id FROM opt_taskorder t WHERE t.trans_trade_type=2 AND t.hub_fee>0) LIMIT 20000;
SHOW WARNINGS;
分解来看看,in内的子查询很快的,那是什么问题你?要看执行计划,在对于上面标准的sql执行顺序。
/* select#1 */ SELECT
`test-oms`.`p`.`taskorder_paymentid` AS `taskorder_paymentid`,
`test-oms`.`p`.`taskorder_fee_type` AS `taskorder_fee_type`,
`test-oms`.`p`.`total_fee` AS `total_fee`
FROM `test-oms`.`opt_trade_payment` `p`
semi JOIN (`test-oms`.`opt_taskorder` `t`)
WHERE ((`test-oms`.`p`.`taskorder_id` = `<subquery2>`.`taskorder_id`)
AND (`test-oms`.`t`.`trans_trade_type` = 2)
AND (`test-oms`.`t`.`hub_fee` > 0)) LIMIT 20000;可以看出mysql进行了查询语句优化,如果对opt_trade_payment添加KEY `idx_otp_taskorder_id` (`taskorder_id`),你会发现它效率得到提升
/* select#1 */SELECT
`test-oms`.`p`.`taskorder_paymentid` AS `taskorder_paymentid`,
`test-oms`.`p`.`taskorder_fee_type` AS `taskorder_fee_type`,
`test-oms`.`p`.`total_fee` AS `total_fee`
FROM `test-oms`.`opt_taskorder` `t`
JOIN `test-oms`.`opt_trade_payment` `p`
WHERE ((`test-oms`.`t`.`taskorder_id` = `test-oms`.`p`.`taskorder_id`)
AND (`test-oms`.`t`.`trans_trade_type` = 2)
AND (`test-oms`.`t`.`hub_fee` > 0))
LIMIT 20000;
mysql嵌套子查询效率确实比较低,可以将其优化成连接查询。