最近研究MySQL應(yīng)用優(yōu)化中討論的一個(gè)問(wèn)題：如何做到在APP端盡量將業(yè)務(wù)邏輯緊密相關(guān)的幾條SQL封裝成單個(gè)SQL批量發(fā)送給Server。這種思路和存儲(chǔ)過(guò)程還不太一樣，另外存儲(chǔ)過(guò)程需要將業(yè)務(wù)邏輯綁定在服務(wù)器端，并且測(cè)試過(guò)程發(fā)現(xiàn)在效率上要相對(duì)Oracle弱化不少。我們需要的是什么樣的功能呢？
業(yè)務(wù)場(chǎng)景：
舉一個(gè)典型的賬務(wù)邏輯中SQL例子：
begin;
update t1 set xxx where xxx; # 影響兩行記錄
insert into t2 values(); # 成功插入一行記錄
xxxyyyzzz;
commit;
注意，業(yè)務(wù)上非常強(qiáng)的邏輯要求：update必須是成功更新兩條記錄 && insert必須是成功插入一條記錄。
此時(shí)業(yè)務(wù)優(yōu)化希望能將update & insert 封裝成一條邏輯語(yǔ)句，任何一條語(yǔ)句不成功便需要返回錯(cuò)誤，是否回滾則讓APP決定。
為此，MySQL服務(wù)器層必須要擴(kuò)展語(yǔ)法：
update min_batch_rows=2 t1 set xxx where xxx;
insert min_batch_rows=1 into t2 values();
在APP端，將這兩條語(yǔ)句一起發(fā)送給服務(wù)器端（CLIENT_MULTI_STATEMENTS），一旦有一條語(yǔ)句執(zhí)行不成功則中止。
1. 對(duì)單條記錄，需要擴(kuò)展 min_batch_rows 語(yǔ)法，在命令處理完后判斷影響的行數(shù)從而決定是否回滾。
2. 對(duì)多條記錄，需要將這幾條語(yǔ)句批量發(fā)送，這一組連續(xù)的帶hint的語(yǔ)句為一組特殊的語(yǔ)句，要么全做，要么全不做。
1. 單條語(yǔ)句
## min_batch_rows 是指最小影響行數(shù)，如果影響的行數(shù)小于此值，則當(dāng)前語(yǔ)句會(huì)被回滾。
# 兩條a=11的記錄
mysql> select * from t1 where a=11;
+------+
| a |
+------+
| 11 |
| 11 |
+------+
2 rows in set (3.79 sec)
# 指定最小更新量為2，a=11的記錄會(huì)被更新
mysql> update MIN_BATCH_SIZE=2 t1 set a=a+10 where a=11;
Query OK, 2 rows affected (1.40 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> select * from t1 where a=11;
Empty set (1.18 sec)
mysql> select * from t1 where a=21;
+------+
| a |
+------+
| 21 |
| 21 |
+------+
2 rows in set (1.94 sec)
# 指定最小更新量為3，a=21的記錄會(huì)不被更新，因?yàn)橹挥袃蓷l記錄有影響
mysql> update MIN_BATCH_SIZE=3 t1 set a=a+10 where a=21;
ERROR 1721 (HY000): effected rows are less than specified mini_batch_rows.
mysql> select * from t1 where a=21;
+------+
| a |
+------+
| 21 |
| 21 |
+------+
2 rows in set (1.90 sec)
2. 多條語(yǔ)句
## min_batch_rows 的語(yǔ)句為一組邏輯，只有上條語(yǔ)句正確執(zhí)行后下一條語(yǔ)句才可能會(huì)執(zhí)行。
mysql> delimiter ||
mysql> truncate table t1;
-> begin;insert into t1 values(1); insert into t1 values(2); insert into t1 values(3);commit ||
mysql> select * from t1 ||
+------+
| a |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
mysql> begin;
-> update MIN_BATCH_SIZE=1 t1 set a=a+10 where a=1;
-> update MIN_BATCH_SIZE=1 t1 set a=a+10 where a=2;
-> update MIN_BATCH_SIZE=2 t1 set a=a+10 where a=3;
-> commit ||
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
ERROR 1721 (HY000): effected rows are less than specified mini_batch_rows.
mysql>
# 仍舊是原先的123
# 注意，如果在原先的session中查看記錄會(huì)是修改后的記錄，因?yàn)閙ulti-sql被過(guò)截掉了。
delimiter ||
truncate table t1;
begin;insert into t1 values(1); insert into t1 values(2); insert into t1 val<code>ues(3);commit ||
## SQL：
delimiter ||
begin;
update MIN_BATCH_SIZE=1 t1 set a=a+10 where a=1;
update MIN_BATCH_SIZE=1 t1 set a=a+10 where a=2;
update MIN_BATCH_SIZE=2 t1 set a=a+10 where a=3;
commit ||
## SQL：
delimiter ||
begin;
update MIN_BATCH_SIZE=1 t1 set a=a+10 where a=1;
update MIN_BATCH_SIZE=1 t1 set a=a+10 where a=2;
update MIN_BATCH_SIZE=2 t1 set a=a+10 where a=3 ||
update MIN_BATCH_SIZE=1 t1 set a=a+10 where a=3;
commit ||
## SQL：
delimiter ||
begin;
update MIN_BATCH_SIZE=1 t1 set a=a+10 where a=1;
update MIN_BATCH_SIZE=1 t1 set a=a+10 where a=2;
update MIN_BATCH_SIZE=2 t1 set a=a+10 where a=3 ||
update MIN_BATCH_SIZE=1 t1 set a=a+10 where a=3 ||
commit ||
其執(zhí)行結(jié)果：
mysql> delimiter ||
mysql> begin;
-> update MIN_BATCH_SIZE=1 t1 set a=a+10 where a=1;
-> update MIN_BATCH_SIZE=1 t1 set a=a+10 where a=2;
-> update MIN_BATCH_SIZE=2 t1 set a=a+10 where a=3 ||
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
ERROR 1721 (HY000): effected rows are less than specified mini_batch_rows.
mysql> update MIN_BATCH_SIZE=1 t1 set a=a+10 where a=3 ||
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> commit ||
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t1; ||
+------+
| a |
+------+
| 11 |
| 12 |
| 13 |
+------+
3 rows in set (0.00 sec)