800人参赛只有1人全部答对，题目却是现实中发生过的故障？

喜大普奔，腾讯云首届数据库诊断赛初赛圆满结束啦~经过一个月紧锣密鼓的比赛，腾讯云智能数据库管家DBbrain对每个参赛用户的实例进行了健康巡检和打分，恭喜以下30名选手进入决赛！

有朋友问了：虽然我没有进入决赛，但是重在参与，对于初赛的题目我还有很多疑惑，到底应该怎么调优呢？

那么今天除了公布成绩，数据君也为大家带来了初赛的赛题解析和优秀选手的解题思路分享。

一、赛题设置

首先我们来回顾一下比赛规则：腾讯云在云服务器上模拟业务访问，造成每组的MySQL云数据库上出现故障、异常、隐患等现象，参赛者需根据云基础监控和日志信息，在不降低业务访问量的情况下，对业务代码或数据库进行优化。腾讯云智能数据库管家DBbrain对每个参赛用户的实例进行健康巡检和打分，比赛结束后，按照DBbrain的健康打分确定晋级/排名。如分数相同的，用时最短的参赛者获胜。本文的赛题解析中的优化方案，由DBbrain给出。

比赛共设置了两道题目，分别关于更新语句和查询语句。

先来看一下题目：

一、库表结构

1. Order表

CREATE TABLE `order` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) NOT NULL,
  `creator` varchar(24) NOT NULL,
  `price` varchar(64) NOT NULL,
  `create_time` timestamp NOT NULL DEFAULTCURRENT_TIMESTAMP,
  `status` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

2. Order_item表

CREATE TABLE `order_item` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) NOT NULL,
  `parent` bigint(20) NOT NULL,
  `status` int(11) NOT NULL,
  `type` varchar(12) NOT NULL DEFAULT '0',
  `quantity` int(11) NOT NULL DEFAULT '1',
  `update_time` timestamp NOT NULL DEFAULTCURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

二、待优化的SQL

1. 更新语句

1.1）SQL语句

update `order` set
create_time = now()
where id in (
    select parent from order_itemwhere type = 2
)

1.2）执行时间：执行时间很长，甚至临时空间满

1.3）执行计划

2. 查询语句

2.1）SQL语句

SELECT *
FROM   `order` o
       INNER JOIN order_itemi ON i.parent = o.id
ORDER  BY o.status ASC,
          i.update_timeDESC
LIMIT  0, 20

2.2）执行时间：2.6秒

2.3）执行计划

二、赛题解析

一、更新语句

1. 优化思路

对这一个常见的更新语句，我们首先想到的是利用好mysql在semijoin上的优化能力。不少参赛者将其改成如下形式，性能会变得更差。这种方式导致semijoin失效，聚合查询dependent subquery被执行多次。

update `order` set
create_time = now()
where id in (
    select distinct(parent) from order_item where type = 2
)

其次是要根据实际数据量的大小判断是否有必要将其改写成join，以便更改驱动顺序，但前提是要估计好子查询聚合带来的性能开销。在这里我们可以利用好合适的索引将这一开销减小到最小。创建索引时首先要注意到order_item表中字段type的定义为varchar，但是SQL语句中的条件值却是整数，类型不匹配会导致该条件隐式转换；其次最好创建组合索引，以便 "Using index for group-by"。

2. DBbrain优化方案

2.1）增加索引

alter table `order` add index idx_1(type,parent);

2.2）SQL改写

update `order` o inner join (
   select type, parent from `order_item` where type = '2' group by type, parent
) i on o.id = i.parent set create_time = now();

3. 优化效果

3.1）执行时间：毫秒级

3.2）参考执行计划

二、查询语句

1. 优化思路

参赛者反馈该SQL的数据模型存在很大问题，有无从下手的感觉。但是该SQL语句来自一个实际用户的业务场景。开发同学有他建立这一数据模型的理由，找DBA帮忙也是希望我们能以最小代价快速的出效果。这个时候DBA的现场结合业务场景应变能力就很重要。status只有两种状态，通过一个unin all就可以将混合排序简单化解。除此之外，需要利用好索引的排序能力。

2. DBbrain优化方案

2.1）增加索引

alter table order_item add index `item_idx_1` (`update_time`,`parent`);

2.2）SQL改写

SELECT o.*,i.*
FROM   (
         (SELECT o.id,
            i.id item_id
       FROM   `order` o
            INNER JOIN order_item i
                     ON i.parent =o.id
          WHERE  o.status = 0
          ORDER  BY i.update_time DESC
          LIMIT  0, 20)
          UNION ALL
          (SELECT o.id,
             i.id item_id
          FROM   `order` o
            INNER JOIN order_item i
                    ON i.parent =o.id
          WHERE  o.status = 1
          ORDER  BY i.update_time DESC
          LIMIT  0, 20)
        ) tmp
       INNER JOIN `order` o ON Domain Premium: tmp.id = o.id
       INNER JOIN order_item i ON tmp.item_id =i.id
ORDER  BY o.status ASC,
          i.update_time DESC
LIMIT  0, 20

3. 优化效果

3.1）执行时间：毫秒级

3.2）参考执行计划

对于本次赛题有选手反馈觉得“有点难”，数据君给大家划个重点：初赛题目均是从DBbrain为云上客户提供数据库智能优化服务中遇到的实际业务问题提炼出来的，而且DBbrain能够实时给出优化建议，在日常工作中有了DBbrain的加持，DBA可以快速解决数据库问题。如果未来公司业务也遇到了类似问题，建议大家使用DBbrain来为老板分忧~

值得一提的是，DBbrain可以为云上云下客户提供7*24小时无人值守的数据库保障，通过AI技术和DBA经验库的结合能为数据库实例提供日常健康巡检等多项数据库服务。DBbrain的用户价值也是显而易见的，它不仅能够帮助DBA高效的管理数据库和解决数据库疑难杂症，为企业进行技术赋能，同时也能够结合客户的有效反馈，共同构建数据库完善的生态体系。

欢迎大家点击“腾讯云数据库”公众号的“一键管理”进行体验，为了让DBbrain为您提供更好的服务，可以反馈使用建议哦~

另有激动人心的福利放送：腾讯云将每月开展一次“DBbrain诊断日”活动，精选实际业务中会遇到的数据库运维难题为例题，特邀资深数据库专家结合DBbrain的智能诊断进行业务场景优化解析，欢迎大家后台提供自己遇到的难题，鹅厂专家全天online~