所在的位置: mysql >> mysql前景 >> MySQL优化案例系列RAN

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