MySQLで単一テーブルから複数カラムを重複排除後にSUM関数で集計したい

Question

MySQLで単一テーブルから複数カラムを重複排除後にSUM関数で集計したい

MySQL 5.7を使用しております。
実現したいことは単一のテーブルで複数カラム（C1,C2,C3,C4,C5）の重複を排除したのち
重複排除した別のカラムをSUM関数で集計を行ないたいと思っております。
レコード件数は約3千万件以上あります。イメージとしては以下のSQLを考えましたが
単純にGROUP BYしてSUM関数で集計すればよいと考えられますが、パフォーマンスが極端に落ちます。
パフォーマンスを上げるSQLが考えられず悩んでおります。
どなたかお力をお貸し願いますでしょうか。

[パフォーマンスの悪いクエリ１]
SELECT
  C1
  , C2
  , C3
  , C4
  , C5
  , SUM(C6)
FROM sample_table t1
GROUP BY
  C1
  , C2
  , C3
  , C4
  , C5


[パフォーマンスの悪いクエリ２]
SELECT
  C1
  , C2
  , C3
  , C4
  , C5
  , SUM(C6)
FROM sample_table t1
  LEFT JOIN
    (
    SELECT DISTINCT
      C1
      , C2
      , C3
      , C4
      , C5
    FROM sample_table t2
    ) a
      ON a.C1 = t1.C1
         AND a.C2 = t1.C2
         AND a.C3 = t1.C3
         AND a.C4 = t1.C4
         AND a.C5 = t1.C5

Answer 1

C1～C6をひとまとめにした複合インデックスを貼ってください

参考

create table tbl(id int primary key,
c1 varchar(10) not null,
c2 varchar(10) not null,
c3 varchar(10) not null,
c4 varchar(10) not null,
c5 varchar(10) not null,
c6 int not null,
index (c1,c2,c3,c4,c5,c6));
 

insert into tbl values
( 1,'a1','b1','c1','d1','e1',1),
( 2,'a1','b1','c1','d1','e1',2),
( 3,'a1','b1','c2','d2','e2',3),
( 4,'a1','b2','c2','d2','e2',4),
( 5,'a1','b2','c2','d2','e2',5),
( 6,'a2','b2','c2','d2','e2',6),
( 7,'a2','b3','c2','d2','e2',7),
( 8,'a2','b3','c2','d2','e2',8),
( 9,'a3','b4','c2','d2','e2',9),
(10,'a3','b4','c2','d2','e2',0);

集計

select 
c1,c2,c3,c4,c5
,count(*) as cnt
,sum(c6) as sum_c6
from tbl
group by c1,c2,c3,c4,c5

重複データを除外

select 
c1,c2,c3,c4,c5
,sum(c6) as sum_c6
from tbl
group by c1,c2,c3,c4,c5
having count(*)=1

インデックスチェック

explain select 
c1,c2,c3,c4,c5
,sum(c6) as sum_c6
from tbl
group by c1,c2,c3,c4,c5
having count(*)=1

別解釈

こういうことかも

select sum(c6) as sum_c6 from tbl as t1
where not exists(
select 1 from tbl where 1
and id<>t1.id
and c1=t1.c1
and c2=t1.c2
and c3=t1.c3
and c4=t1.c4
and c5=t1.c5
)

Comments

[s_yoshi]

早速のご回答ありがとうございます。
情報が不足しておりまして申し訳ございません。
複合インデックスについては貼っております。貼った状態で134秒かかっております。
この処理時間はローカルのSSDに対してのIOになります。
但し、実際はHDDでSQLが流れますのでSSDに対して数倍から数十倍の処理時間がかかるとの記事を
ネットで検索したことがあります。134秒という数値が妥当なのかはわかりませんが、もし高速化が
できればと思っております。なお、HDDでの確認手段がないことを付け加えさせていただきます。

[yambejp]

explainでインデックスの使用状況を確認ください
※参考データを確認のこと

c1-c5がユニークでc6をsumするということは実は別解のようなことを希望かも