MYSQL performance optimization sharing (database/table sharding)

1. database/table sharding

Obviously, the unlimited growth of a primary table (that is, a very important table, such as a user table) will inevitably seriously affect performance. Database sharding and table sharding are a good solution, this is also the way to optimize performance. In this case, we have a more than 10 million-record user table members, which is very slow to query. My colleague's approach is to hash it to 100 tables, from members0 to members99 respectively, and then distribute the records to these tables based on the mid distribution records. The awesome code is like this:
Copy codeThe Code is as follows:
<? Php
For ($ I = 0; I I <100; $ I ++ ){
// Echo "create table db2.members {$ I} LIKE db1.members <br> ";
Echo "insert into members {$ I} SELECT * FROM members WHERE mid % 100 ={$ I} <br> ";
}
?>


2. Modify the mysql table structure without stopping services

As for the members table, the structure of the table designed in the earlier stage is not reasonable. As the database continues to run, its redundant data also increases dramatically. Colleagues used the following methods to deal with it:

Create a temporary table first:
/* Create a temporary table */
Create table members_tmp LIKE members

Modify the table structure of members_tmp to a new structure, and then use the for loop above to export data. Because 10 million of data is exported at one time, mid is the primary key, and the data is exported within one interval, it is about exporting 50 thousand entries at a time. I skipped them here.
Rename the table and replace it with the following:

/* This is a classic statement */
Rename table members TO members_bak, members_tmp TO members;

In this way, the table structure can be updated without downtime, but the table is actually locked during RENAME, so it is a skill to operate when the number of online users is small. After this operation, the original 8 GB tables suddenly become more than 2 GB

In addition, we also talked about the strange phenomenon of float field type in mysql, that is, the numbers seen in pma cannot be queried as conditions at all. Thanks to zj for sharing them.