Shulou(Shulou.com)06/01 Report--

Xiaobian to share with you MySQL data types and schema optimization is what, I hope you read this article after the harvest, let us discuss it together!

1. Select optimized data types

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

Smaller is usually better.

You should use the smallest data type possible that can store data correctly. This will take up less disk, memory, and cache, and it will take less time to process.

keep it simple

When both data types are adequate for storing a field, choosing the simpler one is often the best choice. For example, integers and strings, because integers are less expensive to operate on than characters, when choosing between the two, choosing integers usually gives better performance.

Try to avoid NULL

When columns are Nullable, more work is required for MySQL in terms of indexes and value comparisons, although the impact on performance is not great, but design to be Nullable should be avoided as much as possible.

In addition to the above principles, when selecting data types, the steps to follow are: first determine the appropriate large type, such as data, string, time, etc.; and then select the specific type. The following discusses some specific types under the large type, starting with numbers, which come in two types: integers and real numbers.

1.1 integer type

The integer types and space occupied are as follows:

integer type space size (bit) TINYINT8SMALLINT16MEDIUMINT24INT32BIGINT64

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

Integer types have the optional attribute UNSIGNED, which when declared means negative numbers are not allowed, and the storage range becomes: 0 to 2^(N)-1, doubling.

In MySQL, it is also possible to specify a width for integer types, such as INT(1), but this does not mean much, and does not limit the legal range of values, but can still store values from-2^31 to 2^31 -1, which affects the number of characters displayed by interactive tools with MySQL.

1.2 real type

A comparison of real types is as follows:

Real type Space size (Byte) Value range Calculation precision FLOAT4 Negative: -3.4E+38~-1.17E-38; nonnegative: 0, 1.17E-38~3.4E+38 Approximate calculation DOUBLE8 Negative: -1.79E+308~-2.22E-308; nonnegative: 0, 2.22E-308~1.79E+308 Approximate calculation DECIMAL Related to precision Same as DOUBLE Accurate calculation

As can be seen from the above, FLOAT and DOUBLE both have a fixed space size, but at the same time because they use standard floating point operations, they can only be approximated. DECIMAL, on the other hand, can achieve accurate calculations, while occupying more space and consuming more computational overhead.

DECIMAL takes up space that depends on the precision specified, e.g. DECIMAL(M,D):

M is the maximum length of the whole number, the value range is [1, 65], the default value is 10;D is the length after the decimal point, the value range 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.