After a month of intense competition, Tencent Cloud intelligent database manager DBbrain conducted health inspection and scoring for each participating user instance. Congratulations to the following 30 contestants who entered the final!

Some friends asked: although I did not enter the final, but the focus of participation, for the preliminary title I still have a lot of doubts, in the end should be how to tune?

So today, in addition to the announcement of the results, data gentleman also brought you the analysis of the preliminary competition and outstanding players to share their problem-solving ideas.

I. Setting of the questions

First of all, let’s review the competition rules: Tencent Cloud simulated business access on the cloud server, resulting in failures, anomalies, hidden dangers and other phenomena on the MySQL cloud database of each group. Participants need to optimize the business code or database according to the cloud-based monitoring and log information without reducing the business access. DBbrain, the intelligent database manager of Tencent Cloud, will conduct health inspection and score for each instance of participating users. After the competition, promotion/ranking will be determined according to DBbrain’s health score. If the scores are the same, the competitor with the shortest time wins. The optimization scheme of the problem analysis in this paper is given by DBbrain.


There were two questions in the competition, one about update statement and the other about query statement.

Let’s start with the title:

1. Library table structure

1. The Order table

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=utf8Copy the code

2. The Order_item table

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=utf8Copy the code

Second, to optimize the SQL

1. Update statements

1.1) SQL statements

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

1.2) Execution time: The execution time is very long, and even the temporary space is full

1.3) Implementation plan

2. Query statements

2.1) SQL statements

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

2.2) Execution time: 2.6 seconds

2.3) Execute the plan

Second, the problem analysis

Update statement

1. Optimize your thinking

The first thing that came to mind for this common update statement was to take advantage of mysql’s optimization capabilities on SemiJoin. Many competitors change it to the following form, and the performance becomes worse. In this way, semijoin fails and the aggregation query dependent subquery is executed multiple times.

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

The second is to determine whether it is necessary to rewrite it into JOIN according to the size of the actual data volume, so as to change the driving order, but the premise is to estimate the performance cost brought by the subquery aggregation. This is where we can minimize this overhead with appropriate indexes. The first thing to notice when creating an index is that the column type in order_item is defined as vARCHAR, but the condition value in the SQL statement is an integer, and the type mismatch causes the condition to be implicitly converted. The second best way to create a composite index is to “Using index for group-by”.

2. DBbrain optimization scheme

2.1) Add indexes

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

2.2) SQL rewriting

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();Copy the code

3. Optimization effect

3.1) Execution time: milliseconds

3.2) Refer to the execution plan

Second, query statement

1. Optimize your thinking

Participants reported that there were big problems with the SQL data model, and they felt they didn’t know how to start. But the SQL statement comes from a real user’s business scenario. The developer had his reasons for establishing this data model, and asked the DBA for help in the hope that we could achieve the results quickly with the minimum cost. This is where the DBA’s on-the-spot ability to adapt to business scenarios becomes important. There are only two states of status, which can be easily resolved by a single unin all. In addition, you need to take advantage of the sorting capabilities of indexes.

2. DBbrain optimization scheme

2.1) Add indexes

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

2.2) SQL rewriting

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, 20Copy the code

3. Optimization effect

3.1) Execution time: milliseconds

3.2) Refer to the execution plan

As for this question, some contestants gave feedback that it was “a little difficult”, so I give you a key point: The preliminary questions are extracted from the actual business problems encountered by DBbrain in providing intelligent database optimization services for customers on the cloud, and DBbrain can give real-time optimization suggestions. With the support of DBbrain in daily work, DBAs can quickly solve database problems. If your company is facing similar problems in the future, I suggest you use DBbrain to help your boss

It is worth mentioning that DBbrain can provide 7*24 hours unattended database guarantee for customers on and off the cloud. Through the combination of AI technology and DBA experience base, DBbrain can provide daily health inspection and other database services for database instances. The user value of DBbrain is also obvious. It can not only help DBA efficiently manage database and solve difficult problems in database, but also empower enterprises with technology. At the same time, it can also combine the effective feedback of customers to jointly build a perfect ecosystem of database.

Welcome to click “Tencent Cloud database” public account “one-click management” experience, in order to let DBbrain provide you with better service, you can feedback suggestions oh ~

Other exciting benefits: Tencent cloud will carry out a monthly “DBbrain diagnosis day” activities, select the actual business will encounter database operation and maintenance problems as examples, invited senior database experts combined with DBbrain intelligent diagnosis business scene optimization analysis, welcome you to provide their own problems, goose factory experts online all day ~

Upcoming events

If you didn’t enter the final or didn’t have time to participate in the competition, don’t be upset, double 11 crazy Hand cutting festival is coming, darling, we can also try a few jin database, quietly tell you, the operation partner of data jun is almost determined by teeth discount, buy is to earn!

Click here to enjoy MySQL Offers