A brief understanding of MySQL storage field type query efficiency

The search performance from fastest to slowest is (here is what I heard):

• First: tinyint, smallint, mediumint, int, bigint
• Second: char, varchar
• Third: NULL

Explanation (reprint):

Integer Types

1. TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT, use 8, 16, 24, 32, 64 bytes respectively

2. Integers all have the optional attribute UNSIGNED (for example, the value range of the tinyint field is 0-255 after unsigned, while the range of signed is -128 - 127. So if we clearly do not need negative values, we usually do not set signed to support negative numbers.)

3. INT(1) and INT(20) are the same for storage and calculation. N in INT(N) only specifies the number of characters to be displayed by some interactive tools.

Character Type

char is fixed-length, storage efficiency is not as good as varchar, but it is worse than varchar for querying short data

Fixed length. For example, if uuid is used as the primary key, char would be more appropriate.

NULL Type

1. Indexing NULL columns requires extra space

2. Null values ​​will be processed during comparison and calculation, which may cause index failure

So try not to use NULL type, use more integer types

In addition, the time date data type

1. Do not use strings to store date data, which wastes space

2. DATE can save years from 1001 to 9999 with an accuracy of seconds. It encapsulates the date and time into an integer in the format of YYYYMMDDHHMMSS, using 8 bytes.

3. TIMESTAMP stores the number of seconds since 1970, which is the same as Unix timestamp. It can only store 1970 to 2038 and uses 4 bytes.

4. The two functions FROM_UNIXTIME() and UNIX_TIMESTAMP() convert dates and Unix timestamps

5. DATE and TIMESTAMP store time, but where is the time? ? ? : The former is a time representation no matter where you are (regardless of time zone), while the latter is Greenwich Mean Time. . That is to say, when storing, DATE is stored according to the given time, and TIMESTAMP is first calculated according to the corresponding Greenwich Mean Time based on the time zone and the given timestamp before storing. When accessed, DATE is returned according to the stored time, and TIMESTAMP is calculated according to the stored timestamp (regarded as Greenwich Mean Time) and the time zone to calculate the corresponding time in the time zone.

6. TIMESTAMP is usually used, which has high space efficiency

7. MYSQL does not provide date and time values ​​with granularity smaller than seconds. If necessary, you can use BIGINT to store timestamps at the microsecond level, or use DOUBLE to store the decimal part after seconds.

Real number types

1. DECIMAL is used for financial purposes

2. DECIMAL can save integers outside the BIGINT range

3. FLOAT and DOUBLE use standard floating-point operations for approximate calculations

char and varchar

1. char is fixed length, varchar is variable length, that is, varchar saves space (unless created using ROW-FORMAT=FIXED)

2. Because varchar is variable length, if the length becomes longer during UPDATE, additional work will be done

3. Use varchar when: the maximum length of a string column is much larger than the average length; the column is rarely updated (so fragmentation is not an issue); a character set like UTF8 is used (each character is stored using a different number of bytes)

4. CHAR is suitable for: very short, or all values ​​are close to the same length (such as MD5); columns change frequently

5. Trailing space problem: higher version varchar will retain trailing spaces; char and lower version varchar will remove trailing spaces.

6. In CHAR(N) and VARCHAR(N), N represents the number of characters, not the number of bytes (Chinese characters occupy 3 bytes in UTF8)

7. Although the VARCHAR(N) data type stores the size of the string it represents on disk, when it is read into memory, the memory will allocate N*k+1or2 (N<=255,1;else 2;) to it (k is determined by the character set)

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.