MySQL Sub-Library table and non-stop change table structure

in the MySQL Sub-Library table we are generally based on a large amount of time on the MySQL database an optimization approach, below I briefly introduce MySQL sub-table and the simple way of the library. 1, the sub-database is obvious, 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 way to solve, that is, performance optimization approach, now the case is that we have a 1000多万条 record of the user table members, The query is very slow, the practice of the colleague is to hash it into 100 tables, from Members0 to Members99, and then distribute the records to these tables according to the mid, the great code is like this: code to copy 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, do not stop to modify the MySQL table structure is 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 method to deal with: first create a temporary table: code as follows copy code/*Create a temporary table*/CREATE table members_tmp like members then modifies the MEMBERS_TMP table structure to the new structure, then uses the above for loop to export the data because 10 million of the data is not exported at once, and mid is the primary key. An interval of an interval of the guide, the basic is an export 50,000 bar, here omitted to rename the new table replaced: Code as follows to copy the Code/*It's a pretty classic statement, huh?*/RENAME Table members to members_bak,members_tmp to members; that is, basically can do without loss, no downtime Update table structure, but actually RENAME period table is locked, So it's a trick to choose how to do it when you're low online. After this operation, so that the original 8G of the table, and suddenly become more than 2G In addition also talked about the type of float field in MySQL when the strange phenomenon, that is seen in the PMA can not be used as a condition to query the number. Thanks for the fresh sharing of ZJ classmates. 

MySQL Sub-Library table and non-stop change table structure