MySQL优化案例系列RAN

妇孺皆知，在MySQL中，倘若直接ORDERBYRAND()的话，效率特别差，由于会屡屡实行。真相上，倘若等值盘诘也是用RAND()的话也这样，咱们先来看看上面这几个SQL的不同实行安排和实行耗时。

首先，看下建表DDL，这是一个没有显式自增主键的InnoDB表：

[yejr

imysql]showcreatetablet_innodb_random\G

***************************1.row***************************

Table:t_innodb_random

CreateTable:CREATETABLE`t_innodb_random`(

`id`int(10)unsignedNOTNULL,

`user`varchar(64)NOTNULLDEFAULT,

KEY`idx_id`(`id`)

)ENGINE=InnoDBDEFAULTCHARSET=latin1

往这个内外贯注一些测试数据，起码10万以上，id字段也是乱序的。

[yejr

imysql]selectcount(*)fromt_innodb_random\G

***************************1.row***************************

count(*):

1、常量等值检索：

[yejr

imysql]explainselectidfromt_innodb_randomwhereid=\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:Usingindex

[yejr

imysql]selectidfromt_innodb_randomwhereid=;

1rowinset(0.00sec)

能够看到实行安排很不错，是常量等值盘诘，速率特别快。

2、运用RAND()函数乘以常量，求得随机数后检索：

[yejr

imysql]explainselectidfromt_innodb_randomwhereid=round(rand()*)\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:

Extra:Usingwhere;Usingindex

[yejr

imysql]selectidfromt_innodb_randomwhereid=round(rand()*)\G

Emptyset(0.26sec)

能够看到实行安排很糟了，即使是只扫描索引，然而做了全索引扫描，效率特别差。由于WHERE前提中包括了RAND()，使得MySQL把它当做变量来处置，没法用常量等值的方法盘诘，效率很低。

咱们把常量改为取t_innodb_random表的最大id值，再乘以RAND()求得随机数后检索看看甚么情状：

