MySQL in the optimization, simply said a vertical table, the horizontal table (there are several modes of operation), read and write separation.

first, the optimization in MySQL

Optimization of the WHERE statement

1. Try to avoid expression operations on fields in the WHERE clause
Select ID from Uinfo_jifen where jifen/60 > 10000;
After optimization:
Select ID from Uinfo_jifen where jifen>600000;

2. You should try to avoid function operations on the field in the Where clause, which will cause MySQL to discard the use of the index

Select UID from IMiD where DateDiff (create_time, ' 2011-11-22 ') =0
After optimization
Select UID from imid where create_time> = ' 2011-11-21 ' and create_time< ' 2011-11-23 ';

Optimizing GROUP BY statements

By default, MySQL sorts all group by Col1,col2, ..... The method of querying is like specifying an order by Col1,col2 in a query .... If you explicitly include an ORDER BY clause that contains the same column, MySQL can optimize it without slowing down, although it is still sorted. If the query includes group by but you want to avoid the consumption of sort results, you can specify order by NULL to prohibit sorting.

Optimizing the ORDER BY statement

In some cases, MySQL can use an index to satisfy the ORDER BY clause without requiring additional sorting. The Where condition and order by use the same index, and the order by IS in the same sequence as the index, and the order by field is ascending or descending.

Optimizing INSERT Statements

If you insert many rows from the same customer at the same time, use the INSERT statement for more than one value table. This is faster than using separate INSERT statements (several times in some cases). Insert into test values (1,3), (1,4) ... More information: http://www.cnblogs.com/ggjucheng/archive/2012/11/07/2758058.html Second, vertical table, horizontal sub-table (there are several modes of operation), read and write separation.

Vertical Sub-table:

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, while others are not used, vertical segmentation can be used, and vertical partitioning can make the data rows smaller, a data page can hold more data, and the I/O times are reduced at query time. The disadvantage is that redundant columns need to be managed, and the join operation is required to query all data.

Horizontal split:

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

Horizontal segmentation is usually used under the following conditions.
• The table is large, which reduces the number of pages of data and indexes that need to be read at query time, and also lowers the index layer and improves query speed.
• The data in the table is inherently independent, such as a table that records data from each region or different periods of time, especially when some data is commonly used, while others are infrequently used.
• Data needs to be stored on multiple media.
Horizontal partitioning adds complexity to your application, and it usually requires multiple table names at query time, and the union operation is required to query all data. In many database applications, this complexity outweighs its advantages because, as long as the index keyword is not large, when the index is used for querying, the table adds two to three times times the amount of data, and the query increases the number of disks that read an index layer.

Read/write Separation:

　　A database server, is to provide external additions and deletions to change the business of the production server, another database server, mainly for read operations.

Multiple separate hard disks (physical hard disks) are combined in different ways to form a hard disk group (a logical hard disk), primarily through raid.

MySQL in the optimization, simply said a vertical table, the horizontal table (there are several modes of operation), read and write separation.