这期内容当中小编将会给大家带来有关MySQL中SQL优化建议的示例分析,文章内容丰富且以专业的角度为大家分析和叙述,阅读完这篇文章希望大家可以有所收获。
今天早上看到同事的一个优化需求,优化的时间其实不多,但是对于这条SQL的优化思考了很多,希望有一些参考。
业务同学提供的SQL如下:
SELECT b.order_id FROM ( SELECT a.order_id, a.order_time AS create_time FROM trade_order a WHERE a.user_id = 12345678 。。。。。。 AND a.deleted = 0 UNION SELECT v.order_id, v.create_time FROM virtual_order v WHERE v.user_id = 12345678 。。。。 ORDER BY order_id DESC ) AS b LIMIT 0, 10;
根据反馈,这条SQL的执行时长在200毫秒,在压测情况下会到500毫秒左右,从业务层面来看,目前是不满足需求的,想看看我们有没有优化的建议。
第一印象这条SQL执行时长200~500毫秒,要优化好像可打的牌不多啊,如果要想得到一个可接受的基准值,当然反馈会是越快越好。所以从这个角度来看,我们不妨按照毫秒级优化的标准来看,这条SQL需要做哪些补充的工作。
首先通过SQL看下逻辑情况,整体的逻辑是按照用户id去查询两个数据源(trade_order和virtual_order),从两个数据源查询出10条单号数据返回。这个用户在两个数据源中可能有单号,也可能没有,只要有匹配的就返回,累计返回10条,看起来是为了去重才选择了union的组合方式。
先不看表结构信息,我大体有了如下的建议:
union的模式更建议采用union all,两个数据源存在数据重合应该是不合理的。
查询语句里面使用了order_time但是数据返回压根没有用到,建议去掉
SQL层面承载了太多的数据处理压力,比如多数据源,去重和过滤,分页,是不是可以做下精简。
当然到了这里,和业务的需求就产生了脱节,这就属于那种看啥都不顺眼的状态,总想找出点问题来,而且对于业务同学来说,哪怕十个八个需求,你得有一个需求的收益更高,他们采用其他需求的可能性才越大,否则就是不作为了。
所以到了这里,我们开始做下分析,要优化SQL不看看执行计划是不过关的,在执行前,我的大体感觉表数据量很大,应该是生成了派生表,然后在数据去重过滤层面的消耗比较大,而两个子查询来说,返回的结果集应该很少。 预测的执行情况是:
1)子查询trade_order应该很快,毫米级响应
2)子查询virtual_order应该也很快,但是最后有一个order by操作,可能代价略高
3)union的去重过滤代价相对较大,涉及到两个结果集的合并,如果返回结果较多,可能是瓶颈
从执行结果来看,让我有些意外,其中virtual_order的返回结果竟然有40多万行,相当于直接走了全表扫描。
而其他的部分也会收到相关影响,所以后续的处理都会受到影响。
为了快速定位问题,我把两个子查询拆开单独执行,查看执行计划,这是分析瓶颈最快的一种处理思路。
>explain SELECT -> v.order_id, -> v.create_time -> FROM -> virtual_order v -> WHERE -> v.user_id = 12345678 。。。;
执行计划如下:
可以看到是直接走了全表扫描,这是一个基础需求,不会业务同学漏了索引吧,然后查看表结构:
CREATE TABLE `virtual_order` ( `order_id` varchar(255) NOT NULL COMMENT '订单ID', 。。。 `user_id` varchar(255) DEFAULT NULL COMMENT '用户ID', 。。。 `refund` tinyint(3) DEFAULT NULL COMMENT ' 是否退款(1:无,2:是)', `atc_pay_status` int(3) NOT NULL DEFAULT '0' COMMENT '支付状态', 。。。 PRIMARY KEY (`order_id`), KEY `order_status` (`order_status`), KEY `user_id` (`user_id`), KEY `prepaid_account` (`prepaid_account`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;MySQL中SQL优化建议的示例分析