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

2020-01-02 13:31:34

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

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

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

一、赛题设置

首先我们来回顾一下比赛规则：腾讯云在云服务器上模拟业务访问，造成每组的MySQL云数据库上出现故障、异常、隐患等现象，参赛者需根据云基础监控和日志信息，在不降低业务访问量的情况下，对业务代码或数据库进行优化。对每个参赛用户的实例进行健康巡检和打分，比赛结束后，按照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` setcreate_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.idORDER  BY o.status ASC,          i.update_timeDESCLIMIT  , 20

2.2）执行时间：2.6秒

2.3）执行计划

二、赛题解析

一、更新语句

1. 优化思路

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

update `order` setcreate_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 =           ORDER  BY i.update_time DESC          LIMIT  , 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  , 20)        ) tmp       INNER JOIN `order` o ON tmp.id = o.id       INNER JOIN order_item i ON tmp.item_id =i.idORDER  BY o.status ASC,          i.update_time DESCLIMIT  , 20