优化大型数据库中MySQL查询的速度

Question

我正在尝试优化一个SQL查询，希望能提高其执行速度。

我有以下两个表：

CREATE TABLE IF NOT EXISTS `data` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `entry` varchar(255) NOT NULL,
  `numDB` int(11) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `entry` (`entry`),
  UNIQUE KEY `entry_numDB` (`entry`,`numDB`),
  UNIQUE KEY `entry_numDB_id` (`id`,`entry`,`numDB`),
  KEY `numDB` (`numDB`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `details` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `dataID` bigint(20) NOT NULL,
  `dbID` int(11) NOT NULL,
  <removed - unimportant>
  PRIMARY KEY (`id`),
  KEY `dataID` (`dataID`),
  KEY `dbID` (`dbID`),
  KEY `dataID_dbID` (`dataID`,`dbID`),
  <removed - unimportant>
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

表格数据中存储了一个条目（例如，“abc”），其格式为“id = 1; entry = abc, numDB = 2”，并且在详情表格中至少有两个条目：“id = 1，dataID = 1，dbID = 4”和“id = 2，dataID = 1，dbID = 17”。然而，相同的dataID和dbID可能会多次出现，例如“id = 3，dataID = 1，dbID = 17”、“id = 4，dataID = 1，dbID = 17”。

表格数据中的总条目数：45.245.438 表格详细信息中的总条目数：126.608.661

现在，我想从表格数据中获取前50个没有条件dbID = 4的条目，并按照data.numDB进行排序。生成的查询语句如下：

SELECT DISTINCT(data.entry), data.numDB FROM blacklists.data data INNER JOIN blacklists.details details ON details.dbID NOT IN (4) AND data.id = details.dataID ORDER BY data.numDB DESC LIMIT 50

处理时间至少需要10分钟（我在10分钟后停止了它）。

这是EXPLAIN的输出结果：

EXPLAIN SELECT DISTINCT(data.entry), data.numDB FROM blacklists.data data INNER JOIN blacklists.details details ON details.dbID NOT IN (4) AND data.id = details.dataID ORDER BY data.numDB DESC LIMIT 50

id  select_type  table    type   possible_keys            key         key_len  ref                rows      Extra
1   SIMPLE       data     index  PRIMARY,entry_numDB_id   entry_numDB 261      NULL               45166874  Using index; Using temporary; Using filesort
1   SIMPLE       details  ref    dataID,dbID,dataID_dbID  dataID      8        blacklists.data.id  1        Using where; Distinct

不使用DISTINCT（或GROUP BY）会导致条目重复多次。

有没有办法改进这个查询？我已经阅读了许多帮助页面和其他问题及其答案，但无法找到这些表的解决方案。

- Mkay

---

在这种情况下，您可以使用临时表。并且可以使用engine = memory（内存更快），mysql在连接关闭后会删除临时表。因此，请尝试使用它。还要在my.cnf中定义堆表大小参数。 - Masood Alam

你说你“想要从表数据中获取前50条不满足条件dbID = 4的记录”-但是，查询似乎在询问“从表数据中获取前50个详情不为'dbID = 4'的记录”，哪一个是正确的？ - Doug Kress

我刚刚在内存中使用了临时表进行测试，但是速度仍然非常慢。我想获取所有dbID != 4的详细信息。 - Mkay

Answer 1

加入细节在这里有点让你困扰。由于dbID！= 4并没有真正过滤掉太多内容，因此它仍然需要扫描大部分“data”表。这是一个子查询可能会帮助您的领域。与其查询所有“data”表中实际上是最后50行+任何与dbID == 4的表“details”连接的行，不如手动限制为最后几千行或任何近似值，这些行不会具有dbID == 4

我认为通过像这样编写查询，您将看到很大的性能提升：

SELECT  DISTINCT(data.entry), 
        data.numDB 
FROM
    (
        SELECT x.entry, x.numDB, x.numDB
        FROM blacklists.data x
        ORDER BY x.numDB DESC LIMIT 2000
    ) data
INNER JOIN blacklists.details details 
    ON details.dbID NOT IN (4) 
    AND data.id = details.dataID 
ORDER BY data.numDB DESC LIMIT 50

根据您的需求调整子查询中的限制。较小的值将使此查询运行更快，但可能无法获得您想要的50条记录，较大的值将运行更慢，但给您更好的机会获得您想要的50条记录。

- invertedSpear

Answer 2

我想知道您是否对以下查询有建议：

详细表与上述相同，我还有：

CREATE TABLE IF NOT EXISTS `ips` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `dataID` bigint(20) NOT NULL,
  `ip` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `dataID_ip` (`dataID`,`ip`),
  KEY `dataID` (`dataID`),
  KEY `ip` (`ip`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

我现在想从该表中获取所有不在dbID = 4中的IP，按它们的count(ip)排序：

我的做法是：

SELECT ip.id, ip.ip, count(ip.ip)
FROM
    (
        SELECT x.id, x.ip, x.dataID
        FROM ips x
        GROUP BY x.ip ORDER BY COUNT(x.ip) DESC LIMIT 1000
    ) ip
INNER JOIN details details 
    ON details.dbID NOT IN (4) 
    AND ip.dataID = details.dataID 
GROUP BY ip.ip ORDER BY COUNT(ip.ip) DESC LIMIT 50

然而内部SELECT需要进行全表扫描。

1 PRIMARY <derived2> ALL       NULL    NULL    NULL    NULL    1000     Using temporary; Using filesort
1 PRIMARY details    ref       dataID,dbID,dataID_dbID,dataID_active,dbID_active_...     dataID     8     ip.dataID     1     Using where
2 DERIVED x          index     NULL    ip     4     NULL    8960260     Using temporary; Using filesort

有没有办法进一步优化这个查询？

- Mkay

---

我认为，但我不确定并且没有你的数据进行实验，你正在通过计算特定列来防止任何索引，除非ips.ip有能力为空（根据你的DDL它不是），那么你应该使用count(*)让MySQL决定如何计数，并更好地选择使用哪个索引。同时将你的count(*)放在子查询的select子句中并给它一个别名。MySQL将允许你按照该别名排序和分组，以及让父查询语句仅提取该值，而不是运行自己的单独计数。 - invertedSpear

Answer 3

首先，我会将dbID NOT IN (4)更改为dbID <> 4。MySQL可能会正确地优化它，但我想确保。

其次，我会考虑对数据进行一些去规范化的处理，将一个字段放置在data中，表示所需条件，从而允许您执行单表查询（速度要快得多）。该字段可以在应用程序中或使用触发器维护。

- Doug Kress

---

我故意使用了“not in”，因为有时候有更多的dbIDs我想要排除 :) - Mkay