Shulou(Shulou.com)06/02 Report--

This article focuses on "the design and use of MYSQL tables". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Next, let the editor take you to learn "the design and use of MYSQL watches".

The design of a table, from a personal point of view, first depends on the business and the architecture you choose, whether the volume of business is large or small, whether the business is Internet or traditional, whether the business can be changed greatly, or more fixed-line, and so on. Of course, there may be more subdivision, from the database point of view, which kind of database you are going to use, decide whether you can divide the database into tables, partition tables, or hot and cold tables. Or use some special tricks to make your watch more refreshing. At the same time, different databases also give table design more leeway, so I have been hoping that development and DBA can be closely integrated, because most of the developers do not know the access to various databases, and some strange functions, while DBA may not have the developer's deep understanding of the business, if the combination of the two, the design of the table will be much better than the unilateral design of the table. And more worthy of consideration.

Let's talk about some of the minor troubles we've seen in MYSQL table design, and how to solve them.

1 get the data, MYSQL's table actually does not have a primary key, according to communication with developers, found that they have a very interesting idea that there is no primary key of the table will be inserted faster, because they want to insert faster, and according to their previous ORACLE experience that is the case. At that time, I explained to them that they proposed that the table of ORACLE without a primary key would generate ROWID by default on the table, so the table recorded with such information (weak business) can be designed in this way, not to mention whether it is OK in ORACLE. In MYSQL, I only asked them a question, "do you still need to query this table?" the answer is of course.

Based on their answers, I suggest that since you want to query, you need to create a primary key, or optionally, create a unique index. And explain why (the principle of MYSQL's table, as well as the underlying structure), the developer agreed and immediately added the primary key.

In fact, we have encountered such a situation before, but the angle of interpretation at that time is, rules and regulations, military regulations, or in explaining the replication of MYSQL, we must set a primary key, and so on. But the feedback given by the developer to me is not too good, which made me reflect at that time, when solving the problem, I should stand on the other side's point of view to deal with it, the other side will better accept and understand, and will also solve it with you, because it designs his own interests.

2 when participating in the design of a system, the developer sets the primary key of a table to multiple columns. According to business logic, there is no problem for him to do so. Region coding, adding customer code, and adding customer type can determine the uniqueness of customers in this table. Although this is not recommended from the point of view of MYSQL itself, the developer proposes that the business has been designed in this way. What do you want me to do? There is one field missing, which does not confirm the uniqueness of a specific customer, and the business does not intend to assign a unique code to each customer.

At this point, even if you come up with military rules, or database principles to talk to the developer, it is invalid, and he is also a victim. The key question now is how to resolve this matter, rather than being tough to create "opposites".

Solution 1 discussed with the developer whether an incremental primary key could be created according to the INT type, while our region, customer type, and customer ID could be created as a unique index, and the developer said it could be considered.

Solution 2 discusses with the developer whether it is possible to re-encode a unique value by calculating the values of three fields, using this value as the primary key, and consider the order when calculating the value, for example

Region + user type + user ID + data insertion time (in seconds)-based on the ratio of the number of users entered to time. And add the time of data insertion to the fields of the table, which seems more troublesome than the above way, but it can solve the uniqueness of the query, and there is no need to establish a unique index, and the corresponding users can be quickly located through the primary key.

In the end, the developer chose the second method. In fact, most DBA are restricted by rules and rules. Of course, this is right, and this is also considered for the normal operation of the system, but if you slightly stand on each other's point of view and deal with it, the effect may be better.

3 when developing a system, most developers have not used the MYSQL database before. in the design of the table structure, although there are some MYSQL-specific specifications mentioned earlier, they still found a large number of primary and foreign key designs in the database design, and then communicated with the developers, and found that the developers' ideas were still in the three paradigms, 3NF, and the correlation integrity between tables. And the non-repetition of the data. After communicating with the developers, the current version of MYSQL and the MYSQL operation principle of Join were explained, the developers understood, and the developers re-combed the original table design, aggregating some frequently queried data into one table, or two tables, not in 4-9 tables for JOIN in order to obtain data, but also to understand the complexity of developers after changing the design.

From the communication, we also know that programmers' ideas are mostly based on the influence of 3NF, avoiding duplicate fields in different tables, and outputting information through the association + conditions of multiple tables in the query. Compared with the Internet industry, the logic of some traditional industries will be more complex, so using MYSQL will make the program do more in the non-database layer and think more. This is why some programmers complain that MYSQL databases are not easy to use.

Therefore, when using any kind of database, the premise is to be service-centered, divergent or mixed-row thinking based on the principle of the database used, and not just die in one database, for example, if the status is updated frequently, but no matter how many changes there are in a row record or business, the value of the final change is fixed. Is it possible to use other databases (non-RDS) for fast state buffering and finally brushing the results into the database table to avoid performance problems caused by frequent updates and queries, and so on, which all need to be communicated with DBA, using mutual knowledge to achieve maximum optimization and eliminate problems in the early stages of system design.

Therefore, cooperation can be win-win, while military regulations, regulations, and so on are all within the same scope, and if the problem cannot be solved in this scope, is there a problem in this scope, or whether we can further improve our capabilities? while using various means to maintain military regulations, but also to meet the needs of business and development, it depends on the individual's ability. The more you will, the higher your level of problem solving will be. The less opposites related to you.

At this point, I believe you have a deeper understanding of "the design and use of MYSQL watches". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.