[yejr

imysql]explainselectidfromt_innodb_randomwhereid=round(rand()*(selectmax(id)fromt_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:

Extra:Usingwhere;Usingindex

***************************2.row***************************

id:2

select_type:SUBQUERY

table:NULL

type:NULL

possible_keys:NULL

key:NULL

key_len:NULL

ref:NULL

rows:NULL

Extra:Selecttablesoptimizedaway

[yejr

imysql]selectidfromt_innodb_randomwhereid=round(rand()*(selectmax(id)fromt_innodb_random))\G

Emptyset(0.27sec)

能够看到，实行安排仍然是全索引扫描，实行耗时也基真相当。

3、变革成一般子盘诘形式，这边有两次子盘诘

[yejr

imysql]explainselectidfromt_innodb_randomwhereid=(selectround(rand()*(selectmax(id)fromt_innodb_random))asnid)\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:

Extra:Usingwhere;Usingindex

***************************2.row***************************

id:3

select_type:SUBQUERY

table:NULL

type:NULL

possible_keys:NULL

key:NULL

key_len:NULL

ref:NULL

rows:NULL

Extra:Selecttablesoptimizedaway

[yejr

imysql]selectidfromt_innodb_randomwhereid=(selectround(rand()*(selectmax(id)fromt_innodb_random))asnid)\G

Emptyset(0.27sec)

能够看到，实行安排也不好，实行耗时较慢。

4、变革成JOIN联系盘诘，不过最大值仍然用常量示意

[yejr

imysql]explainselectidfromt_innodb_randomt1join(selectround(rand()*)asid2)ast2wheret1.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:Usingwhere;Usingindex

***************************3.row***************************

id:2

select_type:DERIVED

table:NULL

type:NULL

possible_keys:NULL

key:NULL

key_len:NULL

ref:NULL

rows:NULL

Extra:Notablesused

[yejr

imysql]selectidfromt_innodb_randomt1join(selectround(rand()*)asid2)ast2wheret1.id=t2.id2\G

Emptyset(0.00sec)

这时辰实行安排就特别完满了，和最开端的常量等值盘诘是同样的了，实行耗时也特别之快。

这类法子即使很好，然而有或许盘诘不到纪录，变革领域搜索，但成效LIMIT1就能够了：

[yejr

imysql]explainselectidfromt_innodb_randomwhereid(selectround(rand()*(selectmax(id)fromt_innodb_random))asnid)limit1\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:

Extra:Usingwhere;Usingindex

***************************2.row***************************

id:3

select_type:SUBQUERY

table:NULL

type:NULL

possible_keys:NULL

key:NULL

key_len:NULL

ref:NULL

rows:NULL

Extra:Selecttablesoptimizedaway

[yejr

imysql]selectidfromt_innodb_randomwhereid(selectround(rand()*(selectmax(id)fromt_innodb_random))asnid)limit1\G

***************************1.row***************************

id:

1rowinset(0.00sec)

能够看到，即使实行安排也是全索引扫描，然而由于有了LIMIT1，只要要找到一笔纪录，便可闭幕扫描，因此效率仍然很快的。

小结：

从数据库中随机取一笔纪录时，能够把RAND()生成随机数放在JOIN子盘诘中以抬高效率。

5、再来看看用ORDRRBYRAND()方法一次取很多个随机值的方法：

[yejr

imysql]explainselectidfromt_innodb_randomorderbyrand()limit\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:

Extra:Usingindex;Usingtemporary;Usingfilesort

[yejr

imysql]selectidfromt_innodb_randomorderbyrand()limit;

rowsinset(0.41sec)

全索引扫描，生成排序长期表，太差太慢了。

6、把随机数放在子盘诘里看看：

[yejr

imysql]explainselectidfromt_innodb_randomwhereid(selectrand()*(selectmax(id)fromt_innodb_random)asnid)limit\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:

Extra:Usingwhere;Usingindex

***************************2.row***************************

id:3

select_type:SUBQUERY

table:NULL

type:NULL

possible_keys:NULL

key:NULL

key_len:NULL

ref:NULL

rows:NULL

Extra:Selecttablesoptimizedaway

[yejr

imysql]selectidfromt_innodb_randomwhereid(selectrand()*(selectmax(id)fromt_innodb_random)asnid)limit\G

rowsinset(0.04sec)

嗯，提速了不少，这个看起来还不赖：）

7、照样上头的法子，改为JOIN和随机数子盘诘联系

[yejr

imysql]explainselectidfromt_innodb_randomt1join(selectrand()*(selectmax(id)fromt_innodb_random)asnid)t2ont1.idt2.nidlimit\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:

Extra:Usingwhere;Usingindex

***************************3.row***************************

id:2

select_type:DERIVED

table:NULL

type:NULL

possible_keys:NULL

key:NULL

key_len:NULL

ref:NULL

rows:NULL

Extra:Notablesused

***************************4.row***************************

id:3

select_type:SUBQUERY

table:NULL

type:NULL

possible_keys:NULL

key:NULL

key_len:NULL

ref:NULL

rows:NULL

Extra:Selecttablesoptimizedaway

[yejr

imysql]selectidfromt_innodb_randomt1join(selectrand()*(selectmax(id)fromt_innodb_random)asnid)t2ont1.idt2.nidlimit\G

rowsinset(0.00sec)

能够看到，全索引检索，觉察吻合纪录的前提后，直接博得行，这个法子是最快的。

综上，想从MySQL数据库中随机取一条或许N笔纪录时，最佳把RAND()生成随机数放在JOIN子盘诘中以抬高效率。

上头说了那末多的空话，结尾简朴说下，即是把上面这个SQL：

SELECTidFROMtableORDERBYRAND()LIMITn;

变革成上面这个：

SELECTidFROMtablet1,JOIN(SELECTRAND()*(SELECTMAX(id)FROMtable)ASnid)t2ONt1.idt2.nidLIMITn;

就能够享用在SQL中直接博得随机数了，不必再在程序中构造一串随机数去检索了。

===============================================================================

倘若再有甚么题目，欢送参与我的QQ群()议论。

===============================================================================

题图：诸葛亮，诸葛孔明，两代伶人剧照，众人更喜爱哪个呢？话说诸葛亮在大普遍民心目中理当是英明、英明、眼光深远的局面。毕生弯腰尽瘁辅助刘备复兴汉室，怅然终不能成，倘若他一开端就抉择辅助曹操，史籍将会产生甚么变动咱们不得而知，由于永恒没有法子产生这个倘若了。在诸葛亮的中后期，不少事项都是“弯腰尽瘁，死然后已”，这类形式即是好的吗，我看未必。其沮丧的一面即是致使他的东家、他的手下自助考虑的主动性没法被变动，终究甚么事项都要靠他一限度，那不累死才怪。因此说，做为团队的负责人，不要甚么事项都大包大揽，而理当恰当受权给手下，给他们恰当出错的机缘，如许本领生长，不然这个负责人永恒都妥善保母。

对于MySQL的各个方面众人想知道甚么，能够直接留言答复，我会从中抉择一些热点话题施行分享。同时指望众人多多转发，多一些浏览量是老叶赓续勉力分享的绝佳助力，感谢众人:)

结尾打个广告，运维圈人士专属铁观音茶叶微店上线了，拜候：

转载请注明：http://www.aierlanlan.com/cyrz/1136.html