這篇文章主要介紹了MySQL下的RAND()優化案例,包括對JOIN查詢和子查詢的優化,需要的朋友可以參考下

眾所周知，在MySQL中，如果直接 ORDER BY RAND() 的話，效率非常差，因為會多次執行。事實上，如果等值查詢也是用 RAND() 的話也如此，我們先來看看下面這幾個SQL的不同執行計劃和執行耗時。

首先，看下建表DDL，這是一個沒有顯式自增主鍵的InnoDB表：

1. [yejr@imysql]> show create table t_innodb_random/G 
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 字段也是亂序的。

1. [yejr@imysql]> select count(*) from t_innodb_random/G 
2. *************************** 1. row *************************** 
3. count(*): 393216 

1、常量等值檢索：

1. [yejr@imysql]> explain select id from t_innodb_random where id = 13412/G 
2. *************************** 1. row *************************** 
3. id: 1 
4. select_type: SIMPLE 
5. table: t_innodb_random 
6. type: ref 
7. possible_keys: idx_id 
8. key: idx_id 
9. key_len: 4 
10. ref: const 
11. rows: 1 
12. Extra: Using index 

1. [yejr@imysql]> select id from t_innodb_random where id = 13412; 
2. 1 row in set (0.00 sec) 

可以看到執行計劃很不錯，是常量等值查詢，速度非?？?。

2、使用RAND()函數乘以常量，求得隨機數后檢索：

1. [yejr@imysql]> explain select id from t_innodb_random where id = round(rand()*13241324)/G 
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 where; Using index 

1. [yejr@imysql]> select id from t_innodb_random where id = round(rand()*13241324)/G 
2. Empty set (0.26 sec) 

可以看到執行計劃很糟糕，雖然是只掃描索引，但是做了全索引掃描，效率非常差。因為WHERE條件中包含了RAND()，使得MySQL把它當做變量來處理，無法用常量等值的方式查詢，效率很低。

我們把常量改成取t_innodb_random表的最大id值，再乘以RAND()求得隨機數后檢索看看什么情況：

1. [yejr@imysql]> explain select id from t_innodb_random where id = round(rand()*(select max(id) from t_innodb_random))/G 
2. *************************** 1. row *************************** 
3. id: 1 
4. select_type: PRIMARY 
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 where; Using index 
13. *************************** 2. row *************************** 
14. id: 2 
15. select_type: SUBQUERY 
16. table: NULL 
17. type: NULL 
18. possible_keys: NULL 
19. key: NULL 
20. key_len: NULL 
21. ref: NULL 
22. rows: NULL 
23. Extra: Select tables optimized away 

1. [yejr@imysql]> select id from t_innodb_random where id = round(rand()*(select max(id) from t_innodb_random))/G 
2. Empty set (0.27 sec) 

可以看到，執行計劃依然是全索引掃描，執行耗時也基本相當。

3、改造成普通子查詢模式 ，這里有兩次子查詢

1. [yejr@imysql]> explain select id from t_innodb_random where id = (select round(rand()*(select max(id) from t_innodb_random)) as nid)/G 
2. *************************** 1. row *************************** 
3. id: 1 
4. select_type: PRIMARY 
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 where; Using index 
13. *************************** 2. row *************************** 
14. id: 3 
15. select_type: SUBQUERY 
16. table: NULL 
17. type: NULL 
18. possible_keys: NULL 
19. key: NULL 
20. key_len: NULL 
21. ref: NULL 
22. rows: NULL 
23. Extra: Select tables optimized away 

1. [yejr@imysql]> select id from t_innodb_random where id = (select round(rand()*(select max(id) from t_innodb_random)) as nid)/G 
2. Empty set (0.27 sec) 

可以看到，執行計劃也不好，執行耗時較慢。

4、改造成JOIN關聯查詢，不過最大值還是用常量表示

1. [yejr@imysql]> explain select id from t_innodb_random t1 join (select round(rand()*13241324) as id2) as t2 where t1.id = t2.id2/G 
2. *************************** 1. row *************************** 
3. id: 1 
4. select_type: PRIMARY 
5. table: <derived2> 
6. type: system 
7. possible_keys: NULL 
8. key: NULL 
9. key_len: NULL 
10. ref: NULL 
11. rows: 1 
12. Extra: 
13. *************************** 2. row *************************** 
14. id: 1 
15. select_type: PRIMARY 
16. table: t1 
17. type: ref 
18. possible_keys: idx_id 
19. key: idx_id 
20. key_len: 4 
21. ref: const 
22. rows: 1 
23. Extra: Using where; Using index 
24. *************************** 3. row *************************** 
25. id: 2 
26. select_type: DERIVED 
27. table: NULL 
28. type: NULL 
29. possible_keys: NULL 
30. key: NULL 
31. key_len: NULL 
32. ref: NULL 
33. rows: NULL 
34. Extra: No tables used 

1. [yejr@imysql]> select id from t_innodb_random t1 join (select round(rand()*13241324) as id2) as t2 where t1.id = t2.id2/G 
2. Empty set (0.00 sec) 

這時候執行計劃就非常完美了，和最開始的常量等值查詢是一樣的了，執行耗時也非常之快。

這種方法雖然很好，但是有可能查詢不到記錄，改造范圍查找，但結果LIMIT 1就可以了：

1. [yejr@imysql]> explain select id from t_innodb_random where id > (select round(rand()*(select max(id) from t_innodb_random)) as nid) limit 1/G 
2. *************************** 1. row *************************** 
3. id: 1 
4. select_type: PRIMARY 
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 where; Using index 
13. *************************** 2. row *************************** 
14. id: 3 
15. select_type: SUBQUERY 
16. table: NULL 
17. type: NULL 
18. possible_keys: NULL 
19. key: NULL 
20. key_len: NULL 
21. ref: NULL 
22. rows: NULL 
23. Extra: Select tables optimized away 

1. [yejr@imysql]> select id from t_innodb_random where id > (select round(rand()*(select max(id) from t_innodb_random)) as nid) limit 1/G 
2. *************************** 1. row *************************** 
3. id: 1301 
4. 1 row in set (0.00 sec) 

可以看到，雖然執行計劃也是全索引掃描，但是因為有了LIMIT 1，只需要找到一條記錄，即可終止掃描，所以效率還是很快的。

小結：

從數據庫中隨機取一條記錄時，可以把RAND()生成隨機數放在JOIN子查詢中以提高效率。

5、再來看看用ORDRR BY RAND()方式一次取得多個隨機值的方式：

1. [yejr@imysql]> explain select id from t_innodb_random order by rand() limit 1000/G 
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 

1. [yejr@imysql]> select id from t_innodb_random order by rand() limit 1000; 
2. 1000 rows in set (0.41 sec) 

全索引掃描，生成排序臨時表，太差太慢了。

6、把隨機數放在子查詢里看看：

1. [yejr@imysql]> explain select id from t_innodb_random where id > (select rand() * (select max(id) from t_innodb_random) as nid) limit 1000/G 
2. *************************** 1. row *************************** 
3. id: 1 
4. select_type: PRIMARY 
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 where; Using index 
13. *************************** 2. row *************************** 
14. id: 3 
15. select_type: SUBQUERY 
16. table: NULL 
17. type: NULL 
18. possible_keys: NULL 
19. key: NULL 
20. key_len: NULL 
21. ref: NULL 
22. rows: NULL 
23. Extra: Select tables optimized away 

1. [yejr@imysql]> select id from t_innodb_random where id > (select rand() * (select max(id) from t_innodb_random) as nid) limit 1000/G 
2. 1000 rows in set (0.04 sec) 

嗯，提速了不少，這個看起來還不賴：)

7、仿照上面的方法，改成JOIN和隨機數子查詢關聯

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

1. [yejr@imysql]> select id from t_innodb_random t1 join (select rand() * (select max(id) from t_innodb_random) as nid) t2 on t1.id > t2.nid limit 1000/G 
2. 1000 rows in set (0.00 sec) 

可以看到，全索引檢索，發現符合記錄的條件后，直接取得1000行，這個方法是最快的。

綜上，想從MySQL數據庫中隨機取一條或者N條記錄時，最好把RAND()生成隨機數放在JOIN子查詢中以提高效率。

上面說了那么多的廢話，最后簡單說下，就是把下面這個SQL：

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

改造成下面這個：

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

如果想要達到完全隨機，還可以改成下面這種寫法：

1. SELECT id FROM table t1 JOIN (SELECT round(RAND() * (SELECT MAX(id) FROM table)) AS nid FROM table LIMIT n) t2 ON t1.id = t2.nid; 

就可以享受在SQL中直接取得隨機數了，不用再在程序中構造一串隨機數去檢索了。