mysql的upsert与查询插入更新性能、速度对比

前言

当实际工作中遇到这么一种需求：数据库没有这条数据，我需要新增，有这条数据我需要修改，或者某个字段的数量进行调整。

那么我们就可以考虑使用mysql的upsert语句了。

使用php语言对比mysql的upsert效率

upsert写法性能测试

1.代码片段使用mysql的upsert,代码片段

$query = "insert into tbl_referral_link_member_retain
            (report_date,day,referral_link_id,referral_link_quto_date,retain_count)
            VALUES
            ('2024-04-23',1,1,'2024-04-22',1)
            ON DUPLICATE KEY UPDATE
            retain_count = retain_count + 1";
$rows = $db->query($query);
        dump($rows);            

执行1000次耗时

执行2000次耗时

传统写法性能测试

2.代码片段使用php查询，存在则更新、不存在则插入,代码片段

		$db = $this->referral_link_member_model->get_table_retain()->getDb();
        $where = ['referral_link_id'=>1,'referral_link_quto_date'=>'2024-04-22','day'=>1,'report_date'=>'2024-04-23'];
		if($this->referral_link_member_model->get_table_retain()->find($where,['select'=>'id'])){
            $query = "update tbl_referral_link_member_retain
                    set retain_count = retain_count + 1 where referral_link_id = 1 and referral_link_quto_date = '2024-04-22' and day = 1 and report_date = '2024-04-23'";
            $rows = $db->query($query);
            dump($rows);
        }else {
            $this->referral_link_member_model->get_table_retain()->insert($where);
            dump("insert");
        }
     

执行1000次耗时
​​

​​​​执行2000次耗时

为了更加深入比较
去掉find执行、直接update,代码片段

	//测试mysql upsert性能
    public function test_find_insert_and_update() {
        $db = $this->referral_link_member_model->get_table_retain()->getDb();
        $where = ['referral_link_id'=>1,'referral_link_quto_date'=>'2024-04-22','day'=>1,'report_date'=>'2024-04-23'];
        //if($this->referral_link_member_model->get_table_retain()->find($where,['select'=>'id'])){
            $query = "update tbl_referral_link_member_retain
                    set retain_count = retain_count + 1 where referral_link_id = 1 and referral_link_quto_date = '2024-04-22' and day = 1 and report_date = '2024-04-23'";
            $rows = $db->query($query);
            dump($rows);
//        }else {
//            $this->referral_link_member_model->get_table_retain()->insert($where);
//            dump("insert");
//        }
    }

​​​​执行1000次耗时

直接update1000次其实跟upsert的执行时间差不多，1.4秒与1.2秒的区别

结论

upsert代码更加简洁的同时、执行效率更加快，差不多将近快1倍的速度