[MySQL optimization case] series-rand () optimization

As we all know, in MySQL, if the direct ORDER by RAND (), the efficiency is very poor, because it will be executed multiple times. In fact, if the equivalent query is also used with RAND (), let's take a look at the different execution plans and execution time of the following SQL.

First, look at the table DDL, which is a InnoDB table with no explicit self-increment primary key:

[Email protected]]> Show CREATE TABLE t_innodb_random\g*************************** 1. Row ***************************table:t_innodb_randomcreate table:create Table ' t_innodb_random ' (' id ' int (10) unsigned not NULL, ' user ' varchar (+) NOT null default ', KEY ' idx_id ' (' id ')) engine=innodb default Charset=latin1

To fill in this table some test data, at least 100,000, ID field is also disorderly.

[Email protected]]> Select COUNT (*) from t_innodb_random\g*************************** 1. Row ***************************count (*): 393216

1, constant equivalent search:

[[Email protected]]> explain select ID from t_innodb_random where id = 13412\g*************************** 1. Row ***************************id:1select_type:simpletable:t_innodb_randomtype:refpossible_keys:idx_idkey:idx_ Idkey_len:4ref:constrows:1extra:using index[[email protected]]> Select ID from t_innodb_random where id = 13412;1 R ow in Set (0.00 sec)

You can see the execution plan is very good, is a constant equivalent query, very fast.

2. Use the rand () function multiplied by the constant to obtain a random number after retrieval:

[[Email protected]]> explain select ID from t_innodb_random where id = round (rand () *13241324) \g********************** 1. Row ***************************id:1select_type:simpletable:t_innodb_randomtype:indexpossible_keys:nullkey:idx_ Idkey_len:4ref:nullrows:393345extra:using where; Using index[[email protected]]> Select ID from t_innodb_random where id = round (rand () *13241324) \gempty Set (0.26 sec)

You can see that the execution plan is bad, although it is only a scan index, but a full index scan, the efficiency is very poor. Because the Where condition contains rand (), it is inefficient for MySQL to treat it as a variable that cannot be queried in a constant equivalent way.

We change the constants to take the maximum ID value of the T_innodb_random table, and then multiply rand () to get a random number after the search to see what happens:

[[Email protected]]> explain select ID from t_innodb_random where id = round (rand () * (select MAX (ID) from T_innodb_rando m)) \g*************************** 1. Row ***************************id:1select_type:primarytable:t_innodb_randomtype:indexpossible_keys:nullkey:idx_ Idkey_len:4ref:nullrows:393345extra:using where; Using index*************************** 2. Row ***************************id:2select_type:subquerytable:nulltype:nullpossible_keys:nullkey:nullkey_len: NULLref:NULLrows:NULLExtra:Select tables optimized away[[email protected]]> Select ID from t_innodb_random where ID = Round (rand () * (select MAX (ID) from t_innodb_random)) \gempty Set (0.27 sec)

As you can see, the execution plan is still a full index scan, and the execution time is pretty basic.

3, transform into a common sub-query mode, there are two sub-queries

