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

By | 2014 年 3 月 18 日

使用背景:

将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/

2 thoughts on “SELECT…FROM UPDATE 特性介绍及性能测试

  1. lee

    非常不错!貌似patch的链接失效了,能再提供一下吗?想了解具体如何实现,thx

    Reply
    1. gpfeng Post author

      完整的patch这会儿找不到了,你看看这个:http://www.gpfeng.com/get_update.patch
      这是一个初始版本,可以看看原理
      patch基于alimysql,所有不能直接在网上下载的mysql源码上applay

      Reply

发表评论

电子邮件地址不会被公开。 必填项已用*标注