Mysql Optimization-database objects

(1) Optimize the table data type

The data type used by the table depends on the application. Although the length of fields must be redundant during application design, it is not recommended to leave a large number of redundant fields, which wastes storage and memory. Procedure analyse () can be used to determine the types of existing tables. This function can be used to optimize the Data Types of columns in a data table, you can determine whether to implement optimization based on the actual situation of the application. Syntax:

SELECT ** FROM tbl_name PROCEDURE ANALYSE(,);

 

Each output column provides optimization suggestions for the Data Type of the columns in the data table. The second example tells procedure analyse () not to recommend for ENUM types containing more than 16 or 256 bytes. Without such restrictions, the output information may be long; The ENUM definition is usually hard to read. When optimizing the field type, you can optimize it based on the statistical information and the actual situation of the application.

(2) Improve table access efficiency through sharding
Here we talk about splitting, mainly for tables of the Myisam type. The splitting method can be divided into two situations:
1. vertical Split: Vertical Split splits frequently accessed fields and infrequently accessed fields in the table into two tables based on the frequency of application access. The frequently accessed fields are as long as possible, this effectively improves the efficiency of Table query and update.
2. horizontal Split: horizontal split refers to splitting data horizontally into several tables or into multiple partitions Based on the application, this effectively prevents lock problems caused by Myisam table reading and updating.

(3) Inverse Normalization
The standardized design of database morality emphasizes data independence. Data should be redundant as little as possible because there is too much redundant data, which means more physical space is occupied, it also brings problems to data maintenance and consistency check. However, for applications with many query operations, a single query may need to access multiple tables. If Redundant records are stored in the same table, the update cost will not increase much, but the query efficiency can be significantly improved, in this case, we can consider using redundant data to improve efficiency.

(4) use redundant statistical tables
Use the create temporary table syntax. It is a session-based table. The table data is stored in the memory. When the session is disconnected, the table is naturally eliminated. For the statistical analysis of large tables, if the statistical data volume is small, USE insert... It is more efficient for select to move data to a temporary table than to make statistics directly on a large table.

(5) Select a more appropriate table Type
1. If a severe lock conflict occurs in the application, consider whether to change the storage engine to innodb. The row lock mechanism can effectively reduce the occurrence of the lock conflict.
2. If the application has many query operations and does not have strict transaction integrity requirements, you can use the Myisam storage engine. For more storage engine selection principles, see the relevant chapters in the development section.