[[Email protected]]> explain select ID from t_innodb_random WHERE id = (SELECT round (rand () * (select MAX (ID) from T_inno Db_random) as Nid) \g*************************** 1. Row ***************************id:1select_type:primarytable:t_innodb_randomtype:indexpossible_keys:nullkey:idx_ Idkey_len:4ref:nullrows:393345extra:using where; Using index*************************** 2. Row ***************************id:3select_type:subquerytable:nulltype:nullpossible_keys:nullkey:nullkey_len: NULLref:NULLrows:NULLExtra:Select tables optimized away[[email protected]]> Select ID from t_innodb_random where ID = (Select round (rand () * (select MAX (ID) from t_innodb_random) as Nid) \gempty set (0.27 sec)

You can see that the execution plan is not good and the execution time is slow.

4, transform into Join Association query, but the maximum value or constant representation

[[Email protected]]> 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:1select_type:primarytable: <derived2>type:systempossible_keys:nullkey: nullkey_len:nullref:nullrows:1extra:*************************** 2. Row ***************************id:1select_type:primarytable:t1type:refpossible_keys:idx_idkey:idx_idkey_len: 4ref:constrows:1extra:using where; Using index*************************** 3. Row ***************************id:2select_type:derivedtable:nulltype:nullpossible_keys:nullkey:nullkey_len: NULLref:NULLrows:NULLExtra:No Tables Used[[email protected]]> select ID from t_innodb_random T1 join (select round (r and () *13241324) as Id2) as t2 where t1.id = T2.id2\gempty Set (0.00 sec)

The execution plan is perfect at this time, and the first constant equivalent query is the same, and the execution is very fast.
Although this method is very good, but it is possible to query the record, the scope of transformation search, but the result of limit 1 can be:

[[Email protected]]> explain select ID from t_innodb_random where ID > (select round (rand () * () (select Max (ID) from t_i Nnodb_random) as NID) limit 1\g*************************** 1. Row ***************************id:1select_type:primarytable:t_innodb_randomtype:indexpossible_keys:nullkey:idx_ Idkey_len:4ref:nullrows:393345extra:using where; Using index*************************** 2. Row ***************************id:3select_type:subquerytable:nulltype:nullpossible_keys:nullkey:nullkey_len: NULLref:NULLrows:NULLExtra:Select tables optimized away[[email protected]]> 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:13011 row in Set (0.00 sec)

As you can see, although the execution plan is also full index scan, because of the limit 1, only need to find a record to terminate the scan, so the efficiency is very fast.

Summary:
When you randomly fetch a record from a database, you can put rand () generation random number in the join subquery to improve efficiency.

5. Take a look at how to obtain multiple random values at once using the ORDRR by RAND () method:

[Email protected]]> explain select ID from T_innodb_random ORDER by rand () limit 1000\g*************************** 1. Row ***************************id:1select_type:simpletable:t_innodb_randomtype:indexpossible_keys:nullkey:idx_ Idkey_len:4ref:nullrows:393345extra:using index; Using temporary; Using filesort[[email protected]]> Select ID from T_innodb_random ORDER by rand () limit 1000;1000 the rows in set (0.41 sec )

Full index Scan, generate sort temp table, too bad too slow.

6. Put the random number in the subquery to see:

[[Email protected]]> explain select ID from t_innodb_random where ID > (select rand () * (select MAX (ID) from T_innod B_random) as NID) limit 1000\g*************************** 1. Row ***************************id:1select_type:primarytable:t_innodb_randomtype:indexpossible_keys:nullkey:idx_ Idkey_len:4ref:nullrows:393345extra:using where; Using index*************************** 2. Row ***************************id:3select_type:subquerytable:nulltype:nullpossible_keys:nullkey:nullkey_len: NULLref:NULLrows:NULLExtra:Select tables optimized away[[email protected]]> Select ID from t_innodb_random where ID > (select rand () * (select MAX (ID) from t_innodb_random) as NID) limit 1000\g1000 rows in Set (0.04 sec)

Well, it's got a lot of speed, and it looks good:)

7, imitate the above method, change to join and random number subquery Association

[[email protected]]> 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:1select_type:primarytable: <derived2>type:systempossible_keys:nullkey: nullkey_len:nullref:nullrows:1extra:*************************** 2. Row ***************************id:1select_type:primarytable:t1type:rangepossible_keys:idx_idkey:idx_idkey_len: 4ref:nullrows:196672extra:using where; Using index*************************** 3. Row ***************************id:2select_type:derivedtable:nulltype:nullpossible_keys:nullkey:nullkey_len: NULLref:NULLrows:NULLExtra:No Tables used*************************** 4. Row ***************************id:3select_type:subquerytable:nulltype:nullpossible_keys:nullkey:nullkey_len: NULLref:NULLrows:NULLExtra:Select tables optimized away[[email protected]]> 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\g1 Rows in Set (0.00 sec)

As can be seen, full index retrieval, found to meet the conditions of the record, the direct access to 1000 lines, this method is the fastest.

In summary, when you want to randomly fetch one or n records from the MySQL database, it is best to put rand () generated random numbers in the join subquery to improve efficiency.
The above said a lot of nonsense, the last simple, is to put the following SQL:

SELECT ID from table ORDER by RAND () LIMIT N;

Transform it to the following:

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

If you want to achieve a completely random, 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;

You can enjoy getting random numbers directly in SQL without having to construct a random number in the program to retrieve them.

From:http://imysql.com/2014/07/04/mysql-optimization-case-rand-optimize.shtml

[MySQL optimization case] series-rand () optimization