High Performance MySQL Chapter 4th schema and data type optimization

Basic principles:

• Smaller is usually better: consumes less disk memory and CPU cache.
• Simple: For example, shaping is less expensive than character type. Use date types to store dates instead of strings. Use shaping to store IP addresses.
• Try to avoid null: If you can add a NOT NULL constraint. Because nullable columns make indexes, index statistics, and value comparisons all become complex. It is common to change a nullable column to not NULL to bring a small performance boost, and there is no need to fix the situation first to troubleshoot the problem when tuning. But if you plan to index on a column, if you can guarantee that it is not NULL, then try to add it.

Integer type:

Tinyint,smallint.,mediumint,int,bigint. 1,2,3,4,8 bytes, respectively. This is the 8,16,24,32,64 bit, which stores data ranging from -2 (n-1) to 2 (n-1)-1.

If you use the unsigned property, negative numbers are not allowed. Then the stored range becomes 0 to 2n-1.

MySQL can set widths for integer types, such as int (1) and int (20), but only one more width constraint, internally, they are all int.

Real type:

A real number is a digit with a decimal point. Their role, however, is not limited to this. If you can use decimal to store larger integers than bigint.

Imprecise numeric types (floating-point types): float double, they are 4 bytes, 8 bytes respectively.

With regard to the specified precision, the book was swept over, the following content from the network:

Float numeric types are used to represent single-precision floating-point numbers, whereas double numeric types are used to represent double-precision floating-point numbers, float and double are floating-point, and decimal is fixed-point, and MySQL floating-point and fixed-point types can be represented by the type name plus (m,d). m represents the total length of the value, D represents the length after the decimal point, and M and D are also known as precision and scale, such as float (7,4), which can be displayed as -999.9999,mysql when the value is saved, and if 999.00009 is inserted, the result is 999.0001.

Decimal can only be up to 65 digits, that is, the total m maximum of that side is only 65.

A decimal of the same size takes up more space than a float and a double, consumes more CPUs, so it's best not to use decimal if it's not required for precise calculations such as financial calculations.

The book mentions a kind of tactful method, will have the decimal number to be a certain multiples, such as 10,000 times times, then save as bigint. can improve performance. I wonder if this kind of tactful approach brings a better performance boost. It may be used in extreme environments.

High Performance MySQL Chapter 4th schema and data type optimization