| [yejr@imysql]> select id from t_innodb_random where id > (select round(rand()*(select max(id) from t_innodb_random)) as nid) limit 1G *************************** 1. row *************************** id: 1301 1 row in set (0.00 sec) |
可以看到，虽然执行计划也是全索引扫描，但是因为有了LIMIT 1，只需要找到一条记录，即可终止扫描，所以效率还是很快的。
小结：
从数据库中随机取一条记录时，可以把RAND()生成随机数放在JOIN子查询中以提高效率。
5、再来看看用ORDRR BY RAND()方式一次取得多个随机值的方式：
| [yejr@imysql]> explain select id from t_innodb_random order by rand() limit 1000G *************************** 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 order by rand() limit 1000; 1000 rows in set (0.41 sec) |
全索引扫描，生成排序临时表，太差太慢了。
6、把随机数放在子查询里看看：
| [yejr@imysql]> explain select id from t_innodb_random where id > (select rand() * (select max(id) from t_innodb_random) as nid) limit 1000G *************************** 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 nid) limit 1000G 1000 rows in set (0.04 sec) |
嗯，提速了不少，这个看起来还不赖：）
7、仿照上面的方法，改成JOIN和随机数子查询关联
| [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 1000G *************************** 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 |
