php - 优化PHP/mysql算法

Question

我必须为我的应用程序做一些统计，所以我需要一个性能尽可能最好的算法。我有几个问题。

我在mysql数据库中有这样一个数据结构:

user_id    group_id     date
1          5            2012-11-20
1          2            2012-11-01
1          4            2012-11-01
1          3            2012-10-15
1          9            2013-01-18
...

所以我需要在特定日期找到某个用户的组。例如，用户 1 在日期 2012-11-15(2012 年 11 月 15 日)的组应该返回最近的组，即 2 和 4(同时有多个组)在日期 2012-11-01(最近和更小的日期)。

通常，我可以执行 Select where date <= chosen date order by date desc 等...但这不是重点，因为如果我有 1000 个用户，则需要 1000 个请求才能获得所有结果。

所以这里有一些问题:

1. 我已经使用php的方法循环遍历数组来避免大量的mysql请求，但仍然不好，因为数组大小可能超过10000。使用 foreach(或 for？)的成本非常高。所以我的问题是，如果给定一个按日期(desc 或 asc)排序的数组，找到包含小于(或大于)给定日期的日期的元素的最接近索引的最快方法是什么？除了使用 for 或 foreach 循环遍历每个元素。
2. 如果第一个问题没有解决方案，那么对于此类问题，您建议使用哪种数据结构。

注意:日期是mysql格式，存入数组时没有转换成时间戳

编辑:这是一个 sql fiddle http://sqlfiddle.com/#!2/dc28d/1 对于 dos_id = 6, t="2012-11-01" 它应该只在日期 "2010-12-10 13:16 返回 2 和 5: 58"

Answer 1

不确定为什么要在 php 中执行此操作。这里有一些 SQL 使用连接代替为给定日期的所有用户获取最近的组。确保您有关于日期和用户 ID 的索引。

SELECT *
FROM test t1
LEFT JOIN test t2
ON t1.userid = t2.userid AND t2.thedate <= '2012-11-15' AND t2.thedate > t1.thedate
WHERE t1.thedate <= '2012-11-15' AND t2.userid IS NULL;

SQLfiddle

或者使用你的 SQLFiddle

SELECT t1.*
FROM dossier_dans_groupe t1
LEFT JOIN dossier_dans_groupe t2
ON t1.dos_id = t2.dos_id AND t2.updated_at <= '2012-11-01' 
   AND t2.updated_at > t1.updated_at
WHERE t1.updated_at <= '2012-11-01' AND t2.dos_id IS NULL;