MySQL Simple optimization

The company upgraded the server configuration, the memory crunch problem solved, and thus thought of several simple ways to optimize MySQL. First, two things are clear, our business scenario is a common high concurrency Web service, the query speed is the top priority. There is the optimization before you must back up the database, otherwise the problem can only run.

1. Using a solid-state drive

This optimization method has no technical content, is entirely to pay for performance, but have to say that this method is quite simple, rough, effective. High-frequency read and write speed of SSD can greatly improve the performance of MySQL

2. Add an Index

When the amount of data reaches a certain level, it is necessary to add an appropriate index. MySQL is a B + tree or its variant tree, which sorts the data according to the index structure to optimize the query effect. If the data in the table is frequently inserted and deleted, these operations can damage the index, causing the index to take up a lot of invalid space. I've seen a data sheet. So much larger than the amount of data, the query speed is very slow or not to add indexes, because of frequent insertion and deletion caused by the need to periodically delete the index and then rebuild the index

3. Remove the foreign key

During the university, the teacher emphasized that it is necessary to use foreign keys to constrain data consistency. At school I also strictly adhere to this point of view, after all, it allows me to write less code, the consistency of the problem to throw the database. After working with real business scenarios, databases are often a performance bottleneck, and services do not become performance bottlenecks, so it is necessary to write solutions to data consistency issues into the service to reduce database stress.

4. Using InnoDB

Many facts suggest that InnoDB is more advantageous than MyISAM. InnoDB use of memory more fully, memory access speed is obviously faster than the disk

5. Setting InnoDB Memory

The Innodb_buffer_pool_size parameter represents the amount of memory allocated to the InnoDB, leaving enough memory for the operating system when allocating memory. According to the colleague's experience is that you can allocate server 80% of memory for Innodb_buffer_pool_size, of course, the precondition is that your service is basically only MySQL, if there is data parsing on the server and other very memory-consuming services, innodb_ Buffer_pool_size values to be reduced as appropriate

6. Set InnoDB multi-tasking

If the memory allocated to Innodb_buffer_pool_size is greater than 2G, we can consider dividing the INNODB buffer pool into multiple, and we can modify the Innodb_buffer_pool_instances parameter in the configuration. For high concurrency services, performance bottlenecks tend to be multi-threaded access to MySQL, and dividing more buffer pools can alleviate this problem effectively. Of course, the buffer pool is not the more the better, each buffer pool memory is too low to play the advantages of multiple buffer pools. The official recommendation is that each buffer pool require at least 1G of memory.

Finally, to remind you that the configuration changes to MySQL need to restart MySQL to take effect

MySQL Simple optimization