High performance Mysql-schema and data type optimization 1

High performance MySQL reading notes chapter fourth--schema and data type optimization reading notes

Good logical and physical design is the cornerstone of high performance, and the schema should be designed according to the query statement that the system will execute, which often requires a tradeoff between various factors.

This chapter lays the groundwork for the next index optimization and query optimization, covering the theme of MySQL's unique Schem design:

I. Select the optimized data type

MySQL supports a very high number of data types, and choosing the right data type is critical to gaining performance. When selecting a data type for a column, the first step is to determine the appropriate large type, and the next step is to select different subtype data under the same large type as the specific type, sometimes with some special behaviors and properties.

1. Smaller data types are usually better

2. Simple is good

3. Avoid null as much as possible

Two. Integer type

There are several types of integers that you can use: Tinyint,smallint,mediumint,int,bigint use 8,16,24,32,64 bit storage space respectively.

An integer type has an optional unsigned property, which means that negative values are not allowed, which can roughly increase the upper limit of a positive number by a factor.

Integer calculations generally use a 64-bit bigint integer, even if the 32-bit environment (some aggregate functions are exceptions, they are computed using a decimal or double).

Three. Real type

The float and double types support approximate calculations using standard floating-point operations. The decimal type is used to store accurate decimals, and the MySQL server itself implements a high-precision calculation of decimal, which, by contrast, directly supports native floating-point calculations, so the floating-point computation is significantly faster.

Both floating-point and decimal types can specify precision, and for decimal you can specify the maximum number of digits allowed before and after the decimal point, which affects the space consumption of the column.

Floating-point types tend to use less space than decimal when storing values of the same range, so they should be used only when precise calculations of decimals are possible.

Four. String type

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

The varchar type stores variable-length strings, he is more space-saving than the fixed-length type, and varchar saves storage space, so it is also useful for performance, but because it is longer, it may make the row longer than it was in the update, which leads to additional work.

Char is fixed-length, suitable for storing very short strings (the end is filled with spaces).

Five. Blob and text type

MySQL treats each blob and text value as a separate object.

Six. Using enumerations (enums) instead of string types

Sometimes enumerations can be used instead of commonly used string types, and enumerations store non-repeating strings into a predefined collection. Enumerations are in the form of (numbers-strings) when they are saved.

Seven. Date and Time type

MySQL uses many types to save date and time values, such as the minimum time granularity that year and Date,mysql can store in seconds, and MySQL provides two similar date types: datetime and timestamp, and in some cases one is better than the other.

Datetime

This type holds the maximum value from 1001-9,999, with a precision of seconds.

Timestamp uses 4 bytes to hold the date, default not NULL.

Eight. Bit data type

The bit has a maximum length of 64 bits. MySQL treats bit as a string type, not as a numeric type.

Sets a set of packaged bits. MySQL has functions like find_in_set and field, which are easy to use in queries. His main drawback is that the cost of altering the columns is too high and cannot be found on the set through the index.

Nine. Select an identifier . Usually an integer type and a string.

10. Special types of data , such as millisecond time, IP address, can be converted when used.

High performance Mysql-schema and data type optimization 1