乱爱性全过程免费视频/午夜毛片视频/中文字幕av三区/一级视频网址

當前位置： 維易PHP培訓學院 > MYSQL教程 > 內容正文

Mysql入門如何提高MySQL RAND隨機排序效率

作者：VEPHP   時間 2017-09-27

《Mysql入門如何提高MySQL RAND隨機排序效率》要點：
本文介紹了Mysql入門如何提高MySQL RAND隨機排序效率，希望對您有用。如果有疑問，可以聯系我們。

導讀：在MySQL中,如果直接 ORDER BY RAND() 的話,效率非常差,那么如何進行優化呢??眾所周知,在MySQL中,如果直接 ORDER BY RAND() ...
在MySQL中,如果直接 ORDER BY RAND() 的話,效率非常差,那么如何進行優化呢??

眾所周知,在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 字段也是亂序的.
?MYSQL必讀

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

1、常量等值檢索
?MYSQL必讀

  1. [yejr@imysql]>?explain?select?id?fromt_innodb_random?where??
  2. ?id?=?13412\G?
  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.id2\G?
  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.id2\G?
  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?1\G?
  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?1\G?
  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?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?
  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?1000\G?
  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?1000\G?
  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?1000\G?
  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?1000\G?
  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必讀

轉載請注明本頁網址：
http://www.fzlkiss.com/jiaocheng/5722.html


    標簽：