Xin Xing makes mysql run faster. First, it optimizes the direction and database modeling. Xin Xing makes mysql

Xin Xing makes mysql run faster. First, it optimizes the direction and database modeling. Xin Xing makes mysql

I plan to write a set of bibliography recently, that is, about mysql optimization. First, I will write it on my blog and then sort it into a pdf document. Of course, I look forward to your attention. The optimization of mysql is a big topic, and there are a lot of things to be optimized. You can start with these things.

The first is the hardware level, including selecting the appropriate operating system, selecting the right hardware, and then the source code level. However, although mysql is open-source, there are many companies that can modify its source code, but there are not so many, but we can choose a more suitable compiler to recompile its source code, and then design the table, that is, database modeling.

Next, we can consider using other technologies, such as read/write splitting and table sharding, or cluster technology. This requires us to build an overall architecture. For specific content, you can set indexes, optimize SQL query statements, fix regular tables, and use stored procedures.

Here, let's talk about the database modeling part. This part is often related to the business logic. Generally, new users prefer the third or BC paradigm. If it is more perfect, the fifth paradigm may be suspended, as a matter of fact, the experts also come here step by step, so let's first introduce what is the third paradigm.

The first paradigm is the atomic constraint on attributes. That is to say, the columns in a table are atomic and cannot be split again. The constraint is really low, as long as the database is a relational database, it will automatically satisfy the first paradigm. Which of the following may not satisfy the first paradigm? If we store a set and an array, this does not meet the first paradigm. However, this is not possible for relational databases. We cannot store an array in it. Note that a set is stored, not an element in the set.

The second paradigm is that attributes fully depend on the primary key. For example, if we create a People table, we can write a field that is the ID card number, because each person's ID card number is not repeated, the information of this person is also determined by the unique ID card number of this person. This can be understood as a primary key. The ID card number here is the primary key, but the name is not the primary key, there are still many duplicate names. It aims to prevent data duplication, so we use a primary key to uniquely identify a record. Note that the primary key is not necessarily a field. For example, if two pieces of information are used to determine a record, for example, the file name and Suffix in a folder, can be used to determine a unique file name, if we store information separately, these two fields constitute their primary keys. It is worth noting that we usually use a digital auto-increment id as the primary key, but it is not necessary.

The third paradigm is more demanding. It requires no redundant data in the table. Its specification means that any non-primary key attribute must depend on the primary key, however, it cannot be passed because it depends on the primary key. This is also an example of learning the third paradigm. It is so classic that I may never forget it. For example, if I have created a table, its field information is as follows, however, it will generate redundant information, that is, the address of the student's system. This attribute is also uniquely determined by the student ID, but it is not directly dependent and it transmits dependencies, it depends on the system name rather than the student's student ID. Therefore, we will find that different student IDs will store many identical system names. This is called redundant information.

So how can we modify it? The answer is table sharding. Note that we must follow the second paradigm. we can add a system number field, that is, the first table information (student ID, student name, Department number), second table (Department number, Department name, Department address), so that we will not store redundant information, this is the effect of the third paradigm.

In addition to the third paradigm, there is also a very important one, called the BC paradigm, that is, the famous bus-cod paradigm. What is its requirement? It is a primary key requirement, that is, the primary code. It requires that no one of the primary codes can uniquely identify the primary code. For example, if I combine the student ID card number and student ID number into a primary key, it does not violate the third paradigm, but it violates the BC paradigm. Why? In a school, the IDs and student IDs of any two students are different. That is to say, we can use student IDs as the primary key or ID number as the primary key, but we can use these two IDs as the primary key, no.

I don't know if the readers are dizzy. The general idea is like this. In fact, these paradigms are made by people and it is not hard to understand. I have not introduced many logical conformances and professional terms here, it can be described in a common language, but also in the hope of better understanding. If you have any questions, please leave a message below.