MySQL learning notes (19): optimizing database objects

Time:2021-1-20

This article is updated on August 18, 2019, using MySQL 5.7 and operating system deepin 15.4.

catalog
  • 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 byteENUMSuggestions 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.

Inverse normalization

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
  • trigger

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.

Recommended Today

MySQL partition

MySQL partition Advantages of partitioning Store more data To optimize the query, only scan one or more necessary partitions. For count () and sum (), only summarize the partition statistics For data that is out of date or does not need to be saved, partitioning is faster Data queries are distributed across multiple disks to […]