## Write it at the front > MySQL Databases are used more frequently in the Internet industry , Some partners may think MySQL The database is small , Can't store a lot of data . In fact , These guys really don't understand MySQL.MySQL It doesn't mean to use MySQL There is little data stored , It's small , It's light . Use MySQL It can store hundreds of billions of data , I will share this with my partners in the following article MySQL Storing data above 100 billion levels . Or my friends can book my new book in advance 《MySQL A complete collection of Technology ： Develop 、 Optimization and operation and maintenance 》. Okay , So much said , Today I'd like to share with you an article about MySQL The classic interview questions of ： How to get the most out of MySQL A random record in a query ？ ## Interview questions How to get from MySQL Query a random record in a data table , At the same time, to ensure the highest efficiency . Judging from this topic , There are actually two requirements , The first requirement is ： From MySQL Query a random record in the table . The second requirement is to ensure the highest efficiency . Next , Let's try to use various ways to get from MySQL Query data in the data table . ## Method 1 This is the most primitive and intuitive grammar , as follows ： ```sql SELECT * FROM foo ORDER BY RAND() LIMIT 1 ``` When the amount of data in the table is small , This method is feasible . But when the amount of data reaches a certain level , such as 100 Million data or more , There is a big efficiency problem . If you pass EXPLAIN To analyze this Sentence , Will find that although MySQL Sort by creating a temporary table , But because ORDER BY and LIMIT Its own characteristics , Before the sort is done , We still can't get through LIMIT To get the records you need . or , How many records do you have , You have to sort the data first . ## Method 2 It seems that for large amounts of random data extraction , The crux of efficiency is ORDER BY On , So how to avoid ？ Method two provides a solution . First , Gets the number of all records in the table ： ```sql SELECT count(*) AS num_rows FROM foo ``` And then , Record the total number of this record through the corresponding background program （ Assumed to be num_rows）. Then execute ： ```sql SELECT * FROM foo LIMIT [0 To num_rows Between a random number ],1 ``` The above random number can be obtained through the background program . The premise of this method is tabular ID It's continuous or self increasing . This method has successfully avoided ORDER BY The emergence of . ## Method 3 Is it possible not to ORDER BY, Use one SQL Statement implementation method 2 ？ Sure , That's using 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; ``` This method achieves our goal , At the same time , In the case of a large amount of data , Also avoided ORDER BY The resulting sorting process of all records , Because it passed JOIN Inside SELECT The statement is actually executed only once , instead of N Time （N It is equivalent to num_rows）. and , We can add “ Bigger than ” Symbols , It can be avoided because ID The phenomenon that the record is empty caused by discontinuity . stay MySQL Query in 5 Data that doesn't repeat , Use the following ： ```sql SELECT * FROM `table` ORDER BY RAND() LIMIT 5 ``` That's all right. . But after a real test, we found that the efficiency was very low . One 15 Library of more than 10000 articles , Inquire about 5 Information , You want it 8 More than seconds Search for Google, The Internet is basically full of inquiries max(id) * rand() To get random information . ```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; ``` But it creates a continuous 5 Records . The solution can only be to query one at a time , Inquire about 5 Time . Even so, it's worth it , Because 15 Ten thousand watches , The query only needs 0.01 Less than a second . The sentence above uses JOIN,mysql People use it on the forum ```sql SELECT * FROM `table` WHERE id >= (SELECT FLOOR( MAX(id) * RAND()) FROM `table` ) ORDER BY id LIMIT 1; ``` I tested it , need 0.5 second , The speed was good, too , But there is still a big gap with the above sentence . I always feel that something is not normal . So I rewrote the sentence . ```sql SELECT * FROM `table` WHERE id >= (SELECT floor(RAND() * (SELECT MAX(id) FROM `table`))) ORDER BY id LIMIT 1; ``` Now , Efficiency has increased again , The query time is only 0.01 second Finally , Perfect the sentence again , add MIN(id) The judgment of . When I started testing , Because it didn't add MIN(id) The judgment of , The result is that the first few rows in the table are always queried half the time . The complete query statement is ： ```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; ``` Finally, query the two statements separately 10 Time , The former takes time 0.147433 second The latter takes time 0.015130 second It seems that JOIN The grammar is directly compared to WHERE It's much more efficient to use functions in . ## Heavy benefits Search on wechat 【 Glacier Technology 】 Wechat public account , Focus on this deep programmer , Read hard core technology dry goods every day , Reply in the public account 【PDF】 I have prepared the interview materials of the first-line large factories and my original superhard core PDF Technical documents , And I carefully prepared for you a set of Resume Template （ Constantly updating ）, I hope you can find the job you want , Learning is a kind of melancholy from time to time , Now and then the road of laughter , come on. . If you succeed in getting into the company you want , Don't slack off and relax , Workplace growth is the same as learning new technology , No advance, no retreat . If we're lucky, we'll see you in the world ！ in addition , I open source each PDF, I will continue to update and maintain , Thank you for your long-term support of the glacier