SELECT…FROM UPDATE 特性介绍及性能测试

使用背景：

将update+select两条SQL合并成单条SQL，向客户端直接返回update之后的结果列，通过减小网络开销及sql解析代价提高性能

特性描述：

postgresql的语法是returning … as …

http://www.postgresql.org/docs/9.1/static/sql-update.html

oracle的语法是returning…into…

http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/returninginto_clause.htm#LNPLS01354

MySQL中由于需要兼容MySQL JDBC，我们将这个特性实现为：SELECT…FROM UPDATE…

语法描述：

SELECT
  select_expr [, select_expr ...]
FROM UPDATE
  [LOW_PRIORITY] [IGNORE]
  [COMMIT_ON_SUCCESS] [ROLLBACK_ON_FAIL] [QUEUE_ON_PK] [TARGET_AFFECT_ROW num]
tbl_name
SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT} ...]
[WHERE where_condition]  [ORDER BY ...]  [LIMIT row_count]

说明：

1）仅支持单个表UPDATE语句

2）SELECT返回的数据量为matched rows，而非modified rows，即逻辑更新而非物理更新，这个与PostgreSQl/Oracle是一致的

性能数据

1）测试数据

mysql>; show create table ttt\G
*************************** 1. row ***************************
       Table: ttt
Create Table: CREATE TABLE `ttt` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `num` int(11) DEFAULT NULL,
  `str` varchar(32) DEFAULT 'hello, world!',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=17366752 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
 
mysql>; select count(*) from ttt;
+----------+
| count(*) |
+----------+
| 16777216 |
+----------+
1 row in set (3.72 sec)

2）对比数据

client_statement：UPDATE+SELECT分两次发送到服务器端执行

client_multi_statement：multi-statement模式将UPDATE+SELECT一次发送到服务器端执行

select_update：发送SELECT…FROM UPDATE到服务器端执行

3）实验结果

通过unix socket测试（服务器和压测程序位于同一台物理机器）：

query cnt: 100000
client_statement: 16 seconds
client_multi_statement: 15 seconds
select_update: 10 seconds

query cnt: 500000
client_statement: 81 seconds
client_multi_statement: 74 seconds
select_update: 52 seconds

query cnt: 1000000
client_statement: 162 seconds
client_multi_statement: 146 seconds
select_update: 103 seconds

通过TCP测试（服务器和压测程序位于同机房两台机器，RTT：0.140 ms）

query cnt: 100000
client_statement: 48 seconds
client_multi_statement: 32 seconds
select_update: 27 seconds

query cnt: 500000
client_statement: 244 seconds
client_multi_statement: 161 seconds
select_update: 135 seconds

query cnt: 500000

client_statement: 481 seconds
client_multi_statement: 320 seconds

select_update: 267 seconds

结论：

本地unix socket连接下：

SELECT…FROM UPDATE 相对于分两次发送SQL执行性能提升60%左右，相对于multi-statement性能提升40%左右

同机房TCP连接下：

SELECT…FROM UPDATE 相对于分两次发送SQL执行性能提升80%左右，相对于multi-statement性能提升20%左右

即：SELECT…FROM UPDATE特性通过减小网络交互代价，可以极大提高性能

实现原理及细节：

我的博客：http://www.gpfeng.com/?p=134

patch地址：http://rb.corp.taobao.com/r/62123/