php - MySQL超时问题

Question

我在 Linux 服务器上与 PHP 和 MySql 相关的锻炼中遇到了严重的问题，而当我在本地主机中使用相同的数据库运行相同的代码时，它工作正常。

我的数据库表中有近 30,000 条记录，mysql 是:

SELECT * FROM tbl_movies where id not in (select movie_id from tbl_usermovieque where user_id='3' union 
                                      select movie_id from tbl_user_movie_response where user_id='3' union 
                                      select movie_id from tbl_user_movie_fav where user_id='3') and id < 220 order by rand() limit 0,20

在我的本地主机上花费了 0.0010 秒，在我们的 Linux 服务器上则花费了无限的时间。我找不到原因。

谢谢卡迈勒

Answer 1

您能确认这会返回相同的结果吗？这样应该会更快。 Union 有时很有用，但并未真正优化。

SELECT * FROM tbl_movies where id not in (
    select distinct movie_id
    from tbl_movies m
    inner join tbl_usermovieque um ON um.movie_id = m.movie_id = m.movie_id
    inner join tbl_user_movie_response umr ON umr.movie_id = m.movie_id = m.movie_id
    inner join tbl_user_movie_fav umf ON umf.movie_id = m.movie_id = m.movie_id
    where um.user_id = 3 or umr.user_id = 3 or umf.user_id = 3
) and id < 220 order by rand() limit 0,20;

PS:我假设您有 oser_id 和 id_movie 索引

编辑:您的问题可能来自rand()

MySQL order by optimization在页面中查找 RAND() :在评论中，有一些性能测试 => 单独使用 rand() 似乎是一个糟糕的解决方案

Performance

Now let's see what happends to our performance. We have 3 different queries for solving our problems.

• Q1. ORDER BY RAND()
• Q2. RAND() * MAX(ID)
• Q3. RAND() * MAX(ID) + ORDER BY ID

Q1 is expected to cost N * log2(N), Q2 and Q3 are nearly constant.

The get real values we filled the table with N rows ( one thousand to one million) and executed each query 1000 times.

Rows ||100 ||1.000 ||10.000 ||100.000 ||1.000.000

Q1||0:00.718s||0:02.092s||0:18.684s||2:59.081s||58:20.000s Q2||0:00.519s||0:00.607s||0:00.614s||0:00.628s||0:00.637s Q3||0:00.570s||0:00.607s||0:00.614s|0:00.628s ||0:00.637s

As you can see the plain ORDER BY RAND() is already behind the optimized query at only 100 rows in the table.