原创

工作笔记:百万数据随机返回N条优化

1.百万数据返回随机N条数据

场景: 在百万条数据中返回随机N条数据
参考地址
原始SQL:

SELECT word
FROM tbl_ads_search_words
WHERE search_type = 23
 AND agent_id = 'B2vD8RCf'
 AND word_type = 2
ORDER BY RAND()
LIMIT 10

优化后SQL:

SELECT
    word
FROM
    `tbl_ads_search_words`
WHERE
 search_type = 23
 AND agent_id = 'B2vD8RCf'
 AND word_type = 2
 and 
    id >= (
        SELECT floor(RAND() * (SELECT MAX(id) FROM `tbl_ads_search_words`))
    ) 
ORDER BY
    id
LIMIT 10;

2. 分析原因

order by RAND()会扫描全表,导致查询速度非常慢

3. 思考

1.SQL前几天运行正常,为何会突然变慢?

  1. 是否有其他解决方案:
    a. 先查询出最大ID,然后用代码生成limit数量的随机数,再select * from table where id in()。 这种方法只适用于ID为整数时。
    b. 以上SQL可能会导致查询数量小于10,所以在子查询时,就应该做好limit限制。

    4.总结

    1.能否设置mysql预警机制,在预警时,及时发现并解决?zabbix? druid?
    预警能否提前发邮件告知对应开发人员?

2.开发时其实有考虑过大量数据的场景,当时SQL也和上面差不多,但由于项目催得紧,所以并未来得及修改,下次当注意,及时规避类似昨天发生的情景。

3.当时写这段SQL的时候,也预想到可能会出现SQL查询慢的问题,再次验证墨菲定律:如果一件事有变坏的可能,那么它一定会往坏的方向发展。

  1. 以后切忌为了快速完成某个项目,而不考虑周全就上线,这样不仅会导致回过头来修改以前做过的工作,甚至可能带来严重的线上问题。

5.举一反三

其他项目是否也有这样的问题?留下你的问题,我们一起探讨吧!

正文到此结束