How does MySQL optimize for Tens's big table?

Source: Internet
Author: User

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?

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.