MySQL 分表有什么坏处和改进意见？

Question

如果一张表数据很大，将分表拆分数据，或者预估数据量很大，会预先生成很多分表，那么，日后要改字段，岂不是每张表都是执行同样的 sql 改字段？

MySQL 还有分区的概念，就是解决一张表数据量过大的，为什么现实开发中还是普遍使用分表的方式？

Answer 1

I think that whether it is a database or various programming models, it does not mean that 0 coupling is the best. Proper redundancy can improve performance, readability, and maintainability.

Answer 2

In my humble opinion, partitioning requires you to specify rules to divide tables, which is relatively rigid. Table sharding can perform flexibly hot data sharding on the business logic layer.

Answer 3

The capacity of a table is still limited. The more data there is, the insertion performance will be affected. Every time you insert, you have to find the corresponding partition and index, which will greatly reduce the insertion speed.

As for the disadvantages of splitting tables, of course you said that if you need to modify fields, then you have to do it in every table, but this possibility is small, and the previous data has become historical data, and if you If you put all the data in one table, won't changing the table structure lock the table? From this aspect, sub-tables can also achieve separation of reading and writing. Another disadvantage is that the query requires union tables, and you need to use while to splice sql, which is a bit cumbersome, and there is no need to splice partitions. . .

So in practice, tables and partitions are often combinedand each complements the other.