阅读:794回复:1

建议优化一下random 。有优化demo

楼主#
更多 发布于:2023-05-04 18:51
order by rand()  性能太差了。
demo:

<?php
function generateRandomRecordsSQL($tableName, $primaryKey, $num) {
    $crossJoinNumbers = implode(" UNION SELECT ", range(1, $num));


    $sql = "SELECT t2.*
    FROM (
        SELECT DISTINCT
            FLOOR((SELECT MIN({$primaryKey}) FROM {$tableName}) +
            (SELECT (MAX({$primaryKey}) - MIN({$primaryKey})) FROM {$tableName}) * RAND()) AS id
        FROM {$tableName}
        CROSS JOIN (SELECT {$crossJoinNumbers}) AS num
        LIMIT {$num}
    ) AS t1
    INNER JOIN {$tableName} AS t2 ON t2.{$primaryKey} = t1.id;";


    return $sql;
}
// 按需调用该函数
$numOfRecords = 20;
$tableName = 'sea_news';
$primaryKey = 'n_id';
$sql = generateRandomRecordsSQL($tableName, $primaryKey, $numOfRecords);
echo $sql;
?>测试100万条数据,随机都是0.00几秒。。
[roo于2023-05-04 18:57编辑了帖子]
1楼#
发布于:2023-05-07 18:22
感谢反馈,接下来会进行测试。
游客

返回顶部