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

Source: Internet
Author: User
Tags database sharding
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!

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.