php mysql 随机查询_PHP+Mysql随机高效查询

译者序

之前有位朋友提到从MySQL随机取1条记录其实只要SELECT * FROM table ORDER BY RAND() LIMIT 1即可。其实这个语句有很大的性能问题，对于大表的效率是非常低下的。我们可以看一下MySQL对其的解释：

EXPLAIN SELECT *

FROM money_logs

ORDER BY RAND( )

LIMIT 1idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra1SIMPLEtableALLNULLNULLNULLNULL173784Using temporary; Using filesort

这个SQL语句无法使用任何索引，还必须使用临时表和文件排序，在一个15万条记录的MyISAM表需要花大约0.3秒。已经是相当慢的了。如何优化，请往下看：

第一个例子我们先假设ID是从1开始并且1和ID的最大值之间没有任何空档。

将工作移入应用程序

第一个想法：如果我们可以事先在应用程序中计算出ID，那么就可以简化整个工作。

1

2

3

SELECTMAX(id)FROMrandom;

## 在应用程序中生成随机id

SELECTnameFROMrandomWHEREid=<random-id>

由于MAX(id) == COUNT(id) 我们只要生成从1到MAX(id)之间一个随机数，并将其传给数据库并取回随机行。

上面第一个SELECT基本上是一个可以被优化掉的空操作。第二个是一个针对常量的eq_ref查询，同样也很快。

将任务放入数据库

不过有必要将其放入应用程序吗？难道我们不能在数据库里完成？

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

# 生成一个随机 ID

>SELECTRAND()*MAX(id)FROMrandom;

+------------------+

|RAND()*MAX(id)|

+------------------+

|689.37582507297|

+------------------+

# 喔，这是一个浮点数，不过我们需要整数

>SELECTCEIL(RAND()*MAX(id))FROMrandom;

+-------------------------+

|CEIL(RAND()*MAX(id))|

+-------------------------+

|1000000|

+-------------------------+

# 好多了。不过性能如何？

>EXPLAIN

SELECTCEIL(RAND()*MAX(id))FROMrandom;

+----+-------------+-------+-------+------+-------------+

|id|select_type|table|type|rows|Extra|

+----+-------------+-------+-------+------+-------------+

|1|SIMPLE|random|index|1000000|Usingindex|

+----+-------------+-------+-------+------+-------------+

## 一个索引扫描？我们没有对MAX()进行优化

>EXPLAIN

SELECTCEIL(RAND()*(SELECTMAX(id)FROMrandom));

+----+-------------+-------+------+------+------------------------------+

|id|select_type|table|type|rows|Extra|

+----+-------------+-------+------+------+------------------------------+

|1|PRIMARY|NULL|NULL|NULL|Notablesused|

|2|SUBQUERY|NULL|NULL|NULL|Selecttablesoptimizedaway|

+----+-------------+-------+------+------+------------------------------+

## 一个简单的子查询给我们将性能找了回来。

OK，现在我们知道如何生成随机ID了，不过如何获取记录行？

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

>EXPLAIN

SELECTname

FROMrandom

WHEREid=(SELECTCEIL(RAND()*

(SELECTMAX(id)

FROMrandom));

+----+-------------+--------+------+---------------+------+---------+------+---------+------------------------------+

|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|

+----+-------------+--------+------+---------------+------+---------+------+---------+------------------------------+

|1|PRIMARY|random|ALL|NULL|NULL|NULL|NULL|1000000|Usingwhere|

|3|SUBQUERY|NULL|NULL|NULL|NULL|NULL|NULL|NULL|Selecttablesoptimizedaway|

+----+-------------+--------+------+---------------+------+---------+------+---------+------------------------------+

>showwarnings;

+-------+------+------------------------------------------+

|Level|Code|Message|

+-------+------+------------------------------------------+

|Note|1249|Select2wasreducedduringoptimization|

+-------+------+------------------------------------------+

哦，不！不要走这条路。虽然它很直观，但是也是最容易犯的错。理由是：在WHERE子句中的SELECT会针对外部SELECT取出的每一行执行一次。这可能会是0到4091行，看你的运气了。

我们必须用一种方法确保随机ID只被生成一次：

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

SELECTname

FROMrandomJOIN

(SELECTCEIL(RAND()*

(SELECTMAX(id)

FROMrandom))ASid

)ASr2

USING(id);

+----+-------------+------------+--------+------+------------------------------+

|id|select_type|table|type|rows|Extra|

+----+-------------+------------+--------+------+------------------------------+

|1|PRIMARY|<derived2>|system|1||

|1|PRIMARY|random|const|1||

|2|DERIVED|NULL|NULL|NULL|Notablesused|

|3|SUBQUERY|NULL|NULL|NULL|Selecttablesoptimizedaway|

+----+-------------+------------+--------+------+------------------------------+

内部的 SELECT 生成了一个常数临时(TEMPORARY)表并且联接(JOIN)只选择了一行。完美。

没有排序、没有应用程序介入，查询的大部分都被优化了。

在数字中加入空档

为了使最终的解决方案通用化，我们必须考虑空档的可能性，如当你删除(DELETE)了记录行。

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

SELECTname

FROMrandomASr1JOIN

(SELECT(RAND()*

(SELECTMAX(id)

FROMrandom))ASid)

ASr2

WHEREr1.id>=r2.id

ORDERBYr1.idASC

LIMIT1;

+----+-------------+------------+--------+------+------------------------------+

|id|select_type|table|type|rows|Extra|

+----+-------------+------------+--------+------+------------------------------+

|1|PRIMARY|<derived2>|system|1||

|1|PRIMARY|r1|range|689|Usingwhere|

|2|DERIVED|NULL|NULL|NULL|Notablesused|

|3|SUBQUERY|NULL|NULL|NULL|Selecttablesoptimizedaway|