mysql 优化select or set记录

标签：set UNION 202301 emtc select mysql WHERE SELECT voltage

1  原始sql 

SELECT * FROM st_voltage_r_202301 
    WHERE emtc IN (SELECT emtc FROM sw_statistics_info WHERE manufacturer_name = '中电科27所')  or   emtc  in ('0112320021', '0112320041', '0203230011', '0203230031', '0382120021' );

　　需要2.9秒

  explain SELECT * FROM st_voltage_r_202301 
    WHERE   emtc  in ('0112320021', '0112320041', '0203230011', '0203230031', '0382120021' );


    explain SELECT * FROM st_voltage_r_202301 
    WHERE emtc IN (SELECT emtc FROM sw_statistics_info WHERE manufacturer_name = '中电科27所')  ;

单独查询都只需要0.2秒

 

说明肯定优化的

方案1： 将查询结果都放临时表中

CREATE TEMPORARY TABLE temp_emtc(emtc VARCHAR(20));
INSERT INTO temp_emtc VALUES ('0112320021'), ('0112320041'), ('0203230011'), ('0203230031'), ('0382120021');

INSERT INTO temp_emtc  SELECT emtc FROM sw_statistics_info WHERE manufacturer_name = '中电科27所'


SELECT * FROM st_voltage_r_202301 
    WHERE emtc IN (SELECT emtc FROM temp_emtc );

查询结果0.2秒

方案 2:  不适用or条件，适用union all或者union

SELECT * FROM st_voltage_r_202301 
WHERE emtc IN (SELECT emtc FROM (SELECT emtc FROM sw_statistics_info WHERE manufacturer_name = '中电科27所'
    UNION ALL 
    SELECT '0112320021'
    UNION ALL 
    SELECT '0112320041' 
    UNION ALL 
    SELECT '0203230011' 
    UNION ALL 
    SELECT '0203230031'
    UNION ALL 
    SELECT '0382120021') as subquery);

　　0.2秒 , 这样变成了只查索引数据，不会全表扫描

 

标签：set,UNION,202301,emtc,select,mysql,WHERE,SELECT,voltage
From： https://www.cnblogs.com/chengxin1982/p/17045412.html