Shulou(Shulou.com)06/02 Report--

This article mainly explains "how to solve the problem of slow MySQL left join query". The content of the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "how to solve the problem of slow MySQL left join query".

Why did this problem arise?

In the process of work, several tables in the sql server database should be migrated to MySQL, because there are many different dialects and functions of the database that need to be replaced. After the replacement is completed, a problem is found that the same associated query statement only takes about 0.2 seconds in sql server and 11 seconds in MySQL.

MySQL sqlSELECT a.estate_name AS estateName, a.location AS estateLocation, IFNULL (b. Number of listings, 0) AS numberListed, IFNULL (c. Number of transactions, 0) AS tradingVolume FROM (SELECT CONCAT (IFNULL (estate_name,'), IFNULL (area_name,')) AS ea, estate_name, MAX (location) AS location FROM beike_estate GROUP BY estate_name Area_name) AS a LEFT JOIN (SELECT estate_name, COUNT (estate_name) AS listing number FROM beike_property WHERE estate_name IS NOT NULL GROUP BY estate_name) AS b ON a.estate_name = b.estate_name LEFT JOIN (IFNULL (estate_name,''), IFNULL (area_name,'') AS ea COUNT (estate_name) AS transaction quantity FROM crawler_publish_property WHERE `status` = 1 GROUP BY estate_name, area_name) AS c ON a.ea = c.easql server sqlSELECTa.estate_name AS estateName,a.location AS estateLocation,ISNULL (b. Number of listings, 0) AS numberListed,ISNULL (c. Number of transactions, 0) AS tradingVolume FROM (SELECT ISNULL (estate_name,') + ISNULL (area_name,') AS ea, estate_name, MAX (location) AS location FROM beike_estate GROUP BY estate_name Area_name) AS a LEFT JOIN (SELECT estate_name, COUNT (estate_name) AS listing number FROM beike_property WHERE estate_name IS NOT NULL GROUP BY estate_name) AS b ON a.estate_name = b.estate_name LEFT JOIN (SELECT ISNULL (estate_name,'') + ISNULL (area_name,'') AS ea COUNT (estate_name) AS transaction quantity FROM crawler_publish_property WHERE [status] = 1 GROUP BY estate_name, area_name) AS c ON a.ea = c.ea

You can see that except for the functional difference, there is basically no difference in the two sentences of sql.

Why the query speed of using MySQL lift join is too slow

Since there is no difference, why is the execution speed of MySQL so slow?

The first thing that comes to mind when the query is too slow is to add an index, but this sql is an associated query of three temporary tables aggregated by three table queries. Because there is no way to create indexes in temporary tables, I first add indexes on the three original tables, and then execute them again. The speed is still more than 10 seconds as before, and it has not been optimized. Use EXPLAIN to analyze this sql, and sure enough, no index is used.

Since the index cannot be added, we have to look for other solutions. After some time, Baidu learned that for the connected table MySQL, there are 2 algorithms for join, respectively.

Nested Loop Join algorithm

NLJ algorithm: take the result set of the driving table / external table as the circular basic data, and then loop to get the data from the result set one at a time as the filter condition of the next table to query the data, and then merge the results. If you have multiple tables join, take the result set of the previous table as circular data, take each row to the next table of the join to match in a loop, get the result set and return it to the client.

Block Nested Loop Join algorithm

BNL algorithm: the row / result set of the outer loop is stored in join buffer, and each row of the inner loop is compared with the records in the entire buffer, thus reducing the number of inner loops.

Well, is it because the lift join statement does not use the Block Nested Loop algorithm, so it is very slow to use the EXPLAIN analysis to find that the Block Nested Loop algorithm is already used, so it is not this reason.

After some time on Baidu, I learned that MySQL has a configuration of Join_buffer_size, which controls the cache size of MySQ join queries. The default configuration of Join_buffer_size is 128k. Well, is it because the cache area is too small that the query speed is too slow? I went to inquire about it.

The results show that 256m of memory is available in the cache area, which means that the slow query speed is not caused by this reason.

After a period of Baidu, it is found that it is not because of other reasons, that is, the simple MySQL is inefficient in dealing with join.

Solution

Since it is impossible to optimize in the database, it can only be optimized in the server layer.

Since it is lift join, you only need to paginate the left table and then use multiple threads to query, and then encapsulate and return the query after the completion of multiple threads.

Public List findEstateMsg () {List list = beiKePropertyMapper.findEstateMsg (); Integer I = beiKePropertyMapper.findEstateCount (); / / find out the total number first I = (I / 1000) + 1; / / calculate the number of threads Integer row = 1000; CountDownLatch countDownLatch = new CountDownLatch (I); / / thread counter List bkFindEstateMsgDTOS = new ArrayList (); for (thread j = 0; j

{/ / Multithreaded simultaneous query List list = beiKePropertyMapper.findEstateMsg1 (j1roomrowMagrow); bkFindEstateMsgDTOS.addAll (list); countDownLatch.countDown (); / / submission counter});} try {countDownLatch.await () / / all threads complete submission} catch (Exception e) {e.printStackTrace ();}}

After using multithreading, it only takes about 2 seconds to execute.

If you don't want too many threads to split the sql into two lift join query statements, use two threads to query at the same time, and the second query statement returns the map collection, taking the conditions that need to join the table as key and the query result as value. After the query comes out, you can traverse the first result set and retrieve the corresponding value set to the corresponding object through key. So the final query result is about 6 seconds.

To return the specified column as the key of the map collection, you only need to add the @ MapKey ("") annotation to the dao layer interface method.

Thank you for your reading, the above is the content of "how to solve the problem of slow MySQL left join query". After the study of this article, I believe you have a deeper understanding of how to solve the problem of slow MySQL left join query, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.