Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about how to solve the slow MySQL query sentence, which may not be well understood by many people. in order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.

Optimization of join query

No matter what kind of database, the query cost of multi-table join is relatively high, so for highly concurrent applications, we should try to reduce the number of joined queries, and the number of multi-table joins should not exceed 4 tables. Generally speaking, when the amount of data is small, the connection is small or small, and there are generally no performance problems, but when the amount of data becomes larger, then the performance problems will be more prominent. So at the beginning of the database, it is best to determine which table can become a large table, and then reverse the normal form design to reduce the join table, such as adding redundant fields, etc., or do join calculation in the business code.

Some experience summary points:

1. The columns in ON and USING sentences confirm that there is an index. If the join order is B or A, then you only need to create an index on the column of Table A, without indexing in B, which can reduce unnecessary indexing overhead.

Examples of enquiries:

SELECT B. Magnum A.* FROM B JOIN An ON B.col1 = A.col2

MYSQL scans the whole table of table B and looks for table A records on each row of table B, so it is necessary to use the index on the COL2 column of table A to improve efficiency.

2. Use EXPLAIN to check the connection and look at the ROWS column. If the value of the column is too high, such as thousands or tens of thousands, then you need to consider whether the index is invalid and the order of the join table is not correct.

3. Consider the implementation of join query in the application layer. For example, you can decompose the complex query into several simple queries in JAVA to get a smaller set of results. After processing the traversal, and then get the complete data according to the conditions, this is often more efficient, because the data is separated, it is less easy to change, and it is beneficial to the database to cache data.

Examples are as follows:

SELECT A. * FROM A WHERE a.id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)

If the id=1~8 records are already stored in the cache REDIS, then we only need to query the data of id=9 and 10, which reduces a lot of database connection interaction and improves performance.

Optimization of GROUP BY, DISTINCT and ORDER BY statements

These statements are sorted by ORDER BY by default, and the idea of optimization is similar.

1. If more than one table makes a join query, the column of ORDER BY should belong to the first table in the join order. If you are not in the same table, consider redundant columns or merge tables.

2. You need to make sure that the index column and the ORDER BY column are the same, and that each column is sorted in the same direction.

3. Specify ORDER BY NULL. By default, MYSQL will sort all GROUP BY queries. If you want to avoid the consumption caused by sorting results, you can specify ORDER BY NULL.

Examples are as follows:

Select count (1) from sys_dept group by dept_id order by null limit 3 subquery optimization

Because the readability of subqueries is more in line with the thinking habits of developers, they are used to writing subqueries, but subqueries are the most common performance bottleneck in the production environment.

For databases, in most cases, connections are faster than subqueries, and optimizers can generally generate better execution plans, load data with cosine, process queries more efficiently, and generate temporary tables without indexes, so they are less efficient.

In current practice, subqueries should be rewritten as JOIN as possible.

Take a common example.

SELECT C1 FROM T1 where t1.c1 IN (SELECT C1 FROM T2)

We can convert it into a way of connecting:

SELECT C1 FROM t1.c1 FROM T1 optimized IN list for T2 WHERE t1.c1=t2.c2

For IN lists, MySQL sorts the values and uses binary lookup to locate the data, so it's useless to rewrite IN sentences into OR. The IN list is not recommended to be too long, and for high concurrency services, no more than a few dozen are recommended. The optimization idea can be transformed into multiple equal queries. For example, in the following statement, if there are a lot of ID values, the performance will not be very good.

SELECT * FROM A where A.ID IN (SELECT id FROM B)

Optimization ideas:

You can start from the business layer of the program, first query SELECT id FROM B, then get the value of ID, gradually splice it with SELECT * FROM A, and convert it into SELECT * FROM A where ID =? In the form of.

Optimize UNION

The UNION statement removes duplicate records by default, and sorting operations are required. If the result set is very large, the cost will be very high. It is recommended to use UNION ALL statements as far as possible. For multiple UNION sub-table scenarios, you should determine the data uniqueness of each sub-table as far as possible when the database is divided into tables, so that you do not need to use UNION to repeat.

In addition, WHERE conditions outside the query statement are not applied to each individual UNION clause, so each UNION clause adds a where condition.

Optimize the query of BLOB and TEXT type fields

Because mysql memory temporary tables do not support BLOB and TEXT types, disk-based temporary tables will be used if the queries include them, and the performance will be very low, so if it is not necessary, the query conditions do not need these two types.

Optimization ideas:

1. If you have to, you can consider splitting the table and separating the BLOB and TEXT fields into separate tables.

2. If you have many large fields, consider merging them into one field. Storing one large 200KB is more efficient than storing 20 10KB.

3. Consider using COMPRESS () before storing.

After reading the above, do you have any further understanding of how to solve the slow MySQL query? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.