Mysql入门如何提高MySQL RAND随机排序效率

《Mysql入门如何提高MySQL RAND随机排序效率》要点：
本文介绍了Mysql入门如何提高MySQL RAND随机排序效率，希望对您有用。如果有疑问，可以联系我们。

1. [yejr@imysql]>?show?create?table?t_innodb_randomG?
2. ***************************?1.?row***************************?
3. Table:t_innodb_random?
4. Create?Table:?CREATE?TABLE?`t_innodb_random`?(?
5. `id`?int(10)unsigned?NOT?NULL,?
6. `user`?varchar(64)NOT?NULL?DEFAULT?'',?
7. KEY?`idx_id`?(`id`)?
8. )?ENGINE=InnoDB?DEFAULT?CHARSET=latin1?

往这个表里灌入一些测试数据,至少10万以上,id 字段也是乱序的.
?MYSQL必读

1. [yejr@imysql]>?select?count(*)?from?t_innodb_randomG?
2. ***************************?1.?row***************************?
3. count(*):?393216?

1、常量等值检索
?MYSQL必读

1. [yejr@imysql]>?explain?select?id?fromt_innodb_random?where??
2. ?id?=?13412G?
3. ***************************?1.?row***************************?
4. id:?1?
5. select_type:?SIMPLE?
6. table:t_innodb_random?
7. type:?ref?
8. possible_keys:?idx_id?
9. key:?idx_id?
10. key_len:?4?
11. ref:?const?
12. rows:?1?
13. Extra:?Using?index?
14. ?
15. [yejr@imysql]>?select?id?from?t_innodb_random??
16. ?where?id?=13412;?
17. 1?row?in?set?(0.00?sec)?

可以看到执行计划很不错,是常量等值查询,速度非常快.

2、使用RAND()函数乘以常量,求得随机数后检索MYSQL必读

1. [yejr@imysql]>?explain?select?id?from?t_innodb_random?where??
2. ?id?=?round(rand()*13241324)G?
3. ***************************?1.?row***************************?
4. id:?1?
5. select_type:?SIMPLE?
6. table:t_innodb_random?
7. type:?index?
8. possible_keys:?NULL?
9. key:?idx_id?
10. key_len:?4?
11. ref:?NULL?
12. rows:?393345?
13. Extra:?Using?where;?Using?index?
14. ?
15. [yejr@imysql]>?select?id?from?t_innodb_random?where??
16. ?id?=?round(rand()*13241324)G?
17. Empty?set?(0.26?sec)?

可以看到执行计划很糟糕,虽然只扫描索引,但却是全索引扫描,效率非常差.因为WHERE条件中包含了RAND(),使得MySQL把它当做变量来处理,无法用常量等值的方式查询,效率很低.

我们把常量改成取t_innodb_random表的最大id值,再乘以RAND()求得随机数后检索看看什么情况：MYSQL必读

1. [yejr@imysql]>?explain?select?id?from?t_innodb_random?where??
2. ?id?=?round(rand()*(select?max(id)?from?t_innodb_random))G?
3. ***************************?1.?row***************************?
4. id:?1?
5. select_type:PRIMARY?
6. table:t_innodb_random?
7. type:?index?
8. possible_keys:?NULL?
9. key:?idx_id?
10. key_len:?4?
11. ref:?NULL?
12. rows:?393345?
13. Extra:?Using?where;?Using?index?
14. ***************************?2.?row***************************?
15. id:?2?
16. select_type:SUBQUERY?
17. table:?NULL?
18. type:?NULL?
19. possible_keys:?NULL?
20. key:?NULL?
21. key_len:?NULL?
22. ref:?NULL?
23. rows:?NULL?
24. Extra:?Selecttables?optimized?away?
25. ?
26. [yejr@imysql]>?select?id?from?t_innodb_random?where??
27. ?id?=?round(rand()*(select?max(id)?from?t_innodb_random))G?
28. Empty?set?(0.27?sec)?

可以看到,执行计划依然是全索引扫描,执行耗时也基本相当.

3、改造成普通子查询模式,这里有两次子查询
?MYSQL必读

1. [yejr@imysql]>?explain?select?id?fromt_innodb_random?where??
2. ?id?=?(select?round(rand()*(select?max(id)?fromt_innodb_random))?as?nid)G?
3. ***************************?1.?row***************************?
4. id:?1?
5. select_type:PRIMARY?
6. table:t_innodb_random?
7. type:?index?
8. possible_keys:?NULL?
9. key:?idx_id?
10. key_len:?4?
11. ref:?NULL?
12. rows:?393345?
13. Extra:?Using?where;?Using?index?
14. ***************************?2.?row***************************?
15. id:?3?
16. select_type:SUBQUERY?
17. table:?NULL?
18. type:?NULL?
19. possible_keys:?NULL?
20. key:?NULL?
21. key_len:?NULL?
22. ref:?NULL?
23. rows:?NULL?
24. Extra:?Selecttables?optimized?away?
25. ?
26. [yejr@imysql]>?select?id?from?t_innodb_random?where??
27. ?id?=?(select?round(rand()*(select?max(id)?from?t_innodb_random))?as?nid)G?
28. Empty?set?(0.27?sec)?

可以看到,执行计划也不好,执行耗时较慢.

4、改造成JOIN关联查询,不过最大值还是用常量表示
?MYSQL必读

1. //编程之家PHP培训网?www.bcty365.com?
2. ?
3. [yejr@imysql]>?explain?select?id?fromt_innodb_random?t1?join??
4. ?(select?round(rand()*13241324)?as?id2)?as?t2?wheret1.id?=?t2.id2G?
5. ***************************?1.?row***************************?
6. id:?1?
7. select_type:PRIMARY?
8. table:<derived2>?
9. type:?system?
10. possible_keys:?NULL?
11. key:?NULL?
12. key_len:?NULL?
13. ref:?NULL?
14. rows:?1?
15. Extra:?
16. ***************************?2.?row***************************?
17. id:?1?
18. select_type:PRIMARY?
19. table:?t1?
20. type:?ref?
21. possible_keys:?idx_id?
22. key:?idx_id?
23. key_len:?4?
24. ref:?const?
25. rows:?1?
26. Extra:?Using?where;?Using?index?
27. ***************************?3.?row***************************?
28. id:?2?
29. select_type:DERIVED?
30. table:?NULL?
31. type:?NULL?
32. possible_keys:?NULL?
33. key:?NULL?
34. key_len:?NULL?
35. ref:?NULL?
36. rows:?NULL?
37. Extra:?Notables?used?
38. ?
39. [yejr@imysql]>?select?id?from?t_innodb_random?t1?join??
40. ?(select?round(rand()*13241324)?as?id2)?as?t2?where?t1.id?=t2.id2G?
41. Empty?set?(0.00?sec)?

这时候执行计划就非常完美了,和最开始的常量等值查询是一样的了,执行耗时也非常之快.

这种方法虽然很好,但是有可能查询不到记录,改造范围查找,但结果LIMIT 1就可以了：
?MYSQL必读

1. [yejr@imysql]>?explain?select?id?fromt_innodb_random?where??
2. ?id?>?(select?round(rand()*(select?max(id)?fromt_innodb_random))?as?nid)??
3. ?limit?1G?
4. ***************************?1.?row***************************?
5. id:?1?
6. select_type:PRIMARY?
7. table:t_innodb_random?
8. type:?index?
9. possible_keys:?NULL?
10. key:?idx_id?
11. key_len:?4?
12. ref:?NULL?
13. rows:?393345?
14. Extra:?Using?where;?Using?index?
15. ***************************?2.?row***************************?
16. id:?3?
17. select_type:SUBQUERY?
18. table:?NULL?
19. type:?NULL?
20. possible_keys:?NULL?
21. key:?NULL?
22. key_len:?NULL?
23. ref:?NULL?
24. rows:?NULL?
25. Extra:?Selecttables?optimized?away?
26. ?
27. [yejr@imysql]>?select?id?from?t_innodb_random?where??
28. ?id?>?(select?round(rand()*(select?max(id)?from?t_innodb_random))?asnid)??
29. ?limit?1G?
30. ***************************?1.?row***************************?
31. id:?1301?
32. 1?row?in?set?(0.00?sec)?

可以看到,虽然执行计划也是全索引扫描,但是因为有了LIMIT 1,只需要找到一条记录,即可终止扫描,所以效率还是很快的.

小结：从数据库中随机取一条记录时,可以把RAND()生成随机数放在JOIN子查询中以提高效率.

5、再来看看用ORDRRBY RAND()方式一次取得多个随机值的方式
?MYSQL必读

