MySQL Database optimization

When it comes to optimization, determine whether the problem is the storage engine selection problem, the SQL statement usage problem (such as index), or a single storage server's tens of data.

Workaround:
1, according to different business selection of different storage engine, although the general situation is preferred to choose InnoDB.

2. Analyze the effect result set of SQL statement. Check the query log, locate the SQL statement for the slow query, and see if there is a locked SQL operation.

3, sub-Library, sub-table, partition (careful use of partitions, often OLTP operations do not apply to the partition, the partition will slow down the original query)
4, the use of cache or NoSQL instead of some of the existing hot query operations, reduce the mysql pressure.
Can be split based on business logic, different services distributed across different servers, reducing single server pressure
Two ways of vertical and horizontal table

Vertical table: Separate the frequently updated and infrequently updated fields in the table

Horizontal sub-table, three kinds of sub-table way:

Equal sub-table, hash table, the advantage is the average distribution of load, the disadvantage is that when the capacity continues to increase the expansion of prosperity is inconvenient, need to re-table, the primary key is very bad processing.

Time tables, based on different creation time tables, are suitable for OLAP applications.

MySQL Database optimization