MySQL detailed (------------) Table structure optimization

Many people have the database design paradigm as the database table structure of the "bible", that as long as the requirements of this paradigm design, you can make the design of the table structure is sufficient to optimize, not only to ensure excellent performance and also meet the requirements of scalability. As everyone knows, the 3 paradigm of database design, which was served as the Bible by N years ago, has not been fully applied. This class organized some of the more common database table structure design optimization techniques, hope to be useful to everyone.

Since the MySQL database is a row-based database, and the database operates IO as a page (block), that is, if the amount of space that each of our records consumes decreases, the number of rows of data that can be stored in each page increases, so each IO The number of rows that can be accessed is also increased. Conversely, with the same number of rows of data, the page that needs to be accessed is reduced, that is, the number of IO operations decreases, directly improving performance. In addition, because our memory is limited, increasing the number of rows of data stored in each page is equal to increasing the amount of cached data per block of memory, while also increasing the chance of the memory in-exchange data hit, that is, the cache hit ratio.

Data type selection

The most time-consuming operation in the database operation is IO processing, most of the database operation more than 90% times spent on the IO read and write. Therefore, to minimize the IO read and write volume, 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 take some thought into how that data is stored. The following tuning recommendations for field types are primarily applicable to scenarios where the number of records is large and the amount of data is larger, because fine-grained data type settings can lead to higher maintenance costs, and over-optimization can also lead to other problems:

1. Numeric type: Never use double as a last resort, it's not just a matter of storage length, but also an issue of accuracy. Similarly, decimal is not recommended for fixed-precision decimals, and it is recommended to convert to integer storage by multiplying the fixed multiples, which saves storage space without any additional maintenance costs. For the storage of integers, in the case of large amount of data, it is recommended to distinguish the choice of open tinyint/int/bigint, because the storage space occupied by the three is also very different, can determine the fields that do not use negative numbers, it is recommended to add unsigned definition. Of course, if you have a database with a small amount of data, you can also not strictly distinguish between three integer types.
2. Character type: Not the last resort to use the TEXT data type, which determines whether his performance is less than char or varchar type processing. Fixed Length field, it is recommended to use CHAR type, indefinite long field to use VARCHAR as far as possible, and only set the appropriate maximum length, rather than very random to a very large maximum length limit, because of different length range, MySQL will have the same storage processing.
3. Time Type: Use the timestamp type as much as possible because the storage space requires only half of the DATETIME type. For data types that only need to be accurate to one day, it is recommended to use the date type because his storage space requires only 3 bytes, less than timestamp. It is not recommended to store the value of a UNIX timestamp through the int type class because it is too intuitive to cause unnecessary inconvenience to maintenance and does not bring any benefit.
4. Enum & SET: For the Status field, you can try to use an ENUM to store it, because you can greatly reduce the storage space, and even if you need to add a new type, as long as it is added at the end, modifying the structure does not require rebuilding the table data. What about storing pre-defined attribute data? You can try out the set type, even if there are multiple properties, and you can also save a lot of storage space.
5. LOB type: Strongly against storing LOB type data in the database, although the database provides such functionality, but this is not what he is good at, we should let the right tools do what he is good at, in order to reach the extreme. Storing LOB data in a database is like having a marketing professional who learned a little bit of Java in school a few years ago to write Java code.

Character encoding

The character set directly determines how the data is stored in MySQL, and because the same content uses different character sets to indicate the size of the space used, it can help us reduce the amount of data and thus reduce the number of IO operations by using the appropriate character set.

1. The pure Latin word rp means that there is no need to choose a character encoding other than latin1 because it saves a lot of storage space
2. 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 creates a lot of wasted storage space
3. MySQL data types can be accurate to the field, so when we need large databases to hold multi-byte data, you can reduce the amount of data storage by using different data types for different fields in different tables, thus reducing the number of IO operations and increasing the cache hit rate

Proper splitting of

There are times when we might want to have a complete object that corresponds to a database table, which is good for application development, but sometimes it can be a big problem in terms of performance.

When there is a large number of fields in our table that resemble TEXT or a very big varchar type, if we do not need this field for most of our visits to this table, we should not hesitate to split it into separate tables to reduce the storage space used by common data. One obvious benefit of this is that the number of data bars that can be stored in each block can be significantly increased, reducing the number of physical IO times and significantly increasing the cache hit rate in memory.

The above points are optimized to reduce the amount of storage space per record, so that more records can be stored in each database to reduce the number of IO operations and improve cache hit ratios. The following optimization recommendations may not be quite understood by many developers, as this is a typical inverse paradigm design and is also contrary to some of the above-mentioned optimization recommendations.

Moderate redundancy

Why do we need redundancy? Does this increase the size of each piece of data, reducing the number of records that each block can hold?

Indeed, this will increase the size of each record and reduce the number of bars that can be stored in each record, but in some scenarios we still have to do this: it is frequently referenced and can only be 2 (or more) large tables to get the independent small segment such a scene because each join is just to get the value of a small segment, join to the record is large, will cause a lot of unnecessary IO, can be completely in the way of space for time to optimize. However, redundancy requires that data consistency is not compromised, and that redundant fields are updated as well as updated

Try to use not NULL

The NULL type is special and SQL is difficult to optimize. Although the MySQL null type differs from the null of Oracle, it goes into the index, but if it is a combined index, the null type field can greatly affect the efficiency of the entire index. In addition, NULL processing in the index is also special and takes up additional storage space.
Many people think that null will save some space, so as far as possible to allow NULL to achieve the purpose of saving Io, but most of the time this will backfire, although there may indeed be some savings in space, it brings a lot of other optimization problems, not only not to save the IO amount, but increased the amount of SQL Io. So try to ensure that the default value is not NULL, but also a good table structure design optimization habits.

Copyright NOTICE: Welcome to reprint, hope to reprint the same time add the original address, thank you for your cooperation, learning happy!

MySQL detailed (------------) Table structure optimization