This article is updated on August 18, 2019, using MySQL 5.7 and operating system deepin 15.4.
- Data type of optimization table
- Improve the efficiency of table access by splitting
- Inverse normalization
- Using intermediate table to improve the efficiency of statistical query
Once the database object is designed and put into use, it is troublesome to modify it.
Data type of optimization table
select_statement PROCEDURE ANALYSE([max_elements[, max_memory]])
The optimization suggestions for the data type of each output column are put forward, which users can consider according to the actual situation. The meaning of the parameter is: do not exceed max_ Elements or max_ Memory byte
ENUMSuggestions are put forward.
Improve the efficiency of table access by splitting
- Vertical split: put the primary key and some columns in one table, and put the primary key and other columns in another table.
- Horizontal splitting: putting data rows into multiple tables according to the values of one or more columns. Horizontal splitting will increase the complexity of the application, which usually requires multiple table names in the query.
The common reverse normalization techniques are as follows
- Add redundant columns: there are the same columns in multiple tables to avoid join operation during query.
- Add derived column: the added column is calculated from the data in other tables, which is used to reduce join operations and avoid using aggregate functions.
- Regroup tables: if you often need to see the results of two tables joining, regroup the two tables into one to avoid joining.
- Split table: vertical split and horizontal split.
Reverse normalization needs to maintain the integrity of data
- Batch maintenance
- Application logic
Using intermediate table to improve the efficiency of statistical query
- The intermediate table replicates part of the data of the source table, and is isolated from the source table. Making statistical queries on the intermediate table will not have a negative impact on online applications.
- The middle table can flexibly add indexes or temporary fields to improve the efficiency of statistical query.