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

Although sub-table and sub-database can solve the pressure of large tables on the database system, it is not omnipotent, and it also has some disadvantages, so the first problem is, regardless of library, which library, what rules, how many pieces.

Principle 1: can not be divided, less than 10 million of the table, do not recommend fragmentation, through the appropriate index, read-write separation and other ways, can well solve the performance problem.

Principle 2: the number of shards is as small as possible, and shards are evenly distributed over multiple DataHost as far as possible, because the more a query SQL spans shards, the worse the overall performance. Although it is better than the result of all data in one shard, expand the capacity and increase the number of shards only when necessary.

Principle 3: sharding rules need to be carefully selected, sharding rules need to be selected carefully, data growth mode, data access mode, sharding relevance, and sharding expansion need to be considered. The latest sharding strategies are range sharding, enumerating shards, and consistent Hash shards, which are all conducive to capacity expansion.

Principle 4: try not to span multiple fragments in the SQL of a transaction. Distributed transactions have always been a difficult problem to deal with.

Principle 5: optimize the query conditions as far as possible, avoid Select * as far as possible, under a large number of data result sets, it will consume a lot of bandwidth and CPU resources, query try to avoid returning a large number of result sets, and try to establish indexes for frequently used query statements.

If the data of a table has obvious time characteristics, such as orders, transaction records, etc., they are usually suitable for time range slicing, because of the timely data, we tend to pay attention to its recent data, and the query conditions are often filtered with a time field. A better solution is that the currently active data is sliced over a relatively short period of time, while the historical data Longer span storage is used.

Generally speaking, the choice of sharding depends on the condition of querying SQL most frequently, because the query SQL without any Where statement will facilitate all shards, and the performance is relatively the worst, so the more this kind of SQL, the greater the impact on the system, so we should try our best to avoid this kind of SQL.

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.