MYSQL Database Table Design Optimization

During this period, I studied a lot of database knowledge, so I also consulted a lot of professional materials, and I strongly recommend that you learn the trigger well. If you have more time to practice, at least write the format! After these days of interview experience

During this period, I studied a lot of database knowledge, so I also consulted a lot of professional materials, and I strongly recommend that you learn the trigger well. If you have more time to practice, at least write the format! After these days of interview experience

During this period, I studied a lot of database knowledge, so I also consulted a lot of professional materials, and I strongly recommend that you learn the trigger well. If you have more time to practice, at least write the format! After these days of interview experience, it is necessary to apply for database jobs, triggers, and storage calls. Of course, there are many development jobs. This is also the reason why I repeatedly recommend that I learn this knowledge. The database covers a wide range of content. Of course, for more enterprises, the oracle database is more widely used. The following are the knowledge points of Mysql, for emergency purposes.

1. Select the Optimized Data Type

MySQL

However, it is extremely effort-consuming to increase the range of data types in multiple places in the architecture without being underestimated. If no data type is required, select the minimum type that you think will not be out of the range.

(2 ).

Define fields as full as possible

MySQL

Even if you want to store a field that can be empty in the table, there is a way not to use it.

Set

The integer type is optional.

Signed (

Your choice will be decided

MySQL

3. Real Number

Real numbers have scores. However, they are not just scores. Available

Float

Comparison

Because extra space and computing overhead are required, it is used only when decimals need to be precise.

.

Char

Use

5. BLOB

BLOB

The fact is that each of them has its own data type family: the character type has

Different from other types,

MySQL

6. Use

Enum

Because

7. Date and Time types

MySQL

MySQL

TIMESTAMP

TIMESTAMP

8. Select an identifier

It is very important to select a data type for the ID column. You may use them more to compare with other columns and use them as foreign keys for other tables, because when you select a data type for the identifier column, you may also select a data type for the relevant table.

When selecting a data type for the identifier column, consider not only the storage type, but also

Once the data type is selected, make sure that the same type is used in the relevant table. Exact match before the type, including

Integers are usually the best choice for identifiers because they are fast and can be used.

Enum

Avoid using strings as identifiers as much as possible because they occupy a lot of space and are generally slower than integer types.

MyISAM

PACK_KEYS

If you want the index to be smaller, set this option

If you do not use

9. Special Data Types

Some data types do not directly correspond to built-in data types. a timestamp with a precision lower than the second is an example, and another example is

This article is from the "gorgeous JAVA turn" blog. Please keep this source