不安定な結果セット

このドキュメントでは、不安定な結果セットのエラーを解決する方法について説明します。

グループ化

便宜上、MySQL はGROUP BY構文を「拡張」して、 SELECT句がGROUP BY句で宣言されていない非集約フィールド、つまりNON-FULL GROUP BY構文を参照できるようにします。他のデータベースでは、結果セットが不安定になるため、これは構文エラーと見なされます。

たとえば、次の 2 つのテーブルがあるとします。

stu_info学生情報を保存します
stu_score生徒のテストのスコアが格納されます。

次に、次のような SQL クエリステートメントを記述します。

SELECT
    `a`.`class`,
    `a`.`stuname`,
    max( `b`.`courscore` )
FROM
    `stu_info` `a`
    JOIN `stu_score` `b` ON `a`.`stuno` = `b`.`stuno`
GROUP BY
    `a`.`class`,
    `a`.`stuname`
ORDER BY
    `a`.`class`,
    `a`.`stuname`;

結果：

+------------+--------------+------------------+
| class      | stuname      | max(b.courscore) |
+------------+--------------+------------------+
| 2018_CS_01 | MonkeyDLuffy |             95.5 |
| 2018_CS_03 | PatrickStar  |             99.0 |
| 2018_CS_03 | SpongeBob    |             95.0 |
+------------+--------------+------------------+
3 rows in set (0.00 sec)

a . classおよびa . stunameフィールドはGROUP BYステートメントで指定され、選択された列はa . class 、 a . stunameおよびb . courscoreです。 GROUP BY条件にない唯一の列b . courscoreも、 max()関数を使用して一意の値で指定されます。この SQL ステートメントを曖昧さなく満たす結果は1 つだけあり、これをFULL GROUP BY構文と呼びます。

反例はNON-FULL GROUP BY構文です。たとえば、これら 2 つのテーブルでは、次の SQL クエリ (delete a . stuname in GROUP BY ) を記述します。

SELECT
    `a`.`class`,
    `a`.`stuname`,
    max( `b`.`courscore` )
FROM
    `stu_info` `a`
    JOIN `stu_score` `b` ON `a`.`stuno` = `b`.`stuno`
GROUP BY
    `a`.`class`
ORDER BY
    `a`.`class`,
    `a`.`stuname`;

すると、この SQL に一致する 2 つの値が返されます。

最初に返される値:

+------------+--------------+------------------------+
| class      | stuname      | max( `b`.`courscore` ) |
+------------+--------------+------------------------+
| 2018_CS_01 | MonkeyDLuffy |                   95.5 |
| 2018_CS_03 | PatrickStar  |                   99.0 |
+------------+--------------+------------------------+

2 番目に返される値:

+------------+--------------+------------------+
| class      | stuname      | max(b.courscore) |
+------------+--------------+------------------+
| 2018_CS_01 | MonkeyDLuffy |             95.5 |
| 2018_CS_03 | SpongeBob    |             99.0 |
+------------+--------------+------------------+

aフィールドの値を SQL で取得する方法を指定しておらず、2 つの結果が両方とも SQL セマンティクスで満たされているstuname 、結果が 2 つあります。その結果、結果セットが不安定になります。したがって、 GROUP BYステートメントの結果セットの安定性を保証する場合は、 FULL GROUP BY構文を使用します。

MySQL は、 FULL GROUP BY構文をチェックするかどうかを制御するsql_modeスイッチONLY_FULL_GROUP_BY提供します。TiDB もこのsql_modeスイッチと互換性があります。

mysql> select a.class, a.stuname, max(b.courscore) from stu_info a join stu_score b on a.stuno=b.stuno group by a.class order by a.class, a.stuname;
+------------+--------------+------------------+
| class      | stuname      | max(b.courscore) |
+------------+--------------+------------------+
| 2018_CS_01 | MonkeyDLuffy |             95.5 |
| 2018_CS_03 | PatrickStar  |             99.0 |
+------------+--------------+------------------+
2 rows in set (0.01 sec)

mysql> set @@sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY';
Query OK, 0 rows affected (0.01 sec)

