一次MySQL left join 查询过慢的解决过程

为什么会出现这个问题

在工作的过程中要把sql server 数据库中的几个表迁移到MySQL当中，以为数据库的方言和函数不同很多地方需要替换。在替换完成之后发现了一个问题，同样的一句关联查询语句在sql server总只需要0.2秒左右，在MySQL中却需要11秒左右。

MySQL sql

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

可以看到2句sql除了函数上的区别，其他地方基本没有区别。

为什么使用MySQL lift join 查询速度过慢

既然没有区别为什么MySQL执行速度回这么慢呢？

查询过慢先想到的就是添加索引，但是这句sql是有三张表查询聚合出来的三张临时表关联查询，由于临时表并没有办法创建索引，我先在三张原始表上添加了索引，然后再次执行，速度还是和之前一样还是10多秒，并没有得到优化。使用 EXPLAIN 分析了一下这条sql，果然并没有使用到索引。

既然索引加不了，那就只能寻找其他解决方案了。经过一番百度了解到对于连表MySQL有2中join的算法分别是

Nested Loop Join算法

NLJ 算法:将驱动表/外部表的结果集作为循环基础数据，然后循环从该结果集每次一条获取数据作为下一个表的过滤条件查询数据，然后合并结果。如果有多表join，则将前面的表的结果集作为循环数据，取到每行再到联接的下一个表中循环匹配，获取结果集返回给客户端。

Block Nested Loop Join算法

BNL 算法:将外层循环的行/结果集存入join buffer, 内层循环的每一行与整个buffer中的记录做比较，从而减少内层循环的次数。

那么是不是因为lift join语句没有使用 Block Nested Loop算法所以很慢呢使用EXPLAIN分析发现使用的已经是Block Nested Loop算法了，所以也不是这个原因。

经过一番百度我了解到MySQL有一个Join_buffer_size的配置，这个配置是控制MySQ join 查询的缓存区大小的配置，Join_buffer_size的默认配置为128k。那么是不是由于这个缓存区太小导致查询速度过慢呢，我去查询了一下

结果显示缓存区域有256m的内存可供使用，也就说明查询速度慢并不是这个原因导致的。

经过一番百度，发现并不是因为其他原因，就是单纯的MySQL对join的处理效率不行。

解决方案

既然在数据库库陈无法进行优化，那么只能在server层进行优化了

既然是lift join那么只需要把左表进行分页查询再使用多个线程去查询，多个线程查询完成后再封装返回。

public List<BkFindEstateMsgDTO> findEstateMsg(){
	        List<BkFindEstateMsgDTO> list = beiKePropertyMapper.findEstateMsg();

        Integer i = beiKePropertyMapper.findEstateCount(); // 先查出总数目

        i = (i / 1000) + 1; // 计算需要几个线程

        Integer row = 1000;

        CountDownLatch countDownLatch = new CountDownLatch(i); // 线程计数器

        List<BkFindEstateMsgDTO> bkFindEstateMsgDTOS = new ArrayList<>();


        for (int j = 0; j < i; j++) {
            int j1 = j;
            executorService.execute(() -> { // 多线程同时查询
                List<BkFindEstateMsgDTO> list = beiKePropertyMapper.findEstateMsg1(j1*row,row);
                bkFindEstateMsgDTOS.addAll(list);
                countDownLatch.countDown(); // 提交计数器
            });
        }
	try {
          countDownLatch.await(); // 所有线程完成提交
        } catch (Exception e) {
            e.printStackTrace();
        }
}

使用多线程之后只需要2秒左右就可执行完毕。

如果不想线程太多可以将sql拆分为2个lift join的查询语句，使用2个线程同时进行查询，第二条查询语句返回map集合，将需要连表的条件作为key，查询结果作为value，查询出来之后遍历第一个结果集合通过key取出对应的value set到对应的对象当中即可。这样最后的查询结果在6秒左右。

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

本文采用署名-非商业性使用-相同方式共享 4.0 国际许可协议，转载请注明出处。