mysql 优化select or set记录
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秒 , 这样变成了只查索引数据,不会全表扫描