转账接口(特别是批量转账接口)性能差怎么办?
背景:网易圈圈应用的转账接口性能差,但是这是核心接口,TPS上要求高。怎么优化?
经过实战,效果最明显的优化是一条insert语句多个values值
,单机TPS从300涨到1000多。
多表值Sql提升性能,形象地说叫一目十行
(假设总共100条测试记录)
- 一条数据一个insert语句,一共100条insert语句
- 十条数据一个insert语句,一共10条insert语句
我再本机环境试验了insert into table1 values(),(),(),(),(),(),(),(),(),(),(),(),() 确实性能提升很多,同样100条记录,如果10条复合insert语句插入,是0.3秒完成; 如果是单条记录insert,100次,是3.8秒相差十倍!!; 而100条1commit还是10条1commit还是每条commit,反而对性能没有什么影响~
原理解析:
在未***绑定变量***的情况下,一条sql语句的执行过程是漫长的,包括:sql语句变身的解析,激活事务,插入位置定位,以及事后的记录undo,redo及binlog
d_insert::mysql_insert
>Sql_cmd_insert::mysql_insert
>切换session状态为 update
>进入插入逻辑
>handler::ha_write_row
>ha_innobase::write_row
>row_insert_for_mysql
>row_insert_for_mysql_using_ins_graph
>trx_start_if_not_started_xa_low
>trx_start_low 激活事物,事物状态由 not_active 变为 active
>row_ins_step
>row_ins
>row_ins_index_entry_step
>row_ins_index_entry
>row_ins_clust_index_entry
>row_ins_clust_index_entry_low
>btr_cur_search_to_nth_level 查找定位数据
>btr_cur_optimistic_insert 进行乐观插入
>btr_cur_ins_lock_and_undo
>trx_undo_report_row_operation
>trx_undo_page_report_insert 记录insert的undo记录
>trx_undo_page_set_next_prev_and_add
>trx_undof_page_add_undo_rec_log 记录undo的redo log 入redo buffer
>page_cur_tuple_insert 进行insert 元组插入,及实际的插入操作
>page_cur_insert_rec_write_log 记录插入的redo log 入redo buffer
>binlog_log_row
>write_locked_table_maps
>THD::binlog_write_table_map
>binlog_start_trans_and_stmt
>binlog_cache_data::write_event binlog event 写入到 binlog cache
多表值sql虽然增大了单条语句和解析的负担,但是减少了前后的各种动作,比如激活事务,记录log等的次数,所以观测到效率提升
批量接口
社交奖励发放,都是批量的,统计激励服务那边会在晚间算好某用户该奖励多少,统一发放
自然时间激励,如果领取了激励券,但是调用批量接口的话,余额是没法实时增加的,需要等一段时间用户才能查询到(这个可能是调用实时接口了)
测试脚本
十条数据一个insert语句
CREATE DEFINER=`root`@`%` PROCEDURE `rawinsert_procedure`()
BEGIN
insert into currency(id, symbol) values (0, '0'),(1, '1'),(2, '2'),(3, '3'),(4, '4'),(5, '5'),(6, '6'),(7, '7'),(8, '8'),(9, '9');
insert into currency(id, symbol) values (10, '10'),(11, '11'),(12, '12'),(13, '13'),(14, '14'),(15, '15'),(16, '16'),(17, '117'),(18, '18'),(19, '19');
insert into currency(id, symbol) values (20, '20'),(21, '21'),(22, '22'),(23, '23'),(24, '24'),(25, '25'),(26, '26'),(27, '27'),(28, '28'),(29, '29');
insert into currency(id, symbol) values (30, '30'),(31, '31'),(32, '32'),(33, '33'),(34, '34'),(35, '35'),(36, '36'),(37, '37'),(38, '38'),(39, '39');
insert into currency(id, symbol) values (40, '40'),(41, '41'),(42, '42'),(43, '43'),(44, '44'),(45, '45'),(46, '46'),(47, '47'),(48, '48'),(49, '49');
insert into currency(id, symbol) values (50, '50'),(51, '51'),(52, '52'),(53, '53'),(54, '54'),(55, '55'),(56, '56'),(57, '57'),(58, '58'),(59, '59');
insert into currency(id, symbol) values (60, '60'),(61, '61'),(62, '62'),(63, '63'),(64, '64'),(65, '65'),(66, '66'),(67, '67'),(68, '68'),(69, '69');
insert into currency(id, symbol) values (70, '70'),(71, '71'),(72, '72'),(73, '73'),(74, '74'),(75, '75'),(76, '76'),(77, '77'),(78, '78'),(79, '79');
insert into currency(id, symbol) values (80, '80'),(81, '81'),(82, '82'),(83, '83'),(84, '84'),(85, '85'),(86, '86'),(87, '87'),(88, '88'),(89, '89');
insert into currency(id, symbol) values (90, '90'),(91, '91'),(92, '92'),(93, '93'),(94, '94'),(95, '95'),(96, '96'),(97, '97'),(98, '98'),(99, '99');
commit;
END
或者是:
CREATE DEFINER=`root`@`%` PROCEDURE `test_procedure`()
BEGIN
declare i,j int;
declare tmpstr char(10);
while i < 10 do
set i = i + 1;
set j = i * 10;
set tmpstr = convert(j, char(6));
insert into currency(id, symbol) values (j, tmpstr||'0'),(j+1, tmpstr||'1'),(j+2, tmpstr||'2'),(j+3, tmpstr||'3'),(j+4, tmpstr||'4'),(j+5, tmpstr||'5'),(j+6, tmpstr||'6'),(j+7, tmpstr||'7'),(j+8, tmpstr||'8'),(j+9, tmpstr||'9');
commit;
end while;
END
一条数据一个insert语句
CREATE DEFINER=`root`@`%` PROCEDURE `single_procedure`()
BEGIN
insert into currency(id, symbol) values (0, '0');
insert into currency(id, symbol) values (1, '1');
insert into currency(id, symbol) values (2, '2');
insert into currency(id, symbol) values (3, '3');
insert into currency(id, symbol) values (4, '4');
insert into currency(id, symbol) values (5, '5');
insert into currency(id, symbol) values (6, '6');
insert into currency(id, symbol) values (7, '7');
insert into currency(id, symbol) values (8, '8');
insert into currency(id, symbol) values (9, '9');
insert into currency(id, symbol) values (10, '10');
insert into currency(id, symbol) values (11, '11');
insert into currency(id, symbol) values (12, '12');
insert into currency(id, symbol) values (13, '13');
insert into currency(id, symbol) values (14, '14');
insert into currency(id, symbol) values (15, '15');
insert into currency(id, symbol) values (16, '16');
insert into currency(id, symbol) values (17, '117');
insert into currency(id, symbol) values (18, '18');
insert into currency(id, symbol) values (19, '19');
insert into currency(id, symbol) values (20, '20');
insert into currency(id, symbol) values (21, '21');
insert into currency(id, symbol) values (22, '22');
insert into currency(id, symbol) values (23, '23');
insert into currency(id, symbol) values (24, '24');
insert into currency(id, symbol) values (25, '25');
insert into currency(id, symbol) values (26, '26');
insert into currency(id, symbol) values (27, '27');
insert into currency(id, symbol) values (28, '28');
insert into currency(id, symbol) values (29, '29');
insert into currency(id, symbol) values (30, '30');
insert into currency(id, symbol) values (31, '31');
insert into currency(id, symbol) values (32, '32');
insert into currency(id, symbol) values (33, '33');
insert into currency(id, symbol) values (34, '34');
insert into currency(id, symbol) values (35, '35');
insert into currency(id, symbol) values (36, '36');
insert into currency(id, symbol) values (37, '37');
insert into currency(id, symbol) values (38, '38');
insert into currency(id, symbol) values (39, '39');
insert into currency(id, symbol) values (40, '40');
insert into currency(id, symbol) values (41, '41');
insert into currency(id, symbol) values (42, '42');
insert into currency(id, symbol) values (43, '43');
insert into currency(id, symbol) values (44, '44');
insert into currency(id, symbol) values (45, '45');
insert into currency(id, symbol) values (46, '46');
insert into currency(id, symbol) values (47, '47');
insert into currency(id, symbol) values (48, '48');
insert into currency(id, symbol) values (49, '49');
insert into currency(id, symbol) values (50, '50');
insert into currency(id, symbol) values (51, '51');
insert into currency(id, symbol) values (52, '52');
insert into currency(id, symbol) values (53, '53');
insert into currency(id, symbol) values (54, '54');
insert into currency(id, symbol) values (55, '55');
insert into currency(id, symbol) values (56, '56');
insert into currency(id, symbol) values (57, '57');
insert into currency(id, symbol) values (58, '58');
insert into currency(id, symbol) values (59, '59');
insert into currency(id, symbol) values (60, '60');
insert into currency(id, symbol) values (61, '61');
insert into currency(id, symbol) values (62, '62');
insert into currency(id, symbol) values (63, '63');
insert into currency(id, symbol) values (64, '64');
insert into currency(id, symbol) values (65, '65');
insert into currency(id, symbol) values (66, '66');
insert into currency(id, symbol) values (67, '67');
insert into currency(id, symbol) values (68, '68');
insert into currency(id, symbol) values (69, '69');
insert into currency(id, symbol) values (70, '70');
insert into currency(id, symbol) values (71, '71');
insert into currency(id, symbol) values (72, '72');
insert into currency(id, symbol) values (73, '73');
insert into currency(id, symbol) values (74, '74');
insert into currency(id, symbol) values (75, '75');
insert into currency(id, symbol) values (76, '76');
insert into currency(id, symbol) values (77, '77');
insert into currency(id, symbol) values (78, '78');
insert into currency(id, symbol) values (79, '79');
insert into currency(id, symbol) values (80, '80');
insert into currency(id, symbol) values (81, '81');
insert into currency(id, symbol) values (82, '82');
insert into currency(id, symbol) values (83, '83');
insert into currency(id, symbol) values (84, '84');
insert into currency(id, symbol) values (85, '85');
insert into currency(id, symbol) values (86, '86');
insert into currency(id, symbol) values (87, '87');
insert into currency(id, symbol) values (88, '88');
insert into currency(id, symbol) values (89, '89');
insert into currency(id, symbol) values (90, '90');
insert into currency(id, symbol) values (91, '91');
insert into currency(id, symbol) values (92, '92');
insert into currency(id, symbol) values (93, '93');
insert into currency(id, symbol) values (94, '94');
insert into currency(id, symbol) values (95, '95');
insert into currency(id, symbol) values (96, '96');
insert into currency(id, symbol) values (97, '97');
insert into currency(id, symbol) values (98, '98');
insert into currency(id, symbol) values (99, '99');
commit;
END