不安定な結果セット

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

グループ化

便宜上、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の値を取得する方法を指定しなかったため、2 つの結果があります。 SQL のstunameフィールド、および 2 つの結果は両方とも SQL セマンティクスによって満たされます。結果セットが不安定になります。したがって、 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 InnoDB ストレージエンジン) は、主キーまたはインデックスの順序で結果セットを出力することさえできます。

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

次の例では、1 つのフィールドのみがORDER BY句に追加され、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 はストレージレイヤーからデータを並行して読み取るため、結果セットは不安定です。そのため、 ORDER BYを使用せずにGROUP_CONCAT()で返される結果セットの順序は、不安定であると簡単に認識されます。

GROUP_CONCAT()が結果セットの出力を順番に取得できるようにするには、並べ替えフィールドをORDER BY句に追加する必要があります。これは、SQL セマンティクスに準拠しています。次の例では、 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`の不安定な結果

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

不安定な結果セット

グループ化

オーダーバイ

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

SELECT * FROM T LIMIT Nの不安定な結果

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

`SELECT * FROM T LIMIT N`の不安定な結果