mysql - MySQL：因为索引/FKEY 导致 SELECT 慢？

问题描述

亲爱的 StackOverflow 会员

这是我的第一篇文章，所以请多多关照:-)

我有一个奇怪的 SQL 行为，我无法解释，也找不到任何解释它的资源。

我建立了一个网络蜜罐，它记录所有访问和攻击并将其显示在统计页面上。但是随着数据的增加，统计页面的生成速度越来越慢。

我把它缩小到一些需要很长时间的选择语句。

“问题”似乎是特定列的索引。

*当然，真正的问题是我缺乏知识:-)

数据库： mysql

数据库模式

事件表（删除不相关的列）：

事件表大小：30MB

事件表记录：335k

  CREATE TABLE `event` (

   `EventID` int(11) NOT NULL,

   `EventTime` datetime NOT NULL DEFAULT current_timestamp(),

   `WEBURL` varchar(50) COLLATE utf8_bin DEFAULT NULL,

   `IP` varchar(15) COLLATE utf8_bin NOT NULL,

   `AttackID` int(11) NOT NULL

 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

 ALTER TABLE `event`
   ADD PRIMARY KEY (`EventID`),
   ADD KEY `AttackID` (`AttackID`);
 ALTER TABLE `event`
   ADD CONSTRAINT `event_ibfk_1` FOREIGN KEY (`AttackID`) REFERENCES `attack` (`AttackID`);

攻击表

攻击表大小：32KB
攻击表记录：11

创建表attack（

  `AttackID` int(4) NOT NULL,

  `AttackName` varchar(30) COLLATE utf8_bin NOT NULL,

  `AttackDescription` varchar(70) COLLATE utf8_bin NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

ALTER TABLE `attack`
  ADD PRIMARY KEY (`AttackID`),

慢查询：

SELECT Count(EventID), IP
-> FROM event
-> WHERE AttackID >0
-> GROUP BY IP
-> ORDER BY Count(EventID) DESC
-> LIMIT 5;

结果：集合中的 5 行（1.220 秒）（这对我来说似乎很长，对于一个简单的查询）

查询慢

现在奇怪的是：如果我删除外键关系，查询的性能是一样的。但是，如果我删除 event.AttackID 上的索引，则相同的 select 语句要快得多：

(ALTER TABLE `event` DROP INDEX `AttackID`;)

SQL SELECT 查询的结果： 集合中的 5 行（0.242 秒）

快速查询

据我了解，“WHERE”中使用的列的索引应该可以提高性能。

为什么删除索引会对查询产生如此大的影响？
我能做些什么来保持表之间的关系并更快地执行 SELECT？

干杯

标签： mysqlsqlselectindexingquery-performance

解决方案

基本上，除了 FKey 之外，查询的每个部分都会使查询变慢。

您的查询相当于

SELECT  Count(*), IP
    FROM  event
    WHERE  AttackID >0
    GROUP BY  IP
    ORDER BY  Count(*) DESC
    LIMIT  5;

COUNT(*)除非您需要避免，否则请使用NULL。

如果AttackID很少，则 >0最佳索引可能是

ADD INDEX(AttackID,    -- for filtering
          IP)          -- for covering

否则，最佳索引可能是

ADD INDEX(IP,          -- to avoid sorting
          AttackID)    -- for covering

您可以简单地添加两个索引并让优化器决定。同时，如果它们存在，请摆脱它们：

DROP INDEX(AttackID)
DROP INDEX(IP)

因为它们的任何使用都由新索引处理。

此外，保留 1 列索引可能会使优化器混淆使用它们而不是覆盖索引。（这似乎是至少某些版本的 MySQL/MariaDB 的设计缺陷。）

“覆盖”意味着查询可以完全在索引的BTree中执行。 EXPLAIN将用“使用索引”指示它。“覆盖”索引可将查询速度提高 2 倍——但这一预测存在很大差异。（“使用索引条件”是不同的。）

有关索引创建的更多信息：http: //mysql.rjweb.org/doc.php/index_cookbook_mysql

mysql - MySQL：因为索引/FKEY 导致 SELECT 慢？

问题描述

解决方案

推荐阅读