1. [yejr@imysql]>?explain?select?id?from?t_innodb_random?order?by?rand()?limit?1000G?
2. ***************************?1.?row***************************?
3. id:?1?
4. select_type:?SIMPLE?
5. table:t_innodb_random?
6. type:?index?
7. possible_keys:?NULL?
8. key:?idx_id?
9. key_len:?4?
10. ref:?NULL?
11. rows:?393345?
12. Extra:?Using?index;?Using?temporary;?Using?filesort?
13. ?
14. [yejr@imysql]>?select?id?from?t_innodb_randomorder?by?rand()?limit?1000;?
15. 1000?rows?in?set?(0.41?sec)?

全索引扫描,生成排序临时表,太差太慢了.

6、把随机数放在子查询里看看
?MYSQL必读

1. [yejr@imysql]>?explain?select?id?fromt_innodb_random?where??
2. ?id?>?(select?rand()?*?(select?max(id)?fromt_innodb_random)?as?nid)??
3. ?limit?1000G?
4. ***************************?1.?row***************************?
5. id:?1?
6. select_type:PRIMARY?
7. table:t_innodb_random?
8. type:?index?
9. possible_keys:?NULL?
10. key:?idx_id?
11. key_len:?4?
12. ref:?NULL?
13. rows:?393345?
14. Extra:?Using?where;?Using?index?
15. ***************************?2.?row?***************************?
16. id:?3?
17. select_type:SUBQUERY?
18. table:?NULL?
19. type:?NULL?
20. possible_keys:?NULL?
21. key:?NULL?
22. key_len:?NULL?
23. ref:?NULL?
24. rows:?NULL?
25. Extra:?Selecttables?optimized?away?
26. ?
27. [yejr@imysql]>?select?id?from?t_innodb_random?where??
28. ?id?>?(select?rand()?*?(select?max(id)?from?t_innodb_random)?as?nid)?
29. ?limit?1000G?
30. 1000?rows?in?set?(0.04?sec)?

嗯,提速了不少,这个看起来还不赖：)

7、仿照上面的方法,改成JOIN和随机数子查询关联
?MYSQL必读

1. [yejr@imysql]>?explain?select?id?fromt_innodb_random?t1?join??
2. ?(select?rand()?*?(select?max(id)?from?t_innodb_random)as?nid)?t2?on??
3. ?t1.id?>?t2.nid?limit?1000G?
4. ***************************?1.?row***************************?
5. id:?1?
6. select_type:PRIMARY?
7. table:<derived2>?
8. type:?system?
9. possible_keys:?NULL?
10. key:?NULL?
11. key_len:?NULL?
12. ref:?NULL?
13. rows:?1?
14. Extra:?
15. ***************************?2.?row***************************?
16. id:?1?
17. select_type:PRIMARY?
18. table:?t1?
19. type:?range?
20. possible_keys:?idx_id?
21. key:?idx_id?
22. key_len:?4?
23. ref:?NULL?
24. rows:?196672?
25. Extra:?Using?where;?Using?index?
26. ***************************?3.?row***************************?
27. id:?2?
28. select_type:DERIVED?
29. table:?NULL?
30. type:?NULL?
31. possible_keys:?NULL?
32. key:?NULL?
33. key_len:?NULL?
34. ref:?NULL?
35. rows:?NULL?
36. Extra:?Notables?used?
37. ***************************?4.?row***************************?
38. id:?3?
39. select_type:SUBQUERY?
40. table:?NULL?
41. type:?NULL?
42. possible_keys:?NULL?
43. key:?NULL?
44. key_len:?NULL?
45. ref:?NULL?
46. rows:?NULL?
47. Extra:?Selecttables?optimized?away?
48. ?
49. [yejr@imysql]>?select?id?from?t_innodb_randomt1?join?(select?rand()?*?(select?max(id)?from?t_innodb_random)?as?nid)?t2?ont1.id?>?t2.nid?limit?1000G?
50. 1000?rows?in?set?(0.00?sec)?

可以看到,全索引检索,发现符合记录的条件后,直接取得1000行,这个方法是最快的.

综上,想从MySQL数据库中随机取一条或者N条记录时,最好把RAND()生成随机数放在JOIN子查询中以提高效率.

简言之,就是把下面这个SQL：

SELECT id FROM table ORDER BY RAND() LIMIT n;

改造成下面这个：

SELECT id FROM table t1 JOIN
(SELECT RAND() * (SELECTMAX(id) FROM table) AS nid) t2
ON t1.id > t2.nid LIMIT n;

就可以享受在SQL中直接取得随机数了,不用再在程序中构造一串随机数去检索了.
?MYSQL必读

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!