• mysql order by rand() 优化方法


    mysql order by rand() 优化方法 适用于领取奖品等项目
    <pre>
    mysql> select * from user order by rand() limit 1;
    +-------+------------+----------------------------------+----------+--------------+-----------+
    | id | phone | password | salt | country_code | ip |
    +-------+------------+----------------------------------+----------+--------------+-----------+
    | 15160 | 6549721306 | e4f302120c006880a247b652ad0e42f2 | 40343586 | 86 | 127.0.0.1 |
    +-------+------------+----------------------------------+----------+--------------+-----------+
    1 row in set (0.25 sec)
    mysql> explain select * from user order by rand() limit 1;
    +----+-------------+-------+------+---------------+------+---------+------+--------+---------------------------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+-------+------+---------------+------+---------+------+--------+---------------------------------+
    | 1 | SIMPLE | user | ALL | NULL | NULL | NULL | NULL | 200303 | Using temporary; Using filesort |
    +----+-------------+-------+------+---------------+------+---------+------+--------+---------------------------------+
    1 row in set (0.00 sec)
    </pre>
    根据分析结果,运行需要0.25秒,order by rand() 需要使用临时表(Using temporary),需要使用文件排序(Using filesort),效率低下。


    改进方法
    <pre>
    <pre>
    <?php
    // 获取总记录数
    $sqlstr = 'select count(*) as recount from user';
    $query = mysql_query($sqlstr) or die(mysql_error());
    $stat = mysql_fetch_assoc($query);
    $total = $stat['recount'];

    // 随机偏移
    $offset = mt_rand(0, $total-1);

    // 偏移后查询
    $sqlstr = 'select * from user limit '.$offset.',1';
    $query = mysql_query($sqlstr) or die(mysql_error());
    $result = mysql_fetch_assoc($query);

    print_r($result);
    ?>
    </pre>
    <pre>
    mysql> select * from user limit 23541,1;
    +-------+------------+----------------------------------+----------+--------------+-----------+
    | id | phone | password | salt | country_code | ip |
    +-------+------------+----------------------------------+----------+--------------+-----------+
    | 23542 | 3740507464 | c8bc1890de179538d8a49cc211859a46 | 93863419 | 86 | 127.0.0.1 |
    +-------+------------+----------------------------------+----------+--------------+-----------+
    1 row in set (0.01 sec)

    mysql> explain select * from user limit 23541,1;
    +----+-------------+-------+------+---------------+------+---------+------+--------+-------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+-------+------+---------------+------+---------+------+--------+-------+
    | 1 | SIMPLE | user | ALL | NULL | NULL | NULL | NULL | 200303 | NULL |
    +----+-------------+-------+------+---------------+------+---------+------+--------+-------+
    1 row in set (0.00 sec)
    </pre>
    </pre>

  • 相关阅读:
    二维数组循环获取替换逗号(PH商品详情页中的,,,去除)
    怎样去除IE9以下浏览器的input自带的X?
    js基础之---slice()
    js基础之---parseInt()
    js去掉字符串中的空格
    div超出内容后自动显示滚动条
    Pytorch 基础
    Pytorch 线性回归问题 总结
    python 图像分类问题 (cifar10)
    Python绘图总结(seaborn库的使用)(下)
  • 原文地址:https://www.cnblogs.com/newmiracle/p/11865478.html
Copyright © 2020-2023  润新知