MySQL schema and data type optimization analysis

Schema and data type optimization:

1. Select the optimized data type

Smaller is usually better: in general, try to use the smallest data type that can store the data correctly

such as: just save 0-200,tinyint unsigned better.

Simple: For example, an integer is less expensive than a string operation, and the date and time should be stored using MySQL built-in types instead of strings.

Try to avoid null: it is generally preferable to specify a column not null unless you really want to store a null value. For example, the query contains nullable columns that are more difficult to optimize for MySQL.

2. Select the Type range

Both datetime and timestamp columns can store the same type of data (time and date, accurate to seconds), but timestamp only use

A datetime is half the storage space, and will have special automatic update capabilities depending on the time zone change.

3. Integer type

There are two types of numbers: integers and real numbers

If you store integers, you can use tinyint (8), SMALLINT (+), Mediumint (+), INT (+), BIGINT (64).

An integer type has an optional unsigned property, which means that negative values are not allowed, which can increase the positive limit by one more times. such as tinyint unsign can be stored in a range of 0~255.

Signed and unsigned use the same storage space and have the same performance.

4. Real type: Real numbers are digits with fractional parts.

5. String types: VarChar and char are the most important string types.

VARCHAR: Used for variable-length strings, which is more space-saving than fixed-length because it uses only the necessary space.

Suitable for use with varchar: The maximum length of string columns is much larger than the average length, and the column updates are few;

Using the UTF-8 character set

CHAR: Is fixed-length, suitable for storing short strings, or all values are close to the same length. such as: Store the MD5 value of the password,

Because this is a fixed-length value. For frequently changed data, char is also better than varchar, because the fixed-length type is not prone to fragmentation.

For very short columns, char is more efficient than varchar in storage space. For example: Storing only N and y values in char (1) is only one byte,

But with varchar (1) It takes two bytes, because there is also an extra byte for record length.

The space overhead of storing ' hello ' with Varhar (5) and char (5) is the same, and longer columns consume more memory.

MySQL schema and data type optimization analysis