Some summary of MySQL optimization

Under normal circumstances, the start-up traffic is not very large, MySQL database in the absence of optimization can still meet performance requirements, especially after the 5.6 version of MySQL has a significant performance improvement, so in the early days did not spend energy on this. However, a series of subsequent problems still illustrate that MySQL performance optimization should be taken into account when the project starts. The following are the reasons for the problem and the areas needing attention as follows:

source of the problem

Most of the developers used Oracle before, and because Oracle's powerful performance and projects are mostly internal enterprise applications, performance issues are unlikely to occur. In this context, developers still follow the original way to write code, resulting in later performance problems, summed up the following points:

1.SQL Multi-table joint query, usually 3, 4 tables joint query

Using a number of functions in 2.sql

Direct SELECT * in 3.sql

4. Table fields are missing an index or indexed in a wrong way

5. Table design is poorly conceived and there are lots of empty fields

6. Table primary key is designed as UUID, due to the use of traditional mechanical hard disk, the addressing is very unfavorable

7. Excessive use of triggers

Complex calculations exist in 8.sql

Solution Solutions

First, the slow log to filter out more than 1s of SQL statements, to solve the MySQL performance problem, be sure to take advantage of slow logs. Then use the execution Plan (explain) to see how SQL is performing. The specific optimization measures are as follows:

union queries split into single-table queries

If this SQL is a federated query, first verify that it is possible to split into a single-table query and then process the data through the program. A maximum of two table union queries cannot be exceeded.

build an appropriate index

Through the execution of the plan, the query for the full table scan must be indexed, when the index, it is important to consider whether this field has a large number of empty fields, the field value is a large number of repetitions, the degree of distinction is high, otherwise the index is not meaningful, but the impact of insert and delete operations. For fields with long character types, increase the count by adding a prefix, calculated as: Select COUNT (distinct left (b,5))/count (distinct B) as Left5,count (distinct left (b,6))/ COUNT (distinct B) as left6 from Test_unique
Limit 1, where 5, 6 is a pre-estimate, and for a long character type such as UUID, the general prefix is 6. When you increase the index, the SQL statement is: ALTER TABLE Test.test_unique add key (left (6)); For fields that cannot be duplicated, it is recommended to use unique indexes, one is to ensure that the insertion value is unique, and the other is to increase the query speed. In the fields following the where, order, group by, as far as possible, it is important to note that if the where is followed by multiple fields, you need to establish a federated index instead of a single index, and pay attention to the order of the federated indexes, such as where a= ' x ' and B = ' Y ' , where is present in the other SQL
B= ' Z ', then the Federated Index Order is (B,A), rather than establishing (a, b) and (b) Two indexes separately, because when a federated index (B,A) is established, MySQL builds two indexes (b), (b,a) two indexes.

Avoid index invalidation

Using a function on a field will invalidate the index, so be sure to avoid using the function in the left-hand field, but rather work with the program in advance.

avoid functions as much as possible

MySQL provides a number of functions, but for these functions, try not to use, but in the program processing, currently MySQL for these function optimization work is not very good, and sometimes lead to serious performance problems.

table primary key using self-increment sequence

Table primary key as far as possible to use the self-increment sequence, this can be full of MySQL storage features, mysql with B + Tree storage. It is important to note that if you are a sub-database, you need not use the self-increment sequence directly, there are other ways to do it, and a common way to maintain a set of IDs through Redis.

Some summary of MySQL optimization