Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

What are the requirements for MySQL to build tables?

2025-03-01 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

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

This article mainly introduces "what are the MySQL table building requirements". In the daily operation, I believe many people have doubts about the MySQL table building requirements. The editor consulted all kinds of data and sorted out simple and easy-to-use methods of operation. I hope it will be helpful for you to answer the doubts about "what are the MySQL table building requirements?" Next, please follow the editor to study!

Yesterday, I received a demand email from a business classmate. Generally, some complicated business students will send us an email to inform us that we need to evaluate it before delivering it. After reading the email, I found that this requirement seems to be a little awkward. It basically means to create a table in the environment of middleware. The table structure is as follows:

CREATE TABLE `app_loading_ info` (`id` int (11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'self-added ID', `pid` bigint (20) NOT NULL DEFAULT' 0' COMMENT, `appid` int (11) NOT NULL DEFAULT'0' COMMENT 'APPID', `username` varchar (64) NOT NULL DEFAULT' COMMENT 'name, `card`varchar (20) NOT NULL DEFAULT', `ai`varchar (40) NOT NULL DEFAULT', `state`int (11) NOT NULL DEFAULT'0', `ctime`datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'creation time' `mtime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'update time', PRIMARY KEY (`id`), KEY `idx_ pid` (`pid`), KEY `idx_ state` (`state`) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

According to ID sharding, the basic logic is as follows:

Every day, we will filter the user data to complete the processing, reprocess it, and modify one of the user's flag bits after the processing is completed. There are mainly several steps:

1) extract state=0 data according to state status (incomplete processing data)

2) batch extraction according to the interval of id in the program

3) after the extraction, modify state to state=1, and combine it according to pid,state

After reading this preliminary design, I always felt that something was wrong, so I found my business classmates to communicate face to face.

First of all, for the definition of this table, business students say that it belongs to the status table, which means that each user in the table has a unique status value, and the amount of data stored in this table will become larger and larger.

Secondly, the unprocessed data is extracted according to the state status field. The target environment is a cluster environment, in which the cluster is sliced according to id, but the query conditions are potentially problematic according to state.

For example, the use of self-increasing id in the business layer may not be unique in the sharding environment. As shown in the figure above, there may be at most N pieces of the same data in id=1 (N is the number of shards), so the business requirements cannot be met.

In addition, to query data according to state=0, the complexity of this query is high, which means that state=0 needs to traverse all shards, and each shard will filter the data through the index conditions of state=0. In terms of use, this is also a potential impact of sub-database and sub-table, and this is not recommended.

There is also the design of field id, which is unreasonable according to the way the status table is used. In some special scenarios, we will use id+ to combine other business attribute fields with primary keys, which is obviously not the case here.

If the primary key mode is used to remove the id field, it seems to violate the original intention of the business to extract intervals according to id, and this requirement is contradictory to detail.

In the most reluctant way, it is recommended to deal with it within a specified time range, such as between 8: 00 and 9: 00, then do not do anything like heartbeat or service detection outside this time range, which is basically acceptable to the business side. but in any case, this is not an optimal solution, and the use of the index is really contrary to the original intention of using middleware services.

After further communication, we excavated the requirements again. As for how to deal with the table data in it, business students said that in fact, if the data in the table needs to be cleaned up after a long time, so according to this model, this requirement is basically clear, which is quite different from the initial demand.

In fact, there is a big turning point in the direction of demand here. It is better for this table to use the log table mode according to the current demand. For example, the data in the table is stored according to the following list, and the date table is stored as a dimension.

If you need to deal with the unfinished data according to the state 1 mode, the entire complexity only performs an index scan on the table on a certain day, which will not have an associated impact on other tables, but if you store it in a single table according to the date, you will have more freedom in the whole thing. Query according to the dimension of state or pid, the effect is acceptable.

Therefore, after discussion and evaluation, it is not necessary to deal with this kind of business in the middleware environment. The service based on middleware undertakes the core business and is sensitive to the impact of performance and load. If it is implemented in a muddle, it will bring some other hidden dangers.

Through such a seemingly simple communication and mining of requirements, different solutions are finally produced, which are relatively satisfactory for the business side, at least beyond their basic needs expectations. and many details of the work does not require more human participation and later discussion, which greatly reduces the marginal cost of communication.

At this point, the study on "what is the need for MySQL to build a table" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report