The rand () Optimization case analysis _mysql under Mysql

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:

[yejr@imysql]> Show CREATE TABLE t_innodb_random\g
*************************** 1 row ************************
table:t_innodb_random
Create table:create Table ' t_innodb_random ' (
' id ' int () 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.

[yejr@imysql]> Select COUNT (*) from t_innodb_random\g
*************************** 1. Row ********************
Count (*): 393216

1, constant equivalent search:

[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:

[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
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:

[yejr@imysql]> explain select ID from t_innodb_random where id = round (rand () * (select Max (IDS) from T_innodb_random) \g< c4/>*************************** 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
where; Using index
*************************** 2 row ***************************
id:2
select_type: Subquery
table:null
type:null
possible_keys:null
key:null
key_len: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
Emp Ty 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

[yejr@imysql]> explain select ID from t_innodb_random WHERE id = (SELECT round (rand () * (select MAX (ID) from T_innodb_ran DOM) as NID) \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
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

[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:

[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
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 ():

[yejr@imysql]> explain select ID from T_innodb_random ORDER by rand () limit 1000\g
*************************** 1. R ow ***************************
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:

[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
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

[yejr@imysql]> explain select ID from t_innodb_random T1 join (select rand () * (select MAX (ID) from T_innodb_random) a s 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:n ull 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:n ull ref:null rows:null extra:no tables used *************************** 4. Row *************************** id:3 select_type:subquery table:null type:null possible_keys:null key:null:

 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:

SELECT ID from Table order by RAND () LIMIT N;

Into the following:

Select ID from table T1 JOIN (select RAND () * (select MAX (ID) to table) as NID) T2 on t1.id > T2.nid LIMIT N;

If you want to achieve complete randomness, you can also change to the following wording:

Select ID from table T1 JOIN (select round (RAND () * (select MAX (ID) from table) as Nid from Table LIMIT N) t2 on t1.id = T2.nid;

The

can enjoy a random number directly in SQL without having to construct a sequence of random numbers in the program to retrieve it.