インベントリヒントを使用したスループットの向上 - ApsaraDB RDS

このトピックでは、AliSQL が提供するインベントリヒント機能について説明します。この機能は、Returning 機能およびステートメントキュー機能と組み合わせて使用することで、トランザクションを高速にコミットまたはロールバックできます。これにより、アプリケーションのスループットが向上します。

背景情報

フラッシュセールなどのユースケースでは、在庫削減は一般的なタスクであり、高い同時実行性と直列化処理の両方が必要です。 AliSQL は、キューイングとトランザクションヒントを使用して同時実行性を制御します。これにより、トランザクションを迅速にコミットまたはロールバックし、ビジネスのスループットを向上させることができます。

インベントリヒント機能を使用すると、テーブルの単一行にあるホットデータを更新する際の、ApsaraDB RDS for MySQL インスタンスのパフォーマンスを最大 31,000 TPS (秒間トランザクション数) まで向上させることができます。詳細については、「単一行のホットデータ更新におけるテスト方法と結果」をご参照ください。

前提条件

RDS インスタンスでは、次のいずれかの MySQL バージョンが実行されている必要があります。

MySQL 8.4
MySQL 8.0
MySQL 5.7
MySQL 5.6

構文

SELECT、UPDATE、INSERT、DELETE ステートメントでは、次のヒントを使用できます。

MySQL 5.7、8.0、8.4 の場合：
- 次のトランザクションヒントが導入されています。
  - COMMIT_ON_SUCCESS：このヒントが適用されたステートメントの実行が成功した場合、トランザクションはコミットされます。
  - ROLLBACK_ON_FAIL：このヒントが適用されたステートメントの実行が失敗した場合、トランザクションはロールバックされます。
  構文：
```
/*+ COMMIT_ON_SUCCESS */
/*+ ROLLBACK_ON_FAIL */
```
  例：
```
UPDATE /*+ COMMIT_ON_SUCCESS ROLLBACK_ON_FAIL */ T
SET c = c - 1
WHERE id = 1;
```
- 条件付きヒント TARGET_AFFECT_ROW(NUMBER) が導入されています。
  この条件付きヒントをステートメントに適用すると、影響を受ける行数がヒントで指定された数と一致する場合にのみ、ステートメントは成功します。
  構文：
```
/*+ TARGET_AFFECT_ROW(NUMBER) */
```
  例：
```
UPDATE /*+ TARGET_AFFECT_ROW(1) */ T
SET c = c - 1
WHERE id = 1;
```
MySQL 5.6
MySQL 5.6 のヒントの構文は、MySQL 5.7 および MySQL 8.0 のものと同様です。唯一の違いは、MySQL 5.7 および MySQL 8.0 ではヒントをコメントに含める必要がないという点です。
例：
```
UPDATE COMMIT_ON_SUCCESS ROLLBACK_ON_FAIL T
SET c = c - 1
WHERE id = 1;
UPDATE TARGET_AFFECT_ROW(1) T
SET c = c - 1
WHERE id = 1;
```

注意事項

ヒントの後には、テーブル名を続ける必要があります。
トランザクションで使用されるヒントが有効になると、トランザクションが自動的にコミットされます。したがって、ヒントはトランザクションの最後の SQL ステートメントに配置してください。
トランザクションヒントは、オートコミットモードをサポートしていません。オートコミットモードで実行されるステートメントでトランザクションヒントを使用すると、エラーが返されます。例：
```
mysql> UPDATE /*+ commit_on_success rollback_on_fail target_affect_row(1) */ t
    -> SET col1 = col1 + 1
    -> WHERE id = 1;
ERROR 7531 (HY000): Inventory transactional hints didn't allowed in autocommit mode
```

トランザクションヒントは、サブステートメントでは使用できません。サブステートメントでトランザクションヒントを使用すると、エラーが返されます。例：

mysql> CREATE TRIGGER tri_1
    -> BEFORE INSERT ON t
    -> FOR EACH ROW
    -> BEGIN
    -> INSERT /*+ commit_on_success */ INTO t1 VALUES (1);
    -> end//

mysql> INSERT INTO t VALUES (2, 1);
ERROR HY000: Inventory transactional hints didn't alllowed in stored procedure

条件付きヒントは、SELECT または EXPLAIN ステートメントでは使用できません。 SELECT または EXPLAIN ステートメントで条件付きヒントを使用すると、エラーが返されます。例：

mysql> EXPLAIN UPDATE /*+ commit_on_success rollback_on_fail target_affect_row(1) */ t
    -> SET col1 = col1 + 1
    -> WHERE id = 1;
ERROR 7532 (HY000): Inventory conditional hints didn't match with result

説明

TARGET_AFFECT_ROW に無効な数値を指定して、システムからエラーが返されるかどうかを確認できます。

mysql> EXPLAIN UPDATE /*+ commit_on_success rollback_on_fail target_affect_row(-1) */ t
    -> SET col1 = col1 + 1
    -> WHERE id = 1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | UPDATE      | t     | NULL       | range | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
1 row in set, 2 warnings (0.00 sec)

mysql> show warnings;
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                 |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1064 | Optimizer hint syntax error near '-1) */ t set col1=col1+1 where id =1' at line 1                                                       |
| Note    | 1003 | update /*+ COMMIT_ON_SUCCESS ROLLBACK_ON_FAIL */ `test`.`t` set `test`.`t`.`col1` = (`test`.`t`.`col1` + 1) where (`test`.`t`.`id` = 1) |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Returning 機能との連携

インベントリヒント機能と Returning 機能を組み合わせて使用することで、システムがリアルタイムの結果セットを返すことができます。詳細については、「Returning」をご参照ください。例：

mysql> CALL dbms_trans.returning("*", "update /*+ commit_on_success rollback_on_fail target_affect_row(1) */ t
                                       set col1=col1+1 where id=1");
+----+------+
| id | col1 |
+----+------+
|  1 |   13 |
+----+------+
1 row in set (0.00 sec)

mysql> CALL dbms_trans.returning("*", "insert /*+ commit_on_success rollback_on_fail target_affect_row(1) */ into
                                       t values(10,10)");
+----+------+
| id | col1 |
+----+------+
| 10 |   10 |
+----+------+
1 row in set (0.01 sec)

ステートメントキュー機能との連携

インベントリヒント機能とステートメントキュー機能を組み合わせて使用することで、システムにステートメントをキューイングさせることができます。詳細については、「ステートメントキュー」をご参照ください。例：

mysql> UPDATE /*+ ccl_queue_field(id) commit_on_success rollback_on_fail target_affect_row(1) */ t
    -> SET col1 = col1 + 1
    -> WHERE id = 1;

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE /*+ ccl_queue_value(1) commit_on_success rollback_on_fail target_affect_row(1) */ t
    -> SET col1 = col1 + 1
    -> WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0