1, the Sub-Library table
Obviously, a primary table (that is, a very important table, for example, user table) Unlimited growth is bound to seriously affect performance, the library and the 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 up very slowly, A colleague's approach is to hash it out into 100 tables, from Members0 to Members99, and then to the tables according to mid distribution, which is probably the code:
Copy Code code 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 members WHERE mid%100={$i}<br>";
}
?>
2. Modify MySQL table structure without downtime
Also the members table, the early design of the table structure is not reasonable, as the database continues to run, its redundant data is also a huge increase, colleagues use 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 correct, mid is the primary key, an interval of a guide, basically is one export 50,000 bar, here omitted
Next rename replaces the new table:
* * This is a rather classic statement.
RENAME TABLE members to members_bak,members_tmp to the members;
In this way, the basic can do without loss, no downtime to update the table structure, but in fact, the table is locked rename, so the choice of less online operation is a skill. After this operation, so that the original 8G many tables, suddenly become more than 2G
In addition to the MySQL in the Float field type when the strange phenomenon, that is, in the PMA see the number can not be used as a condition to query. Thanks for the fresh sharing of ZJ students.
