MySQL Data type optimization

1. Smaller is usually better

2. It's simple.

3. Try to avoid null
   

One, integer type

There are two types of numbers: integers and real numbers. If you store integers, you can use these kinds of integer types: tinyint,smallint,dediumint,int,bigint. Use 8,16,24,32,64 bit storage space, respectively.

Using the unsigned property means that replication is not allowed, which is roughly one-fold higher than the upper limit for example positive numbers.

Ii. Types of real numbers

A real number is one with a fractional part. However, they are not just for storing fractional parts; You can also use decimal to store integers larger than bigint. MySQL supports both exact types and imprecise types.

The decimal type is used to store exact decimals. Because additional space and computational overhead are required, you should try to use decimal--, for example, to store financial data only when you are making accurate calculations of decimals. However, when the number is relatively large, you may consider using bigint instead of decimal, the currency units that need to be stored are multiplied by the number of decimal places. Assuming that you want to store the financial data exactly one out of 10,000 points, you can counter all amounts by 1 million, and then store the results in bigint so that you can avoid the problem of inaccurate floating-point computations and the high cost of decimal precision calculation.

Three, String type

varchar and char are two of the most important string types.

The varchar type is used to store variable-length strings, and if MySQL tables are created using row_format=fixed, each row will use fixed-length storage, which can be a waste of space. varchar needs to record the length of the note string using 1 or 2 extra bytes

The char type is fixed length, char is suitable for storing very short strings, or all values are close to the same length. such as the MD5 value, because this is a fixed-length value.

Char is also better than varchar for frequently changed data because the fixed-length char type is not prone to fragmentation.

Generosity is not knowingly:

MySQL Data type optimization