• 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>

  • 相关阅读:
    Go语言学习资源
    优秀编程学习网站
    我对架构的理解
    【转】TCP协议中的三次握手和四次挥手(图解)
    【转】asp.net c# 网上搜集面试题目大全(附答案)
    spring framework 4 源码阅读
    浮点类型
    把二元查找树转变成排序的双向链表
    用模板写快速排序-链表
    用模板写快速排序-数组
  • 原文地址:https://www.cnblogs.com/newmiracle/p/11865478.html
Copyright © 2020-2023  润新知