在数据库方面,我是一个相对新手。我们正在使用 MySQL,我目前正在尝试加速似乎需要一段时间才能运行的 SQL 语句。我环顾四周寻找类似的问题,但没有找到。
目标是删除表 A 中在表 B 中具有匹配 id 的所有行。
我目前正在做以下事情:
DELETE FROM a WHERE EXISTS (SELECT b.id FROM b WHERE b.id = a.id);
表 a 中大约有 100K 行,表 b 中大约有 22K 行。 'id' 列是两个表的 PK。CREATE TABLE b LIKE a;
表 a(以及表 b)有一些索引来帮助加快对其进行的查询。同样,我是 DB 工作中的相对新手,仍在学习中。我不知道这对事物有多大影响,如果有的话。我认为它确实有影响,因为索引也必须清理,对吗?我还想知道是否还有其他可能影响速度的数据库设置。DROP TABLE IF EXISTS `frobozz`.`a`;
CREATE TABLE `frobozz`.`a` (
`id` bigint(20) unsigned NOT NULL auto_increment,
`fk_g` varchar(30) NOT NULL,
`h` int(10) unsigned default NULL,
`i` longtext,
`j` bigint(20) NOT NULL,
`k` bigint(20) default NULL,
`l` varchar(45) NOT NULL,
`m` int(10) unsigned default NULL,
`n` varchar(20) default NULL,
`o` bigint(20) NOT NULL,
`p` tinyint(1) NOT NULL,
PRIMARY KEY USING BTREE (`id`),
KEY `idx_l` (`l`),
KEY `idx_h` USING BTREE (`h`),
KEY `idx_m` USING BTREE (`m`),
KEY `idx_fk_g` USING BTREE (`fk_g`),
KEY `fk_g_frobozz` (`id`,`fk_g`),
CONSTRAINT `fk_g_frobozz` FOREIGN KEY (`fk_g`) REFERENCES `frotz` (`g`)
) ENGINE=InnoDB AUTO_INCREMENT=179369 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
我怀疑问题的一部分是该表有许多索引。id
和 h
.starting 0.000018
checking query cache for query 0.000044
checking permissions 0.000005
Opening tables 0.000009
init 0.000019
optimizing 0.000004
executing 0.000043
end 0.000005
end 0.000002
query end 0.000003
freeing items 0.000007
logging slow query 0.000002
cleaning up 0.000002
DELETE FROM c WHERE c.id = theId;
我查看了 EXPLAIN 语句并将其重写为,EXPLAIN SELECT * FROM c WHERE c.other_id = 12345;
所以,我可以看到这是在做什么,它给了我以下信息:id 1
select_type SIMPLE
table c
type ALL
possible_keys NULL
key NULL
key_len NULL
ref NULL
rows 2633
Extra using where
这告诉我,这是一个痛苦的操作,因为它将被调用 22500 次(对于被删除的给定数据集),这就是问题所在。一旦我在 other_id 列上创建了一个 INDEX 并重新运行 EXPLAIN,我得到:id 1
select_type SIMPLE
table c
type ref
possible_keys Index_1
key Index_1
key_len 8
ref const
rows 1
Extra
好多了,事实上真的很棒。最佳答案
从 InnoDB 中删除数据是您可以请求的最昂贵的操作。正如您已经发现查询本身不是问题 - 无论如何,它们中的大多数都会优化为相同的执行计划。
虽然可能很难理解为什么所有情况下的 DELETE 都是最慢的,但有一个相当简单的解释。 InnoDB 是一个事务存储引擎。这意味着,如果您的查询在中途中止,所有记录仍将保留,就好像什么也没发生一样。一旦完成,一切都会在同一瞬间消失。在 DELETE 期间,连接到服务器的其他客户端将看到记录,直到您的 DELETE 完成。
为了实现这一点,InnoDB 使用了一种称为 MVCC(多版本并发控制)的技术。它的主要作用是为每个连接提供整个数据库的快照 View ,就像事务的第一个语句开始时一样。为了实现这一点,InnoDB 内部的每条记录都可以有多个值——每个快照一个。这也是为什么在 InnoDB 上进行计数需要一些时间 - 这取决于您当时看到的快照状态。
对于您的 DELETE 事务,根据您的查询条件标识的每条记录都会被标记为删除。由于其他客户端可能同时访问数据,因此无法立即将它们从表中删除,因为它们必须查看各自的快照以保证删除的原子性。
一旦所有记录都被标记为删除,事务就成功提交。即便如此,在 DELETE 事务之前使用快照值的所有其他事务也结束之前,它们也无法立即从实际数据页面中删除。
因此,实际上您的 3 分钟并没有那么慢,考虑到必须修改所有记录才能以事务安全的方式将它们删除。当语句运行时,您可能会“听到”硬盘在工作。这是由访问所有行引起的。
为了提高性能,您可以尝试增加服务器的 InnoDB 缓冲池大小,并尝试在 DELETE 时限制对数据库的其他访问,从而减少 InnoDB 每条记录必须维护的历史版本数。
使用额外的内存 InnoDB 可能能够将您的表(大部分)读入内存并避免一些磁盘搜索时间。
https://stackoverflow.com/questions/812512/