这是我的第一个问题，因为我曾经遇到过的每个问题都在这里有了答案。请原谅糟糕的格式。

查询本身在 1 毫秒内运行，这很棒。它从大约 300 万个条目中产生大约 600,000 个结果，而数据库每秒插入大约 10 个。我知道这对于数据库来说不是很多，所以我认为负载不是问题。我还有其他大型查询可以很好地插入文件中。具体来说，当添加“SELECT * INTO OUTFILE”时，运行时间约为 11 小时。这对于运行查询来说太长了，我不知道为什么。

表:container_table

- Primary Key: containerID(bigint), mapID(int), cavityID(int)

- Index: timestamp(datetime)

表:cont_meas_table

- Primary Key: containerID(bigint), box(int), probe(int), inspectionID(int), measurementID(int)

表:cavity_map

- Primary Key: mapID(int), gob(char), section(int), cavity(int)

查询:

(SELECT  'containerID','timestamp','mapID','lineID','fp','fpSequence','pocket','cavityID', 'location','inspResult',
     'otgMinThickMeasValuePrb2_1','otgMaxThickMeasValuePrb2_1','RatioPrb2_1','otgOORMeasValuePrb2_1',
     'otgMinThickMeasValuePrb2_2','otgMaxThickMeasValuePrb2_2','RatioPrb2_2','otgOORMeasValuePrb2_2',
     'otgMinThickMeasValuePrb2_3','otgMaxThickMeasValuePrb2_3','RatioPrb2_3')
