如何更快随机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

长期坚持原创分享实属不易，点赞和转发就是最好的认可

可以加入我的知识星球

也可以来一份

「老叶茶馆」铁观音

以资鼓励

http://yejinrong.com