主页 / dba / 问题 / 1497
Accepted
Derek Downey
Asked: 2011-03-01 12:01:05 +0800 CST

如何提高 InnoDB DELETE 性能？

  • 772

所以我有这个审计表（跟踪我数据库中任何表的操作）：

CREATE TABLE `track_table` (
  `id` int(16) unsigned NOT NULL,
  `userID` smallint(16) unsigned NOT NULL,
  `tableName` varchar(255) NOT NULL DEFAULT '',
  `tupleID` int(16) unsigned NOT NULL,
  `date_insert` datetime NOT NULL,
  `action` char(12) NOT NULL DEFAULT '',
  `className` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `userID` (`userID`),
  KEY `tableID` (`tableName`,`tupleID`,`date_insert`),
  KEY `actionDate` (`action`,`date_insert`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

我需要开始归档过时的项目。该表已增长到大约 5000 万行，因此我删除行的最快方法是一次删除一个表（基于tableName）。

这工作得很好，但在一些写繁重的表上，它不会完成。delete我的查询删除了对 tupleID/tableName 组合具有关联操作的所有项目：

DELETE FROM track_table WHERE tableName='someTable' AND tupleID IN (
  SELECT DISTINCT tupleID FROM track_table
  WHERE tableName='someTable' AND action='DELETE' AND date_insert < DATE_SUB(CURDATE(), INTERVAL 30 day)
)

我让它在我的服务器上运行了 3 天，但它从未完成最大的表。解释输出（如果我将删除切换为选择：

| id | select_type        | table       | type | possible_keys      | key     | key_len | ref        | rows    | Extra                        |
|  1 | PRIMARY            | track_table | ref  | tableID            | tableID | 257     | const      | 3941832 | Using where                  |
|  2 | DEPENDENT SUBQUERY | track_table | ref  | tableID,actionDate | tableID | 261     | const,func |       1 | Using where; Using temporary |

因此，我认为 400 万行不应该需要 3 天才能删除。我将 innodb_buffer_pool_size 设置为 3GB，并且服务器未设置为使用 one_file_per_table。还有哪些其他方法可以提高 InnoDB 删除性能？（在 Mac OSX 上运行 MySQL 5.1.43）

mysql performance

4 个回答

  1. Best Answer

    您可以批量删除数据。

    在 SQL Server 中，语法是delete top X表中的行。然后，您在一个循环中执行此操作，每个批次都有一个事务（当然，如果您有多个语句），因此要保持事务简短并仅在短时间内保持锁定。

    在 MySQL 语法中：DELETE FROM userTable LIMIT 1000

    对此有一些限制（例如，不能LIMIT在带有连接的删除中使用），但在这种情况下，您可能可以这样做。

    当涉及到复制时，使用LIMITwith还有一个额外的危险；DELETE删除的行有时在从属设备上的删除顺序与在主设备上的删除顺序不同。

    • 11

  2. 尝试使用临时表方法。尝试这样的事情：

    步骤1）CREATE TABLE track_table_new LIKE track_table;

    第2步）INSERT INTO track_table_new SELECT * FROM track_table WHERE action='DELETE' AND date_insert >= DATE_SUB(CURDATE(), INTERVAL 30 day);

    步骤 3)ALTER TABLE track_table RENAME track_table_old;

    第4步）ALTER TABLE track_table_new RENAME track_table;

    步骤 5)DROP TABLE track_table_old;

    我没有在步骤 2 中包含元组字段。请看看这是否产生了预期的效果。如果这是您想要的，您可能希望完全放弃元组字段，除非您出于其他原因使用元组字段。

    • 6

  3. 批量删除不需要的行应该保持其他操作可行。但是您的删除操作是有条件的，因此请确保在超过条件的列上有适当的索引。

    由于 MySQL 不支持完整的松散索引扫描功能，您可以尝试调整 to 的KEY actionDate (action, date_insert)顺序KEY actionDate (date_insert, action)。使用 'date_insert' 前缀，MySQL 应该使用该索引来扫描在您的日期时间条件之前的行。

    使用这样的索引，您可以将 SQL 编写为：

    DELETE
FROM track_table
WHERE tableName='someTable'
    AND action='DELETE'
    AND date_insert < DATE_SUB(CURDATE(), INTERVAL 30 day)
LIMIT 1000 -- Your size of batch
    • 1 
  4. | id | select_type        | table       | type | possible_keys      | key     | key_len | ref        | rows    | Extra                        |
|  1 | PRIMARY            | track_table | ref  | tableID            | tableID | 257     | const      | 3941832 | Using where                  |
|  2 | DEPENDENT SUBQUERY | track_table | ref  | tableID,actionDate | tableID | 261     | const,func |       1 | Using where; Using temporary |

    -拳头，根据您的解释，key_len 这么大 => 您需要将尺寸降级为尽可能小。对于您的查询，我认为最好的方法是将操作字段的数据类型从 char(12) 更改为 tinyint，因此数据映射如下所示：

    1: -> DELETE
2: -> UPDATE
3: -> INSERT
...

    您也可以更改 table_id 而不是 tablename。获得最佳性能的 DDL 可以：

    CREATE TABLE `track_table` (
  `id` int(11) unsigned NOT NULL,
  `userID` smallint(6) unsigned NOT NULL,
  `tableid` smallint(6) UNSIGNED NOT NULL DEFAULT 0,
  `tupleID` int(11) unsigned NOT NULL,
  `date_insert` datetime NOT NULL,
  `actionid` tinyin(4) UNSIGNED NOT NULL DEFAULT 0,
  `className` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `userID` (`userID`),
  KEY `tableID` (`tableid`,`tupleID`,`date_insert`),
  KEY `actionDate` (`actionid`,`date_insert`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `actions` (
  `id` tinyint(4) unsigned NOT NULL 
  `actionname` varchar(255) NOT NULL,
  PRIMARY KEY (`id`) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `table_name` (
  `id` tinyint(4) unsigned NOT NULL 
  `tablename` varchar(255) NOT NULL,
  PRIMARY KEY (`id`) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

    所以查询可以运行如下：

    DELETE FROM track_table WHERE tableid=@tblid AND tupleID IN (
  SELECT DISTINCT tupleID FROM track_table
  WHERE tableid=@tblid AND actionid=@actionid AND date_insert < DATE_SUB(CURDATE(), INTERVAL 30 day)
).

    但最快的方法是使用分区。所以你可以删除分区。目前，我的表有超过 4000 万行。并每小时更新一次（每次更新 400k 行），我可以删除 curr_date 分区并将数据重新加载到表中。drop 命令非常快（<100ms）。希望这有帮助。

    • 1

相关问题