🥤 🐌 🙋🏿 単純なMySQLクエリからの予期しない結果 👩🏿‍🔧 🐷 👩🏻‍🍳

私は最近、単一のリクエストがなぜこのような奇妙な結果を生成するのかを解明するために膨大な時間を費やしました。 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バグトラッカーでチケットを作成しました。開発者がそれについてどう思うか見てみましょう。

単純なMySQLクエリからの予期しない結果

何がありますか？

どうする？

どのような結論を導き出すことができますか？

More articles: