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

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

 永远用小的结果集驱动大的结果集
很多人喜欢在优化 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;

p_product_prop经过pproductpr0_.p_product_id = 'C07C25F3621A4B509E9DCE111812B7BA'
过滤之后就是个小的结果集

 再EXISTS的时候，效率也很高

 最终要的是能用上pproductpr0_.p_product_id的单键索引