Data types and performance of MySQL

MySQL supports a lot of data types, choosing the right data type is critical to performance, and this article discusses how to correctly choose the data type of MySQL.

Integer

There are several integers: TINYINT, Smaillint, Mediumint, INT, BIGINT, respectively, using 8, 16, 24, 32, 64-bit storage space. They all have an optional unsigned attribute, indicating that negative numbers are not allowed.

You can specify a width for an integer type, such as int (13), but it is meaningless for most applications, and int (1) and int (18) are the same for storage calculations.

Real

Real numbers are numbers with decimal parts, and MySQL supports both exact types and imprecise types.

float and double support the use of standard floating-point computations (that is, imprecise types), Float uses 4 bytes, double uses 8 bytes, there are several ways to specify the precision of floating-point columns, but these precision are nonstandard, so we recommend that you specify only the data type, not the precision.

Decimal is used to store exact decimals, and it can also specify the maximum number of digits allowed before and after a decimal point, such as decimal (20,2), which indicates that 2 digits are stored after the decimal point, and 18 digits are stored before the decimal point. It is clear that decimal takes up more space and consumes more CPUs than floating-point numbers, which is to precisely indicate the cost.

Therefore, you can use decimal only if you have to use exact decimal places, such as finance, or you can use floating-point numbers as much as possible.

String

MySQL supports string types that are more complex, and each string column can define its own character set and collation.

VARCHAR: Used to store variable-length strings. It requires 1 or 2 extra bytes to record the length of the string: if the maximum length of the column is <=255, 1 bytes are used, otherwise 2 bytes are used, such as varchar (100) using 1 bytes, and varchar (1000) using 2 bytes. It is more appropriate to use varchar in these cases: the maximum length of the string column is much larger than the average length, and the column update is very small, because the update operation may cause the line to grow longer and may result in space fragmentation. Note: The trailing space is reserved for varchar,mysql when it is stored.

CHAR: Used to store fixed-length strings. Note: The trailing space is deleted for char,mysql when it is stored.

Similar types as char and varchar have binary and varbinary, but they store bytecode, so they have no concept of character set and collation, are strictly case-sensitive, and, when compared to size, are compared by byte, so performance is better.

Text and blobs are designed to store larger strings and are stored in both character and binary mode.

Text has: Tinytext, smalltext, text, Mediumtext, Longtext, where text and Smailltext are synonyms.

BLOBs are: Tinyblob, Smallblob, blobs, Mediumblob, Longblob, where blobs and smallblob are synonyms.

Unlike other types, MySQL treats each text and blob as a separate object, and when they are too large, InnoDB is stored in a dedicated "external" storage area, and a 1~4 byte is required to store pointers within the line.

Date and time

DATETIME: Can save a large range of values, from 1001 to 9999, precision is seconds, it encapsulates the time and date in a Yyyymmddhhmmss integer, independent of the time zone, using 8 bytes of storage.

TIMESTAMP: Saves the number of seconds (precision also seconds) experienced since midnight January 1, 1970 (Greenwich Mean Time), using only 4 bytes, only from 1970 to 2038, and its display depends on the time zone.

Note: MySQL does not currently provide a date and time type that stores smaller granularity than seconds.

Data type selection criteria

The choice of MySQL data type should follow the following guidelines:

1) smaller usually better.

In general, you should try to use the smallest data type that can store the data correctly, for example: if you only need to save 0~200, tinyint unsigned best.

2) Simple and better

Simple data type operations require less CPU cycles, such as an integral type that performs better than character manipulation. Therefore: You should use the date and time data types built in MySQL to store the date and time instead of strings, and you should store the IP address with an unsigned integer.

3) Try to avoid null on index columns

When MySQL is in the table, the following properties are allowed to be null by default, but it is best to specify NOT NULL unless you really need to store null values, because nullable columns make indexes, index statistics, and values more complex and use more storage space. Especially if you plan to index on columns, you should avoid designing nullable columns as much as possible.

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/MySQL/