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

This article introduces the relevant knowledge of "how to optimize the table structure by MySQL". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Because the MySQL database is a Row-based database, and the database operates IO in the way of page (block), that is, if we reduce the amount of space occupied by each record, it will increase the number of rows of data that can be stored in each page, then the number of rows accessible by IO each time will also increase. On the other hand, when dealing with the same number of rows of data, the number of page that needs to be accessed is reduced, that is, the number of IO operations is reduced, which directly improves performance. In addition, because our memory is limited, increasing the number of rows of data stored in each page will increase the amount of cached data in each block of memory, and will also increase the probability of data hit in memory swap, that is, cache hit ratio.

Data type selection

The most time-consuming operation in database operations is IO processing, and most database operations spend more than 90% of their time on IO reading and writing. Therefore, reducing the amount of IO reading and writing as much as possible can greatly improve the performance of database operations.

We can't change the data that needs to be stored in the database, but we can put some thought into how the data is stored. The following optimization recommendations on field types are mainly applicable to scenarios with a large number of records and a large amount of data, because fine data type settings may increase maintenance costs, and over-optimization may also lead to other problems:

1. Digital type: do not use DOUBLE as a last resort, it is not only a matter of storage length, but also a question of accuracy. Similarly, fixed-precision decimals are not recommended to use DECIMAL, it is recommended to multiply by fixed multiples to integer storage, can greatly save storage space, and will not incur any additional maintenance costs. For the storage of integers, when the amount of data is large, it is recommended to distinguish the choice of TINYINT / INT / BIGINT, because the storage space occupied by the three is also very different, and it is recommended to add unsigned definition if you can be sure that fields with negative numbers will not be used. Of course, if the database with a small amount of data, there is no need to strictly distinguish the three integer types.

two。 Character type: do not use the TEXT data type as a last resort, and its handling determines that its performance is lower than that of the char or varchar type. For fixed-length fields, it is recommended to use CHAR type. For variable-length fields, use VARCHAR as far as possible, and only set the appropriate maximum length, rather than arbitrarily limit a large maximum length, because different length ranges, MySQL will have different storage processing.

3. Time type: try to use the TIMESTAMP type because its storage space is only half that of the DATETIME type. For data types that only need to be accurate to a certain day, it is recommended to use the DATE type because its storage space is only 3 bytes, which is less than TIMESTAMP. It is not recommended to store a unix timestamp value through the INT class type class, as this is too unintuitive and can cause unnecessary hassle to maintenance without any benefits.

4.ENUM & SET: for status fields, you can try to use ENUM to store them, because the storage space can be greatly reduced, and even if you need to add new types, as long as you add them at the end, you do not need to rebuild the table data to modify the structure. What if it is storing predefined attribute data? You can try using the SET type, even if there are multiple properties, you can do it easily, and you can also save a lot of storage space.

5.LOB type: strongly opposes storing LOB type data in the database, although the database provides such a function, but this is not what he is good at, we should let the right tool do what he is good at in order to make the best use of it. Storing LOB data in a database is like asking a marketing professional who learned a little about Java in school many years ago to write Java code.

Character coding

The character set directly determines how the data is stored and encoded in MySQL. Because the same content is represented by different character sets, the space occupied will be quite different, so by using the appropriate character set, we can help us to reduce the amount of data as much as possible, and then reduce the number of IO operations.

1. For what can be represented by pure Latin characters, there is no need to choose character encodings other than latin1, because this will save a lot of storage space.

two。 If we can be sure that there is no need to store multiple languages, there is no need to use UTF8 or other UNICODE character types, which results in a lot of waste of storage space.

The data type of 3.MySQL can be accurate to fields, so when we need to store multi-byte data in a large database, we can greatly reduce the amount of data storage by using different data types for different fields of different tables, thus reducing the number of IO operations and improving cache hit rates.

Appropriate split

Sometimes we may want to map a complete object to a database table, which is good for application development, but sometimes it can cause big performance problems.

When there is a large field similar to TEXT or a large VARCHAR type in our table, if most of us do not need this field when accessing the table, we should not hesitate to split it into separate tables to reduce the storage space occupied by commonly used data. One of the obvious benefits of this is that the number of pieces of data that can be stored in each block can be greatly increased, both reducing the number of physical IO and greatly increasing the cache hit ratio in memory.

The optimization of the above points is to reduce the storage space of each record, so that more records can be stored in each database, so as to reduce the number of IO operations and improve the cache hit rate. The following optimization suggestion may not be understood by many developers because it is a typical anti-paradigm design and runs counter to the goals of the above optimization suggestions.

Moderate redundancy

Why should we be redundant? Doesn't this increase the size of each piece of data and reduce the number of records that can be stored in each block?

Indeed, this increases the size of each record and reduces the number of data that can be stored in each record, but in some scenarios we still have to do this:

1. Small independent fields that are frequently referenced and can only be obtained through Join 2 (or more) large tables.

two。 In such a scenario, because each Join is just to get the value of a small field, and the record of Join is large, it will result in a large number of unnecessary IO, which can be optimized by exchanging space for time. However, redundancy also needs to ensure that the consistency of the data is not broken and that redundant fields are updated at the same time.

Use NOT NULL as much as possible

The type of NULL is special, and SQL is difficult to optimize. Although there are differences between the MySQL NULL type and Oracle's NULL, it will enter the index, but if it is a combined index, then the field of this NULL type will greatly affect the efficiency of the entire index. In addition, the processing of NULL in the index is also special and takes up extra storage space.

Many people think that NULL will save some space, so try to let NULL to achieve the goal of saving IO, but most of the time this will be counterproductive. Although there may be some savings in space, it brings a lot of other optimization problems. Instead of saving IO, it increases the I / O of SQL. So try to make sure that the DEFAULT value is not NULL, which is also a good habit of table structure design optimization.

This is the end of the content of "how to optimize the table structure by MySQL". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.