As we all know, in MySQL, if direct order by RAND (), the efficiency is very poor, because it will be executed multiple times. In fact, if the equivalent query is also in RAND (), let's take a look at the different execution plans and execution time of the following several SQL.
First, look at the table DDL, which is a InnoDB table with no explicit self-adding primary key:
Copy Code code as follows:
[yejr@imysql]> Show CREATE TABLE T_innodb_random\g
1. Row ***************************
Table:t_innodb_random
Create table:create Table ' T_innodb_random ' (
' ID ' int (a) unsigned not NULL,
' User ' varchar not NULL DEFAULT ',
KEY ' idx_id ' (' ID ')
) Engine=innodb DEFAULT charset=latin1
Put some test data into this table, at least 100,000, the ID field is also disorderly order.
Copy Code code as follows:
[yejr@imysql]> Select COUNT (*) from t_innodb_random\g
1. Row ***************************
COUNT (*): 393216
1, constant equivalent search:
Copy Code code as follows:
[yejr@imysql]> explain select ID from t_innodb_random where id = 13412\g
1. Row ***************************
Id:1
Select_type:simple
Table:t_innodb_random
Type:ref
possible_keys:idx_id
key:idx_id
Key_len:4
Ref:const
Rows:1
Extra:using Index
[yejr@imysql]> Select ID from t_innodb_random where id = 13412;
1 row in Set (0.00 sec)
You can see that the execution plan is very good, constant equivalence query, very fast.
2. Use the rand () function to multiply the constants and retrieve the random numbers after:
Copy Code code as follows:
[yejr@imysql]> explain select ID from t_innodb_random where id = round (rand () *13241324) \g
1. Row ***************************
Id:1
Select_type:simple
Table:t_innodb_random
Type:index
Possible_keys:null
key:idx_id
Key_len:4
Ref:null
rows:393345
Extra:using where; Using Index
[yejr@imysql]> Select ID from t_innodb_random where id = round (rand () *13241324) \g
Empty Set (0.26 sec)
You can see that the execution plan is bad, although it is only scanning the index, but it is very inefficient to do a full index scan. Because the Where condition contains rand (), so that MySQL treats it as a variable, it cannot be queried in the same way as constant equivalence, which is inefficient.
We change the constants to the maximum ID value of the T_innodb_random table, multiplied by rand () to retrieve a random number to see what happens:
Copy Code code as follows:
[yejr@imysql]> explain select ID from t_innodb_random where id = round (rand () * (select Max (IDS) from T_innodb_random) \g
1. Row ***************************
Id:1
Select_type:primary
Table:t_innodb_random
Type:index
Possible_keys:null
key:idx_id
Key_len:4
Ref:null
rows:393345
Extra:using where; Using Index
2. Row ***************************
Id:2
Select_type:subquery
Table:null
Type:null
Possible_keys:null
Key:null
Key_len:null
Ref:null
Rows:null
Extra:select Tables Optimized away
[yejr@imysql]> Select ID from t_innodb_random where id = round (rand () * (select MAX (ID) from t_innodb_random) \g
Empty Set (0.27 sec)
As you can see, the execution plan is still a full index scan, and execution time is almost equal.
3, transformation into a normal subquery mode, here are two times subquery
Copy Code code as follows:
[yejr@imysql]> explain select ID from t_innodb_random WHERE id = (SELECT round (rand () * (select MAX (ID) from T_innodb_ran DOM)) \g as NID)
1. Row ***************************
Id:1
Select_type:primary
Table:t_innodb_random
Type:index
Possible_keys:null
key:idx_id
Key_len:4
Ref:null
rows:393345
Extra:using where; Using Index
2. Row ***************************
Id:3
Select_type:subquery
Table:null
Type:null
Possible_keys:null
Key:null
Key_len:null
Ref:null
Rows:null
Extra:select Tables Optimized away
[yejr@imysql]> Select ID from t_innodb_random WHERE id = (SELECT round (rand () * (select MAX (ID) from t_innodb_random)) as NID) \g
Empty Set (0.27 sec)
As you can see, the execution plan is bad and the execution time is slow.
4, transformation into Join Association query, but the maximum value is still expressed by constant
Copy Code code as follows:
[yejr@imysql]> explain select ID from t_innodb_random T1 join (select round (rand () *13241324) as Id2) as T2 where t1.id = T2.id2\g
1. Row ***************************
Id:1
Select_type:primary
Table: <derived2>
Type:system
Possible_keys:null
Key:null
Key_len:null
Ref:null
Rows:1
Extra:
2. Row ***************************
Id:1
Select_type:primary
Table:t1
Type:ref
possible_keys:idx_id
key:idx_id
Key_len:4
Ref:const
Rows:1
Extra:using where; Using Index
3. Row ***************************
Id:2
Select_type:derived
Table:null
Type:null
Possible_keys:null
Key:null
Key_len:null
Ref:null
Rows:null
Extra:no tables used
[yejr@imysql]> Select ID from t_innodb_random T1 join (select round (rand () *13241324) as Id2) as t2 where t1.id = T2.id2 \g
Empty Set (0.00 sec)
The execution plan is perfect, and the first constant equivalence query is the same, and the execution time is very fast.
Although this method is very good, but it is possible to query the record, the scope of the search, but the results limit 1 can be:
Copy Code code as follows:
[yejr@imysql]> explain select ID from t_innodb_random where ID > (select round (rand () * (select MAX (ID) from t_innodb_ Random) as NID) limit 1\g
1. Row ***************************
Id:1
Select_type:primary
Table:t_innodb_random
Type:index
Possible_keys:null
key:idx_id
Key_len:4
Ref:null
rows:393345
Extra:using where; Using Index
2. Row ***************************
Id:3
Select_type:subquery
Table:null
Type:null
Possible_keys:null
Key:null
Key_len:null
Ref:null
Rows:null
Extra:select Tables Optimized away
[yejr@imysql]> Select ID from t_innodb_random where ID > (select round (rand () * (select MAX (ID) from t_innodb_random) As NID) limit 1\g
1. Row ***************************
id:1301
1 row in Set (0.00 sec)
As you can see, although the execution plan is also a full index scan, because of the limit 1, only need to find a record, you can terminate the scan, so the efficiency is very fast.
Summary:
When you randomly take a record from a database, you can increase the efficiency by generating a random number of rand () in a join subquery.
5. Take a look at how to get multiple random values at once with ORDRR by RAND ():
Copy Code code as follows:
[yejr@imysql]> explain select ID from t_innodb_random to rand () limit 1000\g
1. Row ***************************
Id:1
Select_type:simple
Table:t_innodb_random
Type:index
Possible_keys:null
key:idx_id
Key_len:4
Ref:null
rows:393345
Extra:using index; Using temporary; Using Filesort
[yejr@imysql]> Select ID from t_innodb_random to rand () limit 1000;
1000 rows in Set (0.41 sec)
Full index scan, generating sort temp table, too bad too slow.
6, put the random number in the subquery to see:
Copy Code code as follows:
[yejr@imysql]> explain select ID from t_innodb_random where ID > (select rand () * (select MAX (ID) from T_innodb_rand OM) as NID) limit 1000\g
1. Row ***************************
Id:1
Select_type:primary
Table:t_innodb_random
Type:index
Possible_keys:null
key:idx_id
Key_len:4
Ref:null
rows:393345
Extra:using where; Using Index
2. Row ***************************
Id:3
Select_type:subquery
Table:null
Type:null
Possible_keys:null
Key:null
Key_len:null
Ref:null
Rows:null
Extra:select Tables Optimized away
[yejr@imysql]> Select ID from t_innodb_random where ID > (select rand () * (select MAX (ID) from t_innodb_random) as N ID) Limit 1000\g
1000 rows in Set (0.04 sec)
Well, it's got a lot of speed, this looks pretty good:
7, imitate the above method, change into join and random number subquery Association
Copy Code code as follows:
[yejr@imysql]> explain select ID from t_innodb_random T1 join (select rand () * (select MAX (ID) from t_innodb_random) as NID) T2 on t1.id > T2.nid limit 1000\g
1. Row ***************************
Id:1
Select_type:primary
Table: <derived2>
Type:system
Possible_keys:null
Key:null
Key_len:null
Ref:null
Rows:1
Extra:
2. Row ***************************
Id:1
Select_type:primary
Table:t1
Type:range
possible_keys:idx_id
key:idx_id
Key_len:4
Ref:null
rows:196672
Extra:using where; Using Index
3. Row ***************************
Id:2
Select_type:derived
Table:null
Type:null
Possible_keys:null
Key:null
Key_len:null
Ref:null
Rows:null
Extra:no tables used
4. Row ***************************
Id:3
Select_type:subquery
Table:null
Type:null
Possible_keys:null
Key:null
Key_len:null
Ref:null
Rows:null
Extra:select Tables Optimized away
[yejr@imysql]> Select ID from t_innodb_random T1 join (select rand () * (select MAX (ID) from t_innodb_random) as nid) T2 On t1.id > T2.nid limit 1000\g
1000 rows in Set (0.00 sec)
As you can see, full index retrieval, found to meet the conditions of the record, directly to get 1000 lines, this method is the fastest.
In summary, if you want to randomly take one or n records from a MySQL database, it is a good idea to put Rand () to generate random numbers in a join subquery to improve efficiency.
It says so much nonsense, and finally, simply put the following sql:
Copy Code code as follows:
SELECT ID from Table order by RAND () LIMIT N;
Into the following:
Copy Code code as follows:
Select ID from table T1 JOIN (select RAND () * (select MAX (ID) to table) as NID) T2 on t1.id > T2.nid LIMIT N;
You can enjoy the random number in the SQL directly, no longer in the program to construct a series of random numbers to retrieve.
