用STRAIGHT_JOIN优化mysql的执行速度

57 阅读 0 评论 38 点赞

我是靠谱客的博主唠叨蜗牛，最近开发中收集的这篇文章主要介绍用STRAIGHT_JOIN优化mysql的执行速度，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

优化sql的执行速度，我们通常需要查看EXPLAIN的结果，没有使用索引的加上索引，索引使用不对的也可以强制使用某个索引，但是有些情况下，这2招都不管用，是sql内部的执行顺序不正确，导致速度很慢。

执行顺序通常是mysql内部优化器根据表预测的大小，索引等等情况自动判断的，但是某些情况下会判断错误，这个时候我们就需要调整执行顺序。

mysql8.0以上有优化器提示(Optimizer Hints)，可以详细控制优化器的执行顺序，比较复杂。但是生产环境是5.6，无法使用。

8.0以下可以使用STRAIGHT_JOIN 来调整执行顺序，用法也比较简单，用STRAIGHT_JOIN 来代替JOIN就可以了，mysql会按照书写的顺序从左到右执行，这样我们就控制了mysql执行顺序。

下面我们来看一个具体的例子，有4个表INNER JOIN连接，还有一个EXISTS条件

	SELECT
		*
	FROM
		mm_detail a
		INNER JOIN  mm_domain d ON d.DOMAIN=a.DOMAIN
		INNER JOIN  mm_media c ON c.DOMAIN_ID=d.DOMAIN_ID
		INNER JOIN  mm_url x ON x.MEDIA_ID=c.MEDIA_ID
	WHERE
		a.URL_TYPE=1
		AND NOT EXISTS(SELECT 1 FROM mm_detail y WHERE y.URL_TYPE=3 AND y.URL_UNIQUE=x.URL_UNIQUE AND y.GROUP_NO=a.GROUP_NO)

由于数据量比较大，上面这个sql要执行几个小时以上，具体是几个小时不知道，没有耐心等待

id	select_type	table	type	poosible_keys	key	key_ken	ref	rows	Extra
1	PRIMARY	x	ALL	media_id				238426	Using where
1	PRIMARY	c	eq_ref	PRIMARY,DOMAIN_ID	PRIMARY	8	media.x.MEDIA_ID	1
1	PRIMARY	d	eq_ref	PRIMARY,domain	PRIMARY	4	media.c.DOMAIN_ID	1
1	PRIMARY	a	ref	IDX_SEARCH,IDX_DOMAIN,IDX_URL_TYPE	IDX_DOMAIN	768	media.d.DOMAIN	26	Using index condition; Using where
2	DEPENDENT SUBQUERY	y	ref	IDX_SEARCH,IDX_GROUP_NO,IDX_URL_TYPE,IDX_URL_UNIQUE	IDX_SEARCH	2111	const,media.a.GROUP_NO,media.x.URL_UNIQUE	1	Using index

从EXPLAIN的结果可以看到执行顺序，先执行了包含EXISTS的x表，并且是遍历全表，由于x表数据量最大，导致非常慢，这个执行顺序显然是错误的

下面我用STRAIGHT_JOIN 代替 INNER JOIN，强制查询顺序，执行时间变成21秒，差距非常大

	SELECT
		*
	FROM
		mm_detail a
		STRAIGHT_JOIN  mm_domain d ON d.DOMAIN=a.DOMAIN
		STRAIGHT_JOIN  mm_media c ON c.DOMAIN_ID=d.DOMAIN_ID
		STRAIGHT_JOIN  mm_url x ON x.MEDIA_ID=c.MEDIA_ID
	WHERE
		a.URL_TYPE=1
		AND NOT EXISTS(SELECT 1 FROM mm_detail y WHERE y.URL_TYPE=3 AND y.URL_UNIQUE=x.URL_UNIQUE AND y.GROUP_NO=a.GROUP_NO)

id	select_type	table	type	poosible_keys	key	key_ken	ref	rows	Extra
1	PRIMARY	a	ref	IDX_SEARCH,IDX_DOMAIN,IDX_URL_TYPE	IDX_SEARCH	4	const	223000	Using where
1	PRIMARY	d	eq_ref	PRIMARY,domain	domain	302	media.a.DOMAIN	1	Using index condition
1	PRIMARY	c	ref	PRIMARY,DOMAIN_ID	DOMAIN_ID	4	media.d.DOMAIN_ID	2
1	PRIMARY	x	ref	media_id	media_id	9	media.c.MEDIA_ID	264	Using where
2	DEPENDENT SUBQUERY	y	ref	IDX_SEARCH,IDX_GROUP_NO,IDX_URL_TYPE,IDX_URL_UNIQUE	IDX_SEARCH	2111	const,media.a.GROUP_NO,media.x.URL_UNIQUE	1	Using index