MySQL update关联更新效率测试
【A】update t3,t4 set t3.c6=t4.c6 where t3.c1=t4.c1;
【B】update t3 set c6=(select c6 from t4 where t4.c1=t3.c1) where exists (select 1 from t4 where t4.c1=t3.c1 );
【C】update t3 set c6=(select c6 from t4 where t4.c1=t3.c1) where c1 in (select c1 from t4 where t4.c1=t3.c1 );
【D】update t3 set c6=ifnull((select c6 from t4 where t4.c1=t3.c1),c6);
一、模拟数据:
mysql> CREATE TABLE t1
(
id INT UNSIGNED NOT NULL auto_increment,
c1 INT NOT NULL DEFAULT ‘0’,
c2 INT NOT NULL DEFAULT ‘0’,
c3 INT NOT NULL DEFAULT ‘0’,
c4 INT NOT NULL DEFAULT ‘0’,
c5 TIMESTAMP NOT NULL,
c6 VARCHAR(200) NOT NULL DEFAULT ‘’,
PRIMARY KEY(id)
);
mysql> CREATE TABLE t2
(
id INT UNSIGNED NOT NULL auto_increment,
c1 INT NOT NULL DEFAULT ‘0’,
c2 INT NOT NULL DEFAULT ‘0’,
c3 INT NOT NULL DEFAULT ‘0’,
c4 INT NOT NULL DEFAULT ‘0’,
c5 TIMESTAMP NOT NULL,
c6 VARCHAR(200) NOT NULL DEFAULT ‘’,
PRIMARY KEY(id)
);
– 存储过程造数据
mysql>
delimiter !!
DROP PROCEDURE IF EXISTS insert_zh
;
CREATE PROCEDURE
insert_zh
(IN row_num INT )
begin
DECLARE i INT DEFAULT 0;WHILE i < row_num do
INSERT INTO t1
(
c1,
c2,
c3,
c4,
c5,
c6
)
VALUES
(
floor(rand()*row_num),
floor(rand()*row_num),
floor(rand()*row_num),
floor(rand()*row_num),
now(),
REPEAT(‘XiaoZhangde1B’, floor(rand()*5))
);
SET i = i+1;
END WHILE;
end !!
delimiter ;
mysql> call insert_zh(20000);
mysql>
delimiter !!
DROP PROCEDURE IF EXISTS insert_zh
;
CREATE PROCEDURE
insert_zh
(IN row_num INT )
begin
DECLARE i INT DEFAULT 0;WHILE i < row_num do
INSERT INTO t2
(
c1,
c2,
c3,
c4,
c5,
c6
)
VALUES
(
floor(rand()*row_num),
floor(rand()*row_num),
floor(rand()*row_num),
floor(rand()*row_num),
now(),
REPEAT(‘XiaoZhangde1B’, floor(rand()*5))
);
SET i = i+1;
END WHILE;
end !!
delimiter ;
mysql> call insert_zh(10000);
– c1字段去重,/考虑测试效率限定数据/
mysql> create table t3_bak select * from t1 group by c1 /limit 8000/;
mysql> create table t4_bak select * from t2 group by c1 /limit 4000/;
mysql> create table t3 select * from t3_bak;
mysql> create table t4 select * from t4_bak;
– 刷新数据
mysql> truncate t3;
mysql> truncate t4;
mysql> insert into t3 select * from t1_back;
mysql> insert into t4 select * from t2_back;
– 增加、删除索引
mysql> alter table t3 add index idx_1(c1);
mysql> alter table t4 add index idx_1(c1);
mysql> alter table t3 drop index idx_1;
mysql> alter table t4 drop index idx_1;
二、测试过程
【场景一】:t3大表、t4小表、无索引
<1> update t3,t4 set t3.c6=t4.c6 where t3.c1=t4.c1
Affected rows: 1098
时间: 36.123s
Affected rows: 0
时间: 35.174s
Affected rows: 0
时间: 35.221s
Affected rows: 0
时间: 35.29s
Affected rows: 0
时间: 35.468s
<2> update t3 set c6=(select c6 from t4 where t4.c1=t3.c1) where exists (select 1 from t4 where t4.c1=t3.c1 )
Affected rows: 1098
时间: 18.123s
Affected rows: 0
时间: 18.28s
Affected rows: 0
时间: 18.211s
Affected rows: 0
时间: 18.347s
Affected rows: 0
时间: 18.219s
<3> update t3 set c6=(select c6 from t4 where t4.c1=t3.c1) where c1 in (select c1 from t4 where t4.c1=t3.c1 )
Affected rows: 1098
时间: 18.507s
Affected rows: 0
时间: 18.471s
Affected rows: 0
时间: 18.134s
Affected rows: 0
时间: 18.117s
Affected rows: 0
时间: 18.206s
<4> update t3 set c6=ifnull((select c6 from t4 where t4.c1=t3.c1),c6)
Affected rows: 1098
时间: 18.386s
Affected rows: 0
时间: 18.215s
Affected rows: 0
时间: 18.196s
Affected rows: 0
时间: 18.247s
Affected rows: 0
时间: 17.996s
【效率小结】:B=C=D>A
【执行计划】:
mysql> explain update t3,t4 set t3.c6=t4.c6 where t3.c1=t4.c1;
±—±------------±------±-----------±-----±--------------±-----±--------±-----±-----±---------±------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
±—±------------±------±-----------±-----±--------------±-----±--------±-----±-----±---------±------------+
| 1 | SIMPLE | t4 | NULL | ALL | NULL | NULL | NULL | NULL | 3321 | 100.00 | NULL |
| 1 | UPDATE | t3 | NULL | ALL | NULL | NULL | NULL | NULL | 6665 | 10.00 | Using where |
±—±------------±------±-----------±-----±--------------±-----±--------±-----±-----±---------±------------+
2 rows in set (0.07 sec)
mysql> explain update t3 set c6=(select c6 from t4 where t4.c1=t3.c1) where exists (select 1 from t4 where t4.c1=t3.c1 );
±—±-------------------±------±-----------±-----±--------------±-----±--------±-----±-----±---------±------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
±—±-------------------±------±-----------±-----±--------------±-----±--------±-----±-----±---------±------------+
| 1 | UPDATE | t3 | NULL | ALL | NULL | NULL | NULL | NULL | 6665 | 100.00 | Using where |
| 3 | DEPENDENT SUBQUERY | t4 | NULL | ALL | NULL | NULL | NULL | NULL | 3321 | 10.00 | Using where |
| 2 | DEPENDENT SUBQUERY | t4 | NULL | ALL | NULL | NULL | NULL | NULL | 3321 | 10.00 | Using where |
±—±-------------------±------±-----------±-----±--------------±-----±--------±-----±-----±---------±------------+
3 rows in set (0.04 sec)
mysql> EXPLAIN update t3 set c6=(select c6 from t4 where t4.c1=t3.c1) where c1 in (select c1 from t4 where t4.c1=t3.c1 );
±—±-------------------±------±-----------±-----±--------------±-----±--------±-----±-----±---------±------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
±—±-------------------±------±-----------±-----±--------------±-----±--------±-----±-----±---------±------------+
| 1 | UPDATE | t3 | NULL | ALL | NULL | NULL | NULL | NULL | 6665 | 100.00 | Using where |
| 3 | DEPENDENT SUBQUERY | t4 | NULL | ALL | NULL | NULL | NULL | NULL | 3321 | 1.00 | Using where |
| 2 | DEPENDENT SUBQUERY | t4 | NULL | ALL | NULL | NULL | NULL | NULL | 3321 | 10.00 | Using where |
±—±-------------------±------±-----------±-----±--------------±-----±--------±-----±-----±---------±------------+
3 rows in set (0.04 sec)
mysql> EXPLAIN update t3 set c6=ifnull((select c6 from t4 where t4.c1=t3.c1),c6);
±—±-------------------±------±-----------±-----±--------------±-----±--------±-----±-----±---------±------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
±—±-------------------±------±-----------±-----±--------------±-----±--------±-----±-----±---------±------------+
| 1 | UPDATE | t3 | NULL | ALL | NULL | NULL | NULL | NULL | 6665 | 100.00 | NULL |
| 2 | DEPENDENT SUBQUERY | t4 | NULL | ALL | NULL | NULL | NULL | NULL | 3321 | 10.00 | Using where |
±—±-------------------±------±-----------±-----±--------------±-----±--------±-----±-----±---------±------------+
2 rows in set (0.05 sec)
【场景二】:t3大表、t4小表、有索引
<1> update t3,t4 set t3.c6=t4.c6 where t3.c1=t4.c1
Affected rows: 1098
时间: 0.101s
Affected rows: 0
时间: 0.032s
Affected rows: 0
时间: 0.03s
Affected rows: 0
时间: 0.048s
Affected rows: 0
时间: 0.029s
<2> update t3 set c6=(select c6 from t4 where t4.c1=t3.c1) where exists (select 1 from t4 where t4.c1=t3.c1 )
Affected rows: 1098
时间: 0.241s
Affected rows: 0
时间: 0.144s
Affected rows: 0
时间: 0.144s
Affected rows: 0
时间: 0.153s
Affected rows: 0
时间: 0.137s
<3> update t3 set c6=(select c6 from t4 where t4.c1=t3.c1) where c1 in (select c1 from t4 where t4.c1=t3.c1 )
Affected rows: 1098
时间: 3.941s
Affected rows: 0
时间: 3.122s
Affected rows: 0
时间: 3.321s
Affected rows: 0
时间: 3.207s
Affected rows: 0
时间: 3.125s
<4> update t3 set c6=ifnull((select c6 from t4 where t4.c1=t3.c1),c6)
Affected rows: 1098
时间: 0.29s
Affected rows: 0
时间: 0.137s
Affected rows: 0
时间: 0.135s
Affected rows: 0
时间: 0.133s
Affected rows: 0
时间: 0.132s
【效率小结】:A>B=D>C
【执行计划】:
mysql> explain update t3,t4 set t3.c6=t4.c6 where t3.c1=t4.c1;
±—±------------±------±-----------±-----±--------------±------±--------±------------±-----±---------±------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
±—±------------±------±-----------±-----±--------------±------±--------±------------±-----±---------±------+
| 1 | SIMPLE | t4 | NULL | ALL | idx_1 | NULL | NULL | NULL | 3321 | 100.00 | NULL |
| 1 | UPDATE | t3 | NULL | ref | idx_1 | idx_1 | 4 | liyue.t4.c1 | 1 | 100.00 | NULL |
±—±------------±------±-----------±-----±--------------±------±--------±------------±-----±---------±------+
2 rows in set (0.13 sec)
mysql> explain update t3 set c6=(select c6 from t4 where t4.c1=t3.c1) where exists (select 1 from t4 where t4.c1=t3.c1 );
±—±-------------------±------±-----------±-----±--------------±------±--------±------------±-----±---------±------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
±—±-------------------±------±-----------±-----±--------------±------±--------±------------±-----±---------±------------+
| 1 | UPDATE | t3 | NULL | ALL | NULL | NULL | NULL | NULL | 6665 | 100.00 | Using where |
| 3 | DEPENDENT SUBQUERY | t4 | NULL | ref | idx_1 | idx_1 | 4 | liyue.t3.c1 | 1 | 100.00 | Using index |
| 2 | DEPENDENT SUBQUERY | t4 | NULL | ref | idx_1 | idx_1 | 4 | liyue.t3.c1 | 1 | 100.00 | NULL |
±—±-------------------±------±-----------±-----±--------------±------±--------±------------±-----±---------±------------+
3 rows in set (0.06 sec)
mysql> EXPLAIN update t3 set c6=(select c6 from t4 where t4.c1=t3.c1) where c1 in (select c1 from t4 where t4.c1=t3.c1 );
±—±-------------------±------±-----------±-----±--------------±------±--------±------------±-----±---------±-------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
±—±-------------------±------±-----------±-----±--------------±------±--------±------------±-----±---------±-------------------------+
| 1 | UPDATE | t3 | NULL | ALL | NULL | NULL | NULL | NULL | 6665 | 100.00 | Using where |
| 3 | DEPENDENT SUBQUERY | t4 | NULL | ref | idx_1 | idx_1 | 4 | liyue.t3.c1 | 1 | 100.00 | Using where; Using index |
| 2 | DEPENDENT SUBQUERY | t4 | NULL | ref | idx_1 | idx_1 | 4 | liyue.t3.c1 | 1 | 100.00 | NULL |
±—±-------------------±------±-----------±-----±--------------±------±--------±------------±-----±---------±-------------------------+
3 rows in set (0.05 sec)
mysql> EXPLAIN update t3 set c6=ifnull((select c6 from t4 where t4.c1=t3.c1),c6);
±—±-------------------±------±-----------±-----±--------------±------±--------±------------±-----±---------±------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
±—±-------------------±------±-----------±-----±--------------±------±--------±------------±-----±---------±------+
| 1 | UPDATE | t3 | NULL | ALL | NULL | NULL | NULL | NULL | 6665 | 100.00 | NULL |
| 2 | DEPENDENT SUBQUERY | t4 | NULL | ref | idx_1 | idx_1 | 4 | liyue.t3.c1 | 1 | 100.00 | NULL |
±—±-------------------±------±-----------±-----±--------------±------±--------±------------±-----±---------±------+
2 rows in set (0.07 sec)
【场景三】:t3大表、t4小表、无索引
<1> update t4,t3 set t4.c6=t3.c6 where t4.c1=t3.c1
Affected rows: 1098
时间: 33.297s
Affected rows: 0
时间: 32.191s
Affected rows: 0
时间: 33.433s
Affected rows: 0
时间: 31.537s
Affected rows: 0
时间: 31.445s
<2> update t4 set c6=(select c6 from t3 where t3.c1=t4.c1) where exists (select 1 from t3 where t3.c1=t4.c1 )
Affected rows: 1098
时间: 18.943s
Affected rows: 0
时间: 18.467s
Affected rows: 0
时间: 20.54s
Affected rows: 0
时间: 18.636s
Affected rows: 0
时间: 18.579s
<3> update t4 set c6=(select c6 from t3 where t3.c1=t4.c1) where c1 in (select c1 from t3 where t3.c1=t4.c1 )
Affected rows: 1098
时间: 19.504s
Affected rows: 0
时间: 18.957s
Affected rows: 0
时间: 18.391s
Affected rows: 0
时间: 18.684s
Affected rows: 0
时间: 18.501s
<4> update t4 set c6=ifnull((select c6 from t3 where t3.c1=t4.c1),c6)
Affected rows: 1098
时间: 18.826s
Affected rows: 0
时间: 17.848s
Affected rows: 0
时间: 18.561s
Affected rows: 0
时间: 18.691s
Affected rows: 0
时间: 18.091s
【效率小结】:B=C=D>A
【执行计划】:
mysql> explain update t4,t3 set t4.c6=t3.c6 where t4.c1=t3.c1;
±—±------------±------±-----------±-----±--------------±-----±--------±-----±-----±---------±------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
±—±------------±------±-----------±-----±--------------±-----±--------±-----±-----±---------±------------+
| 1 | UPDATE | t4 | NULL | ALL | NULL | NULL | NULL | NULL | 3321 | 100.00 | NULL |
| 1 | SIMPLE | t3 | NULL | ALL | NULL | NULL | NULL | NULL | 6665 | 10.00 | Using where |
±—±------------±------±-----------±-----±--------------±-----±--------±-----±-----±---------±------------+
2 rows in set (0.11 sec)
mysql> explain update t4 set c6=(select c6 from t3 where t3.c1=t4.c1) where exists (select 1 from t3 where t3.c1=t4.c1 );
±—±-------------------±------±-----------±-----±--------------±-----±--------±-----±-----±---------±------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
±—±-------------------±------±-----------±-----±--------------±-----±--------±-----±-----±---------±------------+
| 1 | UPDATE | t4 | NULL | ALL | NULL | NULL | NULL | NULL | 3321 | 100.00 | Using where |
| 3 | DEPENDENT SUBQUERY | t3 | NULL | ALL | NULL | NULL | NULL | NULL | 6665 | 10.00 | Using where |
| 2 | DEPENDENT SUBQUERY | t3 | NULL | ALL | NULL | NULL | NULL | NULL | 6665 | 10.00 | Using where |
±—±-------------------±------±-----------±-----±--------------±-----±--------±-----±-----±---------±------------+
3 rows in set (0.05 sec)
mysql> EXPLAIN update t4 set c6=(select c6 from t3 where t3.c1=t4.c1) where c1 in (select c1 from t3 where t3.c1=t4.c1 );
±—±-------------------±------±-----------±-----±--------------±-----±--------±-----±-----±---------±------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
±—±-------------------±------±-----------±-----±--------------±-----±--------±-----±-----±---------±------------+
| 1 | UPDATE | t4 | NULL | ALL | NULL | NULL | NULL | NULL | 3321 | 100.00 | Using where |
| 3 | DEPENDENT SUBQUERY | t3 | NULL | ALL | NULL | NULL | NULL | NULL | 6665 | 1.00 | Using where |
| 2 | DEPENDENT SUBQUERY | t3 | NULL | ALL | NULL | NULL | NULL | NULL | 6665 | 10.00 | Using where |
±—±-------------------±------±-----------±-----±--------------±-----±--------±-----±-----±---------±------------+
3 rows in set (0.04 sec)
mysql> EXPLAIN update t4 set c6=ifnull((select c6 from t3 where t3.c1=t4.c1),c6);
±—±-------------------±------±-----------±-----±--------------±-----±--------±-----±-----±---------±------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
±—±-------------------±------±-----------±-----±--------------±-----±--------±-----±-----±---------±------------+
| 1 | UPDATE | t4 | NULL | ALL | NULL | NULL | NULL | NULL | 3321 | 100.00 | NULL |
| 2 | DEPENDENT SUBQUERY | t3 | NULL | ALL | NULL | NULL | NULL | NULL | 6665 | 10.00 | Using where |
±—±-------------------±------±-----------±-----±--------------±-----±--------±-----±-----±---------±------------+
2 rows in set (0.05 sec)
【场景四】:t3大表、t4小表、有索引
<1> update t4,t3 set t4.c6=t3.c6 where t4.c1=t3.c1
Affected rows: 1098
时间: 0.189s
Affected rows: 0
时间: 0.025s
Affected rows: 0
时间: 0.025s
Affected rows: 0
时间: 0.025s
Affected rows: 0
时间: 0.025s
<2> update t3 set c6=(select c6 from t4 where t4.c1=t3.c1) where exists (select 1 from t4 where t4.c1=t3.c1 )
Affected rows: 1098
时间: 0.406s
Affected rows: 0
时间: 0.138s
Affected rows: 0
时间: 0.14s
Affected rows: 0
时间: 0.147s
Affected rows: 0
时间: 0.142s
<3> update t4 set c6=(select c6 from t3 where t3.c1=t4.c1) where c1 in (select c1 from t3 where t3.c1=t4.c1 )
Affected rows: 1098
时间: 0.263s
Affected rows: 0
时间: 0.078s
Affected rows: 0
时间: 0.079s
Affected rows: 0
时间: 0.08s
Affected rows: 0
时间: 0.079s
<4> update t3 set c6=ifnull((select c6 from t4 where t4.c1=t3.c1),c6)
Affected rows: 1098
时间: 0.222s
Affected rows: 0
时间: 0.137s
Affected rows: 0
时间: 0.137s
Affected rows: 0
时间: 0.132s
Affected rows: 0
时间: 0.138s
【效率小结】:A>C=D>B
mysql> explain update t4,t3 set t4.c6=t3.c6 where t4.c1=t3.c1;
±—±------------±------±-----------±-----±--------------±------±--------±------------±-----±---------±------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
±—±------------±------±-----------±-----±--------------±------±--------±------------±-----±---------±------+
| 1 | UPDATE | t4 | NULL | ALL | idx_1 | NULL | NULL | NULL | 3321 | 100.00 | NULL |
| 1 | SIMPLE | t3 | NULL | ref | idx_1 | idx_1 | 4 | liyue.t4.c1 | 1 | 100.00 | NULL |
±—±------------±------±-----------±-----±--------------±------±--------±------------±-----±---------±------+
2 rows in set (0.07 sec)
mysql> explain update t4 set c6=(select c6 from t3 where t3.c1=t4.c1) where exists (select 1 from t3 where t3.c1=t4.c1 );
±—±-------------------±------±-----------±-----±--------------±------±--------±------------±-----±---------±------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
±—±-------------------±------±-----------±-----±--------------±------±--------±------------±-----±---------±------------+
| 1 | UPDATE | t4 | NULL | ALL | NULL | NULL | NULL | NULL | 3321 | 100.00 | Using where |
| 3 | DEPENDENT SUBQUERY | t3 | NULL | ref | idx_1 | idx_1 | 4 | liyue.t4.c1 | 1 | 100.00 | Using index |
| 2 | DEPENDENT SUBQUERY | t3 | NULL | ref | idx_1 | idx_1 | 4 | liyue.t4.c1 | 1 | 100.00 | NULL |
±—±-------------------±------±-----------±-----±--------------±------±--------±------------±-----±---------±------------+
3 rows in set (0.05 sec)
mysql> EXPLAIN update t4 set c6=(select c6 from t3 where t3.c1=t4.c1) where c1 in (select c1 from t3 where t3.c1=t4.c1 );
±—±-------------------±------±-----------±-----±--------------±------±--------±------------±-----±---------±-------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
±—±-------------------±------±-----------±-----±--------------±------±--------±------------±-----±---------±-------------------------+
| 1 | UPDATE | t4 | NULL | ALL | NULL | NULL | NULL | NULL | 3321 | 100.00 | Using where |
| 3 | DEPENDENT SUBQUERY | t3 | NULL | ref | idx_1 | idx_1 | 4 | liyue.t4.c1 | 1 | 100.00 | Using where; Using index |
| 2 | DEPENDENT SUBQUERY | t3 | NULL | ref | idx_1 | idx_1 | 4 | liyue.t4.c1 | 1 | 100.00 | NULL |
±—±-------------------±------±-----------±-----±--------------±------±--------±------------±-----±---------±-------------------------+
3 rows in set (0.06 sec)
mysql> EXPLAIN update t4 set c6=ifnull((select c6 from t3 where t3.c1=t4.c1),c6);
±—±-------------------±------±-----------±-----±--------------±------±--------±------------±-----±---------±------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
±—±-------------------±------±-----------±-----±--------------±------±--------±------------±-----±---------±------+
| 1 | UPDATE | t4 | NULL | ALL | NULL | NULL | NULL | NULL | 3321 | 100.00 | NULL |
| 2 | DEPENDENT SUBQUERY | t3 | NULL | ref | idx_1 | idx_1 | 4 | liyue.t4.c1 | 1 | 100.00 | NULL |
±—±-------------------±------±-----------±-----±--------------±------±--------±------------±-----±---------±------+
2 rows in set (0.06 sec)
三、结果分析
A:有索引最快,无索引最慢,无论修改的表时大表还是小表
B、C:要看修改的表是大表还是小表从而做出选择
D:无论大表小表,有无索引,效率都尚可