MySQLのGROUP BYとDISTINCTについて質問があります。

Question

テーブルA

CREATE table table_a
(
YYYYMM CHAR(6) NOT NULL,
CD1 CHAR(6) NOT NULL,
CD2 CHAR(2) NOT NULL,
CD3 CHAR(5) NOT NULL,
CD4 CHAR(2) NOT NULL,
CD5 CHAR(3) NOT NULL,
MAX1 CHAR(5) NOT NULL ,
MIN1 CHAR(5) NOT NULL,
CD6 CHAR (3) NOT NULL,
PRIMARY KEY (YYYYMM, CD1, CD2)
);

table_aには約3000万件のレコードがあります。
実行環境はAccess2016とOracleになっておりました。
これをAccess2016とMySQLで実現することになりました。
CD3,CD4,CD5をキーにしてMAX,MIN,COUNTの集合関数を使用します。
しかし、処理速度がOracleでは数分でしたがMySQLに変更すると処理時間が大幅に増え数十分から
１時間以上かかるようになりました。SQLについてはOracleとMySQLは同じ内容です。

今までのSQL
SELECT
CD3
, CD4
, CD5
, MAX(MAX1)
, MIN(MIN1)
, COUNT(CD6)
FROM table_a
GROUP BY
CD3
, CD4
, CD5
で実現定義してしておりました。

これをMySQLでは例として
SELECT
CD3
, CD4
, CD5
, MAX(MAX1)
, MIN(MIN1)
, COUNT(CD6)
FROM
(
SELECT DISTINCT
CD3
, CD4
, CD5
FROM table_a
) a
GROUP BY
CD3
, CD4
, CD5
イメージ的には、まずCD3,CD4,CD5で重複を除外しその結果からグルーピングして集合関数を
利用すればスピードアップが図れると思っておりますが、上記のSQLでは文法エラーになります。
この文法では文法エラーになって当然です。
とにかく処理時間を短縮する手法として皆様のお知恵をお借りしたいと思っております。
ご教示のほどよろしくお願いいたします。

Comments

[hoshi-takanori]

index を作ると良いかも？
https://teratail.com/questions/83355

[sazi]

そのSQLはAccessでのリンクテーブルに対してのAccessのクエリーですか？
また質問では一つのテーブルですけど、実際には幾つものテーブルを使用しているというような事は無いですか？

[pecmm]

回答返信から、oracle,mysqlどちらも適切にインデックスが使用されている前提とします。
joinもwhere相当も無いシンプルなクエリで、処理時間に10倍？くらいの差が出るとなると、他の原因を疑いたくなります
たとえばmysqlは検証環境へ仮導入みたいな状況で、サーバのHW性能差が大きい…等はないですか？

Answer 1

oracleで作業すればよいのでは？

追記：何故かスコア剥奪されたのだが。意味が分からない。テラテイルよ、スコアを返しなさい。
何故数分で作業出来る環境が既にあるのにSQLに変えて作業時間を増やすのか分からない。

Comments

[s_yoshi]

早速のご回答ありがとうございます。
現在Oracleで構築しておりますシステムが今回、顧客要望によりMySQLに変更せざることになりました。
よってどうしてもMySQLで実現しなくてはなりません。
このことを踏まえてよい案がありましたらご教示お願いいたします。

[ikedas]

@EA_Maker2023さん
「SQLに変えて」って、……Oracle DatabaseもSQLデータベースなんですが。

@logres_Fanさん
質問の要件を無視している回答にグッドを繰り返し送る意図は何でしょうか。

Answer 2

適切なインデックスを貼れば高速化は可能だと思います
MySQLの例としてあげているSQLは明らかにおかしいので実行できません

テスト

CREATE table table_a
(
ID int primary key auto_increment,
YYYYMM CHAR(6) NOT NULL,
CD1 CHAR(6) NOT NULL,
CD2 CHAR(2) NOT NULL,
CD3 CHAR(5) NOT NULL,
CD4 CHAR(2) NOT NULL,
CD5 CHAR(3) NOT NULL,
MAX1 CHAR(5) NOT NULL ,
MIN1 CHAR(5) NOT NULL,
CD6 CHAR (3) NOT NULL,
unique(YYYYMM, CD1, CD2),
index(CD3,CD4,CD5,MAX1,MIN1,CD6)
);

insert into table_a(YYYYMM,CD1,CD2,CD3,CD4,CD5,MAX1,MIN1,CD6) values
('202309','000001','01','30001','41','501','99001','90001','601'),
('202309','000001','02','30001','41','501','99002','90001','601'),
('202309','000001','03','30001','42','501','99003','90001','601'),
('202309','000002','01','30001','41','501','99003','90001','601'),
('202309','000002','02','30001','42','501','99002','90001','601');

