英文:
MySQL queries getting slower with time
问题
我有一个大约有90万条记录的表。其中大约60万条每5分钟更新一次（使用REPLACE INTO）。
CREATE TABLE `servers` (
`ipport` varchar(255) NOT NULL,
`ip` varchar(255) NOT NULL,
`port` int(11) NOT NULL,
`ip_as_int` int(10) unsigned NOT NULL,
`version` text NOT NULL,
`protocol` int(11) NOT NULL,
`online_count` int(11) NOT NULL,
`max_count` int(11) NOT NULL,
`description` text NOT NULL,
`favicon` text DEFAULT NULL,
`last_seen` int(11) NOT NULL,
`cracked` tinyint(1) DEFAULT NULL,
`joined_on` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`ipport`),
KEY `ip_as_int_idx` (`ip_as_int`),
KEY `last_seen_idx` (`last_seen`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci
如果我重新启动MariaDB（systemctl restart mariadb），查询需要大约3秒钟。随着时间的推移，查询变得越来越慢（今天早上我看到一个查询花了2分钟！！！）。
服务器规格：
8 GB RAM
6 个虚拟CPU核心
8 GB 交换内存
变量转储：
https://pastebin.com/raw/5aXTxRV1
我想知道为什么查询随着时间的推移而变得越来越慢。
查询始终类似于以下内容（在WHERE语句中可以使用任何列）：
SELECT /*+ MAX_EXECUTION_TIME(120000) */ *
FROM servers AS s
WHERE (protocol = 762)
AND (s.last_seen > 1687177923)
ORDER BY RAND()
LIMIT 100;
英文:
I have a table with around 900k entries. Around 600k of those get updated every 5 minutes. (using REPLACE INTO)
CREATE TABLE `servers` (
`ipport` varchar(255) NOT NULL,
`ip` varchar(255) NOT NULL,
`port` int(11) NOT NULL,
`ip_as_int` int(10) unsigned NOT NULL,
`version` text NOT NULL,
`protocol` int(11) NOT NULL,
`online_count` int(11) NOT NULL,
`max_count` int(11) NOT NULL,
`description` text NOT NULL,
`favicon` text DEFAULT NULL,
`last_seen` int(11) NOT NULL,
`cracked` tinyint(1) DEFAULT NULL,
`joined_on` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`ipport`),
KEY `ip_as_int_idx` (`ip_as_int`),
KEY `last_seen_idx` (`last_seen`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci
If I restart MariaDB (systemctl restart mariadb) the queries take around 3 seconds. The more time passes, the slower the queries get. (This morning I saw one that took 2 minutes!!!)
Server specs:
8 GB RAM
6 vCPU cores
8 GB swap memory
Variable dump:
https://pastebin.com/raw/5aXTxRV1
I am wondering why the queries are getting slower with time.
Queries are always similar to this (any of the columns can be used in the WHERE statement though):
SELECT /*+ MAX_EXECUTION_TIME(120000) */ *
FROM servers AS s
WHERE (protocol = 762)
AND (s.last_seen > 1687177923)
ORDER BY RAND()
LIMIT 100;
</details>
# 答案1
**得分**: 1
我会首先在两个查询条件上创建一个复合索引。
表 索引
servers ( 协议, 最后可见 )
去掉最大执行时间。
另外，请编辑帖子，展示一些实际查询和返回的列的具体内容，而不是看起来是查询和C#样式注释的连接。
<details>
<summary>英文:</summary>
I would START with a compound index on both querying criteria.
table index on
servers ( protocol, last_seen )
Get rid of the max_execution_time.
Also, edit the post and show a little more specific of the actual queries and columns returned vs what appears to be concatenation of the query and c# style commenting.
</details>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论