MySql data table structure optimization summary

You should try to use the smallest data type that can store the data correctly, smaller data types are usually faster because they take up less disk, memory, and CPU cache, and require less CPU for processingFewer cycles, but make sure you don't underestimate the range of values ​​you need to store, and if you can't identify which data type, choose the smallest type that you think won't exceed the range

Case:

Design two tables, design different data types, and view the capacity of the table

If the query contains nullable columns, it is difficult to optimize for mysql, because nullable columns make indexing, index statistics and value comparison more complicated, frankly, usually nullThe performance improvement brought by changing the column to not null is relatively small, so there is no need to modify the schema of all tables, but it should be avoided as much as possible to design a nullable column, and a null field can be changed to an empty characterstring.

Integer types:

Several integer types that can be used: TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT use 8, 16, 24, 32, 64-bit storage space respectively.Try to use the smallest data type that meets your needs

String and character type

Char has a fixed length, that is, each piece of data occupies an equal-length byte space; the maximum length is 255 characters, which is suitable for ID card numbers, mobile phone numbers, etc.Fixed-length string

varchar is variable, and the maximum length can be set; the maximum space is 65535 bytes, which is suitable for use in variable-length attributes

text does not set the length, whenWhen you don't know the maximum length of the attribute, it is suitable to use text

according to the query speed: char>varchar>text

BLOB and TEXT types

MySQL treats each BLOB and TEXT value as a separate object.

Both are string types designed to store large data, using binary and character storage respectively.

datetime and timestamp

Do not use string types to store datetime data

Datetime types usually take up less storage space than strings

Date and time types can be compared using dates when searching and filtering

Date and time types also have rich processing functions, which can easily calculate dates for time types

Using int to store date and time is not as good as using timestamp type

Use enumeration instead of character type

Sometimes you can use enumeration class instead of common string type, MySQL storage enumeration type will be very compact, it will be compressed into one according to the data of list valueor two bytes, mysql internally saves the position of each value in the list as an integer, and saves a lookup table of the "number-string" mapping in the table's .frm file

create table enum_test(e enum('fish','apple','dog') not null);

insert into enum_test(e) values('fish'),('dog'),('apple');

select e+0 from enum_test;

Special types of data

People often use varchar(15) to store ip addresses, however, its nature is a 32-bit unsigned integer not a string, you can use INET_ATON() and INET_NTOAThe function converts between these two representations

Case:

select inet_aton('1.1.1.1')

select inet_ntoa(16843009)