如何更快随机UPDATE？
如何更快随机UPDATE？
wubx
发布于 2019-02-27 17:32:33
发布于 2019-02-27 17:32:33
导读
UPDATE + RAND()怎么可以更快？
有时候，我们随机更新几行数据，可能会下意识的直接写成下面的SQL：
[yejr@imysql]> UPDATE t1 SET c1 = ? WHERE id = ROUND(RAND() * 102400);不过你可能不知道，这个SQL的效率极低，需要进行全表扫描，因为无法使用索引：
[yejr]@[imysql.com]> EXPLAIN UPDATE t1 SET c1 = 3 WHERE id = ROUND(RAND() * 102400);
*************************** 1. row ***************************
id: 1
select_type: UPDATE
table: t1
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 102400
filtered: 100.00
Extra: Using where这就尴尬了。
把之前写的SQL用JOIN改造一下：
[yejr@imysql]> EXPLAIN UPDATE t1, (SELECT ROUND(RAND() * (SELECT MAX(id) FROM t1)) AS rndid) t2 SET t1.c1=3 WHERE t1.id=t2.rndid;
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: <derived2>
partitions: NULL
type: system
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1
filtered: 100.00
Extra: NULL
*************************** 2. row ***************************
id: 1
select_type: UPDATE
table: t1
partitions: NULL
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: const
rows: 1
filtered: 100.00
Extra: NULL
*************************** 3. row ***************************
id: 2
select_type: DERIVED
table: NULL
partitions: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
filtered: NULL
Extra: No tables used
*************************** 4. row ***************************
id: 3
select_type: SUBQUERY
table: NULL
partitions: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
filtered: NULL
Extra: Select tables optimized away再来看下两种 UPDATE 的代价：
[yejr@imysql]>UPDATE t1 SET c1 = 3 WHERE id = ROUND(RAND()*102400);
Query OK, 1 row affected (0.69 sec)
[yejr@imysql]>SHOW STATUS LIKE 'handler%read%';
+-----------------------+--------+
| Variable_name | Value |
+-----------------------+--------+
| Handler_read_first | 1 |
| Handler_read_key | 1 |
| Handler_read_last | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 799995 |
+-----------------------+--------+
[yejr@imysql]>show profile for query 5;
...
| System lock | 0.000040 |
| updating | 0.691625 |
| end | 0.000020 |
| query end | 0.000515 |
...
[yejr@imysql]>UPDATE t1, (SELECT ROUND(RAND() * (SELECT MAX(id) FROM t1)) AS rndid) t2 SET t1.c1=3 WHERE t1.id=t2.rndid;
Query OK, 1 row affected (0.02 sec)
[yejr@imysql]>SHOW STATUS LIKE 'handler%read%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 1 |
| Handler_read_key | 3 |
| Handler_read_last | 1 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 1 |
| Handler_read_rnd_next | 3 |
+-----------------------+-------+
[yejr@imysql]>show profile for query 6;
...
| updating reference tables | 0.011772 |
| end | 0.000040 |
| end | 0.000012 |
| removing tmp table | 0.000018 |
| end | 0.000005 |
...
| query end | 0.014745 |
...不过，上面这种多表UPDATE（Multiple-table UPDATE）有局限性，就是只能更新一行记录，不能同时更新多行，所以也可以改写成下面的SQL：
[yejr@imysql]> set @rnd_id=ROUND(RAND()*102400); UPDATE t1 SET c1=3 WHERE id>=@rnd_id LIMIT 2;最后记住重点：不要在WHERE子句中直接使用RAND()函数。
本文参与 腾讯云自媒体分享计划，分享自微信公众号。
原始发表：2018-02-24，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
目录