MySQL schema and data type optimization

Schema and data type optimization Select an optimized data type 1. Smaller usually better

Smaller data types are usually faster because they consume less disk, memory, and CPU cache

2. Simple is good

Operations of simple data types typically require less CPU cycles. For example: integer is less expensive than word Fu Cao, because character sets and collation rules make character comparisons more complex than integers

3. Avoid null as much as possible

Generally it is best to make a column not null unless you really need to store a null value.

If the query contains nullable columns, it is more difficult for MySQL to optimize because nullable columns make the index. Index statistics and value comparisons are more complex. Nullable columns use more storage space and require special handling in MySQL

If you plan to build indexes on columns, you should avoid designing nullable columns as much as possible

Integer type

Tinyint,smallint,mediumint,int,bigint. Use 8,16,24,32,64 as storage space respectively.

The optional unsigned property of an integer type means that negative values are not allowed, which can roughly increase the upper limit of a positive number by one more. For example: TINYINT unsigned can store a range of 0~255, while TINYINT storage range is -128~127

Real type

The decimal type is used to store exact decimals

Decimal type allows a maximum of 65 digits

Because additional space and computational overhead are required, you should try to use decimal only when you are making accurate calculations of decimals, such as storing financial data. But when the amount of data is large, consider using bigint instead of decimal. The currency units that need to be stored are multiplied by the number of decimal places. This avoids the problem of inaccurate floating-point storage computations and the high cost of decimal computation.

String type varchar

The varchar type is used to store variable-length strings and is the most common string data type. It is more space-saving than a fixed-length type because it uses only the necessary space

varchar requires 1 or 2 extra bytes to record the length of a string: if the maximum length of a column is less than or equal to 255 bytes, then only 1 bytes are used, otherwise 2 bytes are used

In these cases, it is appropriate to use varchar: The maximum length of the string column is much larger than the average length; the column is not updated very often, so fragmentation is not a problem; a complex character set such as UTF-8 is used, each character is stored with a different number of bytes

CHAR

The char type is fixed-length: MySQL always allocates enough space based on the length of the string defined. Char values are populated with spaces as needed for easy comparison.

Char is suitable for storing very short strings, or all values are close to the same length. For example: Char is ideal for storing the MD5 value of a password, 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. For very short columns, char is more efficient than varchar in storage space.

Date and Time type

MySQL can store a minimum time granularity of seconds

Datetime

This type can hold a large range of values, from 1001 to 9999, with a precision of seconds. Use 8 bytes of storage space

TIMESTAMP

The timestamp type holds the number of seconds since midnight January 1, 1970 and can only be displayed from 1970 to 2038. The default value is not NULL

MySQL schema and data type optimization