Optimizing database table structure reasonably and improving project execution efficiency [database design]

2. database table Design optimization :
   

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

3. Use index :

   2.1 database table design should be reasonable to use Ordinary index, primary key index, unique index, full - text index and composite (combined ) index.

   2.2 when to create an index:

   (1) A field that is more frequent as a query condition ;

   (2) The uniqueness of the field is too poor ( such as the Gender/Status field, etc. ), even if we frequently as a condition of the query, not fit to create the index;

   (3) Data update very frequently fields (such as the statistics platform current online person Number field/Item sales quantity fields, etc.) is not suitable to create an index.

   2.3 Use of specific indexes see: http://yaowusheng.blog.51cto.com/11020801/1853754
   

Note: Creating an index is cost-intensive, takes up a certain amount of disk space, and the index data is difficult to move; to maintain the index file, it can cause the increment, delete, change the operation to be slow, do not overuse the index, the reasonable use index is very important.

3. Select the appropriate MySQL storage engine:

3.1 MyISAM Storage Engine
If the table on the transaction requirements are not high, but also to query and add the main, consider MyISAM storage engine (such as BBS posting table and reply table);
3.2 InnoDB Storage Engine
High transaction requirements, stored data are important data, it is recommended to use the InnoDB storage engine (such as Account form, order form, etc. important table);
3.3 Memory Storage Engine
Data changes frequently, do not need storage, but also frequent query and modification, you can consider using Memory storage engine ( Note: The inserted data will not be persisted to disk space, but temporarily saved in memory, the data will be lost when closed );

This article is from the "Yao Technology blog" blog, make sure to keep this source http://yaowusheng.blog.51cto.com/11020801/1853695

Optimizing database table structure reasonably and improving project execution efficiency [database design]