Why don't internet companies use mysql partition tables ?, Mysql Partition Table

Why don't internet companies use mysql partition tables ?, Mysql Partition Table

Origin: A friend asked me about the 58 Partition Table application. I couldn't answer it. In my impression, Baidu and 58 didn't even hear about partition table-related applications, during some technical exchanges in the industry, we also use our own database and table sharding instead of using partition tables. So I checked it online and asked the DBA experts at home 58 to accumulate the information I received and share it with everyone.

 

What problems can be solved?

A: When the database in a single mysql table is too large, the access speed of the database will decrease. The common solution to the "large data volume" problem is "horizontal segmentation ".

 

What are common horizontal Splitting Methods of mysql?

Answer: database/table sharding and Partition Table

 

What is mysql database/table sharding?

Answer: divide the data of a large database (table) into several databases (tables). Each database (table) has the same structure, however, they may be distributed across different mysql instances or even different physical machines to reduce the data volume of a single database (table) and improve access performance.

Database/table sharding is often implemented at the business layer. After database/table sharding, rd code is often required to meet certain service functions.

 

What is a mysql partition table?

A: All data is still in one table, but physical storage is stored in different files according to certain rules. This is a function supported by mysql, and the Business rd Code does not need to be changed.

 

It seems that partition tables are very handsome. Why do most of the Internet still choose their own database/table sharding for horizontal scaling?

Answer:

1) Partition Table, partition key design is not flexible, If you do not leave partition key, it is easy to appear full table lock

2) once the data volume concurrency comes up, it is a disaster to associate a partitioned table.

3) database/table sharding, business scenarios and access modes under control. The partition table, developed and written an SQL statement, is not sure how mysql is played, not controllable

4) O & M pitfalls

5 )...

 

The article is very short. I hope you can get it done in one minute. If you have any questions, please feel free to ask the DBA experts. If you have a partition table application, you can reply to it if you have any questions. I will share this article in the next article.

 

Traps: how to perform horizontal segmentation and database/table sharding? If you are interested, we will talk about more database architectures later.

 

ToUser (uid, name, passwd)

ExtendUser (uid, name, passwd, age, sex)Example

 

The basic principle is:

(1) create a new table named user_new (uid, name, passwd, age, sex) after the expanded field)

(2) create three triggers on the original table user. All insert/delete/update operations on the original table user will perform the same operation on the new table user_new.

(3) Batch insert the data in the original table user to the new table user_new until the data migration is complete.

(4) Delete the trigger and remove the original table (drop by default)

(5) rename the new table user_new to the original table user.

The expanded field is complete.

 

Advantages: Tables do not need to be locked throughout the process, and services can be continuously provided to external users.

 

Required during operationNote::

(1) The most important part in the change process is the handling of conflicts. One principle is that the new data of the trigger prevails, this requires that the table to be migrated must have a primary key (this requirement is basically met)

(2) A trigger must be created for write operations during the change process. Therefore, if the original table already has many triggers, the solution will not work (high-concurrency online services of Internet big data, generally, triggers are not allowed)

(3) The creation of a trigger will affect the performance of the original table. Therefore, we recommend that you perform this operation when the traffic is low.

 

Pt-online-schema-changeIt is an essential tool for DBAs and is mature and widely used in Internet companies.