微信号:iMySQL_WX

介绍:叶金荣,ORACLE MySQL ACE,专注MySQL十余年,分享MySQL技术及工作心得. 个人站点:http://imysql.com,QQ群:272675472. ACMUG(http://acmug.com)官方指定运营公众号.

如何更快随机UPDATE?

2018-02-24 22:40 叶师傅

导读

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()函数

延伸阅读



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


可以加入我的知识星球


也可以来一份

老叶茶馆铁观音

以资鼓励

http://yejinrong.com


 
老叶茶馆 更多文章 简单几招提高MySQL安全性 说说MySQL ORDER BY 年终盘点|知数堂2017年度好文 基于MHA插件的MySQL高可用切换架构 深入理解MySQL 5.7 GTID系列(六):MySQL启动初始化GTID模块
猜您喜欢 5月Java就业喜报|刚毕业就年薪15万,是你想要的人生吗? 与UMCLOUD达成合作,FIT2CLOUD帮助用户更好释放云生产力 Codis架构笔记 一周阅读推荐 #2 移动终端WebAPP开发必备知识