這篇文章主要講解了“如何解決MySQL left join 查詢過慢的問題”，文中的講解內(nèi)容簡(jiǎn)單清晰，易于學(xué)習(xí)與理解，下面請(qǐng)大家跟著小編的思路慢慢深入，一起來研究和學(xué)習(xí)“如何解決MySQL left join 查詢過慢的問題”吧！

創(chuàng)新互聯(lián)建站主營(yíng)巴楚網(wǎng)站建設(shè)的網(wǎng)絡(luò)公司,主營(yíng)網(wǎng)站建設(shè)方案,重慶App定制開發(fā),巴楚h5小程序定制開發(fā)搭建,巴楚網(wǎng)站營(yíng)銷推廣歡迎巴楚等地區(qū)企業(yè)咨詢

為什么會(huì)出現(xiàn)這個(gè)問題

在工作的過程中要把sql server 數(shù)據(jù)庫中的幾個(gè)表遷移到MySQL當(dāng)中，以為數(shù)據(jù)庫的方言和函數(shù)不同很多地方需要替換。在替換完成之后發(fā)現(xiàn)了一個(gè)問題，同樣的一句關(guān)聯(lián)查詢語句在sql server總只需要0.2秒左右，在MySQL中卻需要11秒左右。

MySQL sql

SELECT
	a.estate_name AS estateName,
	a.location AS estateLocation,
	IFNULL( b.掛牌數(shù)量, 0 ) AS numberListed,
	IFNULL( c.成交數(shù)量, 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 掛牌數(shù)量 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
		CONCAT( IFNULL( estate_name, '' ), IFNULL( area_name, '' ) ) AS ea,
		COUNT( estate_name ) AS 成交數(shù)量 
	FROM
		crawler_publish_property 
	WHERE
		`status` = 1 
	GROUP BY
	estate_name,
	area_name 
	) AS c ON a.ea = c.ea

sql server sql

SELECT
a.estate_name AS estateName,
a.location AS estateLocation,
ISNULL( b.掛牌數(shù)量, 0 ) AS numberListed,
ISNULL( c.成交數(shù)量, 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 掛牌數(shù)量 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 成交數(shù)量 
	FROM
		crawler_publish_property 
	WHERE
		[status] = 1 
	GROUP BY
		estate_name,
		area_name 
	) AS c ON a.ea = c.ea

可以看到2句sql除了函數(shù)上的區(qū)別，其他地方基本沒有區(qū)別。

為什么使用MySQL lift join 查詢速度過慢

既然沒有區(qū)別為什么MySQL執(zhí)行速度回這么慢呢？

查詢過慢先想到的就是添加索引，但是這句sql是有三張表查詢聚合出來的三張臨時(shí)表關(guān)聯(lián)查詢，由于臨時(shí)表并沒有辦法創(chuàng)建索引，我先在三張?jiān)急砩咸砑恿怂饕?，然后再次?zhí)行，速度還是和之前一樣還是10多秒，并沒有得到優(yōu)化。使用 EXPLAIN 分析了一下這條sql，果然并沒有使用到索引。

既然索引加不了，那就只能尋找其他解決方案了。經(jīng)過一番百度了解到對(duì)于連表MySQL有2中join的算法分別是

Nested Loop Join算法

NLJ 算法:將驅(qū)動(dòng)表/外部表的結(jié)果集作為循環(huán)基礎(chǔ)數(shù)據(jù)，然后循環(huán)從該結(jié)果集每次一條獲取數(shù)據(jù)作為下一個(gè)表的過濾條件查詢數(shù)據(jù)，然后合并結(jié)果。如果有多表join，則將前面的表的結(jié)果集作為循環(huán)數(shù)據(jù)，取到每行再到聯(lián)接的下一個(gè)表中循環(huán)匹配，獲取結(jié)果集返回給客戶端。

Block Nested Loop Join算法

BNL 算法:將外層循環(huán)的行/結(jié)果集存入join buffer, 內(nèi)層循環(huán)的每一行與整個(gè)buffer中的記錄做比較，從而減少內(nèi)層循環(huán)的次數(shù)。

那么是不是因?yàn)閘ift join語句沒有使用 Block Nested Loop算法所以很慢呢使用EXPLAIN分析發(fā)現(xiàn)使用的已經(jīng)是Block Nested Loop算法了，所以也不是這個(gè)原因。

經(jīng)過一番百度我了解到MySQL有一個(gè)Join_buffer_size的配置，這個(gè)配置是控制MySQ join 查詢的緩存區(qū)大小的配置，Join_buffer_size的默認(rèn)配置為128k。那么是不是由于這個(gè)緩存區(qū)太小導(dǎo)致查詢速度過慢呢，我去查詢了一下

結(jié)果顯示緩存區(qū)域有256m的內(nèi)存可供使用，也就說明查詢速度慢并不是這個(gè)原因?qū)е碌摹?/p>

經(jīng)過一番百度，發(fā)現(xiàn)并不是因?yàn)槠渌?，就是單純的MySQL對(duì)join的處理效率不行。

解決方案

既然在數(shù)據(jù)庫庫陳無法進(jìn)行優(yōu)化，那么只能在server層進(jìn)行優(yōu)化了

既然是lift join那么只需要把左表進(jìn)行分頁查詢?cè)偈褂枚鄠€(gè)線程去查詢，多個(gè)線程查詢完成后再封裝返回。

public List findEstateMsg(){
	        List list = beiKePropertyMapper.findEstateMsg();

        Integer i = beiKePropertyMapper.findEstateCount(); // 先查出總數(shù)目

        i = (i / 1000) + 1; // 計(jì)算需要幾個(gè)線程

        Integer row = 1000;

        CountDownLatch countDownLatch = new CountDownLatch(i); // 線程計(jì)數(shù)器

        List bkFindEstateMsgDTOS = new ArrayList<>();


        for (int j = 0; j < i; j++) {
            int j1 = j;
            executorService.execute(() -> { // 多線程同時(shí)查詢
                List list = beiKePropertyMapper.findEstateMsg1(j1*row,row);
                bkFindEstateMsgDTOS.addAll(list);
                countDownLatch.countDown(); // 提交計(jì)數(shù)器
            });
        }
	try {
          countDownLatch.await(); // 所有線程完成提交
        } catch (Exception e) {
            e.printStackTrace();
        }
}

使用多線程之后只需要2秒左右就可執(zhí)行完畢。

如果不想線程太多可以將sql拆分為2個(gè)lift join的查詢語句，使用2個(gè)線程同時(shí)進(jìn)行查詢，第二條查詢語句返回map集合，將需要連表的條件作為key，查詢結(jié)果作為value，查詢出來之后遍歷第一個(gè)結(jié)果集合通過key取出對(duì)應(yīng)的value set到對(duì)應(yīng)的對(duì)象當(dāng)中即可。這樣最后的查詢結(jié)果在6秒左右。

返回指定的列作為map集合的key只需要在dao層接口方法上添加@MapKey("")注解即可。

感謝各位的閱讀，以上就是“如何解決MySQL left join 查詢過慢的問題”的內(nèi)容了，經(jīng)過本文的學(xué)習(xí)后，相信大家對(duì)如何解決MySQL left join 查詢過慢的問題這一問題有了更深刻的體會(huì)，具體使用情況還需要大家實(shí)踐驗(yàn)證。這里是創(chuàng)新互聯(lián)，小編將為大家推送更多相關(guān)知識(shí)點(diǎn)的文章，歡迎關(guān)注！