Mysql database sharding and table sharding methods (commonly used) _ MySQL

This article mainly introduces Mysql database sharding and table sharding methods (commonly used), involving mysql database knowledge, if you are interested in mysql database sharding and table sharding, this article will introduce you to Mysql database sharding and table sharding methods (commonly used), involving mysql database knowledge, anyone interested in mysql database sharding and table sharding

1 Sub-Database

1.1 Database Sharding by function

Perform Database Sharding according to the function. It is commonly divided into six major databases:

1. user class library: used to save user information. For example, db_user, db_system, and db_company.

2. Business Library: used to store information about major businesses. For example, the main business is joke, and the library is used to save the joke business. For example, db_joke and db_temp_joke.

3. memory class library: it mainly uses the Mysql memory engine. The front-end data is quickly searched from the memory library. For example, heap.

4. image library: Stores Image indexes and associations. For example, db_img_index and db_img_res.

5. log class library: record click, refresh, logon, and other log information. For example, db_log_click, db_log_fresh, and db_log_login.

6. statistics class library: business statistics, such as clicks and refreshes. For example, db_stat.

1.2 install City Station database shards

If your business is spread all over the country, copy the same database in each city according to the function database, but the database suffix is the city name. For example, db_log_click_bj, db_log_click_tj, and db_log_click_sh;

2-table sharding

2.1 Table sharding by user or Business ID

Users or businesses can be divided into n tables by Number % n.
Example: joke table.

Tb_joke_01, tb_joke_02, tb_joke_03, tb_joke_04 ........

2.2 Table sharding by date

For tables such as logs or statistics. The table can be sharded by year, month, day, and week.

For example, click count statistics.

tb_click_stat_201601，tb_click_stat_201602，tb_click_stat_201603

3. common Mysql database architecture

Core: one master, multiple slaves, read/write splitting.

We will introduce the Mysql database sharding and table sharding methods (commonly used) here, and hope to help you!