高效查询

使用 EXPLAIN 查看查询执行计划。

对于第一个查询，MySQL 无法有效利用索引范围扫描操作。必须为表中的每一行计算 WHERE 子句中的表达式。当我们与裸列进行比较时，我们会获得更好的性能。在字面上进行操作...将这些值转换为要与之比较的列的数据类型。

WHERE e.date BETWEEN expr1 AND expr2 

对于expr1，您需要一个将$min值转换为日期时间的表达式。请注意时区转换。我认为这可能会满足您对 expr1 的需求：

 FROM_UNIXTIME( $min /1000)

像这样：

WHERE e.date BETWEEN FROM_UNIXTIME( $min /1000) AND FROM_UNIXTIME( $max /1000)

然后我们应该看到 MySQL 能够有效地利用带有日期前导列的索引。解释输出应显示访问类型的range。

如果返回的列数很小，请考虑使用覆盖索引。然后 EXPLAIN 将显示"使用索引"，这意味着查询可以完全从索引中满足，而无需查找基础表中的页面。

其次，避免在循环中多次运行查询。运行返回单个结果集的单个查询通常更有效，因为将 SQL 发送到数据库的开销，该数据库解析 SQL 文本，用于有效语法（关键字在正确的位置），有效的语义（标识符引用有效对象），考虑可能的访问路径并确定哪个成本最低， 然后执行查询计划，获取元数据锁，生成结果集，将其返回到客户端，然后进行清理。对于单个语句来说，这并不明显，但是当您开始在紧密循环中运行大量语句时，它开始加起来。再加上一个低效的查询，它开始变得非常明显。

如果exam中的examID列是唯一的并且不为空（或者它是exam的主键，那么看起来您可以使用单个查询，如下所示：

SELECT UNIX_TIMESTAMP(e.date)*1000 AS `date_ts`
     , e.examID                    AS `examID`
     , SUM(ct.ctdivol_mGy)         AS `SUM(ctdivol_mGy)`
     , COUNT(ct.ctdivol_mGy)       AS `count(ctdivol_mGy)`
  FROM exam e
  LEFT
  JOIN ct
    ON ct.examid = e.examID
   AND ct.ctdivol_mGy > 0
 WHERE e.modality = 'CT'
   AND e.date >= FROM_UNIXTIME(  $min  /1000)
   AND e.date <= FROM_UNIXTIME(  $max  /1000)
 GROUP
    BY e.modality
     , e.date
     , e.examID
 ORDER
    BY e.modality
     , e.date
     , e.examID

为了获得最佳性能，您需要覆盖索引：

  ... ON exam (modality, date, examID)
  ... ON ct (examID, ctdivol_mGy)

我们希望看到EXPLAIN输出;我们希望MySQL可以利用考试索引来执行GROUP BY（并避免"使用文件排序"操作），并且还可以利用索引上的ref操作来ct。

重申...该查询要求examID是exam表的主键（或至少保证是唯一且非空的）。否则，其结果可能与原始代码不同。如果没有这种保证，我们可以在SELECT列表中使用内联视图或子查询。但就性能而言，我们不想在没有充分理由的情况下去那里。

这只是一些一般的想法，而不是硬性而快速的"这会更快"。

可以通过exam_id将第一个表上的联接写入子查询表：

$query = "SELECT (unix_timestamp(date)*1000) as time_calculation, ed.examID, inner_ct.inner_sum, inner_ct.inner_count "
" FROM exam ed,"
. " ( SELECT SUM(ctdivol_mGy) as inner_sum, count(ctdivol_mGy) as inner_count, examID"
. "   FROM ct"
. "   WHERE  ctdivol_mGy>0 ) inner_ct"
. " WHERE ed.modality = 'CT' AND time_calculation between"
. " '$min' and '$max'"
. " AND ed.examId = inner_ct.examID";

该( SELECT . . .) inner_ct将创建一个可从中加入的内存表中。如果要在联接中选择组合数据（在本例中为总和），则非常有用。

相反，您可以使用以下语法：

$query = "SELECT (unix_timestamp(date)*1000) as time_calculation, ed.examID, inner_ct.inner_sum, inner_ct.inner_count "
" FROM exam ed,"
. " LEFT JOIN ( SELECT SUM(ctdivol_mGy) as inner_sum, count(ctdivol_mGy) as inner_count, examID"
. "   FROM ct"
. "   WHERE  ctdivol_mGy>0 ) inner_ct"
. " ON ed.examID = inner_ct.examID"
. " WHERE ed.modality = 'CT' AND time_calculation between"
. " '$min' and '$max'";

您没有在问题中提供样本数据，因此我们诉诸假设来尝试回答。如果ct中的许多行只有一个exam行 - 但可以存在根本没有 ct 行的考试行 - 则此单个查询应提供所需的结果。

SELECT
      exam.examID
    , (unix_timestamp(exam.date) * 1000
    , SUM(ct.ctdivol_mGy)
    , COUNT(ct.ctdivol_mGy)
FROM exam
LEFT OUTER JOIN ct on exam.examID = ct.examID AND ct.ctdivol_mGy > 0
WHERE exam.modality = 'CT'
      AND exam.date >= @min AND exam.date < @max
GROUP BY
      exam.examID
    , (unix_timestamp(exam.date) * 1000)
      ;

注意 我不是在尝试 PHP 代码，只是专注于 SQL。我使用了 @min 和 @max 来表示 where 子句中要求的 2 个日期。这些应该与列具有相同的数据类型exam.date因此在添加到查询字符串之前在 PHP 中进行这些计算。

我想计算特定区间内ctdivol的平均值 日期。

如果您尝试返回单个数字，那么这应该会有所帮助：

SELECT
      AVG(ct.ctdivol_mGy)
FROM exam
INNER JOIN ct on exam.examID = ct.examID AND ct.ctdivol_mGy > 0
WHERE exam.modality = 'CT'
      AND exam.date >= @min AND exam.date < @max
      ;

请注意，对于此变体，我们可能不需要左连接（但同样由于缺乏示例数据和预期结果，这是一个假设）。