How to Improve MYSQL Performance

I would like to share several methods to improve MYSQL performance, which are not comprehensive enough. I hope you can add them! Please make a picture of the error!

1. database/table sharding

Obviously, the unlimited growth of a primary table (that is, a very important table, such as a user table) will inevitably seriously affect performance. Database sharding and table sharding are a good solution, this is also the way to optimize performance. In this case, we have a more than 10 million-record user table members, which is very slow to query. My colleague's approach is to hash it to 100 tables, from members0 to members99 respectively, and then distribute the records to these tables based on the mid distribution records. The awesome code is 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 the mysql table structure without stopping services

As for the members table, the structure of the table designed in the earlier stage is not reasonable. As the database continues to run, its redundant data also increases dramatically. Colleagues used the following methods to deal with it:

Create a temporary table first:
/* Create a temporary table */
Create table members_tmp LIKE members

Modify the table structure of members_tmp to a new structure, and then use the for loop above to export data. Because 10 million of data is exported at one time, mid is the primary key, and the data is exported within one interval, it is about exporting 50 thousand entries at a time. I skipped them here.
Rename the table and replace it with the following:

/* This is a classic statement */
Rename table members TO members_bak, members_tmp TO members;

In this way, the table structure can be updated without downtime, but the table is actually locked during RENAME, so it is a skill to operate when the number of online users is small. After this operation, the original 8 GB tables suddenly become more than 2 GB

In addition, we also talked about the strange phenomenon of float field type in mysql, that is, the numbers seen in pma cannot be queried as conditions at all. Thanks to zj for sharing them.

Articles you may be interested in

• How to load javascript to improve website performance
• Summary of practical methods to improve PHP Performance
• MYSQL performance optimization sharing (database/table sharding)
• How to Improve gastrointestinal absorption and improve Gastrointestinal Function
• Use the PHP function memory_get_usage to obtain the current PHP memory consumption for program performance optimization.
• PHP checks browser parameters to prevent SQL injection.
• Performance Comparison of using in_array () foreach array_search () to find whether an array contains
• Top 10 Best Web Performance Testing Software Tools