High-performance MySQL (4) Data type optimization

First, the Basic principles

1, smaller is usually better

Smaller data types are usually faster because they take up less disk, memory, and CPU cache and require less CPU cycles to process.

But make sure that you don't underestimate the range of values you need to store, because increasing the range of data types in multiple places in a schema is a time-consuming operation.

2, Simple is good

For example, an integer is less expensive than a string operation and should use an inline type instead of a string to store the time and date, with an integer storage IP.

3. Avoid null as far as possible

A nullable column uses more storage space and requires special processing. In particular, when nullable columns are indexed, additional bytes are required for each index, and even a fixed-size index may be caused in the MyISAM engine, so the columns that are scheduled to be indexed are avoided using NULL.

Ii. Types of data

1. Integer type

TINYINT, SMALLINT, Mediuint, INT, BIGINT, respectively, use 8, 16, 24, 32, 64 bit storage space. range from -2 (N-1) to 2 (N-1) secondary to 1.

Specifies a range, just a display, for storage and calculations int (1) and int (20) are the same.

2. Real type

float and double support approximate calculations using standard floating-point operations.

The decimal type is used to store the exact decimal

A floating-point type has a value that stores the same range, and typically uses less space than decimal. Because of the extra space and computational overhead, it should be used only when accurate calculations are performed on decimals, or you can consider using bigint instead of decimal, multiplying the number of decimal digits by the corresponding multiples.

3. String type

The varchar type is used to store variable-length strings, but requires 1-2 extra bytes to record the length of the string. Because rows are longer, different engines require different additional processing at update time. The trailing spaces are preserved while being stored and retrieved.

The char type is fixed length. The trailing spaces are deleted when the store is stored.

For the maximum length of the string is much larger than the average length, the column update is very small, suitable for varchar. For frequently updated data, char is better because fragmentation is not easy to produce.

4, Blob and text type

is a string type designed for storing large data, used in binary and character storage, and a blob with no collation and character set.

MySQL sorts the blob and text columns differently than the other types, it only sorts for the first max_sort_length byte of each column, or uses the order by substring (column,length).

If your query uses blobs and text columns and you need to use an implicit temporary table, you will have to use the Myiasm Disk temp table, which is a huge overhead. If you can't avoid it, one trick is to use substring (column,length) to convert a column value to a string wherever you use the BLOB field, so you can use the memory temp table. However, make sure that the truncated string is short enough to make the temporary table larger than max_heap_table_size and tmp_table_size, if the disk temp table is exceeded or used.

If the extra column for explain contains a "using temporary", then an implicit temporary table is used

5. Enum type

Storing a few repetitive strings into a predefined set saves space. MySQL internally saves each value in the list as an integer and saves a lookup table of the mapping relationship of "number-string" in the table's. frm file. is converted to a string only when a lookup is made.