PHP combined with MySQL 10 million level data processing

Time:2021-6-9

Thinking of MySQL table

PHP combined with MySQL 10 million level data processing

A 100 million order table can be divided into five tables, so that each table has only 20 million data, sharing the pressure of the original table. Sub tables need to be divided according to certain conditions. Here, tables can be divided according to regions. A middleware is needed to control which table to go to and find the data you want.

Middleware: according to the self increasing ID of the main table, which fields are suitable for middleware? Be unique)

How to distribute? After the main table is inserted, an ID is returned, and the module is taken according to the ID and the number of tables. The data is inserted into which table according to the remainder.

Note: the ID of the sub table should be consistent with that of the main table

In the future, only insert operation will use the main table, modify, delete, read, do not need to use the main table

PHP combined with MySQL 10 million level data processing

Redis message queue

1. What is message queue?

A container that holds messages during message propagation

2. The historical reason of message queue

PHP combined with MySQL 10 million level data processing

Characteristics of message queue: first in first out

Save the SQL statements to be executed in the message queue, and then insert them into the database asynchronously

Application: Sina, put the instant comments into the message queue first, and then insert the SQL statements in the message queue into the database successively through the timing task

modify

Modify the operation sub table

PHP combined with MySQL 10 million level data processing

There is a problem in this modification. The data of the main table and the sub table will be inconsistent. How to make the data of the main table and the word table consistent?

Redis queue keeps the data of main table and sub table consistent

After modification, the data of the main table will be modified and stored in the redis queue

PHP combined with MySQL 10 million level data processing

Then, Linux timed task (continuous) circulates the SQL statements in the redis queue to update the contents of the main table synchronously

PHP combined with MySQL 10 million level data processing

MySQL distributed sub table (query, delete)

Query only needs to query the sub table, not the general table

PHP combined with MySQL 10 million level data processing

To delete, first find the sub table to be deleted according to the ID, then delete it, and then press an SQL statement to delete the total table data into the message queue

Then execute the timing task to delete the master table data

PHP combined with MySQL 10 million level data processing

Scheduled tasks:

PHP combined with MySQL 10 million level data processing

Distributed branch database of MySQL

Thinking of sub database

PHP combined with MySQL 10 million level data processing

Schematic diagram of branch library:

PHP combined with MySQL 10 million level data processing

PHP combined with MySQL 10 million level data processing

MySQL distributed database (add)

PHP combined with MySQL 10 million level data processing

Note: after operating a database, you must close the database connection, otherwise MySQL will think that the same database is always connected

Or is the module taken to determine which configuration file to load and which database to connect to

PHP combined with MySQL 10 million level data processing

Distributed branch database of MySQL

The principle is the same as the new one

PHP combined with MySQL 10 million level data processing

MySQL distributed sub database (search, delete)

The principle is similar

PHP combined with MySQL 10 million level data processing

delete

PHP combined with MySQL 10 million level data processing

Execution queue

Application of distributed cache (Memcache) in MySQL

Put the data into the cache to save the cost of the database. First go to the cache to check. If there is any, take it out directly. If there is no, go to the database to check, and then store it in the cache

PHP combined with MySQL 10 million level data processing

After editing the information, you need to delete the cache, otherwise you will always read the cached data instead of the modified data

PHP combined with MySQL 10 million level data processing


php

This work adoptsCC agreementReprint must indicate the author and the link of this article