mysql - 如果单表的数据达到300万，是否考虑分表？

Question

数据在mysql中，大约10个字段，预计达到300万条。

网站注册时需要在这300万条记录中找到相匹配的；

如何做到优化？

请教

Answer 1

The 500W sub-meter has already reached 300W. You should reserve knowledge and make plans for sub-meters.

Answer 2

Ignoring hardware, business, database parameters, table structure design, etc., and directly judging whether to split tables based on the amount of data, isn’t it nonsense?

1. FIO, SSD, HDD. . .
2. Simple query and complex query. . .
3. Index optimization and caching technology. . .

Answer 3

The index is handled well, so you won’t need to split the table

Answer 4

MySQL can still provide better services when the single table reaches 1000W to 2000W. Currently, there is a table online with more than 1000W, and there are no performance problems. With multiple tables containing millions of data, we are starting to consider sub-tables. Well, if the table is relatively simple and the index is reasonable, there is no need to split the table for 3 million W. I remember the DBA said that the performance of mysql single table exceeds 50 million, 60 million, or tens of millions, and the performance will drop significantly, so it does not reach tens of millions. There is no need to consider sub-tables for levels

Answer 5

When registering the website, you need to find matching records among these 3 million records;

What you want to do is to simulate this situation now to see the performance of the db, optimize the index and your implementation, and the sub-table is not needed for this amount of data.

Answer 6

@gongweixin, you are right. In theory, the performance will drop sharply only when the data exceeds 50 million. Some db architects have also said so. But the actual situation is not necessarily different. According to some real projects, the price of more than 5 million will start to slow down. Of course, this is also closely related to specific business logic, response requirements, and table design. All table fields are of int type and can be optimized to more than 10 million.
@Mr_Jing is right, when it reaches 3 million, it is time to make technical reserves for optimization. But it doesn’t have to be divided into tables. Performance can also be improved through technologies such as caching and search engines.
The specific time to optimize depends on your system monitoring.
You can also read my article: mysql solution

Answer 7

Depend on your business situation. Query statement
If they are all queries that hit the index, there is still no need to split the table

Answer 8

Find preparations for sub-tables. Performance is a problem when there is too much data. .

Answer 9

Consider it when adding two zeros