如何更快随机UPDATE?

2018-03-01 11:12:19来源:oschina作者:Mr_zebra人点击

分享

UPDATE + RAND()怎么可以更快?


有时候,我们随机更新几行数据,可能会下意识的直接写成下面的SQL:


[[email protected]]> 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

这就尴尬了。


关注我网站(http://imysql.com)的同学,可能还记得我以前还写过一个关于随机排序的分享:[MySQL优化案例]系列 — RAND()优化。可以借鉴这篇文章的思路,把上面的SQL用JOIN改造一下:


[[email protected]]> 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:
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 的代价:我有几张阿里云幸运券分享给你,用券购买或者升级阿里云相应产品会有特惠惊喜哦!把想要买的产品的幸运券都领走吧!快下手,马上就要抢光了。


[[email protected]]>UPDATE t1 SET c1 = 3 WHERE id = ROUND(RAND()*102400);
Query OK, 1 row affected (0.69 sec)
[[email protected]]>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 |
+-----------------------+--------+
[[email protected]]>show profile for query 5;
...
| System lock | 0.000040 |
| updating| 0.691625 |
| end| 0.000020 |
| query end | 0.000515 |
...
[[email protected]]>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)
[[email protected]]>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 |
+-----------------------+-------+
[[email protected]]>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:


[[email protected]]> set @rnd_id=ROUND(RAND()*102400);UPDATE t1 SET c1=3 WHERE id>[email protected]_id LIMIT 2;

最后记住重点:不要在WHERE子句中直接使用RAND()函数。

最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台