explainで確認

EXPLAIN SELECT
CD3
,CD4
,CD5
,MAX(MAX1)
,MIN(MIN1)
,COUNT(CD6)
FROM table_a
GROUP BY
CD3
,CD4
,CD5

※結果

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	table_a	NULL	index	CD3	CD3	92	NULL	5	100.00	Using index

Comments

[s_yoshi]

CD3,CD4,CD5の順番でインデックスを１つ定義してみました。しかし3000万件という膨大なデータ量では
これといった改善が見られませんでした（ほぼインデックスを定義する前）
例に挙げたSQLはエラーで機能しないことは存じております。
これといった改善点が見いだせなれないのが現状です。
何とかお知恵を拝借できないでしょうか。ご教示のほどよろしくお願いいたします。

[yambejp]

「今までのSQL」文からわざわざ「MySQLでは例」として非効率なサブクエリに書き換えているのはなにか意図があるのでしょうか？

[ikedas]

多分話が逆で、遅いからサブクエリに書き換えれば (DISTINCTで処理するレコード数を減らせば) 改善しないかなー、というアイディアだと思います。

[ikedas]

@s_yoshi さん
MAX1, MIN1, CD6にもインデックスをつけてはどうですか。集約関数ではNULLの行を無視する (「COUNT(*)」は例外で、単に全レコード数を返す) ので、インデックスがないと全ての行の実データを読んで実際にNULLでないことを確かめてしまいます。インデックスがあれば実データを読まないのですぐに終わります。

[追記] yambejpさんが追記された例ではレコード数が少ないため、インデックスなしで実データを全部読んでもそんなに遅くなっていませんが、同じことを3000万行のデータでやれば遅くなると思われます。

Answer 3

結論を先に述べると、ボトルネックはMySQLとは考えにくく、AccessとMySQLの間のネットワーク部分か、Accessそのものがボトルネックだと考えられます。

---

そのへんで動いてたMariaDBで検証しました。

まず、ランダムデータで1 000 000行用意しました。

MariaDB [test]> SELECT COUNT(*) FROM table_a;
+----------+
| COUNT(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.86 sec)

MariaDB [test]> SELECT * FROM table_a LIMIT 10;
+--------+--------+-----+-------+-----+-----+-------+-------+-----+
| YYYYMM | CD1    | CD2 | CD3   | CD4 | CD5 | MAX1  | MIN1  | CD6 |
+--------+--------+-----+-------+-----+-----+-------+-------+-----+
| 199001 | AAIYUH | BI  | GJNNW | AN  | JKQ | QDNPO | WQCBO | HLG |
| 199001 | AAMYRC | OB  | PIBAU | LH  | XFH | HIPCE | WPKQB | FQG |
| 199001 | AARQNL | JM  | TBQDD | HQ  | UDL | CKJAP | IKULH | DJL |
| 199001 | AASJFC | MD  | OFMPT | FN  | HNP | NUCCR | UHGID | LOA |
| 199001 | AAVASX | FL  | HSPXF | SL  | VBX | IAYAB | OGLFN | KUG |
| 199001 | AAVQKI | UE  | YCKRK | JB  | ABM | CFEVY | HUWAF | XYG |
| 199001 | ABEMMO | VP  | EFNDL | YO  | FSR | ONQKA | EBSBW | RLU |
| 199001 | ABHXRO | EK  | CFYUA | LR  | NXS | LCRPQ | EAAXD | KKS |
| 199001 | ABJSDJ | AE  | FAMNW | UY  | DPJ | BERQJ | XINPH | HYJ |
| 199001 | ABQVNS | RN  | SODEC | EL  | JBM | AFIVJ | VBSJN | RPD |
+--------+--------+-----+-------+-----+-----+-------+-------+-----+
10 rows in set (0.01 sec)

実行計画を見ると、使えるインデックスがありません。

MariaDB [test]> EXPLAIN
    -> SELECT
    -> CD3
    -> , CD4
    -> , CD5
    -> , MAX(MAX1)
    -> , MIN(MIN1)
    -> , COUNT(CD6)
    -> FROM table_a
    -> GROUP BY
    -> CD3
    -> , CD4
    -> , CD5;
+------+-------------+---------+------+---------------+------+---------+------+--------+---------------------------------+
| id   | select_type | table   | type | possible_keys | key  | key_len | ref  | rows   | Extra                           |
+------+-------------+---------+------+---------------+------+---------+------+--------+---------------------------------+
|    1 | SIMPLE      | table_a | ALL  | NULL          | NULL | NULL    | NULL | 885048 | Using temporary; Using filesort |
+------+-------------+---------+------+---------------+------+---------+------+--------+---------------------------------+
1 row in set (0.01 sec)

