How to choose a more appropriate data type for MySQL

Smaller is usually better

At the beginning of the table design, I think the choice of the right data type here is very critical, he can minimize the disk footprint of the database, and in the process of querying the simpler the smaller the data type is required to occupy a smaller CPU cache, memory, hard disk, and relative processing CPU cycles.

It's simple.

The choice of data types should be kept as simple as possible, for example, shaping is less expensive than character types, because the collation and collation of character types is more complex than shaping .

You should use MySQL's built-in type instead of selecting a string to store the date and time, and you should use shaping to store the IP address.

Try to avoid null

And you should avoid using null types, because NULL is more difficult for MySQL to optimize, and a null column causes more complex changes to indexes, index statistics, and value comparisons, and a null column uses more storage space in MySQL and requires more special handling. When NULL is indexed

Each index record requires an extra byte, which in MyISAM even causes a fixed-size index (for example, only one integer column index) to become a variable-size index. normally null columns to NOT NULL result in a small performance increase, so (when tuning) there is no need to first in an existing data table

find and fix in , unless you determine that this block will cause problems, but in the column that you plan to create the index, you should try to avoid designing null columns, but it is worth mentioning that the use of a null column in InnoDB storage is the use of a separate bit (bit) to store null values, in a large number of NULL The situation, there is good

of space efficiency . But this does not apply to MyISAM.

How to choose a more appropriate data type for MySQL