MySQL ORDER BY RAND() 函数的优化
简介
使用MySQL 中的 ORDER BY RAND() 函数可能会导致查询性能降低,尤其是在优化不佳的情况下表。本文深入研究了优化此类查询的有效技术,并提供了解决此问题的实用解决方案。
优化技术
一种有效的解决方案涉及使用子查询与STRAIGHT_JOIN,如下所示:
SELECT * FROM ( SELECT @cnt := COUNT(*) + 1, @lim := 10 FROM t_random ) vars STRAIGHT_JOIN ( SELECT r.*, @lim := @lim - 1 FROM t_random r WHERE (@cnt := @cnt - 1) AND RAND(20090301) < @lim / @cnt ) i
该方法已被证明对于 MyISAM 表特别有效,因为COUNT(*) 操作的瞬时性质。对于 InnoDB 表,它仍然比 ORDER BY RAND() 快得多。
如果只需要单个随机记录,可以使用不同的方法:
SELECT aco.* FROM ( SELECT minid + FLOOR((maxid - minid) * RAND()) AS randid FROM ( SELECT MAX(ac_id) AS maxid, MIN(ac_id) AS minid FROM accomodation ) q ) q2 JOIN accomodation aco ON aco.ac_id = COALESCE ( ( SELECT accomodation.ac_id FROM accomodation WHERE ac_id > randid AND ac_status != 'draft' AND ac_images != 'b:0;' AND NOT EXISTS ( SELECT NULL FROM accomodation_category WHERE acat_id = ac_category AND acat_slug = 'vendeglatohely' ) ORDER BY ac_id LIMIT 1 ), ( SELECT accomodation.ac_id FROM accomodation WHERE ac_status != 'draft' AND ac_images != 'b:0;' AND NOT EXISTS ( SELECT NULL FROM accomodation_category WHERE acat_id = ac_category AND acat_slug = 'vendeglatohely' ) ORDER BY ac_id LIMIT 1 ) )
此方法假设 ac_id 值的分布相对均匀。
以上是如何优化 MySQL 的 ORDER BY RAND() 以获得更快的查询性能?的详细内容。更多信息请关注PHP中文网其他相关文章!