mysql - 摘要加入查询花费的时间太长

Question

我正在尝试获取每个“telephone_number”的最大记录，其中 process_status='0'，为此我正在使用以下查询。

SELECT ID, CUSTID, telephone_number, TOTAL_USAGE, ACCOUNT_STATUS
FROM SPRINTABLE_DATA t
JOIN (SELECT MAX( id ) AS maxid FROM SPRINTABLE_DATA GROUP BY telephone_number)dt
ON t.id = dt.maxid WHERE process_status = '0'
AND RESET_FLAG = '0'
ORDER BY id DESC limit 0,700

上面的查询给了我想要的结果，但问题是这太慢了..

我的表有大约 2000 万行，此查询有时需要大约 15-20 分钟。

可以做些什么来改善这一点？

这是结构:。

CREATE TABLE `SPRINTABLE_DATA` (
 `ID` bigint(11) NOT NULL AUTO_INCREMENT,
 `CUSTID` int(11) DEFAULT NULL,
 `telephone_number` varchar(20) DEFAULT NULL,
 `TOTAL_USAGE` int(11) DEFAULT NULL,
 `PROCESS_STATUS` tinyint(4) DEFAULT '0',
 `RESET_FLAG` tinyint(4) DEFAULT '0',
 `RESET_REASON` varchar(10) DEFAULT NULL,
 `PLAN_ID` varchar(20) DEFAULT NULL,
 `ACCOUNT_STATUS` varchar(30) DEFAULT NULL,
 PRIMARY KEY (`ID`),
 KEY `telephone_number` (`telephone_number`),
 KEY `CALL_CUST` (`CALL_START_TIME`,`CUSTID`),
 KEY `telephone_number1` (`telephone_number `,`PROCESS_STATUS`,`SOC_ADDED`),
 KEY `CURRENT_USAGE` (`CURRENT_USAGE`),
 KEY `TOTAL_USAGE` (`TOTAL_USAGE`)
) ENGINE=InnoDB AUTO_INCREMENT=36392272 DEFAULT CHARSET=latin1

Answer 1

您似乎在查找最近调用的 700 个号码。 (如果这不正确，请编辑您的问题。

您的查询遵循一个很好的做法，即在您的子查询中检索每个项目(在您的案例中是电话号码)的最新日志行，如下所示。

   SELECT MAX( id ) AS id 
    FROM SPRINTABLE_DATA  
GROUP BY telephone_number

要优化此子查询的性能，您需要在两个字段上使用复合索引:(telephone_number, id)，按此顺序排列。如果您没有该索引，请添加它。这是为了允许进行所谓的松散索引扫描，这是一种非常有效的满足查询的方式。

其次，您正在寻找(我推测)一小部分数据。据推测，您有 700 多个不同的 telephone_number 值。这意味着您使用 ORDER BY 对大量数据进行排序只是为了使用 LIMIT 丢弃它。因此，让我们进行延迟连接，对最少数量的列进行排序，然后检索您需要的所有信息。

下面是获取你需要的700行的ID值的方法

SELECT q.ID   /* get our 700 records */
  FROM SPRINTABLE_DATA q
  JOIN (
          SELECT MAX( id ) AS id 
           FROM SPRINTABLE_DATA  
       GROUP BY telephone_number
       ) r ON q.id = r.id
  WHERE q.process_status = '0'
    AND q.RESET_FLAG = '0'
  ORDER BY q.ID DESC
  LIMIT 0,700

这将提取 700 个 ID 号码。您需要对索引进行一些试验，以找出最有助于优化它的方法。

上的索引可能

   process_status, RESET_FLAG, id

会有所帮助。更改索引中列的顺序也可能会有所帮助，如下所示:

   id, process_status, RESET_FLAG

两个都试试。

最后，我们将使用它作为子查询来执行连接(所谓的延迟连接)以获取实际的详细记录。这种技术消除了对所有数据进行排序的需要。

SELECT t.ID, t.CUSTID, t.telephone_number, t.TOTAL_USAGE, t.ACCOUNT_STATUS
  FROM SPRINTABLE_DATA t
  JOIN (
          SELECT q.ID  /* get our 700 records */
            FROM SPRINTABLE_DATA q
            JOIN (
                    SELECT MAX( id ) AS id 
                     FROM SPRINTABLE_DATA  
                 GROUP BY telephone_number
                 ) r ON q.id = r.id
            WHERE q.process_status = '0'
              AND q.RESET_FLAG = '0'
            ORDER BY q.ID DESC
            LIMIT 0,700
       ) s ON t.ID = s.ID
  ORDER BY t.ID DESC

这将产生相同的结果，但会更快。

现在，最后，如果可以从满足您条件的 700 个号码中选择最新的电话，您可以大大简化此查询。不过，这会以一种微妙的方式改变您的结果集。在这种情况下，您的调用选择子查询将如下所示:

                    SELECT MAX( id ) AS id  /* 700 matching numbers */
                     FROM SPRINTABLE_DATA  
                    WHERE process_status = '0'
                      AND reset_flag = '0'
                 GROUP BY telephone_number
                 ORDER BY ID desc
                    LIMIT 0,700

在

上有复合覆盖索引

   reset_flag, process_status, telephone_number, ID

这个查询会很快。在这种情况下，您的最终查询将是

SELECT t.ID, t.CUSTID, t.telephone_number, t.TOTAL_USAGE, t.ACCOUNT_STATUS
  FROM SPRINTABLE_DATA t
  JOIN (
                    SELECT MAX( id ) AS id  /* 700 matching numbers */
                     FROM SPRINTABLE_DATA  
                    WHERE process_status = '0'
                      AND reset_flag = '0'
                 GROUP BY telephone_number
                 ORDER BY ID desc
                    LIMIT 0,700
       ) s ON t.ID = s.ID
  ORDER BY t.ID DESC