実際、実行にそこそこの時間がかかります。とはいえそれほど極端にはかかりませんが。

MariaDB [test]> SELECT CD3 , CD4 , CD5 , MAX(MAX1) , MIN(MIN1) , COUNT(CD6) FROM table_a GROUP BY CD3 , CD4 , CD5 LIMIT 1;
+-------+-----+-----+-----------+-----------+------------+
| CD3   | CD4 | CD5 | MAX(MAX1) | MIN(MIN1) | COUNT(CD6) |
+-------+-----+-----+-----------+-----------+------------+
| AAAAE | FL  | UMY | JPFAI     | NBYLF     |          1 |
+-------+-----+-----+-----------+-----------+------------+
1 row in set (20.27 sec)

インデックスを追加してみました。実行計画によると、インデックスが使えるようになりそうです。

MariaDB [test]> ALTER TABLE table_a ADD INDEX (CD3, CD4, CD5);
Query OK, 0 rows affected (13.98 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [test]> ANALYZE TABLE table_a;
+--------------+---------+----------+----------+
| Table        | Op      | Msg_type | Msg_text |
+--------------+---------+----------+----------+
| test.table_a | analyze | status   | OK       |
+--------------+---------+----------+----------+
1 row in set (0.16 sec)

MariaDB [test]> EXPLAIN SELECT CD3 , CD4 , CD5 , MAX(MAX1) , MIN(MIN1) , COUNT(CD6) FROM table_a GROUP BY CD3 , CD4 , CD5;
+------+-------------+---------+-------+---------------+------+---------+------+--------+-------+
| id   | select_type | table   | type  | possible_keys | key  | key_len | ref  | rows   | Extra |
+------+-------------+---------+-------+---------------+------+---------+------+--------+-------+
|    1 | SIMPLE      | table_a | index | NULL          | CD3  | 30      | NULL | 910704 |       |
+------+-------------+---------+-------+---------------+------+---------+------+--------+-------+
1 row in set (0.00 sec)

実行すると激速になりました。インデックスは効いてます。

MariaDB [test]> SELECT CD3 , CD4 , CD5 , MAX(MAX1) , MIN(MIN1) , COUNT(CD6) FROM table_a GROUP BY CD3 , CD4 , CD5 LIMIT 1;
+-------+-----+-----+-----------+-----------+------------+
| CD3   | CD4 | CD5 | MAX(MAX1) | MIN(MIN1) | COUNT(CD6) |
+-------+-----+-----+-----------+-----------+------------+
| AAAAE | FL  | UMY | JPFAI     | NBYLF     |          1 |
+-------+-----+-----+-----------+-----------+------------+
1 row in set (0.05 sec)

MariaDB [test]> 

というわけで、MySQL自体は、インデックスがなくても質問者さんの経験したほど時間がかかるかどうかは疑問です。適切なインデックスをつければまったく問題ないでしょう。

一方、今回のテストでは完全にランダムなデータを用いたため、出力される行数はほぼテーブルの行数と同じでした。実際の環境ではこれほどではないにせよ、かなり出力行数が多くなると考えられます。つまり処理すべき出力量が多いことが問題の可能性があります。

結論として、ボトルネックはMySQLとは考えにくく、AccessとMySQLの間のネットワーク部分か、Accessそのものがボトルネックだと考えられます。

Answer 4

#質問にコメントしてますが、返信が無いのでこちらに。
Accsesはどのように関係しますか？
SQLがAccsesのクエリーの話なら、パススルークエリーにする事をお薦めします。

追記

Accessのパススルークエリで実行しております。

質問のSQLは質問の為に改編していませんか？　
テーブル名やカラム名程度であれば問題ありませんが、SQLの構造を改編している場合は、SQLチューニングに関する質問に於いては致命的ですので。

上記該当しない場合は、以下の様な事も考えられます。
・oracleとMySQLそれぞれのSQLで参照している項目が同じで、データ件数も同等という事であれば、インデックスの定義内容が同じかどうか。

・oracleとMySQLでの環境との相違もレスポンスに関係します。
[メモリの割当]　MｙSQLへの割り当てに不足が生じていないか
[環境]　CPU/メモリ/ストレージ/ネットワーク等スペックは同等か
※上記が該当するなら、SQLチューニングに余地は無いと思います。
ネットワークに関しては、MySQLサーバー上で質問のSQLを実行しレスポンスを確認する事で、切り分けできます。

Comments

[s_yoshi]

ご回答ありがとうございます。
Accessのパススルークエリで実行しております。タイムアウトを10分に設定しておりますが
この10分が限界と考えております。
どうしても良い案が見つからず焦っております。妙案がございましたらご教示お願いいたします。