MySQL優(yōu)化之對(duì)RAND()的優(yōu)化方法
眾所周知，在MySQL中，如果直接 ORDER BY RAND() 的話(huà)，效率非常差，因?yàn)闀?huì)多次執(zhí)行。事實(shí)上，如果等值查詢(xún)也是用 RAND() 的話(huà)也如此，我們先來(lái)看看下面這幾個(gè)SQL的不同執(zhí)行計(jì)劃和執(zhí)行耗時(shí)。
首先，看下建表DDL，這是一個(gè)沒(méi)有顯式自增主鍵的InnoDB表：
[yejr@imysql]> show create table t_innodb_random\G
*************************** 1. row ***************************
Table: t_innodb_random
Create Table: CREATE TABLE `t_innodb_random` (
`id` int(10) unsigned NOT NULL,
`user` varchar(64) NOT NULL DEFAULT '',
KEY `idx_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
往這個(gè)表里灌入一些測(cè)試數(shù)據(jù)，至少10萬(wàn)以上， id 字段也是亂序的。
[yejr@imysql]> select count(*) from t_innodb_random\G
*************************** 1. row ***************************
count(*): 393216
1、常量等值檢索：
[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)
可以看到執(zhí)行計(jì)劃很不錯(cuò)，是常量等值查詢(xún)，速度非?？臁?/p>
2、使用RAND()函數(shù)乘以常量，求得隨機(jī)數(shù)后檢索：
[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)
可以看到執(zhí)行計(jì)劃很糟糕，雖然是只掃描索引，但是做了全索引掃描，效率非常差。因?yàn)閃HERE條件中包含了RAND()，使得MySQL把它當(dāng)做變量來(lái)處理，無(wú)法用常量等值的方式查詢(xún)，效率很低。
我們把常量改成取t_innodb_random表的最大id值，再乘以RAND()求得隨機(jī)數(shù)后檢索看看什么情況：
[yejr@imysql]> explain select id from t_innodb_random where id = round(rand()*(select max(id) 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)
可以看到，執(zhí)行計(jì)劃依然是全索引掃描，執(zhí)行耗時(shí)也基本相當(dāng)。
3、改造成普通子查詢(xún)模式 ，這里有兩次子查詢(xún)
[yejr@imysql]> explain select id from t_innodb_random where id = (select round(rand()*(select max(id) from t_innodb_random)) 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
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)
可以看到，執(zhí)行計(jì)劃也不好，執(zhí)行耗時(shí)較慢。
4、改造成JOIN關(guān)聯(lián)查詢(xún)，不過(guò)最大值還是用常量表示
[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)
這時(shí)候執(zhí)行計(jì)劃就非常完美了，和最開(kāi)始的常量等值查詢(xún)是一樣的了，執(zhí)行耗時(shí)也非常之快。
這種方法雖然很好，但是有可能查詢(xún)不到記錄，改造范圍查找，但結(jié)果LIMIT 1就可以了：
[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)
可以看到，雖然執(zhí)行計(jì)劃也是全索引掃描，但是因?yàn)橛辛薒IMIT 1，只需要找到一條記錄，即可終止掃描，所以效率還是很快的。
小結(jié)：
從數(shù)據(jù)庫(kù)中隨機(jī)取一條記錄時(shí)，可以把RAND()生成隨機(jī)數(shù)放在JOIN子查詢(xún)中以提高效率。
5、再來(lái)看看用ORDRR BY RAND()方式一次取得多個(gè)隨機(jī)值的方式：
[yejr@imysql]> explain select id from t_innodb_random order by 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 order by rand() limit 1000;
1000 rows in set (0.41 sec)
全索引掃描，生成排序臨時(shí)表，太差太慢了。
6、把隨機(jī)數(shù)放在子查詢(xún)里看看：
[yejr@imysql]> explain select id from t_innodb_random where id > (select rand() * (select max(id) from t_innodb_random) 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 nid) limit 1000\G
1000 rows in set (0.04 sec)
嗯，提速了不少，這個(gè)看起來(lái)還不賴(lài)：）
7、仿照上面的方法，改成JOIN和隨機(jī)數(shù)子查詢(xún)關(guān)聯(lián)
[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)
可以看到，全索引檢索，發(fā)現(xiàn)符合記錄的條件后，直接取得1000行，這個(gè)方法是最快的。
綜上，想從MySQL數(shù)據(jù)庫(kù)中隨機(jī)取一條或者N條記錄時(shí)，最好把RAND()生成隨機(jī)數(shù)放在JOIN子查詢(xún)中以提高效率。
上面說(shuō)了那么多的廢話(huà)，最后簡(jiǎn)單說(shuō)下，就是把下面這個(gè)SQL：
SELECT id FROM table ORDER BY RAND() LIMIT n;
改造成下面這個(gè)：
SELECT id FROM table t1 JOIN (SELECT RAND() * (SELECT MAX(id) FROM table) AS nid) t2 ON t1.id > t2.nid LIMIT n;
就可以享受在SQL中直接取得隨機(jī)數(shù)了，不用再在程序中構(gòu)造一串隨機(jī)數(shù)去檢索了。
相關(guān)文章
MySQL創(chuàng)建定時(shí)任務(wù)實(shí)例(每天凌晨1點(diǎn)、每小時(shí)、每分鐘、某一時(shí)間點(diǎn))
在mysql中有時(shí)候要定時(shí)更新或者刪除一部分?jǐn)?shù)據(jù)需要用到mysql的定時(shí)任務(wù),下面這篇文章主要給大家介紹了關(guān)于MySQL創(chuàng)建定時(shí)任務(wù)的相關(guān)資料,包括每天凌晨1點(diǎn)、每小時(shí)、每分鐘、某一時(shí)間點(diǎn)等,需要的朋友可以參考下2023-03-03
MySQL學(xué)習(xí)之事務(wù)與并發(fā)控制
這篇文章主要介紹了MySQL中的事務(wù)與并發(fā)控制，一個(gè)事務(wù)可以理解為一組操作，這一組操作要么全部執(zhí)行，要么全部不執(zhí)行，想了解更多的小伙伴，可以參考閱讀本文2023-03-03
MySQL 8.0.23中復(fù)制架構(gòu)從節(jié)點(diǎn)自動(dòng)故障轉(zhuǎn)移的問(wèn)題
這篇文章主要介紹了MySQL 8.0.23中復(fù)制架構(gòu)從節(jié)點(diǎn)自動(dòng)故障轉(zhuǎn)移的問(wèn)題,本文給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2021-01-01
這篇文章主要給大家介紹了關(guān)于mysql自增id超大問(wèn)題的排查與解決方法，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2018-12-12
JOIN是一種非常常見(jiàn)的操作，用于將兩個(gè)或多個(gè)表中的數(shù)據(jù)合并到一個(gè)結(jié)果集中。MySQL支持多種JOIN類(lèi)型，本文通過(guò)代碼示例詳細(xì)介紹了Join的使用優(yōu)化，有需要的小伙伴可以參考閱讀2023-04-04
MySQL中的MVCC機(jī)制想必大家都有所耳聞吧，雖然在平時(shí)MySQL使用過(guò)程中基本上用不到，但是面試中出場(chǎng)率十分高，那么你對(duì)MVCC機(jī)制了解多少呢，MVCC機(jī)制是用來(lái)干嘛的呢，底層的工作原理是怎么樣的呢，本文就帶你一探究竟2023-07-07
這篇文章主要介紹了MySQL中的binary類(lèi)型使用操作，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2020-12-12
MySQL中一些常用的數(shù)據(jù)表操作語(yǔ)句筆記
這篇文章主要介紹了MySQL中一些常用的數(shù)據(jù)表操作語(yǔ)句筆記,其中重點(diǎn)講解了刪除關(guān)聯(lián)表的方法,需要的朋友可以參考下2016-03-03
MySQL 獲得當(dāng)前日期時(shí)間 函數(shù)
這篇文章主要介紹了MySQL 獲得當(dāng)前日期時(shí)間 函數(shù) 非常不錯(cuò)，具有參考借鑒價(jià)值,需要的朋友可以參考下2017-07-07
Mysql5.7.17 winx64.zip解壓縮版安裝配置圖文教程
這篇文章主要介紹了Mysql5.7.17 winx64.zip解壓縮版安裝配置圖文教程,需要的朋友可以參考下2018-03-03