mysql使用联合索引去重

-- 建表，有重复数据的表
create table a (a1 char(10), a2 char(10), a3 char(10));
-- 造数据
insert into a values ('7', '4', '3');
insert into a select * from a;-- 反复执行该语句，造出1千万的数据

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 ;

-- 创建联合索引 强制索引去掉重复数据
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;
-- 关键的点为联合索引，高效执行
insert into c select a1, a2, max(a3) from temp force index (ind_temp_c123) group by a1, a2; -- 4 秒
SELECT * from c ; -- 已去重，至此已完成去重