Several effective optimization methods of MySQL data table

Below we want four kinds of MySQL tutorial data table several effective optimization methods , so as to improve the MySQL database tutorial in the application of data throughput capacity.

The data type of the optimization table

What type of table needs to be based on the needs of the time to take into account the length of the application design, but it is not recommended that many fields leave a lot of redundancy, so that both waste disk and waste of physical memory

SELECT * FROM tablename procedure analyse ();

SELECT * FROM tablename procedure analyse (16.265);

The above output a column of information, mu your datasheet field to propose optimization build justice,

Improve data access efficiency by splitting tables

Split is a split on the table, if it is for the MyISAM type of table processing, there can be two ways to split

1, is the vertical split, the main and some scattered to a table, and then put the main and another column in another table.

2, horizontal split method, according to the value of one or more columns of data rows into two separate tables, horizontal split usually several cases.

The table is large, split can reduce the query speed of data and indexing, but also reduce the number of layers of the index, improve the speed of the query.

The data in the table is independent, the data in each region or the data of different periods are recorded separately, in particular some data are commonly used, and some data of the toilet country are not used

The data needs to be stored on a number of different media.

Third, reverse standardization

Four, use the intermediate table optimization method

How to improve the statistical query speed of the table oh, we talked about using the middle table to improve the query and statistical speed of the datasheet.

For tables with a large database tutorial, it is usually slower to make a statistical query, and also to consider whether the query will affect the online application, usually in which case we use the intermediate table to increase the query statistics speed