Summarize how MySQL is optimized under big data volumes

Before you write a library:

Once the database business is established and a database table is created, there are some common issues to consider, to avoid a response after a period of data growth, which can result in increased time and maintenance costs:

• Monthly increment of data, year increment
• The fast growth point of data
• Whether a trigger or event is required, etc.
• Query Business Requirements
• Number of server accesses

The above considerations have a very important impact on the types of databases , the structure of tables , the definition of relationships between tables , and the configuration of databases .

Post-run Optimizations:

Optimization order

First, optimize your SQL and index ;

To implement a query, you can write many kinds of query statements, different statements, depending on the engine you choose, the distribution of data in the table, index situation, database optimization strategy, the lock strategy in the query, and other factors, the efficiency of the final query varies greatly; optimization to consider from the whole, sometimes after you refine a statement, Other queries are less efficient, so take a balanced point.

Second, add cache , Memcached,redis;

Third, master-slave replication or primary master replication, Read and write separation ;

Four, if the above are done or slow, do not want to do the slicing, MySQL comes withPartition Table, first try this, is transparent to your application, do not need to change the code, but the SQL statement needs to be optimized for the partition table, the SQL condition to take the partition condition of the column, so that the query to locate a small number of partitions, otherwise it will scan all partitions, in additionpartition table and some pits .(The use of partitioned tables is still somewhat reserved, and it seems that the currentPartitioning key design is not too flexible, if you do not go to the partition key, it is easy to have full table lock, in addition, once the data volume concurrency, if the Partition Table Implementation Association, is a disaster. ）

Five, if the above are done, then do the vertical split , in fact, according to the coupling degree of your module, a large system is divided into a number of small systems, that is, distributed systems;

Six, is the horizontal segmentation , for the data volume of the table, this step is the most troublesome, the most able to test the technical level, to choose a reasonable sharding key, in order to have a good query efficiency, table structure to change, do a certain redundancy, the application should be changed, SQL as far as possible with sharding Key, locating the data in a restricted table, rather than scanning all the tables;

Reference:

1,mysql How to optimize the big table for tens? https://www.zhihu.com/question/19719997

2, improve the write speed (collation) by configuring MySQL parameters https://www.cnblogs.com/lzy1991/p/4778786.html

3, MySQL partition table http://blog.csdn.net/qq_19707521/article/details/59058135 not recommended

Summarize how MySQL is optimized under big data volumes