MySQL performance optimization Sharing (sub-Library sub-table)

1, sub-database sub-table

Obviously, a main table (that is, very important tables, such as user tables) unlimited growth is bound to seriously affect performance, sub-library and sub-table is a very good solution, that is, the performance optimization approach, now the case is that we have a 1000多万条 record of the user table members, query very slow, A colleague's approach is to hash it into 100 tables, from Members0 to Members99, and then distribute the records to these tables based on the mid, which is probably what the awesome code looks like:

Copy CodeThe code is as follows:
<?php
for ($i =0; $i < $i + +) {
echo "CREATE TABLE db2.members{$i} like Db1.members<br>";
echo "INSERT into members{$i} SELECT * from the members WHERE mid%100={$i}<br>";
}
?>

2. Modify MySQL table structure without downtime

Also the members table, the design of the table structure is not reasonable, with the database constantly running, its redundant data is also huge growth, colleagues used the following methods to deal with:

Create a temporary table first:
/* Create temporary table */
CREATE TABLE members_tmp like members

Then modify the MEMBERS_TMP table structure for the new structure, and then use the above for loop to export the data, because 10 million of the data one-time export is not right, mid is the primary key, an interval of an interval of the guide, basically is an export 50,000 bar, here omitted
Then rename the new table to replace it:

/* This is a fairly classic statement, huh? */
RENAME TABLE members_bak,members_tmp to members;

That is, the basic can be done without loss, no downtime to update the table structure, but in fact, the Rename period table is locked, so the choice of less online when the operation is a skill. After this operation, so that the original 8G more than a table, suddenly become more than 2G

In addition, I also talked about the strange phenomenon of the type of float field in MySQL, that is, the numbers you see in the PMA simply cannot be queried as a condition. Thanks for the fresh sharing of ZJ classmates.

MySQL performance optimization Sharing (sub-Library sub-table)