私は最近、単一のリクエストがなぜこのような奇妙な結果を生成するのかを解明するために膨大な時間を費やしました。 MariaDBでは、明らかに間違った結果が出ましたが、古き良きMySQLではまったく実行されませんでした。 つまり リクエストは開始されましたが、その完了を待つことはできませんでした。 問題が何であるかを理解するために、私は少し研究をしなければなりませんでした。 しかし、すべてについて順番に話しましょう。
先月の日中にフォーラムに少なくとも1回書き込んだユーザーの数を計算する必要がありました。
フォーラムの投稿が次の表にあるとします。
CREATE TABLE `forum_posts` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `user_id` int(10) unsigned NOT NULL, `created` datetime NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
次のデータが含まれます INSERT INTO `forum_posts` (`user_id`, `created`) VALUES (1, '2013-01-01'), (1, '2013-01-01'), (2, '2013-01-01'), (2, '2013-01-01'), (2, '2013-01-01'), (3, '2013-01-01'), (3, '2013-01-01'), (4, '2013-01-01'), (5, '2013-01-01'), (5, '2013-01-01'), (1, '2013-01-02'), (1, '2013-01-02'), (2, '2013-01-02'), (2, '2013-01-02'), (3, '2013-01-02'), (3, '2013-01-02'), (4, '2013-01-02'), (4, '2013-01-02'), (1, '2013-02-02'), (1, '2013-02-02'), (2, '2013-02-02'), (2, '2013-02-02'), (3, '2013-02-02'), (3, '2013-02-02'), (4, '2013-02-02'), (4, '2013-02-02'), (5, '2013-02-02'), (5, '2013-02-02');
必要なデータを返すクエリ：
SELECT DATE(fp.created) dt, count(*) qnt FROM forum_posts fp WHERE fp.id IN ( SELECT fp2.id FROM forum_posts fp2 WHERE fp2.created >= '2013-01-01' AND fp2.created < '2013-02-01' GROUP BY DATE(fp2.created), fp2.user_id ) GROUP BY dt;
このリクエストの結果は予期しないものであり、次を返しました。
2013-01-01 10 2013-01-02 8
私の目を信じていなかったので、私は急いでサブクエリを個別に実行しました-彼は正しい結果（9行）を返しました。 つまり、
WHERE id IN (...)条件を使用したクエリは、
WHERE id IN (...)渡された識別子よりも多くのレコードを返しました。 夕方が長いことが明らかになりました。
何がありますか？
サブクエリを使用した簡単なリクエストがあります。 サブクエリは
WHEREで値を切り捨て、日付とユーザーでグループ化し、受信した識別子を日付でのみグループ化する外部クエリに渡します。 しかし、テストが示すように、データベースはサブクエリの結果を無視します。
私は神秘主義を信じておらず、夕方には本当に自分自身を信頼していないので、私は同志の
xzanderを描いて問題を解決しました。 彼は私のデータベースのダンプに対してクエリを開始しました（実際、約400万行ありました）。 結果は予想外でした-10分でリクエストは満たされませんでした。 環境を比較すると、MariaDB（5.5.30）でクエリを実行しており、MySQL（5.5.28）でクエリを実行していることがわかりました。
さらに掘り下げてみると、MySQLとMariaDBのクエリ実行方法が異なることがわかりました。 「スマート」MySQLオプティマイザーは、最初に外部クエリを実行し、各行をサブクエリに結合して、結果として
N回実行する必要があると判断します。 したがって、このような要求は、大規模なデータベースで
非常に長い時間実行されます。
MariaDBはよりトリッキーです-クエリをすばやく実行しますが、サブクエリの
GROUP BYを無視し
ます 。結局のところ、これはバグではなく
機能です 。 ここでは、移行の完全な互換性と透過性があります。
このような状況でどうやって自分を見つけたのでしょうか？ 結合でできるのに、なぜサブクエリなのですか？ 簡単です：このプロジェクトでは
Doctrineが使用されましたが、他の適切なORMのように、結合を行うために最初に接続を記述する必要があります。 単一のクエリのためだけにテーブルとそれ自体の関係を説明することは、すでに何らかの方法で多すぎます。
どうする？
オプティマイザを欺くのは難しくないことが判明しました。サブクエリに思ったよりもサブクエリが難しいことを彼に納得させ、サブクエリに
HAVING 1を追加しました。
そのため、MariaDBで正しく機能するクエリを次に示します。
SELECT DATE(fp.created) dt, count(*) qnt FROM forum_posts fp WHERE fp.id IN ( SELECT fp2.id FROM forum_posts fp2 WHERE fp2.created >= '2013-01-01' AND fp2.created < '2013-02-01' GROUP BY DATE(fp2.created), fp2.user_id HAVING 1 ) GROUP BY dt;
MySQLでは、おそらくサブクエリを削除して、クエリを書き直す必要があります。
どのような結論を導き出すことができますか？
- 単純なクエリの結果でも確認する必要があります。 そのようなリクエストが誤ったデータを返すことに気付かないことは簡単でした。
- MySQLからMariaDBへの移行は、彼らが言うほど透明ではありません。
- 複雑な統計クエリを実行する必要がある場合、ORMに縛られるべきではありません。
UPD。ファイリング
アルノから、
HAVING 1松葉杖の代わりに、サブクエリの
MIN(fp2.id)を
MIN(fp2.id)置き換えるだけで十分であることが
fp2.idました。
しかし、
petropavelは、これはまだ機能ではなくバグであると判断し、MariaDBバグトラッカーで
チケットを作成しました。 開発者がそれについてどう思うか見てみましょう。