MySQL performance optimization

One, 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, divide the table from Members0 to Memebers99, and then distribute the records to these tables according to the Mid,

The 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>";
}
?>

Second, do not stop to modify the MySQL table structure
Also is 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 1 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,

Then rename the new table to replace it:

/* This is a classic statement yo */

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 is locked, so the choice of online less time operation is a skill, after this operation, so that the original 8G more than the table, suddenly become more than 2G

In addition, the fload of the type of field in MySQL is a strange phenomenon, that is, the number seen in the PMA can not be queried as a condition

MySQL performance optimization