Reasonably optimize database table structure to improve project execution efficiency [database design]

Database table design optimization:

Sometimes in order to improve the efficiency of the database, we can properly consider the anti-three paradigms, add redundant fields appropriately, and reduce multi-table disassociation queries.

Use the index:

2.1 Database tables should be designed reasonably to use general index, primary key index, unique index, full-text index and composite (combined) index.

2.2 when the index is created:

(1) Fields that are more frequently used as query criteria

(2) for fields with poor uniqueness (such as gender / status fields, etc.), even if we frequently use them as conditions for query, it is not suitable to create indexes.

(3) the fields whose data are updated very frequently (such as the current online population field of the statistical platform / the sales quantity field of goods, etc.) are not suitable for creating indexes.

2.3 for the use of the index, please see: http://yaowusheng.blog.51cto.com/11020801/1853754

Note: creating an index requires overhead and takes up a certain amount of disk space; it is difficult to move the index data; in order to maintain the index file, it will slow down the operation of adding, deleting and changing. Do not overuse the index. It is very important to use the index properly.

3. Select the appropriate MySQL storage engine:

3.1 myisam Storage engine

If the table is not transaction-oriented and focuses on query and addition, consider the myisam storage engine (such as bbs's posting table and reply table)

3.2 innodb Storage engine

High requirements for transactions, saved data are important data, it is recommended to use innodb storage engine (such as account tables, order tables and other important tables)

3.3The memory storage engine

Data changes frequently, does not need to be stored in storage, and is frequently queried and modified at the same time. Consider using memory storage engine (Note: the inserted data will not be persisted to disk space, but will be temporarily saved in memory. If closed, the data will be lost.)