mysql--Optimizing Database Objects

1) Optimize the data type of the table

The type of data that a table needs to use is judged by the application. While application design requires some redundancy in the length of the field, it is not recommended to leave a lot of redundancy in many fields, wasting disk storage and wasting physical memory while the application is operating.

In MySQL, you can use the function PROCEDURE analyse () to analyze the currently applied table, which provides optimization suggestions for the data types of columns in the data table, and allows users to consider whether or not to implement optimizations, as appropriate for the actual application.

Here is how the function PROCEDURE analyse () is used:

SELECT * from Tbl_name PROCEDURE analyse ();

SELECT * from Tbl_name PROCEDURE analyse (16, 256);

Each column of the output proposes optimization recommendations for the data type of the columns in the data table. The second sentence above tells PROCEDURE analyse () do not make recommendations for ENUM types that contain more than 16 or 256 bytes of value.

2) Improve the efficiency of table access by splitting

The split in this case refers to the splitting of the data table. If you are doing a table for the MyISAM type, there are two ways to split it.

2.1> Vertical Split

Put the main code and some columns into a table, and then put the main code and other columns in the other table.

If some columns in a table are commonly used, and others are not, you can split vertically, and vertical splits can make data rows smaller, a page of data can hold more data, and I/O times are reduced when queried. The disadvantage is that you need to manage redundant columns, and querying all data requires a federated (join) operation.

2.2> Horizontal Split

Place data rows into two separate tables based on the values of one or more columns of data. Horizontal splitting is usually used in the following situations.
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 query speed.

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

Data needs to be stored on multiple media.

Horizontal splits add complexity to your application, which usually requires multiple table names at query time and a UNION operation to query all data. In many database applications, this complexity will outweigh its advantages, because as long as the index keyword is not large, when the index is used for querying, the table increases the number of double-fold data, the query also increases the reading of an index layer of disk times, so the horizontal split to consider the growth rate of data volume, Decide whether you want to split the table horizontally according to the actual situation.

mysql--Optimizing Database Objects