Let Mysql database run faster for data loss _mysql

Source: Internet
Author: User

In the database optimization work, makes the data as small as possible, causes the table to occupy on the hard disk space as small as possible, this is the most commonly used, is one of most effective means. Because the reduction of data, relatively can improve the hard disk read and write speed, and in the query process of small and medium table content processing when the system resources occupied less. Similarly, if you set an index on a relatively small column, the index will have less resources to occupy. So how do database administrators lose weight on their data? The author has the following several suggestions.

Recommendation I: null value does not necessarily occupy space

Here I first give you a literacy. Some database administrators think that null values do not occupy system resources, in fact, this is a mistaken understanding. They do not like to set the properties of a field to not NULL when designing a database. And let the user input data according to their own needs. The author thinks, this kind of practice is disadvantageous to the performance of the database.

The author's opinion is that if possible, try to set the column to not NULL, that is, null values are not allowed. In doing so, you can speed up the processing of the next process, and from the data storage can also make each column save a bit, so as to achieve the goal of data loss. In practice, you can also use the default fields to achieve non-null purposes if there are situations where you do not need to enter data for your users. As in the payroll system, you can set the user's working life by default to 0 instead of blank. Of course, if you do need null, there is no way. But as a database engineer, try to avoid using null values.

Recommendation two: Use as small a data type as possible

The size of the data type also affects the size of the underlying table. for mediumint and int two data types, it can be used to save integer data, but it can be stored in a different precision. But from the point of view of storing data, the storage space required by the former is about 25% less than that of the latter. To do this, do not use int when you can use Mediumint.

In addition, when defining the length of the data, in the case of satisfying the requirements, it should be as short as possible. If there are employees in the payroll system now coding a field. If the enterprise employee code has been identified, there are five-digit characters. So when you define a field, you only need to define the length of 5 characters. This can not only reduce storage space, but also can play a certain function of data proofing. When the user enters an encoding length of more than 5 digits, the data cannot be saved.

Although there are many types of data you can choose to save, you can also define a larger number of character digits. But choosing as small a data type as possible can help reduce the data storage space and achieve the goal of data losing weight. To further improve the performance of the database.

Recommendation three: The relationship between index and datasheet size

The author has said at the beginning of the article that if you set an index on a relatively small column, the index will also take up less resources. Visible, the index is also closely related to the size of the datasheet. Setting the right index at the right place and at the right time can also achieve the goal of losing weight on the data.

In general, each data table may have multiple indexes, but the primary index is often one. This should be considered as short as possible for each table's primary index. This helps the database to be recognized faster.

Try to index the prefix as much as possible. If you have a table now, you need to set an index on a column. This column has a feature that has a unique prefix on the first few characters. If this is the case, it would be better to tightly index the prefix, not all. In the MySQL database, you support the creation of an index on the leftmost part of a character column. This means that the database splits a field into two parts, based on certain rules. If the data in the previous part of the split is unique, you only need to set the index on the previous section without having to index the entire field's data. This will undoubtedly reduce the resources occupied by the index to achieve the goal of weight loss. Shorter indexes to provide faster query speed. Because they occupy less hard disk space, they will save more access in the index cache. Thus reduce the number of hard disk search, improve the efficiency of the query.

The last thing to note is that indexes cannot be abused. Using an index does improve the processing power of your data, but indexing can also incur additional overhead. Using an index only improves the performance of the database only if the benefit is greater than the cost. Otherwise, it would have the opposite effect. If a table needs to be stored quickly, indexes can have side effects if you set too many indexes on the table. The author suggests that if a table is accessed primarily through a combination of search columns, it is best to set them only one index. Of course, this index section should be the most commonly used column in daily work. If you need to use more than one index, it's best to get better index compression with more replicas using columns. This reduces the resource consumption that is increased by using multiple indexes.

Recommendation IV: Where the need for "fullness" is still not able to save

A woman, the thin place to be thin, the fullness of the place to be plump. In fact, the same is true of databases. Where you can save hard disk space, you need to save. Where there is no savings, it cannot be streamlined to lose weight. Sometimes this can backfire.

The author takes varchar as an example. If you do not have any variable-length columns in the MyISAM label, it is best to use a fixed-size data type. Although the use of fixed-length data types, often waste a certain amount of storage space. Because if the user enters the data is insufficient, uses the fixed length, the data storage will still press this fixed length to store. In this case, however, you can use fixed length or fixed lengths. Because in this case, although it will waste a certain amount of hard disk space, but can improve the data query speed.

Visible, not in any case to lose weight on the data can improve the performance of the database. It's like saving money on open source, which saves on the edge. Otherwise, not only will not be able to savings, but also to the foot. The popular saying, is that the thin place to thin, the fullness of the place to plump. Remember this sentence, that's right.

Recommendation five: Divide the table to achieve the goal of weight loss

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 moves. This is the principle of splitting the cake. In fact, this phenomenon is often common in daily work. If we have a database table, if there are a lot of records, then the table can be very slow to allow. In this case, you can divide the table into multiple workbooks according to certain rules. If there is a company employee's attendance information now. When querying, sorting, and counting this table, the waiting time is very long. You can then split it into different workbooks according to the department, and then analyze the data for them. At this point, although the workload will be a bit larger, but its processing speed will be much faster.

Based on this principle, a large table that is frequently scanned can be divided into 2 or 2 representations that are very useful in database optimization. As in daily work, the author now has a dynamic format of the data table, and this data is using a scan table, it will use this to find the relevant rows of relatively small static format table.

By splitting the table, a large cake can be divided into small pieces of cake to facilitate the statistics and analysis of subsequent data. Of course this effect is good or bad, directly related to this split rule. about how to split the table can achieve the desired effect, this is a relatively large topic. Due to the limited space here, I do not do too much explanation. Perhaps in the follow-up article, the author will be carried out with this proposition, to give you a detailed explanation.

Original source: http://publish.itpub.net/a2011/0302/1161/000001161945.shtml

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.