AI 助理
备案控制台
开发者社区 老叶茶馆 文章 正文

[MySQL优化案例]系列 -- RAND()优化

2021-11-18 275
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云数据库 RDS MySQL，集群系列 2核4GB
推荐场景：
搭建个人博客
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
RDS MySQL Serverless 高可用系列，价值2615元额度，1个月
简介： [MySQL优化案例]系列 -- RAND()优化

众所周知，在MySQL中，如果直接 ORDER BY RAND() 的话，效率非常差，因为会多次执行。事实上，如果等值查询也是用 RAND() 的话也如此，我们先来看看下面这几个SQL的不同执行计划和执行耗时。

首先，看下建表DDL，这是一个没有显式自增主键的InnoDB表：

[yejr@imysql]> show create table t_innodb_random\G

*************************** 1. row***************************

Table:t_innodb_random

Create Table: CREATE TABLE `t_innodb_random` (

`id` int(10)unsigned NOT NULL,

`user` varchar(64)NOT NULL DEFAULT '',

KEY `idx_id` (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

往这个表里灌入一些测试数据，至少10万以上， id 字段也是乱序的。

[yejr@imysql]> select count(*) from t_innodb_random\G

*************************** 1. row***************************

count(*): 393216

1、常量等值检索：

[yejr@imysql]> explain select id fromt_innodb_random where id = 13412\G

*************************** 1. row***************************

id: 1

select_type: SIMPLE

table:t_innodb_random

type: ref

possible_keys: idx_id

key: idx_id

key_len: 4

ref: const

rows: 1

Extra: Using index

[yejr@imysql]> select id from t_innodb_random where id =13412;

1 row in set (0.00 sec)

可以看到执行计划很不错，是常量等值查询，速度非常快。

2、使用RAND()函数乘以常量，求得随机数后检索：

[yejr@imysql]> explain select id from t_innodb_randomwhere id = round(rand()*13241324)\G

*************************** 1. row***************************

id: 1

select_type: SIMPLE

table:t_innodb_random

type: index

possible_keys: NULL

key: idx_id

key_len: 4

ref: NULL

rows: 393345

Extra: Using where; Using index

[yejr@imysql]> select id from t_innodb_randomwhere id = round(rand()*13241324)\G

Empty set (0.26 sec)

可以看到执行计划很糟糕，虽然是只扫描索引，但是做了全索引扫描，效率非常差。因为WHERE条件中包含了RAND()，使得MySQL把它当做变量来处理，无法用常量等值的方式查询，效率很低。

我们把常量改成取t_innodb_random表的最大id值，再乘以RAND()求得随机数后检索看看什么情况：

[yejr@imysql]> explain select id from t_innodb_randomwhere id = round(rand()*(select max(id) from t_innodb_random))\G

*************************** 1. row***************************

id: 1

select_type:PRIMARY

table:t_innodb_random

type: index

possible_keys: NULL

key: idx_id

key_len: 4

ref: NULL

rows: 393345

Extra: Using where; Using index

*************************** 2. row***************************

id: 2

select_type:SUBQUERY

table: NULL

type: NULL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: NULL

Extra: Selecttables optimized away

[yejr@imysql]> select id from t_innodb_randomwhere id = round(rand()*(select max(id) from t_innodb_random))\G

Empty set (0.27 sec)

可以看到，执行计划依然是全索引扫描，执行耗时也基本相当。

3、改造成普通子查询模式 ，这里有两次子查询

[yejr@imysql]> explain select id fromt_innodb_random where id = (select round(rand()*(select max(id) fromt_innodb_random)) as nid)\G

*************************** 1. row***************************

id: 1

select_type:PRIMARY

table:t_innodb_random

type: index

possible_keys: NULL

key: idx_id

key_len: 4

ref: NULL

rows: 393345

Extra: Using where; Using index

*************************** 2. row***************************

id: 3

select_type:SUBQUERY

table: NULL

type: NULL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: NULL

Extra: Selecttables optimized away

[yejr@imysql]> select id from t_innodb_randomwhere id = (select round(rand()*(select max(id) from t_innodb_random)) as nid)\G

Empty set (0.27 sec)

可以看到，执行计划也不好，执行耗时较慢。

4、改造成JOIN关联查询，不过最大值还是用常量表示

[yejr@imysql]> explain select id fromt_innodb_random t1 join (select round(rand()*13241324) as id2) as t2 wheret1.id = t2.id2\G

*************************** 1. row***************************

id: 1

select_type:PRIMARY

table:<derived2>

type: system

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 1

Extra:

*************************** 2. row***************************

id: 1

select_type:PRIMARY

table: t1

type: ref

possible_keys: idx_id

key: idx_id

key_len: 4

ref: const

rows: 1

Extra: Using where; Using index

*************************** 3. row***************************

id: 2

select_type:DERIVED

table: NULL

type: NULL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: NULL

Extra: Notables used

[yejr@imysql]> select id from t_innodb_randomt1 join (select round(rand()*13241324) as id2) as t2 where t1.id =t2.id2\G

Empty set (0.00 sec)

这时候执行计划就非常完美了，和最开始的常量等值查询是一样的了，执行耗时也非常之快。

这种方法虽然很好，但是有可能查询不到记录，改造范围查找，但结果LIMIT 1就可以了：

[yejr@imysql]> explain select id fromt_innodb_random where id > (select round(rand()*(select max(id) fromt_innodb_random)) as nid) limit 1\G

*************************** 1. row***************************

id: 1

select_type:PRIMARY

table:t_innodb_random

type: index

possible_keys: NULL

key: idx_id

key_len: 4

ref: NULL

rows: 393345

Extra: Using where; Using index

*************************** 2. row***************************

id: 3

select_type:SUBQUERY

table: NULL

type: NULL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: NULL

Extra: Selecttables optimized away

[yejr@imysql]> select id from t_innodb_randomwhere id > (select round(rand()*(select max(id) from t_innodb_random)) asnid) limit 1\G

*************************** 1. row***************************

id: 1301

1 row in set (0.00 sec)

可以看到，虽然执行计划也是全索引扫描，但是因为有了LIMIT 1，只需要找到一条记录，即可终止扫描，所以效率还是很快的。

小结：

从数据库中随机取一条记录时，可以把RAND()生成随机数放在JOIN子查询中以提高效率。

5、再来看看用ORDRRBY RAND()方式一次取得多个随机值的方式：

[yejr@imysql]> explain select id from t_innodb_randomorder by rand() limit 1000\G

*************************** 1. row***************************

id: 1

select_type: SIMPLE

table:t_innodb_random

type: index

possible_keys: NULL

key: idx_id

key_len: 4

ref: NULL

rows: 393345

Extra: Using index; Using temporary; Using filesort

[yejr@imysql]> select id from t_innodb_randomorder by rand() limit 1000;

1000 rows in set (0.41 sec)

全索引扫描，生成排序临时表，太差太慢了。

6、把随机数放在子查询里看看：

[yejr@imysql]> explain select id fromt_innodb_random where id > (select rand() * (select max(id) fromt_innodb_random) as nid) limit 1000\G

*************************** 1. row***************************

id: 1

select_type:PRIMARY

table:t_innodb_random

type: index

possible_keys: NULL

key: idx_id

key_len: 4

ref: NULL

rows: 393345

Extra: Using where; Using index

*************************** 2. row ***************************

id: 3

select_type:SUBQUERY

table: NULL

type: NULL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: NULL

Extra: Selecttables optimized away

[yejr@imysql]> select id from t_innodb_randomwhere id > (select rand() * (select max(id) from t_innodb_random) as nid)limit 1000\G

1000 rows in set (0.04 sec)

嗯，提速了不少，这个看起来还不赖：）

7、仿照上面的方法，改成JOIN和随机数子查询关联

[yejr@imysql]> explain select id fromt_innodb_random t1 join (select rand() * (select max(id) from t_innodb_random)as nid) t2 on t1.id > t2.nid limit 1000\G

*************************** 1. row***************************

id: 1

select_type:PRIMARY

table:<derived2>

type: system

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 1

Extra:

*************************** 2. row***************************

id: 1

select_type:PRIMARY

table: t1

type: range

possible_keys: idx_id

key: idx_id

key_len: 4

ref: NULL

rows: 196672

Extra: Using where; Using index

*************************** 3. row***************************

id: 2

select_type:DERIVED

table: NULL

type: NULL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: NULL

Extra: Notables used

*************************** 4. row***************************

id: 3

select_type:SUBQUERY

table: NULL

type: NULL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: NULL

Extra: Selecttables optimized away

[yejr@imysql]> select id from t_innodb_randomt1 join (select rand() * (select max(id) from t_innodb_random) as nid) t2 ont1.id > t2.nid limit 1000\G

1000 rows in set (0.00 sec)

可以看到，全索引检索，发现符合记录的条件后，直接取得1000行，这个方法是最快的。

综上，想从MySQL数据库中随机取一条或者N条记录时，最好把RAND()生成随机数放在JOIN子查询中以提高效率。

上面说了那么多的废话，最后简单说下，就是把下面这个SQL：

SELECT id FROM table ORDER BY RAND() LIMIT n;

改造成下面这个：

SELECT id FROM table t1, JOIN (SELECT RAND() * (SELECTMAX(id) FROM table) AS nid) t2 ON t1.id > t2.nid LIMIT n;

就可以享受在SQL中直接取得随机数了，不用再在程序中构造一串随机数去检索了。




文章标签：
云数据库 RDS MySQL 版
SQL
关系型数据库
索引
MySQL
数据库
关键词：
云数据库 RDS MySQL 版优化
云数据库 RDS MySQL 版案例
云数据库 RDS MySQL 版rand
云数据库 RDS MySQL 版优化案例rand
云数据库 RDS MySQL 版优化案例
相关实践学习
如何在云端创建MySQL数据库
开始实验后，系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
开发者小达人
目录
相关文章
mikechen的互联网架构
|
1天前
|
SQL 关系型数据库 MySQL
MySQL慢查询优化、索引优化、以及表等优化详解
本文详细介绍了MySQL优化方案，包括索引优化、SQL慢查询优化和数据库表优化，帮助提升数据库性能。关注【mikechen的互联网架构】，10年+BAT架构经验倾囊相授。
mikechen的互联网架构
21 9
MySQL慢查询优化、索引优化、以及表等优化详解
vohelon
|
5天前
|
缓存 监控 关系型数据库
如何优化MySQL查询速度？
如何优化MySQL查询速度？【10月更文挑战第31天】
vohelon
20 3
mao29955
|
8天前
|
缓存 关系型数据库 MySQL
如何优化 MySQL 数据库的性能？
【10月更文挑战第28天】
mao29955
28 1
最好zzz
|
15天前
|
NoSQL 关系型数据库 MySQL
MySQL与Redis协同作战：百万级数据统计优化实践
【10月更文挑战第21天】 在处理大规模数据集时，传统的单体数据库解决方案往往力不从心。MySQL和Redis的组合提供了一种高效的解决方案，通过将数据库操作与高速缓存相结合，可以显著提升数据处理的性能。本文将分享一次实际的优化案例，探讨如何利用MySQL和Redis共同实现百万级数据统计的优化。
最好zzz
46 9
潇洒洒
|
15天前
|
NoSQL 关系型数据库 MySQL
MySQL与Redis协同作战：优化百万数据查询的实战经验
【10月更文挑战第13天】 在处理大规模数据集时，传统的关系型数据库如MySQL可能会遇到性能瓶颈。为了提升数据处理的效率，我们可以结合使用MySQL和Redis，利用两者的优势来优化数据查询。本文将分享一次实战经验，探讨如何通过MySQL与Redis的协同工作来优化百万级数据统计。
潇洒洒
42 5
mzlogin
|
15天前
|
关系型数据库 MySQL 数据库
一个 MySQL 数据库死锁的案例和解决方案
本文介绍了一个 MySQL 数据库死锁的案例和解决方案。
mzlogin
19 3
小王老师呀
|
18天前
|
存储 关系型数据库 MySQL
基于案例分析 MySQL 权限认证中的具体优先原则
【10月更文挑战第26天】本文通过具体案例分析了MySQL权限认证中的优先原则，包括全局权限、数据库级别权限和表级别权限的设置与优先级。全局权限优先于数据库级别权限，后者又优先于表级别权限。在权限冲突时，更严格的权限将被优先执行，确保数据库的安全性与资源合理分配。
小王老师呀
42 4
mao29955
|
20天前
|
存储 关系型数据库 MySQL
优化 MySQL 的锁机制以提高并发性能
【10月更文挑战第16天】优化 MySQL 锁机制需要综合考虑多个因素，根据具体的应用场景和需求进行针对性的调整。通过不断地优化和改进，可以提高数据库的并发性能，提升系统的整体效率。
mao29955
28 1
小王老师呀
|
20天前
|
缓存 关系型数据库 MySQL
一文彻底弄懂MySQL优化之深度分页
【10月更文挑战第24天】本文深入探讨了 MySQL 深度分页的原理、常见问题及优化策略。首先解释了深度分页的概念及其带来的性能和资源问题。接着介绍了基于偏移量（OFFSET）和限制（LIMIT）以及基于游标的分页方法，并分析了它们的优缺点。最后，提出了多种优化策略，包括合理创建索引、优化查询语句和使用数据缓存，帮助提升分页查询的性能和系统稳定性。
小王老师呀
65 1
潇洒洒
|
23天前
|
NoSQL 关系型数据库 MySQL
MySQL与Redis协同作战：百万数据量的优化实录
【10月更文挑战第6天】 在现代互联网应用中，随着用户量的增加和业务逻辑的复杂化，数据量级迅速增长，这对后端数据库系统提出了严峻的挑战。尤其是当数据量达到百万级别时，传统的数据库解决方案往往会遇到性能瓶颈。本文将分享一次使用MySQL与Redis协同优化大规模数据统计的实战经验。
潇洒洒
91 3

老叶茶馆

热门文章

最新文章

  • 1
    测试开启MySQL performance_schema后对性能的影响
  • 2
    【MySQL专题】MySQL百万级数据插入效率优化
  • 3
    mysql修改用户密码
  • 4
    一台mysql管理多个实例
  • 5
    MySQL-5.5.33主从复制
  • 6
    删除MySQL重复数据
  • 7
    ELK搭建（四）：监控mysql慢查询、错误日志日志
  • 8
    应用MySQL MGR
  • 9
    干货分享 ｜ 揭秘下一代云数据库引擎MyBase for MySQL
  • 10
    SSH框架+mysql+tomcat 服务器 中文乱码解决方案
  • 1
    《MySQL 入门教程》第 05 篇 账户和权限，Java高并发编程详解深入理解pdf
    52
  • 2
    实时计算 Flink版产品使用合集之支持 MySQL 的并行复制吗
    71
  • 3
    实时计算 Flink版产品使用合集之flink-connector-mysql-cdc 和 flink-sql-connector-mysql-cdc有什么区别
    355
  • 4
    Mysql优化提高笔记整理，来自于一位鹅厂大佬的笔记，阿里P7亲自教你
    58
  • 5
    Echarts+vue+java+mysql实现数据可视化
    181
  • 6
    MYSQL--JSON_OBJECT 和 JSON_ARRAYAGG
    159
  • 7
    实时计算 Flink版产品使用合集之flink-connector-mysql-cdc 和 flink-connector-sqlserver-cdc 这两个CDC连接器只能选择其中一个使用吗
    156
  • 8
    MYSQL根据查询结果删除sql 去除重复id 新增对比前一条与后一条数据 去重3种方法​ 窗口函数
    124
  • 9
    MySQL 实现数据分片如何进行数据查询
    248
  • 10
    MySQL各字符集、排序规则的由来、用法，区别和联系
    129

    • 相关课程

    更多
  • MySQL企业常见架构与调优经验分享
  • 云数据库MySQL版快速上手教程
  • 阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
  • MySQL实战进阶
  • 数据库及SQL/MySQL基础
  • 云数据库MySQL快速入门

    • 相关电子书

    更多
  • 搭建电商项目架构连接MySQL
  • 搭建4层电商项目架构，实战连接MySQL
  • PolarDB MySQL引擎重磅功能及产品能力盛大发布

    • 相关实验场景

    更多
  • 灵活弹性的PolarDB Serverless确保数据业务持续在线
  • 云端MySQL的安装及应用
  • 体验PolarDB MySQL多主集群
  • 基于ECS+RDS的在线留言系统
  • PolarDB MySQL HTAP：实时数据分析加速
  • MySQL数据库DTS迁移上云

    • 推荐镜像

    更多
  • mysql
  • postgresql
  • mariadb
    • 下一篇
    无影云桌面