mysql rand在子查询_随机函数rand作子查询的bug ???

最新推荐文章于 2021-03-29 00:28:48 发布
最新推荐文章于 2021-03-29 00:28:48 发布
阅读量174 收藏
点赞数
文章标签： mysql rand在子查询
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_36081109/article/details/113935461
版权

环境信息：

系统版本: CentOS release 6.10

配置：虚机、64G内存、16C逻辑CPU

版本：5.7.12-log MySQL Community Server (GPL)

pt-query-digest mysql-slow.log --since '2020-11-01 00:01:00' --until '2020-11-09 10:00:00' >slow_report1.log# Attribute    pct   total     min     max     avg     95%  stddev  median

# ============ === ======= ======= ======= ======= ======= ======= =======

# Count          5     302

# Exec time     11  37083s      5s    673s    123s    246s     88s    107s

# Lock time      1    78ms   153us   586us   257us   366us    62us   247us

# Rows sent      0     302       1       1       1       1       0       1

# Rows examine  91 162.88G  23.16M   2.96G 552.29M   1.08G 395.64M 483.92M

# Query size    17  84.94k     288     288     288     288       0     288

# Query_time distribution

SELECT

*

FROM

t_test_info

WHERE id >=

(SELECT

FLOOR(MAX(id) * RAND())

FROM

t_test_info)

AND area_code = '5400'

ORDER BY id

LIMIT 1\G

以上的慢SQL，平均执行时间为123s，看着是很简单的SQL，基本逻辑是：查询t_test_info表id大于等于它本身最大的id乘以个随机数取整，同时区域编号为5400的数据，根据id排序后，取第一行数据。t_test_info的数据量为6w+,并不是非常大。

