Shulou(Shulou.com)05/31 Report--

This article mainly introduces "how to optimize the Explain index". In the daily operation, I believe many people have doubts about how to optimize the Explain index. The editor consulted all kinds of data and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the doubts of "how to optimize the Explain index". Next, please follow the editor to study!

Preview of the content of this article:

1. Background introduction of the project

1.1 Table structure involved

1.2 clear query requirements

two。 Index problem confirmation and tuning

2.1 problem discovery

2.2 problem verification

2.3 Index optimization

Background introduction of Part1 project

Students who have read the previous article will remember that when describing the principle of indexing and the actual case, we gave an example of the main transaction table in Ali distributed transactions.

Coincidentally, some time ago, due to business requirements, we developed a long transaction consistency engine to deal with the upstream and downstream consistency of billing data in the advertising system, which also involves a table like this.

However, a recent iterative code walk found that there was a problem with the indexing.

0.1 Table structure involved

As shown in the figure above, the field and index structure of the database looks like this.

The tx_id globally unique increment field is the primary key.

The status field identifies the current status of the record, which is used to distinguish between records that have not been successfully executed

Create time and update fields to assist in time falloff sequence fetching execution during asynchronous recovery.

If you are interested in how each field works, you can browse the article "distributed transactions from introduction to abandonment (2)-- detailing the principle and design of DT engine consistency".

0.2 make clear the query request

The purpose of this table is to retrieve those records that have not reached the final state and perform abnormal recovery.

In order to avoid the records being processed by the system, limit the time to 1 minute ago.

In order to be as efficient as possible, the time range is limited to the first 10 minutes, and longer failure records are handed over to lower-frequency scheduled tasks.

In order to achieve the time attenuation after the failure of asynchronous processing, modify is also used to avoid the backlog of newly generated data due to problems with old data processing.

In fact, the demand is relatively simple: regularly capture the first 1 minute to the first 10 minutes, and the state belongs to the records of certain states, namely:

Select * from activity_t where status in (1BI 2) and gmt_modified > = '2021-01-01 xx:xx:10' and gmt_modified='2021-01-01 xx:xx:10' and gmt_modified

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.