UNION
(SELECT * INTO OUTFILE 'testcsv.csv'
   FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
   LINES TERMINATED BY '\n'
 FROM
(SELECT          containerID, timestamp, groupmeas.mapID, lineID, fp, fpSequence, pocket,     cavityID, CONCAT(MIN(section), MIN(gob)) AS location,
             inspResult, otgMinThickMeasValuePrb2_1, otgMaxThickMeasValuePrb2_1, 
             (COALESCE(otgMaxThickMeasValuePrb2_1/NULLIF(CAST(otgMinThickMeasValuePrb2_1 AS DECIMAL(10,5)), 0), 0)) AS RatioPrb2_1,
             otgOORMeasValuePrb2_1, otgMinThickMeasValuePrb2_2, otgMaxThickMeasValuePrb2_2,
             (COALESCE(otgMaxThickMeasValuePrb2_2/NULLIF(CAST(otgMinThickMeasValuePrb2_2 AS DECIMAL(10,5)), 0), 0)) AS RatioPrb2_2,
             otgOORMeasValuePrb2_2, otgMinThickMeasValuePrb2_3, otgMaxThickMeasValuePrb2_3,
             (COALESCE(otgMaxThickMeasValuePrb2_3/NULLIF(CAST(otgMinThickMeasValuePrb2_3 AS DECIMAL(10,5)), 0), 0)) AS RatioPrb2_3
FROM 
(SELECT   dbad.container_table.containerID, dbad.container_table.timestamp, dbad.container_table.mapID, dbad.container_table.lineID, dbad.container_table.fp, 
      dbad.container_table.fpSequence, dbad.container_table.pocket, dbad.container_table.cavityID, dbad.container_table.inspResult, 
      CASE WHEN aggMeas.otgMinThickMeasValuePrb2_1 IS NULL
         THEN - 1 ELSE aggMeas.otgMinThickMeasValuePrb2_1 END AS otgMinThickMeasValuePrb2_1, 
      CASE WHEN aggMeas.otgMaxThickMeasValuePrb2_1 IS NULL 
         THEN - 1 ELSE aggMeas.otgMaxThickMeasValuePrb2_1 END AS otgMaxThickMeasValuePrb2_1, 
      CASE WHEN aggMeas.otgOORMeasValuePrb2_1 IS NULL 
         THEN - 1 ELSE aggMeas.otgOORMeasValuePrb2_1 END AS otgOORMeasValuePrb2_1, 
      CASE WHEN aggMeas.otgMinThickMeasValuePrb2_2 IS NULL 
         THEN - 1 ELSE aggMeas.otgMinThickMeasValuePrb2_2 END AS otgMinThickMeasValuePrb2_2, 
      CASE WHEN aggMeas.otgMaxThickMeasValuePrb2_2 IS NULL 
         THEN - 1 ELSE aggMeas.otgMaxThickMeasValuePrb2_2 END AS otgMaxThickMeasValuePrb2_2, 
      CASE WHEN aggMeas.otgOORMeasValuePrb2_2 IS NULL 
         THEN - 1 ELSE aggMeas.otgOORMeasValuePrb2_2 END AS otgOORMeasValuePrb2_2, 
      CASE WHEN aggMeas.otgMinThickMeasValuePrb2_3 IS NULL 
         THEN - 1 ELSE aggMeas.otgMinThickMeasValuePrb2_3 END AS otgMinThickMeasValuePrb2_3, 
      CASE WHEN aggMeas.otgMaxThickMeasValuePrb2_3 IS NULL 
         THEN - 1 ELSE aggMeas.otgMaxThickMeasValuePrb2_3 END AS otgMaxThickMeasValuePrb2_3, 
      CASE WHEN aggMeas.otgOORMeasValuePrb2_3 IS NULL 
         THEN - 1 ELSE aggMeas.otgOORMeasValuePrb2_3 END AS otgOORMeasValuePrb2_3
 FROM   dbad.container_table 
      LEFT OUTER JOIN
      (SELECT     containerID, 
       COALESCE(MIN(CASE WHEN (meas.inspectionID = 1) AND (meas.measurementID = 0) AND (meas.probe = 0) THEN meas.value END), - 1) AS otgMinThickMeasValuePrb2_1, 
       COALESCE(MIN(CASE WHEN (meas.inspectionID = 1) AND (meas.measurementID = 1) AND (meas.probe = 0) THEN meas.value END), - 1) AS otgMaxThickMeasValuePrb2_1, 
       COALESCE(MIN(CASE WHEN (meas.inspectionID = 1) AND (meas.measurementID = 2) AND (meas.probe = 0) THEN meas.value END), - 1) AS otgOORMeasValuePrb2_1, 
       COALESCE(MIN(CASE WHEN (meas.inspectionID = 1) AND (meas.measurementID = 0) AND (meas.probe = 1) THEN meas.value END), - 1) AS otgMinThickMeasValuePrb2_2, 
       COALESCE(MIN(CASE WHEN (meas.inspectionID = 1) AND (meas.measurementID = 1) AND (meas.probe = 1) THEN meas.value END), - 1) AS otgMaxThickMeasValuePrb2_2, 
       COALESCE(MIN(CASE WHEN (meas.inspectionID = 1) AND (meas.measurementID = 2) AND (meas.probe = 1) THEN meas.value END), - 1) AS otgOORMeasValuePrb2_2, 
       COALESCE(MIN(CASE WHEN (meas.inspectionID = 1) AND (meas.measurementID = 0) AND (meas.probe = 2) THEN meas.value END), - 1) AS otgMinThickMeasValuePrb2_3, 
       COALESCE(MIN(CASE WHEN (meas.inspectionID = 1) AND (meas.measurementID = 1) AND (meas.probe = 2) THEN meas.value END), - 1) AS otgMaxThickMeasValuePrb2_3, 
       COALESCE(MIN(CASE WHEN (meas.inspectionID = 1) AND (meas.measurementID = 2) AND (meas.probe = 2) THEN meas.value END), - 1) AS otgOORMeasValuePrb2_3
       FROM  (SELECT  containerID, inspectionID, measurementID, probe, value, threshold, calibration FROM  dbad.cont_meas_table AS a) AS meas
       GROUP BY containerID) AS aggMeas 
    ON dbad.container_table.containerID = aggMeas.containerID) AS groupmeas
INNER JOIN
dbad.cavity_map
  ON groupmeas.mapID=dbad.cavity_map.mapID  AND
  groupmeas.cavityID=dbad.cavity_map.cavity
  WHERE timestamp LIKE '2014-08-29%'
    AND otgMinThickMeasValuePrb2_1 BETWEEN 1 AND 499
    AND otgMinThickMeasValuePrb2_2 BETWEEN 1 AND 499
    AND otgMinThickMeasValuePrb2_3 BETWEEN 1 AND 499
    AND otgMaxThickMeasValuePrb2_1 BETWEEN 1 AND 499
    AND otgMaxThickMeasValuePrb2_2 BETWEEN 1 AND 499
    AND otgMaxThickMeasValuePrb2_3 BETWEEN 1 AND 499
GROUP BY containerID) AS outside)

我已经摆脱了任何 COUNT()或 DISTINCT并删除了我的 WHERE timestamp LIKE '2014-08-29%' 中的前导“%”这样就可以使用时间戳的索引。不幸的是，这没有帮助。

编辑: 添加后

WHERE timestamp >= '2014-08-29' AND timestamp < '2014-08-29' + INTERVAL 1 DAY

查询实际上需要更长的时间。我知道情况不应该是这样，所以我一定是在这个查询中做错了什么。

最佳答案

有一件事跳起来打我的脸:

WHERE timestamp LIKE '2014-08-29%'  /* slow! */

这会破坏 timestamp 列上索引的使用，因为它隐式地将 timestamp 转换为字符串。

试试用这个代替:

WHERE timestamp >= '2014-08-29'
  AND timestamp <  '2014-08-29' + INTERVAL 1 DAY

这将允许查询在 timestamp 上使用索引范围扫描，这可能会有很大帮助。它之所以有效，是因为它将常量日期转换为与 timestamp 相同的数据类型，而不是相反。

索引的要点是避免所谓的全表扫描，在这种情况下，MySQL 服务器必须遍历表的每一行以寻找匹配数据。省略 WHERE 子句还会使服务器查看表的每一行。

关于插入文件时，MySQL 查询速度变慢，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/25651173/