表结构如下：CREATE TABLE `t_test_info` (

`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',

`con_id` bigint(20) DEFAULT NULL COMMENT '连接id',

........,

`area_code` varchar(8) DEFAULT NULL COMMENT '地区编码',

`ctime` datetime DEFAULT NULL COMMENT '创建日期',

........,

PRIMARY KEY (`id`) USING BTREE,

........,

KEY `idx_con_id` (`con_id`) USING BTREE,

KEY `idx_ctime` (`ctime`)

) ENGINE=InnoDB AUTO_INCREMENT=94681 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC;

对应的执行计划：id select_type      table     partitions  type   partition_key  key    key_len  rows    filtered   Extra

1 PRIMARY         t_test_info          index            PRIMARY    8      1      10      Using where

2 UNCACHEABLE SUBQUERY t_test_info         index            idx_ctime  6      63713    100      Using index

UNCACHEABLE SUBQUERY：对于外层的主表，一个子查询的结果不能被缓存，每次都需要计算(耗时操作)

发现这执行计划存在问题，idx_ctime这个索引是ctime列的，整个SQL都未出现ctime这一列，难道是优化器选错索引？考虑ingore index(idx_ctime) 去干预，发现它就选择了idx_con_id这个索引，忽略考虑ingore index(idx_ctime) 去干预，发现它就选择了idx_con_id索引又会选择下一个。所以从执行计划来看，虽然是使用了索引，但filtered过滤的是100%的数据，跟全表扫描没啥区别。

这里也考虑说select * 全字段数据，换成select id减少SQL执行阶段Sending data的时间，执行时间也是一样的，没有区别。

FORMAT=JSON格式的执行计划，查看资源消耗情况，根据SQL的逻辑，看资源消耗是正常的(先进行子查询外部的查询,得到一个结果集63713行数据,然后这个结果的每一行在跟select子查询的结果集进行匹配){

"query_block": {

"select_id": 1,

"cost_info": {

"query_cost": "13801.60"

},

"ordering_operation": {

"using_filesort": false,

"table": {

"table_name": "t_test_info",

"access_type": "index",

"key": "PRIMARY",

"used_key_parts": [

"id"

],

"key_length": "8",

"rows_examined_per_scan": 1,

"rows_produced_per_join": 6371,

"filtered": "10.00",

"cost_info": {

"read_cost": "12527.34",

"eval_cost": "1274.26",

"prefix_cost": "13801.60",

"data_read_per_join": "12M"

},

"used_columns": [

"id",

"con_id",

.......

"area_code",

"ctime",

.......

],

"attached_condition": "((`zw`.`t_test_info`.`id` >= (/* select#2 */ select floor((max(`zw`.`t_test_info`.`id`) * rand())) from `zw`.`t_test_info`)) and (`zw`.`t_test_info`.`area_code` = '5400'))",

"attached_subqueries": [

{

"dependent": false,

"cacheable": false,

"query_block": {

"select_id": 2,

"cost_info": {

"query_cost": "13801.60"

},

"table": {

"table_name": "t_test_info",

"access_type": "index",

"key": "idx_ctime",

"used_key_parts": [

"gmt_created"

],

"key_length": "6",

"rows_examined_per_scan": 63713,

"rows_produced_per_join": 63713,

"filtered": "100.00",

"using_index": true,

"cost_info": {

"read_cost": "1059.00",

"eval_cost": "12742.60",

"prefix_cost": "13801.60",

"data_read_per_join": "126M"

},

"used_columns": [

"id"

]

开启optimizer_trace，对该sql执行的过程进行跟踪分析:set optimizer_trace='enabled=on';

set optimizer_trace_max_mem_size=10000;

SELECT *  FROM

t_test_info

WHERE id >=

(SELECT

FLOOR(MAX(id) * RAND())

FROM

t_test_info)

AND area_code = '5400'

ORDER BY id

LIMIT 1;

select * from information_schema.optimizer_trace;

部分结果：

"join_execution": {

"select#": 1,

"steps": [

{

"subselect_execution": {

"select#": 2,

"steps": [

{

"join_execution": {

"select#": 2,

"steps": [

},

-- 出现3039次相同的内容，这里感觉是异常的,一直都在执行子查询部分，陷入某个循环里面在空转？

{

"subselect_execution": {

"select#": 2,

"steps": [

{

"join_execution": {

"select#": 2,

"steps": [

}

同时使用pstask进行记录堆栈信息for((a=0;a<1;a--));

do

pstack 19175 >> /tmp/pstack_1110.log

done

这里的19175 是根据performance_schema.threads查询该SQL对应的THREAD_OS_ID。

通过pt-pmp对trace信息汇总 pt-pmp /tmp/pstack_1110.log：

ba9f7145aabcc37146b9d967dacb7450.png

/tmp/pstack_1110.log 部分信息Thread 1 (process 19175):

#0  0x00000000010afbf7 in row_search_mvcc(unsigned char*, page_cur_mode_t, row_prebuilt_t*, unsigned long, unsigned long) ()

#1  0x0000000000fc357e in ha_innobase::general_fetch(unsigned char*, unsigned int, unsigned int) ()

#2  0x0000000000815551 in handler::ha_index_next(unsigned char*) ()

#3  0x0000000000cd811c in join_read_next(READ_RECORD*) ()

#4  0x0000000000cdcc06 in sub_select(JOIN*, QEP_TAB*, bool) ()

#5  0x0000000000cdbb7a in JOIN::exec() ()

#6  0x0000000000c0e6b0 in subselect_single_select_engine::exec() ()

#7  0x0000000000c10696 in Item_subselect::exec() ()

#8  0x0000000000c0cd69 in Item_singlerow_subselect::val_real() ()

#9  0x0000000000841080 in Arg_comparator::compare_real_fixed() ()

#10 0x0000000000849592 in Item_func_ge::val_int() ()

#11 0x000000000082592c in Item::val_bool() ()

#12 0x000000000084364a in Item_cond_and::val_int() ()

#13 0x0000000000cdb274 in evaluate_join_record(JOIN*, QEP_TAB*) ()

#14 0x0000000000cdcc53 in sub_select(JOIN*, QEP_TAB*, bool) ()

#15 0x0000000000cdbb7a in JOIN::exec() ()

#16 0x0000000000d45db0 in handle_query(THD*, LEX*, Query_result*, unsigned long long, unsigned long long) ()

#17 0x0000000000d06e93 in execute_sqlcom_select(THD*, TABLE_LIST*) ()

#18 0x0000000000d0a82a in mysql_execute_command(THD*, bool) ()

#19 0x0000000000d0c52d in mysql_parse(THD*, Parser_state*) ()

#20 0x0000000000d0d6be in dispatch_command(THD*, COM_DATA const*, enum_server_command) ()

#21 0x0000000000d0e324 in do_command(THD*) ()

#22 0x0000000000dded7c in handle_connection ()

#23 0x00000000012436d4 in pfs_spawn_thread ()

#24 0x0000003f65407aa1 in start_thread () from /lib64/libpthread.so.0

#25 0x0000003f650e8c4d in clone () from /lib64/libc.so.6

根据pt-pmp的统计，以上的次数多达237次。这里感觉是陷入某内部函数循环里面，需要多次判断取值才能返回。要想找到根因，只能看源码了，个人能力有限，先记录下来，有大神感兴趣可以研究一下。

到这里，我考虑将rand函数换成一个常量，查看执行计划并执行，发现是不一样的，执行时间也是毫秒级。EXPLAIN SELECT

*  FROM  t_test_info   WHERE  id >= (SELECT  FLOOR(MAX(id) * 0.02)  FROM  t_test_info)

AND area_code = '5400'  ORDER BY  id  LIMIT 1;

id select_type  table  partitions type partition_key key    key_len  rows   filtered   Extra

1 PRIMARY     t_test_info       range PRIMARY    PRIMARY  8     31856 10            Using where

2 SUBQUERY                                                         Select tables optimized away

为进一步猜想是随机函数作为子查询的bug(也可能是UNCACHEABLE SUBQUERY问题，我更倾向这个原因，能力有限，还无法定论，大神可以指正一下)，那么在当前版本5.7.12上可能存在，在别的版本已经修复，于是我在测试环境找了个8.0.20版本的库，构建了相同表结构，并造了相同数量的数据。

先对比了执行计划，发现执行计划是一样的， 8.0.20版本的执行计划：id select_type      table     partitions  type   partition_key  key    key_len  rows    filtered   Extra

1 PRIMARY         t_test_info          index            PRIMARY    8      1      10      Using where

2 UNCACHEABLE SUBQUERY t_test_info         index            idx_ctime  6      63713    100      Using index

不同的是，在8.0.20的环境上，执行时间大概是0.03秒，差了几万倍，差别太大了。

回到5.7.12环境上，我根据SQL实现的逻辑，进行了改写，避免rand函数作为子查询，按照子查询改写为join的思路如下：SELECT

a.*

FROM

t_test_info a join

(

SELECT

FLOOR(MAX(id) * RAND()) xx

FROM

t_test_info

) b

where  a.id>=b.xx

AND a.area_code = '5400'

ORDER BY

a.id

LIMIT 1;

以上SQL在5.7.12环境上毫秒级就可以出结果，但该SQL会产生笛卡尔积，如果数据量很大，性能也会下降。并非合适的解决方案。

于是再按照避免rand函数作为子查询思路，拆分成两个SQL，先获取一个随机数，以传参的方式，进行查询，这种在代码层也相对较好实现。SELECT

FLOOR(MAX(id) * RAND()) into @str

FROM

t_test_info;

SELECT

*

FROM

t_test_info

WHERE

id >= @str

AND area_code = '5400'

ORDER BY

id

LIMIT 1;

在8.0以上版本，可以考虑使用with as的方式构建个临时表的方式进行改写，5.7不支持该语法。WITH cc AS (

SELECT

floor(max(id) * rand())

FROM

t_test_info

) SELECT

*

FROM

t_test_info

WHERE

area_code = '5400'

AND id >= (SELECT * FROM cc)

ORDER BY

id

LIMIT 1;

后记，后续参考了官方资料，更倾向是执行计划走UNCACHEABLE SUBQUERY这个原因导致的

https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

https://dev.mysql.com/doc/refman/5.7/en/query-cache-operation.html

同时参考《MySQL：查询字段数量多少对查询效率的影响》

岁月未染
关注
mysql数据库中求圆的面积_MYSQL数据库
weixin_36096137的博客
01-19 2318
数据库：数据库就是存储数据的仓库为了方便数据的存储和管理，它将数据按照特定的规律存储在磁盘上，通过数据库的管理系统，可以有效的管理存储在数据库中的数据；mysql数据库的优点：1.多语言的支持2.可以移植性好，3.免费开源，4.高效5.支持大量数据的存储和查询sql语言：就是结构化查询语言 数据管理系统通过sql语言来管理数据库中的数据其中DDL语句：create alter dropDML语...
mysql变量命名规则_MySQL命名、设计及使用规范
weixin_30419031的博客
01-21 2689
导语最近在看MySQL相关的内容，整理如下规范，作为一名刚刚学习MySQL的菜鸟，整理的内容非常的基础，中间可能涉及到有错误的地方，欢迎批评指正，看到有错误的地方期望看官留言。数据库环境dev：开发环境，开发可读写，可修改表结构。开发人员可以修改表结构，可以随意修改其中的数据但是需要保证不影响其他开发同事。qa：测试环境，开发可读写，开发人员可以通过工具修改表结构。sim：模拟环境，开发可读写，发...
MySQL的bug：子查询中使用rand()，上层查询中rand()的值被重算
淡然坊
09-29 1609
  在使用mysql的过程中，发现了一个神奇的bug。   mysql语句如下： select random_val from (select floor(rand()*10) as random_val from Test) b where b.random_val &lt;3;   其中，Test表是个无关轻重的表（毕竟并没有从里面取数据，只是得到的结果跟Test内的数据条数有关），...
mysql随机取数据的rand_MySQL实现表中取出随机数据
weixin_39634237的博客
01-28 135
以前在群里讨论过这个问题,比较的有意思.mysql的语法真好玩.他们原来都想用PHP的实现随机,但取出多条好像要进行两次以上查询.翻了手册,找到了下面这个语句,可以完成任务了。SELECT * FROM table_name ORDER BY rand() LIMIT 5;rand在手册里是这么说的:RAND()RAND(N)返回在范围0到1.0内的随机浮点值。如果一个整数参数N被指定，它被用作种...
6. 查询性能优化
chenlexh的专栏
12-07 169
6.1 为什么查询速度会慢（这个标题有毛病？查询要是快我还学你干嘛？） 6.2 慢查询基础：优化数据访问 6.2.1 是否向数据库请求了不需要的数据 6.2.2 MySQL是否在扫描额外的记录 响应时间 扫描的行数 返回的行数 6.3 重构查询的方式 6.3.1 一个复杂的查询还是多个简单查询 6.3.2 切分查询 6.3.3 分解关联查询 6.4 查询执行的基础 客户端--> 查询缓存-->解析器-->预处理器-->查询优化器-->查询执行引擎--&g...
(转)mysql随机查询的优化 mysql随机函数RAND()的使用方法
iteye_12449的博客
11-07 154
mysql随机查询最常见的写法如下: Php代码   SELECT * FROM tablename ORDER BY RAND() LIMIT 1   SELECT * FROM tablename ORDER BY RAND() LIMIT 1 php手册上如此解释: About selecting random rows from a MySQL table...
Mycat 之MySQL随机函数又爆Bug
12-10 657
今天测试了一下Mycat中的MySQL随机函数，又发现一个这样的Bug 2016-12-10 22:38:39,109 [INFO ][$_NIOREACTOR-2-RW] close connection,reason:quit cmd ,ServerConnection [id=4, schema=TESTDB, host=127.0.0.1, user=root,txIsolation=...
mysql8新特性集锦
qq_34556369的博客
08-19 204
1. 默认字符集由latin1变为utf8mb4 在8.0版本之前，默认字符集为latin1，utf8指向的是utf8mb3，8.0版本默认字符集为utf8mb4，utf8默认指向的也是utf8mb4。 注：在Percona Server 8.0.15版本上测试，utf8仍然指向的是utf8mb3，与官方文档有出入。 Warning | 3719 | 'utf8' is currently an...
default mysql_MySQL 中数据类型的默认值 - default 约束
weixin_36328527的博客
01-18 2064
MySQL 中，所有的数据类型，都可以显式或隐式的拥有默认值。我们可以使用 DEFAULT 约束显式的为列指定一个默认值。比如CREATE TABLE t1 (i INT DEFAULT -1,c VARCHAR(10) DEFAULT '',price DOUBLE(16,2) DEFAULT 0.00);在上面这条语句中，我们为 int 类型的 i 列指定了默认值 -1 ，为 ...
mysql 用户名 命名_MySQL命名、设计及使用规范《MySQL命名、设计及使用规范》
weixin_35876760的博客
01-25 1529
数据库环境dev：开发环境，开发可读写，可修改表结构。开发人员可以修改表结构，可以随意修改其中的数据但是需要保证不影响其他开发同事。qa：测试环境，开发可读写，开发人员可以通过工具修改表结构。sim：模拟环境，开发可读写，发起上线请求时，会先在这个环境上进行预执行，这个环境也可供部署上线演练或压力测试使用。real：生产数据库从库(准实时同步)，只读环境，不允许修改数据，不允许修改表结构，供线上问...
php mysql 随机优化,mysql 随机取一条记要优化
weixin_35943776的博客
03-29 97
mysql 随机取一条记录优化表结构：CREATE TABLE test_rand(id int(11) AUTO_INCREMENT PRIMARY KEY,c2 varchar(30) DEFAULT NULL, c3 date DEFAULT NULL);插入800W条记录；随机取一条记录：(id已建立索引)SELECT * FROM test_rand order by rand() li...
多表查询
sjwl0526的博客
09-09 1595
多表查询 概述 基础语法： select ​ 列名列表 from ​ 表名列表 where… 多表查询的分类 内连接查询： 隐式内查询： 使用where消除无用的数据 显式内查询 外链接查询： 子查询：
rand函数_技术分享 | 使用 RAND() 函数过程中发现的诡异 Bug 分析
weixin_39616686的博客
11-27 483
作者：Agate Li爱可生研发团队成员，负责数据库管理平台相关项目，.Net 技术爱好者，长期潜水于技术圈。本文来源：原创投稿*爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。背景MySQL 中的 RAND() 函数是一个随机数发生器，可以返回一个 >=0 并 <1.0 的随机浮点数。最近在实际使用过程里遇见了一个主流版本中非常诡异的 Bug，故整理出来，...
mysql随机取数据的rand_MySQL中rand函数随机取数据介绍
weixin_34977480的博客
01-19 305
网上基本上都是查询max(id) * rand()来随机获取数据。代码如下复制代码SELECT *FROM `table` AS t1 JOIN (SELECT ROUND(RAND() * (SELECT MAX(id) FROM `table`)) AS id) AS t2WHERE t1.id >= t2.idORDER BY t1.id ASC LIMIT 5;但是这样会产生连续的5...
sql随机获获取数据
duanghuang的博客
08-24 4446
有朋友问到如何在mysql中使用随机数   如何写一个语句能一下更新几百条MYSQL数据！ 需要测试MYSQL数据库，里面有一个上万条数据的数据库，如何写一个PHP文件一下每次更新几百条信息，我都是写一个循环一次更新一条信息，这样我知道用WHILE写就可以了，要是一次更新好比100条数据改如何写呢！ 谢谢   正确答案是：UPDATE cdb_posts  SE
MySQL随机查询rand()优化
tianlianchao1982的专栏
02-05 1415
一直以为MySQL随机查询几条数据，就用 SELECT * FROM `table` ORDER BY RAND() LIMIT 5 就可以了。 但是真正测试一下才发现这样效率非常低。一个15 万余条的库，查询5条数据，居然要8秒以上 查看官方手册，也说rand()放在ORDER BY 子句中会被执行多次，自然效率及很低。 You cannot use a column
mysql查询结果随机取一个_从SQL查询结果随机取一条数据
weixin_35844772的博客
02-06 3500
最近工作中会涉及复杂的SQL语句，还是挺有意思的。以前想写没有应用场景，现在正好可以多锻炼锻炼。我觉得在实际开发中，程序代码并不是最难的，最难的还是SQL。SQL逻辑性最强，而且还要很清楚表设计，业务场景。特别是复杂的业条场景下提炼数据，要考虑到功能，性能，是否会锁表。需求：有一张表保存诺干条广告信息。当APP启动时，根据登录用户所在的区域，随机取出一条广告返回，作为启动页。数据库：MySQL刚开...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值