从宏观上谈mysql查询优化

以你之姓@ 2022-04-05 03:28 270阅读 0赞

在之前一次大数据技术分享（大数据-Apache Kylin 2.5更智能、更敏捷、更易用的OLAP引擎）中，有一页的ppt是这样讲sql优化的，有些感触现分享出来。SQL语句的逻辑处理顺序，指的是SQL语句按照一定的规则，一整条语句应该如何执行，每一个关键字、子句部分在什么时刻执行。除了逻辑顺序，还有物理执行顺序。物理顺序是SQL语句真正被执行时的顺序(执行计划)，它是由各数据库系统的关系引擎中的语句分析器、优化器等等组件经过大量计算、分析决定的。

通常一般人谈起mysql查询优化，侃侃而谈。其实很多人都忽略了这样的一个问题，mysql的逻辑执行顺序是什么样子的。

![20181229172237440][]

以下是对上述逻辑执行顺序的描述：

1.  .首先从FROM语句中获取要操作的表并计算笛卡尔积。如果有要联接的表，则还获取联接表。对它们计算笛卡尔积，笛卡尔积的结果形成一张虚拟表vt1。这里就体现了物理顺序和逻辑顺序的一个不同点：按照逻辑顺序，在执行SQL语句之初总是会进行笛卡尔积的计算，如果是两张或多张非常大的表，计算笛卡尔积是非常低效的行为，这是不能容忍的。所以物理顺序会进行一些优化决定，比如使用索引跳过一部分或整个笛卡尔积让计算变得很小。
2.  .对虚拟表vt1执行ON筛选语句，得到虚拟表vt2。
3.  .根据联接类型，将保留表的外部行添加到vt2中得到虚拟表vt3。
4.  .对vt3执行where条件筛选，得到虚拟表vt4。
5.  .执行分组，得到虚拟表vt5。注意，分组之后，整个SQL的操作上下文就变成了分组列，而不再是表中的每一列，后续的一切操作都是围绕所分的组作为操作对象进行操作的。也就是说，不在分组列表中的列不能在后续步骤中使用。例如，使用"group by a"对a列分组，那么后续的select列表中就不能使用b列，除非是对b列进行分组聚合运算。SQL Server、Oracle和MariaDB、Mysql最大的区别就在于此步，后两者可以引用分组列以外的列。
6.  .对vt5执行集合操作cube或者rollup，得到虚拟表vt6。
7.  .对分组的最终结果vt6执行having筛选，得到虚拟表vt7。
8.  .根据给定的选择列列表，将vt7中的选择列插入到虚拟表vt8中。注意，选择列是"同时性操作"，在选择列中不能使用列别名来引用列表中的其他列。例如 select col1+1 as a,a+1 as b from t1 是错误的，因为"col1+1"和"a+1"之间没有执行上的先后顺序，所以它认为"a+1"中的a列是不存在的。
9.  .对vt8进行窗口分组相关的计算，得到虚拟表vt9。
10. .对vt9按照指定的列去除重复行，得到虚拟表vt10。这一步是将数据复制到内存中相同的临时表结构中进行的，不过该临时表多出了一个唯一性索引列用来做重复消除。
11. .对vt10进行排序，排序后的表为虚拟表vt11。
12. .从vt11中根据top条件挑出其中满足的行，得到虚拟表vt12。如果没有应用order by，则记录是无序的集合，top挑出的行可能是随机的。也因此top一般要和order by字句一起使用。
13. .将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执行顺序。
> 
> ![20190528134857953.png][]
> 
> /\* 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\`)，你会发现它效率得到提升
> 
> ![20190528105247306.png][]
> 
> /\* 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嵌套子查询效率确实比较低，可以将其优化成连接查询。![50403061][]

[20181229172237440]: /images/20220405/ffdb31b9563d447e969824bd0209af66.png
[20190528134857953.png]: /images/20220405/03e74438989a4c58886caf20d477eecc.png
[20190528105247306.png]: /images/20220405/26a4dd3099fa4680b240249ee0bdb23f.png
[50403061]: /images/20220405/1e925b08eb18421ab4912c021d6ab4de.png