1.提高大数据表查询重复记录的效率
MySQL 如果遇到1000万左右数据量的大表,查询重复记录的时候速度会非常慢
我们通常查询重复记录的方法是
select * from tgs_code where bianhao in (SELECT bianhao FROM tgs_code GROUP BY bianhao HAVING COUNT(bianhao)>1)
上述查询如果数据量在 1000万左右的时候,基本就没反应了。我们用下列方法先建立一个临时表
CREATE TABLE tgs_code_tmp AS (
SELECT bianhao FROM tgs_code GROUP BY bianhao HAVING COUNT(bianhao)>1)
再通过多表联合查询,就很快出结果了
SELECT * FROM tgs_code a,tgs_code_tmp b WHERE a.bianhao = b.bianhao