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

This article will explain in detail how to optimize Schema in MySQL. The content of the article is of high quality, so the editor shares it for you as a reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

1. Select optimized data type

There are many data types supported by MySQL, and how to choose the right data type is critical to performance. The following principles can help determine the data type:

Smaller ones are usually better.

Whenever possible, use the smallest data type that can store the data correctly, as long as it is enough. This takes up less disk, memory, and cache, and takes less time to process.

Just keep it simple.

When both data types are competent for the storage of a field, choosing the simple one is often the best choice. For example, integers and strings, because the operation cost of integers is less than characters, choosing integers usually results in better performance when choosing between the two.

Try to avoid NULL

When the column can be NULL, for MySQL, more work needs to be done in terms of indexing and value comparison, although the impact on performance is not great, but we should try to avoid being designed to be NULL.

In addition to the above principles, when selecting a data type, you need to follow the steps: first determine the appropriate large type, such as data, string, time, and so on; then select the specific type. The following will discuss some specific types under large types, starting with numbers, there are two types: integers and real numbers.

1.1 Integer type

The type of integer and the space occupied are as follows:

Integer type space size (bit) TINYINT8SMALLINT16MEDIUMINT24INT32BIGINT64

The range that an integer type can store depends on the size of the space:-2 ^ (Nmur1) to 2 ^ (Nmur1)-1, where N is the number of digits of the space size.

The integer type has the optional property of UNSIGNED. When declared, negative numbers are not allowed, and the storage range becomes: 0 to 2 ^ (N)-1, which is doubled.

In MySQL, you can also specify a width for an integer type, such as INT (1), but this does not make much sense, does not limit the legal range of values, and can still store values from-2 ^ 31 to 2 ^ 31-1, affecting the number of characters displayed by tools that interact with MySQL.

1.2 Real number types

The comparison of real number types is as follows:

Real type space size (Byte) value range calculation accuracy FLOAT4 negative number:-3.4 2.22E-308~1.79E+308 38 color 1.17Ely38; non-negative number: 0, 1.17E-38~3.4E+38 approximate calculation DOUBLE8 negative number:-1.79Ecolor 308 color 2.22Ely308; non-negative number: 0, 2.22E-308~1.79E+308 approximate calculation DECIMAL is related to the accuracy of DOUBLE accurate calculation

As you can see from the above, both FLOAT and DOUBLE have a fixed size of space, but at the same time, because they use standard floating-point operations, they can only be calculated approximately. On the other hand, DECIMAL can achieve accurate calculation, and at the same time, it will occupy more space and consume more computational overhead.

The amount of space occupied by DECIMAL is related to the specified precision, such as DECIMAL (MMagne D):

M is the maximum length of the whole number, the value range is [1,65], and the default value is 10.

D is the length after the decimal point, the range of values is [0,30], and D

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.