Database optimization design _ PHP Tutorial

Database optimization design. Most of the time, we may change the field in the original database table structure as needed to meet our business needs. To put it simple, many times, we may add fields to the original database table structure as the requirements change constantly to meet our business needs.

Here is a simple example:

I need a database table to store the user information of a website. This user information needs to include (whether the account is an active account, whether the account is bound to a mailbox, whether the account has purchased products, and whether the account has expired .... normally, we may design this database table as follows:

　　

  ()    AUTO_INCREMENT,       `F001` ()  ,       `F002` ()  ,       `F003`  ()  ,       `F004`  ()  ,        (InnoDB AUTO_INCREMENT  CHARSETutf8;

If you follow the above method for design, when the demand changes, such as adding a field, whether the account is valid. Then we need to add another field 'f005 ', which will cause great trouble in management. when adding a field, we need to update all or historical data, which can easily lead to data loss.

Then, how can we solve this problem and minimize the impact of association between data fields. One way is to integrate all these fields into one field. For example, the 'flag' field is stored in decimal format (first: 1: Active, 0: inactive. Second: 1: Bound, 2: not bound. Third place: 1: purchased products, 0: not purchased ....), explanation: If the value of this field is 5, the value is 101 in binary format, 1 in the first place, 0 in the second place, and 1 in the third place. this indicates that the account is "active, unbound, and purchased ". What are the advantages of this method? the advantage is that the association between various types is very small and it does not affect other fields because a field type is updated. So how can we query this design? Bitwise and, for example, when I want to query all purchased products and active accounts (the first and third places are 1 ), for other bits, enter 0, that is, 101 = 5:

SQL statement query:

　　　　

   ACCOUNT  F005;

The preceding SQL statement can be used to query accounts that have purchased products and are active.

Then, you only need to update the binary bits to the required values in the background program. for example, the purchased binary bits are active, that is, 101, if you want to change it to purchased but not active, use (5 & 1) = 1, so that the third position is 0. Here we provide a PHP function that can handle the business of the program very well.

　　

 = (2, (-1 () |! () |! () | (! = 0 &&! = (& (0xffff ^) |

Bytes. A simple example...