일치하는 행을 삭제하는 더 빠른 방법
데이터베이스에 관해서는 비교적 초보입니다.현재 MySQL을 사용하고 있으며 SQL 문을 실행하는 데 시간이 걸릴 것 같은 속도를 높이려고 합니다.비슷한 질문을 하려고 SO를 찾아봤지만 하나도 없었다.
목표는 테이블 B에 일치하는 ID를 가진 테이블A의 모든 행을 삭제하는 것입니다.
현재 다음과 같은 작업을 하고 있습니다.
DELETE FROM a WHERE EXISTS (SELECT b.id FROM b WHERE b.id = a.id);
테이블 a에는 약 10K 행이 있고 테이블 b에는 약 22K 행이 있습니다.'id' 열은 두 테이블의 PK입니다.
이 명령어는 테스트 박스(Pentium D, XP SP3, 2GB RAM, MySQL 5.0.67)에서 실행하는데 약 3분 걸립니다.이건 느린 것 같아.아닐 수도 있지만, 좀 더 빨리 진행했으면 좋겠어요.더 나은/빠른 방법이 있을까요?
편집:
도움이 될 수 있는 몇 가지 추가 정보입니다.표 A와 표 B는 표 B를 작성하기 위해 다음과 같은 구조를 가지고 있습니다.
CREATE TABLE b LIKE a;
표 a(및 표 b)에는 이에 대한 쿼리 속도를 높이는 데 도움이 되는 인덱스가 몇 개 있습니다.다시 말씀드리지만, 저는 DB 업무는 비교적 초보이고 아직 배우고 있습니다.만약 있다면, 이것이 사물에 얼마나 큰 영향을 미치는지 모르겠다.인덱스도 정리해야 하니까 효과가 있겠죠?그 밖에 속도에 영향을 줄 만한 DB 설정이 있는지 궁금합니다.
또, 저는 INNO DB를 사용하고 있습니다.
여기 도움이 될 수 있는 몇 가지 추가 정보가 있습니다.
표 A는 다음과 같은 구조를 가지고 있습니다(이것을 조금 삭제했습니다).
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;
문제의 일부는 이 테이블에 대한 많은 인덱스가 있다는 것입니다.B와 은 'B' 'B' 'B' 'B' 'B' 'B' 'B' 'B' 'B' 'B' 'B' 'B' 'B'밖에 없습니다'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
해결된
많은 반응과 댓글에 감사드립니다.그들은 확실히 내가 그 문제에 대해 생각하게 만들었다."다른 테이블은 a.id을 참조하고 있습니까?"라는 간단한 질문을 던짐으로써 문제에서 벗어날 수 있게 해준 것에 대해 칭찬합니다.
문제는 테이블 A에 DELETE TRIGER가 있다는 것입니다.이것에 의해, 다른 2개의 테이블, C와 D를 갱신하기 위한 스토어드 프로시저가 호출되었습니다.표 C는 FK를 a.id으로 되돌려 저장 프로시저에서 해당 ID와 관련된 작업을 수행한 후 다음과 같은 문구를 가지고 있습니다.
DELETE FROM c WHERE c.id = theId;
DESPLINE 스테이트먼트를 살펴보고 이렇게 다시 썼습니다.
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를 작성하고 DESPLIND를 다시 실행하면 다음과 같이 표시됩니다.
id 1
select_type SIMPLE
table c
type ref
possible_keys Index_1
key Index_1
key_len 8
ref const
rows 1
Extra
훨씬 나아졌어, 사실 정말 대단해.
Index_1과 나의 삭제 시간은 mattkemp가 보고한 시간과 일치한다고 덧붙였습니다.이것은 마지막 순간에 신발 호어링으로 인한 매우 미묘한 오류였습니다.Daniel이 말한 것처럼 DELETE/SELECT 제안문의 대부분은 기본적으로 동일한 시간이 걸렸고, Soulmerge가 언급했듯이, 이 문장은 내가 해야 할 일을 바탕으로 한 거의 최고의 문장이었습니다.이 다른 테이블 C의 인덱스를 제공하자 DELETE가 빨라졌습니다.
사후:
이 연습에서 얻은 두 가지 교훈이 있습니다.첫째, SQL 쿼리의 영향을 더 잘 이해하기 위해 EXPLINE 스테이트먼트의 힘을 활용하지 않은 것이 분명합니다.그건 초보자 실수니까 자책하지 않을 거예요.나는 그 실수를 통해 배울 것이다.둘째, 문제의 코드는 '빠른 실행'이라는 사고방식의 결과였고 부적절한 설계/테스트로 인해 이 문제가 더 빨리 나타나지 않았습니다.이 신기능의 테스트 입력으로 사용할 수 있는 대용량의 테스트 데이터 세트를 몇 개 생성했더라면 제 시간이나 여러분의 시간을 낭비하지 않았을 것입니다.DB측에서의 테스트는 어플리케이션측에서의 상세도가 부족했습니다.이제 그걸 개선할 수 있는 기회가 생겼죠
InnoDB에서 데이터를 삭제하는 것은 당신이 요청할 수 있는 작업 중 가장 비용이 많이 드는 작업입니다.이미 알고 있듯이 쿼리 자체는 문제가 아닙니다.대부분은 같은 실행 계획에 최적화되어 있습니다.
모든 사례에서 DELETE가 가장 느린 이유를 이해하기 어려울 수 있지만, 다소 간단한 설명이 있습니다.InnoDB는 트랜잭션 스토리지 엔진입니다.즉, 쿼리가 중간에 중단되어도 모든 레코드는 아무 일도 없었던 것처럼 그대로 유지됩니다.일단 그것이 완성되면, 모든 것은 순식간에 사라질 것이다.DELETE 중에 서버에 접속하고 있는 다른 클라이언트는 DELETE가 완료될 때까지 레코드를 확인합니다.
이를 위해 InnoDB는 MVCC(Multi Version Concurrency Control)라는 기술을 사용한다.기본적으로는 트랜잭션의 첫 번째 스테이트먼트가 시작되었을 때와 마찬가지로 각 접속에 데이터베이스 전체의 스냅샷 뷰를 제공하는 것입니다.이를 위해 InnoDB 내부의 모든 레코드는 스냅샷마다 하나씩 여러 개의 값을 가질 수 있습니다.따라서 InnoDB에 대한 카운트에는 시간이 걸립니다.그때 표시되는 스냅샷 상태에 따라 달라집니다.
DELETE 트랜잭션의 경우 조회 조건에 따라 식별된 각 레코드는 삭제 대상으로 표시됩니다.다른 클라이언트는 동시에 데이터에 액세스 하고 있을 가능성이 있기 때문에 테이블에서 즉시 삭제할 수 없습니다.삭제의 원자성을 보증하기 위해 각각의 스냅샷을 확인해야 하기 때문입니다.
모든 레코드가 삭제 마크가 붙어 있으면 트랜잭션은 정상적으로 커밋됩니다.DELETE 트랜잭션 전에 스냅샷 값으로 작동했던 다른 모든 트랜잭션이 종료되기 전에는 실제 데이터 페이지에서 즉시 제거할 수 없습니다.
따라서 거래 시 안전한 방법으로 삭제 준비를 위해 모든 레코드를 수정해야 하는 점을 고려할 때 실제로는 3분이 그리 느리지는 않습니다.아마 문이 실행되는 동안 하드 디스크가 작동하는 것을 듣게 될 것입니다.이 문제는 모든 행에 액세스하기 때문에 발생합니다.성능을 향상시키기 위해 서버의 InnoDB 버퍼 풀 크기를 늘리고 삭제 중에 데이터베이스에 대한 다른 액세스를 제한함으로써 InnoDB가 레코드당 유지해야 하는 이전 버전의 수를 줄일 수 있습니다.추가 메모리를 사용하면 InnoDB는 (대부분의) 테이블을 메모리로 읽고 디스크 탐색 시간을 피할 수 있습니다.
이것을 시험해 보세요.
DELETE a
FROM a
INNER JOIN b
on a.id = b.id
서브쿼리를 사용하면 외부 쿼리의 각 레코드에 대해 실행되므로 조인보다 속도가 느려지는 경향이 있습니다.
대용량 데이터로 작업해야 할 경우 항상 다음과 같이 합니다(여기에서는 150000 행의 샘플 테스트 테이블).
drop table if exists employees_bak;
create table employees_bak like employees;
insert into employees_bak
select * from employees
where emp_no > 100000;
rename table employees to employees_todelete;
rename table employees_bak to employees;
drop table employees_todelete;
이 경우 sql은 50000 행을 백업테이블에 필터링 합니다.쿼리 캐스케이드는 느린 머신에서 5초 만에 실행됩니다.자체 필터 조회를 통해 선택 항목에 삽입을 대체할 수 있습니다.
이것이 빅 데이터베이스에서 대량 삭제를 수행하는 요령입니다!;=)
3분이라는 시간이 너무 느린 것 같아요.ID 열이 제대로 인덱싱되지 않은 것 같습니다.사용하고 있는 정확한 테이블 정의를 제공해 주시면 감사하겠습니다.
테스트 데이터를 생성하기 위해 간단한 python 스크립트를 만들고 동일한 데이터 세트에 대해 여러 버전의 삭제 쿼리를 실행했습니다.테이블 정의는 다음과 같습니다.
drop table if exists a;
create table a
(id bigint unsigned not null primary key,
data varchar(255) not null) engine=InnoDB;
drop table if exists b;
create table b like a;
그런 다음 100k 행을 a에, 25k 행을 b에 각각 삽입했습니다(그 중 22.5k도 a에 포함).다음은 다양한 delete 명령어의 결과입니다.그나저나 중간에 테이블을 떨어뜨리고 다시 채웠어요.
mysql> DELETE FROM a WHERE EXISTS (SELECT b.id FROM b WHERE a.id=b.id);
Query OK, 22500 rows affected (1.14 sec)
mysql> DELETE FROM a USING a LEFT JOIN b ON a.id=b.id WHERE b.id IS NOT NULL;
Query OK, 22500 rows affected (0.81 sec)
mysql> DELETE a FROM a INNER JOIN b on a.id=b.id;
Query OK, 22500 rows affected (0.97 sec)
mysql> DELETE QUICK a.* FROM a,b WHERE a.id=b.id;
Query OK, 22500 rows affected (0.81 sec)
모든 테스트는 인텔 Core2 쿼드코어 2.5에서 실행되었습니다.Ubuntu 8.10 및 MySQL 5.0 탑재 GHz, 2GB RAM1개의 sql 문의 실행은 여전히 단일 스레드입니다.
업데이트:
itsmatt의 스키마를 사용하기 위해 테스트를 업데이트했습니다.auto increment 삭제(합성 데이터 생성 중)와 문자 집합 인코딩(작동하지 않음)으로 약간 수정했습니다.
새로운 테이블 정의는 다음과 같습니다.
drop table if exists a;
drop table if exists b;
drop table if exists c;
create table c (id varchar(30) not null primary key) engine=InnoDB;
create table a (
id bigint(20) unsigned not null primary key,
c_id 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,
key l_idx (l),
key h_idx (h),
key m_idx (m),
key c_id_idx (id, c_id),
key c_id_fk (c_id),
constraint c_id_fk foreign key (c_id) references c(id)
) engine=InnoDB row_format=dynamic;
create table b like a;
그런 다음 a에 100k 행이 있고 b에 25k 행이 있는 동일한 검정을 다시 실행했습니다.
mysql> DELETE FROM a WHERE EXISTS (SELECT b.id FROM b WHERE a.id=b.id);
Query OK, 22500 rows affected (11.90 sec)
mysql> DELETE FROM a USING a LEFT JOIN b ON a.id=b.id WHERE b.id IS NOT NULL;
Query OK, 22500 rows affected (11.48 sec)
mysql> DELETE a FROM a INNER JOIN b on a.id=b.id;
Query OK, 22500 rows affected (12.21 sec)
mysql> DELETE QUICK a.* FROM a,b WHERE a.id=b.id;
Query OK, 22500 rows affected (12.33 sec)
보시다시피 인덱스가 여러 개 있기 때문에 이전보다 속도가 상당히 느려졌습니다.하지만 3분대에는 미치지 못한다.
롱 텍스트필드를 스키마 끝으로 이동하는 방법도 있습니다.mySQL은 모든 크기 제한 필드가 처음이고 text, blob 등이 끝에 있으면 성능이 더 좋은 것으로 기억합니다.
a의 모든 행에 대해 b에 대한 서브쿼리를 수행하고 있습니다.
시험:
DELETE FROM a USING a LEFT JOIN b ON a.id = b.id WHERE b.id IS NOT NULL;
이것을 시험해 보세요.
DELETE QUICK A.* FROM A,B WHERE A.ID=B.ID
일반 쿼리보다 훨씬 빠릅니다.
구문에 대해서는, http://dev.mysql.com/doc/refman/5.0/en/delete.html 를 참조해 주세요.
OP의 색인 누락으로 인해 이 문제가 거의 해결된 것으로 알고 있습니다만, 이 문제에 대한 보다 일반적인 사례에서 유효한 추가 조언을 드리고 싶습니다.
한 테이블에서 다른 테이블에 있는 많은 행을 삭제해야 하는 문제에 대해 개인적으로 대처해 왔습니다.제 경험으로는 특히 많은 행을 삭제할 것으로 예상되는 경우에는 다음 작업을 수행하는 것이 가장 좋습니다.이 기술을 사용하면 복제 슬레이브 지연이 개선됩니다.각 뮤테이터 쿼리가 오래 실행될수록 지연이 심해집니다(복제는 싱글 스레드화됨).
예를 들어, 먼저 SELECT를 별도의 쿼리로 실행하고 스크립트/애플리케이션에서 반환된 ID를 기억한 후 일괄 삭제(한 번에 50,000 행 등)를 계속합니다.이를 통해 다음을 달성할 수 있습니다.
- 각 delete 문장은 테이블을 너무 오래 잠그지 않기 때문에 복제 지연이 제어 불능이 되지 않습니다.복제를 사용하여 비교적 최신 데이터를 제공하는 경우 특히 중요합니다.배치를 사용하면 각 DELETE 쿼리가 여전히 너무 오래 걸리는 경우 DB 구조를 건드리지 않고 더 작게 조정할 수 있습니다.
- 별도의 SELECT를 사용하는 또 다른 장점은 SELECT 자체를 실행하는 데 시간이 오래 걸릴 수 있다는 것입니다. 특히 어떤 이유로든 최상의 DB 인덱스를 사용할 수 없는 경우에는 더욱 그렇습니다.SELECT가 DELETE 내부일 경우 스테이트먼트 전체가 슬레이브로 이행할 때 SELECT를 처음부터 다시 실행해야 합니다.이 경우 롱 셀렉트를 처음부터 다시 해야 하기 때문에 슬레이브가 지연될 수 있습니다.노예 지연은 다시 심각한 고통을 겪습니다.별도의 SELECT 쿼리를 사용하면 ID 목록만 전달되므로 이 문제는 해결됩니다.
내 논리에 뭔가 잘못이 있으면 알려줘.
복제 지연 및 이와 유사한 해결 방법에 대한 자세한 내용은 MySQL Slave Lag(지연) 설명 및 7가지 해결 방법을 참조하십시오.
추신: 한 가지 주의해야 할 점은 SELECT가 종료되고 DELETE가 시작될 때까지 테이블이 편집될 수 있다는 것입니다.당신의 어플리케이션과 관련된 트랜잭션 및/또는 논리를 사용하여 이러한 세부사항을 처리하도록 하겠습니다.
DELETE FROM a WHERE id IN (SELECT id FROM b)
그런 hugh 쿼리를 실행하기 전에 표시를 재구성해야 할 수도 있습니다.음, 정기적으로 재건해야 합니다.
REPAIR TABLE a QUICK;
REPAIR TABLE b QUICK;
그런 다음 위의 쿼리 중 하나를 실행합니다(즉,
DELETE FROM a WHERE id IN (SELECT id FROM b)
쿼리 자체가 이미 최적의 형태이므로 인덱스를 업데이트하면 전체 작업이 이렇게 오래 걸립니다.작업 전에 테이블의 키를 비활성화할 수 있으므로 작업 속도가 빨라집니다.즉시 필요하지 않은 경우 나중에 다시 켤 수 있습니다.
하나의 은 '먹다'를 입니다.deletedflag-column을 테이블로 이동하고 다른 쿼리를 조정하여 해당 값을 고려합니다.mysql에서 가장 빠른 부울 유형은 다음과 같습니다.CHAR(0) NULL(true = ', false = NULL).빠른 작업이므로 나중에 값을 삭제할 수 있습니다.
sql 문에서도 같은 생각이 나타납니다.
ALTER TABLE a ADD COLUMN deleted CHAR(0) NULL DEFAULT NULL;
-- The following query should be faster than the delete statement:
UPDATE a INNER JOIN b SET a.deleted = '';
-- This is the catch, you need to alter the rest
-- of your queries to take the new column into account:
SELECT * FROM a WHERE deleted IS NULL;
-- You can then issue the following queries in a cronjob
-- to clean up the tables:
DELETE FROM a WHERE deleted IS NOT NULL;
이것 역시 원하는 것이 아닌 경우 mysql docs가 삭제문의 속도에 대해 무엇을 말하고 있는지 확인할 수 있습니다.
그런데, 제 블로그에 위의 글을 올린 후, Percona의 Baron Schwartz는 그의 maatkit이 이 목적만을 위한 도구를 이미 가지고 있다는 것을 제게 알려주었습니다. - mk-archiver.http://www.maatkit.org/doc/mk-archiver.html 를 참조해 주세요.
이 툴은 업무에 가장 적합한 툴일 가능성이 높습니다.
분명히 그SELECT토대를 구축하는 쿼리DELETE조작이 매우 빠르기 때문에 외부 키 제약이나 인덱스 중 하나가 매우 느린 쿼리의 원인이라고 생각합니다.
해라
SET foreign_key_checks = 0;
/* ... your query ... */
SET foreign_key_checks = 1;
그러면 외부 키에 대한 검사가 비활성화됩니다.아쉽게도 InnoDB 테이블로 키업데이트를 비활성화할 수 없습니다(적어도 방법은 모르겠습니다).MyISAM 테이블을 사용하면 다음과 같은 작업을 수행할 수 있습니다.
ALTER TABLE a DISABLE KEYS
/* ... your query ... */
ALTER TABLE a ENABLE KEYS
이러한 설정이 쿼리 기간에 영향을 줄지는 실제로 테스트하지 않았습니다.하지만 시도해 볼 만해요.
terminal을 사용하여 날짜 기반에 연결하고 다음 명령을 실행합니다. 각각의 결과 시간을 보면 10, 100, 1000, 10000, 100000 레코드의 삭제 시간이 곱셈되지 않습니다.
DELETE FROM #{$table_name} WHERE id < 10;
DELETE FROM #{$table_name} WHERE id < 100;
DELETE FROM #{$table_name} WHERE id < 1000;
DELETE FROM #{$table_name} WHERE id < 10000;
DELETE FROM #{$table_name} WHERE id < 100000;
1만 건을 삭제하는 시간은 10만 건을 삭제하는 시간의 10배에 미치지 못한다.레코드를 더 빨리 삭제하는 방법을 찾는 것 외에 몇 가지 간접적인 방법이 있습니다.
1, table_name의 이름을 table_name_bak으로 변경하고 table_name_bak에서 table_name으로 레코드를 선택할 수 있습니다.
2, 10,000개의 레코드를 삭제하려면 1000개의 레코드를 10회 삭제하면 됩니다.이를 수행하기 위한 예제 루비 스크립트가 있습니다.
#!/usr/bin/env ruby
require 'mysql2'
$client = Mysql2::Client.new(
:as => :array,
:host => '10.0.0.250',
:username => 'mysql',
:password => '123456',
:database => 'test'
)
$ids = (1..1000000).to_a
$table_name = "test"
until $ids.empty?
ids = $ids.shift(1000).join(", ")
puts "delete =================="
$client.query("
DELETE FROM #{$table_name}
WHERE id IN ( #{ids} )
")
end
id 필드를 사용하여 단일 테이블에서 여러 개의 Row form MySQL을 삭제하는 기본 기술
DELETE FROM tbl_name WHERE id <= 100 AND id >=200; 이 쿼리는 특정 테이블에서 100과 200 사이의 일치 조건을 삭제합니다.
언급URL : https://stackoverflow.com/questions/812512/faster-way-to-delete-matching-rows
'programing' 카테고리의 다른 글
| MySQL 행을 읽기 전용으로 설정하는 방법이 있습니까? (0) | 2022.12.13 |
|---|---|
| Java 8의 체인 옵션 (0) | 2022.12.13 |
| 반복기의 개수/길이/사이즈를 얻는 가장 좋은 방법은 무엇입니까? (0) | 2022.12.13 |
| 간단한 CLI Java linter가 있나요? (0) | 2022.12.13 |
| JDBC와 함께 MySQL을 사용한 .sql 스크립트 실행 (0) | 2022.12.13 |