mysql中in参数过多该如何优化

优化方式概述

在这里插入图片描述
未优化前

SELECT * FROM rb_product rb where sku in('1022044','1009786')

方案2示例

    public static void main(String[] args) {
        //往list里面设置3000个值
        List<String> list = new ArrayList<>();
        for (int i = 0; i < 3000; i++) {
            list.add("" + i);
        }
        //切割成3份，每份1000条
        List<List<String>> split = ListUtil.split(list, 1000);
        for (List<String> strings : split){
            //分批查询
        }
    }

方案3示例

方案4示例

假设表rb_product（产品表），产品唯一标识表rb_sku
EXISTS优化优化后

SELECT rb.*
FROM rb_product rb
WHERE EXISTS (
    SELECT sku
    FROM sku_temp st where rb.sku=st.sku		    
);

方案5示例

方法1

创建临时表

CREATE TEMPORARY TABLE sku_temp (sku VARCHAR(50));
INSERT INTO sku_temp (sku) VALUES (1002196),(1002198),(1002200),(1002201),(1002203),(1002204),(1002254),(1002255),(1002256),(1002257);

连表查询

select rb.*  from sku_temp  st  LEFT JOIN rb_product rb on st.sku=rb.sku;

方法2

WITH tmp_src AS (
  SELECT '1002196' AS sku
  UNION ALL
  SELECT '1002200' AS sku
  UNION ALL
  SELECT '1002201' AS sku
  UNION ALL
  SELECT '1002203' AS sku
  UNION ALL
  SELECT '1002204' AS sku
)

select rb.*  from tmp_src  st  LEFT JOIN rb_product rb on st.sku=rb.sku;