时间戳

首页 > 解决方案 > 根据同一张表的结果优化选择表中的所有行？

mysql - 根据同一张表的结果优化选择表中的所有行？

问题描述

我将是第一个承认我不擅长 SQL 的人（我可能不应该将它视为滚动日志文件），但我想知道是否可以得到一些改进一些慢查询的建议。 .

我有一个包含 2M 行的大型 mysql 表，我在其中根据最新数据的子集进行两次全表查找。当我加载包含这些查询的页面时，我经常发现它们需要几秒钟才能完成，但里面的查询很快。

PMA 的（据说很糟糕）顾问几乎把整个厨房水槽扔给我，临时表，太多种类，没有索引的连接（我什至没有任何连接？），从固定位置读取，读取下一个位置，临时表写入到磁盘...最后一个特别让我怀疑这是否是配置问题，但我玩了所有的旋钮，甚至支付了似乎没有帮助的托管服务。

CREATE TABLE `archive` (
  `id` bigint UNSIGNED NOT NULL,
  `ip` varchar(15) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `service` enum('ssh','telnet','ftp','pop3','imap','rdp','vnc','sql','http','smb','smtp','dns','sip','ldap') CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `hostid` bigint UNSIGNED NOT NULL,
  `date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

ALTER TABLE `archive`
  ADD PRIMARY KEY (`id`),
  ADD KEY `service` (`service`),
  ADD KEY `date` (`date`),
  ADD KEY `ip` (`ip`),
  ADD KEY `date-ip` (`date`,`ip`),
  ADD KEY `date-service` (`date`,`service`),
  ADD KEY `ip-date` (`ip`,`date`),
  ADD KEY `ip-service` (`ip`,`service`),
  ADD KEY `service-date` (`service`,`date`),
  ADD KEY `service-ip` (`service`,`ip`);

添加索引肯定有帮助（即使它们是实际数据大小的 4 倍），但我有点不知所措，我可以进一步优化。最初我考虑在 php 中缓存子查询结果并将其用于主查询两次，但我认为一旦关闭子查询我就无法访问结果。我考虑过进行连接，但它们看起来像是用于 2 个或更多单独的表，但子查询来自同一个表，所以我不确定这是否也有效。查询应该根据我在过去 24 小时内是否有来自某个 ip 的数据来查找最活跃的 ip/服务...

SELECT service, COUNT(service) AS total FROM `archive`
WHERE ip IN
(SELECT DISTINCT ip FROM `archive` WHERE date > DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 24 HOUR))
GROUP BY service HAVING total > 1
ORDER BY total DESC, service ASC LIMIT 10

+----+--------------+-----------------+------------+-------+----------------------------------------------------------------------------+------------+---------+------------------------+-------+----------+---------------------------------+
| id | select_type  | table           | partitions | type  | possible_keys                                                              | key        | key_len | ref                    | rows  | filtered | Extra                           |
+----+--------------+-----------------+------------+-------+----------------------------------------------------------------------------+------------+---------+------------------------+-------+----------+---------------------------------+
|  1 | SIMPLE       | <subquery2>     | NULL       | ALL   | NULL                                                                       | NULL       | NULL    | NULL                   |  NULL |   100.00 | Using temporary; Using filesort |
|  1 | SIMPLE       | archive         | NULL       | ref   | service,ip,date-service,ip-date,ip-service,service-date,service-ip         | ip-service | 47      | <subquery2>.ip         |     5 |   100.00 | Using index                     |
|  2 | MATERIALIZED | archive         | NULL       | range | date,ip,date-ip,date-service,ip-date,ip-service                            | date-ip    | 5       | NULL                   | 44246 |   100.00 | Using where; Using index        |
+----+--------------+-----------------+------------+-------+----------------------------------------------------------------------------+------------+---------+------------------------+-------+----------+---------------------------------+


SELECT ip, COUNT(ip) AS total FROM `archive`
WHERE ip IN
(SELECT DISTINCT ip FROM `archive` WHERE date > DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 24 HOUR))
GROUP BY ip HAVING total > 1
ORDER BY total DESC, INET_ATON(ip) ASC LIMIT 10

+----+--------------+-----------------+------------+-------+---------------------------------------------------------------+---------+---------+------------------------+-------+----------+---------------------------------+
| id | select_type  | table           | partitions | type  | possible_keys                                                 | key     | key_len | ref                    | rows  | filtered | Extra                           |
+----+--------------+-----------------+------------+-------+---------------------------------------------------------------+---------+---------+------------------------+-------+----------+---------------------------------+
|  1 | SIMPLE       | <subquery2>     | NULL       | ALL   | NULL                                                          | NULL    | NULL    | NULL                   |  NULL |   100.00 | Using temporary; Using filesort |
|  1 | SIMPLE       | archive         | NULL       | ref   | ip,date-ip,ip-date,ip-service,service-ip                      | ip-date | 47      | <subquery2>.ip         |     5 |   100.00 | Using index                     |
|  2 | MATERIALIZED | archive         | NULL       | range | date,ip,date-ip,date-service,ip-date,ip-service               | date-ip | 5       | NULL                   | 44168 |   100.00 | Using where; Using index        |
+----+--------------+-----------------+------------+-------+---------------------------------------------------------------+---------+---------+------------------------+-------+----------+---------------------------------+

普通子查询：0.0351s

整个查询1：1.4270s

整个查询2：1.5601s

总页面加载：3.050 秒（总共 7 个查询）

我是否注定要在这张桌子上表现糟糕？

希望这里有足够的信息来了解正在发生的事情，但是如果有人可以提供帮助，我当然会很感激。我不介意在这个问题上投入更多的硬件，但是当一个 16gb 的 8c/16t 服务器无法处理 150mb 的数据时，我不确定会怎样。提前感谢您阅读我冗长的问题。

标签： mysqlquery-optimization

解决方案

您拥有正确的索引（以及许多其他索引），并且您的查询既符合您的规范，又接近最佳运行。您不太可能使这个速度更快：它需要一直追溯到表格的开头。

如果你可以改变你的规范，那么你只需要回顾有限的时间，比如一年，你就会得到一个很好的加速。

一些可能的小调整。

  • 使用您的列的latin1_bin排序规则。ip它使用 8 位字符并在不区分大小写的情况下对它们进行整理。这对于 IPv4 dotted-quad 地址（和 IPv6 地址）来说已经足够了。您将摆脱匹配和分组的一些开销。或者，更好的是，
  • 如果您知道除了 IPv4 地址之外什么都没有，请修改您的ip列以存储它们的二进制表示（即INET_ATON()每个 IPv4 的 - 生成值）。您可以将它们放入UNSIGNED INT32 位整数数据类型中，从而更快地进行查找、分组和排序。

可以重新设计收集这些数据的方式。例如，您可以安排每天每次服务最多收集一行。这会降低数据的时间序列分辨率，但也会使查询速度更快。像这样定义你的表：

CREATE TABLE archive2 (
  ip      VARCHAR(15) COLLATE latin1_bin NOT NULL,
  service ENUM ('ssh','telnet','ftp',
                'pop3','imap','rdp',
                'vnc','sql','http','smb',
                'smtp','dns','sip','ldap') COLLATE NOT NULL,
  `date`  DATE NOT NULL,
  `count` INT NOT NULL,
   hostid bigint UNSIGNED NOT NULL,
   PRIMARY KEY (`date`, ip, service)
) ENGINE=InnoDB;

然后，当您插入一行时，使用以下查询：

 INSERT INTO archive2 (`date`, ip, service, `count`, hostid)
               VALUES (CURDATE(), ?ip, ?service, 1, ?hostid)
ON DUPLICATE KEY UPDATE
              SET count = count + 1;

如果,和的行已经存在，这将自动增加您的count列。ipservicedate

然后您的第二个查询将如下所示：

SELECT ip, SUM(`count`) AS total
  FROM archive 
 WHERE ip IN  (
           SELECT ip FROM archive 
            WHERE `date` > CURDATE() - INTERVAL 1 DAY
            GROUP BY ip
            HAVING total > 1
        )
ORDER BY total DESC, INET_ATON(ip) ASC LIMIT 10;

主键的索引将满足这个查询。

推荐阅读