MySQL performance optimization (2): choose the optimized data type

1. Principle of selection

Regardless of the type of data you store, here are a few simple principles that will help you make better choices.
1. Smaller is usually better
In general, you should choose the smallest data type that will correctly store your data if possible. Smaller data types are generally faster because they take up less disk space, memory, and require fewer CPU cycles to process.
However, when choosing a smaller data type, it is important not to underestimate the range of stored values, as modifying the data type and length later is a very painful and time-consuming operation. If you can't decide which data type is the best, choose the smallest type that you think won't go out of range.
2. Simple is good
Simple data type operations generally require fewer CPU cycles. For example, integer operations are less expensive than character operations because character sets and varsity rules (eg, collation) make character comparisons more complex than integer comparisons.
3. Try to avoid using NULL
NULL is a common value. It is usually used to set the default value of NULL for some fields, which is actually a very bad habit. If the field value in the query happens to be a set NULL value, it is more difficult for MySQl to optimize because NULLable fields make indexing and value comparison more complicated.
The NULL value cannot be indexed, which affects the statistics of the index and the judgment of the optimizer. As long as one column in a composite index contains NULL values, that column is invalid for the composite index.

2. String type

The string type is the most frequently used data type in the database. VARCHAR and CHAR are the two most important string types, both of which can be used to store strings, but they are stored and retrieved differently. VARCHAR is a variable-length character type, while CHAR is a fixed-length character type. Below is a description and comparison of the two types.
The VARCHAR type is used to store variable-length strings, and it is more space-efficient than fixed-length types because it only uses the necessary space (eg, shorter strings use the least space).
VARCHAR requires 1 or 2 extra bytes to record the length of the string (if the maximum length of the field is less than or equal to 255 bytes, only 1 byte is used for the length, otherwise 2 bytes are used for the length) . For example, a VARCHAR(10) field requires 11 bytes of storage, and a VARCHAR(1000) requires 1002 bytes of storage, including 2 bytes for the length.
The CHAR type is fixed length. When the data type is CHAR, MySQL removes all trailing spaces.
The CHAR type is suitable for storing very short strings, or all values ​​close to the same length. For example, the CHAR type is ideal for storing the MD5 value of a password because it is a fixed-length value. For frequently changing data, the CHAR type is also better than the VARCHAR type, because the fixed-length CHAR type is less prone to fragmentation. For storing very short columns, the CHAR type is more space efficient than VARCHAR. For example, using CHAR(1) to store only Y and N values ​​would require 2 bytes if using VARCHAR(1) because there would be an extra byte for the record length.
The following specific examples are used to illustrate CHAR, which is helpful for better understanding. Here, create a table char_test with only one CHAR(10) field, and insert three strings xcbeyond into it, paying attention to the difference between the spaces before and after:
mysql> create table char_test(ch char(10));
Query OK, 0 rows affected
mysql> insert into char_test(ch) values('xcbeyond'),('xcbeyond'),('xcbeyond');
Query OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0
Strange thing happens, when we query, we find that the spaces at the end of the third string are automatically truncated. In order to better show whether there are spaces, splice ' characters before and after the ch field for easy viewing and comparison.
mysql> select concat("'",ch,"'") from char_test;
| concat("'",ch,"'") |
| 'xcbeyond' |
| 'xcbeyond' |
| 'xcbeyond' |
3 rows in set
If the same value is stored in a VARCHAR(10) field, spaces at the end of the string are not truncated.

3. Date type

MySQL provides two similar date types: DATETIME and TIMESTAMP, which are silly and indistinguishable when used. After reading this section, don't say that you don't know how to choose.
For applications, they all represent dates well, but in some cases, they are different. Let's take a look at it next.
The DATETIME type can hold a wide range of dates, from 1001 to 9999, with seconds precision. It encapsulates the date and time into integers of the format YYYYMMDDHHMMSS, independent of the time zone, and uses 8 bytes of storage.
By default, MySQL displays DATETIME values ​​in a sortable, unambiguous format, such as 2020-03-05 22:38:40.
TIMESTAMP, as its name suggests, is the same as a UNIX timestamp, saving the number of seconds since January 1, 1970, 0:00:00. TIMESTAMP uses only 4 bytes of storage, so it has a much smaller range than DATETIME and can only represent the years from 1970 to 2038.
The value displayed by TIMESTAMP depends on the time zone, which is set by the MySQL server, operating system, and client connections. Therefore, a TIMESTAMP with a stored value of 0 appears as 1969-12-31 19:00:00 in the US Eastern time zone, which is 5 hours away from GMT.
Usually you should try to use TIMESTAMP, because it saves storage space than DATETIME, and for business across time zones, TIMESTAMP is more suitable.
What if you need to store date and time values ​​with finer granularity than seconds? MySQL does not currently provide a suitable data type, but other workarounds are possible, such as you can use your own storage format: you can use the BIGINT type to store timestamps at the subtle level, or use DOUBLE to store fractional seconds after. Alternatively, you can use MariaDB database instead of MySQL.

Related Articles

Explore More Special Offers

  1. Short Message Service(SMS) & Mail Service

    50,000 email package starts as low as USD 1.99, 120 short messages start at only USD 1.00

phone Contact Us