时间戳

首页 > 解决方案 > 如何优化 mysql 查询，因为 Full ProcessList 显示发送数据超过 24 小时

mysql - 如何优化 mysql 查询，因为 Full ProcessList 显示发送数据超过 24 小时

问题描述

我有以下永远运行的查询，我正在寻找是否有可以优化它的方法。这是在一个总共有 1,406,480 行数据的表上运行的，但除了 Filename 和 Refcolumn 之外，ID 和 End_Date 都已被索引。

我的查询：

INSERT INTO UniqueIDs
    (
    SELECT
        T1.ID
    FROM
        master_table T1
    LEFT JOIN
        master_table T2
    ON
    (
        T1.Ref_No = T2.Ref_No
    AND
        T1.End_Date = T2.End_Date
    AND
        T1.Filename = T2.Filename
    AND
        T1.ID > T2.ID
    )
    WHERE T2.ID IS NULL
    AND
        LENGTH(T1.Ref_No) BETWEEN 5 AND 10
    )
    ;

解释结果： 在此处输入图像描述

不索引 Ref_No 的原因是这是一个文本列，因此当我尝试索引此列时出现 BLOB/TEXT 错误。

如果有人能就如何加快这个查询提出建议，我将不胜感激。

谢谢

感谢 Bill 在多列索引方面我已经取得了一些进展。我首先运行了这段代码：

CREATE INDEX I_DELETE_DUPS ON master_table(id, End_Date);

然后，我添加了一个新列来显示 Ref_No 的长度，但由于我的 MySQL 版本是 5.5，因此必须从 Bill 提到的查询中更改它。所以我分三个步骤运行它：

ALTER TABLE master_table
ADD COLUMN Ref_No_length SMALLINT UNSIGNED;

UPDATE master_table SET Ref_No_length = LENGTH(Ref_No);

ALTER TABLE master_table ADD INDEX (Ref_No_length);

最后一步是使用 where 子句更改我的插入查询的长度。这已更改为：

AND t1.Ref_No_length between 5 and 10;

然后我运行了这个查询，并在 15 分钟内将价值 280k 的 id 插入到我的 UniqueIDs 表中。我确实去更改了我的插入脚本，看看是否可以通过执行以下操作为长度添加更多值：

AND t1.Ref_No_length IN (5,6,7,8,9,10,13);

这是为了引入长度也等于 13 的值。这个查询花费了更长的时间，准确地说是 2 小时 50 分钟，但是查找所有长度为 13 的行的额外要求给了我额外的 700k 唯一 ID。

我正在寻找使用 IN 子句优化查询的方法，但是这个查询保持运行 24 小时是一个很大的改进。所以非常感谢比尔。

标签： mysqlindexingexplain

解决方案

对于 JOIN，您应该在(Ref_No, End_Date, Filename).

您可以像这样在 TEXT 列上创建前缀索引：

ALTER TABLE master_table ADD INDEX (Ref_No(10));

但这不会帮助您根据 LENGTH() 进行搜索。索引仅有助于按索引值搜索，而不是按列上的函数搜索。

在 MySQL 5.7 或更高版本中，您可以像这样创建一个虚拟列，其中包含为虚拟列计算的值的索引：

ALTER TABLE master_table
  ADD COLUMN Ref_No_length SMALLINT UNSIGNED AS (LENGTH(Ref_No)),
  ADD INDEX (Ref_No_length);

然后 MySQL 会识别出您在查询中的条件与虚拟列的表达式相同，并且它会自动使用索引（例外：根据我的经验，这不适用于使用 JSON 函数的表达式）。

但这并不能保证该指数会有所帮助。如果大多数行匹配长度在 5 到 10 之间的条件，优化器将不会打扰索引。使用索引可能比进行表扫描更费力。

推荐阅读