mysql优化--explain--实战调优--3.1

mysql优化–explain–实战调优–3.1

---

1、案例1

1.1、sql语句

SELECT DISTINCT
	a.id_ AS "artId",
	a.urlTitle,
	a.content,
	a.articleId,
	a.resourcePrimKey,
	e.entryId,
	a.modifiedDate,
	e.title,
	IFNULL(v.viewCount, 0) total
FROM
	journalarticle a
JOIN (
	SELECT
		max(art.modifiedDate) modifiedDate
	FROM
		assetcategory cat
	JOIN assetentryassetcategoryrel ref ON cat.categoryId = ref.assetCategoryId
	AND (
		cat.treePath LIKE '%/38284/%'
		OR cat.treePath LIKE '%/38287/%'
	)
	JOIN assetentry ent ON ent.entryId = ref.assetEntryId
	JOIN (
		SELECT
			a.resourcePrimKey,
			a.modifiedDate
		FROM
			(
				SELECT
					jat.resourcePrimKey,
					MAX(jat.version) version
				FROM
					journalarticle jat
				GROUP BY
					jat.resourcePrimKey
			) ja_sub
		JOIN journalarticle a ON a.resourcePrimKey = ja_sub.resourcePrimKey
		AND a.version = ja_sub.version
		AND a. STATUS = 0
	) art ON art.resourcePrimKey = ent.classPK
	WHERE
		EXISTS (
			SELECT
				1
			FROM
				assetcategory cat
			JOIN assetentryassetcategoryrel ref ON cat.categoryId = ref.assetCategoryId
			AND cat.categoryId = 38404
			JOIN assetentry ent ON ent.entryId = ref.assetEntryId
			WHERE
				art.resourcePrimKey = ent.classPK
		)
	GROUP BY
		cat.categoryId
) b ON a.modifiedDate = b.modifiedDate
AND a. STATUS = 0
JOIN assetentry e ON e.classPK = a.resourcePrimKey
LEFT JOIN viewcountentry v ON v.classPK = e.entryId
ORDER BY
	a.modifiedDate DESC

1.2、耗时

1.3、优化

1.3.1、执行计划

1.3.2、执行计划分析

1. id=2 中存在全表扫描，这是可能需要优化的点

   

2. id=1 中存在全表扫描，这是可能需要优化的点

   

   

1.3.3、优化–id=1中存在的全表扫描

建索引（考虑ref和eq_ref）

再次查看

1.3.4、优化–id=1中存在的全表扫描

查看索引情况

创建索引

再次查看

我们看到rows=2170的那条记录，还是走了全表扫描

通过分析代码，这里是查分类的最大时间，时间我们是不会去建索引的。

所以这里的优化，是基于sql语句的优化。

查看耗时