永远用小的结果集驱动大的结果集

转载自：公司内数据工程师：

永远用小的结果集驱动大的结果集

很多人喜欢在优化 SQL 的时候使用小表驱动大表，个人认为这不太严谨。为什么？因为大表经过 WHERE 条件过滤之后返回的结果集并不一定就比小表所返回的大，也许更小。在这种情况下如果仍然采用小表驱动大表，就会得到相反的性能效果。

其实这也非常容易理解，在MySQL中，只有 Nested Loop 一种 Join 方式，也就是说MySQL的 Join 都是通过嵌套循环来实现的。驱动结果集越大，所需要循环就越多，那么被驱动表的访问次数自然也就越多，而每次访问被驱动表，即使需要的逻辑 IO 很少，循环次数多了，总量也不可能小，而且每次循环都不能避免消耗CPU，所以 CPU 运算量也会跟着增加。如果仅仅以表的大小来作为驱动表的判断依据，假若小表过滤后所剩下的结果集比大表多很多，结果就会在嵌套循环中带来更多的循环次数，反之，所需要的循环次数就会更少，总体 IO 量和 CPU 运算量也会更少。在非 Nested Loop 的 Join 算法中，如 Oracle 中的 Hash Join，小结果集驱动大的结果集同样是最优的选择。

所以，在优化 Join Query 的时候，最基本的原则就是“小结果集驱动大结果集”，通过这个原则来减少嵌套循环中的循环次数，以减少 IO总量及CPU运算的次数。

SELECT
pproductpr0_.p_product_prop_id AS col_0_0_,
pproductpr0_.p_product_prop_code AS col_1_0_,
pproductpr0_.p_product_prop_name AS col_2_0_,
CASE WHEN pproductpr0_.p_product_prop_val IS NULL THEN ” ELSE pproductpr0_.p_product_prop_val END AS col_3_0_,
pproductpr0_.p_product_prop_order AS col_4_0_,
(SELECT
bdic4_.b_dic_code
FROM b_dic bdic4_
WHERE bdic4_.b_dic_id = pproductpr0_.p_product_prop_must) AS col_5_0_,
pproductpr0_.p_product_prop_status AS col_6_0_,
(SELECT
bdic5_.b_dic_code
FROM b_dic bdic5_
WHERE bdic5_.b_dic_id = pproductpr0_.p_product_prop_display) AS col_7_0_,
pproductpr0_.p_product_prop_source AS col_8_0_,
pproductpr0_.p_product_prop_int_code AS col_9_0_,
pproduct3_.p_product_id AS col_10_0_,
pproductpr0_.p_product_prop_default_value AS col_11_0_,
(SELECT
bdic6_.b_dic_code
FROM b_dic bdic6_
WHERE bdic6_.b_dic_id = pproductpr0_.p_product_prop_available) AS col_12_0_,
(SELECT
bdic7_.b_dic_code
FROM b_dic bdic7_
WHERE bdic7_.b_dic_id = pproductpr0_.p_product_prop_width) AS col_13_0_,
(SELECT
bdic8_.b_dic_code
FROM b_dic bdic8_
WHERE bdic8_.b_dic_id = pproductpr0_.p_product_prop_valid_type) AS col_14_0_,
pproductty2_.p_ptp_classify_id AS col_15_0_,
pproductty1_.p_ptype_prop_id AS col_16_0_,
(SELECT
bdic9_.b_dic_code
FROM b_dic bdic9_
WHERE bdic9_.b_dic_id = pproductpr0_.p_product_prop_source_type) AS col_17_0_,
pproductpr0_.p_product_display_format AS col_18_0_,
pproductpr0_.p_product_value_field AS col_19_0_,
pproductpr0_.p_product_text_field AS col_20_0_,
pproductpr0_.p_product_min_length AS col_21_0_,
pproductpr0_.p_product_max_length AS col_22_0_,
pproductpr0_.p_product_event_source AS col_23_0_,
pproductpr0_.p_product_prop_prop_name AS col_24_0_,
pproductpr0_.p_product_prop_create_on AS col_25_0_,
pproductpr0_.p_product_prop_create_by AS col_26_0_,
pproductpr0_.p_product_prop_update_on AS col_27_0_,
pproductpr0_.p_product_prop_update_by AS col_28_0_
FROM p_product_prop pproductpr0_
LEFT OUTER JOIN p_product_type_prop pproductty1_
ON pproductpr0_.p_ptype_prop_id = pproductty1_.p_ptype_prop_id
LEFT OUTER JOIN p_product_type_prop_classify pproductty2_
ON pproductty1_.p_ptp_classify_id = pproductty2_.p_ptp_classify_id
LEFT OUTER JOIN p_product pproduct3_
ON pproductpr0_.p_product_id = pproduct3_.p_product_id
WHERE pproductpr0_.p_product_id = ‘C07C25F3621A4B509E9DCE111812B7BA’
AND EXISTS(
SELECT pproductty2_.p_ptp_classify_id
FROM p_product_type_prop_classify pproductty2_
WHERE pproductty1_.p_ptp_classify_id = pproductty2_.p_ptp_classify_id
AND pproductty2_.p_ptp_classify_id = ‘8a8a94e55162a9db015162b123470065’)
ORDER BY pproductty1_.p_ptype_prop_order;