order by rand()优化

优化前：

SELECT
    id,
    loan_id,
    NAME,
    company
FROM
    tablename
WHERE
    time BETWEEN 1522512000
AND 1525103999
AND loan_time BETWEEN 1522512000
AND 1525103999
AND STATUS = 1
AND is_again_yd = 0
AND company <> 1
order by rand()
LIMIT 30

优化方案一：

采用JOIN，查询max(id) * rand()来随机获取数据。
SELECT
    t1.id,
    t1.loan_id,
    t1. NAME,
t1.company
FROM
    `tablename` AS t1
JOIN (
    SELECT
        ROUND(
            RAND() * (
                SELECT
                    MAX(id)
                FROM
                    `tablename`
            )
        ) AS id
) AS t2
WHERE
    t1.id >= t2.id
AND time BETWEEN 1522512000
AND 1525103999
AND loan_time BETWEEN 1522512000
AND 1525103999
AND STATUS = 1
AND is_again_yd = 0
AND company <> 1
ORDER BY t1.id ASC
LIMIT 30;

优化方案二：

 1 SELECT
 2   t1.id,
 3   t1.loan_id,
 4   t1.NAME
 5 FROM
 6   tablename t1
 7 WHERE
 8 t1.id >= (
 9         SELECT
10             floor(
11                 RAND() * (
12                     SELECT
13                         MAX(id)
14                     FROM
15                         `tablename`
16                 )
17             )
18     )
19 AND t1.time BETWEEN 1522512000 AND 1525103999
20 AND t1.STATUS IN (1, 2, 4)
21 AND t1.type IN (1, 2)
22 AND t1.belong_company = 0
23 AND t1.is_del = 0
24 AND NOT EXISTS (
25 SELECT
26 loan_id
27 FROM
28 tablename2 t2
29 WHERE
30 t2.loan_id = t1.loan_id
31 AND t2.company = 1
32 )
33 ORDER BY id
34 limit 20

 

说明：这种方案替代mysql rand(),性能太幅度提升，随机性没有rand()强，如果随机性强的请选择替代方案。

转载于:https://www.cnblogs.com/dawuge/p/9046939.html