MySQL performance optimization

1. Optimized query for query cache

Most MySQL servers have query caching turned on, which is one of the most effective ways to improve performance, and this is handled by the MySQL database engine.

Some queries do not use caching, such as Curdate (), now (), Rand (), or any other SQL functions that do not open the query cache because these functions

Returns are variable, cannot be cached, can be replaced with a MySQL function, and the cache is opened by means of a parameter.

2. EXPLAIN Query Statement

Using the Explain keyword, you can analyze how MySQL handles SQL statements, and can help you analyze the performance bottlenecks of your own query statements or table structures.

Explain command analysis: reference

Https://www.cnblogs.com/gomysql/p/3720123.html

　　

Top columns of concern: Id,select_type,type,rows,extra

ID contains a set of numbers to view the order in which the SELECT clause or action table is executed in the query, and to view the precedence relationship

Select_type See the type of each SELECT clause (simple or complex) to avoid dependent subquery

Subquery efficiency relies on outer query

Type to see how the query finds the desired rows in the table, avoiding full table scans, using indexes or foreign key associations

Rows View SELECT statement to get the number of lines that the result needs to be read, optimized according to the number of rows

Extra view Some additional information, such as not using index sorting

3. Use limit 1 when only one row of data is used

When you query the table some time, if you know the query results will only have one, you can add limit 1, when the MySQL database engine found a piece of data

Instead of continuing to look for the next record-compliant data, you will stop searching

4. Index The search field

The index does not necessarily give the primary key or the unique field. Index The fields that are frequently searched in the table.

5. Use the equivalent type in the Join table and index it. such as through a foreign key association, or in two tables to store the same field data.

6. Avoid SELECT *

The more data you read from the database, the slower the query becomes, and if the database server and Web server are two separate servers,

It also increases the load on the network transport. Replace * with the field name.

7, anti-normalization design, the horizontal division of the table (with a certain condition column as a segmentation condition, such as the year)

Split vertically (divides a table's columns into different tables)

Consolidation of tables

Anti-Normalization of columns (complete with column replication, eliminating multiple connections between tables)

8. Split large DELETE or INSERT statements

Both of these operations result in a lock table, and the table cannot be entered if the other operation is locked. If the amount of traffic is large, a 30-second lock table may crash the Web service.

If there is a lot of processing, you need to split it and use the limit to divide it.

　　

　　

MySQL performance optimization