Optimizing Database Objects

Procedure Analyse ()

To optimize the data type of a table use procedure analyse () to analyze the currently applied table

SELECT * FROM table_name procedure analyse ();

SELECT * FROM table_name procedure analyse (16,256);

The second statement tells procedure analyse () not to make recommendations for enum types that contain more than 16 or 256 bytes of value

SELECT * From rental procedure analyse () \g;

file_name	The name of the column
Min_value	Minimum value of the listed species
Max_value	The maximum value in the column
Min_length	The minimum length of the listed species value
Max_length	Maximum length of values in a column
Optimal_fieldtype	The advice given

Splitting increases the efficiency of table access

Vertical split

If some columns in a table are commonly used, and others are not, you can split vertically.

Advantages of vertical splitting

Vertical splitting can make data rows smaller, one data page can hold more data, and fewer I/O times when queried.

Disadvantages

The redundant columns need to be managed, and the join operation is required to query all data.

Split horizontally

Place data rows in two separate tables based on the values of one or more columns of data

Advantages

The table is large, can reduce the number of pages of data and indexes that need to be read at query time, and also reduce the index layer, improve the speed of the query.

The data in the table is inherently independent, for example: data from each region is recorded in the table or data from different periods, especially when some data is commonly used, while others are not used

Disadvantages

Data needs to be present on multiple media

Horizontal splitter adds complexity to your application, and querying all data requires a union operation.

Inverse normalization

Standardization:

The higher the normalization, the more relationships are generated, and the more direct the relationship is, the more frequent the connection operations between the tables will result. The connection operation between tables is a low performance operation that directly affects the speed of the query.

Anti-normalization:

The benefit of the anti-spec is to reduce the need for connection operations, reduce the number of foreign keys and indexes, and possibly reduce the number of tables. Speed up queries, but decrease the speed of modifications.

Anti-normalization classification: Adding redundant columns

Multiple tables with the same columns, often used to avoid connection operations when querying

Derived columns

The added columns come from data from other tables, and the data in the other tables is calculated and generated. The effect is to reduce the connection operation at query time, avoid using Set function

Re-set Table

If many users need to see the result data that is connected to two tables, the two tables are re-formed into a table to reduce connectivity and improve performance

Split table

Vertical Segmentation and horizontal segmentation

Common methods for maintaining the integrity of inverse paradigm data batch processing

Batch maintenance means that after a certain amount of time has elapsed since the modification of a replicated column or a derived column, a batch of processing jobs or a stored program that modifies the copied column or derived column can only be used if the real-time requirement is not high

Application logic

The complete line of data can also be implemented by application logic, which requires that all the tables involved in the same transaction must be increased, deleted, modified, and applied logic to achieve the integrity of the data risk, because in the same logic must be used in all applications and maintenance, easy to show, especially in the change in demand, not easy to maintain.

Trigger

Any modification to the data immediately triggers the corresponding modification of the copied column or derived column, the trigger is real-time, and the corresponding processing logic appears only in one place and is easy to maintain. Generally speaking, it's a good way to solve this kind of problem.

Using an intermediate table to increase the speed of statistical queries

For tables with large amounts of data, it is often inefficient to make statistical queries on them, and also to consider whether statistical queries will have an impact on online applications. In this case, using the intermediate table can improve the efficiency of the statistical query.

Advantages

The intermediate table duplicates the original table part of the data and is isolated from the original table, and the statistical query on the intermediate table does not negatively affect the online application.

The flexibility to add indexes and new fields for temporary use to improve the efficiency of statistical queries and the role of auxiliary statistical queries

Optimizing Database Objects