MySql 请求时间慢

Question

我有一台非常好的电脑，我想知道为什么要花这么长时间才能向我的一张桌子发出一个非常简单的请求。

我的电脑：

• i9 10900kf（10 核）
• 64 GB 内存
• 2TB NVME SSD
• RTX 3090 Ventus

1：我的表有 531 732 行（这不是很多行）和 39 列

我有以下<我的表的strong>索引：

• DisplayName
• TmiSentTs
• 用户名
• 频道
• DisplayName_TmiSentTs
• Username_TmiSentTs

当我进行以下查询时，需要 66.016 秒才能获得响应：

SELECT from_unixtime(TmiSentTs/1000,'%Y/%m/%d  %H:%i:%s'),
  DisplayName, message 
FROM MY_TABLE 
WHERE displayname LIKE '%ab%'
ORDER BY TmiSentTs DESC;

我认为这不正常。

我尝试：

1. 将我的 innodb_write_io_threads 从 4 更改为 32
2. 将我的 innodb_read_io_threads 从 4 更改为 32

但是这些都不起作用，我有另一个表（在另一个数据库中），有 37 325 332行，类似的查询需要2秒。

编辑： 经过一番研究，我发现这

SELECT * FROM pepegaclapwr.twitchmessages where instr('aa',username)<>0;

更快

SELECT * FROM pepegaclapwr.twitchmessages where username like '%aa%';

比相同的结果

Answer 1

displayname 列上的索引不会帮助此查询。任何在您要搜索的模式开头带有通配符的 LIKE 条件都不能使用索引，因此它必然会进行表扫描。

想想电话簿。如果我要求您查找以“T”开头的姓氏，这很容易，因为这本书是按姓氏排序的。但是，如果我要求您查找“T”是第四个字母（或第一个字母之后的任何内容）的名字，那么这本书已排序这一事实并没有帮助。你还是得一页一页地读整本书才能找到我要的名字。

要优化上面显示的查询类型，您可能会发现使用全文索引更容易，但前提是您要搜索整个单词。看起来您正在搜索其中某处包含“ab”的任何字符串。这不是一个完整的单词，因此全文索引也无济于事。

在这种情况下，唯一的解决方案是向表中添加另一列以指示该行是否包含“ab”，然后对该列建立索引。在 MySQL 5.7 及更高版本中，您可以基于表达式创建虚拟列。

ALTER TABLE MyTable
  ADD COLUMN displayname_ab TINYINT NOT NULL AS (displayname LIKE '%ab%'),
  ADD INDEX (displayname_ab);

然后搜索：

SELECT ... FROM MyTable WHERE displayname_ab = true;

在MySQL 8.0中，您甚至不需要创建虚拟列，您可以直接从现有列上的表达式创建表达式索引：

ALTER TABLE MyTable
  ADD INDEX ((displayname LIKE '%ab%'));

然后，如果您使用完全相同的表达式进行搜索，它将使用索引：

SELECT ... FROM MyTable WHERE displayname LIKE '%ab%';

但这会将您需要的字符串修复到虚拟列定义中。如果您明天需要搜索“cd”或任何其他模式，这没有帮助。