Shulou(Shulou.com)06/01 Report--

Let me give you a brief account of why it is said that MySQL single table data cannot exceed 5 million rows. Have you known about similar topics before? If you are interested, let's take a look at this article. I believe it is helpful to say that the data in a single MySQL table can not exceed 5 million rows after reading.

Today, we will discuss an interesting topic: how much data can be achieved in a single table in MySQL before you need to consider sub-database and sub-table? Some people say 2000 million lines, others say 5 million lines. So, how much do you think is appropriate?

Once widely spread in China's Internet technology circle such a saying: MySQL single table data volume of more than 2000 million rows, the performance will be significantly degraded. In fact, this rumor is said to have originated from Baidu. The specific situation is something like this. When DBA tested the performance of MySQL that year, it was found that when the amount of a single table was in the order of 2000 rows, the performance of SQL operations declined sharply, so the conclusion came from this. Then it is said that Baidu engineers moved to other companies in the industry and also brought this message, so such a saying spread in the industry.

Later, Alibaba's "Java Development Manual" proposed that the number of rows in a single table exceeds 5 million rows or the capacity of a single table exceeds 2GB. In this regard, there is Ali's golden iron rule support, therefore, many people design big data storage, will take this as the standard, sub-table operation.

So, how much do you think is appropriate? Why not 3 million lines, or 8 million lines, but 5 million lines? Maybe you would say that this may be Ali's best actual combat number, right? So, here comes the question again: how is this figure evaluated? Just a moment, please think for a moment.

In fact, this number has nothing to do with the actual number of records, but has something to do with the configuration of MySQL and the hardware of the machine. Because MySQL loads the index of the table into memory in order to improve performance. When InnoDB buffer size is sufficient, it can be fully loaded into memory, and there will be no problem with the query. However, when a single-table database reaches the upper limit of a certain order of magnitude, memory cannot store its indexes, resulting in disk IO generated by subsequent SQL queries, resulting in performance degradation. Of course, this is also related to the design of the specific table structure, which ultimately leads to memory limitations. Here, increasing the hardware configuration may lead to an immediate performance improvement.

Then, my view on sub-library and sub-table is that it needs to be combined with the actual needs and should not be over-designed. At the beginning of the project, we do not use sub-library and sub-table design, but with the growth of business, when it is impossible to continue to optimize, then consider sub-library and sub-table to improve the performance of the system. In this regard, Alibaba's "Java Development Manual" added: if it is expected that the amount of data after three years can not reach this level at all, please do not divide the database into tables when creating tables. So, back to the initial question, how much do you think is appropriate? My suggestion is that, according to the comprehensive assessment of the situation of one's own machine, if there is no standard in mind, then taking 5 million rows as a unified standard for the time being is a relatively eclectic value.

What do you think of this article about why it is said that MySQL single table data cannot exceed 5 million rows, and whether it has gained anything? If you want to know more about it, you can continue to follow our industry information section.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.