今天早上看到一个同事的优化需求。优化的时间不多，但是对这条SQL的优化思考了很多，希望能有一些参考。业务同学提供的SQL如下：SELECTb.order_idFROM(SELECTa.order_id,a.order_timeAScreate_timeFROMtrade_orderaWHEREa.user_id=12345678...ANDa.deleted=0UNIONSELECTv.order_id,v.create_timeFROMvirtual_ordervWHEREv.user_id=12345678.SCYorb.,10;根据反馈，这条SQL的执行时间是200毫秒，压测下来会在500毫秒左右，从业务上来看，目前不满足需求，想看看大家有没有优化建议，第一感觉这条SQL的执行时间是200-500毫秒，看来优化打的牌不多，如果想得到一个可以接受的benchmark值，反馈当然是asfastaspossible.那么从这个角度，我们不妨按照毫秒级的优化标准，看看这条SQL需要做的补充工作，先看逻辑情况通过SQL。整体逻辑是根据用户id查询两个数据源（trade_order和virtual_order），从两个数据源返回10条单号数据。该用户在两个数据源中可能有也可能没有单个号码。只要有匹配就返回，一共返回10条记录。看来选择union的组合是为了去重。不考虑表结构信息，我一般有以下建议：union模型更推荐使用unionall，两个数据源有数据重叠是不合理的。查询语句中使用了order_time，但根本没有使用数据返回。建议去掉SQL层面过多的数据处理压力，比如多数据源、去重过滤、分页等。可以简化吗？当然，当你来到这里时，你与业务需求脱节了。这就是那种不顺眼的状态，总是想找点问题，而对于商科学生来说，就算有十个八个需求，你也要一个需求的收入越高，他们就越有可能是采用其他要求，否则就是无所作为。所以当我们到了这里，我们就开始做分析。不看执行计划就优化SQL是不够的。执行前，总感觉表的数据量很大。应该是生成派生表，然后对数据进行去重过滤。消费水平比较大，对于两个子查询来说，返回的结果集应该很小。预测执行状态为：1)子查询trade_order应该很快，毫米级响应2)子查询virtual_order应该也很快，但是最后有一个orderbyoperation，可能会稍微贵一些3)的union的去重过滤代价比较大，涉及到两个结果集的合并。如果返回结果很多，可能是瓶颈。从执行结果来看，我有点意外。其中virtual_order的返回结果有40万多行，相当于直接去整张表。扫描。其他部分也会受到影响，所以后续的加工也会受到影响。为了快速定位问题，我把两个子查询拆分开来分别执行，查看执行计划，这是分析瓶颈最快的方法。>>解释SELECT->v.order_id,->v.create_time->FROM->virtual_orderv->WHERE->v.user_id=12345678。..;执行计划如下：可以看到直接进行了全表扫描。这是基本要求。如果你觉得商科学生不会错过索引，那么查看表结构：CREATETABLE`virtual_order`(`order_id`varchar(255)NOTNULLCOMMENT'OrderID',...`user_id`varchar(255)DEFAULTNULLCOMMENT'userID',...`refund`tinyint(3)DEFAULTNULLCOMMENT'Refund(1:None,2:Yes)',`atc_pay_status`int(3)NOTNULLDEFAULT'0'COMMENT'付款状态',...PRIMARYKEY(`order_id`),KEY`order_status`(`order_status`),KEY`user_id`(`user_id`),KEY`prepaid_account`(`prepaid_account`))ENGINE=InnoDBDEFAULTCHARSET=utf8;发现user_id是有索引的，那么问题来了，既然user_id是索引，为什么SQL语句中还是进行了全表扫描呢？在这里思考10秒，继续往下看。其实这个时候问题的边界已经很清楚了。SQL语句很简单，索引也存在。全表扫描后，MySQL中可以暂时排除直方图的影响。目前，直方图功能在5.7版本中不存在。那么只有一个结果：字段的类型产生隐式类型转换。这部分可以参考本文中的一篇文章。MySQL中需要注意的隐式转换，比如初始化语句如下：createtabletest(idintprimarykey,namevarchar(20),keyidx_name(name));insertintotestvalues(1,'10'),(2,'20');然后我们使用下面两条语句对执行计划进行对比测试。explainselect*fromtestwherename=20;explainselect*fromtestwherename='20';当name列为字符类型时，得到的执行计划列表如下：警告信息会明确提示：Message:Cannotuserangeaccessonindex'idx_name'duetotypeorcollat??ionconversiononfield'name'，所以这里的问题也很明显。将子查询的条件修改为字符后，整个SQL的执行效率立马好了很多。使用sql_no_cache进行测试。SQL修改前性能：+--------------------+2rowsinset(0.27sec)修改后性能：+---------------------+2rowsinset(0.00sec)然后再查看执行计划，一切正常，可以解决瓶颈问题，那些规范可以更好的逐步展开，和从提案来看，通过的概率会更高。当然，在这个基础上确实有一些补充的建议，可以定位瓶颈后再展开。优化不是一次性的。在此基础上，还发现了其他一些问题。大家可以看看这张表的表结构信息，其实可以发现一些设计上的小问题。1）表字段的字符类型基本都是varchar(255)，需要尽量避免，对存储性能影响比较大。2)使用int类型int(3)。存储4个字节，但限制范围大大缩小，更小的数值类型可以考虑user_id、status的复合索引）
