MySQL queries getting slower with time

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>