MYSQL performance optimization sharing of library sub table
- 2020-05-12 06:21:11
- OfStack
1. Library sub-table
Obviously, table 1 (also is very important to table, for example, the user table) unlimited growth will severely affect the performance, depots and table is a very good solution, namely performance optimization approach, 1 case is that we now have more than 1000 ten thousand records of the user table members, query up is very slow, colleagues, it is the hash to the 100 table, from members0 to members99 respectively, then according to distribute mid records in these tables, cow force of the code is probably like this:
2. Modify mysql table structure without stopping
The structure of the table designed in the early stage is not reasonable. As the database continues to run, its redundant data also grows greatly. My colleagues used the following methods to deal with it:
Create a temporary table first:
/* create temporary tables */
CREATE TABLE members_tmp LIKE members
Then modify the table structure of members_tmp into a new structure, and then use the above for loop to export data, because it is not right to export 10 million data once. mid is the primary key, and the derivative of one interval is one interval. It is basically exporting 50,000 pieces once, which is omitted here
Then rename to replace the new table:
/* this is a pretty classic statement, ha */
RENAME TABLE members TO members_bak,members_tmp TO members;
In this way, you can basically achieve loss-free, without downtime to update the table structure, but actually the RENAME table is locked during the period, so it is a trick to choose to operate when there is less online. As a result of this operation, the table with more 8G has been changed from 1 to 2G
In addition, we also talked about the weird phenomenon of float field type in mysql, that is, the number seen in pma cannot be queried as a condition at all. Thanks for zj's fresh sharing.
Obviously, table 1 (also is very important to table, for example, the user table) unlimited growth will severely affect the performance, depots and table is a very good solution, namely performance optimization approach, 1 case is that we now have more than 1000 ten thousand records of the user table members, query up is very slow, colleagues, it is the hash to the 100 table, from members0 to members99 respectively, then according to distribute mid records in these tables, cow force of the code is probably like this:
<?php
for($i=0;$i< 100; $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 stopping
The structure of the table designed in the early stage is not reasonable. As the database continues to run, its redundant data also grows greatly. My colleagues used the following methods to deal with it:
Create a temporary table first:
/* create temporary tables */
CREATE TABLE members_tmp LIKE members
Then modify the table structure of members_tmp into a new structure, and then use the above for loop to export data, because it is not right to export 10 million data once. mid is the primary key, and the derivative of one interval is one interval. It is basically exporting 50,000 pieces once, which is omitted here
Then rename to replace the new table:
/* this is a pretty classic statement, ha */
RENAME TABLE members TO members_bak,members_tmp TO members;
In this way, you can basically achieve loss-free, without downtime to update the table structure, but actually the RENAME table is locked during the period, so it is a trick to choose to operate when there is less online. As a result of this operation, the table with more 8G has been changed from 1 to 2G
In addition, we also talked about the weird phenomenon of float field type in mysql, that is, the number seen in pma cannot be queried as a condition at all. Thanks for zj's fresh sharing.