如何更快随机UPDATE？
导读
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这就尴尬了。
关注我网站(http://imysql.com)的同学，可能还记得我以前还写过一个关于随机排序的分享：[MySQL优化案例]系列 — RAND()优化。可以借鉴这篇文章的思路，把上面的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()函数。
延伸阅读
- [MySQL优化案例]系列 — RAND()优化
- https://dev.mysql.com/doc/refman/5.7/en/mathematical-functions.html#function_rand
本文参与 腾讯云自媒体分享计划，分享自微信公众号。
原始发表：2018-02-24，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
目录
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server （TencentDB for SQL Server）是业界最常用的商用数据库之一，对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权，可持续为用户提供最新的功能，避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。