MySQL single table data should not exceed 5 million rows: is it an empirical value or a golden rule?

Today, let’s discuss an interesting topic: How much data does a single MySQL table need to consider before it needs to be divided into databases and tables? Some say 20 million rows, others say 5 million rows. So, what do you think this value is appropriate?

There was once a widely circulated saying in China's Internet technology circle: MySQL's performance will drop significantly if the data volume of a single table exceeds 20 million rows. In fact, this rumor is said to have originated from Baidu. The specific situation is probably like this. When the DBA tested the performance of MySQL, he found that when the size of a single table reached 20 million rows, the performance of SQL operations dropped sharply. Therefore, the conclusion comes from this. Then it was said that Baidu engineers moved to other companies in the industry and brought this information with them, so this saying spread in the industry.

Later, Alibaba's "Java Development Manual" proposed that database and table sharding is only recommended when the number of rows in a single table exceeds 5 million or the capacity of a single table exceeds 2GB. This is supported by Alibaba's golden iron rule. Therefore, when many people design big data storage, they will use this as a standard to perform table operations.

So, what do you think is the appropriate value? Why not 3 million rows, or 8 million rows, but 5 million rows? Maybe you would say that this may be Ali's best actual combat value? So, the question comes again, how is this value evaluated? Wait a moment, please think about it for a moment.

In fact, this value has nothing to do with the actual number of records, but is related to the configuration of MySQL and the hardware of the machine. Because, in order to improve performance, MySQL will load the index of the table into memory. When the InnoDB buffer size is sufficient, it can be fully loaded into memory and there will be no problem with querying. However, when a single-table database reaches an upper limit of a certain magnitude, the memory cannot store its index, causing subsequent SQL queries to generate disk IO, resulting in performance degradation. Of course, this is also related to the design of the specific table structure, and the ultimate problem is memory limitation. Here, increasing the hardware configuration may bring immediate performance improvements.

So, my point of view on sub-database and sub-table is that it needs to be combined with actual needs and should not be over-designed. The sub-database and sub-table design should not be used at the beginning of the project. Instead, as the business grows, it will be unavailable. If optimization continues, consider sharding databases and tables to improve system performance. In this regard, Alibaba's "Java Development Manual" adds: If the data volume is not expected to reach this level in three years, please do not divide the database into tables when creating the table. So, back to the original question, what do you think is an appropriate value? My suggestion is to make a comprehensive evaluation based on the situation of your own machine. If you have no standard in mind, then temporarily use 5 million lines as a unified standard, which is relatively a compromise value.

For more MySQL related technical articles, please visit the MySQL Tutorial column to learn!

The above is the detailed content of MySQL single table data should not exceed 5 million rows: is it an empirical value or a golden rule?. For more information, please follow other related articles on the PHP Chinese website!