How does MySQL optimize for Tens's big table?

Zhuqz
Links: https://www.zhihu.com/question/19719997/answer/81930332
Source: Know
Copyright belongs to the author. Commercial reprint please contact the author for authorization, non-commercial reprint please specify the source.

The first optimization of your SQL and index;

Second plus cache, Memcached,redis;

After the third has done, or slow, the decision from the copy or master copy, read and write separation, can be done in the application layer, high efficiency, can also use three-party tools, third-party tools recommended 360 Atlas, the other is either inefficient, or no one to maintain;

If the above are done or slow, do not want to do slicing, MySQL comes with partition table, first try this, for your application is transparent, no need to change the code, but the SQL statement needs to be optimized for the partition table, SQL conditions to take the partition condition of the column, so that the query to locate a small number of partitions, Otherwise, all the partitions will be scanned, and there will be some pits in the partition table, so there is not much to say;

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;

The second is 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 amount of 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;

MySQL database is generally in accordance with this step to evolve, the cost is from low to high;

How does MySQL optimize for Tens's big table?