MySQL中SQL优化建议的示例分析

本期，边肖将给大家带来一个MySQL中SQL优化建议的实例分析。文章内容丰富，从专业角度进行分析和描述。希望你看完这篇文章能有所收获。

今天早上看到同事的一个优化需求。其实优化的时间不多了，但是我对这个SQL的优化想了很多，希望能有一些参考。

商科学生提供的SQL如下：

选择b . order _ id from(selecta . order _ id，a . order _ time ascreate _ time from rade _ order awherea . user _ id=12345678 .anda . deleted=0 unionselectv . order _ id，v . create _ time from virtual _ order vwhere v . user _ id=12345678 .ORDERBYorder_idDESC)ASbLIMIT0，10；根据反馈，这个SQL的执行时间是200毫秒，在压力测试的情况下会达到500毫秒左右。从业务角度来看，目前不满足需求。我想看看我们有没有优化的建议。

第一印象SQL的执行时间为200~500毫秒。看来要优化的牌不多了。如果你想得到一个可以接受的基准值，当然反馈会越快越好。所以从这个角度来看，我们不妨看看这个SQL按照毫秒级优化标准需要做哪些补充工作。

首先，通过SQL来看逻辑。总体逻辑是根据用户id查询两个数据源(trade_order和virtual_order)，从这两个数据源中找出10条奇数数据并返回。该用户在两个数据源中可能有一个数字，也可能没有。只要有匹配的就退回，一共退回10件。似乎选择了联合的组合方法来消除重复。

不先看表结构信息，我一般有以下建议：

Union模式更推荐采用union all，两个数据源有数据重叠应该是不合理的。

查询语句中使用了Order_time，但是数据返回根本没有用，建议去掉。

SQL层承担了太多的数据处理压力，比如多数据源、去重过滤、分页，能不能精简？

当然，到了这里，就脱离了业务的需求，属于事事不顺眼的状态，总想找出一些问题。而且，对于商科学生来说，即使有十个八个的需求，你也得有一个收入较高的，他们采纳其他需求的可能性就越大，否则什么都不会做。

所以在这里，我们开始分析。不看执行计划优化SQL是不够的。在执行之前，我普遍感觉表中有大量的数据。应该是已经生成了派生表，然后在数据重过滤级别的消耗比较大，而对于两个子查询，返回的结果集应该很少。预测的实施是：

1)子查询trade_order应该是快速的，具有毫米响应。

2)子查询virtual_order应该快，但最后有一个按操作排序的，可能成本会高一点。

3)3)并集的去重过滤成本比较高，涉及到两个结果集的组合。如果返回更多结果，可能是瓶颈。

从执行结果来看，我有些惊讶，其中virtual_order返回了40多万行，相当于直接扫描了整个表。

其他部分也会受到影响，所以后续加工也会受到影响。

为了快速定位问题，我把两个子查询拆分开来分别执行，并检查执行计划，这是分析瓶颈最快的方法。

解释选择-v.order_id，-v . create _ time-FROM-virtual _ order v-WHERE-v . user _ id=12345678 .实施计划如下：

mg src="https://cache.yisu.com/upload/information/20210522/377/518966.jpg" alt="MySQL中SQL优化建议的示例分析">

可以看到是直接走了全表扫描，这是一个基础需求，不会业务同学漏了索引吧，然后查看表结构：

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；

发现user_id是走了索引的，那么问题来了，user_id既然是索引，但是为什么SQL语句中依然走了全表扫描呢?

此处思考10秒钟，继续往下看。

其实这个时候问题的边界都很清晰了，SQL语句很简单，索引也存在，走了全表扫描，在MySQL中可以暂时排除直方图的影响，目前在5.7版本中还不存在直方图的特性，那么结果只有一个：字段的类型产生了隐式类型转换。

这个部分可以参考这篇的一篇文章

MySQL中需要重视的隐式转换

比如初始化语句如下：

create table test(id int primary key,name varchar(20) ,key idx_name(name)); insert into test values(1,'10'),(2,'20');

然后我们使用如下的两条语句进行执行计划的对比测试。

explain select * from test where name=20; explain select * from test where  name=’20’;

在name列为字符类型时，得到的执行计划列表如下：

可以很明显的看到，在name为字符串类型时，如果where条件为name=20,则执行全索引扫描，查看warning信息会明确提示：

Message: Cannot use range access on index 'idx_name' due to type or collation  conversion on field 'name'

所以此处的问题也显而易见了。

修改了子查询的条件为字符后，整个SQL的执行效率就立马好多了。

使用sql_no_cache的方式测试。

SQL修改前性能：

+-----------------------+  2 rows in set (0.27 sec)  修改后性能：  +-----------------------+  2 rows in set (0.00 sec)

然后再次查看执行计划，就都规规矩矩了,这样我们就解决了瓶颈问题，而那些规范，更好的改进就可以逐步展开了，而从建议的角度来看，采用的概率也会高一些。

当然在这个基础上确实有一些补充的建议，在定位瓶颈之后也可以摊开来说了。

优化不是一锤子买卖，在这个基础上，也发现了一些其他的问题，可以看下这个表的表结构信息，其实能够发现一些设计上的小问题。

1) 表字段的字符型基本都是varchar(255),需要尽可能避免这种使用习惯，对于存储性能的开销会有显著影响

2)使用的int类型 int(3)，这种使用对于int还是存储4个字节，但是有限范围大大减少，可以考虑更小的数值类型

3)表的索引比较松散，可以根据业务模型创建复合索引，比如user_id和status的结合场景更多，应该创建的是(user_id,status)的复合索引

上述就是小编为大家分享的MySQL中SQL优化建议的示例分析了，如果刚好有类似的疑惑，不妨参照上述分析进行理解。如果想知道更多相关知识，欢迎关注行业资讯频道。