sql优化那些事

SELECT
	*
FROM
	(
		SELECT
			product_goods.sku AS sku,
			yoursister.*
		FROM
			product_goods
		LEFT JOIN (
			SELECT
				eoo.*,
				pccc. NAME AS cate_one_name
			FROM
				(
					SELECT
						eo.*,
						pcc. NAME AS cate_two_name,
						pcc.pid AS cate_two_pid
					FROM
						(
							SELECT
								a.*, pc. NAME AS product_cate_name,
								pb. NAME AS product_brand_name,
								sc. NAME AS seller_cate_name,
								sr.seller_name AS seller,
								pc.pid AS cate_pid,
								pc.`name` AS cate_three_name
							FROM
								(
									SELECT
										id,
										name1,
										product_cate_id,
										seller_id,
										cost_price,
										mall_pc_price,
										mal_mobile_price,
										product_stock,
										actual_sales,
										is_top,
										create_time,
										up_time,
										state,
										product_brand_id,
										product.id AS pct_id,
										seller_cate_id 
									FROM
										`product`
									WHERE
										`state` IN (6)
									ORDER BY
										id DESC
								) AS a
							LEFT JOIN product_cate pc ON pc.id = a.product_cate_id
							LEFT JOIN product_brand pb ON pb.id = a.product_brand_id
							LEFT JOIN seller_cate sc ON sc.id = a.seller_cate_id
							LEFT JOIN seller sr ON sr.id = a.seller_id
						) eo
					LEFT JOIN product_cate pcc ON pcc.id = eo.cate_pid
				) eoo
			LEFT JOIN product_cate pccc ON pccc.id = eoo.cate_two_pid
		) AS yoursister ON product_goods.product_id = yoursister.pct_id
	) AS xxxx
WHERE
	xxxx.name1 IS NOT NULL

上面这条sql巨慢无比，截图如下（根本查不出来结果，因为太慢了，我等不到结果）：

做视图优化：

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER  VIEW `view_product` AS SELECT
	product_goods.sku,
	pccc.`NAME` AS cate_one_name,
	pcc.`NAME` AS cate_two_name,
	pc.`NAME` AS product_cate_name,
	pb.`NAME` AS product_brand_name,
	sc.`NAME` AS seller_cate_name,
	sr.seller_name AS seller,
	pc.`name` AS cate_three_name,
	product.id,
	product.name1,
	product.seller_id,
	product.cost_price,
	product.mall_pc_price,
	product.mal_mobile_price,
	product.product_stock,
	product.actual_sales,
	product.is_top,
	product.create_time,
	product.up_time,
	product.state,
	product.product_brand_id,
	product.id AS pct_id,
	product.seller_cate_id
FROM
	product_goods
LEFT JOIN product ON product_goods.product_id = product.id
LEFT JOIN product_cate pc ON pc.id = product.product_cate_id
LEFT JOIN product_cate pcc ON pcc.id = pc.pid
LEFT JOIN product_cate pccc ON pccc.id = pcc.pid
LEFT JOIN product_brand pb ON pb.id = product.product_brand_id
LEFT JOIN seller_cate sc ON sc.id = product.seller_cate_id
LEFT JOIN seller sr ON sr.id = product.seller_id

出结果了，截图如下：