【面經】面試官:如何以最高的效率從MySQL中隨機查詢一條記錄?

itread01 2020-11-06 01:17:49
最高 效率 mysql


## 寫在前面 > MySQL資料庫在網際網路行業使用的比較多,有些小夥伴可能會認為MySQL資料庫比較小,儲存不了很多的資料。其實,這些小夥伴是真的不瞭解MySQL。MySQL的小不是說使用MySQL儲存的資料少,而是說其體積小,比較輕量。使用MySQL完全可以儲存千億級別的資料,這個我會在後面的文章中來給小夥伴們分享如何使用MySQL儲存千億級別以上的資料。或者小夥伴們可以提前預定我的新書《MySQL技術大全:開發、優化與運維實戰》。好了,說了這麼多,今天給大家分享一篇有關MySQL的經典面試題:如何以最高的效率從MySQL中隨機查詢一條記錄? ## 面試題目 如何從MySQL一個數據表中查詢一條隨機的記錄,同時要保證效率最高。 從這個題目來看,其實包含了兩個要求,第一個要求就是:從MySQL資料表中查詢一條隨機的記錄。第二個要求就是要保證效率最高。 接下來,我們就來嘗試使用各種方式來從MySQL資料表中查詢資料。 ## 方法一 這是最原始最直觀的語法,如下: ```sql SELECT * FROM foo ORDER BY RAND() LIMIT 1 ``` 當資料表中資料量較小時,此方法可行。但當資料量到達一定程度,比如100萬資料或以上,就有很大的效能問題。如果你通過EXPLAIN來分析這個 語句,會發現雖然MySQL通過建立一張臨時表來排序,但由於ORDER BY和LIMIT本身的特性,在排序未完成之前,我們還是無法通過LIMIT來獲取需要的記錄。亦即,你的記錄有多少條,就必須首先對這些資料進行排序。 ## 方法二 看來對於大資料量的隨機資料抽取,效能的癥結出在ORDER BY上,那麼如何避免?方法二提供了一個方案。 首先,獲取資料表的所有記錄數: ```sql SELECT count(*) AS num_rows FROM foo ``` 然後,通過對應的後臺程式記錄下此記錄總數(假定為num_rows)。 然後執行: ```sql SELECT * FROM foo LIMIT [0到num_rows之間的一個隨機數],1 ``` 上面這個隨機數的獲得可以通過後臺程式來完成。此方法的前提是表的ID是連續的或者自增長的。 這個方法已經成功避免了ORDER BY的產生。 ## 方法三 有沒有可能不用ORDER BY,用一個SQL語句實現方法二?可以,那就是用JOIN。 ```sql SELECT * FROM Bar B JOIN (SELECT CEIL(MAX(ID)*RAND()) AS ID FROM Bar) AS m ON B.ID >= m.ID LIMIT 1; ``` 此方法實現了我們的目的,同時,在資料量大的情況下,也避免了ORDER BY所造成的所有記錄的排序過程,因為通過JOIN裡面的SELECT語句實際上只執行了一次,而不是N次(N等於方法二中的num_rows)。而且, 我們可以在篩選語句上加上“大於”符號,還可以避免因為ID好不連續所產生的記錄為空的現象。 在MySQL中查詢5條不重複的資料,使用以下: ```sql SELECT * FROM `table` ORDER BY RAND() LIMIT 5 ``` 就可以了。但是真正測試一下才發現這樣效率非常低。一個15萬餘條的庫,查詢5條資料,居然要8秒以上 搜尋Google,網上基本上都是查詢max(id) * rand()來隨機獲取資料。 ```sql SELECT * FROM `table` AS t1 JOIN (SELECT ROUND(RAND() * (SELECT MAX(id) FROM `table`)) AS id) AS t2 WHERE t1.id >= t2.id ORDER BY t1.id ASC LIMIT 5; ``` 但是這樣會產生連續的5條記錄。解決辦法只能是每次查詢一條,查詢5次。即便如此也值得,因為15萬條的表,查詢只需要0.01秒不到。 上面的語句採用的是JOIN,mysql的論壇上有人使用 ```sql SELECT * FROM `table` WHERE id >= (SELECT FLOOR( MAX(id) * RAND()) FROM `table` ) ORDER BY id LIMIT 1; ``` 我測試了一下,需要0.5秒,速度也不錯,但是跟上面的語句還是有很大差距。總覺有什麼地方不正常。 於是我把語句改寫了一下。 ```sql SELECT * FROM `table` WHERE id >= (SELECT floor(RAND() * (SELECT MAX(id) FROM `table`))) ORDER BY id LIMIT 1; ``` 這下,效率又提高了,查詢時間只有0.01秒 最後,再把語句完善一下,加上MIN(id)的判斷。我在最開始測試的時候,就是因為沒有加上MIN(id)的判斷,結果有一半的時間總是查詢到表中的前面幾行。 完整查詢語句是: ```sql SELECT * FROM `table` WHERE id >= (SELECT floor( RAND() * ((SELECT MAX(id) FROM `table`)-(SELECT MIN(id) FROM `table`)) + (SELECT MIN(id) FROM `table`))) ORDER BY id LIMIT 1; SELECT * FROM `table` AS t1 JOIN (SELECT ROUND(RAND() * ((SELECT MAX(id) FROM `table`)-(SELECT MIN(id) FROM `table`))+(SELECT MIN(id) FROM `table`)) AS id) AS t2 WHERE t1.id >= t2.id ORDER BY t1.id LIMIT 1; ``` 最後對這兩個語句進行分別查詢10次, 前者花費時間 0.147433 秒 後者花費時間 0.015130 秒 看來採用JOIN的語法比直接在WHERE中使用函式效率還要高很多。 ## 重磅福利 微信搜一搜【冰河技術】微信公眾號,關注這個有深度的程式設計師,每天閱讀超硬核技術乾貨,公眾號內回覆【PDF】有我準備的一線大廠面試資料和我原創的超硬核PDF技術文件,以及我為大家精心準備的多套簡歷模板(不斷更新中),希望大家都能找到心儀的工作,學習是一條時而鬱鬱寡歡,時而開懷大笑的路,加油。如果你通過努力成功進入到了心儀的公司,一定不要懈怠放鬆,職場成長和新技術學習一樣,不進則退。如果有幸我們江湖再見! 另外,我開源的各個PDF,後續我都會持續更新和維護,感謝大家長期以來對冰河的支
版权声明
本文为[itread01]所创,转载请带上原文链接,感谢
https://www.itread01.com/content/1604501888.html

  1. 【计算机网络 12(1),尚学堂马士兵Java视频教程
  2. 【程序猿历程,史上最全的Java面试题集锦在这里
  3. 【程序猿历程(1),Javaweb视频教程百度云
  4. Notes on MySQL 45 lectures (1-7)
  5. [computer network 12 (1), Shang Xuetang Ma soldier java video tutorial
  6. The most complete collection of Java interview questions in history is here
  7. [process of program ape (1), JavaWeb video tutorial, baidu cloud
  8. Notes on MySQL 45 lectures (1-7)
  9. 精进 Spring Boot 03:Spring Boot 的配置文件和配置管理,以及用三种方式读取配置文件
  10. Refined spring boot 03: spring boot configuration files and configuration management, and reading configuration files in three ways
  11. 精进 Spring Boot 03:Spring Boot 的配置文件和配置管理,以及用三种方式读取配置文件
  12. Refined spring boot 03: spring boot configuration files and configuration management, and reading configuration files in three ways
  13. 【递归,Java传智播客笔记
  14. [recursion, Java intelligence podcast notes
  15. [adhere to painting for 386 days] the beginning of spring of 24 solar terms
  16. K8S系列第八篇(Service、EndPoints以及高可用kubeadm部署)
  17. K8s Series Part 8 (service, endpoints and high availability kubeadm deployment)
  18. 【重识 HTML (3),350道Java面试真题分享
  19. 【重识 HTML (2),Java并发编程必会的多线程你竟然还不会
  20. 【重识 HTML (1),二本Java小菜鸟4面字节跳动被秒成渣渣
  21. [re recognize HTML (3) and share 350 real Java interview questions
  22. [re recognize HTML (2). Multithreading is a must for Java Concurrent Programming. How dare you not
  23. [re recognize HTML (1), two Java rookies' 4-sided bytes beat and become slag in seconds
  24. 造轮子系列之RPC 1:如何从零开始开发RPC框架
  25. RPC 1: how to develop RPC framework from scratch
  26. 造轮子系列之RPC 1:如何从零开始开发RPC框架
  27. RPC 1: how to develop RPC framework from scratch
  28. 一次性捋清楚吧,对乱糟糟的,Spring事务扩展机制
  29. 一文彻底弄懂如何选择抽象类还是接口,连续四年百度Java岗必问面试题
  30. Redis常用命令
  31. 一双拖鞋引发的血案,狂神说Java系列笔记
  32. 一、mysql基础安装
  33. 一位程序员的独白:尽管我一生坎坷,Java框架面试基础
  34. Clear it all at once. For the messy, spring transaction extension mechanism
  35. A thorough understanding of how to choose abstract classes or interfaces, baidu Java post must ask interview questions for four consecutive years
  36. Redis common commands
  37. A pair of slippers triggered the murder, crazy God said java series notes
  38. 1、 MySQL basic installation
  39. Monologue of a programmer: despite my ups and downs in my life, Java framework is the foundation of interview
  40. 【大厂面试】三面三问Spring循环依赖,请一定要把这篇看完(建议收藏)
  41. 一线互联网企业中,springboot入门项目
  42. 一篇文带你入门SSM框架Spring开发,帮你快速拿Offer
  43. 【面试资料】Java全集、微服务、大数据、数据结构与算法、机器学习知识最全总结,283页pdf
  44. 【leetcode刷题】24.数组中重复的数字——Java版
  45. 【leetcode刷题】23.对称二叉树——Java版
  46. 【leetcode刷题】22.二叉树的中序遍历——Java版
  47. 【leetcode刷题】21.三数之和——Java版
  48. 【leetcode刷题】20.最长回文子串——Java版
  49. 【leetcode刷题】19.回文链表——Java版
  50. 【leetcode刷题】18.反转链表——Java版
  51. 【leetcode刷题】17.相交链表——Java&python版
  52. 【leetcode刷题】16.环形链表——Java版
  53. 【leetcode刷题】15.汉明距离——Java版
  54. 【leetcode刷题】14.找到所有数组中消失的数字——Java版
  55. 【leetcode刷题】13.比特位计数——Java版
  56. oracle控制用户权限命令
  57. 三年Java开发,继阿里,鲁班二期Java架构师
  58. Oracle必须要启动的服务
  59. 万字长文!深入剖析HashMap,Java基础笔试题大全带答案
  60. 一问Kafka就心慌?我却凭着这份,图灵学院vip课程百度云