order by rand()优化
2024-10-08 19:03:16
优化前：
SELECT
id,
loan_id,
NAME,
company
FROM
tablename
WHERE
time BETWEEN 1522512000
AND 1525103999
AND loan_time BETWEEN 1522512000
AND 1525103999
AND STATUS = 1
AND is_again_yd = 0
AND company <> 1
order by rand()
LIMIT 30
优化方案一：
采用JOIN，查询max(id) * rand()来随机获取数据。
SELECT
t1.id,
t1.loan_id,
t1. NAME,
t1.company
FROM
`tablename` AS t1
JOIN (
SELECT
ROUND(
RAND() * (
SELECT
MAX(id)
FROM
`tablename`
)
) AS id
) AS t2
WHERE
t1.id >= t2.id
AND time BETWEEN 1522512000
AND 1525103999
AND loan_time BETWEEN 1522512000
AND 1525103999
AND STATUS = 1
AND is_again_yd = 0
AND company <> 1
ORDER BY t1.id ASC
LIMIT 30;
优化方案二：
SELECT
t1.id,
t1.loan_id,
t1.NAME
FROM
tablename t1
WHERE
t1.id >= (
SELECT
floor(
RAND() * (
SELECT
MAX(id)
FROM
`tablename`
)
)
)
AND t1.time BETWEEN 1522512000 AND 1525103999
AND t1.STATUS IN (1, 2, 4)
AND t1.type IN (1, 2)
AND t1.belong_company = 0
AND t1.is_del = 0
AND NOT EXISTS (
SELECT
loan_id
FROM
tablename2 t2
WHERE
t2.loan_id = t1.loan_id
AND t2.company = 1
)
ORDER BY id
limit 20
说明：这种方案替代mysql rand(),性能太幅度提升，随机性没有rand()强，如果随机性强的请选择替代方案。
最新文章
- [No0000A8]Word中设置图片下的题注及插入多级列表编号
- 51nod1185(wythoff+高精度)
- Power of Three
- PHP访问带密码的Redis
- LA 5135 井下矿工
- const,static,volatile
- ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var mysql 启动不了
- mysql+mybatis 插入可递增字段库表操作
- Codeforces Round #340 (Div. 2) B. Chocolate 水题
- java中的hashcode和euqals的区别和联系
- 字符串匹配算法——KMP算法学习
- oracle 临时表学习
- 在ajax当中使用url重写来避免url的暴露
- Asp.net--Ajax前后台数据交互
- PAT - 基础 - 龟兔赛跑
- android复习-AnsyTask
- SecureCRT安装
- [IOT] 自制蓝牙工牌办公室定位系统 （一）—— 阿里物联网平台概览及打通端到云（硬核·干货）
- 自学Aruba5.3.1-Aruba安全认证-有PEFNG 许可证环境的认证配置OPEN、PSK
- java发送get，post请求