Mycat How to divide the table sub-Library? Mysql DBA Learning

Mycat Sub-database although can solve the large table on the system of the pressure, but there are some disadvantages, so the Mycat sub-table sub-Library to solve the problem is that the sub-Library, which library, what rules, divided how many shards. So what exactly is the point?

1, can not be divided into 10 million of the table, not recommended shards, through the appropriate index, read and write separation, can be a good solution to performance problems.

2, the number of shards as small as possible, the Shard as far as possible evenly distributed on multiple datahost, because a query SQL more cross-Shard, the overall performance is worse, although better than all the data in a shard results, only when necessary to expand, increase the number of shards.

3. Shard Rules need to choose carefully, the choice of Shard rule, need to consider the data growth mode, data access mode, shard correlation problem, as well as the Shard expansion problem, the nearest Shard strategy is the scope shard, enumerate the shards, the consistency hash shard, these kinds of shards are advantageous to the expansion.

4, try not to cross multiple shards of SQL in a transaction, distributed transaction has always been a bad problem to deal with.

5, the query conditions as far as possible optimization, as far as possible to avoid the mode of select *, a large number of data results set, will consume a lot of bandwidth and CPU resources, query to avoid returning a large number of result sets, and try to index the frequently used query statements.

In general, the choice of sharding depends on the most frequent query SQL conditions, because the query SQL without any where statement will facilitate all the shards, the performance is relatively the worst, so the more SQL, the more impact on the system, so we should try to avoid the generation of this SQL.

Internet companies mostly use the database is MySQL, want to stand out from the many IT workers, need to have advanced technology, learning to add value is essential. The way of learning is your insistence. Old boy Education MySQL DBA course, after several update courses, to eliminate the theory, the whole enterprise real case combined with theoretical teaching, want to deep learning MySQL DBA knowledge, can pay attention to the old boy education.

Mycat How to divide the table sub-Library? Mysql DBA Learning