MySQL Database performance optimization-table optimization

Database object Optimization

1. Optimize the data type of the table

• Use procedure analyse () to parse the currently applied table, which can make recommendations for optimizing the data types of the columns in the data table. SELECT * from Tb1_name procedure analyse ();

2, by splitting to improve the efficiency of the table access-- sub-database table

• Vertical split: Place the main code and some columns into a table, and then place the main code and other columns in the other table
• Horizontal split: The data rows are placed in two separate tables based on the values of one or more columns of data.

3. Inverse normalization

4, using the intermediate table to improve the speed of statistical queries

5. Table Maintenance

1) Locate and repair the damaged table

2) Update index statistics

3) Reduce fragmentation of indexes and data

• Analysis table: Analyze table sales;
• Checklist: Check table sales;
• Optimization table: Optimize table sales;

Sub-database sub-table

1. The evolution of the sub-database sub-table strategy

1) Library single table: The most common database design, for example, a user table is placed in database db, all users can be found in the DB Library in the Use table

2) Library Multi-table: As the number of users increased, the user table data volume is more and more large, when the amount of data to a certain extent, the User table query will gradually slow down, thus affecting the performance of the entire DB, if you use MySQL, there is a more serious problem is that when you need to add a column, MySQL locks the table, during which all read and write operations can only wait. User can be in some way to split the table, resulting in two table structure exactly the same user_0000, user_0001 and other tables, user_0000+user_0001+ ... Data is just a complete piece of data.

3) Multi-Library Multi-table: With the increase in data volume, also line a single DB storage space is not enough, with the increase in query volume, a single database server has no way to support, this time can be split the database.

2, sub-database sub-table rules

1) According to the number of sections: user_id distinguish, 1~1000 corresponding db1,1001~2000 corresponding DB2, and so on

2) hash modulus: user_id hash (or user_id is a numerical value can be directly used user_id values), and then use a specific number, such as the application needs to be a database cut into 4 databases, use 4 this number to User_ The hash value of the ID is taken as a modulo operation, i.e. user_id%4.

3) Save the database configuration in the Authentication library: it is to establish a DB, this db to save USER_ID to db separate mapping, every time you access the database to query the database to obtain the specific DB information, and then we need to do the query operation.

MySQL Database performance optimization-table optimization