首页 / MYSQL / MySQL优化之对RAND()的优化方法

MySQL优化之对RAND()的优化方法

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了MySQL优化之对RAND()的优化方法，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含9175字，纯文字阅读大概需要14分钟。

内容图文

众所周知，在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 from t_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_random where 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_random where 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_random where 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: Select tables optimized away

[yejr@imysql]> select id from t_innodb_random where id = round(rand()*(select max(id) from t_innodb_random))\G
Empty set (0.27 sec)

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

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

代码如下:

[yejr@imysql]> explain select id from t_innodb_random where id = (select round(rand()*(select max(id) from t_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: Select tables optimized away

[yejr@imysql]> select id from t_innodb_random where 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 from t_innodb_random t1 join (select round(rand()*13241324) as id2) as t2 where t1.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: No tables used

[yejr@imysql]> select id from t_innodb_random t1 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 from t_innodb_random where id > (select round(rand()*(select max(id) from t_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: Select tables optimized away

[yejr@imysql]> select id from t_innodb_random where id > (select round(rand()*(select max(id) from t_innodb_random)) as nid) limit 1\G
*************************** 1. row ***************************
id: 1301
1 row in set (0.00 sec)

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

小结：

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

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

代码如下:

[yejr@imysql]> explain select id from t_innodb_random order 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_random order by rand() limit 1000;
1000 rows in set (0.41 sec)

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

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

代码如下:

[yejr@imysql]> explain select id from t_innodb_random where id > (select rand() * (select max(id) from t_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: Select tables optimized away

[yejr@imysql]> select id from t_innodb_random where 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 from t_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: No tables 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: Select tables optimized away

[yejr@imysql]> select id from t_innodb_random t1 join (select rand() * (select max(id) from t_innodb_random) as nid) t2 on t1.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() * (SELECT MAX(id) FROM table) AS nid) t2 ON t1.id > t2.nid LIMIT n;

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

您可能感兴趣的文章:

MySQL的指定范围随机数函数rand()的使用技巧
mysql 某字段插入随机数(插入随机数到MySQL数据库)
MySQL查询随机数据的4种方法和性能对比
mysql获取随机数据的方法
mysql优化取随机数据慢的方法
MySQL取出随机数据
从MySQL数据库表中取出随机数据的代码
MySQL中的RAND()函数使用详解
MySQL下的RAND()优化案例分析
MySQL Order By Rand()效率分析
mysql中RAND()随便查询记录效率问题和解决办法分享
MYSQL随机抽取查询 MySQL Order By Rand()效率问题
php 随机记录mysql rand()造成CPU 100%的解决办法
MySQL rand函数实现随机数的方法

内容总结

以上是互联网集市为您收集整理的MySQL优化之对RAND()的优化方法全部内容，希望文章能够帮你解决MySQL优化之对RAND()的优化方法所遇到的程序开发问题。如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容，请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/584566.html

来源：【匿名】

【上一篇】MySQL定期分析检查与优化表的方法小结【下一篇】用IE远程创建Mysql数据库的简易程序

更多 ►

【MySQL优化之对RAND()的优化方法】教程文章相关的互联网学习教程文章

Mysql 优化原理【图】

说起MySQL的查询优化，相信大家收藏了一堆奇技淫巧：不能使用SELECT *、不使用NULL字段、合理创建索引、为字段选择合适的数据类型..... 你是否真的理解这些优化技巧？是否理解其背后的工作原理？在实际场景下性能真有提升吗？我想未必。因而理解这些优化建议背后的原理就尤为重要，希望本文能让你重新审视这些优化建议，并在实际业务场景下合理的运用。 MySQL逻辑架构如果能在头脑中构建一幅MySQL各组件之间如何协同工作的架构图，...

运行环境为centos7，tomcat和mysql的优化方式，经验总结【代码】

一般在服务的cpu占用很高时，1.使用ps -mp pid -o THREAD,tid,time命令查看该进程的线程情况，可以列举出占用高的线程，挑选个占用高的线程的tid，eg:挑选TID为14065的线程，查看该线程的堆栈情况，先将线程id转为16进制，使用printf "%x\n" tid命令进行转换(printf "%x\n" 14065)再使用jstack命令打印线程堆栈信息，命令格式：jstack pid |grep tid -A 30(pid:进程id，tid：转换后的线程id的16进制)，可以打印出堆栈信息，在堆栈信...

MySQL优化技巧【代码】【图】

原文:MySQL优化技巧目录MySQL的特点数据类型优化整型类型小数类型字符串类型时间类型主键类型的选择特殊类型的数据索引优化一个使用Hash值创建索引的技巧前缀索引多列索引聚簇索引覆盖索引重复索引和冗余索引不使用的索引索引使用总结查询优化查询慢的原因重构查询的方式杂七杂八参考书本文的内容是总结一些MySQL的常见使用技巧，以供没有DBA的团队参考。以下内容以MySQL5.5为准，如无特殊说明，存储引擎以InnoDB为准。MySQL的特点...

[mysql][【优化集合】mysql数据库优化集合【代码】

三个层面：1.系统层面2.mysql配置参数3.sql语句优化===========================================================一。系统层面 ===========================================================二。mysql参数层面http://www.oicto.com/mysql-explain-show/2.1slowlog配置slowlog 配置文件： log-slow-queries=/path/to/save/slowlog long_time_query=2 变量设置： show variables like ‘%slow%‘setglobal log_slow_queries = ON...

mysql优化limit查询语句的5个方法

mysql的分页比较简单，只需要limit offset,length就可以获取数据了，但是当offset和length比较大的时候，mysql明显性能下降1.子查询优化法先找出第一条数据，然后大于等于这条数据的id就是要获取的数据缺点：数据必须是连续的，可以说不能有where条件，where条件会筛选数据，导致数据失去连续性，具体方法请看下面的查询实例：复制代码代码如下: mysql> set profiling=1; Query OK, 0 rows affected (0.00 sec) mysql> select c...

mysql优化--explain分析sql语句执行效率

Explain命令在解决数据库性能上是第一推荐使用命令，大部分的性能问题可以通过此命令来简单的解决，Explain可以用来查看SQL语句的执行效果，可以帮助选择更好的索引和优化查询语句，写出更好的优化语句。Explain语法：explain select … from … [where …]例如：explain select * from news;输出：+----+-------------+-------+-------+-------------------+---------+---------+-------+------| id | select_type | table | typ...

Mysql优化（一）

总结下MySql优化。防止数据灾难的发生。

在PHP开发中用到的数据库中MySql是最牛逼的数据库，没有之一……^_^相比Sqlite个人最喜欢的特性就是“支持多线程，充分利用 CPU 资源”，不像Sqlite那样，动不动就锁死，导致存储发生异常，特别是.NET开发桌面应用时，为了方便用了Sqlite，那真的是先甜后苦啊，有苦说不出的苦。MySql遇到比较多的就是商城系统、博客系统，订单和数据存储达到一定量级之后，会出现慢查询，这个问题在开发维护过程中谁也躲不过去。下面是常规的处理方...

Mysql 优化建议【代码】

1、硬件层相关优化1.1、CPU相关在服务器的BIOS设置中，可调整下面的几个配置，目的是发挥CPU最大性能，或者避免经典的NUMA问题：1、选择Performance Per Watt Optimized(DAPC)模式，发挥CPU最大性能，跑DB这种通常需要高运算量的服务就不要考虑节电了； 2、关闭C1E和C States等选项，目的也是为了提升CPU效率3、Memory Frequency（内存频率）选择Maximum Performance（最佳性能）； 4、内存设置菜单中，启用Node Interleaving，避免...

Mysql优化之索引和字段【图】

Mysql优化是一个老生常谈的问题,优化的方向也优化很多:从架构层;从设计层;从存储层;从SQL语句层;今天讲解一下从索引和字段:字段优化:① 尽量使用TINYINT、SMALLINT、MEDIUM_INT作为整数类型而非INT，如果非负则加上UNSIGNED;② VARCHAR的长度只分配真正需要的空间;③ 使用枚举或整数代替字符串类型;④ 尽量使用TIMESTAMP而非DATETIME;⑤ 单表不要有太多字段，建议在20以内;⑥ 避免使用NULL字段，很难查询优化且占用额外索引空...

MySQL 优化实施方案【代码】

1.1 前言　在进行MySQL的优化之前必须要了解的就是MySQL的查询过程，很多的查询优化工作实际上就是遵循一些原则让MySQL的优化器能够按照预想的合理方式运行而已。更多关于MySQL查询相关参照：http://www.cnblogs.com/clsn/p/8038964.html#_label6 系列文章。图 - MySQL查询过程1.2 优化的哲学优化有风险，涉足需谨慎1.2.1 优化可能带来的问题优化不总是对一个单纯的环境进行，还很可能是一个复杂的已投产的系统。优化手段本来...

mysql的优化:官网地址

http://dev.mysql.com/doc/refman/5.1/zh/optimization.html#index-merge-optimization原文：http://www.cnblogs.com/ribavnu/p/3541273.html

MySql 优化全集【代码】【图】

前言：在日常工作中，我们需要对查询较慢的SQL进行分析，这时我们就会用到explain这个命令来查看一个这些SQL语句的执行计划，查看该SQL语句有没有使用上了索引，有没有做全表扫描。-- 分析SQL EXPLAIN SELECT * FROM zy_ticket_sw_classify WHERE industry_name = ‘石油开采‘； expain出来的信息有10列，分别是id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra描述：id：选择标识符；如果explain的...

大牛是怎么思考设计MySQL优化方案的？【代码】【图】

在进行MySQL的优化之前，必须要了解的就是MySQL的查询过程，很多查询优化工作实际上就是遵循一些原则，让MySQL的优化器能够按照预想的合理方式运行而已。图-MySQL查询过程一、优化的哲学注：优化有风险，涉足需谨慎1、优化可能带来的问题优化不总是对一个单纯的环境进行，还很可能是一个复杂的已投产的系统；优化手段本来就有很大的风险，只不过你没能力意识到和预见到；任何的技术可以解决一个问题，但必然存在带来一个问题的风险...

[MySQL优化] -- 如何使用SQL Profiler 性能分析器

mysql 的 sql 性能分析器主要用途是显示 sql 执行的整个过程中各项资源的使用情况。分析器可以更好的展示出不良 SQL 的性能问题所在。下面我们举例介绍一下 MySQL SQL Profiler 的使用方法：首先，开启 MySQL SQL Profilermysql> SELECT @@profiling; +-------------+ | @@profiling | +-------------+ | 0 | +-------------+ 1 row in set (0.00 sec) mysql> SET profiling = 1; Query OK, 0 rows affected (0.00 sec) mysql> S...

MYSQL - 技术教程分类

MySQL 教程 MySQL 安装 MySQL 管理 MySQL PHP 语法 MySQL 连接 MySQL 创建数据库 MySQL 删除数据库 MySQL 选择数据库 MySQL 数据类型 MySQL 创建数据表 MySQL 删除数据表 MySQL 插入数据 MySQL 查询数据 MySQL WHERE 子句 MySQL UPDATE 更新 MySQL DELETE 语句 MySQL LIKE 子句 MySQL UNION MySQL 排序 MySQL 分组 MySQL 连接的使用 MySQL NULL 值处理 MySQL 事务 MySQL ALTER命令 MySQL 索引 MySQL 临时表 MySQL 复制表 MySQL 元数据 MySQL 序列使用 MySQL 处理重复数据 MySQL 及 SQL 注入 MySQL 导出数据 MySQL 导入数据 MySQL 函数 MySQL 运算符 mysql 全部

MYSQL - 最热教程

sql分组取最大记录方法 mysql如何设置默认值 mysql创建数据表时指定默认值教程 MySQL 5.7 的初始化操作（root初始密码...Oracle中合并数据集(多行变一行)mysql 相同内容的字段合并为一条的方法解决mysql设置时区时的错误Unknown or ...解决ubuntu下mysql的'Access denied fo...ubuntu系统中MysqlERROR1045(28000)报错...mysql数据库设置不区分大小写

首页 / MYSQL / MySQL优化之对RAND()的优化方法

MySQL优化之对RAND()的优化方法

内容导读

内容图文

您可能感兴趣的文章:

内容总结

内容备注

内容手机端

【MySQL优化之对RAND()的优化方法】教程文章相关的互联网学习教程文章

Mysql 优化原理【图】

运行环境为centos7，tomcat和mysql的优化方式，经验总结【代码】

MySQL优化技巧【代码】【图】

[mysql][【优化集合】mysql数据库优化集合【代码】

mysql优化limit查询语句的5个方法

mysql优化--explain分析sql语句执行效率

Mysql优化（一）

总结下MySql优化。防止数据灾难的发生。

Mysql 优化建议【代码】

Mysql优化之索引和字段【图】

MySQL 优化实施方案【代码】

mysql的优化:官网地址

MySql 优化全集【代码】【图】

大牛是怎么思考设计MySQL优化方案的？【代码】【图】

[MySQL优化] -- 如何使用SQL Profiler 性能分析器

MYSQL - 相关标签

MYSQL - 技术教程分类

MYSQL - 最新教程

MYSQL - 最热教程