-- 建表，有重復(fù)數(shù)據(jù)的表
create table a (a1 char(10), a2 char(10), a3 char(10));
-- 造數(shù)據(jù)
insert into a values ('7', '4', '3');
insert into a select * from a;-- 反復(fù)執(zhí)行該語句，造出1千萬的數(shù)據(jù)
select count(*) from a;--
-- 重建中間表
create table temp select * from a where 1=2;
-- 重建目標表
create table c select * from temp where 1=2;
select * from temp ;
-- 創(chuàng)建聯(lián)合索引 強制索引去掉重復(fù)數(shù)據(jù)
create index ind_temp_c123 on temp(a1, a2, a3);
explain select a1, a2, max(a3) from temp force index (ind_temp_c123) group by a1, a2;
insert into temp select * from a; -- 227 s
select count(*) from temp;
-- 關(guān)鍵的點為聯(lián)合索引，高效執(zhí)行
insert into c select a1, a2, max(a3) from temp force index (ind_temp_c123) group by a1, a2; -- 4 秒
SELECT * from c ; -- 已去重，至此已完成去重
