欧美97色伦欧美一区二区日韩,国产福利片在线观看,freexxx性欧美vide0高清,西西亚洲,日本欧美国产精品第一页久久,成人18免费软件

當(dāng)前位置： 維易PHP培訓(xùn)學(xué)院 > MYSQL教程 > 內(nèi)容正文

Mysql入門如何提高M(jìn)ySQL RAND隨機(jī)排序效率

作者：VEPHP   時(shí)間 2017-09-27

《Mysql入門如何提高M(jìn)ySQL RAND隨機(jī)排序效率》要點(diǎn)：
本文介紹了Mysql入門如何提高M(jìn)ySQL RAND隨機(jī)排序效率，希望對(duì)您有用。如果有疑問，可以聯(lián)系我們。

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

眾所周知,在MySQL中,如果直接 ORDER BY RAND() 的話,效率非常差,因?yàn)闀?huì)多次執(zhí)行.事實(shí)上,如果等值查詢也是用 RAND() 的話也如此,我們先來看看下面這幾個(gè)SQL的不同執(zhí)行計(jì)劃和執(zhí)行耗時(shí).

首先,看下建表DDL,這是一個(gè)沒有顯式自增主鍵的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?

往這個(gè)表里灌入一些測(cè)試數(shù)據(jù),至少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)?

可以看到執(zhí)行計(jì)劃很不錯(cuò),是常量等值查詢,速度非常快.

2、使用RAND()函數(shù)乘以常量,求得隨機(jī)數(shù)后檢索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)?

可以看到執(zhí)行計(jì)劃很糟糕,雖然只掃描索引,但卻是全索引掃描,效率非常差.因?yàn)閃HERE條件中包含了RAND(),使得MySQL把它當(dāng)做變量來處理,無法用常量等值的方式查詢,效率很低.

我們把常量改成取t_innodb_random表的最大id值,再乘以RAND()求得隨機(jī)數(shù)后檢索看看什么情況：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)?

可以看到,執(zhí)行計(jì)劃依然是全索引掃描,執(zhí)行耗時(shí)也基本相當(dāng).

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)?

可以看到,執(zhí)行計(jì)劃也不好,執(zhí)行耗時(shí)較慢.

4、改造成JOIN關(guān)聯(lián)查詢,不過最大值還是用常量表示
?MYSQL必讀

  1. //維易PHP培訓(xùn)網(wǎng)?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)?

這時(shí)候執(zhí)行計(jì)劃就非常完美了,和最開始的常量等值查詢是一樣的了,執(zhí)行耗時(shí)也非常之快.

這種方法雖然很好,但是有可能查詢不到記錄,改造范圍查找,但結(jié)果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)?

可以看到,雖然執(zhí)行計(jì)劃也是全索引掃描,但是因?yàn)橛辛薒IMIT 1,只需要找到一條記錄,即可終止掃描,所以效率還是很快的.

小結(jié)：從數(shù)據(jù)庫(kù)中隨機(jī)取一條記錄時(shí),可以把RAND()生成隨機(jī)數(shù)放在JOIN子查詢中以提高效率.

5、再來看看用ORDRRBY RAND()方式一次取得多個(gè)隨機(jī)值的方式
?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)?

全索引掃描,生成排序臨時(shí)表,太差太慢了.

6、把隨機(jī)數(shù)放在子查詢里看看
?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)?

嗯,提速了不少,這個(gè)看起來還不賴：)

7、仿照上面的方法,改成JOIN和隨機(jī)數(shù)子查詢關(guān)聯(lián)
?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)?


可以看到,全索引檢索,發(fā)現(xiàn)符合記錄的條件后,直接取得1000行,這個(gè)方法是最快的.

綜上,想從MySQL數(shù)據(jù)庫(kù)中隨機(jī)取一條或者N條記錄時(shí),最好把RAND()生成隨機(jī)數(shù)放在JOIN子查詢中以提高效率.

簡(jiǎn)言之,就是把下面這個(gè)SQL：

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

改造成下面這個(gè)：

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

就可以享受在SQL中直接取得隨機(jī)數(shù)了,不用再在程序中構(gòu)造一串隨機(jī)數(shù)去檢索了.
?MYSQL必讀

轉(zhuǎn)載請(qǐng)注明本頁(yè)網(wǎng)址：
http://www.snjht.com/jiaocheng/5722.html


    標(biāo)簽：