Mysql sub-table _ MySQL

Mysql sub-table bitsCN.com

The popularity of web in recent years has led to a boom in Mysql usage. both small enterprises and large websites have intentionally or unintentionally started using Mysql to build a new data platform. with the amount of visits from the industry, traditional websites, with the rapid expansion of data volumes, centralized databases become increasingly bottlenecks, making it difficult to further expand and separate read/write data. these are the advantages of Mysql, easy expansion makes Mysql a new choice for enterprises.

When it comes to scalability, like apps, when the database is under pressure, you only need to increase the number of database servers to solve the problem and try not to adjust the application. the server hardware is broken, you just need to take a new server to the top, which is the original intention of my design. Next, I will talk about the design ideas of the SNS database through Mysql (note: I only understand it). SNS focuses on individual (userid), such as my friends and my logs, my album, My gang, and so on. I emphasize individual behaviors of people. now SNS websites like 51.comare very popular, and Facebook is built through Mysql, next, I will try to explore the design trajectory of this business type and how to achieve scalability.

Starting from how to split the database, consider Database Sharding based on the user (userid) attribute. users who have friends, blogs, and albums follow the user's behavior, A single database contains all the rows of the user. this method is easy to locate and finds all the actions of the user after finding a database, which is very convenient. You can also split databases by business type. Each business is database-based and divided into friend libraries, blog libraries, and album libraries. different business modules are independent of each other. this idea is clear, database Sharding rules are flexible and easy to develop for different businesses.

Next we will discuss the design of table sharding. I strive to achieve the principle of "small and fast" when designing a database on Mysql. the data size of a single database must be small, and the database must respond quickly. the table design should be very flexible, for different businesses, you can select the corresponding table sharding rules. The idea of Xiao Qiling requires that the table design be easily scaled horizontally. when the data volume is too large, it is easy to split the table. In terms of design ideas, you can use a configuration table (metadata) to store configuration information for sub-tables. assume that the number of registered users planned to be around 20 million this year is divided into four tables, table1 stores 1-5 million data, 10 million-is stored in table2 ,... Each table retains 5 million of the data. table sharding rules and query routes are maintained through the configuration table. one day, we found that the data volume in Table 1 was too active and the database was under heavy pressure. We also started to consider table sharding, modify the table sharding configuration information and split Table 1 into two tables and store them on two servers to share the pressure. of course, access pressure may not necessarily lead to table sharding, when the number of records in a table is large to a certain number, you also need to split the table. The rules for using configuration information to maintain table shards are very flexible. configuration information prevails. However, too flexible means that this risk is high, and the importance of configuration information is self-evident. Next, I mentioned that mod Modulo is used for table sharding. in the initial stage, mod (4) is used to divide the table into four tables. based on the modulo result, the tables 0, 1, and 2 are divided into four tables, as the business grows, the amount of data expands and the access pressure increases, the table needs to be further split because of the special nature of the modulo operation and the principle of table splitting without data migration as much as possible, we recommend that you use mod (8) to expand tables by using multiples. Next, we will consider using mod (16) to split tables and constantly scale tables by multiples. Compared with configuration information table sharding, the modulo design is not so flexible, but flexibility is not necessarily a good thing. in case the configuration information is mistakenly adjusted, it will be troublesome.

It is often seen that many tables do not have primary keys, which is generally unacceptable for rigorous system design. most tables use auto-incrementing IDs for primary keys, which may be single tables at the beginning, the next step may be to split multiple tables. with auto incremental, it is difficult to ensure uniqueness in multiple tables. how can we ensure table sharding (user_table1, user_table2 ,...) What is the uniqueness of IDs between them? Considering that oracle uses sequences to ensure the uniqueness of IDs, sequences are superior to tables. we also consider adding a sequence table to the Mysql database to simulate the sequence implementation method of oracle, you can add a record to all tables that require auto-increment. for sub-tables, each sub-table calls the same record, and the application directly calls this record for auto-increment, to ensure the uniqueness of the id. This is just a method of implementation. if only the uniqueness of the id is required, you can use the function to generate it, or you can ensure that the id is unique according to certain rules such as time accurate to milliseconds, there are too many methods.

Around scalability, we briefly introduced some design ideas for Database Sharding, table sharding, and id generation. this is only the tip of the iceberg in the entire website. we need to think about the details, such as basic data tables, only a few dozen pieces of data are not sharded into tables or sub-databases. when other tables are split into multiple databases, where is the data stored? do you need to create a base database or keep one copy for each database, in the end, how many values are appropriate? is the Myisam storage engine used or Innodb? All of these require us to think carefully. The Details determine success or failure. pay attention to every detail. the implementation method of each table should be involved in the business as much as possible, and learn more about the product manager and development ideas, learn more about Mysql implementation methods, Mysql advantages, Mysql scalability advantages, learn more about the industry's mature design ideas, and design a more reasonable system based on its own business model.

BitsCN.com