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

This article will explain in detail for you how to give your own MySQL database to lose weight, the content of the article is of high quality, so the editor will share it with you for reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

In database optimization, making the data as small as possible and making the space occupied by the table on the hard disk as small as possible is one of the most commonly used and effective means. Because shrinking data can relatively improve the reading and writing speed of the hard disk, and the content processing of small and medium-sized tables in the query process takes up less system resources. Similarly, if you set an index on a smaller column, the index will take up less resources. So how can database administrators give their own data to lose weight? The following editor to explain how the database administrator to their own MySQL database to lose weight?

How to make MySQL database lose weight for database administrator

Suggestion 1: null values do not necessarily take up space

Here, the author first gives you literacy. Some database administrators think that null values will not occupy system resources, in fact, this is a misunderstanding. When designing a database, they don't like to set the property of the field to NOTNULL. And let users enter data according to their own needs. The author believes that this practice is disadvantageous to the performance of the database.

The author's opinion is that if possible, try to set the column to NOTNULL, that is, no null values are allowed. By doing so, we can speed up the speed of subsequent processing, and at the same time, from the point of view of data storage, we can save one bit per column, so as to achieve the purpose of data weight loss. In practice, if some situations do not require the user to enter data, the default field can also be used to achieve the purpose of non-empty. For example, in the payroll system, the user's working life can be set to 0 by default instead of blank. Of course, if NULL is really needed, there is no way. But as a database engineer, you should avoid using null values as much as possible.

Recommendation 2: use data types as small as possible

The size of the data type also affects the size of the underlying table. For example, for MEDIUMINT and INT data types, they can be used to save integer data, but they can be saved with different precision. But from the point of view of storing data, the former needs about 25% less storage space than the latter. For this reason, do not use INT if you can use MEDIUMINT.

In addition, when defining the length of the data, it should be as short as possible when meeting the requirements. For example, there is an employee code field in the salary assessment system. If the employee code has been determined, it is made up of five characters. Then when you define a field, you only need to define a length of 5 characters. This can not only reduce the storage space, but also play a certain function of data proofreading. When the length of the encoding entered by the user exceeds 5 digits, the data cannot be saved.

Although there are many data types to choose from to save a certain data, you can also define a relatively large number of characters. However, choosing the data type as small as possible can help reduce the data storage space and achieve the purpose of data weight loss. So as to further improve the performance of the database.

How to make MySQL database lose weight for database administrator

Recommendation 3: relationship between index and data table size

The author mentioned at the beginning of the article that if you set an index for smaller columns, then the index will also take up less resources. It can be seen that the index is also closely related to the size of the data table. Setting the right index in the right place and at the right time can also achieve the purpose of losing weight on the data.

As usual, there may be multiple indexes per data table, but there is often only one primary index. For this reason, the primary index of each table should be considered as short and concise as possible. This can help the database identify faster.

For example, try to index the prefix. If you have a table now, you need to set an index on a column. One feature of this column is that it has a unique prefix on the first few characters. If this is the case, it would be better to index this prefix tightly instead of all of it. In the MySQL database, it is supported to create an index on the leftmost part of a character column. That is to say, the database will split a field into two parts according to certain rules. If the first part of the data after the split can be kept unique, then you only need to set the index on the previous part, not on the data of the entire field. This can undoubtedly reduce the resources occupied by the index and achieve the purpose of losing weight. Shorter indexes can provide faster query speed. Because they take up less hard disk space, and they will keep more access in the index cache. So as to reduce the search times of the hard disk and improve the efficiency of the query.

Finally, it is important to note that indexes cannot be abused. It is true that using indexes can improve the processing power of data, but indexes also bring additional overhead. Only when this benefit outweighs the overhead, the use of indexes can improve database performance. Otherwise, it will have the opposite effect. If a table needs to be stored quickly, if too many indexes are set on the table, the index will have a side effect. To this end, the author suggests that if you access a table mainly by searching for a combination of columns, it is best to set only one index on them. Of course, this index section should be the most commonly used column in daily work. As a last resort, if you need to use multiple indexes, it is best to use columns with more copies to get better index compression. As a result, the increased resource consumption due to the use of multiple indexes is reduced.

Suggestion 4: you can't save where you need "fullness".

A woman should be thin where she is thin and plump where she should be. In fact, the same is true of databases. Where you can save hard disk space, save it. If you can't save something, you can't streamline it in order to lose weight. Sometimes this can be counterproductive.

The author takes Varchar as an example. For example, in the MyISAM tag, if there are no variable-length columns, it is best to use a fixed-size data type. Although fixed-length data types are used, a certain amount of storage space is often wasted. Because if the data entered by the user is insufficient and the fixed length is used, the data will still be stored according to this fixed length. But in this case, if you can use a fixed length, you still have to use a fixed length. Because in this case, although a certain amount of hard disk space will be wasted, it can improve the query speed of data.

It can be seen that losing weight on data can not improve the performance of the database in any case. This is like saving money and increasing revenue, and the savings should be saved on the cutting edge. Otherwise, you will not be able to save money, but you will also lift a stone and shoot yourself in the foot. Popularly speaking, it should be thin where it should be thin and plump where it should be plump. Remember this sentence, that's right.

Recommendation 5: divide the table to achieve the purpose of losing weight

When an ant is moving food, if a piece of food is too large to move, the ant may divide the piece of food until it can move it. This is the principle of cake sharing. In fact, this phenomenon is often seen in daily work. If we have a database table, if there are many records in it, then the allowable speed of the table will be very slow. In this case, the table can be divided into multiple workbooks according to certain rules. For example, there is a copy of the attendance information of employees in the enterprise. When querying, sorting and counting this table, the waiting time is very long. At this point, it can be divided into different workbooks according to the department, and then related data analysis can be carried out. At this time, although the workload will be a little larger, but its processing speed will be much faster.

According to this principle, it is useful to split a large table that is often scanned into two or more representations during database optimization. For example, in daily work, the author now has a data table in dynamic format, and when this data is a scan table, it will be used to find out the smaller static format of the relevant rows.

Through the splitting of this table, a large cake can be divided into several small cakes to facilitate the statistics and analysis of the follow-up data. Of course, the effect is directly related to the rules of the split. About how to split the table in order to achieve the desired results, this is another big topic. Due to the limited space here, the author does not explain too much. Perhaps in the follow-up article, the author will unfold with this proposition and give you a detailed explanation.

On how to give their own MySQL database to lose weight to share here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.