mysql随机查询一条数据性能优化

第一种语句0.055秒查询

    <?php
    $offset_result = mysql_query( " SELECT FLOOR(RAND() * COUNT(*)) AS `offset` FROM `table` ");
    $offset_row = mysql_fetch_object( $offset_result );
    $offset = $offset_row->offset;
    $result = mysql_query( " SELECT * FROM `table` LIMIT $offset, 1 " );

第二种语句。0.001秒查询

    SELECT * 
    FROM `dede_arctiny` AS t1 JOIN (SELECT ROUND(RAND() * ((SELECT MAX(id) FROM `dede_arctiny`)-(SELECT MIN(id) FROM `dede_arctiny`))+(SELECT MIN(id) FROM `dede_arctiny`)) AS id) AS t2 
    WHERE t1.id >= t2.id 
    ORDER BY t1.id LIMIT 1;

但是这样获得的是5条连续的记录。解决办法只能是每次查询一条，查询5次,但这个又不能满足我的要求了，我要一次找几条

高效写法

    SELECT * FROM USER  WHERE userId >= ((SELECT MAX(userId) FROM USER )-(SELECT MIN(userId) FROM USER )) * RAND() + (SELECT MIN(userId) FROM USER )  LIMIT 5

这样测试发现满足了我的要求同时测试10W数据也只要0.0几秒非常的快。如果把*换成单独查询id给数组，然后根据数组id单独查出一条记录，会更有效率。

代码实例：

   $r = mysql_query("SELECT file_id FROM pd_files WHERE file_id >= (( SELECT MAX(file_id) FROM pd_files ) - ( SELECT MIN(file_id) FROM pd_files )) * RAND() + ( SELECT MIN(file_id) FROM pd_files ) LIMIT 14");
    $arr = array();
    while ($row = mysql_fetch_assoc($r))
    {
    $arr[] = $row;
    }
    $s0 = $arr[0]['file_id'];
    $s1 = $arr[1]['file_id'];
    print($s0);//用来测试
    $get_shuchu = mysql_query("select * from (SELECT file_id,file_name,file_time,file_extension FROM pd_files WHERE file_id=$s0)t
    union all
    select * from (SELECT file_id,file_name,file_time,file_extension FROM pd_files WHERE file_id=$s1)t
    union all
    select * from (SELECT file_id,file_name,file_time,file_extension FROM pd_files WHERE file_id=$s2)t");
    print($get_shuchu);//用来测试

发表评论 取消回复

发表评论取消回复