免费提问

PHP+Mysql随机高效查询

144
作者 admin
2017-06-01 14:58 字数 10522 阅读 1164评论 0

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

EXPLAIN SELECT *
FROM money_logs
ORDER BY RAND( )
LIMIT 1

idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEtableALLNULLNULLNULLNULL173784Using temporary; Using filesort

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

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


将工作移入应用程序

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

1

2

3

SELECT MAX(id) FROM random;

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

SELECT name FROM random WHERE id = <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

> SELECT RAND() * MAX(id) FROM random;

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

| RAND() * MAX(id) |

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

|  689.37582507297 |

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

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

 

> SELECT CEIL(RAND() * MAX(id)) FROM random;

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

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

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

|                    1000000  |

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

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

 

> EXPLAIN

   SELECT CEIL(RAND() * MAX(id)) FROM random;

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

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

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

|  1 | SIMPLE      | random  | index | 1000000  | Using index |

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

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

 

> EXPLAIN

   SELECT CEIL(RAND() * (SELECT MAX(id) FROM random));

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

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

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

|  1 | PRIMARY     | NULL  | NULL | NULL | No tables used               |

|  2 | SUBQUERY    | NULL  | NULL | NULL | Select tables optimized away |

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

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

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

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

> EXPLAIN

SELECT name

  FROM random

WHERE id = (SELECT CEIL(RAND() *

                         (SELECT MAX(id)

                            FROM random));

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

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

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

|  1 | PRIMARY     | random | ALL  | NULL          | NULL | NULL    | NULL | 1000000 | Using where                  |

|  3 | SUBQUERY    | NULL   | NULL | NULL          | NULL | NULL    | NULL |    NULL | Select tables optimized away |

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

> show warnings;

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

| Level | Code | Message                                  |

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

| Note  | 1249 | Select 2 was reduced during optimization |

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

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


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

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

SELECT name

  FROM random JOIN

       (SELECT CEIL(RAND() *

                    (SELECT MAX(id)

                       FROM random)) AS id

        ) AS r2

       USING (id);

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

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

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

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

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

|  2 | DERIVED     | NULL       | NULL   | NULL | No tables used               |

|  3 | SUBQUERY    | NULL       | NULL   | NULL | Select tables optimized away |

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

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

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


在数字中加入空档

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

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

SELECT name

  FROM random AS r1 JOIN

       (SELECT (RAND() *

                     (SELECT MAX(id)

                        FROM random)) AS id)

        AS r2

WHERE r1.id >= r2.id

ORDER BY r1.id ASC

LIMIT 1;

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

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

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

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

|  1 | PRIMARY     | r1         | range  |  689 | Using where                  |

|  2 | DERIVED     | NULL       | NULL   | NULL | No tables used               |

|  3 | SUBQUERY    | NULL       | NULL   | NULL | Select tables optimized away |

+----+----

网站简介 | 竞价技巧 | 网站建设 | 原创作品 | 联系我们 |

鄂ICP备17013851号-4 |经营许可证:鄂B2-20160818 |互联网药品信息服务资格证: 粤20130124|鄂公网安备:42010601000001|版权登记号:2016SR017186