一、exists/not exists 和 in/ont in介绍：

exists 和 in 的区别： ** exists 是走索引的，in可能导致全表扫描（慎用）

如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用in, 反之如果外层的主查询记录较少，子查询中的表大，又有索引时使用exists。其实我们区分in和exists主要是造成了驱动顺序的改变(这是性能变化的关键)，如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询，所以我们会以驱动表的快速返回为目标，那么就会考虑到索引及结果集的关系了 ，另外IN时不对NULL进行处理。

not exists 和 not in 的区别： not in 不走索引（全表扫描），not exists 走索引

如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；而not extsts 的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。

二、代码记录：

业务需求、需要查询的表：

需求： 1.根据服务类型serviceMode筛选出商品，2.根据商品id通过商品门店关联表查询出有此商品的门店id，3.根据门店id查询门店id和门店图片…… 需要的表： sp_service_store：门店表 sp_service_commodity：商品表 sp_service_store_commodity：门店商品关联表 sp_service_image：图片表

业务代码：

方式一：使用MySql 8.0 中的窗口函数 ROW_number

目的：使用窗口函数分组去重

  1. <select id="selectStoreList" resultType="com.diditech.dd.datacenter.ops.dto.SpServiceStoreDTO">
  2.  select * from (
  3.    select
  4.    sc.store_id AS id,
  5.    s.store_name AS storeName,
  6.    s.evaluate_score AS evaluateScore,
  7.    i.image_url AS imageUrl,
  8.    sc.price,
  9.    c.commodity_name AS commodityName,
  10.    c2.toalcount,
  11.    ROW_number() over(partition by sc.store_id) group_id
  12.    from
  13.    sp_service_store_commodity sc
  14.    left JOIN
  15.    sp_service_commodity c
  16.    on sc.commodity_id = c.id  and c.del_flag = 0 and c.commodity_status = 0 and c.service_mode = 0
  17.    left join
  18.    sp_service_image i
  19.    on sc.store_id = i.use_by_id and i.image_main = 0 and i.image_type = 0 and i.del_flag = 0
  20.    left join
  21.    sp_service_store s
  22.    on sc.store_id = s.id
  23.    LEFT JOIN
  24.    (SELECT SUM(appointment_num) AS toalcount, store_id FROM sp_service_store_commodity WHERE
  25.    del_flag=0 group by store_id) AS c2 ON
  26.    c2.store_id = s.id
  27.    where
  28.    sc.del_flag =0
  29.    AND c.type_id = #{spStoreQO.typeId,jdbcType=INTEGER}
  30.    <if test="spStoreQO.distict != null and spStoreQO.distict != ''">
  31.       AND s.distict = #{spStoreQO.distict}
  32.    </if>
  33.    <if test="spStoreQO.sortType == 1">
  34.       ORDER BY s.evaluate_score DESC
  35.    </if>
  36.    <if test="spStoreQO.sortType == 3">
  37.       ORDER BY c2.toalcount DESC
  38.    </if>
  39.    ) t
  40.    where
  41.    t.group_id =1
  42. </select>

方式二：使用exists嵌套，查询到list后，使用Stream去重。（也可以不使用exists，直接使用联表查询【联接表较多，可能会降低查询效率。】）

  1. SELECT
  2. a.id,
  3. a.store_name AS storeName,
  4. a.evaluate_score AS evaluateScore,
  5. CONCAT_WS('',a.distict,a.store_address) AS storeAddress,
  6. b.image_url AS imageUrl,
  7. c.toalcount,
  8. a.longitude,
  9. a.latitude,
  10. c.price
  11. FROM
  12. sp_service_store a
  13. LEFT JOIN
  14. sp_service_image b
  15. ON
  16. a.id = b.use_by_id
  17. LEFT JOIN
  18. (SELECT SUM(appointment_num) AS toalcount, store_id, price, commodity_id FROM sp_service_store_commodity WHERE del_flag=0 group by store_id, price, commodity_id) AS c ON
  19. c.store_id = a.id
  20. LEFT JOIN
  21. sp_service_commodity sc
  22. ON
  23. sc.id = c.commodity_id
  24. <where>
  25.    EXISTS
  26.    (SELECT store_id FROM sp_service_store_commodity s2 WHERE
  27.    EXISTS
  28.    (SELECT id FROM sp_service_commodity s3 WHERE s3.id = s2.commodity_id AND s2.store_id = a.id)
  29.    )
  30.    AND b.image_main = 0
  31.    AND b.image_type = 0
  32.    AND sc.del_flag = 0
  33.    AND b.del_flag = 0
  34.    AND a.del_flag = 0
  35.    AND sc.type_id = #{spStoreQO.typeId,jdbcType=INTEGER}
  36.    AND sc.service_mode = #{spStoreQO.serviceMode,jdbcType=INTEGER}
  37.    <if test="spStoreQO.distict != null and spStoreQO.distict != ''">
  38.       AND a.distict = #{spStoreQO.distict}
  39.    </if>
  40.    <if test="spStoreQO.sortType == 1">
  41.       ORDER BY a.evaluate_score DESC
  42.    </if>
  43.    <if test="spStoreQO.sortType == 3">
  44.       ORDER BY c.toalcount DESC
  45.    </if>

总结：exists/not exists 可以替换 in/ont in，避免全表扫描。Mysql 8.0以后开始支持窗口函数，可以省很多功夫，推荐Mysql 8.0窗口函数使用。