mysql> select a.class, a.stuname, max(b.courscore) from stu_info a join stu_score b on a.stuno=b.stuno group by a.class order by a.class, a.stuname;
ERROR 1055 (42000): Expression #2 of ORDER BY is not in GROUP BY clause and contains nonaggregated column '' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

実行結果: 上記の例は、 sql_modeにONLY_FULL_GROUP_BY設定した場合の効果を示しています。

注文する

SQL セマンティクスでは、 ORDER BY構文が使用されている場合にのみ結果セットが順番に出力されます。単一インスタンスデータベースの場合、データは 1 つのサーバーに保存されるため、複数回実行した結果はデータの再編成なしで安定していることがよくあります。一部のデータベース (特に MySQL InnoDBstorageエンジン) では、主キーまたはインデックスの順序で結果セットを出力することもできます。

分散データベースである TiDB は、複数のサーバーにデータを格納します。また、TiDBレイヤーはデータページをキャッシュしないため、 ORDER BYのない SQL ステートメントの結果セットの順序は不安定であると認識されやすくなります。連続した結果セットを出力するには、SQL セマンティクスに準拠するORDER BY節に order フィールドを明示的に追加する必要があります。

次の例では、 ORDER BY句に 1 つのフィールドのみが追加され、TiDB はその 1 つのフィールドのみで結果を並べ替えます。

mysql> select a.class, a.stuname, b.course, b.courscore from stu_info a join stu_score b on a.stuno=b.stuno order by a.class;
+------------+--------------+-------------------------+-----------+
| class      | stuname      | course                  | courscore |
+------------+--------------+-------------------------+-----------+
| 2018_CS_01 | MonkeyDLuffy | PrinciplesofDatabase    |      60.5 |
| 2018_CS_01 | MonkeyDLuffy | English                 |      43.0 |
| 2018_CS_01 | MonkeyDLuffy | OpSwimming              |      67.0 |
| 2018_CS_01 | MonkeyDLuffy | OpFencing               |      76.0 |
| 2018_CS_01 | MonkeyDLuffy | FundamentalsofCompiling |      88.0 |
| 2018_CS_01 | MonkeyDLuffy | OperatingSystem         |      90.5 |
| 2018_CS_01 | MonkeyDLuffy | PrincipleofStatistics   |      69.0 |
| 2018_CS_01 | MonkeyDLuffy | ProbabilityTheory       |      76.0 |
| 2018_CS_01 | MonkeyDLuffy | Physics                 |      63.5 |
| 2018_CS_01 | MonkeyDLuffy | AdvancedMathematics     |      95.5 |
| 2018_CS_01 | MonkeyDLuffy | LinearAlgebra           |      92.5 |
| 2018_CS_01 | MonkeyDLuffy | DiscreteMathematics     |      89.0 |
| 2018_CS_03 | SpongeBob    | PrinciplesofDatabase    |      88.0 |
| 2018_CS_03 | SpongeBob    | English                 |      79.0 |
| 2018_CS_03 | SpongeBob    | OpBasketball            |      92.0 |
| 2018_CS_03 | SpongeBob    | OpTennis                |      94.0 |
| 2018_CS_03 | PatrickStar  | LinearAlgebra           |       6.5 |
| 2018_CS_03 | PatrickStar  | AdvancedMathematics     |       5.0 |
| 2018_CS_03 | SpongeBob    | DiscreteMathematics     |      72.0 |
| 2018_CS_03 | PatrickStar  | ProbabilityTheory       |      12.0 |
| 2018_CS_03 | PatrickStar  | PrincipleofStatistics   |      20.0 |
| 2018_CS_03 | PatrickStar  | OperatingSystem         |      36.0 |
| 2018_CS_03 | PatrickStar  | FundamentalsofCompiling |       2.0 |
| 2018_CS_03 | PatrickStar  | DiscreteMathematics     |      14.0 |
| 2018_CS_03 | PatrickStar  | PrinciplesofDatabase    |       9.0 |
| 2018_CS_03 | PatrickStar  | English                 |      60.0 |
| 2018_CS_03 | PatrickStar  | OpTableTennis           |      12.0 |
| 2018_CS_03 | PatrickStar  | OpPiano                 |      99.0 |
| 2018_CS_03 | SpongeBob    | FundamentalsofCompiling |      43.0 |
| 2018_CS_03 | SpongeBob    | OperatingSystem         |      95.0 |
| 2018_CS_03 | SpongeBob    | PrincipleofStatistics   |      90.0 |
| 2018_CS_03 | SpongeBob    | ProbabilityTheory       |      87.0 |
| 2018_CS_03 | SpongeBob    | Physics                 |      65.0 |
| 2018_CS_03 | SpongeBob    | AdvancedMathematics     |      55.0 |
| 2018_CS_03 | SpongeBob    | LinearAlgebra           |      60.5 |
| 2018_CS_03 | PatrickStar  | Physics                 |       6.0 |
+------------+--------------+-------------------------+-----------+
36 rows in set (0.01 sec)

