mysql随机查询效率优化
最近由于需要研究了一下MYSQL的随机抽取实现方法。举个例子，要从tablename表中随机提取一条记录，大家一般的写法就是： SELECT * FROM content ORDER BY RAND () LIMIT 1 【3万条记录查询花费 0.3745 秒（下同）；从mysql slow query log看出“ORDER BY RAN
最近由于需要研究了一下MYSQL的随机抽取实现方法。举个例子，要从tablename表中随机提取一条记录，大家一般的写法就是：
1 |
|
【3万条记录查询花费 0.3745 秒（下同）；从mysql slow query log看出“ORDER BY RAND() ”全表扫描了2次！】
后来我查了一下MYSQL的官方手册，里面针对RAND()的提示大概意思就是，在ORDER BY从句里面不能使用RAND()函数，因为这样会导致数据列被多次扫描。但是在MYSQL 3.23版本中，仍然可以通过ORDER BY RAND()来实现随机。
但是真正测试一下才发现这样效率非常低。一个15万余条的库，查询5条数据，居然要8秒以上。查看官方手册，也说rand()放在ORDER BY 子句中会被执行多次，自然效率及很低。
搜索Google，采用JOIN，查询max(id) * rand()来随机获取数据。
1 2 3 4 |
|
【查询花费 0.0008 秒，飘易认为可以推荐使用这个语句！！】
但是这样会产生连续的5条记录。解决办法只能是每次查询一条，查询5次。即便如此也值得，因为15万条的表，查询只需要0.01秒不到。
有一个方法：
1 |
|
上面这种方式保证了一定范围内的随机,查询花费 0.4265 秒，也不推荐。
下面的语句，mysql的论坛上有人使用
1 2 3 4 |
|
【查询花费 1.2254 秒，飘易强烈不推荐！因为实测后，3万行的表，这个语句竟然会扫描500万行！！】
跟上面的语句还是有很大差距。总觉有什么地方不正常。于是我把语句改写了一下。
1 2 3 |
|
【查询花费 0.0012 秒】
这下，效率又提高了，查询时间只有0.01秒
最后，再把语句完善一下，加上MIN(id)的判断。我在最开始测试的时候，就是因为没有加上MIN(id)的判断，结果有一半的时间总是查询到表中的前面几行。
完整查询语句是：
1 2 3 |
|
【查询花费 0.0012 秒】
1 2 3 4 |
|
【查询花费 0.0008 秒】
最后在php中对这两个语句进行分别查询10次，
前者花费时间 0.147433 秒
后者花费时间 0.015130 秒
看来采用JOIN的语法比直接在WHERE中使用函数效率还要高很多。（via）
======================================
【好了，最后飘易来总结下】：
第一种方案，即原始的 Order By Rand() 方法：
1 2 3 4 5 6 |
|
3万条数据查12条随机记录，需要0.125秒，随着数据量的增大，效率越来越低。
第二种方案，改进后的 JOIN 方法：
1 2 3 4 5 6 7 |
|
3万条数据查12条随机记录，需要0.004秒，效率大幅提升，比第一种方案提升了约30倍。缺点：多次select查询，IO开销大。
第三种方案，SQL语句先随机好ID序列，用 IN 查询（飘易推荐这个用法，IO开销小，速度最快）：
1 2 3 4 5 6 7 8 9 10 11 12 13 |
|
3万条数据查12条随机记录，需要0.001秒，效率比第二种方法又提升了4倍左右，比第一种方法提升120倍。注，这里使用了 order by
field($idlist2) 是为了不排序，否则 IN 是自动会排序的。缺点：有可能遇到ID被删除的情况，所以需要多选几个ID。
测试方法：
1 2 |
|
参考：
http://blog.csdn.net/zxl315/article/details/2435368
http://jan.kneschke.de/projects/mysql/order-by-rand/
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
AI Hentai Generator
Generate AI Hentai for free.
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
1376
52
The relationship between mysql user and database
Apr 08, 2025 pm 07:15 PM
In MySQL database, the relationship between the user and the database is defined by permissions and tables. The user has a username and password to access the database. Permissions are granted through the GRANT command, while the table is created by the CREATE TABLE command. To establish a relationship between a user and a database, you need to create a database, create a user, and then grant permissions.
MySQL: The Ease of Data Management for Beginners
Apr 09, 2025 am 12:07 AM
MySQL is suitable for beginners because it is simple to install, powerful and easy to manage data. 1. Simple installation and configuration, suitable for a variety of operating systems. 2. Support basic operations such as creating databases and tables, inserting, querying, updating and deleting data. 3. Provide advanced functions such as JOIN operations and subqueries. 4. Performance can be improved through indexing, query optimization and table partitioning. 5. Support backup, recovery and security measures to ensure data security and consistency.
How to fill in mysql username and password
Apr 08, 2025 pm 07:09 PM
To fill in the MySQL username and password: 1. Determine the username and password; 2. Connect to the database; 3. Use the username and password to execute queries and commands.
Can I retrieve the database password in Navicat?
Apr 08, 2025 pm 09:51 PM
Navicat itself does not store the database password, and can only retrieve the encrypted password. Solution: 1. Check the password manager; 2. Check Navicat's "Remember Password" function; 3. Reset the database password; 4. Contact the database administrator.
Query optimization in MySQL is essential for improving database performance, especially when dealing with large data sets
Apr 08, 2025 pm 07:12 PM
1. Use the correct index to speed up data retrieval by reducing the amount of data scanned select*frommployeeswherelast_name='smith'; if you look up a column of a table multiple times, create an index for that column. If you or your app needs data from multiple columns according to the criteria, create a composite index 2. Avoid select * only those required columns, if you select all unwanted columns, this will only consume more server memory and cause the server to slow down at high load or frequency times For example, your table contains columns such as created_at and updated_at and timestamps, and then avoid selecting * because they do not require inefficient query se
How to create navicat premium
Apr 09, 2025 am 07:09 AM
Create a database using Navicat Premium: Connect to the database server and enter the connection parameters. Right-click on the server and select Create Database. Enter the name of the new database and the specified character set and collation. Connect to the new database and create the table in the Object Browser. Right-click on the table and select Insert Data to insert the data.
How to view mysql
Apr 08, 2025 pm 07:21 PM
View the MySQL database with the following command: Connect to the server: mysql -u Username -p Password Run SHOW DATABASES; Command to get all existing databases Select database: USE database name; View table: SHOW TABLES; View table structure: DESCRIBE table name; View data: SELECT * FROM table name;
How to copy tables in mysql
Apr 08, 2025 pm 07:24 PM
Copying a table in MySQL requires creating new tables, inserting data, setting foreign keys, copying indexes, triggers, stored procedures, and functions. The specific steps include: creating a new table with the same structure. Insert data from the original table into a new table. Set the same foreign key constraint (if the original table has one). Create the same index. Create the same trigger (if the original table has one). Create the same stored procedure or function (if the original table is used).
