MySQL如何高效地随机读取数据

MySQL如何高效地随机读取数据

   线上事故

   生产项目中出现了一个线上事故，需求其实很简单：从mysql数据库中中随机读取出100条认证企业的数据到前端来展示，并且要求每一次读取的数据是不同的。

   结果上线后，CPU负载过高，发生了报警。

    1. 数据表

   下面两表中， member_verify 中有约400W数据，company中有约8W条

   

    最开始项目中使用order by rand()这样来实现，具体语句如下：

SELECT c.user_id, c.company_name
FROM `jc_company` c
        LEFT JOIN `jc_member_verify` v ON c.user_id = v.user_id
WHERE v.company_status = 2
        AND c.province_id = 0
GROUP BY c.company_name
ORDER BY rand()
LIMIT 1000

     我们来用explain看下上面语句使用索引的情况：

    

     由上面的结果可以分析，order by rand() 导致了mysql使用了外部排序，sql效率非常低

     优化方案的方案有很多，比如放到redis缓存，每次从redis缓存读取，下面是其中一种：

      1）不做连接查询，先到member_verify通过company_status筛选出满足条件的数据，再到company中去查询最后要查询数据

      2）每次展示N条，取100页的数据，通过随机生成1-100的页码，取到第N页的数据

      示例代码：

  public function cleaningJob($pageSize): array
    {
        $randPage = 1;

        $countQuery= $this->select(['id','user_id']) ->where('company_status', 2);

        $count= $this->select(['id','user_id']) ->where('company_status', 2)->count();

        if ($count > $pageSize) {
            $endNum = (int)ceil($count / $pageSize);
            $randPage = random_int(1, ($endNum > 100 ? 100 : $endNum));
        }

        $offset=(randPage-1)*pageSize;

        $query = $countQuery->limit($offset, $pageSize);
           //...

    }

     然后通过company的唯一键user_id 去company查询出company_name。

     再通过压测，测试下性能，根据情况进行调整。