MySQL performance optimization of the sub-database table with no downtime to modify the MySQL table structure, the need for friends can refer to the next
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 good solution, that is, the performance optimization approach, now the case is that we have a 1000多万条 record of the user table company, the query is very slow, A colleague's approach is to hash it into 100 tables, from Company0 to Company99, and then distribute the records to these tables based on the ID, which is probably the way the code looks:
Copy CodeThe code is as follows:
<?php
for ($i =0; $i < $i + +) {
echo "CREATE TABLE db2.company{$i} like db1.company;\n";
#echo "INSERT into company{$i} select * FROM company WHERE id%100={$i};\n";
}
2. Modify MySQL table structure without downtime
The same company table, the previous design of the table structure is not reasonable, with the database constantly running, its redundant data is also a huge growth, colleagues used the following methods to deal with:
Create a temporary table first:
/* Create temporary table */
CREATE TABLE company_tmp like company
Then modify the COMPANY_TMP table structure for the new structure, and then use the above for the loop to export the data, because 10 million of the data one-time export is not correct, ID 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:
RENAME TABLE Company to Company_bak,company_tmp to company;
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.
MySQL performance optimization Sharing (sub-Library sub-table)