ORDER BY値が同じ場合、結果は不安定になります。ランダム性を減らすには、 ORDER BY値が一意である必要があります。一意性を保証できない場合は、 ORDER BYのフィールドのORDER BYの組み合わせが一意になるまで、さらにORDER BYフィールドを追加する必要があります。そうすれば、結果は安定します。

`GROUP_CONCAT()`で order by が使用されていないため、結果セットは不安定です。

TiDB はstorageレイヤーからデータを並列に読み取るため、結果セットは不安定になり、 ORDER BYなしでGROUP_CONCAT()によって返される結果セットの順序は不安定であると簡単に認識されます。

GROUP_CONCAT()結果セット出力を順序どおりに取得できるようにするには、SQL セマンティクスに準拠するORDER BY句にソートフィールドを追加する必要があります。次の例では、 ORDER BYなしでcustomer_idを結合するGROUP_CONCAT()によって、不安定な結果セットが発生します。

除外ORDER BY

最初のクエリ:

mysql>  select GROUP_CONCAT( customer_id SEPARATOR ',' ) FROM customer where customer_id like '200002%';
+-------------------------------------------------------------------------+
| GROUP_CONCAT(customer_id  SEPARATOR ',')                                |
+-------------------------------------------------------------------------+
| 20000200992,20000200993,20000200994,20000200995,20000200996,20000200... |
+-------------------------------------------------------------------------+

2番目のクエリ:

mysql>  select GROUP_CONCAT( customer_id SEPARATOR ',' ) FROM customer where customer_id like '200002%';
+-------------------------------------------------------------------------+
| GROUP_CONCAT(customer_id  SEPARATOR ',')                                |
+-------------------------------------------------------------------------+
| 20000203040,20000203041,20000203042,20000203043,20000203044,20000203... |
+-------------------------------------------------------------------------+

ORDER BY含む

最初のクエリ:

mysql>  select GROUP_CONCAT( customer_id order by customer_id SEPARATOR ',' ) FROM customer where customer_id like '200002%';
+-------------------------------------------------------------------------+
| GROUP_CONCAT(customer_id  SEPARATOR ',')                                |
+-------------------------------------------------------------------------+
| 20000200000,20000200001,20000200002,20000200003,20000200004,20000200... |
+-------------------------------------------------------------------------+

2番目のクエリ:

mysql>  select GROUP_CONCAT( customer_id order by customer_id SEPARATOR ',' ) FROM customer where customer_id like '200002%';
+-------------------------------------------------------------------------+
| GROUP_CONCAT(customer_id  SEPARATOR ',')                                |
+-------------------------------------------------------------------------+
| 20000200000,20000200001,20000200002,20000200003,20000200004,20000200... |
+-------------------------------------------------------------------------+

`SELECT * FROM T LIMIT N`で結果が不安定になる

返される結果は、storageノード (TiKV) 上のデータの分散に関係します。複数のクエリを実行すると、storageノード (TiKV) の異なるstorageユニット (リージョン) が異なる速度で結果を返すため、結果が不安定になる可能性があります。

不安定な結果セット

グループ化

注文する

GROUP_CONCAT()で order by が使用されていないため、結果セットは不安定です。

SELECT * FROM T LIMIT Nで結果が不安定になる

このページは役に立ちましたか？

`GROUP_CONCAT()`で order by が使用されていないため、結果セットは不安定です。

`SELECT * FROM T LIMIT N`で結果が不安定になる